local-tx-datasource and autocommit

[jobo]

Hi,

In some parts of my code I need to handle the transactions manually.

Dont pay too much attention to the actual example, just want to show the flow.

This is an example:

InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:qpDS");
Connection conn = ds.getConnection();
Savepoint sp = conn.setSavepoint();
Statement stmnt = conn.createStatement();
stmnt.execute("insert into someTable (id, name) values(99, 'Text..')");
// Do something critacal that might fail here...
if(failed) {
 conn.rollback(sp);
}
stmnt.close();
conn.close();

Ofcourse this will fail if my connections autocommit property is true - which will be the case when using a local-tx-datasource.

I know I can use no-tx-datasource to get it work, but other parts of the application requires local-tx-datasource.

What do you suggest? Should I use two diffrent datasources? one local-tx-datasource and one no-tx-datasource?

Doesn't sound like a good solution to me though.

[mikevmoran]

Jobo,

I cannot help you on this post but am looking for information on setting autocommit.

I'm using the mysql jdbc driver with a local-tx-datasource, Hibernate and cannot turn off auto commit by calling connection.setAutocommit(false). My rollbacks fail because you cannot rollback with autocommit set to true.

If I issue the command SET AUTOCOMMIT=0 at the command line it works just fine.

any help is greatly appreciated.