JDBC receiver error

[Former Member]

Hello experts.

I have PI 7.3 on an ORACLE DB.

I have done some testing on JDBC lookup and succeeded, and now I am trying to implement a JDBC receiver scenario.

I have built the DT according to the SAP help:

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/frameset.htm

I'm trying to get the MSG_ID from the table:  XI_AF_LMS_DATA (in the PI DB) for a specific pair of NAME and VAL

I'm sending this data:

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

<ns0:MT_SELECT xmlns:ns0="http://AAAbackup">

<SELECT_MESSAGE_BY_FILTERS>

<dbTableName action="select">

<table>SAPXIDDB.XI_AF_LMS_DATA</table>

<access>

<MSG_ID></MSG_ID>

</access>

<key>

<NAME>ShipmentNumber</NAME>

<VAL compareOperation="EQ">0000000001</VAL>

</key>

</dbTableName>

</SELECT_MESSAGE_BY_FILTERS>

</ns0:MT_SELECT>

from the help - I guessed that this should generate the following SQL query:

SELECT MSG_ID from SAPXIDDB.XI_AF_LMS_DATA where NAME='ShipmentNumber' and VAL='1'

In the response I am getting the following error:

com.sap.engine.interfaces.messaging.api.exception.MessagingException:
Error processing request in sax parser: Error when executing statement for
table/stored proc. 'SAPXIDDB.XI_AF_LMS_DATA' (structure
'SELECT_MESSAGE_BY_FILTERS'): java.sql.SQLSyntaxErrorException: ORA-00904:
"SHIPMENTNUMBER": invalid identifier

I checked a bit - and it seems that the oracle emits this error - when the column name is incorrect.

So I did a little test and changed the "ShipmentNumber" in the xml payload to "NAME"

and now I receive this xml payload as response:

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

<ns0:MT_SELECT_response xmlns:ns0="http://AAAbackup">

<SELECT_MESSAGE_BY_FILTERS_response />

</ns0:MT_SELECT_response>

Can any one please tell me what am I doing wrong? how am I supposed to generate the SQL query:

SELECT MSG_ID from SAPXIDDB.XI_AF_LMS_DATA where NAME='ShipmentNumber' and VAL='1'

Any help will be appreciated.

Thanks,

Imanuel Rahamim.

[MichalKrawczyk · Accepted solution]

hi,

have you tried setting logSQLStatement = true

as per Note 801367 - XI 3.0 JDBC Adapter: Additional Parameters

to see what kind of SQL statement you generate in receiver JDBC adapter ?

(you can use the SQL statement later on the DB itself)

Regards,

Michal Krawczyk

[Former Member · Accepted solution]

Hello.

I have tried this and got this result:

for the JDBC receiver scenario this is the query:

SELECT MSG_ID FROM SAPXIDDB.XI_AF_LMS_DATA WHERE
(NAME LIKE ShipmentNumber AND VAL LIKE 0000076060)

As I said - I have also implemented a JDBC lookup. I have run it with the same parameters and this is the query I see:

SELECT MSG_ID FROM SAPXIDDB.XI_AF_LMS_DATA WHERE
(NAME LIKE ShipmentNumber AND VAL LIKE 0000076060)

seems exactly the same....

I'm also enclosing the screenshots of the rwb message monitoring I took this queries from:

receiver:

lookup:

I have used the same CC for both.

seems like a bug. but I can't find any reference for this...

Any one knows something of this? or maybe tell me what else to check?

(should I go to the DB admin and ask him to make a trace? I have tried the NWA DB monitoring - but I didn't find this query anywhere...)

Thanks again,

Imanuel Rahamim.

[MichalKrawczyk · Accepted solution]

Hi,

>>>>should I go to the DB admin and ask him to make a trace?

this is the quickest way - if you can do this I'd suggest doing that ASAP

Regards,

Michal Krawczyk

[Former Member · Accepted solution]

Hello.
I solved the problem, but I have one last question.

I have consulted the DBA admin - and here are the results:

when I use this query:

SELECT MSG_ID FROM SAPXIDDB.XI_AF_LMS_DATA WHERE
(NAME LIKE ShipmentNumber AND VAL LIKE 0000076060)

in sqlplus on the server itself, the query failes with the same error that I saw before.

but when I use this query:

SELECT MSG_ID FROM SAPXIDDB.XI_AF_LMS_DATA WHERE
(NAME LIKE 'ShipmentNumber' AND VAL=0000076060)

or

SELECT MSG_ID FROM SAPXIDDB.XI_AF_LMS_DATA WHERE
(NAME LIKE 'ShipmentNumber' AND VAL LIKE '%76060')

then I get the correct result.

I have tried to run this query with the JDBC receiver, but I got the following error:
Unable to execute statement for table or stored procedure. 'SAPXIDDB.XI_AF_LMS_DATA' (Structure 'SELECT_MESSAGE_BY_FILTERS') due to java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis

I searched a little in the CC parameters and saw this parameter:
Escape Symbol for Apostrophe

I now understand that by default the PI switches ' (one Apostrophe) for '' (two Apostrophes) to avoid syntax errors if Apostrophe shows in the data.
however - in this case - I must use an Apostrophe so that my query will work
and so I switched the parameter to one Apostrophe and now it works.
I add a screenshot here to explain what I did.

my question is: is this the correct way to do this?

Thanks again for all the help,
Imanuel Rahamim.

[baskar_gopalakrishnan2 · Accepted solution]

You might want to check this thread regarding usage of apostrophe in the jdbc receiver channel.

http://scn.sap.com/thread/1752807

Refer section 5. You can use / or "" as default replacement characters.

http://help.sap.com/saphelp_nwpi711/helpdata/en/48/465581929734d9e10000000a42189d/content.htm

[Former Member · Accepted solution]

The Problem is that I don't want to replace the apostrophe in an escape characther.

I need the adapter to generate it automatically when the DB requires it in order to perform the query successfully.

It seems that I must manually take care of that, so I'll add a function that will check if my string has quotes, and will add an escape char in case there is.

Thanks again.

[former_member219622]

Hi Imanuel Rahamim

We are trying to read the PI Table SAPXIDDB.XI_AF_LMS_DATA. Do you know how did you solve the issue in adapter. Did you use some setting in adapter ?

We have a orcale database with PI Single Stack.

[Ryan-Crosby]

Hi, Imanuel,

I was reading through your last question and it looks like a question of whether or not the database can parse the query as it's written.  From the look of it the database is expecting a string literal or possibly a variable (in the case of a stored procedure) directly after the use of the LIKE operator.  In the example where it fails the two values used for LIKE are not closed in quotes so it cannot be parsed properly.

Regards,

Ryan Crosby

[Former Member]

just you have to provide " logSQLStatement = true" in advanved tab on receiver communication channel what was psoted by earlier poster then have to check  Query in communication channel monitoring on RWB and what is the cause for error and compare ur receiver structure and Query.

[iaki_vila]

HI Imanuel,

What is VAL type in the Oracle DB?

Have you tried with the structure that shows this documentation http://help.sap.com/saphelp_nw73ehp1/helpdata/en/44/7b7171fde93673e10000000a114a6b/frameset.htm (It similar that you pointed above but for SAP PI 7.3 that is your version )?, for the equal comparation doesn't seem neccesary to use compareOperation="EQ"

Regards