Start testing delete and update using JDBC2.0 updateable resultset apisNegative Testl - request for scroll insensitive updatable resultset will give a read only scroll insensitive resultsetWARNING (no SQLState): Insensitive updatable result sets are not supported by server; remapping to insensitive read-only cursorrequested TYPE_SCROLL_INSENSITIVE, CONCUR_UPDATABLE but that is not supportedMake sure that we got TYPE_SCROLL_INSENSITIVE? trueMake sure that we got CONCUR_READ_ONLY? trueownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? falseothersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? falsedeletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE)? falseJDBC 2.0 updatable resultset api will fail on this resultset because this is not an updatable resultsetSQL State : nullGot expected exception This method cannot be invoked while the cursor is on the insert row or if the concurrency of this ResultSet object is CONCUR_READ_ONLY.SQL State : nullGot expected exception This method cannot be invoked while the cursor is on the insert row or if the concurrency of this ResultSet object is CONCUR_READ_ONLY.Negative Test2 - request for scroll sensitive updatable resultset will give a read only scroll insensitive resultsetWARNING (no SQLState): Scroll sensitive result sets are not supported by server; remapping to forward-only cursorWARNING (no SQLState): Insensitive updatable result sets are not supported by server; remapping to insensitive read-only cursorrequested TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE but that is not supportedJira issue Derby-154 : When client connects to Network Server using JCC, it incorrectly shows support for scroll sensitive updatable resultsetsMake sure that we got TYPE_SCROLL_INSENSITIVE? trueMake sure that we got CONCUR_READ_ONLY? trueJDBC 2.0 updatable resultset api will fail on this resultset because this is not an updatable resultsetSQL State : nullGot expected exception This method cannot be invoked while the cursor is on the insert row or if the concurrency of this ResultSet object is CONCUR_READ_ONLY.SQL State : nullGot expected exception This method cannot be invoked while the cursor is on the insert row or if the concurrency of this ResultSet object is CONCUR_READ_ONLY.Negative Test3 - request a read only resultset and attempt deleteRow and updateRow on itMake sure that we got CONCUR_READ_ONLY? trueNow attempting to send a deleteRow on a read only resultset.SQL State : nullGot expected exception This method cannot be invoked while the cursor is on the insert row or if the concurrency of this ResultSet object is CONCUR_READ_ONLY.Now attempting to send an updateRow on a read only resultset.SQL State : nullGot expected exception This method cannot be invoked while the cursor is on the insert row or if the concurrency of this ResultSet object is CONCUR_READ_ONLY.Negative Test4 - request a read only resultset and send a sql with FOR UPDATE clause and attempt deleteRow/updateRow on itMake sure that we got CONCUR_READ_ONLY? trueNow attempting to send a deleteRow on a read only resultset with FOR UPDATE clause in the SELECT sql.SQL State : nullGot expected exception This method cannot be invoked while the cursor is on the insert row or if the concurrency of this ResultSet object is CONCUR_READ_ONLY.Now attempting to send a updateRow on a read only resultset with FOR UPDATE clause in the SELECT sql.SQL State : nullGot expected exception This method cannot be invoked while the cursor is on the insert row or if the concurrency of this ResultSet object is CONCUR_READ_ONLY.Negative Test5 - request updatable resultset for sql with no FOR UPDATE clauseMake sure that we got CONCUR_READ_ONLY? trueJira issue Derby-159 : Warnings raised by Derby are not getting passed to the Client in Network Server ModeWill see the warnings in embedded mode onlyNow attempting to send a delete on a sql with no FOR UPDATE clause.SQL State : nullGot expected exception This method cannot be invoked while the cursor is on the insert row or if the concurrency of this ResultSet object is CONCUR_READ_ONLY.Now attempting to send a updateRow on a sql with no FOR UPDATE clause.SQL State : nullGot expected exception This method cannot be invoked while the cursor is on the insert row or if the concurrency of this ResultSet object is CONCUR_READ_ONLY.Negative Test6 - request updatable resultset for sql with FOR READ ONLY clauseMake sure that we got CONCUR_READ_ONLY? trueJira issue Derby-159 : Warnings raised by Derby are not getting passed to the Client in Network Server ModeWill see the warnings in embedded mode onlyNow attempting to send a delete on a sql with FOR READ ONLY clause.SQL State : nullGot expected exception This method cannot be invoked while the cursor is on the insert row or if the concurrency of this ResultSet object is CONCUR_READ_ONLY.Now attempting to send a updateRow on a sql with FOR READ ONLY clause.SQL State : nullGot expected exception This method cannot be invoked while the cursor is on the insert row or if the concurrency of this ResultSet object is CONCUR_READ_ONLY.Negative Test7 - attempt to deleteRow & updateRow on updatable resultset when the resultset is not positioned on a rowMake sure that we got CONCUR_UPDATABLE? trueNow attempt a deleteRow without first doing next on the resultset.SQL State : XCL08Got expected exception Cursor '<xxx-cursor-name-xxx>' is not on a row.Now attempt a updateRow without first doing next on the resultset.updateRow will check if it is on a row or not even though no changes have been made to the row using updateXXXSQL State : nullGot expected exception Invalid operation to update at current cursor positionResultSet is positioned after the last row. attempt to deleteRow at this point should fail!SQL State : nullGot expected exception Invalid operation: result set closedResultSet is positioned after the last row. attempt to updateRow at this point should fail!SQL State : nullGot expected exception Invalid operation: result set closedNegative Test8 - attempt deleteRow & updateRow on updatable resultset after closing the resultsetMake sure that we got CONCUR_UPDATABLE? trueSQL State : nullGot expected exception Invalid operation: result set closedSQL State : nullGot expected exception Invalid operation: result set closedNegative Test9 - try updatable resultset on system tableSQL State : 42Y90Got expected exception FOR UPDATE is not permitted in this type of statement.  Negative Test10 - try updatable resultset on a viewSQL State : 42Y90Got expected exception FOR UPDATE is not permitted in this type of statement.  Negative Test11 - attempt to open updatable resultset when there is join in the select query should failSQL State : 42Y90Got expected exception FOR UPDATE is not permitted in this type of statement.  Negative Test12 - With autocommit on, attempt to drop a table when there is an open updatable resultset on itOpened an updatable resultset. Now trying to drop that table through another StatementSQL State : X0X95Got expected exception Operation 'DROP TABLE' cannot be performed on object 'T1' because there is an open ResultSet dependent on that object.Since autocommit is on, the drop table exception resulted in a runtime rollback causing updatable resultset object to closeSQL State : 24000Got expected exception Invalid cursor state - no current row.SQL State : 24000Got expected exception Invalid cursor state - no current row.Negative Test13 - foreign key constraint failure will cause deleteRow to failSQL State : 23503Got expected exception DELETE on table 'TABLEWITHPRIMARYKEY' caused a violation of foreign key constraint 'FK' for key (1,1).  The statement has been rolled back.Since autocommit is on, the constraint exception resulted in a runtime rollback causing updatable resultset object to closeJira entry Derby-160 : for Network Server because next should have failedFAIL!!! next should have failed because foreign key constraint failure resulted in a runtime rollbackNegative Test14 - foreign key constraint failure will cause updateRow to failSQL State : 23503Got expected exception UPDATE on table 'TABLEWITHPRIMARYKEY' caused a violation of foreign key constraint 'FK' for key (1,1).  The statement has been rolled back.Since autocommit is on, the constraint exception resulted in a runtime rollback causing updatable resultset object to closeJira entry Derby-160 : for Network Server because next should have failedFAIL!!! next should have failed because foreign key constraint failure resulted in a runtime rollbackNegative Test15 - Can't call updateXXX methods on columns that do not correspond to a column in the tableSQL State : nullGot expected exception Column not updatableNegative Test16 - Call updateXXX method on out of the range columnThere are only 2 columns in the select list and we are trying to send updateXXX on column position 3SQL State : nullGot expected exception Invalid argument: parameter index 3 is out of range.Positive Test1a - request updatable resultset for forward only type resultsetrequested TYPE_FORWARD_ONLY, CONCUR_UPDATABLEgot TYPE_FORWARD_ONLY? truegot CONCUR_UPDATABLE? trueJDBC 2.0 updatable resultset apis on this ResultSet object will pass because this is an updatable resultsetcolumn 1 on this row before deleteRow is 1column 2 on this row before deleteRow is aa                  Since after deleteRow(), in embedded mode and Network Server mode using Derby Net Client, ResultSet is positioned before the next row, getXXX will failSQL State : nullGot expected exception Invalid operation to read at current cursor position.calling deleteRow again w/o first positioning the ResultSet on the next row will failSQL State : 24000Got expected exception Invalid cursor state - no current row.Position the ResultSet with next()Should be able to deletRow() on the current row nowPositive Test1b - request updatable resultset for forward only type resultsetcolumn 1 on this row before updateInt is 1column 1 on this row after updateInt is 234column 2 on this row before updateString is aa                  now updateRow on the rowSince after updateRow(), in embedded mode and Network Server mode using Derby Net Client, ResultSet is positioned before the next row, getXXX will failSQL State : nullGot expected exception Invalid operation to read at current cursor position.calling updateRow again w/o first positioning the ResultSet on the next row will failSQL State : nullGot expected exception Invalid operation to update at current cursor positionPosition the ResultSet with next()Should be able to updateRow() on the current row nowPositive Test2 - even if no columns from table specified in the column list, we should be able to get updatable resultsetWill work in embedded mode because target table is not derived from the columns in the select listWill not work in network server mode because it derives the target table from the columns in the select listtotal number of rows in T1 	 1	 -	{3}column 1 on this row is 1SQL State : 42X01Got expected exception Syntax error: Encountered "WHERE" at line 1, column 14.total number of rows in T1 after one deleteRow is 	 1	 -	{3}Positive Test3a - use prepared statement with concur updatable status to test deleteRowrequested TYPE_FORWARD_ONLY, CONCUR_UPDATABLEgot TYPE_FORWARD_ONLY? truegot CONCUR_UPDATABLE? truecolumn 1 on this row is 1Since after deleteRow(), ResultSet is positioned before the next row, getXXX will failSQL State : nullGot expected exception Invalid operation to read at current cursor position.calling deleteRow again w/o first positioning the ResultSet on the next row will failSQL State : 24000Got expected exception Invalid cursor state - no current row.Position the ResultSet with next()Should be able to deletRow() on the current row nowPositive Test3b - use prepared statement with concur updatable status to test updateXXXrequested TYPE_FORWARD_ONLY, CONCUR_UPDATABLEgot TYPE_FORWARD_ONLY? truegot CONCUR_UPDATABLE? truecolumn 1 on this row is 1column 1 on this row after updateInt is 5Since after updateRow(), ResultSet is positioned before the next row, getXXX will failSQL State : nullGot expected exception Invalid operation to read at current cursor position.calling updateRow/updateXXX again w/o first positioning the ResultSet on the next row will failSQL State : nullGot expected exception Invalid operation to update at current cursor positionSQL State : nullGot expected exception Invalid operation to update at current cursor positionSQL State : nullGot expected exception Invalid operation at current cursor position.Position the ResultSet with next()Should be able to cancelRowUpdates() on the current row nowPositive Test4 - use callable statement with concur updatable statusrequested TYPE_FORWARD_ONLY, CONCUR_UPDATABLEgot TYPE_FORWARD_ONLY? truegot CONCUR_UPDATABLE? truecolumn 1 on this row is 1Since after deleteRow(), ResultSet is positioned before the next row, getXXX will failSQL State : nullGot expected exception Invalid operation to read at current cursor position.calling deleteRow again w/o first positioning the ResultSet on the next row will failSQL State : 24000Got expected exception Invalid cursor state - no current row.Position the ResultSet with next()Should be able to deletRow() on the current row nowPositive Test5 - donot have to select primary key to get an updatable resultsetcolumn 1 on this row is 1now try to delete row when primary key is not selected for that rowPositive Test6a - For Forward Only resultsets, DatabaseMetaData will return false for ownDeletesAreVisible and deletesAreDetectedThis 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 holeownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? falseothersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? truedeletesAreDetected(ResultSet.TYPE_FORWARD_ONLY)? falseThe JDBC program should look at rowDeleted only if deletesAreDetected returns trueSince Derby returns false for detlesAreDetected for FORWARD_ONLY updatable resultset,the program should not rely on rs.rowDeleted() for FORWARD_ONLY updatable resultsetsHave this call to rs.rowDeleted() just to make sure the method does always return false? falseHave this call to rs.rowDeleted() just to make sure the method does always return false? falsePositive Test6b - For Forward Only resultsets, DatabaseMetaData will return false for ownUpdatesAreVisible and updatesAreDetectedThis is because, after updateRow, we position the ResultSet before the next rowownUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? falseothersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? trueupdatesAreDetected(ResultSet.TYPE_FORWARD_ONLY)? falseThe JDBC program should look at rowUpdated only if updatesAreDetected returns trueSince Derby returns false for updatesAreDetected for FORWARD_ONLY updatable resultset,the program should not rely on rs.rowUpdated() for FORWARD_ONLY updatable resultsetsHave this call to rs.rowUpdated() just to make sure the method does always return false? falseHave this call to rs.rowUpdated() just to make sure the method does always return false? falsePositive Test7a - delete using updatable resultset api from a temporary tablefollowing rows in temp table before deleteRow	 C21,C22	 --- ---	{21,1}	{22,1}As expected, no rows in temp table after deleteRow	 C21,C22	 --- ---Positive Test7b - update using updatable resultset api from a temporary tablefollowing rows in temp table before deleteRow	 C31,C32	 --- ---	{21,1}