Re: Synthesize support for Statement.getGeneratedKeys()? - Mailing list pgsql-jdbc

> The previous discussion does detail the remaining steps needed to get
> something ready to be committed: proper quoting, error checking, test
> cases, ...  If you have the time and skill to work on these that would
> be appreciated.
>


Kris (and folks), I did actually find some justification for the time to
work on this. The attached patch should follow on from our last state in
Feb. Essentially the only thing I added was error checking of the
arguments to 'executeUpdate's columnNames. The checking is done in a new
Utils.needsQuoted(String in) (see javadoc) (feel free to rename this
method).

I opted to use the Quoting mechanism I already had in executeUpdate for
now, since the string validation (no 0x00 && no nested quotes) is being
done in needsQuoted (in the same loop that validates quotes and scans
for whitespace).

Let me know in detail what else needs to be implemented in terms of
error checking or methods. I did not add any unit-tests (that will be a
learning curve; seeking volunteers).

Questions:

-is whitespace the sole determinator for needing quoting? And other chars?

-is it fine to leave the string un-quoted if it contains no ws, vs
always quoting it (my feeling is yes).

-is '"' the only legal quoting chars? (I cant remember for having
dabbled with too many non-spec databases)

-my needsQuoted method throws if the identifier contains nested quotes
(foo"bar or "foo"bar"); is there a legal quote-escaping mechanism
similar to apostrophe doubling? eg: how or would one pass foo"bar (I
imagine quotes are never allowed in identifiers but don't have an SQL
spec handy)

Ken

PS - Kris, I recall you said the backslashes in the patch were
troublesome; did you find any fix for your patch tool aside from
translating them to '/'? If not I will translate them from hereto forth.
# 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)
***************
*** 19,24 ****
--- 19,27 ----
  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 ****
--- 31,37 ----
   */
  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());
      }

      /**
--- 110,118 ----
       */
      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);
      }

--- 141,147 ----
      {
          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);
      }

***************
*** 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,
--- 190,236 ----
       */
      public int executeUpdate(String sql, String columnNames[]) throws SQLException
      {
!         //fix me : columnNames only quoted if contain 0x20
!         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(' ');
!         boolean needsQuote;
!         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(',');
!             needsQuote = Utils.needsQuoted(arg);
!             if (needsQuote)
!                 s.append('"');
!             s.append(arg);
!             if (needsQuote)
!                 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)
***************
*** 146,152 ****
--- 146,186 ----

          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;
+     }
+
+ }