8 Replies Latest reply on Apr 13, 2012 1:03 PM by drollins

    AS 7 datasource using MSSQL (SQL Server)

    drollins

      I am trying to move from JBoss 6 to JBoss 7 and am running into a few difficulties.   At the moment I'm stopped with the datasource.

       

       

      I have added the following to

       

      standalone\configuration\standalone.xml

       

       

      <datasource jndi-name="java:jdbc/myDB" enabled="true" use-java-context="true" pool-name="myDB">
                               <connection-url>jdbc:jtds:sqlserver://SERVER_NAME:1433/DB_NAME;loginTimeout=30</connection-url>
                               <driver>JTDS</driver>
                               <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
                               <new-connection-sql>select 1</new-connection-sql>
                               <security>
                                   <user-name>USER_NAME</user-name>
                                   <password>USER_PASSWORD</password>
                               </security>
                               <validation>
                                   <check-valid-connection-sql>select 1</check-valid-connection-sql>
                               </validation>
                               <timeout>
                                   <blocking-timeout-millis>5000</blocking-timeout-millis>
                                   <idle-timeout-minutes>15</idle-timeout-minutes>
                                   <set-tx-query-timeout/>
                          </timeout>
                               <pool>
                                   <min-pool-size>5</min-pool-size>
                                   <max-pool-size>50</max-pool-size>
                               </pool>
                               <statement>
                                   <track-statements>false</track-statements>
                               </statement>
                         </datasource>
      
      

       

       

      and to the drivers section I've added

       

       

      <driver name="JTDS" module="net.sourceforge.jtds">
                 <driver-class>net.sourceforge.jtds.jdbcx.JtdsDataSource</driver-class>
      </driver>
      

       

       

       

       

      When I deploy I get the following error:

       

       

      13:23:05,789 INFO  [org.jboss.as.controller] (Controller Boot Thread) Service status report

         New missing/unsatisfied dependencies:

            service jboss.jdbc-driver.JTDS (missing)

       

       

      I added the following module.xml

       

      net\sourceforge\jtds

       

       

      <?xml version="1.0" encoding="UTF-8"?>
      
      
      <module xmlns="urn:jboss:module:1.0" name="net.sourceforge.jtds">
        <resources>
          <resource-root path="jtds-1.2.5.jar"/>
              <!-- Insert resources here -->
        </resources>
        <dependencies>
          <module name="javax.api"/>
          <module name="javax.transaction.api"/>
        </dependencies>
      </module>
      
      
      
        • 1. Re: AS 7 datasource using MSSQL (SQL Server)
          jesper.pedersen

          Try: java:jdbc/myDB => java:/jdbc/myDB or java:jboss/jdbc/myDB

          • 2. Re: AS 7 datasource using MSSQL (SQL Server)
            drollins

            Hi Jesper,

             

                 Thank you for your advice.  I did try both of these options and continued to receive the same error message.

             

            I decided to try removing the <driver></driver> option and specify the <driver-class/> instead to see if that might bypass the problem.

             

            Instead I receive this message

             

             

            15:42:47,602 ERROR [org.jboss.as.controller] (Controller Boot Thread) Operation ("add") failed - address: ([

                ("subsystem" => "datasources"),

                ("data-source" => "java:jboss/datasources/myDB")

            ]): java.util.NoSuchElementException: No child 'driver-name' exists

                      at org.jboss.dmr.ModelValue.requireChild(ModelValue.java:362)

                      at org.jboss.dmr.ObjectModelValue.requireChild(ObjectModelValue.java:298)

                      at org.jboss.dmr.ModelNode.require(ModelNode.java:703)

             

             

            where driver-name is not defined in the XSD.  If I try to add "driver-name" it I get a parse error. 

            • 3. Re: AS 7 datasource using MSSQL (SQL Server)
              maeste

              The correct connfiguration was the first one, but you have put wrong class name into driver/driver-class. You have to put there the vendor class implementing java.sqlDriver interface that for JTS is net.sourceforge.jtds.jdbc.Driver

               

              regarding the error of last post, the problem is that you haven't specified driver tag into datasource that is mandatory. driver-name is the attribute variable mapping into the model this tag. I agree with you that in this case the error is misleading. I'll have a look if it is possible to have an exception easier to read and understand.

               

              Thanks for the interest and your report

               

              have fun

              S.

              • 4. Re: AS 7 datasource using MSSQL (SQL Server)
                maeste
                • 5. Re: AS 7 datasource using MSSQL (SQL Server)
                  drollins

                  Stefano,

                      Thank you for your insights.  I tried changing the JDBC driver as you recommended, but discovered that it still does not work.  I changed the driver info on the JTDS to be XA-datasource and that still failed.

                   

                  I did discover that the JTDS module has not been able to create the .index file which leads me to believe it may be a problem with the JAR itself, but I have not identified exaclty what it is.

                   

                   

                   

                  I have found a work around at least for now.

                   

                  Instead of the JTDS driver I can change my connection string to

                   

                  jdbc:sqlserver://

                   

                  and use

                   

                  <driver name="MSSQLXA" module="com.microsoft.sqlserver">

                                                                              <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>

                                                                    </driver>

                   

                  Module:

                  com\microsoft\sqlserver\main

                   

                  module.xml

                   

                  <?xml version="1.0" encoding="UTF-8"?>

                   

                  <module xmlns="urn:jboss:module:1.0" name="com.microsoft.sqlserver">

                    <resources>

                      <resource-root path="sqljdbc4.jar"/>

                          <!-- Insert resources here -->

                    </resources>

                    <dependencies>

                      <module name="javax.api"/>

                      <module name="javax.transaction.api"/>

                    </dependencies>

                  </module>

                  • 6. Re: AS 7 datasource using MSSQL (SQL Server)
                    maeste

                    Hi,

                     

                    Happy you find a workaround. More properly you are using another driver which work out of the box with your setting since it is jdbc 4 compliant. Let me explain better why JTDS is not working with your config.

                    We have 2 possible case for jdbc driver:

                    • jdbc 4 compliant drivers. In a nutshell they have (according to jdbc 4 spec) a file specifying the name of vendor class implementing Driver. There is a metadata info about that in jar's META-INF. This is the kind of driver you can use with hot deploy because we are able to get autotically the Driver class name. In the same way you can deploy it as module without specifying the driver/driver-class element in standalone xml.
                    • jdbc 3 or earlier version. You have to deploy it as module and you have to specify driver-class in standalone.xml driver element. The correct config in your case should be:

                    <driver name="JTDS" module="net.sourceforge.jtds">

                               <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class> 
                               <xa-datasource-class>net.sourceforge.jtds.jdbcx.JtdsDataSource</xa-datasource-class>
                    </driver>

                     

                    Needless to say that JTDS is NON jdbc4 compliant, while sqljdbc4.jar is jdbc4 compliant as the name says.

                     

                    Hoping it helps to give you a clear idea of what is happening. BTW have you taken a look to http://www.javalinux.it/wordpress/2011/07/14/how-to-create-an-manage-datasources-in-as7/ too?

                     

                    regards

                    S.

                    • 7. Re: AS 7 datasource using MSSQL (SQL Server)
                      rm1

                      My guess it that the base problem was that the jtds module.xml should be under:

                      'net\sourceforge\jtds\main' instead of just 'net\sourceforge\jtds'.

                      Rasmus

                      • 8. Re: AS 7 datasource using MSSQL (SQL Server)
                        drollins

                        I finally got back to working on this.  I did find a corner case where MSSQL driver had issues with certain stored procedures. 

                         

                        Rasmus was correct in his assessment that I had the jar and module.xml in the wrong place.

                         

                         

                        Final result:

                         

                        modules\net\sourceforge\jtds\main

                             module.xml

                              jtds-1.2.5.jar

                         

                         

                         

                        Module.xml

                        ----------------------------------------

                        <?xml version="1.0" encoding="UTF-8"?>

                        <module xmlns="urn:jboss:module:1.0" name="net.sourceforge.jtds">

                          <resources>

                            <resource-root path="jtds-1.2.5.jar"/>

                                <!-- Insert resources here -->

                          </resources>

                          <dependencies>

                            <module name="javax.api"/>

                            <module name="javax.transaction.api"/>

                          </dependencies>

                        </module>

                         

                         

                         

                        in Standalone.xml

                        -------------------------------------

                        <datasource jndi-name="java:jboss/datasources/myDB" pool-name="myDB" enabled="true" use-java-context="true">

                                            <connection-url>jdbc:jtds:sqlserver://SERVER_NAME:1433/DATABASE_NAME;loginTimeout=30</connection-url>

                                            <driver>JTDS</driver>

                                            <new-connection-sql>select 1</new-connection-sql>

                                            <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

                                            <pool>

                                                <min-pool-size>5</min-pool-size>

                                                <max-pool-size>50</max-pool-size>

                                            </pool>

                                            <security>

                                                <user-name>USER_NAME</user-name>

                                                <password>USER_PASSWORD</password>

                                            </security>

                                            <validation>

                                                <check-valid-connection-sql>select 1</check-valid-connection-sql>

                                            </validation>

                                            <timeout>

                                                <set-tx-query-timeout>true</set-tx-query-timeout>

                                                <blocking-timeout-millis>5000</blocking-timeout-millis>

                                                <idle-timeout-minutes>15</idle-timeout-minutes>

                                            </timeout>

                                            <statement>

                                                <track-statements>false</track-statements>

                                            </statement>

                                        </datasource>

                                      

                                        <drivers>

                                            <driver name="JTDS" module="net.sourceforge.jtds">

                                                <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>

                                            </driver>

                                            ....

                                         </drivers>

                         

                         

                         

                         

                        In my persistence.xml

                        <jta-data-source>java:jboss/datasources/myDB</jta-data-source>

                         

                         

                        In the process I also switched to JBoss AS 7.1.0 Final.  I don't know if that made any difference or not.