Is anyone successfully using DBCP with mySQL?

[Frank Burns]

I can't get a simple DBCP with mySQL working.

I've looked at gazillions of howtos and tutorials and trawled the archives,
but they seem to be littered with inconsistencies. For example, the example
on the jakarta site for setting up the JNDI Datasource in server.xml for
mySQL uses the param name "username" , yet the Connector/J documentation
specifies "user".

Is there one simple, perfect example implementation out there that I can
follow?

What am I doing wrong? I get null pointer exceptions for the connection I'm
trying to retrive from the datasource. Here are my details.

Tomcat 5.0.16. MySQL Connector/J 3.0.11.

Here's my server.xml entry:
<Context path="/amx" reloadable="true"
docBase="E:\SoftwareDevelopment\eclipse\workspace\amx"
workDir="E:\SoftwareDevelopment\eclipse\workspace\amx\work">
  <Resource name="jdbc/AmxResources" auth="Container"
type="javax.sql.DataSource"/>
 <ResourceParams name="jdbc/AmxResources">
  <parameter>
   <name>factory</name>
   <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
  </parameter>
  <parameter>
   <name>dataSourceClassName</name>
   <value>com.mysql.jdbc.jdbc2.optional.MysqlDataSource</value>
  </parameter>
  <parameter>
   <name>serverName</name>
   <value>localhost</value>
  </parameter>
  <parameter>
   <name>databaseName</name>
   <value>amx</value>
  </parameter>
  <parameter>
   <name>port</name>
   <value>3306</value>
  </parameter>
  <parameter>
   <name>username</name>
   <value>frank</value>
  </parameter>
  <parameter>
   <name>password</name>
   <value>banana</value>
  </parameter>
 </ResourceParams>
</Context>

Here's my web.xml entry:
  <resource-ref>
   <description>JNDI Datasource for the database</description>
   <res-ref-name>jdbc/AmxResources</res-ref-name>
 <res-type>com.mysql.jdbc.jdbc2.optional.MysqlDataSource</res-type>
   <res-auth>Container</res-auth>
   <res-sharing-scope>Sharable</res-sharing-scope>
  </resource-ref>

Here's test code:
...
  DataSource ds = null;
  Context ctx = null;
  try {
   ctx = new InitialContext();
   ds = (DataSource) ctx.lookup("java:comp/env/jdbc/AmxResources");
  } catch (NamingException ex) {
       application.log("Failed to access JNDI resource: " +
ex.getMessage());
  }

  Statement stmt = null;
  ResultSet rs = null;

  try {
   Connection conn =
   ds.getConnection();

   stmt = conn.createStatement();
   stmt.execute("SELECT * FROM users");
   rs = stmt.getResultSet();
   while (rs.next()) {
    out.print("Username = " + rs.getString("UserName"));
   }
  } catch (SQLException ex) {
   // handle any errors
   out.println("SQLException: " + ex.getMessage());
   out.println("SQLState: " + ex.getSQLState());
   out.println("VendorError: " + ex.getErrorCode());
  } finally {
   if (rs != null) {
    try {
     rs.close();
    } catch (SQLException sqlEx) { // ignore }
     rs = null;
    }
    if (stmt != null) {
     try {
      stmt.close();
     } catch (SQLException sqlEx) { // ignore }
      stmt = null;
     }
    }
   }
  } // end finally




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