disabling autocommit for Oracle (non-XA) datasource

[kshuk]

Hello,

I'm using jboss 3.0.4 and I'm wondering if there's a way to disable autocommit in a connection pool configuration based on the oracle-service.xml example?

The situation is that I've got a select...for update, and executing this throws a SQLException because Oracle generated an "ORA-01002: fetch out of sequence" error. Looking this up on metalink, I see that I need to disable autocommit. Frankly, I didn't even realize it was even and I don't want autocommit on - it wasn't on when the app was running under JBoss 2.4.4.

I've found some information in the QuickStart guide, Chapter 10 written by David Jencks. The bit about which Connection Manager to use seems related - I'm using the LocalTxConnectionManager and not the NoTxConnection Manager.

Do I have to now call the setAutoCommit method of the connection object to turn it off every time I get a connection? Again, this wasn't an issue with connection pools under 2.4.4.

Do I have to use the Oracle XA configuration instead? I hope not because I've been there and it opens a whole 'nother can of (strange) worms.

Thanks in advance

[dward2]

How did you resolve this? Thanks.

[davidjencks]

Using jta transactions, and local or xa jca-jdbc wrappers, autocommit is in fact off whenever you are in a jta transaction. Most versions of jboss do not report this correctly from "getAutocommit" since I didn't read the spec very carefully.

The jca spec indicates that autocommit behavior is required when you are outside a managed transaction and don't explicitly control a app-managed local transaction. However, I'm starting to think perhaps we should include a configuration flag for this, since the jca spec is really talking only about cci connection factories rather than jdbc DataSources. Any other thoughts?

[dward2]

Actually, I don't think it is off. My proof is not in what gets returned by "getAutocommit," but the fact that *Oracle* complains that it's not off when I'm doing certain LOB operations.