I'm attempting to use the information provided in the Microsoft KB article
Q313173 {http://support.microsoft.com/kb/313173/en-us to set up connection
pooling using Tomcat 5.5. This is the most recent jdbc sql server article I
was able to locate (note reference to Tomcat 4.0 in the readme).
Q313173 article provides a zip file, which has a sample test page to confirm
the connection pool is working and a readme article with step by step
instructions. I've appended some of the readme's content below. I've also
appended the test page provided in the zip file.
I modified and confirmed (using osql) the database name, username, and
password in the resource parrameters definition for "jdbc/sql2000" would
access and select data. I placed the pool.jar in the Tomcat's common\lib
folder.
I am not able to get a connection using the sample test ("DataSource not
created").
I am uncertain about the correctness of the readme file's instructions.
Permutations have not been successful, and there are a few of them. I'm
equally torn between the positions "the published example is always right"
and "the published example must be wrong." I've researched MARC, other
Microsoft kb articles.
My questions are:
In step 2 in the readme, the resource name & corresponding resource parms
name element values are different. Is this correct?
In step 2 in the readme, the references to the pool.jar file provided in the
zip file are to a package with ".mspool." rather than ".pool.". (Reason:
package explorer in eclipse expands them as pool). Is this correct?
In step 2 in the readme, the dsjndiname element points to another resource.
I think this is ok, but I would like confirmation.
Environment: xp home sp2, msde sql server 2000 Microsoft SQL Server 2000 -
8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft
Corporation Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2),
Tomcat 5.5 (from select @@version query).
If everything is correct in Microsoft's zip and instructions, then I must
have missed an elementary step. I'm a novice.
Thank you for any suggestions.
=======================Extract Starts=========================
SAMPLE CONNECTION POOLMANAGER:
Files:
1. com.microsoft.jdbcx.sqlserver.SQLServerDataSource
2. com.microsoft.jdbcx.sqlserver.SQLServerDataSourceFactory
3. com.microsoft.mspool.PoolDataSource
4. com.microsoft.mspool.PoolDataSourceFactory
5. com.microsoft.mspool.Pool
6. com.microsoft.mspool.Pool$ConnectionEventManager
7. com.microsoft.mspool.Pool$PoolMonitor
8. com.microsoft.mspool.PoolHandler
Configuratin Files:
1. JNDI.properties
2. pool.log
Setting up Sample connection Pool manager in Tomcat4.0:
=======================================================
1. Register DataSource. Specify the datasource class to be used and values
needed to
initialize the DataSource class.
Example:
<Resource name="jdbc/sql2000" auth="Container" type="
com.microsoft.jdbcx.sqlserver.SQLServerDataSource"/>
<ResourceParams name="jdbc/sql2000">
<parameter><name>factory</name><value>
com.microsoft.jdbcx.sqlserver.SQLServerDataSourceFactory</value></parameter>
<parameter><name>driverClassName</name><value>
com.microsoft.jdbcx.sqlserver.SQLServerDataSource</value></parameter>
<parameter><name>driverName</name><value>SQLServer</value></parameter>
<parameter><name>description</name><value>SQL Server
DataSource</value></parameter>
<parameter><name>serverName</name><value>localhost</value></parameter>
<parameter><name>portNumber</name><value>1433</value></parameter>
<parameter><name>selectMethod</name><value>cursor</value></parameter>
<parameter><name>databaseName</name><value>northwind</value></parameter>
<parameter><name>user</name><value>user</value></parameter>
<parameter><name>password</name><value>password</value></parameter>
<parameter><name>loginTimeout</name><value>3000</value></parameter>
</ResourceParams>
2. Register PoolDataSource. Here we specify the pool related properties and
a reference to the DataSource
registered in step1.
Example:
<Resource name="jdbc/mspool" auth="Container" type="
com.microsoft.mspool.PoolDataSource"/>
<ResourceParams name="jdbc/mypool">
<parameter><name>factory</name><value>
com.microsoft.mspool.PoolDataSourceFactory</value></parameter>
<parameter><name>className</name><value>com.microsoft.mspool.PoolDataSource
</value></parameter>
<parameter><name>description</name><value>MS_SQL_Pool_DataSource</value></parameter>
<parameter><name>poolName</name><value>wdserverpool</value></parameter>
<parameter><name>maxConnections</name><value>20</value></parameter>
<parameter><name>minConnections</name><value>1</value></parameter>
<parameter><name>checkEvery</name><value>20000</value></parameter>
<parameter><name>idleTimeout</name><value>500000</value></parameter>
<parameter><name>dsJndiName</name><value>jdbc/sql2000</value></parameter>
</ResourceParams>
=======================Extract End=========================
Here is the test page provided by Microsoft in the zip file:
=======================Extract Starts=========================
<HTML>
<HEAD>
<TITLE>
Test Database Connection using Sample connectionpool manager with
Microsoft's Type 4 SQL Server 2000 JDBC Driver
</TITLE>
</HEAD>
<BODY>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<%
String thisLine="";
String query = "Select * from Jobs";
Connection dbConnection = null;
Statement dbStatement = null;
ResultSet dbResultSet = null;
PooledConnection pc =null;
Context ctx = new InitialContext();
Context envCtx=(Context)ctx.lookup("java:comp/env");
%>
<p><B>Got intital Context java:/comp/env/ .......<B></p>
<b>Trying to lookup.................<B><br>
<%
DataSource ds = (DataSource)envCtx.lookup("jdbc/mspool");
%>
<b>Look up successs.........<B><br>
<% if(ds!=null){
dbConnection = ds.getConnection();
dbStatement = dbConnection.createStatement();
dbResultSet = dbStatement.executeQuery(query);
dbResultSet.next(); %>
<center>
<table width=80% border=2 cellspacing=2 cellpadding=3>
<% int i = 2;
while(dbResultSet.next()) {
thisLine = "";
thisLine = "<td>";
thisLine += dbResultSet.getString(i);
thisLine += "</td>"; %>
<TR bgcolor="white"><%= thisLine %></TR>
<% }
dbResultSet.close();
dbStatement.close();
dbConnection.close();
}else { %>
<center>
<table width=80% border=2 cellspacing=2 cellpadding=3>
thisLine = "DataSource not created";
<TR bgcolor="white"><%= thisLine %></TR>
<% }
%>
</BODY>
</HTML>
=======================Extract End=========================
