How to use transaction with JDBC adatper

, ,
1

Hi all,

we’re developing a flow service to insert some records in one table and some other records in a second table.
For every single SQL insert we call adapter1 for the first table and adapter2 for the second table.
We need to perform all these “insert” in the same transaction but in case of failure of one operation a rollback is not performed. We tried to use pub.art.transaction:startTransaction, pub.art.transaction:commitTransaction and pub.art.transaction:commitTransaction but it didn’t work.

We are using IS on webMethods 8.2.

Can you suggest use how to solve this problem?

Thanks in advance.

Luca

2

Hi Luca,

some things to check:

  • is the JDBC Connection configured with at least local transaction (assuming both tables are in the same db)?
  • is the failure properly recognized, are the commit and rollback in the proepr sections of a try catch?

One addition remark: If you insert several records and it’s an all or nohing, why do you not use BatchInsertSQL?

Regards

Martin

3

Hi Luca,
If you have two adapter services, and both uses two different JDBC connections (with LOCAL transaction type), what you see is expected…

You got to use XA Transaction connections in that case. Either one can be LOCAL, and another can be XA, or both can be XA.

If both adapters uses same JDBC Connection (with LOCAL transaction type), it should work. Do not check by debugging the service, as transactional behavior is not maintained during debug/trace service.

-Senthil

4

Considering using the jdbc adapter to invoke a stored procedure in your DB so you can better handle the commits/rollback

5

Hello

You can use “XA_TRANSACTION” type in this case.In this type of connection, in one transaction boundary, all of the
operations on multiple connections will be committed or rolled back together.

Thanks
Baharul

6

Hello, i have a question about adapters and database:
I have an operation to do on a line in my table in a database, and i wanna be sure that no other application have access to that line before im done with it, like blocking it, is start/commit transaction allow me to do that, or there is another way??
Thank you

7

Hello, i have a question about adapters and database:
I have an operation to do on a line in my table in a database, and i wanna be sure that no other application have access to that line before im done with it, like blocking it, is start/commit transaction allow me to do that, or there is another way??
Thank you

8

Please open new threads for new questions and do no hijack existing ones.