6 Replies Latest reply on May 27, 2010 11:08 AM by brannic

    Use LastResource Optimization w/ SQL Server JDBC Driver (2.0+)

      We are in the process of migrating our existing JBoss 4.0.4 application to 5.1.0.

       

      Our original application uses jTDS and the LastResource optimization to simulate XA functionality with our Oracle XA datasources.

       

      We would like to try and replace jTDS with the current JDBC Drivers supplied by Microsoft. 

       

      In the past we were able to make this work by building a wrapper class which extended net.sourceforge.jtds.jdbcx.JtdsDataSource which implemented LastResource.

       

       

      package com.system.jdbc.xa;
      import org.jboss.tm.LastResource;
      public class JtdsJBossLastResourceDataSource extends net.sourceforge.jtds.jdbcx.JtdsDataSource implements LastResource 
      {
      }
      
      
      

       

      We have been trying to duplicate that with the JDBC Driver provided by Microsoft (both rev 2.0 and 3.0) but we have not had any luck.

       

      The JtdsDataSource class implements both a regular as well as a XA datasource.  We have tried replacing the code above with either.

       

       

      public class MsftJBossLastResourceDataSource extends com.microsoft.sqlserver.jdbc.SQLServerDataSource implements LastResource 
      
      
      

       

      When we try the example above, the code compiles but when the server is started we receive an error saying that it cannot cast to a java.sql.XADataSource.

       

      public class MsftJBossLastResourceDataSource extends com.microsoft.sqlserver.jdbc.SQLServerXADataSource implements LastResource 
       
      

       

      However, when we try to compile this, we can't since Microsoft has made this class FINAL can thus is cannot be extended.

       

      Has anyone had any luck with implementing the LastResource optimization with the Microsoft JDBC driver (rev 2.0 or higher)?

       

      Thanks in advance for your help with this.

        • 1. Re: Use LastResource Optimization w/ SQL Server JDBC Driver (2.0+)
          jhalliday

          Stop making life difficult for yourself and use a -ds.xml file like everyone else.

          • 2. Re: Use LastResource Optimization w/ SQL Server JDBC Driver (2.0+)

            Jonathan,

             

            Believe me I'd like to avoid doing this the hard way if I can.  But we actually are defining our connections using -ds.xml files. 

             

            For our Oracle connections we are using an oracle-xa-ds.xml file to define our Oracle XA connections.

             

            For our SQL Server connections we are using a sql-ds.xml file which references the JtdsJBossLastResourceDataSource class specified in my first code example to connect to an outbound system.  We are trying to see if we can use the same method to switch from jTDS to the Microsoft SQL Server driver to maintain the same level of functionality.

             

            If you like I can post them up here, but to me they seem pretty generic.  The only thing which would be changing from most installs is the <driver-class> element.

             

            Thanks for looking at this.

             

            --Rob

            • 3. Re: Use LastResource Optimization w/ SQL Server JDBC Driver (2.0+)
              jhalliday

              Can you explain why using the new MS JDBC driver with a straightforward <local-tx-datasource> and none of this mucking around fails to provide the functionality you need?

              • 4. Re: Use LastResource Optimization w/ SQL Server JDBC Driver (2.0+)

                Jonathan,

                 

                The reason we can't use a regular local-tx-datasource is that we need to ensure that transactions to the Oracle system pass a pre-commit before we commit them to the SQL Server system.  If a problem is detected in the Oracle transactions the entire thing is rolled back. 

                 

                Ideally we could use XA datasources for both the Oracle and the SQL Server databases, but we cannot guarantee that we will have the ability to have the XA components installed on the SQL Server.  That's why we are trying to use the Last Resource Optimization strategy.

                 

                The solution outlined above is currently working with the jTDS driver.  However, I have been instructed to replace jTDS with the Microsoft JDBC Driver as a part of moving to JBoss 5.1.0.  Right now I am trying to determine if people have been able to successfully implement the Last Resource optimization using this driver or see if it is even possible.

                 

                Thanks,

                 

                --Rob

                • 5. Re: Use LastResource Optimization w/ SQL Server JDBC Driver (2.0+)
                  jhalliday

                  > Right now I am trying to determine if people have been able to  successfully implement the Last Resource optimization using this driver  or see if it is even possible.

                   

                  It's possible with any driver. <local-tx-datasource> does it automatically. Stop making your life harder than it needs to be.

                   

                  "Where a single <local-tx-datasource> is used in a transaction,  the LRCO will be automatically applied to it"

                  http://www.redhat.com/docs/en-US/JBoss_Enterprise_Application_Platform/5.0.1/html-single/Administration_And_Configuration_Guide/index.html#id2455794

                  • 6. Re: Use LastResource Optimization w/ SQL Server JDBC Driver (2.0+)

                    Jonathan,

                     

                    Thank you.  I was unaware that using a local-tx-datasource automatically initiated the Last Resource Optimization strategy.

                     

                    I will give this a try and test it out.

                     

                    Thanks again for your assistance.

                     

                    --Rob