Specify Keys in Data Type for receiver JDBC adapter

[Former Member]

Hi guys,

I have the following issue:

In a proxy to JDBC scenario, I want to specify somehow the key values for the SQL statement in the data type that I define for the receiver JDBC adapter. Thus, I am using the following structure in data type:

root

Statement

dbTable

action

table

access

CustomerID

Name

HouseNo

Key

CustomerID

But in the SQL statement that is produced (I can see that in RWB, CC Monitoring, Audit Log), the "Key" segment is translated into "OR". E.g. UPDATE_INSERT ("A100" "Evaggelos Gkatzios" "El Alamein 19") OR ("A100")

Do you have any ideas ?

[Former Member · Accepted solution]

Hi,

Its clear from the statement in RWB (in CC monitoring's auditlog) you are using UPDATE_INSERT as the action.

Please correct me if this is OK. Now the interpretation of input xml file will be like this,

If the input XML is in the following format

<root>

<Statement>

<dbTable action=UPDATE_INSERT>

<table>EXAMPLE</table>

<access>

<CustomerID>ABC</CustomerID>

<Name>xyz</Name>

<HouseNo>123</HouseNo>

</access>

<key>

<CustomerID>DEF</CustomerID>

</key>

</dbTable>

</Statement>

</root>

Resultant SQL after parsing the XML file will be,

1. It will first check with the condition WHERE CustomerID = DEF. If it finds the record with this condition then it will update the record with CustomerID=ABC, Name =xyz & HouseNo=123.

2. If it doesn't find any record with that CustomerID then, it will insert a new record with the same details.

In auditlog, you will find two SQL statements like,

1.UPDATE EXAMPLE SET CustomerID=ABC, Name =xyz & HouseNo=123 WHERE CustomerId=DEF

2.INSERT INTO EXAMPLE VALUES(ABC,xyz,123)

hope this has given clear information about your query,

Regards,

Soorya

[Former Member · Accepted solution]

Hi Soorya,

Thank you for your reply. Yes you are right, I am using UPDATE_INSERT as the action.

If I use capital letters instead of lower case for the names of the segments <ACCESS> and <KEY> does it make any difference ? Also, if I have more than one key per table/record, do I have to create a separate <KEY> segment for each key or can I specify them under the same <KEY> segment ?

Thank you. Evaggelos

[former_member200962 · Accepted solution]

do I have to create a separate <KEY> segment for each key or can I specify them under the same <KEY> segment ?

You have to create separate key node (key1, key2, etc)....i would suggest you keep the node names in the same case as it is shown in this help section: http://help.sap.com/saphelp_nw70/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Regards,

Abhishek.

[former_member187339 · Accepted solution]

Hi Evaggelos,

>>If I use capital letters instead of lower case for the names of the segments <ACCESS> and <KEY> does it make any difference ?

These fields are not case sensitive. IF you want to specify more than one KEY in an OR condition then you need to have Key1, KEy2 etc as the field names

>>do I have to create a separate <KEY> segment for each key or can I specify them under the same <KEY> segment ?

If you specify multiple values in one KEY field then it will be AND condition and if you specify multiple key field then it will be an OR condition for the query

Regards

Suraj

[Former Member]

Hi,

Can you pls tell what action are you mentioning for this ?

-Tanaya.

[Former Member]

Hello Tanya,

I think they are using UPDATE_INSERT as the action item in the input XML file.

Please find my reply about the XML parsing and converting it into SQL statements(depending upon the condition satisfaction)

Please get back to me if you have any queries related to this.

Regards,

Soorya.