Setting autoCommit(false)/Rollback()

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.

Todd,
Welcome to JavaRanch.

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

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 ]

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.

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.

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.