JBoss 3.0 Oracle CMT Autocommit

[alanmoor]

I have a connection pool configuration based on oracle-service.xml (all I did was change the local settings). I have a stateless session bean that uses container managed transactions. No other beans or classes use the pool.

The code in the bean:
Context ctx = new InitialContext();
ds = (DataSource)ctx.lookup(dsLookup);
conn = ds.getConnection();

Upon return, the connection has autocommit = true.

What did I miss?

[davidjencks]

jta transactions are separate from local transactions managed by autocommit and commit/rollback on the connection. The autocommit setting you can read from the connnection handle you get from the datasource is not necessarily the same as the autocommit setting on the underlying jdbc connection.

Don't do anything with the autocommit setting in a jta transaction: reading it may confuse you, and trying to set it will result in an exception.

[alanmoor]

Thank you very much for your prompt reply.

I understand what you are saying, but here is my dilemma. I am passing CachedRowSet into the stateless session bean for it to save under a container managed transaction. The first thing the CachedRowSet does is check the autocommit value and set it to false if it is initially true. I have no control over this.

I am left with several options:

1 - figure out a way to "force" the getAutoCommit() method to return false on the Connection, but that will be difficult and, probably, database specific.

2 - Find a configuration option in oracle-service.xml that sets the default mode to autocommit=false.

3 - Use Bean managed transactions, which will involve a lot of extra coding and a very large increase in testing.

4 - Write my RowSetWriterImpl and use it to push data back to the DB.

I would prefer option #2, since it is the path of least resistance. But, if I can't do that, I will explore the other options.


Thank you again, David, for your prompt reply to my question. You are doing a great job on this forum.

Alan

[l.g.]

5. You can get CachedRowSet from somebody else:

-http://www.inetsoftware.de/English/scripts/FrameSet.asp?path=http://www.inetsoftware.de/English/Produkte/OPTA/&name=BenchCachedRowSet.htm
-http://jxutil.sourceforge.net/documentation (Open Source!)
-Does Oracle have CachedRowSet implementation?


What about using EntityBeans to save data? :)

[alanmoor]

Yes, I had already started installing the Oracle implementation when I read your note. Thank you. Just hope it's a little more robust :)

As far as entity beans go, that's not really possible in our software architecture. Wish it were, it sure would simplify things.

Thanks again,
Alan

[l.g.]

Look at http://jxutil.sourceforge.net
This CachedRowSet implementation is also using setAutocommit(...) before and after DML statements, but because this is an OpenSource you can get source code and modified it yourself. I don't think it will be too difficult.
I don't know much about developers of this CachedRowSet implementation, but their PostgreSQL jdbc 3 driver works great!

BTW did you think about JDO?