Re: Patch for Statement.getGeneratedKeys() - Mailing list pgsql-jdbc

Kris, all: please see the changes in AbstractJdbc3Statement, the
regclass technique suggested by Tom is implemented and working. However
I have not made any attempt to manage the separate generated-keys
resultset nor state (RETURN_GENERATED_KEYS).

Please consider making these changes yourself, or tell me what needs to
be done (again I am not proficient with the spec or state mgmt for the
calls). I think your deeper knowledge of the driver and spec will
produce better quality code. I am losing some cycles starting next week
so won't be able to work on this for several weeks or more.

Thanks,
Ken
# This patch file was generated by NetBeans IDE
# This patch can be applied using context Tools: Apply Diff Patch action on respective folder.
# It uses platform neutral UTF-8 encoding.
# Above lines and this line are ignored by the patching process.
Index: pgjdbc/org/postgresql/core/Utils.java
--- pgjdbc/org/postgresql/core/Utils.java Base (1.6)
+++ pgjdbc/org/postgresql/core/Utils.java Locally Modified (Based On 1.6)
@@ -12,6 +12,7 @@
 package org.postgresql.core;

 import java.sql.SQLException;
+import java.util.ArrayList;

 import org.postgresql.util.GT;
 import org.postgresql.util.PSQLException;
@@ -146,4 +147,65 @@

         return sbuf;
     }
