Multiple records as a single transaction in JDBC Receiver Adapter

[Former Member]

Hi,

I am sending multiple records in a single message to a JDBC receiver adapter to get updated to the database. How to make all the insert a single transaction. Like all the records has to be inserted else all has to be rolled back.

For eg for table Employee two fields EMPNO and EMPNAME

EMPNO EMPNAME

-

---

1 Jay

2 Christie

These two records are in the same message and has to be updated

if one fails the other has to be rolled back.

How can i achieve it using a JDBC Receiver Adapter.

Thanks

Sebin

[justin_santhanam · Accepted solution]

Sebin,

Did you got the logic of the UDF, it will insert the records as below

INSERT INTO EMP_TABLE (EMP_NO,EMPNAME) VALUES('1','Jay'),('2','Christie')

Sebin , did the above URL works for you? Please reply then only we will know whether it works for you or not, even though u don't reward points it doesn't matter, but i'll expect reply for the work done.

Best regards,

raj.

Message was edited by:

Raj

[Former Member · Accepted solution]

Hi raj,

No i found that, JDBC Adapter supports transactions.

So i can do multiple updates in the same transaction.

Thanks for the Reply,

I have rewarded points... You might not have noticed..

Thanks

Sebin

[justin_santhanam · Accepted solution]

Sebin,

You might have misunderstood me, did I asked u for points? I asked only reply from you!!!

I'm juz learning from you guys that how to solve the issues, thats it sebin. I'm not here only for points alone!!!

Best regards,

raj.

[bhavesh_kantilal]

Like pointed by Udo, Every STATEMENT tag in your JDBC datatype is treated a a Single Trnsaction,.

You can have multiple ACCESS Level tags inside a Single STATEMENT tag and only if all of these are successful is data comminited else Rolled back.

Regards

Bhavesh

PS : Have not given this a short thought and it would be interesting if you can provide us with the actual reasults . Do not have access to a DB and a JDBC driver on my XI server

[Former Member]

Thank you all for the reply..

Still am waiting for the driver to be uploaded.

Once the scenarion is done.. I will post with the updates.

thanks

Sebin

[Former Member]

Hi Guys,

I'm following the thread and try my scenario with the suggestions you guys given.

However my test failed and it only update to my Header Table (Without Details), is there anything wrong with my data type declaration?

Below is the data type:-

mt_rmp03_receiver

...STATEMENT

......Header

.........action

.........TABLE

.........access

............Sample1

............Sample2

............Sample3

......Details

.........action

.........TABLE

.........access

............SampleA

............SampleB

............SampleC

Thanks !

Message was edited by:

Ming Fei Pua

[bhavesh_kantilal]

Hi,

Can you provide more details on what you want here and what the issue is?

Thanks,

Regards

Bhavesh

[Former Member]

Hi Bhavesh,

Actually I have a scenario quite similar with the one posted by Sebin in above.

I have project scenario (JDBC Receiver) which need to insert into 2 different tables without using BPM. I want to use 1 XML SQL Format to execute insert syntax into 2 different table. (Header and Details)

In the mean time, if there is a insert failure to any one of the table, the system will do roll back.

Currently I have my data type designed as below:-

mt_rmp03_receiver

...STATEMENT

......Header

.........action (INSERT)

.........TABLE (Header Table)

.........access

............Sample1 (Field 1)

............Sample2 (Field 2)

............Sample3 (Field 3)

......Details

.........action (INSERT)

.........TABLE (Details Table)

.........access

............SampleA (Field A)

............SampleB (Field B)

............SampleC (Field C)

So my question is my XML SQL Format above correct :-

a) Multiple ACCESS Level tags inside a Single STATEMENT tag.

b) When there is a insert failure, the data will roll back because it is under same STATEMENT tag.

When I execute the scenario, I only get the Header Table get inserted with records whereby the Details is empty.

Many Thanks.

[bhavesh_kantilal]

> So my question is my XML SQL Format above correct :-

>

yes it is correct.

> a) Multiple ACCESS Level tags inside a Single

> STATEMENT tag.

> b) When there is a insert failure, the data will roll

> back because it is under same STATEMENT tag.

As far as I know, this should have reverted back the insertion made into the JDBC table.

