You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Frank Burns <fr...@the-hub.demon.co.uk> on 2004/07/16 03:56:43 UTC
Is anyone successfully using DBCP with mySQL?
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: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Is anyone successfully using DBCP with mySQL?
Posted by David Smith <dn...@cornell.edu>.
No problem. Glad to hear you got it working!
--David
Frank Burns wrote:
>David,
>That works perfectly!
>Thank you VERY much for the time and trouble you took to help. Very much
>appreciated.
>Best wishes,
>Frank.
>
>----- Original Message -----
>From: "David Smith" <dn...@cornell.edu>
>To: "Tomcat Users List" <to...@jakarta.apache.org>
>Sent: Friday, July 16, 2004 3:28 AM
>Subject: Re: Is anyone successfully using DBCP with mySQL?
>
>
>
>
>>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: tomcat-user-unsubscribe@jakarta.apache.org
>>>For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>>>
>>>
>>>
>>>
>>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
>>For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>>
>>
>>
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
>For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Is anyone successfully using DBCP with mySQL?
Posted by Frank Burns <fr...@the-hub.demon.co.uk>.
David,
That works perfectly!
Thank you VERY much for the time and trouble you took to help. Very much
appreciated.
Best wishes,
Frank.
----- Original Message -----
From: "David Smith" <dn...@cornell.edu>
To: "Tomcat Users List" <to...@jakarta.apache.org>
Sent: Friday, July 16, 2004 3:28 AM
Subject: Re: Is anyone successfully using DBCP with mySQL?
> 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: tomcat-user-unsubscribe@jakarta.apache.org
> >For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
> >
> >
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Is anyone successfully using DBCP with mySQL?
Posted by David Smith <dn...@cornell.edu>.
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: tomcat-user-unsubscribe@jakarta.apache.org
>For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org