+
+
+    /**
+     * Return an ArrayList of Strings representing the table identifiers, quoted or not.
+     * Any number of ids may exists; no attempt is made to validate the maximum number of IDs.
+     * @param sql INSERT INTO stmt
+     * @param start - end-index of the keyword (INTO, FROM, UPDATE etc) preceding
+     * the table reference, after which the <code>catalog.schema.table</code> identifiers appear
+     * @return ArrayList who first element is the left-most identifiers,
+     * and right-most is the table name.
+     * @author Ken Johanon - ken2006 at onnet.cc
+     */
+    public static ArrayList getTableIdentifiers(String sql, int start)
+    {
+        if (start<0)//assertion
+            throw new IllegalArgumentException("getInsertIds: invalid start index: -1");
+        //advance to first alnum
+        for (; start<sql.length(); start++)
+            if (Character.isLetterOrDigit(sql.charAt(start)))
+                break;
+        //advance to first non-quoted, non-alnum
+        ArrayList ar = new ArrayList(3);
+        int end = start;
+        int pos = start;
+        boolean inQuote = sql.charAt(end-1)=='"';
+        for (; end<sql.length(); end++)
+        {
+            char c = sql.charAt(end);
+            if (inQuote)
+            {
+                if (c=='"')
+                {
+                    ar.add(sql.substring(pos, end));
+                    end++;
+                    pos = end+1;
+                    inQuote = false;
 }
+            }
+            else
+            {
+                if (c=='"')
+                {
+                    inQuote = true;
+                    pos = end+1;
+                }
+                else if (c=='.')
+                {
+                    ar.add(sql.substring(pos, end));
+                    pos = end+1;
+                }
+            }
+
+            if (c=='(' || (!inQuote && Character.isSpaceChar(c)))
+            {
+                if (pos!=end)
+                    ar.add(sql.substring(pos, end));
+                break;
+            }
+        }
+        return ar;
+    }
+}
Index: pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java
--- pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java Base (1.104)
+++ pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java Locally Modified (Based On 1.104)
@@ -286,6 +286,33 @@
     }

     /*
+     * Execute a SQL INSERT, UPDATE or DELETE statement.  In addition
+     * SQL statements that return nothing such as SQL DDL statements
+     * can be executed
+     *
+     * @param sql a SQL statement
+     * @return either a row count, or 0 for SQL commands
+     * @exception SQLException if a database access error occurs
+     */
+    protected int executeUpdateGetResults(String p_sql) throws SQLException
+    {
+        if (preparedQuery != null)
+            throw new PSQLException(GT.tr("Can''t use query methods that take a query string on a
PreparedStatement."),
+                                    PSQLState.WRONG_OBJECT_TYPE);
+        if( isFunction )
+        {
+            executeWithFlags(p_sql, 0);
+            return 0;
+        }
+        checkClosed();
+        p_sql = replaceProcessing(p_sql);
+        Query simpleQuery = connection.getQueryExecutor().createSimpleQuery(p_sql);
+        execute(simpleQuery, null, 0);
+        this.lastSimpleQuery = simpleQuery;
+        return getUpdateCount();
+    }
+
+    /*
      * Execute a SQL INSERT, UPDATE or DELETE statement.  In addition,
      * SQL statements that return nothing such as SQL DDL statements can
      * be executed.
Index: pgjdbc/org/postgresql/jdbc3/AbstractJdbc3Statement.java
--- pgjdbc/org/postgresql/jdbc3/AbstractJdbc3Statement.java Base (1.22)
+++ pgjdbc/org/postgresql/jdbc3/AbstractJdbc3Statement.java Locally Modified (Based On 1.22)
@@ -11,6 +11,7 @@

 import java.math.BigDecimal;
 import java.sql.*;
+import java.util.ArrayList;
 import java.util.Calendar;
 import java.util.Vector;

@@ -19,6 +20,9 @@
 import org.postgresql.core.QueryExecutor;
 import org.postgresql.core.Field;
 import org.postgresql.core.BaseConnection;
+import org.postgresql.core.Utils;
+import org.postgresql.jdbc2.AbstractJdbc2Connection;
+import org.postgresql.jdbc2.AbstractJdbc2Statement.StatementResultHandler;
 import org.postgresql.util.GT;

 /**
@@ -28,6 +32,7 @@
  */
 public abstract class AbstractJdbc3Statement extends org.postgresql.jdbc2.AbstractJdbc2Statement
 {
+
     private final int rsHoldability;

     public AbstractJdbc3Statement (AbstractJdbc3Connection c, int rsType, int rsConcurrency, int rsHoldability) throws
SQLException
@@ -106,7 +111,9 @@
      */
     public ResultSet getGeneratedKeys() throws SQLException
     {
-        return createDriverResultSet(new Field[0], new Vector());
+        return result==null ?
+            createDriverResultSet(new Field[0], new Vector())
+            : result.getResultSet();
     }

     /**
@@ -135,7 +142,7 @@
     {
         if (autoGeneratedKeys == Statement.NO_GENERATED_KEYS)
             return executeUpdate(sql);
-
+        //fix me : impl NO_GENERATED_KEYS & RETURN_GENERATED_KEYS
         throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
     }

@@ -159,11 +166,85 @@
      */
     public int executeUpdate(String sql, int columnIndexes[]) throws SQLException
     {
-        if (columnIndexes.length == 0)
+        if (columnIndexes==null || columnIndexes.length==0)
             return executeUpdate(sql);
-
-        throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
+        String preamble = sql.substring(0, Math.min(sql.length()-1,100)).toUpperCase();
+        int start = -1;
+        String subparse =
+            (start = preamble.indexOf("INSERT"))!=-1 ? "INTO" :
+            (start = preamble.indexOf("DELETE"))!=-1 ? "FROM" :
+            null;
+        if (subparse==null)
+        {//UPDATE
+            start = preamble.indexOf("UPDATE");
+            if (start!=-1) start += 6;
     }
+        else
+        {//INTO or FROM
+            start = preamble.indexOf(subparse, start);
+            if (start!=-1) start += 4;
+        }
+        if (start==-1)//not one of INSERT,DELETE,UPDATE
+            return executeUpdate(sql);
+        ArrayList args = Utils.getTableIdentifiers(sql, start);
+        boolean standardConformingStrings = connection.getStandardConformingStrings();
+        int argLen = args.size();
+        if (argLen==0)
+            throw new PSQLException(GT.tr("Assertion failed: table reference zero elements"),
PSQLState.UNEXPECTED_ERROR);
+        StringBuffer sb = new StringBuffer(argLen*20);
+        if (argLen>2)
+        {
+            sb.append('"');
+            Utils.appendEscapedLiteral(sb,args.get(argLen-3).toString(), standardConformingStrings);
+            sb.append('"');
+            sb.append('.');
+        }
+        if (argLen>1)
+        {
+            sb.append('"');
+            Utils.appendEscapedLiteral(sb,args.get(argLen-2).toString(), standardConformingStrings);
+            sb.append('"');
+            sb.append('.');
+        }
+        sb.append('"');
+        Utils.appendEscapedLiteral(sb,args.get(argLen-1).toString(), standardConformingStrings);
+        sb.append('"');
+        String tblRef = sb.toString();
+        String pgCols =
+            "SELECT attname "+
+            "FROM pg_attribute "+
+            "WHERE attrelid = '"+tblRef+"'::regclass "+
+            "AND attnum > 0 "+
+            "AND NOT attisdropped "+
+            "ORDER BY attnum ASC";
+        String[] columnNames = new String[columnIndexes.length];
+        boolean isOutOfBoundsEx = false;
+        ResultSet rs = null;
+        try {
+            rs = this.executeQuery(pgCols);
+            int j=0;
+            try {
+                for (; j<columnNames.length; j++)
+                {
+                    rs.absolute(columnIndexes[j]);
+                    columnNames[j] = rs.getString(1);
+                }
+            } catch (SQLException ex) {//invalid column-index provided
+                if (rs.getRow()==0)
+                    throw new PSQLException(GT.tr("Table OID not found")+": "+tblRef, PSQLState.INVALID_NAME);
+                isOutOfBoundsEx = true;
+                throw new PSQLException(GT.tr("Column index out of bounds")+": "+columnIndexes[j],
PSQLState.INVALID_PARAMETER_VALUE);
+            }
+        } catch (SQLException ex) {
+            if (isOutOfBoundsEx)
+                throw ex;
+            //in executeQuery:
+            throw new PSQLException(GT.tr("Cannot translate column name indexes"), PSQLState.UNEXPECTED_ERROR, ex);
+        } finally {
+            if (rs!=null) rs.close();
+        }
+        return executeUpdate(sql, columnNames);
+    }

     /**
      * Executes the given SQL statement and signals the driver that the
@@ -184,12 +265,33 @@
      */
     public int executeUpdate(String sql, String columnNames[]) throws SQLException
     {
-        if (columnNames.length == 0)
+        if (columnNames==null || columnNames.length == 0)
             return executeUpdate(sql);
-
-        throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
+        int args = columnNames.length;
+        if (!connection.haveMinimumServerVersion("8.2"))
+            throw new PSQLException(GT.tr("Server version does not support returning generated keys: < 8.2"),
PSQLState.NOT_IMPLEMENTED);
+        if (args==0)
+            return executeUpdate(sql);
+        StringBuffer s = new StringBuffer(sql.length()+(args*32));
+        s.append(sql);
+        s.append('\n');
+        s.append("RETURNING");
+        s.append(' ');
+        for (int i=0; i<args; i++)
+        {
+            String arg = columnNames[i];
+            if (arg==null)
+                throw new PSQLException(GT.tr("Null value in columnNames"), PSQLState.INVALID_PARAMETER_VALUE);
+            if (i!=0)
+                s.append(',');
+            s.append(Utils.appendEscapedIdentifier(null,arg));
     }
+        return executeUpdateGetResults(s.toString());
+        //throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
+    }

+
+
     /**
      * Executes the given SQL statement, which may return multiple results,
      * and signals the driver that any