Skip site navigation (1) Skip section navigation (2)

Re: Problem with prepareStatement and Statement.RETURN_GENERATED_KEYS in PostgreSQL JDBC driver 8.4

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: Heikki Hiltunen <heikki(dot)hiltunen(at)smilehouse(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Problem with prepareStatement and Statement.RETURN_GENERATED_KEYS in PostgreSQL JDBC driver 8.4
Date: 2010-04-23 15:57:11
Message-ID: 4BD1C357.90506@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Heikki Hiltunen wrote:
> When using 8.4 JDBC drivers, calling prepareStatement(String sql, int
> autoGeneratedKeys) with Statement.RETURN_GENERATED_KEYS seems to add
> "RETURNING *" to the end of the SQL even with select statements.
> According to Javadoc for prepareStatement(String sql, int
> autoGeneratedKeys) in java.sql.Connection:
> 
> "The given constant tells the driver whether it should make
> auto-generated keys available for retrieval. This parameter is ignored
> if the SQL statement is not an INSERT statement, or an SQL statement
> able to return auto-generated keys (the list of such statements is
> vendor-specific). "

Yeah, the driver just blindly tacks a " RETURNING *" to the end of the
SQL string if you specify RETURN_GENERATED_KEYS. I'm tempted to do
something like this:

*** AbstractJdbc3Connection.java	23 Dec 2009 10:28:40 +0200	1.21
--- AbstractJdbc3Connection.java	23 Apr 2010 18:49:44 +0300	
***************
*** 359,364 ****
--- 359,381 ----
      throws SQLException
      {
          checkClosed();
+
+ 	/*
+ 	 * We implement fetching auto-generated keys by tacking a
+ 	 * " RETURNING *" to the end of the string. Don't try to do that
+ 	 * with other statements than INSERT/UPDATE/DELETE.
+ 	 *
+ 	 * XXX this gets fooled by comments at the beginning of the SQL string
+ 	 */
+         if (autoGeneratedKeys != Statement.NO_GENERATED_KEYS)
+         {
+ 	    String trimmedSql = sql.trim();
+ 	    if (!trimmedSql.regionMatches(true, 0, "INSERT", 0, 6) &&
+ 		!trimmedSql.regionMatches(true, 0, "UPDATE", 0, 6) &&
+ 		!trimmedSql.regionMatches(true, 0, "DELETE", 0, 6))
+ 		autoGeneratedKeys = Statement.NO_GENERATED_KEYS;
+ 	}
+
          if (autoGeneratedKeys != Statement.NO_GENERATED_KEYS)
              sql = AbstractJdbc3Statement.addReturning(this, sql, new
String[]{"*"}, false);

But that's not very bullet-proof, and will fail to detect the statement
as an INSERT if it e.g begins with a comment. We could add a mini-parser
to detect comments too, but it's not a very robust approach.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

In response to

pgsql-jdbc by date

Next:From: Heikki LinnakangasDate: 2010-04-23 17:15:15
Subject: Re: Very Dangerous bug in XA connection pooling and SL EJBs with jboss-4.2.3 & latest postgresql-8.4-701.jdbc3.jar
Previous:From: Heikki HiltunenDate: 2010-04-23 12:46:44
Subject: Problem with prepareStatement and Statement.RETURN_GENERATED_KEYS in PostgreSQL JDBC driver 8.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group