JDBC - INSERT_UPDATE

[Former Member]

FILE -> PI -> JDBC (SQL server 2005)

1) Tablename = realDbTableName

2) key field = col2

3) database table already has a record for key field col2=abc (INSERT from SAP PI to database works without error)

4) FILE has modified record for key field 'col2'

5) Now PI sends UPDATE_INSERT to database. This time it should update the already existing record in database.

6) Verified in RTW having "UPDATE_INSERT" as below:

<StatementName1>

<dbTableName action= u201CUPDATE_INSERTu201D>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2new</col2>

</access>

<key1>

<col2>val2old</col2>

</key1>

</dbTableName>

</StatementName1>

I see error in RTW as:

Message processing failed. Cause: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'dBTableName' (structure 'Statement'): com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK_KEY1'. Cannot insert duplicate key in object

I requested DBA to turn on trace for database and he confirmed that database received INSERT and hence it is failing as there is already record for key field col2='value2'.

INSERT INTO realDbTableName

(col1, col2)

VALUES

(u2018value1u2019, u2018value2u2019)

The question is:

RTW "UPDATE_INSERT" is converted to INSERT at database, why it did n't send UPDATE? Please help with pointer which log to look at. Thank you.

[Former Member · Accepted solution]

Hi,

You can check which SQL statement is going to the Database server.

To see SQL statement in DC receiver,

DC Receiver Adapter->Advanced tab->Check Advanced Mode-> in the Additional Parameters section,

enter u201ClogSQLStatmentu201D and its corresponding value u201Ctrueu201D

This will help in debugging, see logging in Adapter Engine.

If your scenario is synchronous you can get this details. update_count and insert_count.

SAP help says

http://help.sap.com/saphelp_nw04/Helpdata/EN/22/b4d13b633f7748b4d34f3191529946/frameset.htm

action=UPDATE_INSERT

The statement has the same format as for the UPDATE action. Initially, the same action is executed as for UPDATE. If no update to the database table can be made for this action (the condition does not apply to any table entry), values of the table described in the <access> element are inserted in accordance with the description of the action INSERT. <key> elements are ignored in this case.

The response document has the following format; one of the two values is always 0 because either an UPDATE or an INSERT action is always executed:

<update_count>count</update_count>

<insert_count>count</insert_count>

[Former Member · Accepted solution]

Thanks. Awarded points.

I could see the SQL statements to database and fixed the mapping to generate correct SQL.

In Integration Builder:

Receiver Adapter->Advanced tab->Check Advanced Mode-> in the Additional Parameters section,

enter u201ClogSQLStatementu201D and its corresponding value u201Ctrueu201D

displayed all the database calls from SAP PI at RWB (communication Channel Monitoring-->Select the Adapter and You can see the SQL Statement Logged in the Audit Log).

[Former Member]

Hi Ravi,

Message processing failed. Cause: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'dBTableName' (structure 'Statement'): com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK_KEY1'. Cannot insert duplicate key in object 

Make sure that the <key> field is not null.

The question is: 
RTW "UPDATE_INSERT" is converted to INSERT at database, why it did n't send UPDATE? Please help with pointer which log to look at.

UPDATE is executed first. The below quote is from SAP Help, so I INSERT won't execute first.

Initially, the same action is executed as for UPDATE. If no update to the database table can be made for this action (the condition does not apply to any table entry), values of the table described in the <access> element are inserted in accordance with the description of the action INSERT. <key> elements are ignored in this case.

Regards,

Neetesh