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:
SQL Server 2000 Driver for JDBC Service Pack 3 which can be used with SQL Server 2000
Microsoft SQL Server 2005 JDBC Driver which can talk to either SQL Server 2000 or 2005. This version contains numerous fixes and has been certified for Hibernate. Last this driver runs under JDK 5.
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 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>
Comments