Re: Is anyone successfully using DBCP with mySQL?

[David Smith]

I run this exact setup with DBCP and MySQL in production.  First, make 
sure the mysql jar file is placed in common/lib with the dbcp.jar file.

In your server.xml file:
<parameter>
 <name>dataSourceClassName</name>
 <value>com.mysql.jdbc.jdbc2.optional.MysqlDataSource</value>
</parameter>
should be
<parameter>
 <name>dataSourceClassName</name>
 <value>com.mysql.jdbc.Driver</value>
</parameter>
Replace the serverName, databaseName, and port parameters with one url 
parameter:

<parameter>
 <name>url</name>
 <value>jdbc:mysql://localhost:3306/amx?autoReconnect=true</value>
</parameter>
username and password are both fine as typed.  No caps required.
In web.xml, replace that whole long com.mysql....MysqlDatasource with 
javax.sql.Datasource.  I can't comment on the res-sharing-scope since I 
don't use it.  The test code looks good on the surface.  Now restart 
your Tomcat server to make all these changes live.

That should do it.  If you still have problem, post your exception 
report from the logs so we can see what's happening.

Good luck.
--David
Frank Burns wrote:
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]
 


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