Saving data into two tables "in one transaction"

[Former Member]

Hello everybody!

My scenario is the following: SAP PI receives message containing some data (header and MULTIPLE details data in one message) and has to transfere this data to a database.

In the database there are two tables for storing the data contained in the message: one for HEADER data and one for DETAILS data. I need to save all the data (header and MULTIPLE details data) into these two tables "in one transaction" and roll back saving of HEADER data and DETAILS data if saving of some DETAILS data has failed.

I am going to use receiver JDBC adapter for this purpose.

My questions are:

1. How can I save all the data "in one transaction"? Will it work if I transform the source message into the target message which looks something like this?

<root>

<StatementName1>

<dbTableName action=u201CUPDATE_INSERTu201D>

<table>HEADER_table</table>

<access>

<col1>val1</col1>

<col2>val2</col2>

</access>

<key1>

<col1>val1</col1>

</key1>

</dbTableName>

</StatementName1>

<StatementName2>

<dbTableName action=u201CUPDATE_INSERTu201D>

<table>DETAILS_table</table>

<access>

<col1>val1</col1>

<col2>val3</col2>

</access>

<key1>

<col1>val1</col1>

</key1>

</dbTableName>

</StatementName2>

<StatementName3>

<dbTableName action=u201CUPDATE_INSERTu201D>

<table>DETAILS_table</table>

<access>

<col1>val1</col1>

<col2>val4</col2>

</access>

<key1>

<col1>val1</col1>

</key1>

</dbTableName>

</StatementName3>

</root>

2. How to roll back saving of HEADER data in the case of saving of DETAILS data has failed?

3. What will be the response structure returned by database look like in the case of success?

Thanks,

Vika

[former_member206760 · Accepted solution]

Dear Viktoria,

As specified by you you can use 2 statement nodes....

one for header and other for detail

as far as i know both of them would be executed in one transaction only

[Former Member · Accepted solution]

As specified by you you can use 2 statement nodes....

Valuable note. Thanks!

And what about the response structure? Do you know how it should look in my case?

[former_member206760 · Accepted solution]

yes...

if you are using statement1 and statement2 then they should be like

statement1_response

+row

++field1

++field2

and same for statement2

statement2_response

+row

++field1

++field2

Giving points is another way to say thanks

[Former Member · Accepted solution]

I guess the response in my case will be something as following?

<response>

<Statement1_response>

<row>

<update_count>1</update_count>

<insert_count>0</insert_count>

</row>

</Statement1_response>

<Statement2_response>

<row>

<update_count>0</update_count>

<insert_count>1</insert_count>

</row>

<row>

<update_count>0</update_count>

<insert_count>1</insert_count>

</row>

</Statement2_response>

</response>

Vika

[Former Member]

Hi,

I think this is okay. As long as the tables belong to the same DB you should be able to process like this. To ensure the rollback is executed propperly you should place the commit at the end.

Regards,

Kai

[Former Member]

Hi, Kai!

Could you, please, explain how to place the commit and why I need to do this in the case of rollback?

Thanks,

Vika

[Former Member]

As far as I see, your code should be able to provide the option.

You can also manage Rollback / Commit in stored procedures in an exception handling block. Just catch the error and write conditions for rollback.

Hope this helps!

Kai

[Shabarish_Nair]

use the option Database u2018Auto-Commitu2019 Enabled (No Transaction Handling)

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

[Former Member]

Kai,

When I need to send commit (in the case of success or failure)?

And how can it be done? Is it some kind of message which has to be send after the main message (containing data) is send? I'm not using stored procedures.

Vika

[Former Member]

Hi, Vijayakumar!

As I understand I have to use this option to save data into two tables "in one transaction" only if JDBC Driver do not support transactions. So if JDBC driver supports transactions I only need to send the message described in my first post. Am I right?

Vika

[Shabarish_Nair]

yes you are !!

[Former Member]

If you don't use SP it's not relevant. Just do as supposed in last post - this will work ;o)