updatableresultset.out

derby database source code.good for you.

now try to delete row when primary key is not selected for that row
Positive Test6a - For Forward Only resultsets, DatabaseMetaData will return false for ownDeletesAreVisible and deletesAreDetected
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
ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? false
othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? true
deletesAreDetected(ResultSet.TYPE_FORWARD_ONLY)? false
The JDBC program should look at rowDeleted only if deletesAreDetected returns true
Since Derby returns false for detlesAreDetected for FORWARD_ONLY updatable resultset,the program should not rely on rs.rowDeleted() for FORWARD_ONLY updatable resultsets
Have this call to rs.rowDeleted() just to make sure the method does always return false? false
Have this call to rs.rowDeleted() just to make sure the method does always return false? false
Positive Test6b - For Forward Only resultsets, DatabaseMetaData will return false for ownUpdatesAreVisible and updatesAreDetected
This is because, after updateRow, we position the ResultSet before the next row
ownUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? false
othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? true
updatesAreDetected(ResultSet.TYPE_FORWARD_ONLY)? false
The JDBC program should look at rowUpdated only if updatesAreDetected returns true
Since Derby returns false for updatesAreDetected for FORWARD_ONLY updatable resultset,the program should not rely on rs.rowUpdated() for FORWARD_ONLY updatable resultsets
Have this call to rs.rowUpdated() just to make sure the method does always return false? false
Have this call to rs.rowUpdated() just to make sure the method does always return false? false
Positive Test7a - delete using updatable resultset api from a temporary table
following 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 table
following rows in temp table before deleteRow
	 C31,C32
	 --- ---
	{21,1}
	{22,1}
As expected, updated rows in temp table after updateRow
	 C31,C32
	 --- ---
	{123,1}
	{123,1}
Positive Test8a - change the name of the statement when the resultset is open and see if deleteRow still works
This test works in embedded mode since Derby can handle the change in the name of the statement with an open resultset
But it fails under Network Server mode because JCC and Derby Net Client do not allow statement name change when there an open resultset against it
change the cursor name(case sensitive name) with setCursorName and then try to deleteRow
change the cursor name one more time with setCursorName and then try to deleteRow
PASS!!! passed in embedded mode
Positive Test8b - change the name of the statement when the resultset is open and see if updateRow still works
This test works in embedded mode since Derby can handle the change in the name of the statement with an open resultset
But it fails under Network Server mode because JCC and Derby Net Client do not allow statement name change when there an open resultset against it
change the cursor name one more time with setCursorName and then try to updateRow
change the cursor name(case sensitive name) with setCursorName and then try to updateRow
PASS!!! passed in embedded mode
Positive Test9a - using correlation name for the table in the select sql works in embedded mode and Network Server using Derby Net Client driver
Correlation name for table does not work in Network Server mode (using JCC) because the drivers construct the delete sql with the correlation name rather than the base table name
column 1 on this row is 1
now try to deleteRow
PASS!!! passed in embedded mode
Positive Test9b - using correlation name for updatable columns is not allowed.
Table t1 has following rows
	 C1,C2
	 -- --
	{1,aa                  }
	{2,bb                  }
	{3,cc                  }
attempt to get an updatable resultset using correlation name for an updatable column
The sql is SELECT c1 as col1, c2 as col2 FROM t1 abcde FOR UPDATE of c1
SQL State : 42X42
Got expected exception Correlation name not allowed for column 'C1' because it is part of the FOR UPDATE list.
attempt to get an updatable resultset using correlation name for an readonly column. It should work
The sql is SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1
Table t1 after updateRow has following rows
	 C1,C2
	 -- --
	{11,aa                  }
	{2,bb                  }
	{3,cc                  }
Positive Test9c - try to updateXXX on a readonly column. Should get error
SQL State : 42X31
Got expected exception Column 'C2' is not in the FOR UPDATE list of cursor 'SQLCUR15'.
attempt to get an updatable resultset using correlation name for an readonly column. It should work
The sql is SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1
Table t1 after updateRow has following rows
	 C1,C2
	 -- --
	{11,aa                  }
	{2,bb                  }
	{3,cc                  }
Positive Test9d - try to updateXXX on a readonly column with correlation name. Should get error
SQL State : 42X31
Got expected exception Column 'COL2' is not in the FOR UPDATE list of cursor 'SQLCUR17'.
Table t1 has following rows
	 C1,C2
	 -- --
	{1,aa                  }
	{2,bb                  }
	{3,cc                  }
Positive Test10 - 2 updatable resultsets going against the same table, will they conflict?
delete using first resultset
attempt to send deleteRow on the same row through a different resultset should throw an exception
SQL State : XCL08
Got expected exception Cursor 'SQLCUR19' is not on a row.
Move to next row in the 2nd resultset and then delete using the second resultset
Positive Test11 - setting the fetch size to > 1 will be ignored by updatable resultset. Same as updatable cursors
Notice the Fetch Size in run time statistics output.
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	SELECT * FROM t1 FOR UPDATE of c1
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Table Scan ResultSet for T1 at read committed isolation level using exclusive row locking chosen by the optimizer
Number of opens = 1
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
scan information: 
	Bit set of columns fetched=All
	Number of columns fetched=2
	Number of pages visited=0
	Number of rows qualified=0
	Number of rows visited=0
	Scan type=heap
	start position: 
null	stop position: 
null	qualifiers:
None
statement's fetch size is 200
Positive Test12a - make sure delete trigger gets fired when deleteRow is issued
Verify that before delete trigger got fired, row count is 0 in deleteTriggerInsertIntoThisTable
	 1
	 -
	{0}
column 1 on this row is 1
now try to delete row and make sure that trigger got fired
Verify that delete trigger got fired by verifying the row count to be 1 in deleteTriggerInsertIntoThisTable
	 1
	 -
	{1}
Positive Test12b - make sure update trigger gets fired when updateRow is issued
Verify that before update trigger got fired, row count is 0 in updateTriggerInsertIntoThisTable
	 1
	 -
	{0}
column 1 on this row is 1
now try to update row and make sure that trigger got fired
Verify that update trigger got fired by verifying the row count to be 1 in updateTriggerInsertIntoThisTable
	 1
	 -
	{1}
Positive Test13a - Another test case for delete trigger
column 1 on this row is 1
this delete row will fire the delete trigger which will delete all the rows from the table and from the resultset
SQL State : 24000
Got expected exception Invalid cursor state - no current row.
Verify that delete trigger got fired by verifying the row count to be 0 in table1WithTriggers
	 1
	 -
	{0}
Positive Test13b - Another test case for update trigger
Look at the current contents of table2WithTriggers
	 C1,C2
	 -- --
	{1,1}
	{2,2}
	{3,3}
	{4,4}
column 1 on this row is 2
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
SQL State : 24000
Got expected exception Invalid cursor state - no current row.
Verify that update trigger got fired by verifying that all column c1s have value 1 in table2WithTriggers
	 C1,C2
	 -- --
	{1,1}
	{1,2}
	{1,3}
	{1,4}
Positive Test14a - make sure self referential delete cascade works when deleteRow is issued
	 C1,C2
	 -- --
	{e1,null}
	{e2,e1}
	{e3,e2}
	{e4,e3}
column 1 on this row is e1
this delete row will cause the delete cascade constraint to delete all the rows from the table and from the resultset
SQL State : 24000
Got expected exception Invalid cursor state - no current row.
Verify that delete trigger got fired by verifying the row count to be 0 in selfReferencingT1
	 1
	 -
	{0}
Positive Test14b - make sure self referential update restrict works when updateRow is issued
	 C1,C2
	 -- --