Query to be executed at Syn JDBC Receiver

[Former Member]

Dear Freinds,

I am facing with this issue, i am passing a Native SQL Query as string from Syn Proxy to Syn JDBC with a key field as place holder.

As per my understanding place holder field should have the same as the once used in Native SQL Query. In the query its prefixed & suffixed with $. Correct ?

Also in the action field i have put SQL_QUERY correct ?

I am passing a Select Query (join query). In the query i have same conversion functions for date manipulations which comes within ' ' (single quotes). This quotes changes to '

What could be the problem. In the Communication channel under Processing tab in SQL Syntax parameters (Escape symbol for apostrophe) what should be given ?

Based on the execution of the Select query some fields are fetcehd from Oracle table. This will be response message from JDBC to Proxy.

But i am getting error as '' Invalid SQL stmt" . What could be the problem. How to rectify.

Please give me soultion for the above questions.

Thanks & Regards

K.Ramesh

[Former Member]

Thanks Prateek Raj,

Your soulution worked for me.

[prateek]

Instead of Native SQL Query, use the simple JDBC protocol. Then perform the same thing - Provide ur query as Value to <access> node of receiver structure. Use action as SQL_QUERY.

Try placeholders with $ at both the sides.

Regards,

Prateek

[Former Member]

Dear Friends,

I have done the same thing. We r using PI 7.1.

I have Sync Proxy -> Syn JDBC . I tried to execute a simple query at the JDBC adapter by sending it through *access* field in the XML structure.

This is the input

<?xml version="1.0" encoding="UTF-8" ?>

- <ns0:CoilNoReceive xmlns:ns0="urn:essar-com:production:shearslitpoconfirm">

- <stmt>

- <TABLENAME action="SQL_QUERY">

<access>SELECT coil_no,PARTS_ACT FROM P_INV where fac_index <> 6 and fac_index <>7 and rownum<5</access>

</TABLENAME>

</stmt>

</ns0:CoilNoReceive>

I am getting this error msg..

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

- <!-- Inbound Message

-->

- <SAP:Error xmlns:SAP="http://sap.com/xi/XI/Message/30" xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/" SOAP:mustUnderstand="1">

<SAP:Category>XIAdapterFramework</SAP:Category>

<SAP:Code area="MESSAGE">GENERAL</SAP:Code>

<SAP:P1 />

<SAP:P2 />

<SAP:P3 />

<SAP:P4 />

<SAP:AdditionalText>com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error 'java.sql.SQLException: ORA-00900: invalid SQL statement ' executing service raw2sql with SQL statement '<?xml version="1.0" encoding="UTF-8"?> <ns0:CoilNoReceive xmlns:ns0="urn:essar-com:production:shearslitpoconfirm"><stmt><TABLENAME action="SQL_QUERY"><access>SELECT coil_no,PARTS_ACT FROM P_INV where fac_index <> 6 and fac_index<>7 and rownum<5</access></TABLENAME></stmt></ns0:CoilNoReceive>'</SAP:AdditionalText>

<SAP:Stack />

<SAP:Retry>M</SAP:Retry>

</SAP:Error>

What could be the problem. Reply ASAP.

Thanks

K. Ramesh

[prateek]

Make sure ur Message Protocol is XML SQL format

fac_index 6 and fac_index 7

Whats this in ur SQP query?

Shouldn't there be some operator?

Regards,

Prateek

[Former Member]

HI Ramesh

There is a problem with SQL statement.

Did you tried the same with another SQL editor? try it if works fine then apply here.

Thanks

Gaurav Bhargava

[Former Member]

Dear Freinds,

I have checked the same query (inner join) in SQL Plus (Oracle). Its working perfectly there.

Anybody can tell me what should be the escape character for ' (apostrophe) in Oracle.

And should place holders be prefixed & suffixed b4 and after $ symbol by ' (apostrophe).

Thanks & Regards

Ramesh

[Former Member]

---

I am passing a Select Query (join query). In the query i have same conversion functions for date manipulations which comes within ' ' (single quotes). This quotes changes to &apos;------

quotes are getting change after firing the query to oracle or in XI itself ?

[Former Member]

Hi Friend,

It (apostrophe) is getting replaced in Xi itself. We checked that in Messg Mapping.

Solution is awaited.

Thanks

Ramesh