How do we maintain Atomicity across many query templates executed in a tran

[Former Member]

Hi,

How do we maintain atomicity across many query templates executed in a transaction?

For example, I have a xMII transaction called "Foo.trx". This transaction calls SQL QueryTemplates "Q1" "Q2", ..... "Qn". My requirement is to have all query templates executed successfully or none of them commited in the database.

Thanks,

Sara

[Former Member]

....short answer, is that you can't. A few options:

1) Use a predefined stored procedure (obviously not desirable from a maintenance standpoint)

2) Dynamically generate the stored procedure (essentially build up the SQL via a series of actions), and execute it as a single SQL statement

3) Create your own custom actions that would

a) An action to obtain database connection with "autocommit" off

b) An action to execute a query that would be "linked" to an "OpenConnection" action (you could call any number of these actions)

c) An action to commit the transaction(s) and close/release the connection

d) An action to rollback the transaction(s) and close/release the connection

This would be logically similar to the way the JCO calls work.

Rick

[Former Member]

Hi Rick,

Thanks for your quick response as usual.

Do you know why this feature is not available in xMII, when it is easy to write custom action block to maintain the atomicity between many query calls?

Thanks,

Sara

[Former Member]

Hi

We find the same problem with sql transacctions and we have delevoped some custom actions block to connect to SQL SERVER, begin a sql transactions, commit and roolback that works similar JCO action blocks.

If you are interested in I can explain it a little bit more.

Regards

[agentry_src]

Gerardo,

Please start a new message and place a link to this one if want to reference this one.

Thanks,

Mike