Forums Register Login

Setting autoCommit(false)/Rollback()

+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
Todd,
Welcome to JavaRanch.

What kind of project are you doing this in? EJB? Web? Java?
+Pie Number of slices to send: Send
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 ]
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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.
You have to be odd to be #1 - Seuss. An odd little ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 3177 times.
Similar Threads
BMP stateless session bean can't roll back using JTA
Connection Pool Dirty Connection?
Testing db CRUD calls.
Does a Connection commit on close?
Rollback not working with @Transactional
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 18, 2024 22:54:28.