Skip to Content
0
Former Member
Mar 18, 2009 at 10:36 AM

Saving data into two tables "in one transaction"

176 Views

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