Mailing-List: contact users-help@tomcat.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <users@tomcat.apache.org>
Received-SPF: pass (asf.osuosl.org: domain of quietcircle@gmail.com designates
 66.249.92.206 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:to:subject:mime-version:content-type;
        b=puXKlMzWyzi3QGFcmEQZ0tkib455TGRLH/rTcc5X8A6xu1Ms/2EvbrpQMS4D7PzCuC1CODU8OXVGNTeeBhZe3rUWG6C5G2F2E8Oqa2M8tCDEMWqJPrcJnlmob+o90fu2LEqI4kOo9eCRzvW99RnZMLF/dfPY2Kjtn90Kj5YB6hI=
Message-ID: <801de4f50512280916m46d7b1bescf577e4dc5a97a72@mail.gmail.com>
Date: Wed, 28 Dec 2005 11:16:13 -0600
From: Albert Farewege <quietcircle@gmail.com>
To: users@tomcat.apache.org
Subject: problem using connection pooling with Microsoft Sql Server as
 described in kB Article Q313173
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_5292_31325394.1135790173977"

------=_Part_5292_31325394.1135790173977
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 confir=
m
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 th=
e
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 Microsof=
t
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.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3DExtrac=
t Starts=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D
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:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D
1. Register DataSource. Specify the datasource class to be used and values
needed to
   initialize the DataSource class.
Example:
<Resource name=3D"jdbc/sql2000" auth=3D"Container" type=3D"
com.microsoft.jdbcx.sqlserver.SQLServerDataSource"/>
<ResourceParams name=3D"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=3D"jdbc/mspool" auth=3D"Container" type=3D"
com.microsoft.mspool.PoolDataSource"/>
 <ResourceParams name=3D"jdbc/mypool">
 <parameter><name>factory</name><value>
com.microsoft.mspool.PoolDataSourceFactory</value></parameter>
 <parameter><name>className</name><value>com.microsoft.mspool.PoolDataSourc=
e
</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>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3DExtrac=
t End=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D

Here is the test page provided by Microsoft in the zip file:

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3DExtrac=
t Starts=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D
<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=3D"java.sql.*" %>
<%@ page import=3D"javax.sql.*" %>
<%@ page import=3D"javax.naming.*" %>

<%
 String thisLine=3D"";
 String query =3D "Select * from Jobs";
     Connection dbConnection =3D null;
     Statement dbStatement =3D null;
     ResultSet dbResultSet =3D null;
 PooledConnection pc =3Dnull;
 Context ctx =3D new InitialContext();
 Context envCtx=3D(Context)ctx.lookup("java:comp/env");
%>
    <p><B>Got intital Context java:/comp/env/ .......<B></p>
        <b>Trying to lookup.................<B><br>
 <%
   DataSource ds =3D (DataSource)envCtx.lookup("jdbc/mspool");
%>
        <b>Look up successs.........<B><br>
        <% if(ds!=3Dnull){
            dbConnection =3D ds.getConnection();
            dbStatement =3D dbConnection.createStatement();
            dbResultSet =3D dbStatement.executeQuery(query);
            dbResultSet.next(); %>
          <center>
          <table width=3D80% border=3D2 cellspacing=3D2 cellpadding=3D3>
         <%  int i =3D 2;
           while(dbResultSet.next()) {
              thisLine =3D "";
               thisLine =3D "<td>";
               thisLine +=3D dbResultSet.getString(i);
               thisLine +=3D "</td>"; %>
               <TR bgcolor=3D"white"><%=3D thisLine %></TR>
 <% }
  dbResultSet.close();
  dbStatement.close();
  dbConnection.close();
 }else { %>
          <center>
          <table width=3D80% border=3D2 cellspacing=3D2 cellpadding=3D3>
  thisLine =3D "DataSource not created";
     <TR bgcolor=3D"white"><%=3D thisLine %></TR>
  <%   }
  %>
</BODY>
</HTML>


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3DExtrac=
t End=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D

------=_Part_5292_31325394.1135790173977--