Thanks for the help Steve, here is the code that works.......
try {
javax.naming.Context ctx = new javax.naming.InitialContext();
javax.sql.DataSource ds =
(javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/testDB");
Connection conn = ds.getConnection();
/////////////////////////////////////////////////////////////////////////////////////
BEGIN WORKAROUND
org.apache.commons.dbcp.DelegatingStatement dste =
(org.apache.commons.dbcp.DelegatingStatement)conn.createStatement();
Statement ste = dste.getDelegate();
/////////////////////////////////////////////////////////////////////////////////////
END WORKAROUND
ste.execute("INSERT INTO test (name) VALUES ('test name') ");
ResultSet rs = ste.getGeneratedKeys();
rs.next();
out.print(rs.getInt("GENERATED_KEY"));
}
catch ( Exception e ) {
out.print(e.getMessage());
e.printStackTrace();
}
The root cause is that dbpc's org.apache.commons.dbcp.DelegatingStatement has a
passthru method for every Statement method as defined by the jdbc2 version of the
Statement Interface.
The DelegatingStatement then simply calls the corresponding Statement method i.e.
public int executeUpdate(String sql) throws SQLException { checkOpen(); return
_stmt.executeUpdate(sql);}
What is needed is the following....
public ResultsSet getGeneratedKeys() throws SQLException { checkOpen(); return
_stmt.getGeneratedKeys();}
Of course the more appropriate way of doing it would be to implement the entire jdbc3
version of the Statement interface (as well as the other APIs in jdbc).
Perhaps if I get time I will give it a shot....
On Monday 21 October 2002 12:36 pm, [EMAIL PROTECTED] wrote:
> Hmmm,
>
> Thats an interesting one, as DelegatingStatement is just a wrapper class
> round a regular Statement...And according to the javadocs it inherits the
> getGeneratedKeys() from the Statement!
>
> You can get access to the wrapped Statement class with
> ((DelegatingStatement)ste).getDelegate() - You could maybe try calling the
> getGeneratedKeys() on that and see what happens! Its a bit kludgey as you
> really shouldnt have to cast the Statement - but javac/jasper will choke if
> you dont I think!
>
> Without having a JDBC 3.0 driver to try it out myself thats the best I can
> say - hope that helps!
>
> Steve
>
>
>
> -----Original Message-----
> From: shawn [mailto:shawn@;wnsi.net]
> Sent: Monday, October 21, 2002 2:19 PM
> To: Tomcat Users List
> Subject: Re: Unable to use getGeneratedKeys() through dbpc
>
>
> Thanks for the input Steve,
>
> I agree that it SHOULDN'T be dbpc, but it looks as if it is.
> Actually the driver does support it quite nicely. Everything works great
> if I create my connection manually.
> Things only break if I retrieve the connection from jndi ( and thus dbpc ).
>
> //This can use getGeneratedKeys()
> Class.forName("com.mysql.jdbc.Driver").newInstance();
> Connection conn =
> DriverManager.getConnection("jdbc:mysql://localhost:3306/testDB?user="+dbUs
>e r+"&password="+dbPass);
>
> //This cannot......
> javax.naming.Context ctx = new javax.naming.InitialContext();
> javax.sql.DataSource ds =
> (javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/testDB");
> Connection conn = ds.getConnection();
>
> As things stand now I must either give up connection pooling ( not a good
> option ) or create a workaround through sql ( decreased performance
> through through additional queries ).
>
> Anybody know a way I can have both?
>
> On Monday 21 October 2002 11:54 am, [EMAIL PROTECTED] wrote:
> > Shawn,
> >
> > It shouldnt be DBCP that would cause getGeneratedKeys() to not work - its
> > the JDBC Driver that doesnt support it - my (limited) knowledge of the
> > various different databases is that very few support the JDBC 3.0
> > specification yet so you might have to do a work-around.
> >
> > Its worth checking the MySQL web page to see what it supports right now
>
> but
>
> > my guess is some subset of JDBC 2.0.....
> >
> > You'll probably HAVE to do a workaround by retrieving the new key or
> > something else!
> >
> > Hope this helps,
> > Steve
> >
> > -----Original Message-----
> > From: shawn [mailto:shawn@;wnsi.net]
> > Sent: Monday, October 21, 2002 1:36 PM
> > To: [EMAIL PROTECTED]
> > Subject: Unable to use getGeneratedKeys() through dbpc
> >
> >
> > Hi folks,,,,,
> >
> > It seems to me that dbpc does not support getGeneratedKeys(). If I
> > understand correctly this is a jdbc v3 call and it is possible that dbpc
> > will not yet support jdbc3.
> > I would appreciate any suggestions on alternative pooling mechanisms
> > that could support this (Or any corrections if I am just being dumb).
> > I would really rather not rely on queries to retrieve the new key
> > after the insert.
> >
> > Here are the setup details......
> >
> > RedHat 7.3
> > Tomcat 4.1.2
> > MySQL Connector/J 3.0.1
> >
> > dbpc is working from within JSP, beans and servlets great, but chokes
> > when requesting the generated key after an insert. Here is some code and
> > exceptions........
> >
> >
> > try {
> >
> > //Doing non-pooled connection works
> > Class.forName("com.mysql.jdbc.Driver").newInstance();
> > Connection conn =
>
> DriverManager.getConnection("jdbc:mysql://localhost:3306/testDB?user="+dbUs
>
> >e r+"&password="+dbPass);
> >
> > /*
> > //Doing dbpc provided connection throws an exception
> > javax.naming.Context ctx = new javax.naming.InitialContext();
> > javax.sql.DataSource ds =
> > (javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/testDB");
> > Connection conn = ds.getConnection();
> > */
> >
> > Statement ste = conn.createStatement();
> > ste.execute("INSERT INTO test (name) VALUES ('test name') ");
> >
> > ResultSet rs = ste.getGeneratedKeys();
> > rs.next();
> > out.print(rs.getInt("GENERATED_KEY"));
> > }
> > catch ( Exception e ) {
> > out.print(e.getMessage());
> > e.printStackTrace();
> > }
> >
> >
> > Here the message and stack trace
> > org.apache.jasper.JasperException:
>
> org.apache.commons.dbcp.DelegatingStatement.getGeneratedKeys()Ljava/sql/Res
>
> >u ltSet;
> > at org.apache.jasper.servlet.JspServletWrapper.service(Unknown
> > Source)
> > at org.apache.jasper.servlet.JspServlet.serviceJspFile(Unknown
> > Source)
> > at org.apache.jasper.servlet.JspServlet.service(Unknown Source)
> > at javax.servlet.http.HttpServlet.service(HttpServlet.java)
> > at
> > org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Unknown
> > Source)
> > at
>
> org.apache.catalina.core.ApplicationFilterChain.doFilter(Unknown
>
> > Source)
> > at org.apache.catalina.core.StandardWrapperValve.invoke(Unknown
> > Source)
> > at
>
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo
>
> >k eNext(Unknown Source)
> > at org.apache.catalina.core.StandardPipeline.invoke(Unknown
>
> Source)
>
> > at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
> > at org.apache.catalina.core.StandardContextValve.invoke(Unknown
> > Source)
> > at
>
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo
>
> >k eNext(Unknown Source)
> > at org.apache.catalina.core.StandardPipeline.invoke(Unknown
>
> Source)
>
> > at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
> > at org.apache.catalina.core.StandardContext.invoke(Unknown
> > Source) at org.apache.catalina.core.StandardHostValve.invoke(Unknown
> > Source) at
>
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo
>
> >k eNext(Unknown Source)
> > at org.apache.catalina.valves.ErrorDispatcherValve.invoke(Unknown
> > Source)
> > at
>
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo
>
> >k eNext(Unknown Source)
> > at org.apache.catalina.valves.ErrorReportValve.invoke(Unknown
> > Source)
> > at
>
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo
>
> >k eNext(Unknown Source)
> > at org.apache.catalina.core.StandardPipeline.invoke(Unknown
>
> Source)
>
> > at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
> > at org.apache.catalina.core.StandardEngineValve.invoke(Unknown
> > Source)
> > at
>
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo
>
> >k eNext(Unknown Source)
> > at org.apache.catalina.core.StandardPipeline.invoke(Unknown
>
> Source)
>
> > at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
> > at
> > org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
> > at
> > org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:405
> >) at
>
> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConn
>
> >e ction(Http11Protocol.java:380)
> > at
> > org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:508
> >) at
>
> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.ja
>
> >v a:533)
> > at java.lang.Thread.run(Thread.java:536)
> > ----- Root Cause -----
> > javax.servlet.ServletException:
>
> org.apache.commons.dbcp.DelegatingStatement.getGeneratedKeys()Ljava/sql/Res
>
> >u ltSet;
> > at
> > org.apache.jasper.runtime.PageContextImpl.handlePageException(Unknown
> > Source)
> > at
>
> org.apache.jsp.testGeneratedKeys_jsp._jspService(testGeneratedKeys_jsp.java
>
> >: 81)
> >
> > at org.apache.jasper.runtime.HttpJspBase.service(Unknown Source)
> > at javax.servlet.http.HttpServlet.service(HttpServlet.java)
> > at org.apache.jasper.servlet.JspServletWrapper.service(Unknown
> > Source)
> > at org.apache.jasper.servlet.JspServlet.serviceJspFile(Unknown
> > Source)
> > at org.apache.jasper.servlet.JspServlet.service(Unknown Source)
> > at javax.servlet.http.HttpServlet.service(HttpServlet.java)
> > at
> > org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Unknown
> > Source)
> > at
>
> org.apache.catalina.core.ApplicationFilterChain.doFilter(Unknown
>
> > Source)
> > at org.apache.catalina.core.StandardWrapperValve.invoke(Unknown
> > Source)
> > at
>
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo
>
> >k eNext(Unknown Source)
> > at org.apache.catalina.core.StandardPipeline.invoke(Unknown
>
> Source)
>
> > at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
> > at org.apache.catalina.core.StandardContextValve.invoke(Unknown
> > Source)
> > at
>
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo
>
> >k eNext(Unknown Source)
> > at org.apache.catalina.core.StandardPipeline.invoke(Unknown
>
> Source)
>
> > at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
> > at org.apache.catalina.core.StandardContext.invoke(Unknown
> > Source) at org.apache.catalina.core.StandardHostValve.invoke(Unknown
> > Source) at
>
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo
>
> >k eNext(Unknown Source)
> > at org.apache.catalina.valves.ErrorDispatcherValve.invoke(Unknown
> > Source)
> > at
>
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo
>
> >k eNext(Unknown Source)
> > at org.apache.catalina.valves.ErrorReportValve.invoke(Unknown
> > Source)
> > at
>
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo
>
> >k eNext(Unknown Source)
> > at org.apache.catalina.core.StandardPipeline.invoke(Unknown
>
> Source)
>
> > at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
> > at org.apache.catalina.core.StandardEngineValve.invoke(Unknown
> > Source)
> > at
>
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo
>
> >k eNext(Unknown Source)
> > at org.apache.catalina.core.StandardPipeline.invoke(Unknown
>
> Source)
>
> > at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
> > at
> > org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
> > at
> > org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:405
> >) at
>
> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConn
>
> >e ction(Http11Protocol.java:380)
> > at
> > org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:508
> >) at
>
> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.ja
>
> >v a:533)
> > at java.lang.Thread.run(Thread.java:536)
--
To unsubscribe, e-mail: <mailto:tomcat-user-unsubscribe@;jakarta.apache.org>
For additional commands, e-mail: <mailto:tomcat-user-help@;jakarta.apache.org>
