8 Replies Latest reply: Apr 13, 2012 1:03 PM by David Rollins RSS

AS 7 datasource using MSSQL (SQL Server)

David Rollins Newbie

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 Master

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

  • 2. Re: AS 7 datasource using MSSQL (SQL Server)
    David Rollins Newbie

    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)
    Stefano Maestri Expert

    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.

  • 5. Re: AS 7 datasource using MSSQL (SQL Server)
    David Rollins Newbie

    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)
    Stefano Maestri Expert

    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)
    Rasmus Moustgaard Newbie

    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)
    David Rollins Newbie

    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.