tomcat-5 jndi issues

I've seen this issue described on various forums and am in need of
an answer or solution. This is in reference to obtaining DataSource
for tomcat DBCP. I am running Tomcat 5.

Code snippet I've seen shows DataSource being obtained but error when
creating the connection from the DataSource.

I am only showing relevant pieces of code of the servlet stub I am running.
My comments and location of error are included:

// Get initial context.
// Result: OK
//
initCtx = new InitialContext();
envCtx = (Context)initCtx.lookup( "java:comp/env" );

// Get data source
// Result:OK
//
ds = (DataSource)envCtx.lookup( "jdbc/TestDBCP" );

// Obtain connection.
// Result: Exception
//
java.sql.Connection conn = ds.getConnection();

The exception thrown is:
Exception: [org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null', cause: No suitable driver]


My relevant web.xml entries:
---------------------------
<web-app>
<display-name>Welcome to Arm Database</display-name>
<description>
Welcome to Arm Database
</description>

<resource-ref>
<description>Connection Pool</description>
<res-ref-name>jdbc/TestDBCP</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
. . .
<web-app>

Relevant entries from server.xml:
--------------------------------
...
<Resource
name="jdbc/TestDBCP"
auth="Container"
type="javax.sql.DataSource"/>

<ResourceParams name="jdbc/TestDBCP">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost:3306/arm?autoReconnect=true</value>
</parameter>
<parameter>
<name>password</name>
<value>MyPassword</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>4</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>5000</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>org.gjt.mm.mysql.Driver</value>
</parameter>
<parameter>
<name>username</name>
<value>MyUser</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>2</value>
</parameter>
</ResourceParams>
...

This is the way it is currently set up. Keep in mind that I could not even obtain a datasource
until I found some posts advising setting autoDeploy="false". When I did that I could get the
datasource. However, it does not work unless I have the resource-ref entries in web.xml.

Further, if I just attempt to get the Environment entry from server.xml, failure results:

<!-- Test entry for demonstration purposes -->
<Environment name="simpleValue" type="java.lang.Integer" value="30"/>

FAILS: Integer simpleValue = (Integer)envCtx.lookup( "simpleValue" );
Exception: envCtx.lookup(simpleValue): [javax.naming.NameNotFoundException: Name simpleValue is not bound in this Context]

This leads me to ask if this is more than just a database connection pool issue. Bottom line,
I cannot obtain elements from server.xml no matter where I put them in the server.xml file.

If it is just a matter of digging through documentation for solutions, please point to the doc. Complete examples would
work wonders, not just snippets.

Thank you for any help,
Dave

I spent some time working through just this issue and now have a tomcat-5 connection pool working against mysql.

Here are the applicable software versions:

tomcat: v5.0.27
mysql: Ver 12.22 Distrib 4.0.20

0. Shutdown tomcat.

1. If not already present, copy the following (recent) versions of the mysql connector and commons-* jars
to $TOMCAT_HOME/common/lib. Delete any old ones.

mysql-connector-java-3.0.14-production-bin.jar
commons-collections-2.1.1.jar
commons-dbcp-1.2.1.jar

2. Ignore the advice in the tomcat-5 docs on jakarta.apache.org telling you to modify server.xml. Tomcat 5 uses a minimalist server.xml and now does application-specific configuration in separate files. If you have mucked up your server.xml either by hand editing or using the tomcat manager app, start fresh. Shutdown tomcat and copy a new minimalist server.xml in place.

If your webapp is called foo, create a file $TOMCAT_HOME/conf/Catalina/localhost/foo.xml. Save a copy of foo.xml in another location - the working copy is deleted if you undeploy foo using the tomcat manager.

If your mysql database is called sampdb, the host serving mysql is mysqlhost.mydomain.com, and your mysql user/password for schema sampdb is sampadm/xxx add the following to foo.xml:

<?xml version='1.0' encoding='utf-8'?>
<Context crossContext="true" debug="5" docBase="foo" path="/foo" reloadable="true">
<Logger className="org.apache.catalina.logger.FileLogger" prefix="localhost_foo_log." suffix=".txt" timestamp="true"/>
<Resource name="jdbc/sampdb" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/sampdb">
<parameter>
<name>url</name>
<value>jdbc:mysql://mysqlhost.mydomain.com:3306/sampdb?autoReconnect=true</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value>select now()</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>30</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>10</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>username</name>
<value>sampadm</value>
</parameter>
<parameter>
<name>password</name>
<value>xxx</value>
</parameter>
</ResourceParams>
</Context>

Note that the above also declares a separate file for logging output from foo (localhost_foo_log.txt).

4. In your application you can obtain a connection from the pool as advertised in the tomcat docs.

Here is some template code to open a connection using the pool and then print the contents of the member table from the mysql sample database:

package mypackage;

import javax.naming.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.*;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

public class simpleDBCP extends HttpServlet {

// ***** Servlet access to data base


public void doGet (HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException
{
String query = "SELECT * FROM member";

try{
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
if(envCtx == null )
throw new Exception("Boom - No Environment Context");
// the following matches the resource name defined in foo.xml
DataSource ds =
(DataSource) envCtx.lookup("jdbc/sampdb");

if (ds != null) {
Connection conn = ds.getConnection();

if(conn != null) {
Statement stmt = conn.createStatement ();
ResultSet rs = stmt.executeQuery (query);
printResultSet ( resp, rs );
// important to cleanup
rs.close();
stmt.close();
conn.close();
} else {
throw new Exception("No Connection");
}
} else {
throw new Exception("No Datasource");
}
} catch(SQLException ex) {
PrintWriter out = resp.getWriter();
resp.setContentType("text/html");

while (ex != null) {
out.println ("SQL Exception: " + ex.getMessage ());
ex = ex.getNextException ();
} // end while

} // end catch SQLException
catch(NamingException ex) {
ex.printStackTrace();
}
catch(Exception ex) {
ex.printStackTrace();
}
} // end doGet


private void printResultSet ( HttpServletResponse resp, ResultSet rs )
throws SQLException {

try {

PrintWriter out = resp.getWriter();

out.println("<html>");
out.println("<head><title>rnm simpleDBCP</title></head>");
out.println("<body>");
out.println("<center><font color=AA0000>");
out.println("<h3>rnm Sample Database Connection Pool Servlet</h3>");
out.println("<h3>Data Retrieved:</h3>");

out.println("<table border='1'>");

int numCols = rs.getMetaData().getColumnCount ();
while ( rs.next() ) {
out.println("<tr>");
for (int i=1; i<=numCols; i++) {
out.print("<td>" + rs.getString(i) + "</td>" );
} // end for
out.println("</tr>");
} // end while

out.println("</table>");

out.println("</font></center>");
out.println("</body>");
out.println("</html>");
out.close();

} // end try
catch ( IOException ex) {
ex.printStackTrace();
} // end catch

} // end returnHTML

} // end simpleDBCP

5. Deploy foo.war and start tomcat. Be on the lookout for complaints from tomcat (maybe unable to login using the provided user/password, etc.).


** Additional info (unrelated to DBCP) **

If you wish to create a resource link, say to a globally defined mail resource, you can add something like the following after the closing ResourceParams tag:

<ResourceLink global="mail/Session" name="mail/Session" type="javax.mail.Session"/>

The above expects that a global resource mail/Session has been defined in server.xml as follows (note that this can be done via the tomcat manager application as well):

<Resource name="mail/Session" type="javax.mail.Session"/>
<ResourceParams name="mail/Session">
<parameter>
<name>mail.smtp.host</name>
<value>smtp_relay</value>
</parameter>
</ResourceParams>

Hope this helps.
- Rick

I've been hitting my head against a brick wall for a few hours here today with this null url problem when I came across your post. I'm surprised that this isn't better documented by tomcat! Anyway, when I moved my context out of the server.xml and into it's own config xml file the dbcp datasource worked like a charm.

Thanks again!

Dave

I'm trying to get DBCP working with MySQL (4.1) and Tomcat (5.0.28). I can follow these instructions and get everything working... but, what if my web app is not called foo?

In other words, I don't want to type http://localhost/foo/test.jsp - I want to type http://localhost/test.jsp directly. When I do that I get an error message, "No suitable driver found" I thought I'd reconfigured everything everything correctly for the default (ROOT) app, but I must be missing something!

Does anyone know how to apply these instructions for the case of the default (ROOT) Tomcat web app? Or does anyone know of any other instructions to get it working for the default web app?

Thanks!!!

Frustrated