> Suresh Thalamati wrote:
>
>> Hi Mamta,
>>
>> You may want to test following two scenarions if you have not done
>> already:
>>
>> 1) delete from result set that involves table with referential
>> actions (on delete cascade ..etc). Concerned about
>> How the result set will look after a delete on a table that has
>> self refential delete cascade, especially if the delete happens to
>> affect the
>> rows that are yet to be positioned.
>>
>> 2) rollback of a transaction that has deletes using update
>> resultset. This will help in making sure all contexts are set
>> properly.
>>
>> -suresht
>
Hi,
Here is a small patch with couple more tests for rollback and self referential
constraint. Hope this covers the cases you mentioned.
thanks,
Mamta
Index:
java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java
===================================================================
---
java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java
(revision 125461)
+++
java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java
(working copy)
@@ -37,7 +37,7 @@
/**
This tests JDBC 2.0 updateable resutlset - deleteRow api
*/
-public class updatableResultSet {
+public class updatableResultSet {
private static Connection conn;
private static DatabaseMetaData dbmt;
@@ -593,6 +593,7 @@
stmt.executeUpdate("call
SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
System.out.println("---Positive Test12 - 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"));
@@ -607,7 +608,55 @@
//have to close the resultset because by default,
resultsets are held open over commit
rs.close();
- System.out.println("---Positive Test13 - With
autocommit off, attempt to drop a table when there is an open updatable
resultset on it");
+ System.out.println("---Positive Test13 - Another test
case for delete trigger");
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("SELECT * FROM
anotherTableWithDeleteTriggers 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 anotherTableWithDeleteTriggers");
+ dumpRS(stmt.executeQuery("select count(*) from
anotherTableWithDeleteTriggers"));
+ //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");
+ 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 anotherTableWithDeleteTriggers");
+ 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 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);
@@ -630,9 +679,36 @@
rs.close();
conn.setAutoCommit(true);
- System.out.println("---Positive Test14 - After
deleteRow, resultset is positioned before the next row");
+ System.out.println("---Positive Test16 - 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 tableWithDeleteTriggers");
+ dumpRS(stmt.executeQuery("select count(*) from
tableWithDeleteTriggers"));
+ rs = stmt.executeQuery("SELECT * FROM
tableWithDeleteTriggers 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 tableWithDeleteTriggers");
+ dumpRS(stmt.executeQuery("select count(*) from
tableWithDeleteTriggers"));
+ //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 tableWithDeleteTriggers");
+ dumpRS(stmt.executeQuery("select count(*) from
tableWithDeleteTriggers"));
+ 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();
@@ -725,12 +801,12 @@
static void reloadData() throws SQLException {
Statement stmt = conn.createStatement();
stmt.executeUpdate("delete from t1");
- stmt.executeUpdate("insert into t1 values (1,'aa')");
- stmt.executeUpdate("insert into t1 values (2,'bb')");
- stmt.executeUpdate("insert into t1 values (3,'cc')");
+ stmt.executeUpdate("insert into t1 values (1,'aa'), (2,'bb'),
(3,'cc')");
stmt.executeUpdate("delete from t3");
- stmt.executeUpdate("insert into t3 values (1,1)");
- stmt.executeUpdate("insert into t3 values (2,2)");
+ stmt.executeUpdate("insert into t3 values (1,1), (2,2)");
+ stmt.executeUpdate("delete from tableWithDeleteTriggers");
+ stmt.executeUpdate("insert into tableWithDeleteTriggers values
(1, 1), (2, 2), (3, 3), (4, 4)");
+ stmt.executeUpdate("delete from
deleteTriggerInsertIntoThisTable");
}
static void setup(boolean first) throws SQLException {
@@ -746,6 +822,7 @@
stmt.executeUpdate("create trigger tr1 after delete on
tableWithDeleteTriggers for each statement mode db2sql insert into
deleteTriggerInsertIntoThisTable values (1)");
stmt.executeUpdate("create table anotherTableWithDeleteTriggers
(c1 int, c2 bigint)");
stmt.executeUpdate("create trigger tr2 after delete on
anotherTableWithDeleteTriggers for each statement mode db2sql delete from
anotherTableWithDeleteTriggers");
+ stmt.executeUpdate("create table selfReferencingT1 (c1 char(2)
not null, c2 char(2), constraint selfReferencingT1 primary key(c1), constraint
manages foreign key(c2) references selfReferencingT1(c1) on delete cascade)");
stmt.executeUpdate("insert into t1 values (1,'aa')");
stmt.executeUpdate("insert into t1 values (2,'bb')");
@@ -757,6 +834,7 @@
stmt.executeUpdate("insert into tableWithConstraint values (1,
1), (2, 2), (3, 3), (4, 4)");
stmt.executeUpdate("insert into tableWithDeleteTriggers values
(1, 1), (2, 2), (3, 3), (4, 4)");
stmt.executeUpdate("insert into anotherTableWithDeleteTriggers
values (1, 1), (2, 2), (3, 3), (4, 4)");
+ stmt.executeUpdate("insert into selfReferencingT1 values ('e1',
null), ('e2', 'e1'), ('e3', 'e2'), ('e4', 'e3')");
stmt.close();
}
@@ -768,6 +846,10 @@
stmt.executeUpdate("drop table t3");
stmt.executeUpdate("drop table tableWithConstraint");
stmt.executeUpdate("drop table tableWithPrimaryKey");
+ stmt.executeUpdate("drop table
deleteTriggerInsertIntoThisTable");
+ stmt.executeUpdate("drop table tableWithDeleteTriggers");
+ stmt.executeUpdate("drop table anotherTableWithDeleteTriggers");
+ stmt.executeUpdate("drop table selfReferencingT1");
conn.commit();
stmt.close();
}
Index:
java/testing/org/apache/derbyTesting/functionTests/master/updatableResultSet.out
===================================================================
---
java/testing/org/apache/derbyTesting/functionTests/master/updatableResultSet.out
(revision 125461)
+++
java/testing/org/apache/derbyTesting/functionTests/master/updatableResultSet.out
(working copy)
@@ -1,222 +1,265 @@
-Start testing delete using JDBC2.0 updateable resultset apis
----Negative Testl - request for scroll insensitive updatable resultset will
give a read only scroll insensitive resultset
-warnings on connection = SQL Warning: 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
-Got expected exception 'deleteRow' 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
-requested TYPE_SCROLL_SENSITIVE, 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
-JDBC 2.0 updatable resultset api will fail on this resultset because this is
not an updatable resultset
-Got expected exception 'deleteRow' not allowed because the ResultSet is not an
updatable ResultSet.
----Negative Test3 - request a read only resultset and attempt deleteRow on it
-Make sure that we got CONCUR_READ_ONLY? true
-Now attempting to send a deleteRow on a read only resultset.
-Got expected exception 'deleteRow' 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 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.
-Got expected exception 'deleteRow' 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
-Expected warnings on resultset = java.sql.SQLWarning: 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.
-Got expected exception 'deleteRow' 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
-Expected warnings on resultset = java.sql.SQLWarning: 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.
-Got expected exception 'deleteRow' not allowed because the ResultSet is not an
updatable ResultSet.
----Negative Test7 - attempt to deleteRow 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.
-Got expected exception Invalid cursor state - no current row.
-ResultSet is positioned after the last row. attempt to deleteRow at this point
should fail!
-Got expected exception Invalid cursor state - no current row.
----Negative Test8 - attempt deleteRow on updatable resultset after closing the
resultset
-Make sure that we got CONCUR_UPDATABLE? true
-Got expected exception ResultSet not open, operation 'deleteRow' not
permitted. Verify that autocommit is OFF.
----Negative Test9 - try updatable resultset on system table
-expected exception FOR UPDATE is not permitted on this type of statement.
----Negative Test10 - try updatable resultset on a view
-expected exception FOR UPDATE is not permitted on this type of statement.
----Negative Test11 - attempt to open updatable resultset when there is join in
the select query should fail
-expected exception FOR UPDATE is not permitted on 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
-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
-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
-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
-expected exception ResultSet not open, operation 'next' not permitted. Verify
that autocommit is OFF.
----Positive Test1 - 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(), ResultSet is positioned before the next row, getXXX
will fail
-Got expected exception Invalid cursor state - no current row.
-calling deleteRow again w/o first positioning the ResultSet on the next row
will fail
-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 Test2 - even if no columns from table specified in the column
list, we should be able to get updatable resultset
-total number of rows in T1
- 1
- -
- {3}
-column 1 on this row is 1
-total number of rows in T1 after one deleteRow is
- 1
- -
- {2}
----Positive Test3 - use prepared 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
-Got expected exception Invalid cursor state - no current row.
-calling deleteRow again w/o first positioning the ResultSet on the next row
will fail
-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 Test4 - use callable statement with concur updatable status
-requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE
-got TYPE_FORWARD_ONLY? true
-got CONCUR_UPDATABLE? true
-row not deleted yet. Confirm with rs.rowDeleted()? false
-column 1 on this row is 1
-Since after deleteRow(), ResultSet is positioned before the next row, getXXX
will fail
-Got expected exception Invalid cursor state - no current row.
-calling deleteRow again w/o first positioning the ResultSet on the next row
will fail
-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
-now try to delete row when primary key is not selected for that row
----Positive Test6 - 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 Test7 - 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 Test8 - change the name of the resultset and see if deleteRow
still works
-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
----Positive Test9 - using correlation name for the table in the select sql is
not a problem
-column 1 on this row is 1
-now try to deleteRow
----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
-Got expected exception Cursor 'SQLCUR10' 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 1, 2 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:
-Project-Restrict ResultSet (3):
-Number of opens = 1
-Rows seen = 0
-Rows filtered = 0
-restriction = false
-projection = true
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- restriction time (milliseconds) = 0
- projection time (milliseconds) = 0
-Source result set:
- Project-Restrict ResultSet (2):
- Number of opens = 1
- Rows seen = 0
- Rows filtered = 0
- restriction = false
- projection = true
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- restriction time (milliseconds) = 0
- projection time (milliseconds) = 0
- Source result set:
- 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 Test12 - 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 Test13 - With autocommit off, 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
-expected exception Operation 'DROP TABLE' cannot be performed on object 'T1'
because there is an open ResultSet dependent on that object.
-Since autocommit is off, the drop table exception will NOT result in a runtime
rollback and hence updatable resultset object is still open
----Positive Test14 - After deleteRow, resultset is positioned before the next
row
-getXXX right after deleteRow will fail because resultset is not positioned on
a row, instead it is right before the next row
-expected exception Invalid cursor state - no current row.
-Finished testing updateable resultsets
+Start testing delete using JDBC2.0 updateable resultset apis
+---Negative Testl - request for scroll insensitive updatable resultset will
give a read only scroll insensitive resultset
+warnings on connection = SQL Warning: 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
+Got expected exception 'deleteRow' 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
+requested TYPE_SCROLL_SENSITIVE, 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
+JDBC 2.0 updatable resultset api will fail on this resultset because this is
not an updatable resultset
+Got expected exception 'deleteRow' not allowed because the ResultSet is not an
updatable ResultSet.
+---Negative Test3 - request a read only resultset and attempt deleteRow on it
+Make sure that we got CONCUR_READ_ONLY? true
+Now attempting to send a deleteRow on a read only resultset.
+Got expected exception 'deleteRow' 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 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.
+Got expected exception 'deleteRow' 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
+Expected warnings on resultset = java.sql.SQLWarning: 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.
+Got expected exception 'deleteRow' 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
+Expected warnings on resultset = java.sql.SQLWarning: 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.
+Got expected exception 'deleteRow' not allowed because the ResultSet is not an
updatable ResultSet.
+---Negative Test7 - attempt to deleteRow 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.
+Got expected exception Invalid cursor state - no current row.
+ResultSet is positioned after the last row. attempt to deleteRow at this point
should fail!
+Got expected exception Invalid cursor state - no current row.
+---Negative Test8 - attempt deleteRow on updatable resultset after closing the
resultset
+Make sure that we got CONCUR_UPDATABLE? true
+Got expected exception ResultSet not open, operation 'deleteRow' not
permitted. Verify that autocommit is OFF.
+---Negative Test9 - try updatable resultset on system table
+expected exception FOR UPDATE is not permitted on this type of statement.
+---Negative Test10 - try updatable resultset on a view
+expected exception FOR UPDATE is not permitted on this type of statement.
+---Negative Test11 - attempt to open updatable resultset when there is join in
the select query should fail
+expected exception FOR UPDATE is not permitted on 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
+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
+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
+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
+expected exception ResultSet not open, operation 'next' not permitted. Verify
that autocommit is OFF.
+---Positive Test1 - 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(), ResultSet is positioned before the next row, getXXX
will fail
+Got expected exception Invalid cursor state - no current row.
+calling deleteRow again w/o first positioning the ResultSet on the next row
will fail
+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 Test2 - even if no columns from table specified in the column
list, we should be able to get updatable resultset
+total number of rows in T1
+ 1
+ -
+ {3}
+column 1 on this row is 1
+total number of rows in T1 after one deleteRow is
+ 1
+ -
+ {2}
+---Positive Test3 - use prepared 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
+Got expected exception Invalid cursor state - no current row.
+calling deleteRow again w/o first positioning the ResultSet on the next row
will fail
+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 Test4 - use callable statement with concur updatable status
+requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE
+got TYPE_FORWARD_ONLY? true
+got CONCUR_UPDATABLE? true
+row not deleted yet. Confirm with rs.rowDeleted()? false
+column 1 on this row is 1
+Since after deleteRow(), ResultSet is positioned before the next row, getXXX
will fail
+Got expected exception Invalid cursor state - no current row.
+calling deleteRow again w/o first positioning the ResultSet on the next row
will fail
+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
+now try to delete row when primary key is not selected for that row
+---Positive Test6 - 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 Test7 - 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 Test8 - change the name of the resultset and see if deleteRow
still works
+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
+---Positive Test9 - using correlation name for the table in the select sql is
not a problem
+column 1 on this row is 1
+now try to deleteRow
+---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
+Got expected exception Cursor 'SQLCUR10' 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 1, 2 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:
+Project-Restrict ResultSet (3):
+Number of opens = 1
+Rows seen = 0
+Rows filtered = 0
+restriction = false
+projection = true
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+Source result set:
+ Project-Restrict ResultSet (2):
+ Number of opens = 1
+ Rows seen = 0
+ Rows filtered = 0
+ restriction = false
+ projection = true
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+ Source result set:
+ 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 Test12 - 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 Test13 - 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
+expected exception Invalid cursor state - no current row.
+Verify that delete trigger got fired by verifying the row count to be 0 in
anotherTableWithDeleteTriggers
+ 1
+ -
+ {0}
+---Positive Test14 - make sure self referential delete cascade works when
deleteRow is issued
+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
+expected exception Invalid cursor state - no current row.
+Verify that delete trigger got fired by verifying the row count to be 0 in
anotherTableWithDeleteTriggers
+ 1
+ -
+ {0}
+---Positive Test15 - With autocommit off, 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
+expected exception Operation 'DROP TABLE' cannot be performed on object 'T1'
because there is an open ResultSet dependent on that object.
+Since autocommit is off, the drop table exception will NOT result in a runtime
rollback and hence updatable resultset object is still open
+---Positive Test16 - Do deleteRow within a transaction and then rollback the
transaction
+Verify that before delete trigger got fired, row count is 0 in
deleteTriggerInsertIntoThisTable
+ 1
+ -
+ {0}
+Verify that before deleteRow, row count is 4 in tableWithDeleteTriggers
+ 1
+ -
+ {4}
+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}
+Verify that deleteRow in transaction, row count is 3 in tableWithDeleteTriggers
+ 1
+ -
+ {3}
+Verify that after rollback, row count is back to 0 in
deleteTriggerInsertIntoThisTable
+ 1
+ -
+ {0}
+Verify that after rollback, row count is back to 4 in tableWithDeleteTriggers
+ 1
+ -
+ {4}
+---Positive Test17 - After deleteRow, resultset is positioned before the next
row
+getXXX right after deleteRow will fail because resultset is not positioned on
a row, instead it is right before the next row
+expected exception Invalid cursor state - no current row.
+Finished testing updateable resultsets
