JBoss: Set Up AMS SQL Datasource

chenqb

　　JBoss: Set Up AMS SQL Datasource
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.jsphttp://wiki.jboss.org/wiki/_WikiButtons/out.png

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.ssphttp://wiki.jboss.org/wiki/_WikiButtons/out.png

　　


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.htmhttp://wiki.jboss.org/wiki/_WikiButtons/out.png
https://www.inetsoftware.de/Download.htmhttp://wiki.jboss.org/wiki/_WikiButtons/out.png

　　


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/>
<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 3http://wiki.jboss.org/wiki/_WikiButtons/out.png which can be used with SQL Server 2000
  
• 
  Microsoft SQL Server 2005 JDBC Driverhttp://wiki.jboss.org/wiki/_WikiButtons/out.png 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/>
<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/>
<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 http://wiki.jboss.org/wiki/_WikiButtons/out.png

　　


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/>
<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/>
<!-- 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/>
<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>
　　

　　



Other languages:



Log in to make links between pages



Attachments:

http://wiki.jboss.org/wiki/_Files/SetUpAMSSQLDatasource/test.zip

[History]

The page last changed on Tue Mar 06 09:42:52 EST 2007 by luc.texier@jboss.com