In the Receiver JDBC adapter, in the advanced mode, can you make the trasnaction level as Serializable and give this a shot?

> When I execute the scenario, I only get the Header

> Table get inserted with records whereby the Details

> is empty.

Is this receiver JDBC adapter in Green? IS there any error in the Receiver JDBC adapter?

Regards

Bhavesh

[Former Member]

Hi Bhavesh,

Yes, i've checked into sxmb_moni it gives me all success flag per record (I parsed the record from file 1 by 1) but not any Details record is populated. It seems XI only take the first ACCESS tag in my data type.

As for your suggestion to make the transaction level as Serializable I would give it a try. Many Thanks here.

[bhavesh_kantilal]

Check the status of the JDBC adapter in Comm Channel Moniroting in the RWB.

MONI shows only the status of message in Integration Engine and not in the Adapter engine.

Regards

Bhavesh

[Former Member]

Hi Bhavesh,

It works! Records are inserted into Header and Details now with Multiple access tag under single STATEMENT tag. I just recreated all the data type and retest and it works. thanks.

Will continue with insert multiple records with single transaction - rollback test. Hopefully can update you if I have any test result. Thanks again.

[bhavesh_kantilal]

>

> Will continue with insert multiple records with

> single transaction - rollback test.

Please do update me on the results.

regards

Bhavesh

[Former Member]

Pua Ming Fei, I have the very same problem.

I have XML messages like

STATEMENT

...TABLENAME1 action=INSERT

........ACCESS

............element1

............element2

....../ACCESS

...TABLENAME2 action=INSERT

......ACCESS

............element1

....../ACCESS

/STATEMENT

Just like in your case, only the first Table will be filled, the second remains untouched, no error message at all.

What exactly did help you, or how does your XML look like?

Thanks in advance!

[Former Member]

Rolf,

>>>only the first Table will be filled, the second remains untouched

Can you explain a bit more about your problem? If you have the target XML in the above mentioned way, then if there is any error both the tables will not be updated. The changes will be rolled back. Is this not happening in your case.

Regards,

Jai Shankar

[Former Member]

Hi Jai,

there is no error, no rollback. To be more specific:

The SQL statement generated by the JDBC Adapter contains only the one insert command for the first Table. This is done perfectly, so no rollback needed. But what I need is a second insert-command in the SQL-statement.

If I understood correctly, then a second STATEMENT would cause two different transactions, which I do not want. I want one Transaction (because I want the rollback of both insert commands, if one of them fails). So that is why I want to have only one STATEMENT in the XML message. Am I right here? SAP gives examples which all contain one STATEMENT for each action, but in this thread I found, that it could also be possible to have only one STATEMENT. Thanks for your interest in helping!! Rolf

[Former Member]

HI Rolf,

>>>If I understood correctly, then a second STATEMENT would cause two different transactions

Yes, you are on the right track.

>>>want. I want one Transaction (because I want the rollback of both insert commands, if one of them fails). So that is why I want to have only one STATEMENT in the XML message. Am I right here?

Exactly. If you have a single statement level node in your XML, then at db level you should have a single transaction (no matter how many inserts you perform with in the statement). So if any one of the insert fails, all the inserts should be rolled back.

Can you test your scenario again with correct values for 1st table and incorrect values for 2nd table. In this case, both the tables should not get updated.

Regards,

Jai Shankar

[Former Member]

Jai,

thanks for your help, it's good to know to be on the right track.

