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

Kris, please try to apply the attached and let me know what errors if
any you get.

All ids are now quoted in: executeUpdate(String sql, String
columnIndexes[]), and: int executeUpdate(String sql, int
columnIndexes[]) is implemented, however it currently will work ONLY if
the fully qualified table is set in the insert:

INSERT INTO foocatalog.fooschema.tbl .....(quoted or not)

It will support normalizing the not-supplied catalog and schema names --
after I find out how to extract these from the Connection (hopefully
this would not require an additional round trip). Any suggestions on this?

Ken
# This patch file was generated by NetBeans IDE
# Following Index: paths are relative to: C:\dev\java\proj\pgjdbc\pgjdbc
# This patch can be applied using context Tools: Patch action on respective folder.
# It uses platform neutral UTF-8 encoding and \n newlines.
# Above lines and this line are ignored by the patching process.
Index: org/postgresql/jdbc2/AbstractJdbc2Statement.java
*** C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\jdbc2\AbstractJdbc2Statement.java Base (1.104)
--- C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\jdbc2\AbstractJdbc2Statement.java Locally Modified (Based On 1.104)
***************
*** 286,291 ****
--- 286,318 ----
      }

      /*
+      * 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: org/postgresql/jdbc3/AbstractJdbc3Statement.java
*** C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\jdbc3\AbstractJdbc3Statement.java Base (1.21)
--- C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\jdbc3\AbstractJdbc3Statement.java Locally Modified (Based On 1.21)
***************
*** 11,16 ****
--- 11,17 ----

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

***************
*** 19,24 ****
--- 20,28 ----
  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,33 ****
--- 32,38 ----
   */
  public abstract class AbstractJdbc3Statement extends org.postgresql.jdbc2.AbstractJdbc2Statement
  {
+
      private final int rsHoldability;

      public AbstractJdbc3Statement (AbstractJdbc3Connection c, int rsType, int rsConcurrency, int rsHoldability)
throwsSQLException 
***************
*** 106,112 ****
       */
      public ResultSet getGeneratedKeys() throws SQLException
      {
!         return createDriverResultSet(new Field[0], new Vector());
      }

      /**
--- 111,119 ----
       */
      public ResultSet getGeneratedKeys() throws SQLException
      {
!         return result==null ?
!             createDriverResultSet(new Field[0], new Vector())
!             : result.getResultSet();
      }

      /**
***************
*** 135,141 ****
      {
          if (autoGeneratedKeys == Statement.NO_GENERATED_KEYS)
              return executeUpdate(sql);
!
          throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
      }

--- 142,148 ----
      {
          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,172 ****
       */
      public int executeUpdate(String sql, int columnIndexes[]) throws SQLException
      {
!         if (columnIndexes.length == 0)
              return executeUpdate(sql);
!
!         throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
      }

      /**
--- 166,206 ----
       */
      public int executeUpdate(String sql, int columnIndexes[]) throws SQLException
      {
!         if (columnIndexes==null || columnIndexes.length == 0)
              return executeUpdate(sql);
!         String prefix = sql.substring(0,10).toLowerCase();
!         if (columnIndexes==null || prefix.indexOf("insert")==-1)
!         {
!             return executeUpdateGetResults(sql);
          }
+         int start = Utils.position(sql, "INTO", 0);
+         ArrayList args = Utils.getInsertIds(sql, start);
+         String pgCols =
+             "SELECT column_name "+
+             "FROM information_schema.columns "+
+             "WHERE table_catalog='"+args.get(0)+"' AND table_schema='"+args.get(1)+"' AND
table_name='"+args.get(2)+"'"+ 
+             "ORDER BY ordinal_position";
+         ResultSet rs = null;
+         String[] columnNames = new String[columnIndexes.length];
+         try {
+             rs = this.executeQuery(pgCols);
+         } catch (SQLException ex) {
+             throw new PSQLException(GT.tr("Could not translate column name indexes.")+" "+ex,
PSQLState.UNEXPECTED_ERROR);
+         } finally {
+             if (rs!=null) rs.close();
+         }
+         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
+             throw new PSQLException(GT.tr("Column index out of bounds.")+" "+columnIndexes[j],
PSQLState.UNEXPECTED_ERROR);
+         }
+         return executeUpdate(sql, columnNames);
+     }

      /**
       * Executes the given SQL statement and signals the driver that the
***************
*** 184,198 ****
       */
      public int executeUpdate(String sql, String columnNames[]) throws SQLException
      {
!         if (columnNames.length == 0)
              return executeUpdate(sql);
!
!         throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
      }

      /**
       * Executes the given SQL statement, which may return multiple results,
--- 221,262 ----
       */
      public int executeUpdate(String sql, String columnNames[]) throws SQLException
      {
!         String prefix = sql.substring(0,10).toLowerCase();
!         if (columnNames==null || prefix.indexOf("insert")==-1)
!             return executeUpdateGetResults(sql);
!         if (!(connection instanceof AbstractJdbc2Connection))
!         {
!             throw new PSQLException(GT.tr("Driver version does not support returning generated keys.")+"
"+connection.getClass().getName(),PSQLState.NOT_IMPLEMENTED); 
!         }
!         AbstractJdbc2Connection con = (AbstractJdbc2Connection)connection;
!         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 NullPointerException("executeUpdate: null columnName at index "+i);
!                 throw new PSQLException(GT.tr("Null value in columnNames"), PSQLState.INVALID_PARAMETER_VALUE);
!             if (i!=0)
!                 s.append(',');
!             s.append('"');
!             s.append(arg);
!             s.append('"');
          }
+         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


Index: org/postgresql/core/Utils.java
*** C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\core\Utils.java Base (1.6)
--- C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\core\Utils.java Locally Modified (Based On 1.6)
***************
*** 12,17 ****
--- 12,18 ----
  package org.postgresql.core;

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

  import org.postgresql.util.GT;
  import org.postgresql.util.PSQLException;
***************
*** 146,152 ****
--- 147,286 ----

          return sbuf;
      }
