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

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