5 Replies Latest reply on Oct 5, 2005 4:14 PM by Ken Sell

    Trouble enlisting direct JDBC in a transaction

    Bob Smith Newbie

      Hello,

      I am getting a ClassCastException when doing the following:

      XADataSource m_XADataSource = (XADataSource) ctx.lookup("java:/MyDataSource");
      


      My problem is no doubt not just the above line but my whole understanding of the area of Transactions.

      I am replacing the entity beans in our application with JDBC and I am considering the following situation:

      SessionBean1.method1 {Transaction="RequiresNew"}
      create entity (CMP)
      SessionBean2.method2 {Transaction="Supports"}

      I think I'm right in saying that if SessionBean2.method2 throws a system exception then SessionBean1.method1 will rollback and the entity I created will not be comitted to the database.

      If I replace the entity creation with JDBC code, then my thinking is that, unless I do something clever, the entity will be comitted to the database even if SessionBean2.method2 throws a system exception. So my plan of attack was to, in my JDBC code, check if there is a current transaction and, if there is, join in.

      So, am I barking up the wrong tree completely, or is this a sensible approach? [please don't refer to Hibernate, Spring JDBC, or any other framework - I need to know if it is an OK approach for JDBC].

      The code I have put in to achive my goal is as follows, but I get no further than the line marked "***** FAILS HERE" - the first line!! :

      XADataSource m_XADataSource = (XADataSource) ctx.lookup("java:/MyDataSource"); *****FAILS HERE with ClassCastException
      TransactionManager txManager = (TransactionManager)context.lookup("java:/TransactionManager");
      Transaction tx = txManager.getTransaction();
      XAConnection connection = m_XADataSource.getXAConnection();
      if (tx != null)
      {
       XAResource xares = connection.getXAResource();
       tx.enlistResource(xares);
      }
      PreparedStatement ps = connection.getConnection().prepareStatement("some sql");
      


      The mysql-ds.xml file I am using is:
      <datasources>
       <local-tx-datasource>
       <jndi-name>MyDataSource</jndi-name>
       <connection-url>jdbc:mysql://localhost:3306/testdb</connection-url>
       <driver-class>com.mysql.jdbc.Driver</driver-class>
       <user-name>theuser</user-name>
       <password>thepassword</password>
       <prepared-statement-cache-size>50</prepared-statement-cache-size>
       </local-tx-datasource>
      </datasources>
      


      and I'm using versions 3.2.6 of JBoss and 3.1.6 of MySQL Connector-J.

      Many thanks,
      Paul

        • 1. Re: Trouble enlisting direct JDBC in a transaction
          Ashwin Kumar Karkala Newbie

          Your class cast exception should be easy to solve. You are setting up a regular datasource and trying to cast it to a XADataSource. In your mysql-ds.xml you need to setup a xa datasource.


          <xa-datasource>
          <jndi-name>MySqlDS</jndi-name>
          <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
          <xa-datasource-property name="URL">jdbc:mysql://localhost:3306/test</xa-datasource-property>
          <user-name></user-name>
          <password></password>
          <track-connection-by-tx>true</track-connection-by-tx>
          <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
          <metadata>
          <type-mapping>mySQL</type-mapping>
          </metadata>
          </xa-datasource>


          • 2. Re: Trouble enlisting direct JDBC in a transaction
            Ashwin Kumar Karkala Newbie

            If you want to manage transactions yourself (not a good idea most times) then lookup UserTransaction object from JNDI and use that. Dont use the transaction manager. It cant help you :)

            • 3. Re: Trouble enlisting direct JDBC in a transaction
              Bob Smith Newbie

              Thank you for your reply, niwhsa.

              It appears that the MysqlXADataSource class was removed from Connector/J version 3.0 onwards because "it didn't work well". Bug id is 1737 and can be found here: http://bugs.mysql.com/bug.php?id=1737

              The bug report says that JBoss provides an XA wrapper for non-XA databases, but I have searched JBoss and can find no mention of such an implementation. Anyone out there know what class JBoss provides?

              Also, I am very interested in your comment that I should be using the UserTransaction object rather than the transaction manager. Could you possibly expand on that a little?

              Many thanks.

              • 4. Re: Trouble enlisting direct JDBC in a transaction
                Bob Smith Newbie

                 


                If you want to manage transactions yourself (not a good idea most times) then lookup UserTransaction object from JNDI and use that. Dont use the transaction manager. It cant help you :)

                I can get hold of the UserTransaction via JNDI as you mention, but is that any good to me? I want the SQL I am about to execute to be regarded as part of that same transaction that I just got hold of, but I doubt I can do that using the UserTransaction. That's why I was thinking I need to join in (using the XA code I posted) the existing transaction.

                I've had another search for this "XA Wrapper for non-XA databases" but I can't find anything. I've found someone else asking the same question in another post, only to be told to "read the xxx FAQ" by Adrian at JBoss. Well I've read the FAQ and it doesn't help!

                • 5. Re: Trouble enlisting direct JDBC in a transaction
                  Ken Sell Newbie

                  I also need to connect to a non-XA data source,
                  but I don't want to use the DriverManager to
                  make the connection. I want to use the DataSource
                  class supplied by the data base vendor and get the
                  pooling, etc. provided by a data source.

                  I've also looked around on the web for a solution,
                  but haven't found any.