SetUpAMSSQLDatasource

Version 31

     

    To evaluate those drivers, you can use a simple JSP page to query the pubs database shipped with MSSQL Server.

     

     

     

    Drop the attached WAR archive in /deploy, start the server and point your browser to http://localhost:8080/test/test.jsp

     

        DataSource ds = null;
        Connection con = null; 
        PreparedStatement pr = null; 
        String dsJNDI = "java:/MSSQL2005XADS"; 
    
        InitialContext ic; 
    
        try{ 
    
          out.println("Some jdbc operations using : " +dsJNDI);
          
          ic = new InitialContext(); 
           
          ds = (DataSource)ic.lookup( dsJNDI ); 
          con = ds.getConnection(); 
          pr = con.prepareStatement("SELECT fname FROM employee"); 
        
          ResultSet rs = pr.executeQuery();
          
          while (rs.next()) {
             out.println("<br> fname = " + rs.getString("fname"));
          }
        
          rs.close();
          pr.close();
         
        }catch(NamingException ex){ 
           out.println("NamingException thrown " +ex); 
        
        }catch(Exception e){ 
          out.println("Exception thrown " +e); 
        
        }finally{
          
          if(con != null){
           
           con.close();
          }      
        }
    

     

     

     

          

    -


           

    Using the DataDirect Connect for JDBC� Drivers

     

      http://www.datadirect.com/downloads/index.ssp

     

     

     

    Local-TX

         
         <datasources>
           <local-tx-datasource>
             <jndi-name>MerliaDS</jndi-name>
                <connection-url>jdbc:datadirect:sqlserver://localhost:1433;DatabaseName=jboss</connection-url>
             <driver-class>com.ddtek.jdbc.sqlserver.SQLServerDriver</driver-class>
             <user-name>sa</user-name>
             <password>sa</password>
         
               <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
               <metadata>
                  <type-mapping>MS SQLSERVER2000</type-mapping>
               </metadata>
           </local-tx-datasource>
         
         </datasources>
    

     

     

     

     

          

    -


           

    Merlia Driver from i-net Software

     

    https://www.inetsoftware.de/products/jdbc/mssql/faq.htm

     

    https://www.inetsoftware.de/Download.htm

     

     

     

    Local-TX

         
         <datasources>
           <local-tx-datasource>
             <jndi-name>MerliaDS</jndi-name>
             <connection-url>jdbc:inetdae7:localhost:1433?database=pubs</connection-url>
             <driver-class>com.inet.tds.TdsDataSource</driver-class>
             <user-name>sa</user-name>
             <password>sa</password>
         
               <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
               <metadata>
                  <type-mapping>MS SQLSERVER2000</type-mapping>
               </metadata>
           </local-tx-datasource>
         
         </datasources>
    

     

     

     

    XA

              
         <datasources>
           <xa-datasource>
             <jndi-name>MerliaXADS</jndi-name>
             <track-connection-by-tx></track-connection-by-tx>
             <isSameRM-override-value>false</isSameRM-override-value>
             <xa-datasource-class>com.inet.tds.DTCDataSource</xa-datasource-class>
             <xa-datasource-property name="ServerName">localhost</xa-datasource-property>
             <xa-datasource-property name="DatabaseName">pubs</xa-datasource-property>
             <user-name>sa</user-name>
             <password>sa</password>
             
               <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
               <metadata>
                  <type-mapping>MS SQLSERVER2000</type-mapping>
               </metadata>
           </xa-datasource>
         
         </datasources>
    

     

    -


       

    Using Microsoft JDBC Driver(s)

     

    The Microsoft JDBC driver for MS SQL Server comes now in two flavors:

     

    Make sure to read release.txt included in the distro to understand the differences between these 2 drivers, especially the new package name introduced with 2005 and the potential conflicts when using both drivers in the same app server.

     

    SQL Server 2000 Driver - Local-TX

     

    <?xml version="1.0" encoding="UTF-8"?>
    
    <!-- ===================================================================== -->
    <!--  MS SQL Server 2000 Datasource                                        -->
    <!-- ===================================================================== -->
    
      <!-- ======================================================================-->
      <!--  the driver is available here  -->
      <!-- http://www.microsoft.com/downloads/details.aspx?familyid=07287B11-0502-461A-B138-2AA54BFDC03A&displaylang=en -->
      <!-- ===================================================================== -->
    
    <datasources>
      <local-tx-datasource>
        <jndi-name>MSSQL2000DS</jndi-name>
        <connection-url>jdbc:microsoft:sqlserver://localhost:1433;SelectMethod=cursor;DatabaseName=pubs</connection-url>
        <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
        <user-name>sa</user-name>
        <password>jboss</password>
    
          <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
          <metadata>
             <type-mapping>MS SQLSERVER2000</type-mapping>
          </metadata>
      </local-tx-datasource>
    
    </datasources>
    

     

    SQL Server 2000 Driver - XA

     

    <?xml version="1.0" encoding="UTF-8"?>
    
    <!-- ===================================================================== -->
    <!--  MS SQL Server 2000 Datasource                                        -->
    <!-- ===================================================================== -->
    
      <!-- ======================================================================-->
      <!--  the driver is available here  -->
      <!-- http://www.microsoft.com/downloads/details.aspx?familyid=07287B11-0502-461A-B138-2AA54BFDC03A&displaylang=en -->
      <!-- ===================================================================== -->
    
    <datasources>
      <xa-datasource>
        <jndi-name>MSSQL2000XADS</jndi-name>
        <track-connection-by-tx></track-connection-by-tx>
        <isSameRM-override-value>false</isSameRM-override-value>
        <xa-datasource-class>com.microsoft.jdbcx.sqlserver.SQLServerDataSource</xa-datasource-class>
        <xa-datasource-property name="ServerName">localhost</xa-datasource-property>
        <xa-datasource-property name="DatabaseName">pubs</xa-datasource-property>
        <xa-datasource-property name="SelectMethod">cursor</xa-datasource-property>
        <xa-datasource-property name="User">sa</xa-datasource-property>
        <xa-datasource-property name="Password">jboss</xa-datasource-property>
    
          <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
          <metadata>
             <type-mapping>MS SQLSERVER2000</type-mapping>
          </metadata>
      </xa-datasource>
    
    </datasources>
    
    

     

     

    SQL Server 2005 Driver - Local-TX

     

    <?xml version="1.0" encoding="UTF-8"?>
    
    <!-- ===================================================================== -->
    <!--  MS SQL Server 2005 Datasource                                        -->
    <!-- ===================================================================== -->
    
      <!-- ======================================================================-->
      <!--  the driver is available here  -->
      <!-- http://www.microsoft.com/downloads/details.aspx?familyid=e22bc83b-32ff-4474-a44a-22b6ae2c4e17&displaylang=en -->
      <!-- ===================================================================== -->
    
    <datasources>
      <local-tx-datasource>
        <jndi-name>MSSQL2005DS</jndi-name>
        <connection-url>jdbc:sqlserver://localhost:1433;DatabaseName=pubs</connection-url>
        <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
        <user-name>sa</user-name>
        <password>jboss</password>
    
          <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
          <metadata>
             <type-mapping>MS SQLSERVER2000</type-mapping>
          </metadata>
      </local-tx-datasource>
    
    </datasources>
    

     

    SQL Server 2005 Driver - XA

     

    <?xml version="1.0" encoding="UTF-8"?>
    
    <!-- ===================================================================== -->
    <!--  MS SQL Server 2005 Datasource                                        -->
    <!-- ===================================================================== -->
    
      <!-- ======================================================================-->
      <!--  the driver is available here  -->
      <!-- http://www.microsoft.com/downloads/details.aspx?familyid=e22bc83b-32ff-4474-a44a-22b6ae2c4e17&displaylang=en -->
      <!-- ===================================================================== -->
    
    <datasources>
      <xa-datasource>
        <jndi-name>MSSQL2005XADS</jndi-name>
        <track-connection-by-tx></track-connection-by-tx>
        <isSameRM-override-value>false</isSameRM-override-value>
        <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
        <xa-datasource-property name="ServerName">localhost</xa-datasource-property>
        <xa-datasource-property name="DatabaseName">pubs</xa-datasource-property>
        <xa-datasource-property name="SelectMethod">cursor</xa-datasource-property>
        <xa-datasource-property name="User">sa</xa-datasource-property>
        <xa-datasource-property name="Password">jboss</xa-datasource-property>
    
          <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
          <metadata>
             <type-mapping>MS SQLSERVER2000</type-mapping>
          </metadata>
      </xa-datasource>
    
    </datasources>
    
    

     

     

     

     

     

     

     

    -


                  

    JSQL Driver

     

    <?xml version="1.0" encoding="UTF-8"?>
    
    <!-- ===================================================================== -->
    <!--                                                                       -->
    <!--  JBoss Server Configuration                                           -->
    <!--                                                                       -->
    <!-- ===================================================================== -->
    
    <!-- $Id: jsql-ds.xml,v 1.2 2003/10/30 09:53:25 ejort Exp $ -->
    
      <!-- ======================================================================-->
      <!-- ConnectionManager setup for JSQL Microsoft SQL Server 2000 driver     -->
      <!-- ===================================================================== -->
    
    <datasources>
      <local-tx-datasource>
        <jndi-name>JSQLDS</jndi-name>
        <connection-url>jdbc:JSQLConnect://localhost:1433/databaseName=testdb</connection-url>
        <driver-class>com.jnetdirect.jsql.JSQLDriver</driver-class>
        <user-name>x</user-name>
        <password>y</password>
            <!-- sql to call when connection is created
            <new-connection-sql>some arbitrary sql</new-connection-sql>
            -->
    
            <!-- sql to call on an existing pooled connection when it is obtained from pool 
            <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
            -->
    
      </local-tx-datasource>
    
    </datasources>
    

     

     

    -


       

    Using jTDS JDBC Driver(s)

     

    jTDS is an open source 100% pure Java (type 4) JDBC 3.0 driver for Microsoft SQL Server (6.5, 7, 2000 and 2005) and Sybase (10, 11, 12, 15). jTDS is based on FreeTDS and is currently the fastest production-ready JDBC driver for SQL Server and Sybase. jTDS is 100% JDBC 3.0 compatible, supporting forward-only and scrollable/updateable ResultSets, concurrent (completely independent) Statements and implementing all the DatabaseMetaData and ResultSetMetaData  methods.

     

    jTDS Homepage

     

    jTDS Driver - Local-TX

     

    <?xml version="1.0" encoding="UTF-8"?>
    
    <!-- ===================================================================== -->
    <!--  jTDS for MS SQL Server 2005 Datasource                               -->
    <!-- ===================================================================== -->
    
      <!-- ======================================================================-->
      <!-- the driver is available here  -->
      <!-- http://jtds.sourceforge.net   -->
      <!-- ===================================================================== -->
    
    <datasources>
      <local-tx-datasource>
        <jndi-name>jtdsDS</jndi-name>
        <connection-url>jdbc:jtds:sqlserver://localhost:1433;databaseName=pubs</connection-url>
        <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
        <user-name>sa</user-name>
        <password>jboss</password>
    
        <!-- optional parameters -->
        <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
        <min-pool-size>10</min-pool-size>
        <max-pool-size>30</max-pool-size>
        <idle-timeout-minutes>15</idle-timeout-minutes>
        <blocking-timeout-millis>5000</blocking-timeout-millis>    
        <new-connection-sql>select 1</new-connection-sql>
        <check-valid-connection-sql>select 1</check-valid-connection-sql>
        <set-tx-query-timeout></set-tx-query-timeout>
        <metadata>
          <type-mapping>MS SQLSERVER2000</type-mapping>
        </metadata>
      </local-tx-datasource>
    </datasources>
    

     

    jTDS Driver - XA

     

    <?xml version="1.0" encoding="UTF-8"?>
    
    <!-- ===================================================================== -->
    <!--  jTDS for MS SQL Server 2005 Datasource                               -->
    <!-- ===================================================================== -->
    
      <!-- ======================================================================-->
      <!--  the driver is available here  -->
      <!-- http://jtds.sourceforge.net -->
      <!-- ===================================================================== -->
    
    <datasources>
      <xa-datasource>
        <jndi-name>jtdsXADS</jndi-name>
        <xa-datasource-class>net.sourceforge.jtds.jdbcx.JtdsDataSource</xa-datasource-class>
        <xa-datasource-property name="ServerName">localhost</xa-datasource-property>
        <xa-datasource-property name="DatabaseName">pubs</xa-datasource-property>
        <xa-datasource-property name="User">sa</xa-datasource-property>
        <xa-datasource-property name="Password">jboss</xa-datasource-property>
    
        <!-- 
         When set to true, emulate XA distributed transaction support, when set to false use experimental 
         true distributed transaction support. True distributed transaction support is only available for 
         SQL Server 2000 and requires the installation of an external stored procedure in the target server 
        (see the README.XA file in the distribution for details).
        -->
        <xa-datasource-property name="XaEmulation">true</xa-datasource-property>
    
        <track-connection-by-tx></track-connection-by-tx>
    
        <!-- optional parameters -->
        <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
        <min-pool-size>10</min-pool-size>
        <max-pool-size>30</max-pool-size>
        <idle-timeout-minutes>15</idle-timeout-minutes>
        <blocking-timeout-millis>5000</blocking-timeout-millis>    
        <new-connection-sql>select 1</new-connection-sql>
        <check-valid-connection-sql>select 1</check-valid-connection-sql>
        <set-tx-query-timeout></set-tx-query-timeout>
        <metadata>
          <type-mapping>MS SQLSERVER2000</type-mapping>
        </metadata>
      </xa-datasource>
    
    </datasources>
    
    

     

     

    FAQ

     

    I am getting the following exception during startup

     

       17:17:57,167 WARN  [ServiceController] Problem starting service jboss.mq.destination:name=testTopic,service=Topic
        org.jboss.mq.SpyJMSException: Error getting durable subscriptions for topic TOPIC.testTopic; - nested throwable: (java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid object name 'JMS_SUBSCRIPTIONS'.)
            at org.jboss.mq.sm.jdbc.JDBCStateManager.getDurableSubscriptionIdsForTopic(JDBCStateManager.java:290)
            at org.jboss.mq.server.JMSDestinationManager.addDestination(JMSDestinationManager.java:656)
    

     

     

    You need to specify a SelectMethod in the connection url

     

    e.g

    <connection-url>jdbc:microsoft:sqlserver://localhost:1433;SelectMethod=cursor;DatabaseName=jboss</connection-url>