cancel
Showing results for 
Search instead for 
Did you mean: 

1. How to use transaction to encapsulate all the inserts/updates of an interface,which will prevent partial updates. ? for JDBC reciver

Former Member
0 Kudos

Hi ,


My interface is Proxy to JDBC(MS-SQL)


Sending multiple tables by using single interface.


Now below requirement has been raised by the client,Please help


1. Will you be using transaction to encapsulate all the inserts/updates of an interface. ??


what is transaction in PI.?


regards,

Vinod

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello,

By default, transaction level is with PI which means that in case any update/insert gets failed (for that specific message) then by default PI will rollback the entire transaction.

Thanks

Amit Srivastava

Former Member
0 Kudos

Thanks Amit,

My issue is :

Let’s say an update for one interface consist of the following SQL changes:

  1. Update-insert 50 rows in table A
  2. Update-insert 30 rows in table B
  3. Update-insert 25 rows in table C

If query 2 (table B) fails because of an invalid foreign keys, I do not want any of the 3 tables to be updated.


Is it possible through our PI interface


Please help me on this

regards,

Vin

Former Member
0 Kudos

Hi VIN

Yes it is possible through a PI interface. Just map all tables to one single STATEMENT record.

The JDBC adapter has a built in capability of transaction handling.

Regards,

Akshay

Former Member
0 Kudos

Hi Akshay,

You mean I need to create all the three table fields under one access SEGMENT ..?

Can you please elaborate ....

Regards,

Vin

Former Member
0 Kudos

Hi VIN,

You can create a stored procedure containing all the tables in your database and assign that SP to  the statment structure.

Regards,

Akshay

Former Member
0 Kudos

Hi All

Customer is not ok with stored procedures concept,

Regards,

Vin

Former Member
0 Kudos

Hi VIN

Try this. Create one target message type and 3 data types. Each data type will represent the update of a table.

Include 3 data types in the single message target message type.

Former Member
0 Kudos

Hello,

>>If query 2 (table B) fails because of an invalid foreign keys, I do not want any of the 3 tables to be updated.

I think, yes it's possible. Create below structure in PI and test ur requirement.

BTW, make sure to put StatementName** occ as 0..Unbounded.

<MessageType>

<StatementName1>  ----- 0...Unbounded

<dbTableName action=”Update_Insert”>

<table>Table1</table>

<access>

<col1>val1</col1>

<col2>val2</col2>

</access>

<Key>

…….

</Key>

</dbTableName> 

</ StatementName1>

< StatementName2>

<dbTableName action=”Update_Insert”>

<table>Table2</table>

<access>

<col1>val1</col1>

<col2>val2</col2>

</access>

<Key>

…..

</key>

</dbTableName> 

</ StatementName2>

</MessageType>

Thanks

Amit Srivastava

Answers (1)

Answers (1)

iaki_vila
Active Contributor
0 Kudos

Hi Vin,

Talk with your DB team to do an statement procedure with all insert/update logic encapsulated.

Regards.