Re: Patch for Statement.getGeneratedKeys() - Mailing list pgsql-jdbc
From | Ken Johanson |
---|---|
Subject | Re: Patch for Statement.getGeneratedKeys() |
Date | |
Msg-id | 478EF213.7010004@kensystem.com Whole thread Raw |
In response to | Re: Patch for Statement.getGeneratedKeys() (Kris Jurka <books@ejurka.com>) |
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
pgsql-jdbc by date: