aspose file tools*
The moose likes JDBC and the fly likes Setting autoCommit(false)/Rollback() Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Setting autoCommit(false)/Rollback()" Watch "Setting autoCommit(false)/Rollback()" New topic
Author

Setting autoCommit(false)/Rollback()

Todd Cashman
Greenhorn

Joined: Jan 24, 2005
Posts: 3
I am using WSAD 5.0 and Oracle 9i configured to use oracle.jdbc.pool.OracleConnectionPoolDataSource. I would like to setAutoCommit(false) and then perform a rollback if an exception is thrown. Problem is as soon as I set autoCommit(false) I get an exception saying I can't do that on a global transaction. In my ConnectionManager Java class I use the InitialContext to lookup the datasource and get a connection as follows:

javax.naming.InitialContext ctx = new javax.naming.InitialContext();
ds = (javax.sql.DataSource) ctx.lookup("jdbc/myDs");
return ds.getConnection();

When I change this code to use the DriverManager to retrieve the DS it works. However, I would like to use the connection pool. Does anyone know how I can make this happen? It doesn't seem like this should be difficult yet I can't seem to find any information on this problem. Thanks in advance.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30758
    
156

Todd,
Welcome to JavaRanch.

What kind of project are you doing this in? EJB? Web? Java?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Todd Cashman
Greenhorn

Joined: Jan 24, 2005
Posts: 3
Thanks Jeanne. It is in an EJB project. I use the Connection Manager to get connections in a DAO class containing SQL statements which is used by stateless Session EJBs. But the problem clearly is with the connection. I have a suspicion that the Connection Pool needs to be configured but am unsure how to go about doing that. Any ideas?
[ January 25, 2005: Message edited by: Todd Cashman ]
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
There should be a way in your container to configure the connection pool to create connections with autocommit set to false. I highly recommend that you code your DAOs to always do commit or rollback instead of some using autocommit and others using commit/rollback.
Todd Cashman
Greenhorn

Joined: Jan 24, 2005
Posts: 3
Thanks David,

I think there must be a way for me to configure the Connection Pool so that the connection is never in the global state because that is what is preventing me from setting con.setAutoCommit(false) etc...I just don't know how to do it. As for setting autoCommit() on some connections and not on others, that was never my intention. I just had the SQL statements submiting/updating with autoCommit(true) and I now want a more elegant handling of exceptions by doing the rollback if something goes wrong on any connection.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30758
    
156

Todd,
I actually think it is an EJB transaction setting causing the error. Try setting your EJB to the "requires new" transaction value. That gives each EJB call it's own transaction. In other words, you won't be in the global transaction anymore.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Setting autoCommit(false)/Rollback()