updatableresultset.out

derby database source code.good for you.

Start testing delete and update using JDBC2.0 updateable resultset apis
Negative Testl - request for scroll insensitive updatable resultset will give a read only scroll insensitive resultset
WARNING 01J03: Scroll sensitive and scroll insensitive updatable ResultSets are not currently implemented.
requested TYPE_SCROLL_INSENSITIVE, CONCUR_UPDATABLE but that is not supported
Make sure that we got TYPE_SCROLL_INSENSITIVE? true
Make sure that we got CONCUR_READ_ONLY? true
ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? false
othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? false
deletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE)? false
JDBC 2.0 updatable resultset api will fail on this resultset because this is not an updatable resultset
SQL State : XJ083
Got expected exception 'deleteRow' not allowed because the ResultSet is not an updatable ResultSet. 
SQL State : XJ083
Got 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 resultset
WARNING 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 supported
Jira issue Derby-154 : When client connects to Network Server using JCC, it incorrectly shows support for scroll sensitive updatable resultsets
Make sure that we got TYPE_SCROLL_INSENSITIVE? true
Make sure that we got CONCUR_READ_ONLY? true
JDBC 2.0 updatable resultset api will fail on this resultset because this is not an updatable resultset
SQL State : XJ083
Got expected exception 'deleteRow' not allowed because the ResultSet is not an updatable ResultSet. 
SQL State : XJ083
Got 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 it
Make sure that we got CONCUR_READ_ONLY? true
Now attempting to send a deleteRow on a read only resultset.
SQL State : XJ083
Got 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 : XJ083
Got 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 it
Make sure that we got CONCUR_READ_ONLY? true
Now attempting to send a deleteRow on a read only resultset with FOR UPDATE clause in the SELECT sql.
SQL State : XJ083
Got 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 : XJ083
Got expected exception 'updateRow' not allowed because the ResultSet is not an updatable ResultSet. 
Negative Test5 - request updatable resultset for sql with no FOR UPDATE clause
Make sure that we got CONCUR_READ_ONLY? true
Jira issue Derby-159 : Warnings raised by Derby are not getting passed to the Client in Network Server Mode
Will see the warnings in embedded mode only
WARNING 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 : XJ083
Got 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 : XJ083
Got expected exception 'updateRow' not allowed because the ResultSet is not an updatable ResultSet. 
Negative Test6 - request updatable resultset for sql with FOR READ ONLY clause
Make sure that we got CONCUR_READ_ONLY? true
Jira issue Derby-159 : Warnings raised by Derby are not getting passed to the Client in Network Server Mode
Will see the warnings in embedded mode only
WARNING 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 : XJ083
Got 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 : XJ083
Got 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 row
Make sure that we got CONCUR_UPDATABLE? true
Now attempt a deleteRow without first doing next on the resultset.
SQL State : 24000
Got 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 updateXXX
SQL State : 24000
Got 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 : 24000
Got 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 : 24000
Got expected exception Invalid cursor state - no current row.
Negative Test8 - attempt deleteRow & updateRow on updatable resultset after closing the resultset
Make sure that we got CONCUR_UPDATABLE? true
SQL State : XCL16
Got expected exception ResultSet not open. Operation 'deleteRow' not permitted. Verify that autocommit is OFF.
SQL State : XCL16
Got expected exception ResultSet not open. Operation 'updateRow' not permitted. Verify that autocommit is OFF.
Negative Test9 - try updatable resultset on system table
SQL State : 42Y90
Got expected exception FOR UPDATE is not permitted in this type of statement.  
Negative Test10 - try updatable resultset on a view
SQL State : 42Y90
Got 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 fail
SQL State : 42Y90
Got 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 it
Opened an updatable resultset. Now trying to drop that table through another Statement
SQL State : X0X95
Got 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 close
SQL State : XCL16
Got expected exception ResultSet not open. Operation 'updateRow' not permitted. Verify that autocommit is OFF.
SQL State : XCL16
Got expected exception ResultSet not open. Operation 'deleteRow' not permitted. Verify that autocommit is OFF.
Negative Test13 - foreign key constraint failure will cause deleteRow to fail
SQL State : 23503
Got 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 close
SQL State : XCL16
Got expected exception ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.
Negative Test14 - foreign key constraint failure will cause updateRow to fail
SQL State : 23503
Got 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 close
SQL State : XCL16
Got 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 table
SQL State : XJ084
Got expected exception Column does not correspond to a column in the base table. Cant issue {0} on this column.
Negative Test16 - Call updateXXX method on out of the range column
There are only 2 columns in the select list and we are trying to send updateXXX on column position 3
SQL State : XCL14
Got 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 resultset
requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE
got TYPE_FORWARD_ONLY? true
got CONCUR_UPDATABLE? true
JDBC 2.0 updatable resultset apis on this ResultSet object will pass because this is an updatable resultset
column 1 on this row before deleteRow is 1
column 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 fail
SQL State : 24000
Got expected exception Invalid cursor state - no current row.
calling deleteRow again w/o first positioning the ResultSet on the next row will fail
SQL State : 24000
Got expected exception Invalid cursor state - no current row.
Position the ResultSet with next()
Should be able to deletRow() on the current row now
Positive Test1b - request updatable resultset for forward only type resultset
column 1 on this row before updateInt is 1
column 1 on this row after updateInt is 234
column 2 on this row before updateString is aa                  
now updateRow on the row
Since after updateRow(), in embedded mode and Network Server mode using Derby Net Client, ResultSet is positioned before the next row, getXXX will fail
SQL State : 24000
Got expected exception Invalid cursor state - no current row.
calling updateRow again w/o first positioning the ResultSet on the next row will fail
SQL State : 24000
Got expected exception Invalid cursor state - no current row.
Position the ResultSet with next()
Should be able to updateRow() on the current row now
Positive Test2 - even if no columns from table specified in the column list, we should be able to get updatable resultset
Will work in embedded mode because target table is not derived from the columns in the select list
Will not work in network server mode because it derives the target table from the columns in the select list
total number of rows in T1 
	 1
	 -
	{3}
