Index: java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java =================================================================== --- java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java (revision 154683) +++ java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java (working copy) @@ -34,14 +34,20 @@ import org.apache.derby.iapi.sql.execute.ExecRow; import org.apache.derby.iapi.sql.execute.NoPutResultSet; +import org.apache.derby.iapi.sql.dictionary.TableDescriptor; +import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor; + import org.apache.derby.iapi.sql.Activation; +import org.apache.derby.iapi.sql.execute.CursorActivation; import org.apache.derby.iapi.types.DataValueDescriptor; +import org.apache.derby.iapi.types.VariableSizeDataValue; import org.apache.derby.iapi.sql.ResultDescription; import org.apache.derby.iapi.services.io.StreamStorable; import org.apache.derby.iapi.services.io.LimitInputStream; import org.apache.derby.iapi.services.io.NewByteArrayInputStream; +import org.apache.derby.iapi.services.io.LimitReader; import org.apache.derby.iapi.error.ExceptionSeverity; import org.apache.derby.iapi.reference.JDBC20Translation; import org.apache.derby.iapi.reference.SQLState; @@ -91,9 +97,8 @@ // mutable state protected ExecRow currentRow; - //rowData is protected so deleteRow in EmbedResultSet20.java can make it null. - //This ensures that after deleteRow, ResultSet is not positioned on the deleted row. - protected DataValueDescriptor[] rowData; + //deleteRow & updateRow make rowData null so that ResultSet is not positioned on deleted/updated row. + private DataValueDescriptor[] rowData; protected boolean wasNull; protected boolean isClosed; private Object currentStream; @@ -115,7 +120,7 @@ // Order of creation final int order; - + private final ResultDescription resultDescription; // max rows limit for this result set @@ -142,15 +147,23 @@ protected final int concurrencyOfThisResultSet; + //copyOfDatabaseRow will keep the original contents of the columns of the current row which got updated. + //These will be used if user decides to cancel the changes made to the row using cancelRowUpdates. + private DataValueDescriptor[] copyOfDatabaseRow; + private boolean[] columnGotUpdated; //these are the columns which have been updated so far. Used to build UPDATE...WHERE CURRENT OF sql + private boolean currentRowHasBeenUpdated; //Gets set to true after first updateXXX on a row. Gets reset to false when the cursor moves off the row + private int fetchDirection; private int fetchSize; + //td will be used to ensure the column selected for updateXXX is part of the table. + private TableDescriptor td = null; /** * This class provides the glue between the Cloudscape * resultset and the JDBC resultset, mapping calls-to-calls. */ public EmbedResultSet(EmbedConnection conn, ResultSet resultsToWrap, - boolean forMetaData, EmbedStatement stmt, boolean isAtomic) + boolean forMetaData, EmbedStatement stmt, boolean isAtomic) throws SQLException { super(conn); @@ -185,11 +198,15 @@ // Fill in the column types resultDescription = theResults.getResultDescription(); + //initialize arrays related to updateRow implementation + columnGotUpdated = new boolean[getMetaData().getColumnCount()]; + copyOfDatabaseRow = new DataValueDescriptor[columnGotUpdated.length]; + // assign the max rows and maxfiled size limit for this result set if (stmt != null) { // At connectivity level we handle only for forward only cursor - if (stmt.resultSetType == JDBC20Translation.TYPE_FORWARD_ONLY) + if (stmt.resultSetType == JDBC20Translation.TYPE_FORWARD_ONLY) maxRows = stmt.maxRows; maxFieldSize = stmt.MaxFieldSize; @@ -276,6 +293,11 @@ } } + //since we are moving off of the current row, need to initialize state corresponding to updateRow implementation + for (int i=0; i < columnGotUpdated.length; i++) + columnGotUpdated[i] = false; + currentRowHasBeenUpdated = false; + return movePosition(NEXT, 0, "next"); } @@ -508,6 +530,10 @@ currentRow = null; rowData = null; rMetaData = null; // let it go, we can make a new one + //since we are moving off of the current row(by closing the resultset), need to initialize state corresponding to updateRow implementation + for (int i=0; i < columnGotUpdated.length; i++) + columnGotUpdated[i] = false; + currentRowHasBeenUpdated = false; // we hang on to theResults and messenger // in case more calls come in on this resultSet @@ -1975,7 +2001,7 @@ * @see EmbedDatabaseMetaData#updatesAreDetected */ public boolean rowUpdated() throws SQLException { - throw Util.notImplemented(); + return false; } /** @@ -1996,22 +2022,110 @@ /** * JDBC 2.0 - * + * * Determine if this row has been deleted. A deleted row may leave a visible * "hole" in a result set. This method can be used to detect holes in a * result set. The value returned depends on whether or not the result set * can detect deletions. - * + * * @return true if deleted and deletes are detected * @exception SQLException * if a database-access error occurs - * + * * @see EmbedDatabaseMetaData#deletesAreDetected */ public boolean rowDeleted() throws SQLException { return false; } + //do following few checks before accepting updatable resultset api + //1)Make sure this is an updatable ResultSet + //2)Make sure JDBC ResultSet is not closed + //3)Make sure JDBC ResultSet is positioned on a row + //4)Make sure underneath language resultset is not closed + //5)Make sure for updateXXX methods, the column position is not out of range + //6)Make sure the column corresponds to a column in the base table and it is not a derived column + //7)Make sure correlation names are not used for base table column names in updateXXXX. This is because the mapping + // of correlation name to base table column position is not available at runtime. + protected void checksBeforeUpdateOrDelete(String methodName, int columnIndex) throws SQLException { + + //1)Make sure this is an updatable ResultSet + if (getConcurrency() != JDBC20Translation.CONCUR_UPDATABLE)//if not updatable resultset, then throw exception + throw Util.generateCsSQLException(SQLState.UPDATABLE_RESULTSET_API_DISALLOWED, methodName); + + //2)Make sure JDBC ResultSet is not closed + checkIfClosed(methodName); + + //3)Make sure JDBC ResultSet is positioned on a row + checkOnRow(); // first make sure there's a current row + //in case of autocommit on, if there was an exception which caused runtime rollback in this transaction prior to this call, + //the rollback code will mark the language resultset closed (it doesn't mark the JDBC ResultSet closed). + //That is why alongwith the earlier checkIfClosed call in this method, there is a check for language resultset close as well. + + //4)Make sure underneath language resultset is not closed + if (theResults.isClosed()) + throw Util.generateCsSQLException(SQLState.LANG_RESULT_SET_NOT_OPEN, methodName); + + //the remaining checks only apply to updateXXX methods + if (methodName.equals("updateRow") || methodName.equals("deleteRow") || methodName.equals("cancelRowUpdates")) + return; + + //5)Make sure for updateXXX methods, the column position is not out of range + ResultDescription rd = theResults.getResultDescription(); + if (columnIndex < 1 || columnIndex > rd.getColumnCount()) + throw Util.generateCsSQLException(SQLState.LANG_INVALID_COLUMN_POSITION, new Integer(columnIndex), String.valueOf(rd.getColumnCount())); + + //6)Make sure the column corresponds to a column in the base table and it is not a derived column + if (rd.getColumnDescriptor(columnIndex).getSourceTableName() == null) + throw Util.generateCsSQLException(SQLState.COLUMN_NOT_FROM_BASE_TABLE, methodName); + + //7)Make sure correlation names are not used for base table column names in updateXXX. This is because the mapping + // of correlation name to base table column position is not available at runtime. + //If can't find the column in the base table, then throw exception. This will happen if correlation name is used for column names + if (td == null) getTargetTableDescriptor(); + if (td.getColumnDescriptor(rd.getColumnDescriptor(columnIndex).getName()) == null) + throw Util.generateCsSQLException(SQLState.COLUMN_NOT_FROM_BASE_TABLE, methodName); + } + + //Get the table descriptor for the target table for updateXXX. td will be used to ensure the column selected for updateXXX + //is part of the table. + private void getTargetTableDescriptor() throws SQLException { + setupContextStack(); + try { + LanguageConnectionContext lcc = getEmbedConnection().getLanguageConnection(); + CursorActivation activation = lcc.lookupCursorActivation(getCursorName()); + ExecCursorTableReference targetTable = activation.getPreparedStatement().getTargetTable(); + SchemaDescriptor sd = null; + if (targetTable.getSchemaName() != null) + sd = lcc.getDataDictionary().getSchemaDescriptor(targetTable.getSchemaName(),null, false); + else + sd = lcc.getDataDictionary().getSchemaDescriptor(lcc.getCurrentSchemaName(),null, false); + + if ((sd != null) && sd.getSchemaName().equals(SchemaDescriptor.STD_DECLARED_GLOBAL_TEMPORARY_TABLES_SCHEMA_NAME)) + td = lcc.getTableDescriptorForDeclaredGlobalTempTable(targetTable.getBaseName()); //check if this is a temp table before checking data dictionary + + if (td == null) //td null here means it is not a temporary table. Look for table in physical SESSION schema + td = lcc.getDataDictionary().getTableDescriptor(targetTable.getBaseName(), sd); + } catch (StandardException t) { + throw noStateChangeException(t); + } finally { + restoreContextStack(); + } + } + + //mark the column as updated and return DataValueDescriptor for it. It will be used by updateXXX methods to put new values + protected DataValueDescriptor getDVDforColumnToBeUpdated(int columnIndex, String updateMethodName) throws StandardException, SQLException { + checksBeforeUpdateOrDelete(updateMethodName, columnIndex); + if (columnGotUpdated[columnIndex-1] == false) {//this is the first updateXXX call on this column + //this is the first updateXXX method call on this column. Save the original content of the column into copyOfDatabaseRow + //The saved copy of the column will be needed if cancelRowUpdates is issued + copyOfDatabaseRow[columnIndex - 1] = currentRow.getColumn(columnIndex).getClone(); + } + columnGotUpdated[columnIndex-1] = true; + currentRowHasBeenUpdated = true; + return currentRow.getColumn(columnIndex); + } + /** * JDBC 2.0 * @@ -2028,19 +2142,23 @@ * if a database-access error occurs */ public void updateNull(int columnIndex) throws SQLException { - throw Util.notImplemented(); + try { + getDVDforColumnToBeUpdated(columnIndex, "updateNull").setToNull(); + } catch (StandardException t) { + throw noStateChangeException(t); + } } /** * JDBC 2.0 - * + * * Update a column with a boolean value. - * + * * The updateXXX() methods are used to update column values in the current * row, or the insert row. The updateXXX() methods do not update the * underlying database, instead the updateRow() or insertRow() methods are * called to update the database. - * + * * @param columnIndex * the first column is 1, the second is 2, ... * @param x @@ -2049,19 +2167,23 @@ * if a database-access error occurs */ public void updateBoolean(int columnIndex, boolean x) throws SQLException { - throw Util.notImplemented(); + try { + getDVDforColumnToBeUpdated(columnIndex, "updateBoolean").setValue(x); + } catch (StandardException t) { + throw noStateChangeException(t); + } } /** * JDBC 2.0 - * + * * Update a column with a byte value. - * + * * The updateXXX() methods are used to update column values in the current * row, or the insert row. The updateXXX() methods do not update the * underlying database, instead the updateRow() or insertRow() methods are * called to update the database. - * + * * @param columnIndex * the first column is 1, the second is 2, ... * @param x @@ -2070,19 +2192,23 @@ * if a database-access error occurs */ public void updateByte(int columnIndex, byte x) throws SQLException { - throw Util.notImplemented(); + try { + getDVDforColumnToBeUpdated(columnIndex, "updateByte").setValue(x); + } catch (StandardException t) { + throw noStateChangeException(t); + } } /** * JDBC 2.0 - * + * * Update a column with a short value. - * + * * The updateXXX() methods are used to update column values in the current * row, or the insert row. The updateXXX() methods do not update the * underlying database, instead the updateRow() or insertRow() methods are * called to update the database. - * + * * @param columnIndex * the first column is 1, the second is 2, ... * @param x @@ -2091,19 +2217,23 @@ * if a database-access error occurs */ public void updateShort(int columnIndex, short x) throws SQLException { - throw Util.notImplemented(); + try { + getDVDforColumnToBeUpdated(columnIndex, "updateShort").setValue(x); + } catch (StandardException t) { + throw noStateChangeException(t); + } } /** * JDBC 2.0 - * + * * Update a column with an integer value. - * + * * The updateXXX() methods are used to update column values in the current * row, or the insert row. The updateXXX() methods do not update the * underlying database, instead the updateRow() or insertRow() methods are * called to update the database. - * + * * @param columnIndex * the first column is 1, the second is 2, ... * @param x @@ -2112,19 +2242,23 @@ * if a database-access error occurs */ public void updateInt(int columnIndex, int x) throws SQLException { - throw Util.notImplemented(); + try { + getDVDforColumnToBeUpdated(columnIndex, "updateInt").setValue(x); + } catch (StandardException t) { + throw noStateChangeException(t); + } } /** * JDBC 2.0 - * + * * Update a column with a long value. - * + * * The updateXXX() methods are used to update column values in the current * row, or the insert row. The updateXXX() methods do not update the * underlying database, instead the updateRow() or insertRow() methods are * called to update the database. - * + * * @param columnIndex * the first column is 1, the second is 2, ... * @param x @@ -2133,19 +2267,23 @@ * if a database-access error occurs */ public void updateLong(int columnIndex, long x) throws SQLException { - throw Util.notImplemented(); + try { + getDVDforColumnToBeUpdated(columnIndex, "updateLong").setValue(x); + } catch (StandardException t) { + throw noStateChangeException(t); + } } /** * JDBC 2.0 - * + * * Update a column with a float value. - * + * * The updateXXX() methods are used to update column values in the current * row, or the insert row. The updateXXX() methods do not update the * underlying database, instead the updateRow() or insertRow() methods are * called to update the database. - * + * * @param columnIndex * the first column is 1, the second is 2, ... * @param x @@ -2154,14 +2292,18 @@ * if a database-access error occurs */ public void updateFloat(int columnIndex, float x) throws SQLException { - throw Util.notImplemented(); + try { + getDVDforColumnToBeUpdated(columnIndex, "updateFloat").setValue(x); + } catch (StandardException t) { + throw noStateChangeException(t); + } } /** * JDBC 2.0 - * + * * Update a column with a Double value. - * + * * The updateXXX() methods are used to update column values in the current * row, or the insert row. The updateXXX() methods do not update the * underlying database, instead the updateRow() or insertRow() methods are @@ -2175,7 +2317,11 @@ * if a database-access error occurs */ public void updateDouble(int columnIndex, double x) throws SQLException { - throw Util.notImplemented(); + try { + getDVDforColumnToBeUpdated(columnIndex, "updateDouble").setValue(x); + } catch (StandardException t) { + throw noStateChangeException(t); + } } /** @@ -2196,7 +2342,11 @@ * if a database-access error occurs */ public void updateString(int columnIndex, String x) throws SQLException { - throw Util.notImplemented(); + try { + getDVDforColumnToBeUpdated(columnIndex, "updateString").setValue(x); + } catch (StandardException t) { + throw noStateChangeException(t); + } } /** @@ -2217,19 +2367,23 @@ * if a database-access error occurs */ public void updateBytes(int columnIndex, byte x[]) throws SQLException { - throw Util.notImplemented(); + try { + getDVDforColumnToBeUpdated(columnIndex, "updateBytes").setValue(x); + } catch (StandardException t) { + throw noStateChangeException(t); + } } /** * JDBC 2.0 - * + * * Update a column with a Date value. - * + * * The updateXXX() methods are used to update column values in the current * row, or the insert row. The updateXXX() methods do not update the * underlying database, instead the updateRow() or insertRow() methods are * called to update the database. - * + * * @param columnIndex * the first column is 1, the second is 2, ... * @param x @@ -2239,19 +2393,23 @@ */ public void updateDate(int columnIndex, java.sql.Date x) throws SQLException { - throw Util.notImplemented(); + try { + getDVDforColumnToBeUpdated(columnIndex, "updateDate").setValue(x); + } catch (StandardException t) { + throw noStateChangeException(t); + } } /** * JDBC 2.0 - * + * * Update a column with a Time value. - * + * * The updateXXX() methods are used to update column values in the current * row, or the insert row. The updateXXX() methods do not update the * underlying database, instead the updateRow() or insertRow() methods are * called to update the database. - * + * * @param columnIndex * the first column is 1, the second is 2, ... * @param x @@ -2261,19 +2419,23 @@ */ public void updateTime(int columnIndex, java.sql.Time x) throws SQLException { - throw Util.notImplemented(); + try { + getDVDforColumnToBeUpdated(columnIndex, "updateTime").setValue(x); + } catch (StandardException t) { + throw noStateChangeException(t); + } } /** * JDBC 2.0 - * + * * Update a column with a Timestamp value. - * + * * The updateXXX() methods are used to update column values in the current * row, or the insert row. The updateXXX() methods do not update the * underlying database, instead the updateRow() or insertRow() methods are * called to update the database. - * + * * @param columnIndex * the first column is 1, the second is 2, ... * @param x @@ -2283,19 +2445,23 @@ */ public void updateTimestamp(int columnIndex, java.sql.Timestamp x) throws SQLException { - throw Util.notImplemented(); + try { + getDVDforColumnToBeUpdated(columnIndex, "updateTimestamp").setValue(x); + } catch (StandardException t) { + throw noStateChangeException(t); + } } /** * JDBC 2.0 - * + * * Update a column with an ascii stream value. - * + * * The updateXXX() methods are used to update column values in the current * row, or the insert row. The updateXXX() methods do not update the * underlying database, instead the updateRow() or insertRow() methods are * called to update the database. - * + * * @param columnIndex * the first column is 1, the second is 2, ... * @param x @@ -2307,19 +2473,41 @@ */ public void updateAsciiStream(int columnIndex, java.io.InputStream x, int length) throws SQLException { - throw Util.notImplemented(); + checksBeforeUpdateOrDelete("updateAsciiStream", columnIndex); + + int colType = getColumnType(columnIndex); + switch (colType) { + case Types.CHAR: + case Types.VARCHAR: + case Types.LONGVARCHAR: + case Types.CLOB: + break; + default: + throw dataTypeConversion(columnIndex, "java.io.InputStream"); + } + + java.io.Reader r = null; + if (x != null) + { + try { + r = new java.io.InputStreamReader(x, "ISO-8859-1"); + } catch (java.io.UnsupportedEncodingException uee) { + throw new SQLException(uee.getMessage()); + } + } + updateCharacterStream(columnIndex, r, length); } /** * JDBC 2.0 - * + * * Update a column with a binary stream value. - * + * * The updateXXX() methods are used to update column values in the current * row, or the insert row. The updateXXX() methods do not update the * underlying database, instead the updateRow() or insertRow() methods are * called to update the database. - * + * * @param columnIndex * the first column is 1, the second is 2, ... * @param x @@ -2331,9 +2519,40 @@ */ public void updateBinaryStream(int columnIndex, java.io.InputStream x, int length) throws SQLException { - throw Util.notImplemented(); + checksBeforeUpdateOrDelete("updateBinaryStream", columnIndex); + int colType = getColumnType(columnIndex); + switch (colType) { + case Types.BINARY: + case Types.VARBINARY: + case Types.LONGVARBINARY: + case Types.BLOB: + break; + default: + throw dataTypeConversion(columnIndex, "java.io.InputStream"); + } + if (length < 0) //we are doing the check here and not in updateBinaryStreamInternal becuase updateClob needs to pass -1 for length. + throw newSQLException(SQLState.NEGATIVE_STREAM_LENGTH); + + if (x == null) + { + updateNull(columnIndex); + return; + } + + updateBinaryStreamInternal(columnIndex, x, length,"updateBinaryStream"); } + protected void updateBinaryStreamInternal(int columnIndex, + java.io.InputStream x, int length, String updateMethodName) + throws SQLException + { + try { + getDVDforColumnToBeUpdated(columnIndex, updateMethodName).setValue(new RawToBinaryFormatStream(x, length), length); + } catch (StandardException t) { + throw noStateChangeException(t); + } + } + /** * JDBC 2.0 * @@ -2355,19 +2574,56 @@ */ public void updateCharacterStream(int columnIndex, java.io.Reader x, int length) throws SQLException { - throw Util.notImplemented(); + //If the column type is the right datatype, this method will eventually call getDVDforColumnToBeUpdated which will check for + //the read only resultset. But for other datatypes, we want to catch if this updateCharacterStream is being issued + //against a read only resultset. And that is the reason for call to checksBeforeUpdateOrDelete here. + checksBeforeUpdateOrDelete("updateCharacterStream", columnIndex); + int colType = getColumnType(columnIndex); + switch (colType) { + case Types.CHAR: + case Types.VARCHAR: + case Types.LONGVARCHAR: + case Types.CLOB: + break; + default: + throw dataTypeConversion(columnIndex, "java.io.Reader"); + } + if (length < 0) //we are doing the check here and not in updateCharacterStreamInternal becuase updateClob needs to pass -1 for length. + throw newSQLException(SQLState.NEGATIVE_STREAM_LENGTH); + + if (x == null) + { + updateNull(columnIndex); + return; + } + updateCharacterStreamInternal(columnIndex, x, length, "updateCharacterStream"); } + protected void updateCharacterStreamInternal(int columnIndex, + java.io.Reader reader, int length, String updateMethodName) + throws SQLException + { + try { + LimitReader limitIn = new LimitReader(reader); + if (length != -1) + limitIn.setLimit(length); + ReaderToUTF8Stream utfIn = new ReaderToUTF8Stream(limitIn); + getDVDforColumnToBeUpdated(columnIndex, updateMethodName).setValue(utfIn, length); + } catch (StandardException t) { + throw noStateChangeException(t); + } + } + /** * JDBC 2.0 - * + * * Update a column with an Object value. - * + * * The updateXXX() methods are used to update column values in the current * row, or the insert row. The updateXXX() methods do not update the * underlying database, instead the updateRow() or insertRow() methods are * called to update the database. - * + * * @param columnIndex * the first column is 1, the second is 2, ... * @param x @@ -2381,19 +2637,41 @@ */ public void updateObject(int columnIndex, Object x, int scale) throws SQLException { - throw Util.notImplemented(); + updateObject(columnIndex, x); + /* + * If the parameter type is DECIMAL or NUMERIC, then + * we need to set them to the passed scale. + */ + int colType = getColumnType(columnIndex); + if ((colType == Types.DECIMAL) || (colType == Types.NUMERIC)) { + if (scale < 0) + throw newSQLException(SQLState.BAD_SCALE_VALUE, new Integer(scale)); + + try { + DataValueDescriptor value = currentRow.getColumn(columnIndex); + + int origvaluelen = value.getLength(); + ((VariableSizeDataValue) + value).setWidth(VariableSizeDataValue.IGNORE_PRECISION, + scale, + false); + + } catch (StandardException t) { + throw EmbedResultSet.noStateChangeException(t); + } + } } /** * JDBC 2.0 - * + * * Update a column with an Object value. - * + * * The updateXXX() methods are used to update column values in the current * row, or the insert row. The updateXXX() methods do not update the * underlying database, instead the updateRow() or insertRow() methods are * called to update the database. - * + * * @param columnIndex * the first column is 1, the second is 2, ... * @param x @@ -2402,7 +2680,88 @@ * if a database-access error occurs */ public void updateObject(int columnIndex, Object x) throws SQLException { - throw Util.notImplemented(); + checksBeforeUpdateOrDelete("updateObject", columnIndex); + int colType = getColumnType(columnIndex); + if (colType == org.apache.derby.iapi.reference.JDBC20Translation.SQL_TYPES_JAVA_OBJECT) { + try { + getDVDforColumnToBeUpdated(columnIndex, "updateObject").setValue(x); + return; + } catch (StandardException t) { + throw noStateChangeException(t); + } + } + + if (x == null) { + updateNull(columnIndex); + return; + } + + if (x instanceof String) { + updateString(columnIndex, (String) x); + return; + } + + if (x instanceof Boolean) { + updateBoolean(columnIndex, ((Boolean) x).booleanValue()); + return; + } + + if (x instanceof Short) { + updateShort(columnIndex, ((Short) x).shortValue()); + return; + } + + if (x instanceof Integer) { + updateInt(columnIndex, ((Integer) x).intValue()); + return; + } + + if (x instanceof Long) { + updateLong(columnIndex, ((Long) x).longValue()); + return; + } + + if (x instanceof Float) { + updateFloat(columnIndex, ((Float) x).floatValue()); + return; + } + + if (x instanceof Double) { + updateDouble(columnIndex, ((Double) x).doubleValue()); + return; + } + + if (x instanceof byte[]) { + updateBytes(columnIndex, (byte[]) x); + return; + } + + if (x instanceof Date) { + updateDate(columnIndex, (Date) x); + return; + } + + if (x instanceof Time) { + updateTime(columnIndex, (Time) x); + return; + } + + if (x instanceof Timestamp) { + updateTimestamp(columnIndex, (Timestamp) x); + return; + } + + if (x instanceof Blob) { + updateBlob(columnIndex, (Blob) x); + return; + } + + if (x instanceof Clob) { + updateClob(columnIndex, (Clob) x); + return; + } + + throw dataTypeConversion(columnIndex, x.getClass().getName()); } /** @@ -2421,7 +2780,7 @@ * if a database-access error occurs */ public void updateNull(String columnName) throws SQLException { - throw Util.notImplemented(); + updateNull(findColumnName(columnName)); } /** @@ -2442,7 +2801,7 @@ * if a database-access error occurs */ public void updateBoolean(String columnName, boolean x) throws SQLException { - throw Util.notImplemented(); + updateBoolean(findColumnName(columnName), x); } /** @@ -2463,7 +2822,7 @@ * if a database-access error occurs */ public void updateByte(String columnName, byte x) throws SQLException { - throw Util.notImplemented(); + updateByte(findColumnName(columnName), x); } /** @@ -2484,7 +2843,7 @@ * if a database-access error occurs */ public void updateShort(String columnName, short x) throws SQLException { - throw Util.notImplemented(); + updateShort(findColumnName(columnName), x); } /** @@ -2505,7 +2864,7 @@ * if a database-access error occurs */ public void updateInt(String columnName, int x) throws SQLException { - throw Util.notImplemented(); + updateInt(findColumnName(columnName), x); } /** @@ -2526,7 +2885,7 @@ * if a database-access error occurs */ public void updateLong(String columnName, long x) throws SQLException { - throw Util.notImplemented(); + updateLong(findColumnName(columnName), x); } /** @@ -2547,7 +2906,7 @@ * if a database-access error occurs */ public void updateFloat(String columnName, float x) throws SQLException { - throw Util.notImplemented(); + updateFloat(findColumnName(columnName), x); } /** @@ -2568,7 +2927,7 @@ * if a database-access error occurs */ public void updateDouble(String columnName, double x) throws SQLException { - throw Util.notImplemented(); + updateDouble(findColumnName(columnName), x); } /** @@ -2589,7 +2948,7 @@ * if a database-access error occurs */ public void updateString(String columnName, String x) throws SQLException { - throw Util.notImplemented(); + updateString(findColumnName(columnName), x); } /** @@ -2610,7 +2969,7 @@ * if a database-access error occurs */ public void updateBytes(String columnName, byte x[]) throws SQLException { - throw Util.notImplemented(); + updateBytes(findColumnName(columnName), x); } /** @@ -2632,7 +2991,7 @@ */ public void updateDate(String columnName, java.sql.Date x) throws SQLException { - throw Util.notImplemented(); + updateDate(findColumnName(columnName), x); } /** @@ -2654,7 +3013,7 @@ */ public void updateTime(String columnName, java.sql.Time x) throws SQLException { - throw Util.notImplemented(); + updateTime(findColumnName(columnName), x); } /** @@ -2676,7 +3035,7 @@ */ public void updateTimestamp(String columnName, java.sql.Timestamp x) throws SQLException { - throw Util.notImplemented(); + updateTimestamp(findColumnName(columnName), x); } /** @@ -2700,7 +3059,7 @@ */ public void updateAsciiStream(String columnName, java.io.InputStream x, int length) throws SQLException { - throw Util.notImplemented(); + updateAsciiStream(findColumnName(columnName), x, length); } /** @@ -2724,7 +3083,7 @@ */ public void updateBinaryStream(String columnName, java.io.InputStream x, int length) throws SQLException { - throw Util.notImplemented(); + updateBinaryStream(findColumnName(columnName), x, length); } /** @@ -2748,45 +3107,41 @@ */ public void updateCharacterStream(String columnName, java.io.Reader reader, int length) throws SQLException { - throw Util.notImplemented(); + updateCharacterStream(findColumnName(columnName), reader, length); } /** * JDBC 2.0 - * + * * Update a column with an Object value. - * - * The updateXXX() methods are used to update column values in the current - * row, or the insert row. The updateXXX() methods do not update the - * underlying database, instead the updateRow() or insertRow() methods are - * called to update the database. - * - * @param columnName - * the name of the column - * @param x - * the new column value - * @param scale - * For java.sql.Types.DECIMAL or java.sql.Types.NUMERIC types - * this is the number of digits after the decimal. For all other - * types this value will be ignored. - * @exception SQLException - * if a database-access error occurs + * + * The updateXXX() methods are used to update column values in the + * current row, or the insert row. The updateXXX() methods do not + * update the underlying database, instead the updateRow() or insertRow() + * methods are called to update the database. + * + * @param columnName the name of the column + * @param x the new column value + * @param scale For java.sql.Types.DECIMAL or java.sql.Types.NUMERIC types + * this is the number of digits after the decimal. For all other + * types this value will be ignored. + * @exception SQLException if a database-access error occurs */ public void updateObject(String columnName, Object x, int scale) - throws SQLException { - throw Util.notImplemented(); + throws SQLException { + updateObject(findColumnName(columnName), x, scale); } /** * JDBC 2.0 - * + * * Update a column with an Object value. - * + * * The updateXXX() methods are used to update column values in the current * row, or the insert row. The updateXXX() methods do not update the * underlying database, instead the updateRow() or insertRow() methods are * called to update the database. - * + * * @param columnName * the name of the column * @param x @@ -2795,7 +3150,7 @@ * if a database-access error occurs */ public void updateObject(String columnName, Object x) throws SQLException { - throw Util.notImplemented(); + updateObject(findColumnName(columnName), x); } /** @@ -2813,102 +3168,119 @@ throw Util.notImplemented(); } - /** - * JDBC 2.0 - * - * Update the underlying database with the new contents of the current row. - * Cannot be called when on the insert row. - * - * @exception SQLException - * if a database-access error occurs, or if called when on - * the insert row - */ - public void updateRow() throws SQLException { - throw Util.notImplemented(); - } + /** + * JDBC 2.0 + * + * Update the underlying database with the new contents of the + * current row. Cannot be called when on the insert row. + * + * @exception SQLException if a database-access error occurs, or + * if called when on the insert row + */ + public void updateRow() throws SQLException { + synchronized (getConnectionSynchronization()) { + checksBeforeUpdateOrDelete("updateRow", -1); + setupContextStack(); + LanguageConnectionContext lcc = null; + StatementContext statementContext = null; + try { + if (currentRowHasBeenUpdated == false) //nothing got updated on this row + return; //nothing to do since no updates were made to this row - /** - * JDBC 2.0 - * - * Delete the current row from the result set and the underlying database. - * Cannot be called when on the insert row. - * - * @exception SQLException - * if a database-access error occurs, or if called when on - * the insert row. - */ - public void deleteRow() throws SQLException { - synchronized (getConnectionSynchronization()) { - checkIfClosed("deleteRow"); - checkOnRow(); // first make sure there's a current row + //now construct the update where current of sql + boolean foundOneColumnAlready = false; + StringBuffer updateWhereCurrentOfSQL = new StringBuffer("UPDATE "); + CursorActivation activation = getEmbedConnection().getLanguageConnection().lookupCursorActivation(getCursorName()); + ExecCursorTableReference targetTable = activation.getPreparedStatement().getTargetTable(); + updateWhereCurrentOfSQL.append(getFullBaseTableName(targetTable));//got the underlying (schema.)table name + updateWhereCurrentOfSQL.append(" SET "); + ResultDescription rd = theResults.getResultDescription(); - if (getConcurrency() != JDBC20Translation.CONCUR_UPDATABLE)//if not - // updatable - // resultset, - // can't - // issue - // deleteRow - throw Util.generateCsSQLException( - SQLState.UPDATABLE_RESULTSET_API_DISALLOWED, - "deleteRow"); + for (int i=1; i<=rd.getColumnCount(); i++) { //in this for loop we are constructing columnname=?,... part of the update sql + if (columnGotUpdated[i-1]) { //if the column got updated, do following + if (foundOneColumnAlready) + updateWhereCurrentOfSQL.append(","); + //using quotes around the column name to preserve case sensitivity + updateWhereCurrentOfSQL.append("\"" + rd.getColumnDescriptor(i).getName() + "\"=?"); + foundOneColumnAlready = true; + } + } + //using quotes around the cursor name to preserve case sensitivity + updateWhereCurrentOfSQL.append(" WHERE CURRENT OF \"" + getCursorName() + "\""); + lcc = getEmbedConnection().getLanguageConnection(); + statementContext = lcc.pushStatementContext(isAtomic, updateWhereCurrentOfSQL.toString(), null, false); + org.apache.derby.iapi.sql.PreparedStatement ps = lcc.prepareInternalStatement(updateWhereCurrentOfSQL.toString()); + Activation act = ps.getActivation(lcc, false); - setupContextStack(); - try { - //in case of autocommit on, if there was an exception which - // caused runtime rollback in this transaction prior to this - // deleteRow, - //the rollback code will mark the language resultset closed (it - // doesn't mark the JDBC ResultSet closed). - //That is why alongwith the earlier checkIfClosed call in this - // method, there is a check for language resultset close as - // well. - if (theResults.isClosed()) - throw Util.generateCsSQLException( - SQLState.LANG_RESULT_SET_NOT_OPEN, "deleteRow"); - StringBuffer deleteWhereCurrentOfSQL = new StringBuffer( - "DELETE FROM "); - Activation activation = getEmbedConnection() - .getLanguageConnection().lookupCursorActivation( - getCursorName()); - deleteWhereCurrentOfSQL.append(getFullBaseTableName(activation - .getPreparedStatement().getTargetTable()));//get the - // underlying - // (schema.)table - // name - //using quotes around the cursor name to preserve case - // sensitivity - deleteWhereCurrentOfSQL.append(" WHERE CURRENT OF \"" - + getCursorName() + "\""); - - LanguageConnectionContext lcc = getEmbedConnection() - .getLanguageConnection(); - StatementContext statementContext = lcc.pushStatementContext( - isAtomic, deleteWhereCurrentOfSQL.toString(), null, - false); - org.apache.derby.iapi.sql.PreparedStatement ps = lcc - .prepareInternalStatement(deleteWhereCurrentOfSQL - .toString()); - org.apache.derby.iapi.sql.ResultSet rs = ps.execute(lcc, true); - rs.close(); - rs.finish(); - //For forward only resultsets, after a delete, the ResultSet - // will be positioned right before the next row. - rowData = null; - lcc.popStatementContext(statementContext, null); - } catch (StandardException t) { - throw closeOnTransactionError(t); - } finally { - restoreContextStack(); + //in this for loop we are assigning values for parameters in sql constructed earlier with columnname=?,... + for (int i=1, paramPosition=0; i<=rd.getColumnCount(); i++) { + if (columnGotUpdated[i-1]) //if the column got updated, do following + act.getParameterValueSet().getParameterForSet(paramPosition++).setValue(currentRow.getColumn(i)); + } + org.apache.derby.iapi.sql.ResultSet rs = ps.execute(act, false, true, true); //execute the update where current of sql + rs.close(); + rs.finish(); + //For forward only resultsets, after a update, the ResultSet will be positioned right before the next row. + rowData = null; + currentRow = null; + lcc.popStatementContext(statementContext, null); + } catch (StandardException t) { + throw closeOnTransactionError(t); + } finally { + if (statementContext != null) + lcc.popStatementContext(statementContext, null); + restoreContextStack(); + } } - } - } + } + /** + * JDBC 2.0 + * + * Delete the current row from the result set and the underlying + * database. Cannot be called when on the insert row. + * + * @exception SQLException if a database-access error occurs, or if + * called when on the insert row. + */ + public void deleteRow() throws SQLException { + synchronized (getConnectionSynchronization()) { + checksBeforeUpdateOrDelete("deleteRow", -1); + + setupContextStack(); + //now construct the delete where current of sql + try { + StringBuffer deleteWhereCurrentOfSQL = new StringBuffer("DELETE FROM "); + CursorActivation activation = getEmbedConnection().getLanguageConnection().lookupCursorActivation(getCursorName()); + deleteWhereCurrentOfSQL.append(getFullBaseTableName(activation.getPreparedStatement().getTargetTable()));//get the underlying (schema.)table name + //using quotes around the cursor name to preserve case sensitivity + deleteWhereCurrentOfSQL.append(" WHERE CURRENT OF \"" + getCursorName() + "\""); + + LanguageConnectionContext lcc = getEmbedConnection().getLanguageConnection(); + StatementContext statementContext = lcc.pushStatementContext(isAtomic, deleteWhereCurrentOfSQL.toString(), null, false); + org.apache.derby.iapi.sql.PreparedStatement ps = lcc.prepareInternalStatement(deleteWhereCurrentOfSQL.toString()); + org.apache.derby.iapi.sql.ResultSet rs = ps.execute(lcc, true); //execute delete where current of sql + rs.close(); + rs.finish(); + //For forward only resultsets, after a delete, the ResultSet will be positioned right before the next row. + rowData = null; + currentRow = null; + lcc.popStatementContext(statementContext, null); + } catch (StandardException t) { + throw closeOnTransactionError(t); + } finally { + restoreContextStack(); + } + } + } + private String getFullBaseTableName(ExecCursorTableReference targetTable) { + //using quotes to preserve case sensitivity if (targetTable.getSchemaName() != null) - return targetTable.getSchemaName() + "." - + targetTable.getBaseName(); + return "\"" + targetTable.getSchemaName() + "\".\"" + + targetTable.getBaseName() + "\""; else - return targetTable.getBaseName(); + return "\"" + targetTable.getBaseName() + "\""; } /** @@ -2937,23 +3309,32 @@ throw Util.notImplemented(); } - /** - * JDBC 2.0 - * - * The cancelRowUpdates() method may be called after calling an updateXXX() - * method(s) and before calling updateRow() to rollback the updates made to - * a row. If no updates have been made or updateRow() has already been - * called, then this method has no effect. - * - * @exception SQLException - * if a database-access error occurs, or if called when on - * the insert row. - * - */ - public void cancelRowUpdates() throws SQLException { - throw Util.notImplemented(); - } + /** + * JDBC 2.0 + * + * The cancelRowUpdates() method may be called after calling an + * updateXXX() method(s) and before calling updateRow() to rollback + * the updates made to a row. If no updates have been made or + * updateRow() has already been called, then this method has no + * effect. + * + * @exception SQLException if a database-access error occurs, or if + * called when on the insert row. + * + */ + public void cancelRowUpdates () throws SQLException { + checksBeforeUpdateOrDelete("cancelRowUpdates", -1); + if (currentRowHasBeenUpdated == false) return; //nothing got updated on this row so cancelRowUpdates is a no-op in this case. + for (int i=0; i < columnGotUpdated.length; i++){ + if (columnGotUpdated[i] == true) currentRow.setColumn(i+1, copyOfDatabaseRow[i]);//if column got updated, resotre the original data + columnGotUpdated[i] = false; + } + currentRowHasBeenUpdated = false; + //rowData needs to be refreshed with the currentRow otherwise it will continue to have changes made by updateXXX methods + rowData = currentRow.getRowArray(); + } + /** * JDBC 2.0 * @@ -3141,7 +3522,15 @@ * Feature not implemented for now. */ public void updateBlob(int columnIndex, Blob x) throws SQLException { - throw Util.notImplemented(); + checksBeforeUpdateOrDelete("updateBlob", columnIndex); + int colType = getColumnType(columnIndex); + if (colType != Types.BLOB) + throw dataTypeConversion(columnIndex, "java.sql.Blob"); + + if (x == null) + updateNull(columnIndex); + else + updateBinaryStreamInternal(columnIndex, x.getBinaryStream(), -1, "updateBlob"); } /** @@ -3160,7 +3549,7 @@ * Feature not implemented for now. */ public void updateBlob(String columnName, Blob x) throws SQLException { - throw Util.notImplemented(); + updateBlob(findColumnName(columnName), x); } /** @@ -3179,7 +3568,15 @@ * Feature not implemented for now. */ public void updateClob(int columnIndex, Clob x) throws SQLException { - throw Util.notImplemented(); + checksBeforeUpdateOrDelete("updateClob", columnIndex); + int colType = getColumnType(columnIndex); + if (colType != Types.CLOB) + throw dataTypeConversion(columnIndex, "java.sql.Clob"); + + if (x == null) + updateNull(columnIndex); + else + updateCharacterStreamInternal(columnIndex, x.getCharacterStream(), -1, "updateClob"); } /** @@ -3198,7 +3595,7 @@ * Feature not implemented for now. */ public void updateClob(String columnName, Clob x) throws SQLException { - throw Util.notImplemented(); + updateClob(findColumnName(columnName), x); } @@ -3505,5 +3902,10 @@ return newSQLException(SQLState.LANG_DATA_TYPE_GET_MISMATCH, targetType, resultDescription.getColumnDescriptor(column).getType().getTypeId().getSQLTypeName()); } + + protected final SQLException dataTypeConversion(int column, String targetType) { + return newSQLException(SQLState.LANG_DATA_TYPE_GET_MISMATCH, + resultDescription.getColumnDescriptor(column).getType().getTypeId().getSQLTypeName(), targetType); + } } Index: java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java =================================================================== --- java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java (revision 154683) +++ java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java (working copy) @@ -20,45 +20,31 @@ package org.apache.derby.impl.jdbc; -import org.apache.derby.iapi.services.info.ProductGenusNames; import org.apache.derby.iapi.services.info.ProductVersionHolder; -import org.apache.derby.iapi.services.sanity.SanityManager; import org.apache.derby.iapi.services.monitor.Monitor; import org.apache.derby.iapi.sql.conn.LanguageConnectionContext; import org.apache.derby.iapi.sql.dictionary.DataDictionary; -import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor; import org.apache.derby.iapi.sql.dictionary.SPSDescriptor; -import org.apache.derby.iapi.sql.execute.ConstantAction; - -import org.apache.derby.iapi.store.access.TransactionController; - import org.apache.derby.iapi.error.StandardException; -import org.apache.derby.impl.sql.catalog.DD_Version; import org.apache.derby.impl.sql.execute.GenericConstantActionFactory; import org.apache.derby.impl.sql.execute.GenericExecutionFactory; -import org.apache.derby.catalog.UUID; - -import org.apache.derby.iapi.reference.SQLState; import org.apache.derby.iapi.reference.DB2Limit; import org.apache.derby.iapi.reference.JDBC20Translation; import org.apache.derby.iapi.reference.JDBC30Translation; import java.util.Properties; -import java.util.Enumeration; import java.sql.DatabaseMetaData; import java.sql.Connection; -import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.ResultSet; -import java.sql.ResultSetMetaData; import java.sql.Types; import java.io.IOException; @@ -2414,7 +2400,7 @@ * @see Connection */ public boolean supportsResultSetConcurrency(int type, int concurrency) { - //FORWARD_ONLY + CONCUR_UPDATABLE combination is supported (at this point, delete functionality only) + //FORWARD_ONLY + CONCUR_UPDATABLE combination is supported (at this point, delete and update functionality only) if ((type == JDBC20Translation.TYPE_FORWARD_ONLY) && (concurrency == JDBC20Translation.CONCUR_UPDATABLE)) return true; @@ -2483,6 +2469,8 @@ * @param result set type, i.e. ResultSet.TYPE_XXX * @return true if changes are detected by the resultset type */ + //updatable resultsets are supported for forward only resultset types only. And for forward only + //resultsets, we move to before the next row after a update and that is why updatesAreDetected returns false public boolean updatesAreDetected(int type) { return false; } @@ -2490,13 +2478,15 @@ /** * JDBC 2.0 * - * Determine whether or not a visible row delete can be detected by + * Determine whether or not a visible row delete can be detected by * calling ResultSet.rowDeleted(). If deletesAreDetected() * returns false, then deleted rows are removed from the result set. * * @param result set type, i.e. ResultSet.TYPE_XXX * @return true if changes are detected by the resultset type */ + //updatable resultsets are supported for forward only resultset types only. And for forward only + //resultsets, we move to before the next row after a delete and that is why deletesAreDetected returns false public boolean deletesAreDetected(int type) { return false; } Index: java/engine/org/apache/derby/impl/jdbc/EmbedResultSet20.java =================================================================== --- java/engine/org/apache/derby/impl/jdbc/EmbedResultSet20.java (revision 154683) +++ java/engine/org/apache/derby/impl/jdbc/EmbedResultSet20.java (working copy) @@ -23,7 +23,6 @@ import org.apache.derby.iapi.reference.JDBC20Translation; import org.apache.derby.iapi.reference.SQLState; -import org.apache.derby.iapi.sql.Activation; import org.apache.derby.iapi.sql.ResultSet; import org.apache.derby.iapi.sql.execute.ExecCursorTableReference; @@ -32,7 +31,7 @@ import org.apache.derby.impl.jdbc.Util; import org.apache.derby.iapi.sql.conn.LanguageConnectionContext; import org.apache.derby.iapi.sql.conn.StatementContext; - + import org.apache.derby.iapi.types.DataValueDescriptor; import java.sql.Statement; @@ -71,7 +70,7 @@ ////////////////////////////////////////////////////////////// /** - * This class provides the glue between the Cloudscape + * This class provides the glue between the Derby * resultset and the JDBC resultset, mapping calls-to-calls. */ public EmbedResultSet20(org.apache.derby.impl.jdbc.EmbedConnection conn, @@ -165,32 +164,49 @@ return getBigDecimal(findColumnName(columnName)); } - - - - /** - * JDBC 2.0 - * - * Update a column with a BigDecimal value. - * - * The updateXXX() methods are used to update column values in the - * current row, or the insert row. The updateXXX() methods do not - * update the underlying database, instead the updateRow() or insertRow() - * methods are called to update the database. - * - * @param columnIndex the first column is 1, the second is 2, ... - * @param x the new column value - * @exception SQLException if a database-access error occurs - */ public void updateBigDecimal(int columnIndex, BigDecimal x) throws SQLException { - throw Util.notImplemented(); + try { + getDVDforColumnToBeUpdated(columnIndex, "updateBigDecimal").setValue(x); + } catch (StandardException t) { + throw noStateChangeException(t); } + } + /** + * JDBC 2.0 + * + * Update a column with an Object value. + * + * The updateXXX() methods are used to update column values in the current + * row, or the insert row. The updateXXX() methods do not update the + * underlying database, instead the updateRow() or insertRow() methods are + * called to update the database. + * + * @param columnIndex + * the first column is 1, the second is 2, ... + * @param x + * the new column value + * @exception SQLException + * if a database-access error occurs + */ + public void updateObject(int columnIndex, Object x) throws SQLException { + //If the Object x is the right datatype, this method will eventually call getDVDforColumnToBeUpdated which will check for + //the read only resultset. But for other datatypes of x, we want to catch if this updateObject is being + //issued against a read only resultset. And that is the reason for call to checksBeforeUpdateOrDelete here. + checksBeforeUpdateOrDelete("updateObject", columnIndex); + int colType = getColumnType(columnIndex); + if (x instanceof BigDecimal) { + updateBigDecimal(columnIndex, (BigDecimal) x); + return; + } + super.updateObject(columnIndex, x); + } + /** * JDBC 2.0 - * + * * Update a column with a BigDecimal value. * * The updateXXX() methods are used to update column values in the @@ -204,15 +220,13 @@ */ public void updateBigDecimal(String columnName, BigDecimal x) throws SQLException { - throw Util.notImplemented(); + updateBigDecimal(findColumnName(columnName), x); } - - /** * JDBC 2.0 * - * Returns the value of column @i as a Java object. Use the + * Returns the value of column @i as a Java object. Use the * param map to determine the class from which to construct data of * SQL structured and distinct types. * @@ -356,7 +370,6 @@ throw Util.notImplemented(); } - /** * JDBC 3.0 * Index: java/engine/org/apache/derby/iapi/types/SQLClob.java =================================================================== --- java/engine/org/apache/derby/iapi/types/SQLClob.java (revision 154683) +++ java/engine/org/apache/derby/iapi/types/SQLClob.java (working copy) @@ -242,7 +242,6 @@ public void setValue(byte theValue) throws StandardException { - new Throwable("FRED").printStackTrace(System.out); throwLangSetMismatch("byte"); } Index: java/engine/org/apache/derby/iapi/reference/SQLState.java =================================================================== --- java/engine/org/apache/derby/iapi/reference/SQLState.java (revision 154683) +++ java/engine/org/apache/derby/iapi/reference/SQLState.java (working copy) @@ -1234,6 +1234,7 @@ String LANG_OBSOLETE_PARAMETERS = "XCL10.S"; String LANG_DATA_TYPE_SET_MISMATCH = "XCL12.S"; String LANG_INVALID_PARAM_POSITION = "XCL13.S"; + String LANG_INVALID_COLUMN_POSITION = "XCL14.S"; String LANG_INVALID_COMPARE_TO = "XCL15.S"; String LANG_RESULT_SET_NOT_OPEN = "XCL16.S"; String LANG_MISSING_ROW = "XCL19.S"; @@ -1380,6 +1381,7 @@ //updatable resultset related String UPDATABLE_RESULTSET_API_DISALLOWED = "XJ083.U"; + String COLUMN_NOT_FROM_BASE_TABLE = "XJ084.U"; //following are session severity. String DATABASE_NOT_FOUND = "XJ004.C"; Index: java/engine/org/apache/derby/loc/messages_en.properties =================================================================== --- java/engine/org/apache/derby/loc/messages_en.properties (revision 154683) +++ java/engine/org/apache/derby/loc/messages_en.properties (working copy) @@ -967,6 +967,7 @@ XCL10.S=A PreparedStatement has been recompiled, and the parameters have changed. If you are using JDBC, you must re-prepare the statement. XCL12.S=An attempt was made to put a data value of type ''{0}'' into a data value of type ''{1}''. XCL13.S=The parameter position ''{0}'' is out of range. The number of parameters for this prepared statement is ''{1}''. +XCL14.S=The column position ''{0}'' is out of range. The number of columns for this ResultSet is ''{1}''. XCL15.S=A ClassCastException occurred when calling the compareTo() method on an object ''{0}''. The parameter to compareTo() is of class ''{1}''. XCL16.S=ResultSet not open, operation ''{0}'' not permitted. Verify that autocommit is OFF. XCL17.S=Statement not allowed in this database. @@ -1081,6 +1082,7 @@ XJ077.S=Got an exception when trying to read the first byte/character of the Blob/Clob pattern using getBytes/getSubString. XJ082.U=BLOB/CLOB values are not allowed as method parameters or receiver. XJ083.U=''{0}'' not allowed because the ResultSet is not an updatable ResultSet. +XJ084.U=Column does not correspond to a column in the base table. Can't issue {0} on this column. 0A000.S=Feature not implemented: {0}. Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/build.xml =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/tests/lang/build.xml (revision 154683) +++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/build.xml (working copy) @@ -67,6 +67,7 @@ + @@ -93,6 +94,7 @@ + Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java (revision 154683) +++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java (working copy) @@ -33,21 +33,169 @@ import org.apache.derby.tools.ij; import org.apache.derby.tools.JDBCDisplayUtil; +import org.apache.derby.iapi.services.info.JVMInfo; +import java.math.BigDecimal; +import java.sql.Array; +import java.sql.Blob; +import java.sql.Clob; +import java.sql.Date; +import java.sql.Time; +import java.sql.Timestamp; + /** - This tests JDBC 2.0 updateable resutlset - deleteRow api + This tests JDBC 2.0 updateable resutlset - deleteRow, updateRow api */ public class updatableResultSet { private static Connection conn; private static DatabaseMetaData dbmt; private static Statement stmt, stmt1; - private static ResultSet rs; + private static ResultSet rs, rs1; private static PreparedStatement pStmt = null; private static CallableStatement callStmt = null; + //test all the supported SQL datatypes using updateXXX methods + private static String[] allSQLTypes = + { + "SMALLINT", + "INTEGER", + "BIGINT", + "DECIMAL(10,5)", + "REAL", + "DOUBLE", + "CHAR(60)", + "VARCHAR(60)", + "LONG VARCHAR", + "CHAR(2) FOR BIT DATA", + "VARCHAR(2) FOR BIT DATA", + "LONG VARCHAR FOR BIT DATA", + "CLOB(1k)", + "DATE", + "TIME", + "TIMESTAMP", + "BLOB(1k)", + + }; + + //names for column names to test all the supported SQL datatypes using updateXXX methods + private static String[] ColumnNames = + { + "SMALLINTCOL", + "INTEGERCOL", + "BIGINTCOL", + "DECIMALCOL", + "REALCOL", + "DOUBLECOL", + "CHARCOL", + "VARCHARCOL", + "LONGVARCHARCOL", + "CHARFORBITCOL", + "VARCHARFORBITCOL", + "LVARCHARFORBITCOL", + "CLOBCOL", + "DATECOL", + "TIMECOL", + "TIMESTAMPCOL", + "BLOBCOL", + + }; + + //data to test all the supported SQL datatypes using updateXXX methods + private static String[][]SQLData = + { + {"11","22"}, // SMALLINT + {"111","1111"}, // INTEGER + {"22","222"}, // BIGINT + {"3.3","3.33"}, // DECIMAL(10,5) + {"4.4","4.44"}, // REAL, + {"5.5","5.55"}, // DOUBLE + {"'1992-01-06'","'1992'"}, // CHAR(60) + {"'1992-01-07'","'1992'"}, //VARCHAR(60)", + {"'1992-01-08'","'1992'"}, // LONG VARCHAR + {"X'10'","X'10aa'"}, // CHAR(2) FOR BIT DATA + {"X'10'","X'10bb'"}, // VARCHAR(2) FOR BIT DATA + {"X'10'","X'10cc'"}, //LONG VARCHAR FOR BIT DATA + {"'13'","'14'"}, //CLOB(1k) + {"'2000-01-01'","'2000-01-01'"}, // DATE + {"'15:30:20'","'15:30:20'"}, // TIME + {"'2000-01-01 15:30:20'","'2000-01-01 15:30:20'"}, // TIMESTAMP + {"X'1020'","X'10203040'"} // BLOB + }; + + //used for printing useful messages about the test + private static String[] allUpdateXXXNames = + { + "updateShort", + "updateInt", + "updateLong", + "updateBigDecimal", + "updateFloat", + "updateDouble", + "updateString", + "updateAsciiStream", + "updateCharacterStream", + "updateByte", + "updateBytes", + "updateBinaryStream", + "updateClob", + "updateDate", + "updateTime", + "updateTimestamp", + "updateBlob", + "updateBoolean", + "updateNull", + "updateArray", + "updateRef" + + }; + + + //I have constructed following table based on if combination of datatype and updateXXX method would work or not. + public static final String[][] updateXXXRulesTable = { + + // Types. u u u u u u u u u u u u u u u u u u u u u + // p p p p p p p p p p p p p p p p p p p p p + // d d d d d d d d d d d d d d d d d d d d d + // a a a a a a a a a a a a a a a a a a a a a + // t t t t t t t t t t t t t t t t t t t t t + // e e e e e e e e e e e e e e e e e e e e e + // S I L B F D S A C B B B C D T T B B N A R + // h n o i l o t s h y y i l a i i l o u r e + // o t n g o u r c a t t n o t m m o o l r f + // r g D a b i i r e e a b e e e b l l a + // t e t l n i c s r s e y + // c e g S t y t a + // i t e S a n + // m r r t m + // a e S r p + // l a t e + // m r a + // e m + // a + // m +/* 0 SMALLINT */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" }, +/* 1 INTEGER */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" }, +/* 2 BIGINT */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" }, +/* 3 DECIMAL */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" }, +/* 4 REAL */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" }, +/* 5 DOUBLE */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" }, +/* 6 CHAR */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "PASS", "PASS", "ERROR", "ERROR" }, +/* 7 VARCHAR */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "PASS", "PASS", "ERROR", "ERROR" }, +/* 8 LONGVARCHAR */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "PASS", "PASS", "ERROR", "ERROR" }, +/* 9 CHAR FOR BIT */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" }, +/* 10 VARCH. BIT */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" }, +/* 11 LONGVAR. BIT */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" }, +/* 12 CLOB */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" }, +/* 13 DATE */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" }, +/* 14 TIME */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" }, +/* 15 TIMESTAMP */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" }, +/* 16 BLOB */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "PASS", "ERROR", "ERROR" }, + + }; + public static void main(String[] args) { - System.out.println("Start testing delete using JDBC2.0 updateable resultset apis"); + System.out.println("Start testing delete and update using JDBC2.0 updateable resultset apis"); try { // use the ij utility to read the property file and @@ -67,15 +215,15 @@ warnings = warnings.getNextWarning(); } conn.clearWarnings(); - System.out.println("requested TYPE_SCROLL_INSENSITIVE, CONCUR_UPDATABLE but that is not supported"); - System.out.println("Make sure that we got TYPE_SCROLL_INSENSITIVE? " + (stmt.getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE)); - System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (stmt.getResultSetConcurrency() == ResultSet.CONCUR_READ_ONLY)); - dbmt = conn.getMetaData(); - System.out.println("ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? " + dbmt.ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)); - System.out.println("othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? " + dbmt.othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)); - System.out.println("deletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE)? " + dbmt.deletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE)); - System.out.println("JDBC 2.0 updatable resultset api will fail on this resultset because this is not an updatable resultset"); - rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE"); + System.out.println("requested TYPE_SCROLL_INSENSITIVE, CONCUR_UPDATABLE but that is not supported"); + System.out.println("Make sure that we got TYPE_SCROLL_INSENSITIVE? " + (stmt.getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE)); + System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (stmt.getResultSetConcurrency() == ResultSet.CONCUR_READ_ONLY)); + dbmt = conn.getMetaData(); + System.out.println("ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? " + dbmt.ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)); + System.out.println("othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? " + dbmt.othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)); + System.out.println("deletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE)? " + dbmt.deletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE)); + System.out.println("JDBC 2.0 updatable resultset api will fail on this resultset because this is not an updatable resultset"); + rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE"); rs.next(); try { rs.deleteRow(); @@ -87,6 +235,16 @@ else dumpSQLExceptions(e); } + try { + rs.updateRow(); + System.out.println("FAIL!!! updateRow should have failed because Derby does not yet support scroll insensitive updatable resultsets"); + } + catch (SQLException e) { + if (e.getSQLState().equals("XJ083")) + System.out.println("Got expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } rs.next(); //have to close the resultset because by default, resultsets are held open over commit rs.close(); @@ -100,9 +258,9 @@ } conn.clearWarnings(); System.out.println("requested TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE but that is not supported"); - System.out.println("Make sure that we got TYPE_SCROLL_INSENSITIVE? " + (stmt.getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE)); - System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (stmt.getResultSetConcurrency() == ResultSet.CONCUR_READ_ONLY)); - System.out.println("JDBC 2.0 updatable resultset api will fail on this resultset because this is not an updatable resultset"); + System.out.println("Make sure that we got TYPE_SCROLL_INSENSITIVE? " + (stmt.getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE)); + System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (stmt.getResultSetConcurrency() == ResultSet.CONCUR_READ_ONLY)); + System.out.println("JDBC 2.0 updatable resultset api will fail on this resultset because this is not an updatable resultset"); rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE"); rs.next(); try { @@ -115,19 +273,9 @@ else dumpSQLExceptions(e); } - rs.next();//make sure rs.next() does not fail because of earlier deleteRow - //have to close the resultset because by default, resultsets are held open over commit - rs.close(); - - System.out.println("---Negative Test3 - request a read only resultset and attempt deleteRow on it"); - stmt = conn.createStatement();//the default is a read only forward only resultset - rs = stmt.executeQuery("select * from t1"); - System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY)); - rs.next(); - System.out.println("Now attempting to send a deleteRow on a read only resultset."); try { - rs.deleteRow(); - System.out.println("FAIL!!! deleteRow should have failed because this is a read only resultset"); + rs.updateRow(); + System.out.println("FAIL!!! updateRow should have failed because Derby does not yet support scroll sensitive updatable resultsets"); } catch (SQLException e) { if (e.getSQLState().equals("XJ083")) @@ -135,15 +283,16 @@ else dumpSQLExceptions(e); } + rs.next(); //have to close the resultset because by default, resultsets are held open over commit rs.close(); - System.out.println("---Negative Test4 - request a read only resultset and send a sql with FOR UPDATE clause and attempt deleteRow on it"); + System.out.println("---Negative Test3 - request a read only resultset and attempt deleteRow and updateRow on it"); stmt = conn.createStatement();//the default is a read only forward only resultset - rs = stmt.executeQuery("select * from t1 FOR UPDATE"); + rs = stmt.executeQuery("select * from t1"); System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY)); rs.next(); - System.out.println("Now attempting to send a deleteRow on a read only resultset with FOR UPDATE clause in the SELECT sql."); + System.out.println("Now attempting to send a deleteRow on a read only resultset."); try { rs.deleteRow(); System.out.println("FAIL!!! deleteRow should have failed because this is a read only resultset"); @@ -154,25 +303,10 @@ else dumpSQLExceptions(e); } - //have to close the resultset because by default, resultsets are held open over commit - rs.close(); - - System.out.println("---Negative Test5 - request updatable resultset for sql with no FOR UPDATE clause"); - stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); - rs = stmt.executeQuery("select * from t1");//notice that we forgot to give mandatory FOR UPDATE clause for updatable resultset - System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY)); - warnings = rs.getWarnings(); - while (warnings != null) - { - System.out.println("Expected warnings on resultset = " + warnings); - warnings = warnings.getNextWarning(); - } - rs.clearWarnings(); - rs.next(); - System.out.println("Now attempting to send a delete on a sql with no FOR UPDATE clause."); + System.out.println("Now attempting to send an updateRow on a read only resultset."); try { - rs.deleteRow(); - System.out.println("FAIL!!! deleteRow should have failed on sql with no FOR UPDATE clause"); + rs.updateRow(); + System.out.println("FAIL!!! updateRow should have failed because this is a read only resultset"); } catch (SQLException e) { if (e.getSQLState().equals("XJ083")) @@ -183,19 +317,86 @@ //have to close the resultset because by default, resultsets are held open over commit rs.close(); + System.out.println("---Negative Test4 - request a read only resultset and send a sql with FOR UPDATE clause and attempt deleteRow/updateRow on it"); + stmt = conn.createStatement();//the default is a read only forward only resultset + rs = stmt.executeQuery("select * from t1 FOR UPDATE"); + System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY)); + rs.next(); + System.out.println("Now attempting to send a deleteRow on a read only resultset with FOR UPDATE clause in the SELECT sql."); + try { + rs.deleteRow(); + System.out.println("FAIL!!! deleteRow should have failed because this is a read only resultset"); + } + catch (SQLException e) { + if (e.getSQLState().equals("XJ083")) + System.out.println("Got expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + System.out.println("Now attempting to send a updateRow on a read only resultset with FOR UPDATE clause in the SELECT sql."); + try { + rs.updateRow(); + System.out.println("FAIL!!! updateRow should have failed because this is a read only resultset"); + } + catch (SQLException e) { + if (e.getSQLState().equals("XJ083")) + System.out.println("Got expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + //have to close the resultset because by default, resultsets are held open over commit + rs.close(); + + System.out.println("---Negative Test5 - request updatable resultset for sql with no FOR UPDATE clause"); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + rs = stmt.executeQuery("select * from t1");//notice that we forgot to give mandatory FOR UPDATE clause for updatable resultset + System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY)); + warnings = rs.getWarnings(); + while (warnings != null) + { + System.out.println("Expected warnings on resultset = " + warnings); + warnings = warnings.getNextWarning(); + } + rs.clearWarnings(); + rs.next(); + System.out.println("Now attempting to send a delete on a sql with no FOR UPDATE clause."); + try { + rs.deleteRow(); + System.out.println("FAIL!!! deleteRow should have failed on sql with no FOR UPDATE clause"); + } + catch (SQLException e) { + if (e.getSQLState().equals("XJ083")) + System.out.println("Got expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + System.out.println("Now attempting to send a updateRow on a sql with no FOR UPDATE clause."); + try { + rs.updateRow(); + System.out.println("FAIL!!! updateRow should have failed on sql with no FOR UPDATE clause"); + } + catch (SQLException e) { + if (e.getSQLState() != null && e.getSQLState().equals("XJ083")) + System.out.println("Got expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + //have to close the resultset because by default, resultsets are held open over commit + rs.close(); + System.out.println("---Negative Test6 - request updatable resultset for sql with FOR READ ONLY clause"); stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); rs = stmt.executeQuery("select * from t1 FOR READ ONLY"); - System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY)); - warnings = rs.getWarnings(); - while (warnings != null) - { - System.out.println("Expected warnings on resultset = " + warnings); - warnings = warnings.getNextWarning(); - } - rs.clearWarnings(); - rs.next(); - System.out.println("Now attempting to send a delete on a sql with FOR READ ONLY clause."); + System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY)); + warnings = rs.getWarnings(); + while (warnings != null) + { + System.out.println("Expected warnings on resultset = " + warnings); + warnings = warnings.getNextWarning(); + } + rs.clearWarnings(); + rs.next(); + System.out.println("Now attempting to send a delete on a sql with FOR READ ONLY clause."); try { rs.deleteRow(); System.out.println("FAIL!!! deleteRow should have failed on sql with FOR READ ONLY clause"); @@ -206,14 +407,25 @@ else dumpSQLExceptions(e); } - //have to close the resultset because by default, resultsets are held open over commit + System.out.println("Now attempting to send a updateRow on a sql with FOR READ ONLY clause."); + try { + rs.updateRow(); + System.out.println("FAIL!!! updateRow should have failed on sql with FOR READ ONLY clause"); + } + catch (SQLException e) { + if (e.getSQLState().equals("XJ083")) + System.out.println("Got expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + //have to close the resultset because by default, resultsets are held open over commit rs.close(); - System.out.println("---Negative Test7 - attempt to deleteRow on updatable resultset when the resultset is not positioned on a row"); + System.out.println("---Negative Test7 - attempt to deleteRow & updateRow on updatable resultset when the resultset is not positioned on a row"); stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE"); System.out.println("Make sure that we got CONCUR_UPDATABLE? " + (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE)); - System.out.println("Now attempt a deleteRow without first doing next on the resultset."); + System.out.println("Now attempt a deleteRow without first doing next on the resultset."); try { rs.deleteRow(); System.out.println("FAIL!!! deleteRow should have failed because resultset is not on a row"); @@ -224,7 +436,18 @@ else dumpSQLExceptions(e); } - while (rs.next());//read all the rows from the resultset and position after the last row + System.out.println("Now attempt a updateRow without first doing next on the resultset."); + try { + rs.updateRow(); + System.out.println("FAIL!!! updateRow should have failed because resultset is not on a row"); + } + catch (SQLException e) { + if (e.getSQLState().equals("24000")) + System.out.println("Got expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + while (rs.next());//read all the rows from the resultset and position after the last row System.out.println("ResultSet is positioned after the last row. attempt to deleteRow at this point should fail!"); try { rs.deleteRow(); @@ -236,9 +459,20 @@ else dumpSQLExceptions(e); } + System.out.println("ResultSet is positioned after the last row. attempt to updateRow at this point should fail!"); + try { + rs.updateRow(); + System.out.println("FAIL!!! updateRow should have failed because resultset is after the last row"); + } + catch (SQLException e) { + if (e.getSQLState().equals("24000")) + System.out.println("Got expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } rs.close(); - System.out.println("---Negative Test8 - attempt deleteRow on updatable resultset after closing the resultset"); + System.out.println("---Negative Test8 - attempt deleteRow & updateRow on updatable resultset after closing the resultset"); stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE"); System.out.println("Make sure that we got CONCUR_UPDATABLE? " + (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE)); @@ -254,6 +488,16 @@ else dumpSQLExceptions(e); } + try { + rs.updateRow(); + System.out.println("FAIL!!! updateRow should have failed because resultset is closed"); + } + catch (SQLException e) { + if (e.getSQLState().equals("XCL16")) + System.out.println("Got expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } System.out.println("---Negative Test9 - try updatable resultset on system table"); try { @@ -266,7 +510,7 @@ else dumpSQLExceptions(e); } - + System.out.println("---Negative Test10 - try updatable resultset on a view"); try { rs = stmt.executeQuery("SELECT * FROM v1 FOR UPDATE"); @@ -298,35 +542,74 @@ rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE"); rs.next(); System.out.println("Opened an updatable resultset. Now trying to drop that table through another Statement"); - stmt1 = conn.createStatement(); + stmt1 = conn.createStatement(); try { stmt1.executeUpdate("drop table t1"); System.out.println("FAIL!!! drop table should have failed because the updatable resultset is still open"); } catch (SQLException e) { - if (e.getSQLState().equals("X0X95")) { + if (e.getSQLState().equals("X0X95")){ System.out.println("expected exception " + e.getMessage()); } else dumpSQLExceptions(e); } System.out.println("Since autocommit is on, the drop table exception resulted in a runtime rollback causing updatable resultset object to close"); + try { + rs.updateRow(); + System.out.println("FAIL!!! resultset should have been closed at this point and updateRow should have failed"); + } + catch (SQLException e) { + if (e.getSQLState().equals("XCL16")) + System.out.println("expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + try { + rs.deleteRow(); + System.out.println("FAIL!!! resultset should have been closed at this point and deleteRow should have failed"); + } + catch (SQLException e) { + if (e.getSQLState().equals("XCL16")) + System.out.println("expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + + System.out.println("---Negative Test13 - foreign key constraint failure will cause deleteRow to fail"); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + rs = stmt.executeQuery("SELECT 1, 2 FROM tableWithPrimaryKey FOR UPDATE"); + rs.next(); try { rs.deleteRow(); + System.out.println("FAIL!!! deleteRow should have failed because it will cause foreign key constraint failure"); } catch (SQLException e) { - if (e.getSQLState().equals("XCL16")) + if (e.getSQLState().equals("23503")) System.out.println("expected exception " + e.getMessage()); else dumpSQLExceptions(e); } + System.out.println("Since autocommit is on, the constraint exception resulted in a runtime rollback causing updatable resultset object to close"); + try { + rs.next(); + System.out.println("FAIL!!! next should have failed because foreign key constraint failure resulted in a runtime rollback"); + } + catch (SQLException e) { + if (e.getSQLState().equals("XCL16")) + System.out.println("expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } - System.out.println("---Negative Test13 - foreign key constraint failure will cause deleteRow to fail"); + System.out.println("---Negative Test14 - foreign key constraint failure will cause updateRow to fail"); stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); - rs = stmt.executeQuery("SELECT 1, 2 FROM tableWithPrimaryKey FOR UPDATE"); + rs = stmt.executeQuery("SELECT c1, c2 FROM tableWithPrimaryKey FOR UPDATE"); rs.next(); + rs.updateInt(1,11); + rs.updateInt(2,22); try { - rs.deleteRow(); - System.out.println("FAIL!!! deleteRow should have failed because it will cause foreign key constraint failure"); + rs.updateRow(); + System.out.println("FAIL!!! updateRow should have failed because it will cause foreign key constraint failure"); } catch (SQLException e) { if (e.getSQLState().equals("23503")) @@ -335,23 +618,54 @@ dumpSQLExceptions(e); } System.out.println("Since autocommit is on, the constraint exception resulted in a runtime rollback causing updatable resultset object to close"); + try { + rs.next(); + System.out.println("FAIL!!! next should have failed because foreign key constraint failure resulted in a runtime rollback"); + } + catch (SQLException e) { + if (e.getSQLState().equals("XCL16")) + System.out.println("expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + + System.out.println("---Negative Test15 - Can't call updateXXX methods on columns that do not correspond to a column in the table"); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + rs = stmt.executeQuery("SELECT 1, 2 FROM tableWithPrimaryKey FOR UPDATE"); + rs.next(); try { - rs.next(); - System.out.println("FAIL!!! next should have failed because foreign key constraint failure resulted in a runtime rollback"); + rs.updateInt(1,22); + System.out.println("FAIL!!! updateInt should have failed because it is trying to update a column that does not correspond to column in base table"); } catch (SQLException e) { - if (e.getSQLState().equals("XCL16")) + if (e.getSQLState().equals("XJ084")) System.out.println("expected exception " + e.getMessage()); else dumpSQLExceptions(e); } - System.out.println("---Positive Test1 - request updatable resultset for forward only type resultset"); + System.out.println("---Negative Test16 - Call updateXXX method on out of the range column"); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + rs = stmt.executeQuery("SELECT c1, c2 FROM t1 FOR UPDATE"); + rs.next(); + System.out.println("There are only 2 columns in the select list and we are trying to send updateXXX on column position 3"); + try { + rs.updateInt(3,22); + System.out.println("FAIL!!! updateInt should have failed because there are only 2 columns in the select list"); + } + catch (SQLException e) { + if (e.getSQLState().equals("XCL14")) + System.out.println("expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + + System.out.println("---Positive Test1a - request updatable resultset for forward only type resultset"); stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); warnings = conn.getWarnings(); while (warnings != null) { - System.out.println("warnings = " + warnings); + System.out.println("Unexpected warnings = " + warnings); warnings = warnings.getNextWarning(); } System.out.println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE"); @@ -364,8 +678,19 @@ System.out.println("column 2 on this row before deleteRow is " + rs.getString(2)); rs.deleteRow(); System.out.println("Since after deleteRow(), ResultSet is positioned before the next row, getXXX will fail"); + try { + System.out.println("column 1 on this deleted row is " + rs.getInt(1)); + } + catch (SQLException e) { + if (e.getSQLState().equals("24000")) + System.out.println("Got expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail"); try { - System.out.println("column 1 on this deleted row is " + rs.getInt(1)); + rs.deleteRow(); + System.out.println("FAIL!!! deleteRow should have failed because ResultSet is not positioned on a row"); } catch (SQLException e) { if (e.getSQLState().equals("24000")) @@ -373,10 +698,44 @@ else dumpSQLExceptions(e); } - System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail"); + System.out.println("Position the ResultSet with next()"); + rs.next(); + System.out.println("Should be able to deletRow() on the current row now"); + rs.deleteRow(); + //have to close the resultset because by default, resultsets are held open over commit + rs.close(); + + System.out.println("---Positive Test1b - request updatable resultset for forward only type resultset"); + reloadData(); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + warnings = conn.getWarnings(); + while (warnings != null) + { + System.out.println("Unexpected warnings = " + warnings); + warnings = warnings.getNextWarning(); + } + rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE"); + rs.next(); + System.out.println("column 1 on this row before updateInt is " + rs.getInt(1)); + rs.updateInt(1,234); + System.out.println("column 1 on this row after updateInt is " + rs.getInt(1)); + System.out.println("column 2 on this row before updateString is " + rs.getString(2)); + System.out.println("now updateRow on the row"); + rs.updateRow(); + System.out.println("Since after updateRow(), ResultSet is positioned before the next row, getXXX will fail"); + try { + System.out.println("column 1 on this updateRow row is " + rs.getInt(1)); + } + catch (SQLException e) { + if (e.getSQLState().equals("24000")) + System.out.println("Got expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + System.out.println("calling updateRow again w/o first positioning the ResultSet on the next row will fail"); try { - rs.deleteRow(); - System.out.println("FAIL!!! deleteRow should have failed because it can't be called more than once on the same row"); + rs.updateRow(); + System.out.println("FAIL!!! updateRow should have failed because ResultSet is not positioned on a row"); } catch (SQLException e) { if (e.getSQLState().equals("24000")) @@ -384,17 +743,18 @@ else dumpSQLExceptions(e); } - System.out.println("Position the ResultSet with next()"); + System.out.println("Position the ResultSet with next()"); rs.next(); - System.out.println("Should be able to deletRow() on the current row now"); - rs.deleteRow(); + System.out.println("Should be able to updateRow() on the current row now"); + rs.updateString(2,"234"); + rs.updateRow(); //have to close the resultset because by default, resultsets are held open over commit rs.close(); System.out.println("---Positive Test2 - even if no columns from table specified in the column list, we should be able to get updatable resultset"); reloadData(); System.out.println("total number of rows in T1 "); - dumpRS(stmt.executeQuery("select count(*) from t1")); + dumpRS(stmt.executeQuery("select count(*) from t1")); rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE"); rs.next(); System.out.println("column 1 on this row is " + rs.getInt(1)); @@ -402,9 +762,9 @@ //have to close the resultset because by default, resultsets are held open over commit rs.close(); System.out.println("total number of rows in T1 after one deleteRow is "); - dumpRS(stmt.executeQuery("select count(*) from t1")); + dumpRS(stmt.executeQuery("select count(*) from t1")); - System.out.println("---Positive Test3 - use prepared statement with concur updatable status"); + System.out.println("---Positive Test3a - use prepared statement with concur updatable status to test deleteRow"); reloadData(); pStmt = conn.prepareStatement("select * from t1 where c1>? for update", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); System.out.println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE"); @@ -416,8 +776,19 @@ System.out.println("column 1 on this row is " + rs.getInt(1)); rs.deleteRow(); System.out.println("Since after deleteRow(), ResultSet is positioned before the next row, getXXX will fail"); + try { + System.out.println("column 1 on this deleted row is " + rs.getInt(1)); + } + catch (SQLException e) { + if (e.getSQLState().equals("24000")) + System.out.println("Got expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail"); try { - System.out.println("column 1 on this deleted row is " + rs.getInt(1)); + rs.deleteRow(); + System.out.println("FAIL!!! deleteRow should have failed because it can't be called more than once on the same row"); } catch (SQLException e) { if (e.getSQLState().equals("24000")) @@ -425,10 +796,40 @@ else dumpSQLExceptions(e); } - System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail"); + System.out.println("Position the ResultSet with next()"); + rs.next(); + System.out.println("Should be able to deletRow() on the current row now"); + rs.deleteRow(); + //have to close the resultset because by default, resultsets are held open over commit + rs.close(); + + System.out.println("---Positive Test3b - use prepared statement with concur updatable status to test updateXXX"); + reloadData(); + pStmt = conn.prepareStatement("select * from t1 where c1>? for update", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + System.out.println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE"); + System.out.println("got TYPE_FORWARD_ONLY? " + (pStmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY)); + System.out.println("got CONCUR_UPDATABLE? " + (pStmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE)); + pStmt.setInt(1,0); + rs = pStmt.executeQuery(); + rs.next(); + System.out.println("column 1 on this row is " + rs.getInt(1)); + rs.updateInt(1,5); + System.out.println("column 1 on this row after updateInt is " + rs.getInt(1)); + rs.updateRow(); + System.out.println("Since after updateRow(), ResultSet is positioned before the next row, getXXX will fail"); + try { + System.out.println("column 1 on this updated row is " + rs.getInt(1)); + } + catch (SQLException e) { + if (e.getSQLState().equals("24000")) + System.out.println("Got expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + System.out.println("calling updateRow/updateXXX again w/o first positioning the ResultSet on the next row will fail"); try { - rs.deleteRow(); - System.out.println("FAIL!!! deleteRow should have failed because it can't be called more than once on the same row"); + rs.updateInt(1,0); + System.out.println("FAIL!!! updateXXX should have failed because resultset is not positioned on a row"); } catch (SQLException e) { if (e.getSQLState().equals("24000")) @@ -436,10 +837,30 @@ else dumpSQLExceptions(e); } - System.out.println("Position the ResultSet with next()"); + try { + rs.updateRow(); + System.out.println("FAIL!!! updateRow should have failed because resultset is not positioned on a row"); + } + catch (SQLException e) { + if (e.getSQLState().equals("24000")) + System.out.println("Got expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + try { + rs.cancelRowUpdates(); + System.out.println("FAIL!!! cancelRowUpdates should have failed because resultset is not positioned on a row"); + } + catch (SQLException e) { + if (e.getSQLState().equals("24000")) + System.out.println("Got expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + System.out.println("Position the ResultSet with next()"); rs.next(); - System.out.println("Should be able to deletRow() on the current row now"); - rs.deleteRow(); + System.out.println("Should be able to cancelRowUpdates() on the current row now"); + rs.cancelRowUpdates(); //have to close the resultset because by default, resultsets are held open over commit rs.close(); @@ -454,17 +875,17 @@ System.out.println("column 1 on this row is " + rs.getInt(1)); rs.deleteRow(); System.out.println("Since after deleteRow(), ResultSet is positioned before the next row, getXXX will fail"); + try { + System.out.println("column 1 on this deleted row is " + rs.getInt(1)); + } + catch (SQLException e) { + if (e.getSQLState().equals("24000")) + System.out.println("Got expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail"); try { - System.out.println("column 1 on this deleted row is " + rs.getInt(1)); - } - catch (SQLException e) { - if (e.getSQLState().equals("24000")) - System.out.println("Got expected exception " + e.getMessage()); - else - dumpSQLExceptions(e); - } - System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail"); - try { rs.deleteRow(); System.out.println("FAIL!!! deleteRow should have failed because it can't be called more than once on the same row"); } @@ -477,7 +898,7 @@ System.out.println("Position the ResultSet with next()"); rs.next(); System.out.println("Should be able to deletRow() on the current row now"); - rs.deleteRow(); + rs.deleteRow(); //have to close the resultset because by default, resultsets are held open over commit rs.close(); @@ -489,27 +910,48 @@ System.out.println("column 1 on this row is " + rs.getInt(1)); System.out.println("now try to delete row when primary key is not selected for that row"); rs.deleteRow(); + rs.next(); + rs.updateLong(1,123); + rs.updateRow(); //have to close the resultset because by default, resultsets are held open over commit rs.close(); - System.out.println("---Positive Test6 - For Forward Only resultsets, DatabaseMetaData will return false for ownDeletesAreVisible and deletesAreDetected"); - System.out.println("---This is because, after deleteRow, we position the ResultSet before the next row. We don't make a hole for the deleted row and then stay on that deleted hole"); + System.out.println("---Positive Test6a - For Forward Only resultsets, DatabaseMetaData will return false for ownDeletesAreVisible and deletesAreDetected"); + System.out.println("---This is because, after deleteRow, we position the ResultSet before the next row. We don't make a hole for the deleted row and then stay on that deleted hole"); dbmt = conn.getMetaData(); System.out.println("ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)); System.out.println("othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)); System.out.println("deletesAreDetected(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.deletesAreDetected(ResultSet.TYPE_FORWARD_ONLY)); reloadData(); - stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); - rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE of c1"); - rs.next(); - System.out.println("The JDBC program should look at rowDeleted only if deletesAreDetected returns true"); - System.out.println("Since Derby returns false for detlesAreDetected for FORWARD_ONLY updatable resultset,the program should not rely on rs.rowDeleted() for FORWARD_ONLY updatable resultsets"); - System.out.println("Have this call to rs.rowDeleted() just to make sure the method does always return false? " + rs.rowDeleted()); - rs.deleteRow(); - System.out.println("Have this call to rs.rowDeleted() just to make sure the method does always return false? " + rs.rowDeleted()); - rs.close(); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE of c1"); + rs.next(); + System.out.println("The JDBC program should look at rowDeleted only if deletesAreDetected returns true"); + System.out.println("Since Derby returns false for detlesAreDetected for FORWARD_ONLY updatable resultset,the program should not rely on rs.rowDeleted() for FORWARD_ONLY updatable resultsets"); + System.out.println("Have this call to rs.rowDeleted() just to make sure the method does always return false? " + rs.rowDeleted()); + rs.deleteRow(); + System.out.println("Have this call to rs.rowDeleted() just to make sure the method does always return false? " + rs.rowDeleted()); + rs.close(); - System.out.println("---Positive Test7 - delete using updatable resultset api from a temporary table"); + System.out.println("---Positive Test6b - For Forward Only resultsets, DatabaseMetaData will return false for ownUpdatesAreVisible and updatesAreDetected"); + System.out.println("---This is because, after updateRow, we position the ResultSet before the next row"); + dbmt = conn.getMetaData(); + System.out.println("ownUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.ownUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)); + System.out.println("othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)); + System.out.println("updatesAreDetected(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.updatesAreDetected(ResultSet.TYPE_FORWARD_ONLY)); + reloadData(); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1"); + rs.next(); + System.out.println("The JDBC program should look at rowUpdated only if updatesAreDetected returns true"); + System.out.println("Since Derby returns false for updatesAreDetected for FORWARD_ONLY updatable resultset,the program should not rely on rs.rowUpdated() for FORWARD_ONLY updatable resultsets"); + System.out.println("Have this call to rs.rowUpdated() just to make sure the method does always return false? " + rs.rowUpdated()); + rs.updateLong(1,123); + rs.updateRow(); + System.out.println("Have this call to rs.rowUpdated() just to make sure the method does always return false? " + rs.rowUpdated()); + rs.close(); + + System.out.println("---Positive Test7a - delete using updatable resultset api from a temporary table"); stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); stmt.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged"); stmt.executeUpdate("insert into SESSION.t2 values(21, 1)"); @@ -526,7 +968,26 @@ rs.close(); stmt.executeUpdate("DROP TABLE SESSION.t2"); - System.out.println("---Positive Test8 - change the name of the resultset and see if deleteRow still works"); + System.out.println("---Positive Test7b - update using updatable resultset api from a temporary table"); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + stmt.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged"); + stmt.executeUpdate("insert into SESSION.t3 values(21, 1)"); + stmt.executeUpdate("insert into SESSION.t3 values(22, 1)"); + System.out.println("following rows in temp table before deleteRow"); + dumpRS(stmt.executeQuery("select * from SESSION.t3")); + rs = stmt.executeQuery("select c31 from session.t3 for update"); + rs.next(); + rs.updateLong(1,123); + rs.updateRow(); + rs.next(); + rs.updateLong(1,123); + rs.updateRow(); + System.out.println("As expected, updated rows in temp table after updateRow"); + dumpRS(stmt.executeQuery("select * from SESSION.t3")); + rs.close(); + stmt.executeUpdate("DROP TABLE SESSION.t3"); + + System.out.println("---Positive Test8a - change the name of the resultset and see if deleteRow still works"); reloadData(); stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); System.out.println("change the cursor name(case sensitive name) with setCursorName and then try to deleteRow"); @@ -540,15 +1001,51 @@ rs.deleteRow(); rs.close(); - System.out.println("---Positive Test9 - using correlation name for the table in the select sql is not a problem"); + System.out.println("---Positive Test8b - change the name of the resultset and see if updateRow still works"); reloadData(); + System.out.println("change the cursor name one more time with setCursorName and then try to updateRow"); stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + System.out.println("change the cursor name(case sensitive name) with setCursorName and then try to updateRow"); + stmt.setCursorName("CURSORNOUPDATe");//notice this name is case sensitive + rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1"); + rs.next(); + rs.updateLong(1,123); + stmt.setCursorName("CURSORNOUPDATE1"); + rs.updateRow(); + rs.close(); + + System.out.println("---Positive Test9a - using correlation name for the table in the select sql is not a problem"); + reloadData(); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); rs = stmt.executeQuery("SELECT 1, 2 FROM t1 abcde FOR UPDATE of c1"); rs.next(); System.out.println("column 1 on this row is " + rs.getInt(1)); System.out.println("now try to deleteRow"); rs.deleteRow(); rs.close(); + + System.out.println("---Positive Test9b - using correlation name for column names is not allowed with updateXXX"); + reloadData(); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + System.out.println("Table t1 has following rows"); + dumpRS(stmt.executeQuery("select * from t1")); + rs = stmt.executeQuery("SELECT c1 as col1, c2 as col2 FROM t1 abcde FOR UPDATE of c1"); + rs.next(); + System.out.println("column 1 on this row is " + rs.getInt(1)); + try { + System.out.println("attempt to send updateXXX on correlation name column will fail"); + rs.updateShort(1, (new Integer(123)).shortValue()); + System.out.println("FAIL!!! updateXXX should have failed"); + } + catch (SQLException e) { + if (e.getSQLState().equals("XJ084")) + System.out.println("Got expected exception " + e.getMessage()); + else + dumpSQLExceptions(e); + } + rs.close(); + System.out.println("Table t1 after updateRow has following rows"); + dumpRS(stmt.executeQuery("select * from t1")); System.out.println("---Positive Test10 - 2 updatable resultsets going against the same table, will they conflict?"); conn.setAutoCommit(false); @@ -557,14 +1054,14 @@ stmt1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE"); rs.next(); - ResultSet rs1 = stmt1.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE"); + rs1 = stmt1.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE"); rs1.next(); System.out.println("delete using first resultset"); rs.deleteRow(); try { System.out.println("attempt to send deleteRow on the same row through a different resultset should throw an exception"); rs1.deleteRow(); - System.out.println("FAIL!!! delete using second resultset succedded? " + rs1.rowDeleted()); + System.out.println("FAIL!!! delete using second resultset succedded? "); } catch (SQLException e) { if (e.getSQLState().equals("XCL08")) @@ -573,8 +1070,8 @@ dumpSQLExceptions(e); } System.out.println("Move to next row in the 2nd resultset and then delete using the second resultset"); - rs1.next(); - rs1.deleteRow(); + rs1.next(); + rs1.deleteRow(); rs.close(); rs1.close(); conn.setAutoCommit(true); @@ -591,12 +1088,12 @@ rs.close(); stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)"); - System.out.println("---Positive Test12 - make sure delete trigger gets fired when deleteRow is issued"); + System.out.println("---Positive Test12a - make sure delete trigger gets fired when deleteRow is issued"); reloadData(); stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); System.out.println("Verify that before delete trigger got fired, row count is 0 in deleteTriggerInsertIntoThisTable"); dumpRS(stmt.executeQuery("select count(*) from deleteTriggerInsertIntoThisTable")); - rs = stmt.executeQuery("SELECT * FROM tableWithDeleteTriggers FOR UPDATE"); + rs = stmt.executeQuery("SELECT * FROM table0WithTriggers FOR UPDATE"); rs.next(); System.out.println("column 1 on this row is " + rs.getInt(1)); System.out.println("now try to delete row and make sure that trigger got fired"); @@ -607,121 +1104,1027 @@ //have to close the resultset because by default, resultsets are held open over commit rs.close(); - System.out.println("---Positive Test13 - Another test case for delete trigger"); + System.out.println("---Positive Test12b - make sure update trigger gets fired when updateRow is issued"); + reloadData(); stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); - rs = stmt.executeQuery("SELECT * FROM anotherTableWithDeleteTriggers FOR UPDATE"); + System.out.println("Verify that before update trigger got fired, row count is 0 in updateTriggerInsertIntoThisTable"); + dumpRS(stmt.executeQuery("select count(*) from updateTriggerInsertIntoThisTable")); + rs = stmt.executeQuery("SELECT * FROM table0WithTriggers FOR UPDATE"); rs.next(); System.out.println("column 1 on this row is " + rs.getInt(1)); - System.out.println("this delete row will fire the delete trigger which will delete all the rows from the table and from the resultset"); - rs.deleteRow(); - rs.next(); - try { - rs.deleteRow(); - System.out.println("FAIL!!! there should have be no more rows in the resultset at this point because delete trigger deleted all the rows"); - } - catch (SQLException e) { - if (e.getSQLState().equals("24000")) { - System.out.println("expected exception " + e.getMessage()); - } else - dumpSQLExceptions(e); - } + System.out.println("now try to update row and make sure that trigger got fired"); + rs.updateLong(1,123); + rs.updateRow(); rs.close(); - System.out.println("Verify that delete trigger got fired by verifying the row count to be 0 in anotherTableWithDeleteTriggers"); - dumpRS(stmt.executeQuery("select count(*) from anotherTableWithDeleteTriggers")); + System.out.println("Verify that update trigger got fired by verifying the row count to be 1 in updateTriggerInsertIntoThisTable"); + dumpRS(stmt.executeQuery("select count(*) from updateTriggerInsertIntoThisTable")); //have to close the resultset because by default, resultsets are held open over commit rs.close(); - System.out.println("---Positive Test14 - make sure self referential delete cascade works when deleteRow is issued"); + System.out.println("---Positive Test13a - Another test case for delete trigger"); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + rs = stmt.executeQuery("SELECT * FROM table1WithTriggers FOR UPDATE"); + rs.next(); + System.out.println("column 1 on this row is " + rs.getInt(1)); + System.out.println("this delete row will fire the delete trigger which will delete all the rows from the table and from the resultset"); + rs.deleteRow(); + rs.next(); + try { + rs.deleteRow(); + System.out.println("FAIL!!! there should have be no more rows in the resultset at this point because delete trigger deleted all the rows"); + } + catch (SQLException e) { + if (e.getSQLState().equals("24000")) { + System.out.println("expected exception " + e.getMessage()); + } else + dumpSQLExceptions(e); + } + rs.close(); + System.out.println("Verify that delete trigger got fired by verifying the row count to be 0 in table1WithTriggers"); + dumpRS(stmt.executeQuery("select count(*) from table1WithTriggers")); + //have to close the resultset because by default, resultsets are held open over commit + rs.close(); + + System.out.println("---Positive Test13b - Another test case for update trigger"); + System.out.println("Look at the current contents of table2WithTriggers"); + dumpRS(stmt.executeQuery("select * from table2WithTriggers")); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + rs = stmt.executeQuery("SELECT * FROM table2WithTriggers where c1>1 FOR UPDATE"); + rs.next(); + System.out.println("column 1 on this row is " + rs.getInt(1)); + System.out.println("this update row will fire the update trigger which will update all the rows in the table to have c1=1 and hence no more rows will qualify for the resultset"); + rs.updateLong(1,123); + rs.updateRow(); + rs.next(); + try { + rs.updateRow(); + System.out.println("FAIL!!! there should have be no more rows in the resultset at this point because update trigger made all the rows not qualify for the resultset"); + } + catch (SQLException e) { + if (e.getSQLState().equals("24000")) { + System.out.println("expected exception " + e.getMessage()); + } else + dumpSQLExceptions(e); + } + rs.close(); + System.out.println("Verify that update trigger got fired by verifying that all column c1s have value 1 in table2WithTriggers"); + dumpRS(stmt.executeQuery("select * from table2WithTriggers")); + //have to close the resultset because by default, resultsets are held open over commit + rs.close(); + + System.out.println("---Positive Test14a - make sure self referential delete cascade works when deleteRow is issued"); + dumpRS(stmt.executeQuery("select * from selfReferencingT1")); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + rs = stmt.executeQuery("SELECT * FROM selfReferencingT1 FOR UPDATE"); + rs.next(); + System.out.println("column 1 on this row is " + rs.getString(1)); + System.out.println("this delete row will cause the delete cascade constraint to delete all the rows from the table and from the resultset"); + rs.deleteRow(); + rs.next(); + try { + rs.deleteRow(); + System.out.println("FAIL!!! there should have be no more rows in the resultset at this point because delete cascade deleted all the rows"); + } + catch (SQLException e) { + if (e.getSQLState().equals("24000")) { + System.out.println("expected exception " + e.getMessage()); + } else + dumpSQLExceptions(e); + } + rs.close(); + System.out.println("Verify that delete trigger got fired by verifying the row count to be 0 in selfReferencingT1"); + dumpRS(stmt.executeQuery("select count(*) from selfReferencingT1")); + //have to close the resultset because by default, resultsets are held open over commit + rs.close(); + + System.out.println("---Positive Test14b - make sure self referential update restrict works when updateRow is issued"); + dumpRS(stmt.executeQuery("select * from selfReferencingT2")); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + rs = stmt.executeQuery("SELECT * FROM selfReferencingT2 FOR UPDATE"); + rs.next(); + System.out.println("column 1 on this row is " + rs.getString(1)); + System.out.println("update row should fail because cascade constraint is update restrict"); + rs.updateString(1,"e2"); + try { + rs.updateRow(); + System.out.println("FAIL!!! this update should have caused violation of foreign key constraint"); + } + catch (SQLException e) { + if (e.getSQLState().equals("23503")) { + System.out.println("expected exception " + e.getMessage()); + } else + dumpSQLExceptions(e); + } + //have to close the resultset because by default, resultsets are held open over commit + rs.close(); + + System.out.println("---Positive Test15 - With autocommit off, attempt to drop a table when there is an open updatable resultset on it"); + reloadData(); + conn.setAutoCommit(false); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE"); + rs.next(); + System.out.println("Opened an updatable resultset. Now trying to drop that table through another Statement"); + stmt1 = conn.createStatement(); + try { + stmt1.executeUpdate("drop table t1"); + System.out.println("FAIL!!! drop table should have failed because the updatable resultset is still open"); + } + catch (SQLException e) { + if (e.getSQLState().equals("X0X95")) { + System.out.println("expected exception " + e.getMessage()); + } else + dumpSQLExceptions(e); + } + System.out.println("Since autocommit is off, the drop table exception will NOT result in a runtime rollback and hence updatable resultset object is still open"); + rs.deleteRow(); + rs.close(); + conn.setAutoCommit(true); + + System.out.println("---Positive Test16a - Do deleteRow within a transaction and then rollback the transaction"); + reloadData(); + conn.setAutoCommit(false); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + System.out.println("Verify that before delete trigger got fired, row count is 0 in deleteTriggerInsertIntoThisTable"); + dumpRS(stmt.executeQuery("select count(*) from deleteTriggerInsertIntoThisTable")); + System.out.println("Verify that before deleteRow, row count is 4 in table0WithTriggers"); + dumpRS(stmt.executeQuery("select count(*) from table0WithTriggers")); + rs = stmt.executeQuery("SELECT * FROM table0WithTriggers FOR UPDATE"); + rs.next(); + System.out.println("column 1 on this row is " + rs.getInt(1)); + System.out.println("now try to delete row and make sure that trigger got fired"); + rs.deleteRow(); + rs.close(); + System.out.println("Verify that delete trigger got fired by verifying the row count to be 1 in deleteTriggerInsertIntoThisTable"); + dumpRS(stmt.executeQuery("select count(*) from deleteTriggerInsertIntoThisTable")); + System.out.println("Verify that deleteRow in transaction, row count is 3 in table0WithTriggers"); + dumpRS(stmt.executeQuery("select count(*) from table0WithTriggers")); + //have to close the resultset because by default, resultsets are held open over commit + rs.close(); + conn.rollback(); + System.out.println("Verify that after rollback, row count is back to 0 in deleteTriggerInsertIntoThisTable"); + dumpRS(stmt.executeQuery("select count(*) from deleteTriggerInsertIntoThisTable")); + System.out.println("Verify that after rollback, row count is back to 4 in table0WithTriggers"); + dumpRS(stmt.executeQuery("select count(*) from table0WithTriggers")); + conn.setAutoCommit(true); + + System.out.println("---Positive Test16b - Do updateRow within a transaction and then rollback the transaction"); + reloadData(); + conn.setAutoCommit(false); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + System.out.println("Verify that before update trigger got fired, row count is 0 in updateTriggerInsertIntoThisTable"); + dumpRS(stmt.executeQuery("select count(*) from updateTriggerInsertIntoThisTable")); + System.out.println("Look at the data in table0WithTriggers before trigger gets fired"); + dumpRS(stmt.executeQuery("select * from table0WithTriggers")); + rs = stmt.executeQuery("SELECT * FROM table0WithTriggers FOR UPDATE"); + rs.next(); + System.out.println("column 1 on this row is " + rs.getInt(1)); + System.out.println("now try to update row and make sure that trigger got fired"); + rs.updateLong(1,123); + rs.updateRow(); + rs.close(); + System.out.println("Verify that update trigger got fired by verifying the row count to be 1 in updateTriggerInsertIntoThisTable"); + dumpRS(stmt.executeQuery("select count(*) from updateTriggerInsertIntoThisTable")); + System.out.println("Verify that new data in table0WithTriggers"); + dumpRS(stmt.executeQuery("select * from table0WithTriggers")); + //have to close the resultset because by default, resultsets are held open over commit + rs.close(); + conn.rollback(); + System.out.println("Verify that after rollback, row count is back to 0 in updateTriggerInsertIntoThisTable"); + dumpRS(stmt.executeQuery("select count(*) from updateTriggerInsertIntoThisTable")); + System.out.println("Verify that after rollback, table0WithTriggers is back to its original contents"); + dumpRS(stmt.executeQuery("select * from table0WithTriggers")); + conn.setAutoCommit(true); + + System.out.println("---Positive Test17 - After deleteRow, resultset is positioned before the next row"); + reloadData(); + stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE"); + rs.next(); + rs.deleteRow(); + System.out.println("getXXX right after deleteRow will fail because resultset is not positioned on a row, instead it is right before the next row"); + try { + System.out.println("column 1 (which is not nullable) after deleteRow is " + rs.getString(1)); + } + catch (SQLException e) { + if (e.getSQLState().equals("24000")) { + System.out.println("expected exception " + e.getMessage()); + } else + dumpSQLExceptions(e); + } + rs.close(); + + System.out.println("---Positive Test18 - Test cancelRowUpdates method as the first updatable ResultSet api on a read-only resultset"); + stmt = conn.createStatement(); + rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable"); + try { + rs.cancelRowUpdates(); + System.out.println("Test failed - should not have reached here because cancelRowUpdates is being called on a read-only resultset"); + } catch (SQLException e) { + if (e.getSQLState().equals("XJ083")) { + System.out.println("expected exception " + e.getMessage()); + } else + dumpSQLExceptions(e); + } + rs.close(); + + System.out.println("---Positive Test19 - Test updateRow method as the first updatable ResultSet api on a read-only resultset"); + stmt = conn.createStatement(); + rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable"); + rs.next(); + try { + rs.updateRow(); + System.out.println("Test failed - should not have reached here because updateRow is being called on a read-only resultset"); + return; + } catch (Throwable e) { + System.out.println(" Got expected exception : " + e.getMessage()); + } + rs.close(); + + System.out.println("---Positive Test20 - Test updateXXX methods as the first updatable ResultSet api on a read-only resultset"); + conn.setAutoCommit(false); + stmt = conn.createStatement(); + for (int updateXXXName = 1; updateXXXName <= allUpdateXXXNames.length; updateXXXName++) { + System.out.println(" Test " + allUpdateXXXNames[updateXXXName-1] + " on a readonly resultset"); + for (int indexOrName = 1; indexOrName <= 2; indexOrName++) { + rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable"); + rs.next(); + rs1 = stmt1.executeQuery("SELECT * FROM AllDataTypesNewValuesData"); + rs1.next(); + if (indexOrName == 1) //test by passing column position + System.out.println(" Using column position as first parameter to " + allUpdateXXXNames[updateXXXName-1]); + else + System.out.println(" Using column name as first parameter to " + allUpdateXXXNames[updateXXXName-1]); + try { + if (updateXXXName == 1) {//update column with updateShort methods + if (indexOrName == 1) //test by passing column position + rs.updateShort(1, rs1.getShort(updateXXXName)); + else //test by passing column name + rs.updateShort(ColumnNames[0], rs1.getShort(updateXXXName)); + } else if (updateXXXName == 2){ //update column with updateInt methods + if (indexOrName == 1) //test by passing column position + rs.updateInt(1, rs1.getInt(updateXXXName)); + else //test by passing column name + rs.updateInt(ColumnNames[0], rs1.getInt(updateXXXName)); + } else if (updateXXXName == 3){ //update column with updateLong methods + if (indexOrName == 1) //test by passing column position + rs.updateLong(1, rs1.getLong(updateXXXName)); + else //test by passing column name + rs.updateLong(ColumnNames[0], rs1.getLong(updateXXXName)); + } else if (updateXXXName == 4){ //update column with updateBigDecimal methods + if (indexOrName == 1) //test by passing column position + rs.updateBigDecimal(1, rs1.getBigDecimal(updateXXXName)); + else //test by passing column name + rs.updateBigDecimal(ColumnNames[0], rs1.getBigDecimal(updateXXXName)); + } else if (updateXXXName == 5){ //update column with updateFloat methods + if (indexOrName == 1) //test by passing column position + rs.updateFloat(1, rs1.getFloat(updateXXXName)); + else //test by passing column name + rs.updateFloat(ColumnNames[0], rs1.getFloat(updateXXXName)); + } else if (updateXXXName == 6){ //update column with updateDouble methods + if (indexOrName == 1) //test by passing column position + rs.updateDouble(1, rs1.getDouble(updateXXXName)); + else //test by passing column name + rs.updateDouble(ColumnNames[0], rs1.getDouble(updateXXXName)); + } else if (updateXXXName == 7){ //update column with updateString methods + if (indexOrName == 1) //test by passing column position + rs.updateString(1, rs1.getString(updat