on 02-05-2010 4:21 PM
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>
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
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
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
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.