JDBC Receiver: Meaning of action = UPDATE_INSERT

[Former Member]

Hi everybody,

do you knwo how exactly UPDATE_INSERT works?

Does the adapter

1) first try an INSERT and when the key is already been assigned in the DB-table then

b) makes an UPDATE ?

Thanks

Regards Mario

[Former Member · Accepted solution]

Hi Mario,

Check these too..

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

cheers,

Prashanth

[Former Member · Accepted solution]

Thank you everybody,

update_insert works! BUT ONLY for the first record of the payload.

The second record is not updated!

E.g. If I post the message via JDBC:

<Statement_TABLES>

<dbTableName_action="update_insert">

__<table>MyTable</table>_

-_<access>

__<CRM_GP_NR>1234567893</CRM_GP_NR>_

__<BUSINESS_UNIT>1308</BUSINESS_UNIT>_

__</access>

-_<key1>

__<CRM_GP_NR>1234567893</CRM_GP_NR>_

__</key1>

__</dbTableName>

-_<dbTableName_action="update_insert">

__<table>MyTable</table>_

-_<access>

__<CRM_GP_NR>1234567894</CRM_GP_NR>_

__<BUSINESS_UNIT>1308</BUSINESS_UNIT>_

__</access>

-_<key1>

__<CRM_GP_NR>1234567894</CRM_GP_NR>_

__</key1>

__</dbTableName>

__</Statement_TABLES>

Only 1234567893 is updated, 1234567894 is not

Any ideas?

Regards Mario

[bhavesh_kantilal · Accepted solution]

Mario,

For every UPDATE,_INSERT, create a separate STATEMENT level tag and then i think it should work,

<Statement_TABLES>

<dbTableName_action="update_insert">

__<table>MyTable</table>_

-_<access>

__<CRM_GP_NR>1234567893</CRM_GP_NR>_

__<BUSINESS_UNIT>1308</BUSINESS_UNIT>_

__</access>

-_<key1>

__<CRM_GP_NR>1234567893</CRM_GP_NR>_

__</key1>

__</dbTableName>

<b></Statement_TABLES>

<Statement_TABLES></b>-_

<dbTableName_action="update_insert">

__<table>MyTable</table>_

-_<access>

__<CRM_GP_NR>1234567894</CRM_GP_NR>_

__<BUSINESS_UNIT>1308</BUSINESS_UNIT>_

__</access>

-_<key1>

__<CRM_GP_NR>1234567894</CRM_GP_NR>_

__</key1>

__</dbTableName>

__</Statement_TABLES>

Regards,

Bhavesh

[bhavesh_kantilal]

Mario,

UPDATE_INSERT will first check if the row exists and if yes, it will Update the row.

If the row doesnot exists then a new row will be inserted.

It uses the condition under KEY to check if any row exists that satisfies the condition and if it does, it updates all rows else it inserts a new row with the values.

Regards,

Bhavesh

[MichalKrawczyk]

hi,

first update then insert:

"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."

from help.sap.com

Regards,

michal