wich is the best practice to do SELECT INSERT UPDATE process between two flow services

, ,
1

Hi

i’m having a doubt in a case a flow need to SELECT INSERT UPDATE on two table

example
FLOW A
SELECT TableA
FLOW B
INSERT TableB
UPDATE in Table A.

should i used pub.art.transaction:startTransaction, pub.art.transaction:commit and pub.art.transaction:rollbacktransaction?

or does anybody recommends me a article that mention something about it i will appreciate.

Regards,
Erick Rodriguez

2

using JDBCAdapter ?

You can find documentation on the WmDB here :
http://techcommunity.softwareag.com/ecosystem/documentation/webmethods/wmsuites/wmsuite9-8/Integration_Server/9-8_WmDB_Users_Guide.pdf
(this is for version 9.8 )

WmJDBC :
http://techcommunity.softwareag.com/ecosystem/documentation/webmethods/adapters_estandards/Adapters/JDBC/JDBC_9-10/9-10_Adapter_for_JDBC_Install_and_Users_Guide.pdf
(this is for version 9.10 )

with JDBC, the idea is :
if your business requirement is to have rollback functionality, you setup your JDBC to Transaction type to : LOCAL_TRANSACTION
then in your flow service:
pub.art.transaction:startTransaction,
Execute your select/insert/update
and then
pub.art.transaction:commit or pub.art.transaction:rollbacktransaction

if you don’t need/want to manage commit/rollback, just use a JDBC with Transaction Type NO_TRANSACTION
then just execute your select/insert/update (it will auto commit)

Hope this helps

3

Hi Eric,

whenever possible I prefer JDBC Adapter over WmDB package.

Currently we do not have the neccessity to use WmDB, all our JDBC Services are using JDBC Adapter.

When Table A and Table B are in the same schema/database, you can use the approach Jonathan has described.

When they are in different databases you will have to use XATransactions instead.

Select does not require explicit transaction handling.

For Insert and Update this depends what should happen with the inset to table B when the Update on tanle A fails?
For the case that the Insert to table B should be reverted you will require explicit transaction handling.

Regards,
Holger