User: kunle Date: 01/03/23 17:23:11 Modified: documentation mssql.htm Log: Finally got around to committing this re-write of the MSSQL howto.- now a quick start tutorial to using JBoss+MSSQL- illustrates the process for 3 (yes three!) different JDBC drivers - Merant Connect JDBC - i-net OPTA 2000 (thanks for the link Juha) - Sun JDBC-ODBC Revision Changes Path 1.3 +737 -171 newsite/documentation/mssql.htm Index: mssql.htm =================================================================== RCS file: /cvsroot/jboss/newsite/documentation/mssql.htm,v retrieving revision 1.2 retrieving revision 1.3 diff -u -r1.2 -r1.3 --- mssql.htm 2001/02/08 05:31:34 1.2 +++ mssql.htm 2001/03/24 01:23:11 1.3 @@ -1,174 +1,740 @@ +<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> +<head> +<title>HowTo: Use MS SQL Server with JBoss (under development)</title> +<link rel="stylesheet" type="text/css" href="HowTo.css" > +<meta http-equiv="content-type" content="text/html;charset=iso-8859-1"> +<meta name="generator" content="Adobe GoLive 4"> +</head> +<body> +<div class="page"> + <h1>HowTo: Use MS SQL Server with JBoss (under development)</h1> + <p> </p> + <div class="para" align="left"> + <p>This document provides a concise and easy-to-read set of instructions that + will get you up to speed quickly on the basics of using Microsoft's SQL + Server relational database management system (RDBMS) with the JBoss J2EE + application server suite to develop, deploy and run Enterprise Java + applications that with CMP or BMP.</p> + <p><a href="www.jboss.org">JBoss</a> is the brand name of the leading, Open + Source, standards-compliant J2EE application server suite. The suite includes + the JBossServer EJB v1.1 container and server, the JBossMQ JMS 1.0 implemetation, + the JBossNS JNDI implemetation, the JBossCMP advanced O/R mapper and JDBC + data object storage implementation and, the JAAS-based JBossSX security + framework. All the JBoss applications are 100% Pure Java applications and + are collectively the most innovative J2EE application server suite available + with features including a much admired modular construction and an advanced + plug-in architecture. </p> + <p><a href="http://www.microsoft.com/sql/">Microsoft SQL Server</a> is a robust, + high performance and secure platforms for building and managing relational + databases. It is a Windows application that runs on the Windows 95, 98, + Me, NT and 2000 platforms. There is also a version for the Windows CE platform + but I guess it is safe to assume you aren't using that version with JBoss. + Or is it?</p> + <p>The combination of the JBoss suite of high performance, Open Source, 100% + Pure Java J2EE application servers and the ultra-high performance Microsoft + SQL Server RDBMS offers you a J2EE development and deployment environment + on the Windows platform with superior performance, good return on investment + (ROI) and substantially reduced time to market (TTM) compared to other J2EE + application suites and RDBMS solutions on the Windows platform.</p> + </div> + <h4>C'mon, show me already...</h4> + <div class="para" align="left"> + <p>Not so fast amigos. You need to obtain a copy of the JBoss distribution + from the <a href="http://www.jboss.org/">JBoss website</a>. General information + about all JBoss products as well as instructions on how to get a copy of + JBoss for yourself can be found in the <a href="http://www.jboss.org/newsite/business/faq.html">JBoss + FAQ</a> or on the <a href="http://www.jboss.org/">JBoss website</a>. The + rest of this document covers the following tasks: </p> + <ol> + <li><a href="#INSTALL-DRIVER">Install JDBC Driver</a></li> + <li><a href="#CONFIGURE-JBOSS">Configure JBoss to use JDBC driver</a></li> + <li><a href="#TROUBLE-SHOOTING">Troubleshooting</a></li> + </ol> + <p>Now it is important to be aware that both the JBoss application server + suite and MS SQL Server are still in active development. They are both evolving + as issues are reported & fixed and, new features are identified and + added. This means that <i> some of the information contained in this document + may be out of date with respect to your copy of JBoss or MS SQL Server</i>. + This is even more likely to be the case if you have the latest experimental + or developer version of JBoss. </p> + <p> If this is the case with you or, if you have any further comments or suggestions + about this document or JBoss then please join the <a href="http://www.jboss.org/newsite/business/list.html">JBoss + mailing list</a> . You will then be able to contribute to the production + of this document and the development of JBoss.</p> + <p> </p> + <hr> + </div> + <h2><a name="INSTALL-DRIVER">1. Install JDBC driver</a></h2> + <div class="para" align="left"> + <p>Before you can use Microsoft SQL Server with JBoss (or indeed any other + Java application) you must obtain and install a JDBC driver for Microsoft + SQL Server. Some of the many JDBC drivers that exist for Microsoft SQL Server + are listed below.</p> + </div> + <table width="75%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr> + <td bgcolor="khaki"> + <div align="center"><font color="#000000">JDBC Driver</font></div> + </td> + <td bgcolor="khaki"> + <div align="center"><font color="#000000">Supplier</font></div> + </td> + <td bgcolor="khaki"> + <div align="center"><font color="#000000">JDBC Driver Type</font></div> + </td> + <td bgcolor="khaki"> + <div align="center"><font color="#000000">Availability</font></div> + </td> + </tr> + <tr bgcolor="#CCCCCC"> + <td> + <div align="center"><font color="#000000">FreeTDS</font></div> + </td> + <td bgcolor="#CCCCCC"> + <div align="center"><font color="#000000"><a href="http://www.freetds.org/">www.freetds.org</a></font></div> + </td> + <td> + <div align="center"><font color="#000000">Type 4</font></div> + </td> + <td> + <div align="center"><font color="#000000">Free/Open Source</font></div> + </td> + </tr> + <tr bgcolor="#CCCCCC"> + <td> + <div align="center"><font color="#000000">Merant DataDirect Connect JDBC</font></div> + </td> + <td> + <div align="center"><font color="#000000"><a href="http://www.merant.com/">www.merant.com</a></font></div> + </td> + <td> + <div align="center"><font color="#000000">Type 4</font></div> + </td> + <td> + <div align="center"><font color="#000000">Commercial</font></div> + </td> + </tr> + <tr bgcolor="#CCCCCC"> + <td> + <div align="center"><font color="#000000">i-net Opti JDBC</font></div> + </td> + <td> + <div align="center"><font color="#000000"><a href="http://www.inetsoftware.de/">www.inetsoftware.de</a></font></div> + </td> + <td> + <div align="center"><font color="#000000">Type 4</font></div> + </td> + <td> + <div align="center"><font color="#000000">Commercial</font></div> + </td> + </tr> + <tr bgcolor="#CCCCCC"> + <td> + <div align="center"><font color="#000000">Atinav aveConnect JDBC</font></div> + </td> + <td> + <div align="center"><font color="#000000"><a href="http://www.atinav.com/">www.atinav.com</a></font></div> + </td> + <td> + <div align="center"><font color="#000000">Type 4</font></div> + </td> + <td> + <div align="center"><font color="#000000">Commercial</font></div> + </td> + </tr> + <tr bgcolor="#CCCCCC"> + <td> + <div align="center">JDBC-ODBC Bridge*</div> + </td> + <td> + <div align="center">java.sun.com/jdbc</div> + </td> + <td> + <div align="center">Type 1</div> + </td> + <td> + <div align="center">Free</div> + </td> + </tr> + </table> + <div class="para" align="left"> + <p align="center"><font size="-1">* The Sun JBDC-ODBC Bridge driver is NOT + recommended for serious J2EE development or deployment</font></p> + <br> + <p>For the remainder of this document, the Sun JDBC-ODBC bridge and the Merant + DataDirect Connect JDBC 2.0 Client JDBC drivers will be used to illustrate + the use of MS SQL Server with JBoss.</p> + </div> + <h3>1.2 Installing the Sun JDBC-ODBC Bridge Driver</h3> + <div class="para" align="left"> + <p> The Sun ODBC-JDBC driver is installed automatically during the Java 2 + SDK installation process. It is installed as package <code>sun.jdbc.odbc</code>. + This package must be in the CLASSPATH for any application that uses the + JDBC-ODBC bridge. For this tutorial it will be assumed that the Sun JDBC-ODBC + bridge is properly installed on your system. To use the Sun JDBC-ODBC bridge + with JBoss and MS SQL Server you need to create an ODBC datasource that + references your MS SQL Server database. For this tutorial we will assume + the datasource has been created and named <code><b>jboss_odbc</b></code>.<br> + <br> + </div> + <h3>1.1 Installing the Merant DataDirect Connect JDBC Driver</h3> + <div class="para" align="left"> + <p>According to the blurb on Merant's site:</p> + <p> + <blockquote> + <p>"Connect JDBC are optimized and reliable Type 4 JDBC drivers for + accessing data from Java applets or applications. Connect JDBC supports + the latest Sun JDBC specification and the J2EE framework, providing highly + scalable data access from heterogeneous platforms, and offering greater + flexibility for today's demanding corporate environment."</p> + </blockquote> + <p> Before the Merant DataDirect Connect JDBC drivers can be used with JBoss, + the driver's .jar files must be accessible to JBoss. There are two ways + to accomplish this, you should choose one of the following options:</p> + <ol> + <li> Run Merant's installation program then add the <code>base.jar</code>, + <code>util.jar</code> and <code>sqlserver.jar</code> files to the <code>CLASSPATH</code>. + The files are located in the <code>%MERANT_HOME%/lib</code> directory + on Windows. Or you can just...</li> + </ol> + <ol start="2"> + <li> Copy the <code>base.jar</code>, <code>util.jar</code> and <code>sqlserver.jar</code> + files from the <code>%MERANT_HOME%/lib</code> directory to the <code>%JBOSS_HOME%/lib/ext</code> + directory.</li> + </ol> + </div> + <p> </p> + <div class="para" align="left"> +<hr> + </div> + <h2><a name="#CONFIGURE-JBOSS">2. Configure JBoss to use JDBC driver</a></h2> + <div class="para" align="left"> + <p>The instructions for configuring JBoss to use your JDBC driver is different + depending on the version of JBoss you have. </p> + <ul> + <li><a href="#CONFIGURE-JBOSS-2">Configure JBoss versions up to 2.0 FINAL</a></li> + </ul> + <ul> + <li><a href="#CONFIGURE-JBOSS-2-PLUS">Configure JBoss versions later than 2.0 FINAL</a></li> + </ul> + <p> </p> + </div> + <h3><a name="#CONFIGURE-JBOSS-2">2.1 Configure JBoss 2.0 FINAL</a></h3> + <h4>2.1.1 Add the JDBC driver to jboss.properties</h4> + <div class="para" align="left"> + <p>Now that we have installed a JDBC driver for MS SQL Server, we need to + tell JBoss that we would like it to use this driver. To do this we will + add an entry for the driver to the list of drivers that JBoss can use. This + list is stored in the <code>jbdc.drivers</code> entry in the <code>jboss.properties</code> + file. The file can be found in <code>%JBOSS_HOME%/conf/<config-name></code>. + For a standard distribution of JBoss, <code><config-name></code> is + <code>default</code> whilst for the JBoss-Jetty it is <code>jetty</code>. + The <code>jbdc.drivers</code> entry should be a single, continuous string.</p> + + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td width="50%" height="32" bgcolor="lightgreen"> + <h4 align="center">Sun JDBC-OBDC bridge</h4> + </td> + </tr> + <tr bgcolor="khaki"> + <td width="50%" bgcolor="lightgreen"><code>jdbc.drivers=org.hsql.jdbcDriver,jdbc.idbDriver,<b>sun.jdbc.odbc.JdbcOdbcDriver</b></code></td> + </tr> + </table> + <br> + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td width="50%" height="32"> + <h4 align="center">Merant DataDirect Connect JDBC 2.0</h4> + </td> + </tr> + <tr bgcolor="khaki"> + <td width="50%" ><code>jdbc.drivers=org.hsql.jdbcDriver,jdbc.idbDriver,<b>com.merant.datadirect.jdbc.sqlserver.SQLServerDriver</b></code></td> + </tr> + </table> + <br> + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td width="50%" height="32" bgcolor="lightblue"> + <h4 align="center">i-net OPTA 2000 <font size="-1">(v2.05 or higher)</font></h4> + </td> + </tr> + <tr bgcolor="khaki"> + <td width="50%" bgcolor="lightblue"><code>jdbc.drivers=org.hsql.jdbcDriver,jdbc.idbDriver,<b>com.inet.tds.TdsDriver</b></code></td> + </tr> + </table> + <br> + <br> + </div> + + <h4>2.1.2 Ensure that JBoss loads the JDBC driver correctly</h4> + <div class="para" align="left"> + <p>Start JBoss and ensure that the JDBC driver is found (the relevant output + would be similar to the output shown below). Stop JBoss afterwards.</p> + + + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td height="32" bgcolor="lightgreen"> + <h4 align="center">Sun JDBC-OBDC bridge</h4> + </td> + </tr> + <tr bgcolor="khaki"> + <td bgcolor="lightgreen"><code>[JDBC] Loaded JDBC-driver:org.hsql.jdbcDriver<br> + [JDBC] Loaded JDBC-driver:org.enhydra.instantdb.jdbc.idbDriver<br> + [JDBC] Loaded JDBC-driver:sun.jdbc.odbc.JdbcOdbcDriver</code></td> + </tr> + </table> + <br> + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td height="32"> + <h4 align="center">Merant DataDirect Connect JDBC 2.0</h4> + </td> + </tr> + <tr bgcolor="khaki"> + <td><code>[JDBC] Loaded JDBC-driver:org.hsql.jdbcDriver<br> + [JDBC] Loaded JDBC-driver:org.enhydra.instantdb.jdbc.idbDriver<br> + [JDBC] Loaded JDBC-driver:com.merant.datadirect.jdbc.sqlserver.SQLServerDriver</code></td> + </tr> + </table> + <br> + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td height="32" bgcolor="lightblue"> + <h4 align="center">i-net OPTA 2000 <font size="-1">(v2.05 or higher)</font></h4> + </td> + </tr> + <tr bgcolor="khaki"> + <td bgcolor="lightblue"><code>[JDBC] Loaded JDBC-driver:org.hsql.jdbcDriver<br> + [JDBC] Loaded JDBC-driver:org.enhydra.instantdb.jdbc.idbDriver<br> + [JDBC] Loaded JDBC-driver:com.inet.tds.TdsDriver</code></td> + </tr> + </table> + <br> + <br> + </div> + + <h4>2.1.3 Declare a DB connection pool for your driver</h4> + + <div class="para" align="left"> + <p>Now that we know the driver is correctly installed, we need to set up a + connection pool that EJBs can connect to. We will add an MLET entry into + the <code>jboss.conf</code> file as shown below. The file can be found in + <code>%JBOSS_HOME%/conf/<config-name></code>.</p> + <p>For this tutorial, we will create a connection pool named <code><b>SQLServerPool</b></code> + as specified in the first argument in the MLET entry. The second argument + in the MLET entry should be your driver's <code>XADataSource</code> classname. + If your driver doesn't support <code>XADataSource</code> use <code>org.jboss.minerva.xa.XADataSourceImpl</code> + instead.</p> + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td bordercolor="#000000" height="32" bgcolor="lightgreen"> + <h4 align="center">Sun JDBC-OBDC bridge</h4> + </td> + </tr> + <tr bgcolor="khaki"> + <td bordercolor="#000000" bgcolor="lightgreen"><code><MLET CODE="org.jboss.jdbc.XADataSourceLoader" + ARCHIVE="jboss.jar,rt.jar" CODEBASE="../lib/ext/"><br> + <ARG TYPE="java.lang.String" VALUE="<b>SQLServerPool</b>"><br> + <ARG TYPE="java.lang.String" VALUE="org.jboss.minerva.xa.XADataSourceImpl"><br> + </MLET></code></td> + </tr> + </table> + <br> + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td height="32"> + <h4 align="center">Merant DataDirect Connect JDBC 2.0</h4> + </td> + </tr> + <tr bgcolor="khaki"> + <td><code><MLET CODE="org.jboss.jdbc.XADataSourceLoader" + ARCHIVE="jboss.jar,base.jar,util.jar,sqlserver.jar" CODEBASE="../lib/ext/"><br> + <ARG TYPE="java.lang.String" VALUE="<b>SQLServerPool</b>"><br> + <ARG TYPE="java.lang.String" VALUE="org.jboss.minerva.xa.XADataSourceImpl"><br> + </MLET></code></td> + </tr> + </table> + <br> + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td height="32" bgcolor="lightblue"> + <h4 align="center">i-net OPTA 2000 <font size="-1">(v2.05 or higher)</font></h4> + </td> + </tr> + <tr bgcolor="khaki"> + <td bgcolor="lightblue"><code><MLET CODE="org.jboss.jdbc.XADataSourceLoader" + ARCHIVE="jboss.jar,Opta2000.jar" CODEBASE="../lib/ext/"><br> + <ARG TYPE="java.lang.String" VALUE="<b>SQLServerPool</b>"><br> + <ARG TYPE="java.lang.String" VALUE="com.inet.tds.XDataSource"><br> + </MLET></code></td> + </tr> + </table> + <br> + <br> + </div> + + <h4>2.1.4 Configure the DB connection pool</h4> + + <div class="para" align="left"> + <p>Before we can use our connection pool, we need to tell it how to find our + database server. We can also take this chance to specifiy other configuration + parameters for the pool. To do this, we will add an MBEAN entry into the + <code>jboss.jcml</code> file as shown below. The file can be found in <code>%JBOSS_HOME%/conf/<config-name></code>.</p> + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td bordercolor="#000000" height="32" bgcolor="lightgreen"> + <h4 align="center">Sun JDBC-OBDC bridge</h4> + </td> + </tr> + <tr> + <td bordercolor="#000000" bgcolor="lightgreen"><code><mbean name="DefaultDomain:service=XADataSource,name=<b>SQLServerPool</b>"><br> + <attribute name="URL">jdbc:odbc:<b>jboss_odbc</b></attribute><br> + <attribute name="JDBCUser"><b>dbusername</b></attribute><br> + <attribute name="Password"><b>dbpassword</b></attribute><br> + </mbean> </code> </td> + </tr> + <tr> + <td bordercolor="#000000" height="32" align="center"><p><font size="-1">* + If the ODBC DSN is already configured with a username and password, + you do not to include <b>dbusername</b> and <b>dbpassword</b>.</font></p></td> + </tr> + </table> + <br> + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td height="32"> + <h4 align="center">Merant DataDirect Connect JDBC 2.0</h4> + </td> + </tr> + <tr bgcolor="khaki"> + <td> + <p><code><mbean name="DefaultDomain:service=XADataSource,name=<b>SQLServerPool</b>"><br> + <attribute name="URL">jdbc:sqlserver://<b>servername</b>:1433</attribute><br> + <attribute name="Properties">DatabaseName=<b>DatabaseName</b></attribute><br> + <attribute name="JDBCUser"><b>dbusername</b></attribute><br> + <attribute name="Password"><b>dbpassword</b></attribute><br> + <attribute name="MinSize">0</attribute><br> + <attribute name="MaxSize">10</attribute><br> + <attribute name="GCEnabled">false</attribute><br> + <attribute name="GCMinIdleTime">1200000</attribute><br> + <attribute name="GCInterval">120000</attribute><br> + <attribute name="InvalidateOnError">false</attribute><br> + <attribute name="TimestampUsed">false</attribute><br> + <attribute name="Blocking">true</attribute><br> + <attribute name="LoggingEnabled">false</attribute><br> + <attribute name="IdleTimeoutEnabled">false</attribute><br> + <attribute name="IdleTimeout">1800000</attribute><br> + <attribute name="MaxIdleTimeoutPercent">1.0</attribute><br> + </mbean></code></p> + </td> + </tr> + </table> + <br> + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td height="32" bgcolor="lightblue"> + <h4 align="center">i-net OPTA 2000 <font size="-1">(v2.05 or higher)</font></h4> + </td> + </tr> + <tr bgcolor="khaki"> + <td bgcolor="lightblue"> + <p><code><mbean name="DefaultDomain:service=XADataSource,name=<b>SQLServerPool</b>"><br> + <attribute name="Properties">host=<b>ServerName</b>; + database=<b>DatabaseName</b></attribute><br> + <attribute name="JDBCUser"><b>dbusername</b></attribute><br> + <attribute name="Password"><b>dbpassword</b></attribute><br> + <attribute name="MinSize">0</attribute><br> + <attribute name="MaxSize">10</attribute><br> + <attribute name="GCMinIdleTime">1200000</attribute><br> + <attribute name="GCEnabled">false</attribute><br> + <attribute name="GCInterval">120000</attribute><br> + <attribute name="InvalidateOnError">false</attribute><br> + <attribute name="TimestampUsed">false</attribute><br> + <attribute name="Blocking">true</attribute><br> + <attribute name="LoggingEnabled">false</attribute><br> + <attribute name="IdleTimeoutEnabled">false</attribute><br> + <attribute name="IdleTimeout">1800000</attribute><br> + <attribute name="MaxIdleTimeoutPercent">1.0</attribute><br> + </mbean></code></p> + </td> + </tr> + </table> + <br> + <br> + </div> + + <h4>2.1.5 Check that JBoss creates the connection pool</h4> + <div class="para" align="left"> + <p>Start JBoss and ensure that the JDBC driver is found (the relevant output + would be similar to the output shown below). Stop JBoss afterwards.</p> + <div class="code" align="left"> + [SQLServerPool] Starting<br> + [SQLServerPool] XA Connection pool SQLServerPool bound to java:/SQLServerPool<br> + [SQLServerPool] Started + </div> + <br> + <br> + </div> - <head> - <meta http-equiv="content-type" content="text/html;charset=iso-8859-1"> - <meta name="generator" content="Adobe GoLive 4"> - <title>jboss.xml</title> - </head> - - <font face="arial,helvetica" size="6" color="#ffcc00">MS-SQL Howto (under development) </font> - <p>This Howto is still under development we repost messages sent to the jboss lists</p> - <p>MS-SQL with JDBC</p> - <p>==============</p> - <p>1) Copy your JDBC-jar file from inet to jboss /lib/ext directory<br> - </p> - <p>2) Start JBoss and check if the JDBC-jar file is loaded (it appears<br> - at the very beginning a list of jar-files loaded)<br> - </p> - <p>3) Add the JDBC driver class name in jboss.properties in the jboss.drivers<br> - list<br> - </p> - <p>4) Start JBoss and check if the JDBC-driver is found (it appears at<br> - right after the list of jar-files loaded)<br> - </p> - <p>5) Add a <M-LET> tag to the jboss.conf, add the JDBC-jar in<br> - after the jboss.jar in this tag (everything within {} is to be replaced by<br> - your value inclusive the {}):<br> - </p> - <p><MLET CODE = "org.jboss.jdbc.DataSourceImpl" ARCHIVE="jboss.jar,{your<br> - JDBC-jar file}" CODEBASE="../lib/ext/"><br> - <ARG TYPE="java.lang.String" VALUE="{JDBC-url to the DB}"><br> - <ARG TYPE="java.lang.String" VALUE="{datasource name like: MsSQL}"><br> - <ARG TYPE="java.lang.String" VALUE="{JDBC-driver class name}"><br> - <ARG TYPE="java.lang.String" VALUE="{User name}"><br> - <ARG TYPE="java.lang.String" VALUE="{Password}"><br> - </MLET><br> - </p> - <p>6) Start JBoss and check if a new DataSource is loaded (count them if there<br> - is no output<br> - from the JDBC loading)<br> - </p> - <p>7) Start EJX (java -jar ejx.jar within the /bin directory)<br> - </p> - <p>8) Load your EJB jar file and select the JAWS.xml or create one<br> - </p> - <p>9) Insert MsSQL as datasource name ( or the datasource name you specifed<br> - in step 5)<br> - </p> - <p>10) Set the datatype mapping from the list below BUT<br> - now we have a problem because there is NO datatype mapping for<br> - MS SQL-Server but I will look this week for one or at least tell you how<br> - to create one. CAN someone tell me how?<br> - </p> - <p>11) Start JBoss and check if the output if the tables are created (output<br> - starts with JAWS).</p> - <p></p> - <p>MS-SQL with JDBC-ODBC</p> - <p>====================</p> - <p>From: Steve Kwee <[EMAIL PROTECTED]><br> - To: "'JBoss'" <[EMAIL PROTECTED]><br> - Subject: AW: [JBoss-User] SQL Server<br> - Date: Tue, 27 Jun 2000 12:19:48 +0200<br> - Reply-To: "JBoss" <[EMAIL PROTECTED]><br> - Sender: <[EMAIL PROTECTED]><br> - <br> - <br> - At least, there is someone else, using MSSQL...<br> - Last night I put toghether this kind of readme on the topic.<br> - Works with a compiled version I took from the cvs a week ago.<br> - <br> - <br> - Using MS SQLServer with the JDBC-ODBC Bridge (included in JDK since 1.2.x)<br> - --------------------------------------------------------------------------<br> - Should also work for other ODBC Datasources<br> - <br> - <br> - 1. Install ODBC Datasource <my_dsn><br> - <br> - 2. Add sun.jdbc.odbc.JdbcOdbcDriver to jboss.properties:<br> - <br> - jdbc.drivers=org.hsql.jdbcDriver,jdbc.idbDriver,sun.jdbc.odbc.JdbcOdbcDriver<br> - <br> - <br> - 3. Add MLet for Datasource in jboss.conf<br> - <MLET CODE = "org.jboss.jdbc.DataSourceImpl" ARCHIVE="rt.jar"<br> - CODEBASE="../lib/ext/"><br> - <ARG TYPE="java.lang.String" VALUE="jdbc:odbc:my_dsn"><br> - <ARG TYPE="java.lang.String" VALUE="my_dsn"><br> - <ARG TYPE="java.lang.String" VALUE="sun.jdbc.odbc.JdbcOdbcDriver"><br> - <ARG TYPE="java.lang.String" VALUE="sa"><br> - <ARG TYPE="java.lang.String" VALUE=""><br> - </MLET><br> - <br> - 4. Add Type-Mapping for MS SQLSERVER to the beans jaws.xml<br> - (was provided by Pedro Mota, maybe someone could add it to the<br> - cvs/defaultjaws.xml)<br> - <type-mapping><br> - <name>MS SQLSERVER</name><br> - <mapping><br> - <java-type>java.lang.Integer</java-type><br> - <jdbc-type>INTEGER</jdbc-type><br> - <sql-type>INTEGER</sql-type><br> - </mapping><br> - <mapping><br> - <java-type>java.lang.Character</java-type><br> - <jdbc-type>CHAR</jdbc-type><br> - <sql-type>CHAR</sql-type><br> - </mapping><br> - <mapping><br> - <java-type>java.lang.Short</java-type><br> - <jdbc-type>SMALLINT</jdbc-type><br> - <sql-type>SMALLINT</sql-type><br> - </mapping><br> - <mapping><br> - <java-type>java.lang.Double</java-type><br> - <jdbc-type>DOUBLE</jdbc-type><br> - <sql-type>DOUBLE</sql-type><br> - </mapping><br> - <mapping><br> - <java-type>java.lang.Long</java-type><br> - <jdbc-type>DECIMAL</jdbc-type><br> - <sql-type>DECIMAL(20)</sql-type><br> - </mapping><br> - <mapping><br> - <java-type>java.lang.String</java-type><br> - <jdbc-type>VARCHAR</jdbc-type><br> - <sql-type>VARCHAR(256)</sql-type></p> - <p> </mapping><br> - <mapping><br> - <java-type>java.lang.Object</java-type><br> - <jdbc-type>IMAGE</jdbc-type><br> - <sql-type>IMAGE</sql-type><br> - </mapping><br> - <mapping><br> - <java-type>java.lang.Byte</java-type><br> - <jdbc-type>TINYINT</jdbc-type><br> - <sql-type>TINYINT</sql-type> <br> - </mapping><br> - <mapping><br> - <java-type>java.sql.TimeStamp</java-type><br> - <jdbc-type>TIMESTAMP</jdbc-type><br> - <sql-type>TIMESTAMP</sql-type><br> - </mapping><br> - <mapping><br> - <java-type>java.util.Date</java-type> <br> - <jdbc-type>DATETIME</jdbc-type><br> - <sql-type>DATETIME</sql-type><br> - </mapping><br> - <mapping><br> - <java-type>java.lang.Boolean</java-type><br> - <jdbc-type>BIT</jdbc-type><br> - <sql-type>BIT</sql-type><br> - </mapping><br> - <mapping><br> - <java-type>java.lang.Float</java-type><br> - <jdbc-type>FLOAT</jdbc-type><br> - <sql-type>FLOAT</sql-type><br> - </mapping><br> - </type-mapping><br> - <br> - <br> - 5. Edit your beans jaws.xml and tell your enterprise-beans to use<br> - </p> - <p>a) the MS SQLSERVER Type-Mapping with<br> - <type-mapping>MS SQLSERVER</type-mapping><br> - b) your Datasource<br> - <datasource>my_dsn</datasource><br> - <br> - <br> - This has been testet with the banktest and worked only "half".<br> - Jaws was able to init create the tables and to populate the table customers<br> - but failed to insert data into accounts.</p> - <p>//MF FIXME Test with the latest version - + <h3><a name="#CONFIGURE-JBOSS-2-PLUS">2.2 Configure JBoss versions later than 2.0 FINAL</a></h3> + <div class="para" align="left"> + <h4>2.2.1 Tell JBoss about your JDBC driver</h4> + <p>Now that we have installed a JDBC driver for MS SQL Server, we need to + tell JBoss that we would like it to use this driver. To do this we will + add an entry for the driver to the list of drivers that JBoss can use. This + list is stored in the <code>jboss.jcml</code> file. The file can be found + in <code>%JBOSS_HOME%/conf/<config-name></code>. For a standard distribution + of JBoss, <code><config-name></code> is <code>default</code> whilst + for the JBoss-Jetty it is <code>jetty</code>. The entry should be a single, + continuous list of comma-separated JDBC driver names.</p> + <p>Open the file and look for the entry that begins with the following <code>mbean</code> + tag:</p> + <p> <code><font color="blue"><mbean code="org.jboss.jdbc.JdbcProvider" + name="DefaultDomain:service=JdbcProvider"></font></code></p> + <p> The list of drivers is kept in a sub-tag of this entry named <code>Drivers</code>. + Add your JBDC driver to the list as shown below. When you've finished, keep + the file open as we still need to use it for the next step too.</p> + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td width="50%" height="32" bgcolor="lightgreen"> + <h4 align="center">Sun JDBC-OBDC bridge</h4> + </td> + </tr> + <tr bgcolor="khaki"> + <td width="50%" bgcolor="lightgreen"><code><mbean code="org.jboss.jdbc.JdbcProvider" + name="DefaultDomain:service=JdbcProvider"><br> + <attribute name="<font color="red">Drivers</font>"><br> + org.hsql.jdbcDriver,org.enhydra.instantdb.jdbc.idbDriver<b>,sun.jdbc.odbc.JdbcOdbcDriver</b><br> + </attribute><br> + </mbean></code></td> + </tr> + </table> + <br> + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td width="50%" height="32"> + <h4 align="center">Merant DataDirect Connect JDBC 2.0</h4> + </td> + </tr> + <tr bgcolor="khaki"> + <td width="50%" ><code><mbean code="org.jboss.jdbc.JdbcProvider" + name="DefaultDomain:service=JdbcProvider"><br> + <attribute name="<font color="red">Drivers</font>"><br> + org.hsql.jdbcDriver,org.enhydra.instantdb.jdbc.idbDriver<b>,com.merant.datadirect.jdbc.sqlserver.SQLServerDriver</b><br> + </attribute><br> + </mbean></code></td> + </tr> + </table> + <br> + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td width="50%" height="32" bgcolor="lightblue"> + <h4 align="center">i-net OPTA 2000 <font size="-1">(v2.05 or higher)</font></h4> + </td> + </tr> + <tr bgcolor="khaki"> + <td width="50%" bgcolor="lightblue"><code><mbean code="org.jboss.jdbc.JdbcProvider" + name="DefaultDomain:service=JdbcProvider"><br> + <attribute name="<font color="red">Drivers</font>"><br> + org.hsql.jdbcDriver,org.enhydra.instantdb.jdbc.idbDriver<b>,com.inet.tds.TdsDriver</b><br> + </attribute><br> + </mbean></code></td> + </tr> + </table> + <br> + <br> + </div> + <h4></h4> + <h4>2.2.2 Create a DB connection pool</h4> + + <div class="para" align="left"> + <p>Now that we have added our driver to the list so that JBoss knows about + it, we need to create a connection pool that our EJBs can connect to. For + this tutorial, we will create a connection pool named <code><b>SQLServerPool</b></code>. + To create the connection pool, locate the <code>mbean</code> entry that + begins with the following line in the <code>jboss.jcml</code> file (you + should have kept it open as advised in the last step):</p> + <p> <code><font color="blue"><mbean code="org.jboss.jdbc.XADataSourceLoader" + name="DefaultDomain:service=XADataSource,name=DefaultDS"></font></code><br> + </p> + <p> Add the <code>mbean</code> entry given below to the file just <i>below</i> + the <code>mbean</code> you have just located.</p> + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td bordercolor="#000000" height="32" bgcolor="lightgreen"> + <h4 align="center">Sun JDBC-OBDC bridge</h4> + </td> + </tr> + <tr> + <td bordercolor="#000000" bgcolor="lightgreen"><code><mbean code="<b>org.jboss.jdbc.XADataSourceLoader</b>" + name="DefaultDomain:service=XADataSource,name=<b>SQLServerPool</b>"><br> + <attribute name="DataSourceClass"><b>org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl</b></attribute><br> + <attribute name="PoolName"><b>SQLServerPool</b></attribute><br> + <attribute name="URL">jdbc:odbc:<b>jboss_odbc</b></attribute><br> + <attribute name="JDBCUser"><b>dbusername</b></attribute><br> + <attribute name="Password"><b>dbpassword</b></attribute><br> + </mbean> + </code> </td> + </tr> + <tr> + <td bordercolor="#000000" height="32" align="center"><p><font size="-1">* + If the ODBC DSN is already configured with a username and password, + you do not to include <b>dbusername</b> and <b>dbpassword</b>.</font></p></td> + </tr> + </table> + <br> + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td height="32"> + <h4 align="center">Merant DataDirect Connect JDBC 2.0</h4> + </td> + </tr> + <tr bgcolor="khaki"> + <td bgcolor="khaki"> + <p><code><mbean code="<b>org.jboss.jdbc.XADataSourceLoader</b>" + name="DefaultDomain:service=XADataSource,name=<b>SQLServerPool</b>"><br> + <attribute name="DataSourceClass"><b>org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl</b></attribute><br> + <attribute name="PoolName"><b>SQLServerPool</b></attribute><br> + <attribute name="URL">jdbc:sqlserver://<b>servername</b>:1433</attribute><br> + <attribute name="Properties">DatabaseName=<b>DatabaseName</b></attribute><br> + <attribute name="JDBCUser"><b>dbusername</b></attribute><br> + <attribute name="Password"><b>dbpassword</b></attribute><br> + <attribute name="MinSize">0</attribute><br> + <attribute name="MaxSize">10</attribute><br> + <attribute name="GCEnabled">false</attribute><br> + <attribute name="GCMinIdleTime">1200000</attribute><br> + <attribute name="GCInterval">120000</attribute><br> + <attribute name="InvalidateOnError">false</attribute><br> + <attribute name="TimestampUsed">false</attribute><br> + <attribute name="Blocking">true</attribute><br> + <attribute name="LoggingEnabled">false</attribute><br> + <attribute name="IdleTimeoutEnabled">false</attribute><br> + <attribute name="IdleTimeout">1800000</attribute><br> + <attribute name="MaxIdleTimeoutPercent">1.0</attribute><br> + </mbean> + </code></p> + </td> + </tr> + </table> + <br> + <table class="code" width="93%" border="0" cellspacing="2" cellpadding="1" align="center"> + <tr bgcolor="khaki"> + <td height="32" bgcolor="lightblue"> + <h4 align="center">i-net OPTA 2000 <font size="-1">(v2.05 or higher)</font></h4> + </td> + </tr> + <tr bgcolor="khaki"> + <td bgcolor="lightblue"> + <p><code><mbean code="<b>org.jboss.jdbc.XADataSourceLoader</b>" + name="DefaultDomain:service=XADataSource,name=<b>SQLServerPool</b>"><br> + <attribute name="DataSourceClass"><b>com.inet.tds.XDataSource</b></attribute> + <br> + <attribute name="PoolName"><b>SQLServerPool</b></attribute><br> + <attribute name="Properties">host=<b>ServerName</b>; + database=<b>DatabaseName</b></attribute><br> + <attribute name="JDBCUser"><b>dbusername</b></attribute><br> + <attribute name="Password"><b>dbpassword</b></attribute><br> + <attribute name="MinSize">0</attribute><br> + <attribute name="MaxSize">10</attribute><br> + <attribute name="GCEnabled">false</attribute><br> + <attribute name="GCMinIdleTime">1200000</attribute><br> + <attribute name="GCInterval">120000</attribute><br> + <attribute name="InvalidateOnError">false</attribute><br> + <attribute name="TimestampUsed">false</attribute><br> + <attribute name="Blocking">true</attribute><br> + <attribute name="LoggingEnabled">false</attribute><br> + <attribute name="IdleTimeoutEnabled">false</attribute><br> + <attribute name="IdleTimeout">1800000</attribute><br> + <attribute name="MaxIdleTimeoutPercent">1.0</attribute><br> + </mbean> </code></p> + </td> + </tr> + </table> + <br> + </div> + + <h4>2.2.3 Check that JBoss creates the connection pool</h4> + <div class="para" align="left"> + <p>Start JBoss and ensure that the JDBC driver is found and the connection + pool is created. The relevant out put is shown below for successful JBDC + driver load.</p> + <div class="code" align="left"> + <p>[Transaction manager] Initializing<br> + [Transaction manager] Loaded JDBC-driver:org.hsql.jdbcDriver<br> + [Transaction manager] Loaded JDBC-driver:org.enhydra.instantdb.jdbc.idbDriver<br> + [Transaction manager] Loaded JDBC-driver:com.merant.datadirect.jdbc.sqlserver.SQLServerDriver<br> + [Transaction manager] Initialized</p> + </div> + <p>And a litte later...the output for the connection pool. Stop JBoss afterwards. + </p> + <div class="code" align="left"> + [SQLServerPool] Starting<br> + [SQLServerPool] XA Connection pool SQLServerPool bound to java:/SQLServerPool<br> + [SQLServerPool] Started + </div> + <br> + </div> + <p> </p> + <div class="para" align="left"> +<hr> + </div> + <h2><a name="#TROUBLE-SHOOTING">3. Troubleshooting</a></h2> + <div class="para" align="left"> </div> + <h3>3.1 JDBC driver not loaded error</h3> + <div class="para" align="left"> Error message shown on start-up: + <div class="code" align="left">[JDBC] Could not load driver:sun.jdbc.odbc.JdbcOdbcDrivers</div> + <br> + This error can occur in the following situations:<br> + a) the spelling of a JDBC driver name in the <code>jboss.properties</code> + configuration file (or <code>jboss.jcml</code> for JBoss 2.1 and later) is + incorrect. In the example above it should have been <code>sun.jdbc.odbc.JdbcOdbcDriver</code>.<br> + b) a JDBC driver has not been properly installed - it's class files or .jar + file(s) are not in the <code>CLASSPATH</code> <br> + <br> + </div> + <h3>3.2 JBoss hangs while starting connection pool</h3> + <div class="para" align="left"> When JBoss is started, the sequence of messages + that is shown on the console stops midway. The last message refers to a connection + pool as shown below. Please note that the white lines represent messages that + are <b>not</b> displayed : + <div class="code" align="left">[Hypersonic] Press [Ctrl]+[C] to abort<br> + [InstantDB] Started<br> + [DefaultDS] Starting<br> + [DefaultDS] XA Connection pool DefaultDS bound to java:/DefaultDS<br> + [DefaultDS] Started[SQLServerPool] Starting<br> + [SQLServerPool] XA Connection pool SQLServerPool bound to java:/SQLServerPool<br> + <font color="white">[SQLServerPool] Started<br> + [Container factory] Starting<br> + [Container factory] Started<br> + [JBossMQ] Starting<br> + [JBossMQ] Server Version 0.8 Started<br> + </font> </div> + <p><br> + Check that your JDBC driver is loaded correctly. If not then see 3.1 above + for possible clues. If your JDBC driver is loading correctly then this error + indicates that JBoss is unable to communicate with your database server.</p> + <p>It can occur in the following situations:<br> + a) the spelling of one or more of the parameters needed to connect to the + database server is mispelled or missing. Please check that you have specified + the correct hostname, database, username, password etc for the connection + pool.<br> + </p> + <p> </p> + <hr> + </div> + <br> + <div class="footer" align="left"> + <p>This document was created by Anthony Oguntimehin and <a href="mailto:[EMAIL PROTECTED]">Kunle + Odutola</a> on 20 February 2000. Please report any errors and omissions + to <b> </b>the <a href="http://www.jboss.org/newsite/business/list.html">JBoss + mailing list</a> in the first instance. You may copy the report to <a href="mailto:[EMAIL PROTECTED]">Kunle + Odutola</a> as well. <br> + <br> + Revision: $Id: mssql.htm,v 1.3 2001/03/24 01:23:11 kunle Exp $</p> + </div> +</div> +</body> </html> _______________________________________________ Jboss-development mailing list [EMAIL PROTECTED] http://lists.sourceforge.net/lists/listinfo/jboss-development