I will do that rollback test as a second step. The first step is a prerequisite to that, the SQL statement must contain the two insert commands. (In the current situation, I could put anything in the second insert without any difference in the result.

Do you or anyone have an idea, what could be the reason why the adapter simply ignores the second block of TABLENAME2 action=INSERT in the XML message??

[Former Member]

Rolf,

I got your problem now. Do you mean to say, the second part of the insert is not happening at all...?

K. Try this. Add the parameter in the receiver JDBC communication channel (Select Advanced mode)

<b>parameter</b>: logSQLStatement

<b>value</b>: true

This will enable you to see the SQL statment formed in MDT.

Regards,

Jai Shankar

[ranjit_deshmukh]

Hi Rolf,

please make the JDBC adapter as serializable, in advanced mode

and now try.

Ranjit

[Former Member]

Jai,

I did that already. I see in the SQL Statement, that only the first insert statement appears. No trace of the second one. See for yourself:

SQL Statement:

INSERT INTO R3_DISPO (DISP_NR, LIEF_DATUM) VALUES (1000121871, 20070804)

But the XML message sent to the adpater was:

<?xml version="1.0" encoding="UTF-8" ?>

- <ns0:R3_DISPO_JDBC xmlns:ns0="urn:de.steag.se">

- <Statement>

- <R3_DISPO action="INSERT">

- <access>

<DISP_NR>1000121871</DISP_NR>

<LIEF_DATUM>20070804</LIEF_DATUM>

</access>

</R3_DISPO>

- <R3_ALERT action="INSERT">

- <access>

<HANDSHAKE>E</HANDSHAKE>

<IDNR>10000000</IDNR>

</access>

</R3_ALERT>

</Statement>

</ns0:R3_DISPO_JDBC>

where R3_DISPO is TABLENAME1 and R3_ALERT is TABLENAME2. Any further ideas? I'm a bit lost.....

[Former Member]

Ranjit,

that is the current setting. Rolf

[Former Member]

Am running out of ideas too.:-(

The XML you have given looks just perfect.

Dont have system access currently.

Will test this once I get access and update you.

Regards,

Jai Shankar

[Former Member]

Thank you anyway, Jai!

If you or someone else has an idea within the next days, it would be highly appreciated.

I promise to give a note here, if I should happen to find a solution elsewhere.

So while you don't read my hurra here, I'm still desperate...

[Former Member]

Hi Rolf Micus,

My xml structure to insert 2 tables are as follow:-

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_PODB xmlns:ns0="http://abeam.com/xi/fer_filejdbc_scenarios1">

.. <STATEMENT_HEADER>

.... <ROW action="INSERT">

...... <TABLE>ZPO_HEADER</TABLE>

...... <access>

........ <PONUMBER>001</PONUMBER>

........ <PODATE>20070801</PODATE>

........ <POAMOUNT>200.99</POAMOUNT>

.... </access>

.... </ROW>

.. </STATEMENT_HEADER>

.. <STATEMENT_DETAIL>

.... <ROW action="INSERT">

...... <TABLE>ZPODETAIL</TABLE>

...... <access>

........ <PONUMBER>001</PONUMBER>

........ <PONO>1</PONO>

........ <POITEMCODE>A12345</POITEMCODE>

........ <POITEMDESC>Testing A</POITEMDESC>

........ <POITEMAMOUNT>2990.00</POITEMAMOUNT>

........ <POITEMQTY>55</POITEMQTY>

...... </access>

.... </ROW>

.. </STATEMENT_DETAIL>

</ns0:MT_PODB>

Fro the structure that you have declared..there is only contained 1 statement, try to admend your MT to have 2 statements.

If you wish to insert multiple records, just create a loop/multiple of access tag inside STATEMENT tag. For example, multiple records insert into Header should have multiple access tag in Header STATEMENT. Same goes for muliple records insert into Details STATEMENT should have multiple access tag in Details STATEMENT.

With this structure, whenever any records insert/update/delete failed...it will rollback all the transactions together, ie. Header and Details.

PS: For different table, please create different STATEMENT.

Hope it helps.

Message was edited by: Pua Ming Fei

[Former Member]

> PS: For different table, please create different

> STATEMENT.

HURRA! That is the solution!

I have also done the rollback test. In my case, I built in a value too long ('EF') into the HANDSHAKE element.

So the second transaction failed after the first transaction was successful. The result was, that BOTH transactions were rolled back, as was desired.

THANKS! to Pua Ming Fei and Jai Shankar, will donate forum points if possible.(Since I did not open this question)

Regards, Rolf

[Former Member]

Am really glad you got it working.

Many thanks to Pua Ming Fei for the clarification.

Regards,

Jai Shankar

[Former Member]

Hi Rolf Micus / Jai,

Glad it is working..

[udo_martens]

Hi Sebin,

put the update into one statement, that's enough.

Regards,

Udo

[justin_santhanam]

SEBIN,

Please see the below URL for logic.

http://www.flickr.com/photos/8764045@N06/549186611/

http://www.flickr.com/photos/8764045@N06/549186651/

If you want the coding part let me know I will paste the same.

Best regards,

raj.