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 01J03: Scroll sensitive and scroll insensitive updatable ResultSets are not currently implemented.requested 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 : XJ083Got expected exception 'deleteRow' not allowed because the ResultSet is not an updatable ResultSet. SQL State : XJ083Got expected exception 'updateRow' not allowed because the ResultSet is not an updatable ResultSet. Negative Test2 - request for scroll sensitive updatable resultset will give a read only scroll insensitive resultsetWARNING 01J02: Scroll sensitive cursors are not currently implemented.WARNING 01J03: Scroll sensitive and scroll insensitive updatable ResultSets are not currently implemented.requested 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 : XJ083Got expected exception 'deleteRow' not allowed because the ResultSet is not an updatable ResultSet. SQL State : XJ083Got expected exception 'updateRow' not allowed because the ResultSet is not an updatable ResultSet. 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 : XJ083Got expected exception 'deleteRow' not allowed because the ResultSet is not an updatable ResultSet. Now attempting to send an updateRow on a read only resultset.SQL State : XJ083Got expected exception 'updateRow' not allowed because the ResultSet is not an updatable ResultSet. 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 : XJ083Got expected exception 'deleteRow' not allowed because the ResultSet is not an updatable ResultSet. Now attempting to send a updateRow on a read only resultset with FOR UPDATE clause in the SELECT sql.SQL State : XJ083Got expected exception 'updateRow' not allowed because the ResultSet is not an updatable ResultSet. 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 onlyWARNING 01J06: ResultSet not updatable. Query does not qualify to generate an updatable ResultSet.Now attempting to send a delete on a sql with no FOR UPDATE clause.SQL State : XJ083Got expected exception 'deleteRow' not allowed because the ResultSet is not an updatable ResultSet. Now attempting to send a updateRow on a sql with no FOR UPDATE clause.SQL State : XJ083Got expected exception 'updateRow' not allowed because the ResultSet is not an updatable ResultSet. 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 onlyWARNING 01J06: ResultSet not updatable. Query does not qualify to generate an updatable ResultSet.Now attempting to send a delete on a sql with FOR READ ONLY clause.SQL State : XJ083Got expected exception 'deleteRow' not allowed because the ResultSet is not an updatable ResultSet. Now attempting to send a updateRow on a sql with FOR READ ONLY clause.SQL State : XJ083Got expected exception 'updateRow' not allowed because the ResultSet is not an updatable ResultSet. 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 : 24000Got expected exception Invalid cursor state - no current 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 : 24000Got expected exception Invalid cursor state - no current row.ResultSet is positioned after the last row. attempt to deleteRow at this point should fail!SQL State : 24000Got expected exception Invalid cursor state - no current row.ResultSet is positioned after the last row. attempt to updateRow at this point should fail!SQL State : 24000Got expected exception Invalid cursor state - no current row.Negative Test8 - attempt deleteRow & updateRow on updatable resultset after closing the resultsetMake sure that we got CONCUR_UPDATABLE? trueSQL State : XCL16Got expected exception ResultSet not open. Operation 'deleteRow' not permitted. Verify that autocommit is OFF.SQL State : XCL16Got expected exception ResultSet not open. Operation 'updateRow' not permitted. Verify that autocommit is OFF.Negative 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 : XCL16Got expected exception ResultSet not open. Operation 'updateRow' not permitted. Verify that autocommit is OFF.SQL State : XCL16Got expected exception ResultSet not open. Operation 'deleteRow' not permitted. Verify that autocommit is OFF.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 closeSQL State : XCL16Got expected exception ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.Negative 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 closeSQL State : XCL16Got expected exception ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.Negative Test15 - Can't call updateXXX methods on columns that do not correspond to a column in the tableSQL State : XJ084Got expected exception Column does not correspond to a column in the base table. Cannot issue 'updateInt' on this column.Negative 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 : XCL14Got expected exception The column position '3' is out of range.  The number of columns for this ResultSet is '2'.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 : 24000Got expected exception Invalid cursor state - no current row.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 : 24000Got expected exception Invalid cursor state - no current row.calling updateRow 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 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 1PASS!!! passed in embedded modetotal number of rows in T1 after one deleteRow is 	 1	 -	{2}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 : 24000Got expected exception Invalid cursor state - no current row.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 : 24000Got expected exception Invalid cursor state - no current row.calling updateRow/updateXXX again w/o first positioning the ResultSet on the next row will failSQL State : 24000Got expected exception Invalid cursor state - no current row.SQL State : 24000Got expected exception Invalid cursor state - no current row.SQL State : 24000Got expected exception Invalid cursor state - no current row.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 : 24000Got expected exception Invalid cursor state - no current row.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 resultset