column 1 on this row is 1
PASS!!! passed in embedded mode
total number of rows in T1 after one deleteRow is 
	 1
	 -
	{2}
Positive Test3a - use prepared statement with concur updatable status to test deleteRow
requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE
got TYPE_FORWARD_ONLY? true
got CONCUR_UPDATABLE? true
column 1 on this row is 1
Since after deleteRow(), ResultSet is positioned before the next row, getXXX will fail
SQL State : 24000
Got expected exception Invalid cursor state - no current row.
calling deleteRow again w/o first positioning the ResultSet on the next row will fail
SQL State : 24000
Got expected exception Invalid cursor state - no current row.
Position the ResultSet with next()
Should be able to deletRow() on the current row now
Positive Test3b - use prepared statement with concur updatable status to test updateXXX
requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE
got TYPE_FORWARD_ONLY? true
got CONCUR_UPDATABLE? true
column 1 on this row is 1
column 1 on this row after updateInt is 5
Since after updateRow(), ResultSet is positioned before the next row, getXXX will fail
SQL State : 24000
Got expected exception Invalid cursor state - no current row.
calling updateRow/updateXXX again w/o first positioning the ResultSet on the next row will fail
SQL State : 24000
Got expected exception Invalid cursor state - no current row.
SQL State : 24000
Got expected exception Invalid cursor state - no current row.
SQL State : 24000
Got expected exception Invalid cursor state - no current row.
Position the ResultSet with next()
Should be able to cancelRowUpdates() on the current row now
Positive Test4 - use callable statement with concur updatable status
requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE
got TYPE_FORWARD_ONLY? true
got CONCUR_UPDATABLE? true
column 1 on this row is 1
Since after deleteRow(), ResultSet is positioned before the next row, getXXX will fail
SQL State : 24000
Got expected exception Invalid cursor state - no current row.
calling deleteRow again w/o first positioning the ResultSet on the next row will fail
SQL State : 24000
Got expected exception Invalid cursor state - no current row.
Position the ResultSet with next()
Should be able to deletRow() on the current row now
Positive Test5 - donot have to select primary key to get an updatable resultset
column 1 on this row is 1