Re: Unable to use getGeneratedKeys() through dbpc

[Martin Jacobson]

shawn wrote:
 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=-
 +dbUser+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 ).


I don't think you need worry about the performance - even if the driver 
supports the getGeneratedKeys() method, it still has to make an sql 
query (SELECT LAST_INSERT_ID() for MySQL) to get the information.
The only advantage is that getGeneratedKeys() is db-independent.

Martin


--
To unsubscribe, e-mail:   mailto:tomcat-user-unsubscribe;jakarta.apache.org
For additional commands, e-mail: mailto:tomcat-user-help;jakarta.apache.org

Re: Unable to use getGeneratedKeys() through dbpc

[shawn]

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

Re: Unable to use getGeneratedKeys() through dbpc

[shawn]

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=+dbUser+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

RE: Unable to use getGeneratedKeys() through dbpc

[SMcGarrity]

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=+dbUse
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/Resu
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.invok
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.invok
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.invok
eNext(Unknown Source)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(Unknown
Source)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(Unknown Source)
at org.apache.catalina.valves.ErrorReportValve.invoke(Unknown
Source)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
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.invok
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.processConne
ction(Http11Protocol.java:380

Unable to use getGeneratedKeys() through dbpc

[shawn]

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=+dbUser+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/ResultSet;
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.invokeNext(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.invokeNext(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.invokeNext(Unknown
 Source)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(Unknown Source)
at 
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown
 Source)
at org.apache.catalina.valves.ErrorReportValve.invoke(Unknown Source)
at 
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(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.invokeNext(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.processConnection(Http11Protocol.java:380)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:508)
at 
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:533)
at java.lang.Thread.run(Thread.java:536)
- Root Cause -
javax.servlet.ServletException: 
org.apache.commons.dbcp.DelegatingStatement.getGeneratedKeys()Ljava/sql/ResultSet;
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