CachedRowSet and Oracle JDBC datasource

[kristiane]

Hi!

I am using CachedRowSet in a Session Bean to read and write data to a datasource. I am using JBoss 3.2.1 server and the Oracle JDBC driver is 9.x. CachedRowSet is from the sun.jdbc.rowset package and not from Oracle.

Writing:
I am writing the modified data using the
data.acceptChanges(m_connection); // data is the CachedRowSet instance

The JBoss server throws the following exception:
ERROR [STDERR] No access to MesImn data: Updates failed: You cannot commit during a managed transaction!
10:09:32,800 WARN [WrappedConnection] Closing a statement you left open, please do your own housekeeping
10:09:32,908 INFO [CachedConnectionManager] Successfully closed a connection for you. Please close them yourself: org.jboss.resource.adapter.jdbc.WrappedConnection@64cc4d

Anyboby have any ideas, as to why this is happening?!?

My theory is that the CachedRowSet class tries to commit in code, in the middle of a transaction controlled by the EJB container... Which leads to the next question; How do one get around this? I don't have the source code to CachedRowSet and therefore can't modify the code.

The funny thing is that using a MySQL JDBC driver (towards a MySQL replica database), my code works!!! Writing to datasource is no problem!


The Oracle datasource configuration:
I have basically just configured a oracle-ds.xml. It looks like this:

<local-tx-datasource>
<jndi-name>MBDS</jndi-name>
<connection-url>jdbc:oracle:thin:@137.133.121.216:1521:V9</connection-url>
<driver-class>oracle.jdbc.OracleDriver</driver-class>
<user-name>mmsmb</user-name>
xxx
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>



Reading:
The reading actually works, but with warnings using the Oracle datasource.
I am reading from the datasource using the
data.execute(m_connection); // data is the CachedRowSet instance

The JBoss server prints the following warning:
WARN [TxConnectionManager$LocalXAResource] Prepare called on a local tx. Use of local transactions on a jta transaction with more than one branch may result in inconsistent data in some cases of failure.


I will be grateful for any comments, hints or ideas...
ThanX,

- Kristian

[adrian.brock]

Post the full stacktrace.

My guess is MySQL doesn't trap the error.

Regards,
Adrian

[kristiane]

I have investigated a little further.

The problem is as follows:
CachedRowSet uses internally transaction management. E.g. it calls commit() or rollback() internally. When using CMT, the container controls transactions (commit/rollback), and will not allow the code to call commit() inside it's controlled method.

In my ejb-jar.xml file, the bean was set to use CMT;
<transaction-type>Container</transaction-type>

When calling a method on this EJB, as decribed below, the EJB will throw an exception.

Setting the EJB to use BMT, instead works!
<transaction-type>Bean</transaction-type>

Ok, so far so good. But the following questions remains:
- Is there no way to use CachedRowSet with CMT ?
- Using BMT, isn't is a little dirty to let the CachedRowSet call Connection.commit() directly, instead of using the UserTransaction.commit() ? E.g using the Connection interface, instead of the UserTransaction interface ?

I will be grateful for any comments, hints or ideas...
ThanX,

- Kristian