cancel
Showing results for 
Search instead for 
Did you mean: 

Multiple UPDATE in UPDATE_INSERT in JDBC

Former Member
0 Kudos

Dear Friends,

There is a requirement in which a single interface has to perform either an INSERT or UPDATE.

In the following example, UPDATE has to be performed when a1 = 123 and also when a1 = 444; whereas INSERT for the last iteration (a1 = 999....i1 =999)

Is this possible? If not, what can be the alternative to perform multiple UPDATES?

<STATEMENT>

<TableName action="UPDATE_INSERT">

<TABLE>tablename</TABLE>

<access>

<a1>123</a1>

<b1>Description1</b1>

<c1>Cat1</c1>

<d1>A</d1>

<e1>Kg</e1>

<f1>0</f1>

<g1>0</g1>

<ha>123</ha>

<i1>123</i1>

</access>

<access>

<a1>444</a1>

<b1>Description2</b1>

<c1>Cat2</c1>

<d1>A</d1>

<e1>Km</e1>

<f1>0</f1>

<g1>0</g1>

<ha>444</ha>

<i1>444</i1>

</access>

<access>

<a1>999</a1>

<b1>Description3</b1>

<c1>Cat3</c1>

<d1>A</d1>

<e1>Lt</e1>

<f1>0</f1>

<g1>0</g1>

<ha>999</ha>

<i1>999</i1>

</access>

<key>

<a1>123</a1>

</key>

<key>

<a1>444</a1>

</key>

</TableName>

</STATEMENT>

Accepted Solutions (1)

Accepted Solutions (1)

prateek
Active Contributor
0 Kudos

You may perform multi-mapping first to distinguish between Update and Insert statements. Then pass it in separate call. As the message would be divided only into 2 types of messages only, so won't be a performance problem as well.

Regards,

Prateek

Former Member
0 Kudos

Hi Prateek,

But we will not be able to differentiate between INSERT or UPDATE in mapping. Only when we hit the target DB we will come to know.

Ideally this inteface should peform multiple INSERTS; but when the <key> field is already found present than an UPDATE has to be performed. Which again can be multiple!

former_member187339
Active Contributor
0 Kudos

Hi Vaibhav,

>>But we will not be able to differentiate between INSERT or UPDATE in mapping. Only when we hit the target DB we will come to know.

For this reason we always use Update_insert option (rather than update or insert)

>>Ideally this inteface should peform multiple INSERTS; but when the <key> field is already found present than an UPDATE has to be performed. Which again can be multiple!

Use update_insert as the action name and create multiple Statement node (each with a access and a key)

Regards

Suraj

Former Member
0 Kudos

Hey Suraj,

Many thanks.

Infact, one of my collegue just suggest a similar option. ONly question I have is, Is there any possiblility of table DEADLOCK?

As dont konw, whether COMMIT will happen after execution of every STATEMENT or all statement together!

If you can positively answer this, I am done

former_member187339
Active Contributor
0 Kudos

Hi VaIbhav,

Check the database transaction level in this page, there wont be any problem

http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/frameset.htm

Regards

Suraj

Former Member
0 Kudos

Hi Suraj,

You mean, DEFAULT will work? or something else?

Also the following is mentioned in must read below that - To avoid data inconsistencies in the database when the isolation level is lowered, ensure that multiple database transactions cannot access the database simultaneously.

former_member187339
Active Contributor
0 Kudos

Hi Vaibhav,

Every Statement tag in your JDBC datatype is treated a a Single Transaction,

If you have multiple ACCESS Level tags inside a Single Statement tag and only if all of these are successful is data commited else Rolled back.

So in your case each statement will have separate commit or rollback. Also default is the mode to be used for database transaction isolation

Also the processing is done in sequential order one statement after another, so i dont think there will be a deadlock situation

Regards

Suraj

Answers (1)

Answers (1)

former_member206760
Active Contributor
0 Kudos

In your structure in the key field you have entry for a1 = 123 and a1= 144 but there is no entry for a1 = 999 .

modify your mapping accordingly ...after that if thedatabase table already has entries for a1 = 123 and a1 = 144 it will get updated and if there are no enties with a1 = 999 it will create a new record in the table