• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Setting autoCommit(false)/Rollback()

 
Todd Cashman
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34974
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Todd,
Welcome to JavaRanch.

What kind of project are you doing this in? EJB? Web? Java?
 
Todd Cashman
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34974
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic