SQLTransformer and Transactions

[Daniel Fagerstrom]

I'd like to use transactions in the SQLTransformer and tried something like:

...
<execute-query>
<query>
BEGIN;
</query>
</execute-query>
<execute-query>
<query>
Do something
</query>
</execute-query>
<execute-query>
<query>
Do something that is based on the previous query
</query>
</execute-query>
<execute-query>
<query>
COMMIT;
</query>
</execute-query>
...

This does not work in the current implementation of the SQLTransformer as it gets a new connection from the pool for each execute-query and all statements within one transactions must be run from the same connection. Even worse, as the pooled connections can be reused from another pipeline, the same transaction can continue in a completely unexpected place for another user. So it might work as expected for a single user but in unexpected ways for multiple users.

One way to solve this would be to use the same connection for all execute-query in an input xml document. It would still be necessary to open new connections for embedded queries, using the ancestor functionality.

Another solution would be to introduce a transaction tag and letting all queries within it use the same connection and let the start tag execute "connection.setAutoCommit(false)" and the end tag execute "connection.commit()". Embedded queries must of course still have own connections.

Booth solutions requires some rewriting of the connection handling code in the SQLTransformer.

As I need transactions in the application I currently work on, I have to modify the SQLTransformer to handle it. Is there any problems with the above proposed methods? Which one do you prefer? Are there better methods?

/Daniel Fagerstrom


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, email: [EMAIL PROTECTED]