2 Replies Latest reply on Aug 8, 2003 2:34 AM by kristiane

    CachedRowSet and Oracle JDBC datasource

    kristiane Newbie

      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

        • 1. Re: CachedRowSet and Oracle JDBC datasource
          Adrian Brock Master

          Post the full stacktrace.

          My guess is MySQL doesn't trap the error.

          Regards,
          Adrian

          • 2. Re: CachedRowSet and Oracle JDBC datasource
            kristiane Newbie

            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