+     /**
+      * return true if the string contains whitespace and is not already quoted, false otherwise
+      *
+      * @param in
+      * @return true if the string contains whitespace and is not already quoted
+      * @throws java.sql.SQLException if the string contains quotes inside its value
+      * (foo"bar or "foor"bar"), or contains char 0x00.
+      */
+     public static final boolean needsQuoted(String in) throws SQLException
+     {
+         int len = in.length();
+         //quoted and non-empty quotes:
+         boolean already = len>1 && in.charAt(0)=='"' && in.charAt(len-1)=='"';
+         if (already && len==2)
+             throw new PSQLException(GT.tr("Empty quoted value"), PSQLState.INVALID_PARAMETER_VALUE);
+         int end = len-1;
+         for (int i=1; i<end; i++)
+         {//scan for legal
+             char c = in.charAt(i);
+             if (c=='"')
+                 throw new PSQLException(GT.tr("Invalid quotes found inside argument"),
PSQLState.INVALID_PARAMETER_VALUE);    
+             if (c=='\0')
+                 throw new PSQLException(GT.tr("Null bytes may not occur in identifiers."),
PSQLState.INVALID_PARAMETER_VALUE);
          }
+         for (int i=1; i<end; i++)
+         {
+             char c = in.charAt(i);
+             if (Character.isWhitespace(c))
+                 return !already;
+         }
+         return false;
+     }
+
+     /**
+      * Return an ArrayList of Strings representing the table identifiers, quoted or not.
+     * Any number of id may exists; no attempt is made to validate the maximum number of IDs.
+      * @param sql INSERT INTO stmt
+      * @param start - index of the INTO keyword, 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@onnet.cc
+      */
+     public static ArrayList getInsertIds(String sql, int start)
+     {
+         if (start<0)
+             throw new IllegalArgumentException("getInsertIds: invalid start index: "+start);
+         start += 4;
+         //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(4);
+         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;
+     }
+
+     /**
+      * Search for and return the location of <code>find</code> in String <code>in</code>, case insensitive.
+      * If in is empty, return -1. If find is empty, return 0.
+      * @param in
+      * @param find - string to find
+      * @param pos - starting position to search
+      * @return int location, or -1 if not found
+     * @author Ken Johanon ken2006@onnet.cc
+      */
+     public static int position(CharSequence in, String find, int pos)
+     {
+         boolean c = in==null || in.length()==0;
+         boolean d = find==null || find.length()==0;
+         if (d || c && d)
+             return 0;
+         if (c)
+             return -1;
+         int a = in.length();
+         int b = find.length();
+         int count = 0;
+         //if (pos>a-b)
+         //    return -1;
+         char c1, c2;
+         for (int i=pos; i<a; i++)
+         {
+             c1 = in.charAt(i);
+             c2 = find.charAt(count);
+             if (c1==c2 || c1==Character.toLowerCase(c2) || c1==Character.toUpperCase(c2))
+                 count++;
+             else
+             {
+                 i -= count;
+                 count = 0;
+             }
+             if (count==b)
+                 return i-b+1;
+         }
+         return -1;
+     }
+
+ }