Connection pool nightmare!

[MANOHARAN,MADHAN (HP-PaloAlto,ex1)]

Hi guys,
  I bet there were many postings on this issue before.  I searched the
archive, but couldn't figure out a way to solve this and so posting again.
I use SQL Server 2000, JDBC drivers from MS for SQL Server 2000, Tomcat
4.1.6, windows XP.  I was trying to implement connection pooling and this is
what I did

1) created a connection pool from tomcat admin(pointing to
http://localhost:8080/admin) ).  I did use the datasource driver from
MS(com.microsoft.jdbcx.sqlserver.SQLServerDataSource) for the
driverclassname.
2) created a web.xml with the Resource reference entry
3) created a simple java class where I lookup the context via jndi and try
to get a connection.
4) created a jsp to call the java class.

When I hit the jsp, it calls the java class.  The java class looks up the
datasource and finds it( I guess), but when I try to get the connection, I
get this error

java.sql.SQLException: Cannot load JDBC driver class 'null'
        at
org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.jav
a:529)
        at
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:3
12)
        at foo.DBTest.init(DBTest.java:32)
        at org.apache.jsp.test_jsp._jspService(test_jsp.java:50)
        at
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:136)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

>From what I see, Tomcat is not able to load the driver class for some
reason.  The jar files are in the classpath and also in the tomcat common
lib directory.

This is the method in the java program which tries to use the pool:

public void init() {
    try{
      Context ctx = new InitialContext();
      if(ctx == null )
          throw new Exception("Boom - No Context");
          Context env = (Context)ctx.lookup("java:comp/env/jdbc");
          System.out.println("Environ contxt   "+env);
          Enumeration e = env.list("");
          while(e.hasMoreElements())
          {
                  System.out.println("Environment element
"+e.nextElement());
          }
      DataSource ds =(DataSource)env.lookup("CPSDataSource");
      System.out.println("Lookup successful...");
      if (ds != null) {
        Connection conn = ds.getConnection();
        if(conn != null)  {
            foo = "Got Connection "+conn.toString();
            Statement stmt = conn.createStatement();
            ResultSet rst =
                stmt.executeQuery(
                  "select ID from TEST");
            if(rst.next()) {
               foo=rst.getString(2);
               bar=rst.getInt(3);
            }
            conn.close();
        }
      }
    }catch(Exception e) {
      e.printStackTrace();
    }
 }

I also tried looking up "jdbc/CPSDataSource" from the "java:comp/env"
context; I'm looking up the "java:comp/env/jdbc" to make sure the
CPSDataSource context exists and print the context variables in the program.

Could someone throw some light on whatz wrong or why this fails?

Thanks,
Madhan

--
To unsubscribe, e-mail:   <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>