You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ba...@apache.org on 2005/03/23 20:39:24 UTC
svn commit: r158821 - in
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests:
master/DerbyNet/updatableResultSet.out
master/DerbyNetClient/updatableResultSet.out
master/jdk14/updatableResultSet.out master/updatableResultSet.out
tests/lang/updatableResultSet.java
Author: bandaram
Date: Wed Mar 23 11:39:22 2005
New Revision: 158821
URL: http://svn.apache.org/viewcvs?view=rev&rev=158821
Log:
Enable negative test for scroll sensitive updatable resultsets.
Submitted by Mamta Satoor. (msatoor@gmail.com)
Modified:
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/updatableResultSet.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/updatableResultSet.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk14/updatableResultSet.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/updatableResultSet.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/updatableResultSet.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/updatableResultSet.out?view=diff&r1=158820&r2=158821
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/updatableResultSet.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/updatableResultSet.out Wed Mar 23 11:39:22 2005
@@ -12,6 +12,16 @@
Got 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 : null
Got 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 resultset
+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? false
+Make sure that we got CONCUR_READ_ONLY? false
+JDBC 2.0 updatable resultset api will fail on this resultset because this is not an updatable resultset
+SQL State : null
+Got 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 : null
+Got 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 it
Make sure that we got CONCUR_READ_ONLY? true
Now attempting to send a deleteRow on a read only resultset.
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/updatableResultSet.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/updatableResultSet.out?view=diff&r1=158820&r2=158821
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/updatableResultSet.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/updatableResultSet.out Wed Mar 23 11:39:22 2005
@@ -12,6 +12,16 @@
Got 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 : null
Got 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 resultset
+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 : null
+Got 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 : null
+Got 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 it
Make sure that we got CONCUR_READ_ONLY? true
Now attempting to send a deleteRow on a read only resultset.
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk14/updatableResultSet.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk14/updatableResultSet.out?view=diff&r1=158820&r2=158821
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk14/updatableResultSet.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk14/updatableResultSet.out Wed Mar 23 11:39:22 2005
@@ -14,6 +14,7 @@
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
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
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/updatableResultSet.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/updatableResultSet.out?view=diff&r1=158820&r2=158821
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/updatableResultSet.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/updatableResultSet.out Wed Mar 23 11:39:22 2005
@@ -14,6 +14,7 @@
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
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
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java?view=diff&r1=158820&r2=158821
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java Wed Mar 23 11:39:22 2005
@@ -218,13 +218,13 @@
}
conn.clearWarnings();
System.out.println("requested TYPE_SCROLL_INSENSITIVE, CONCUR_UPDATABLE but that is not supported");
- System.out.println("Make sure that we got TYPE_SCROLL_INSENSITIVE? " + (stmt.getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE));
- System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (stmt.getResultSetConcurrency() == ResultSet.CONCUR_READ_ONLY));
- dbmt = conn.getMetaData();
- System.out.println("ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? " + dbmt.ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
- System.out.println("othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? " + dbmt.othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
- System.out.println("deletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE)? " + dbmt.deletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE));
- System.out.println("JDBC 2.0 updatable resultset api will fail on this resultset because this is not an updatable resultset");
+ System.out.println("Make sure that we got TYPE_SCROLL_INSENSITIVE? " + (stmt.getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE));
+ System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (stmt.getResultSetConcurrency() == ResultSet.CONCUR_READ_ONLY));
+ dbmt = conn.getMetaData();
+ System.out.println("ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? " + dbmt.ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
+ System.out.println("othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? " + dbmt.othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
+ System.out.println("deletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE)? " + dbmt.deletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE));
+ System.out.println("JDBC 2.0 updatable resultset api will fail on this resultset because this is not an updatable resultset");
rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
rs.next();
try {
@@ -247,7 +247,6 @@
//have to close the resultset because by default, resultsets are held open over commit
rs.close();
- if (TestUtil.isEmbeddedFramework()) {
System.out.println("Negative Test2 - request for scroll sensitive updatable resultset will give a read only scroll insensitive resultset");
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
while (warnings != null)
@@ -257,9 +256,10 @@
}
conn.clearWarnings();
System.out.println("requested TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE but that is not supported");
- System.out.println("Make sure that we got TYPE_SCROLL_INSENSITIVE? " + (stmt.getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE));
- System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (stmt.getResultSetConcurrency() == ResultSet.CONCUR_READ_ONLY));
- System.out.println("JDBC 2.0 updatable resultset api will fail on this resultset because this is not an updatable resultset");
+ System.out.println("Jira issue Derby-154 : When client connects to Network Server using JCC, it incorrectly shows support for scroll sensitive updatable resultsets");
+ System.out.println("Make sure that we got TYPE_SCROLL_INSENSITIVE? " + (stmt.getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE));
+ System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (stmt.getResultSetConcurrency() == ResultSet.CONCUR_READ_ONLY));
+ System.out.println("JDBC 2.0 updatable resultset api will fail on this resultset because this is not an updatable resultset");
rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
rs.next();
try {
@@ -281,7 +281,6 @@
rs.next();
//have to close the resultset because by default, resultsets are held open over commit
rs.close();
- }
System.out.println("Negative Test3 - request a read only resultset and attempt deleteRow and updateRow on it");
stmt = conn.createStatement();//the default is a read only forward only resultset
@@ -311,78 +310,78 @@
System.out.println("Negative Test4 - request a read only resultset and send a sql with FOR UPDATE clause and attempt deleteRow/updateRow on it");
stmt = conn.createStatement();//the default is a read only forward only resultset
- rs = stmt.executeQuery("select * from t1 FOR UPDATE");
- System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
- rs.next();
- System.out.println("Now attempting to send a deleteRow on a read only resultset with FOR UPDATE clause in the SELECT sql.");
- try {
- rs.deleteRow();
- System.out.println("FAIL!!! deleteRow should have failed because this is a read only resultset");
- }
- catch (SQLException e) {
- System.out.println("SQL State : " + e.getSQLState());
+ rs = stmt.executeQuery("select * from t1 FOR UPDATE");
+ System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
+ rs.next();
+ System.out.println("Now attempting to send a deleteRow on a read only resultset with FOR UPDATE clause in the SELECT sql.");
+ try {
+ rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should have failed because this is a read only resultset");
+ }
+ catch (SQLException e) {
+ System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
- }
+ }
System.out.println("Now attempting to send a updateRow on a read only resultset with FOR UPDATE clause in the SELECT sql.");
- try {
- rs.updateRow();
- System.out.println("FAIL!!! updateRow should have failed because this is a read only resultset");
- }
- catch (SQLException e) {
- System.out.println("SQL State : " + e.getSQLState());
- System.out.println("Got expected exception " + e.getMessage());
- }
- //have to close the resultset because by default, resultsets are held open over commit
- rs.close();
+ try {
+ rs.updateRow();
+ System.out.println("FAIL!!! updateRow should have failed because this is a read only resultset");
+ }
+ catch (SQLException e) {
+ System.out.println("SQL State : " + e.getSQLState());
+ System.out.println("Got expected exception " + e.getMessage());
+ }
+ //have to close the resultset because by default, resultsets are held open over commit
+ rs.close();
System.out.println("Negative Test5 - request updatable resultset for sql with no FOR UPDATE clause");
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
- rs = stmt.executeQuery("select * from t1");//notice that we forgot to give mandatory FOR UPDATE clause for updatable resultset
- System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
- System.out.println("Jira issue Derby-159 : Warnings raised by Derby are not getting passed to the Client in Network Server Mode");
- System.out.println("Will see the warnings in embedded mode only");
- warnings = rs.getWarnings();
- while (warnings != null)
- {
- System.out.println("Expected warnings on resultset = " + warnings);
- warnings = warnings.getNextWarning();
- }
- rs.clearWarnings();
- rs.next();
- System.out.println("Now attempting to send a delete on a sql with no FOR UPDATE clause.");
- try {
- rs.deleteRow();
- System.out.println("FAIL!!! deleteRow should have failed on sql with no FOR UPDATE clause");
- }
- catch (SQLException e) {
- System.out.println("SQL State : " + e.getSQLState());
- System.out.println("Got expected exception " + e.getMessage());
- }
- //have to close the resultset because by default, resultsets are held open over commit
- rs.close();
+ rs = stmt.executeQuery("select * from t1");//notice that we forgot to give mandatory FOR UPDATE clause for updatable resultset
+ System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
+ System.out.println("Jira issue Derby-159 : Warnings raised by Derby are not getting passed to the Client in Network Server Mode");
+ System.out.println("Will see the warnings in embedded mode only");
+ warnings = rs.getWarnings();
+ while (warnings != null)
+ {
+ System.out.println("Expected warnings on resultset = " + warnings);
+ warnings = warnings.getNextWarning();
+ }
+ rs.clearWarnings();
+ rs.next();
+ System.out.println("Now attempting to send a delete on a sql with no FOR UPDATE clause.");
+ try {
+ rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should have failed on sql with no FOR UPDATE clause");
+ }
+ catch (SQLException e) {
+ System.out.println("SQL State : " + e.getSQLState());
+ System.out.println("Got expected exception " + e.getMessage());
+ }
+ //have to close the resultset because by default, resultsets are held open over commit
+ rs.close();
System.out.println("Now attempting to send a updateRow on a sql with no FOR UPDATE clause.");
- try {
- rs.updateRow();
- System.out.println("FAIL!!! updateRow should have failed on sql with no FOR UPDATE clause");
- }
- catch (SQLException e) {
- System.out.println("SQL State : " + e.getSQLState());
+ try {
+ rs.updateRow();
+ System.out.println("FAIL!!! updateRow should have failed on sql with no FOR UPDATE clause");
+ }
+ catch (SQLException e) {
+ System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
- }
+ }
System.out.println("Negative Test6 - request updatable resultset for sql with FOR READ ONLY clause");
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from t1 FOR READ ONLY");
- System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
- warnings = rs.getWarnings();
- while (warnings != null)
- {
- System.out.println("Expected warnings on resultset = " + warnings);
- warnings = warnings.getNextWarning();
- }
- rs.clearWarnings();
- rs.next();
- System.out.println("Now attempting to send a delete on a sql with FOR READ ONLY clause.");
+ System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
+ warnings = rs.getWarnings();
+ while (warnings != null)
+ {
+ System.out.println("Expected warnings on resultset = " + warnings);
+ warnings = warnings.getNextWarning();
+ }
+ rs.clearWarnings();
+ rs.next();
+ System.out.println("Now attempting to send a delete on a sql with FOR READ ONLY clause.");
try {
rs.deleteRow();
System.out.println("FAIL!!! deleteRow should have failed on sql with FOR READ ONLY clause");
@@ -391,7 +390,7 @@
System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
}
- System.out.println("Now attempting to send a updateRow on a sql with FOR READ ONLY clause.");
+ System.out.println("Now attempting to send a updateRow on a sql with FOR READ ONLY clause.");
try {
rs.updateRow();
System.out.println("FAIL!!! updateRow should have failed on sql with FOR READ ONLY clause");
@@ -400,7 +399,7 @@
System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
}
- //have to close the resultset because by default, resultsets are held open over commit
+ //have to close the resultset because by default, resultsets are held open over commit
rs.close();
if (TestUtil.isEmbeddedFramework()) {
@@ -408,7 +407,7 @@
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
System.out.println("Make sure that we got CONCUR_UPDATABLE? " + (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE));
- System.out.println("Now attempt a deleteRow without first doing next on the resultset.");
+ System.out.println("Now attempt a deleteRow without first doing next on the resultset.");
try {
rs.deleteRow();
System.out.println("FAIL!!! deleteRow should have failed because resultset is not on a row");
@@ -417,7 +416,7 @@
System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
}
- System.out.println("Now attempt a updateRow without first doing next on the resultset.");
+ System.out.println("Now attempt a updateRow without first doing next on the resultset.");
try {
rs.updateRow();
System.out.println("FAIL!!! updateRow should have failed because resultset is not on a row");
@@ -426,7 +425,7 @@
System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
}
- while (rs.next());//read all the rows from the resultset and position after the last row
+ while (rs.next());//read all the rows from the resultset and position after the last row
System.out.println("ResultSet is positioned after the last row. attempt to deleteRow at this point should fail!");
try {
rs.deleteRow();
@@ -507,7 +506,7 @@
rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
rs.next();
System.out.println("Opened an updatable resultset. Now trying to drop that table through another Statement");
- stmt1 = conn.createStatement();
+ stmt1 = conn.createStatement();
try {
stmt1.executeUpdate("drop table t1");
System.out.println("FAIL!!! drop table should have failed because the updatable resultset is still open");
@@ -517,23 +516,23 @@
System.out.println("Got expected exception " + e.getMessage());
}
System.out.println("Since autocommit is on, the drop table exception resulted in a runtime rollback causing updatable resultset object to close");
- try {
- rs.updateRow();
- System.out.println("FAIL!!! resultset should have been closed at this point and updateRow should have failed");
- }
- catch (SQLException e) {
- System.out.println("SQL State : " + e.getSQLState());
- System.out.println("Got expected exception " + e.getMessage());
- }
- try {
- rs.deleteRow();
- System.out.println("FAIL!!! resultset should have been closed at this point and deleteRow should have failed");
- }
- catch (SQLException e) {
- System.out.println("SQL State : " + e.getSQLState());
+ try {
+ rs.updateRow();
+ System.out.println("FAIL!!! resultset should have been closed at this point and updateRow should have failed");
+ }
+ catch (SQLException e) {
+ System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
- }
-
+ }
+ try {
+ rs.deleteRow();
+ System.out.println("FAIL!!! resultset should have been closed at this point and deleteRow should have failed");
+ }
+ catch (SQLException e) {
+ System.out.println("SQL State : " + e.getSQLState());
+ System.out.println("Got expected exception " + e.getMessage());
+ }
+
System.out.println("Negative Test13 - foreign key constraint failure will cause deleteRow to fail");
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT 1, 2 FROM tableWithPrimaryKey FOR UPDATE");
@@ -547,15 +546,15 @@
System.out.println("Got expected exception " + e.getMessage());
}
System.out.println("Since autocommit is on, the constraint exception resulted in a runtime rollback causing updatable resultset object to close");
- try {
- rs.next();
- System.out.println("FAIL!!! next should have failed because foreign key constraint failure resulted in a runtime rollback");
- }
- catch (SQLException e) {
- System.out.println("SQL State : " + e.getSQLState());
+ try {
+ rs.next();
+ System.out.println("FAIL!!! next should have failed because foreign key constraint failure resulted in a runtime rollback");
+ }
+ catch (SQLException e) {
+ System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
- }
-
+ }
+
System.out.println("Negative Test14 - foreign key constraint failure will cause updateRow to fail");
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT c1, c2 FROM tableWithPrimaryKey FOR UPDATE");
@@ -571,17 +570,17 @@
System.out.println("Got expected exception " + e.getMessage());
}
System.out.println("Since autocommit is on, the constraint exception resulted in a runtime rollback causing updatable resultset object to close");
- try {
- rs.next();
- System.out.println("FAIL!!! next should have failed because foreign key constraint failure resulted in a runtime rollback");
- }
- catch (SQLException e) {
- System.out.println("SQL State : " + e.getSQLState());
+ try {
+ rs.next();
+ System.out.println("FAIL!!! next should have failed because foreign key constraint failure resulted in a runtime rollback");
+ }
+ catch (SQLException e) {
+ System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
- }
-
+ }
+
System.out.println("Negative Test15 - Can't call updateXXX methods on columns that do not correspond to a column in the table");
- stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT 1, 2 FROM tableWithPrimaryKey FOR UPDATE");
rs.next();
try {
@@ -594,7 +593,7 @@
}
System.out.println("Negative Test16 - Call updateXXX method on out of the range column");
- stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT c1, c2 FROM t1 FOR UPDATE");
rs.next();
System.out.println("There are only 2 columns in the select list and we are trying to send updateXXX on column position 3");
@@ -625,13 +624,13 @@
System.out.println("column 2 on this row before deleteRow is " + rs.getString(2));
rs.deleteRow();
System.out.println("Since after deleteRow(), ResultSet is positioned before the next row, getXXX will fail");
- try {
- System.out.println("column 1 on this deleted row is " + rs.getInt(1));
- }
- catch (SQLException e) {
- System.out.println("SQL State : " + e.getSQLState());
+ try {
+ System.out.println("column 1 on this deleted row is " + rs.getInt(1));
+ }
+ catch (SQLException e) {
+ System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
- }
+ }
System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
try {
rs.deleteRow();
@@ -644,7 +643,7 @@
System.out.println("Position the ResultSet with next()");
rs.next();
System.out.println("Should be able to deletRow() on the current row now");
- rs.deleteRow();
+ rs.deleteRow();
//have to close the resultset because by default, resultsets are held open over commit
rs.close();
@@ -666,13 +665,13 @@
System.out.println("now updateRow on the row");
rs.updateRow();
System.out.println("Since after updateRow(), ResultSet is positioned before the next row, getXXX will fail");
- try {
- System.out.println("column 1 on this updateRow row is " + rs.getInt(1));
- }
- catch (SQLException e) {
- System.out.println("SQL State : " + e.getSQLState());
+ try {
+ System.out.println("column 1 on this updateRow row is " + rs.getInt(1));
+ }
+ catch (SQLException e) {
+ System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
- }
+ }
System.out.println("calling updateRow again w/o first positioning the ResultSet on the next row will fail");
try {
rs.updateRow();
@@ -685,15 +684,15 @@
System.out.println("Position the ResultSet with next()");
rs.next();
System.out.println("Should be able to updateRow() on the current row now");
- rs.updateString(2,"234");
- rs.updateRow();
+ rs.updateString(2,"234");
+ rs.updateRow();
//have to close the resultset because by default, resultsets are held open over commit
rs.close();
System.out.println("Positive Test2 - even if no columns from table specified in the column list, we should be able to get updatable resultset");
reloadData();
System.out.println("total number of rows in T1 ");
- dumpRS(stmt.executeQuery("select count(*) from t1"));
+ dumpRS(stmt.executeQuery("select count(*) from t1"));
rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
rs.next();
System.out.println("column 1 on this row is " + rs.getInt(1));
@@ -701,7 +700,7 @@
//have to close the resultset because by default, resultsets are held open over commit
rs.close();
System.out.println("total number of rows in T1 after one deleteRow is ");
- dumpRS(stmt.executeQuery("select count(*) from t1"));
+ dumpRS(stmt.executeQuery("select count(*) from t1"));
System.out.println("Positive Test3a - use prepared statement with concur updatable status to test deleteRow");
reloadData();
@@ -715,14 +714,14 @@
System.out.println("column 1 on this row is " + rs.getInt(1));
rs.deleteRow();
System.out.println("Since after deleteRow(), ResultSet is positioned before the next row, getXXX will fail");
- try {
- System.out.println("column 1 on this deleted row is " + rs.getInt(1));
- }
- catch (SQLException e) {
- System.out.println("SQL State : " + e.getSQLState());
+ try {
+ System.out.println("column 1 on this deleted row is " + rs.getInt(1));
+ }
+ catch (SQLException e) {
+ System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
- }
- System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
+ }
+ System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
try {
rs.deleteRow();
System.out.println("FAIL!!! deleteRow should have failed because it can't be called more than once on the same row");
@@ -734,7 +733,7 @@
System.out.println("Position the ResultSet with next()");
rs.next();
System.out.println("Should be able to deletRow() on the current row now");
- rs.deleteRow();
+ rs.deleteRow();
//have to close the resultset because by default, resultsets are held open over commit
rs.close();
@@ -752,14 +751,14 @@
System.out.println("column 1 on this row after updateInt is " + rs.getInt(1));
rs.updateRow();
System.out.println("Since after updateRow(), ResultSet is positioned before the next row, getXXX will fail");
- try {
- System.out.println("column 1 on this updated row is " + rs.getInt(1));
- }
- catch (SQLException e) {
- System.out.println("SQL State : " + e.getSQLState());
+ try {
+ System.out.println("column 1 on this updated row is " + rs.getInt(1));
+ }
+ catch (SQLException e) {
+ System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
- }
- System.out.println("calling updateRow/updateXXX again w/o first positioning the ResultSet on the next row will fail");
+ }
+ System.out.println("calling updateRow/updateXXX again w/o first positioning the ResultSet on the next row will fail");
try {
rs.updateInt(1,0);
System.out.println("FAIL!!! updateXXX should have failed because resultset is not positioned on a row");
@@ -787,7 +786,7 @@
System.out.println("Position the ResultSet with next()");
rs.next();
System.out.println("Should be able to cancelRowUpdates() on the current row now");
- rs.cancelRowUpdates();
+ rs.cancelRowUpdates();
//have to close the resultset because by default, resultsets are held open over commit
rs.close();
@@ -802,14 +801,14 @@
System.out.println("column 1 on this row is " + rs.getInt(1));
rs.deleteRow();
System.out.println("Since after deleteRow(), ResultSet is positioned before the next row, getXXX will fail");
- try {
- System.out.println("column 1 on this deleted row is " + rs.getInt(1));
- }
- catch (SQLException e) {
- System.out.println("SQL State : " + e.getSQLState());
+ try {
+ System.out.println("column 1 on this deleted row is " + rs.getInt(1));
+ }
+ catch (SQLException e) {
+ System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
- }
- System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
+ }
+ System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
try {
rs.deleteRow();
System.out.println("FAIL!!! deleteRow should have failed because it can't be called more than once on the same row");
@@ -821,7 +820,7 @@
System.out.println("Position the ResultSet with next()");
rs.next();
System.out.println("Should be able to deletRow() on the current row now");
- rs.deleteRow();
+ rs.deleteRow();
//have to close the resultset because by default, resultsets are held open over commit
rs.close();
@@ -840,39 +839,39 @@
rs.close();
System.out.println("Positive Test6a - For Forward Only resultsets, DatabaseMetaData will return false for ownDeletesAreVisible and deletesAreDetected");
- System.out.println("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");
+ System.out.println("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");
dbmt = conn.getMetaData();
System.out.println("ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
System.out.println("othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
System.out.println("deletesAreDetected(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.deletesAreDetected(ResultSet.TYPE_FORWARD_ONLY));
reloadData();
- stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
- rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE of c1");
- rs.next();
- System.out.println("The JDBC program should look at rowDeleted only if deletesAreDetected returns true");
- System.out.println("Since Derby returns false for detlesAreDetected for FORWARD_ONLY updatable resultset,the program should not rely on rs.rowDeleted() for FORWARD_ONLY updatable resultsets");
- System.out.println("Have this call to rs.rowDeleted() just to make sure the method does always return false? " + rs.rowDeleted());
- rs.deleteRow();
- System.out.println("Have this call to rs.rowDeleted() just to make sure the method does always return false? " + rs.rowDeleted());
- rs.close();
+ stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE of c1");
+ rs.next();
+ System.out.println("The JDBC program should look at rowDeleted only if deletesAreDetected returns true");
+ System.out.println("Since Derby returns false for detlesAreDetected for FORWARD_ONLY updatable resultset,the program should not rely on rs.rowDeleted() for FORWARD_ONLY updatable resultsets");
+ System.out.println("Have this call to rs.rowDeleted() just to make sure the method does always return false? " + rs.rowDeleted());
+ rs.deleteRow();
+ System.out.println("Have this call to rs.rowDeleted() just to make sure the method does always return false? " + rs.rowDeleted());
+ rs.close();
System.out.println("Positive Test6b - For Forward Only resultsets, DatabaseMetaData will return false for ownUpdatesAreVisible and updatesAreDetected");
- System.out.println("This is because, after updateRow, we position the ResultSet before the next row");
+ System.out.println("This is because, after updateRow, we position the ResultSet before the next row");
dbmt = conn.getMetaData();
System.out.println("ownUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.ownUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
System.out.println("othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
System.out.println("updatesAreDetected(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.updatesAreDetected(ResultSet.TYPE_FORWARD_ONLY));
reloadData();
- stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
- rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
- rs.next();
- System.out.println("The JDBC program should look at rowUpdated only if updatesAreDetected returns true");
- System.out.println("Since Derby returns false for updatesAreDetected for FORWARD_ONLY updatable resultset,the program should not rely on rs.rowUpdated() for FORWARD_ONLY updatable resultsets");
- System.out.println("Have this call to rs.rowUpdated() just to make sure the method does always return false? " + rs.rowUpdated());
- rs.updateLong(1,123);
+ stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
+ rs.next();
+ System.out.println("The JDBC program should look at rowUpdated only if updatesAreDetected returns true");
+ System.out.println("Since Derby returns false for updatesAreDetected for FORWARD_ONLY updatable resultset,the program should not rely on rs.rowUpdated() for FORWARD_ONLY updatable resultsets");
+ System.out.println("Have this call to rs.rowUpdated() just to make sure the method does always return false? " + rs.rowUpdated());
+ rs.updateLong(1,123);
rs.updateRow();
- System.out.println("Have this call to rs.rowUpdated() just to make sure the method does always return false? " + rs.rowUpdated());
- rs.close();
+ System.out.println("Have this call to rs.rowUpdated() just to make sure the method does always return false? " + rs.rowUpdated());
+ rs.close();
System.out.println("Positive Test7a - delete using updatable resultset api from a temporary table");
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
@@ -989,8 +988,8 @@
System.out.println("Got expected exception " + e.getMessage());
}
System.out.println("Move to next row in the 2nd resultset and then delete using the second resultset");
- rs1.next();
- rs1.deleteRow();
+ rs1.next();
+ rs1.deleteRow();
rs.close();
rs1.close();
conn.setAutoCommit(true);
@@ -1040,218 +1039,218 @@
//have to close the resultset because by default, resultsets are held open over commit
rs.close();
- System.out.println("Positive Test13a - Another test case for delete trigger");
- stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
- rs = stmt.executeQuery("SELECT * FROM table1WithTriggers 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) {
- System.out.println("SQL State : " + e.getSQLState());
- System.out.println("Got expected exception " + e.getMessage());
- }
- rs.close();
- System.out.println("Verify that delete trigger got fired by verifying the row count to be 0 in table1WithTriggers");
- dumpRS(stmt.executeQuery("select count(*) from table1WithTriggers"));
- //have to close the resultset because by default, resultsets are held open over commit
- rs.close();
-
- System.out.println("Positive Test13b - Another test case for update trigger");
- System.out.println("Look at the current contents of table2WithTriggers");
- dumpRS(stmt.executeQuery("select * from table2WithTriggers"));
- stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
- rs = stmt.executeQuery("SELECT * FROM table2WithTriggers where c1>1 FOR UPDATE");
- rs.next();
- System.out.println("column 1 on this row is " + rs.getInt(1));
- System.out.println("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");
- rs.updateLong(1,123);
+ System.out.println("Positive Test13a - Another test case for delete trigger");
+ stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("SELECT * FROM table1WithTriggers 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) {
+ System.out.println("SQL State : " + e.getSQLState());
+ System.out.println("Got expected exception " + e.getMessage());
+ }
+ rs.close();
+ System.out.println("Verify that delete trigger got fired by verifying the row count to be 0 in table1WithTriggers");
+ dumpRS(stmt.executeQuery("select count(*) from table1WithTriggers"));
+ //have to close the resultset because by default, resultsets are held open over commit
+ rs.close();
+
+ System.out.println("Positive Test13b - Another test case for update trigger");
+ System.out.println("Look at the current contents of table2WithTriggers");
+ dumpRS(stmt.executeQuery("select * from table2WithTriggers"));
+ stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("SELECT * FROM table2WithTriggers where c1>1 FOR UPDATE");
+ rs.next();
+ System.out.println("column 1 on this row is " + rs.getInt(1));
+ System.out.println("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");
+ rs.updateLong(1,123);
rs.updateRow();
- rs.next();
- try {
- rs.updateRow();
- System.out.println("FAIL!!! there should have be no more rows in the resultset at this point because update trigger made all the rows not qualify for the resultset");
- }
- catch (SQLException e) {
- System.out.println("SQL State : " + e.getSQLState());
- System.out.println("Got expected exception " + e.getMessage());
- }
- rs.close();
- System.out.println("Verify that update trigger got fired by verifying that all column c1s have value 1 in table2WithTriggers");
- dumpRS(stmt.executeQuery("select * from table2WithTriggers"));
- //have to close the resultset because by default, resultsets are held open over commit
- rs.close();
-
- System.out.println("Positive Test14a - make sure self referential delete cascade works when deleteRow is issued");
- dumpRS(stmt.executeQuery("select * from selfReferencingT1"));
- 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) {
- System.out.println("SQL State : " + e.getSQLState());
- System.out.println("Got expected exception " + e.getMessage());
- }
- rs.close();
- System.out.println("Verify that delete trigger got fired by verifying the row count to be 0 in selfReferencingT1");
- 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 Test14b - make sure self referential update restrict works when updateRow is issued");
- dumpRS(stmt.executeQuery("select * from selfReferencingT2"));
- stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
- rs = stmt.executeQuery("SELECT * FROM selfReferencingT2 FOR UPDATE");
- rs.next();
- System.out.println("column 1 on this row is " + rs.getString(1));
- System.out.println("update row should fail because cascade constraint is update restrict");
- rs.updateString(1,"e2");
- try {
- rs.updateRow();
- System.out.println("FAIL!!! this update should have caused violation of foreign key constraint");
- }
- catch (SQLException e) {
- System.out.println("SQL State : " + e.getSQLState());
- System.out.println("Got expected exception " + e.getMessage());
- }
- //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);
- rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
- rs.next();
- System.out.println("Opened an updatable resultset. Now trying to drop that table through another Statement");
- stmt1 = conn.createStatement();
- try {
- stmt1.executeUpdate("drop table t1");
- System.out.println("FAIL!!! drop table should have failed because the updatable resultset is still open");
- }
- catch (SQLException e) {
- System.out.println("SQL State : " + e.getSQLState());
- System.out.println("Got expected exception " + e.getMessage());
- }
- System.out.println("Since autocommit is off, the drop table exception will NOT result in a runtime rollback and hence updatable resultset object is still open");
- rs.deleteRow();
- rs.close();
- conn.setAutoCommit(true);
-
- System.out.println("Positive Test16a - 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 table0WithTriggers");
- dumpRS(stmt.executeQuery("select count(*) from table0WithTriggers"));
- rs = stmt.executeQuery("SELECT * FROM table0WithTriggers 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 table0WithTriggers");
- dumpRS(stmt.executeQuery("select count(*) from table0WithTriggers"));
- //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 table0WithTriggers");
- dumpRS(stmt.executeQuery("select count(*) from table0WithTriggers"));
- conn.setAutoCommit(true);
-
- System.out.println("Positive Test16b - Do updateRow 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 update trigger got fired, row count is 0 in updateTriggerInsertIntoThisTable");
- dumpRS(stmt.executeQuery("select count(*) from updateTriggerInsertIntoThisTable"));
- System.out.println("Look at the data in table0WithTriggers before trigger gets fired");
- dumpRS(stmt.executeQuery("select * from table0WithTriggers"));
- rs = stmt.executeQuery("SELECT * FROM table0WithTriggers FOR UPDATE");
- rs.next();
- System.out.println("column 1 on this row is " + rs.getInt(1));
+ rs.next();
+ try {
+ rs.updateRow();
+ System.out.println("FAIL!!! there should have be no more rows in the resultset at this point because update trigger made all the rows not qualify for the resultset");
+ }
+ catch (SQLException e) {
+ System.out.println("SQL State : " + e.getSQLState());
+ System.out.println("Got expected exception " + e.getMessage());
+ }
+ rs.close();
+ System.out.println("Verify that update trigger got fired by verifying that all column c1s have value 1 in table2WithTriggers");
+ dumpRS(stmt.executeQuery("select * from table2WithTriggers"));
+ //have to close the resultset because by default, resultsets are held open over commit
+ rs.close();
+
+ System.out.println("Positive Test14a - make sure self referential delete cascade works when deleteRow is issued");
+ dumpRS(stmt.executeQuery("select * from selfReferencingT1"));
+ 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) {
+ System.out.println("SQL State : " + e.getSQLState());
+ System.out.println("Got expected exception " + e.getMessage());
+ }
+ rs.close();
+ System.out.println("Verify that delete trigger got fired by verifying the row count to be 0 in selfReferencingT1");
+ 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 Test14b - make sure self referential update restrict works when updateRow is issued");
+ dumpRS(stmt.executeQuery("select * from selfReferencingT2"));
+ stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("SELECT * FROM selfReferencingT2 FOR UPDATE");
+ rs.next();
+ System.out.println("column 1 on this row is " + rs.getString(1));
+ System.out.println("update row should fail because cascade constraint is update restrict");
+ rs.updateString(1,"e2");
+ try {
+ rs.updateRow();
+ System.out.println("FAIL!!! this update should have caused violation of foreign key constraint");
+ }
+ catch (SQLException e) {
+ System.out.println("SQL State : " + e.getSQLState());
+ System.out.println("Got expected exception " + e.getMessage());
+ }
+ //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);
+ rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
+ rs.next();
+ System.out.println("Opened an updatable resultset. Now trying to drop that table through another Statement");
+ stmt1 = conn.createStatement();
+ try {
+ stmt1.executeUpdate("drop table t1");
+ System.out.println("FAIL!!! drop table should have failed because the updatable resultset is still open");
+ }
+ catch (SQLException e) {
+ System.out.println("SQL State : " + e.getSQLState());
+ System.out.println("Got expected exception " + e.getMessage());
+ }
+ System.out.println("Since autocommit is off, the drop table exception will NOT result in a runtime rollback and hence updatable resultset object is still open");
+ rs.deleteRow();
+ rs.close();
+ conn.setAutoCommit(true);
+
+ System.out.println("Positive Test16a - 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 table0WithTriggers");
+ dumpRS(stmt.executeQuery("select count(*) from table0WithTriggers"));
+ rs = stmt.executeQuery("SELECT * FROM table0WithTriggers 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 table0WithTriggers");
+ dumpRS(stmt.executeQuery("select count(*) from table0WithTriggers"));
+ //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 table0WithTriggers");
+ dumpRS(stmt.executeQuery("select count(*) from table0WithTriggers"));
+ conn.setAutoCommit(true);
+
+ System.out.println("Positive Test16b - Do updateRow 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 update trigger got fired, row count is 0 in updateTriggerInsertIntoThisTable");
+ dumpRS(stmt.executeQuery("select count(*) from updateTriggerInsertIntoThisTable"));
+ System.out.println("Look at the data in table0WithTriggers before trigger gets fired");
+ dumpRS(stmt.executeQuery("select * from table0WithTriggers"));
+ rs = stmt.executeQuery("SELECT * FROM table0WithTriggers FOR UPDATE");
+ rs.next();
+ System.out.println("column 1 on this row is " + rs.getInt(1));
System.out.println("now try to update row and make sure that trigger got fired");
rs.updateLong(1,123);
rs.updateRow();
rs.close();
- System.out.println("Verify that update trigger got fired by verifying the row count to be 1 in updateTriggerInsertIntoThisTable");
- dumpRS(stmt.executeQuery("select count(*) from updateTriggerInsertIntoThisTable"));
- System.out.println("Verify that new data in table0WithTriggers");
- dumpRS(stmt.executeQuery("select * from table0WithTriggers"));
- //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 updateTriggerInsertIntoThisTable");
- dumpRS(stmt.executeQuery("select count(*) from updateTriggerInsertIntoThisTable"));
- System.out.println("Verify that after rollback, table0WithTriggers is back to its original contents");
- dumpRS(stmt.executeQuery("select * from table0WithTriggers"));
- 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();
- System.out.println("getXXX right after deleteRow will fail because resultset is not positioned on a row, instead it is right before the next row");
- try {
- System.out.println("column 1 (which is not nullable) after deleteRow is " + rs.getString(1));
- }
- catch (SQLException e) {
- System.out.println("SQL State : " + e.getSQLState());
- System.out.println("Got expected exception " + e.getMessage());
- }
- rs.close();
-
- System.out.println("Positive Test18 - Test cancelRowUpdates method as the first updatable ResultSet api on a read-only resultset");
- stmt = conn.createStatement();
- rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable");
- try {
- rs.cancelRowUpdates();
- System.out.println("Test failed - should not have reached here because cancelRowUpdates is being called on a read-only resultset");
- } catch (SQLException e) {
+ System.out.println("Verify that update trigger got fired by verifying the row count to be 1 in updateTriggerInsertIntoThisTable");
+ dumpRS(stmt.executeQuery("select count(*) from updateTriggerInsertIntoThisTable"));
+ System.out.println("Verify that new data in table0WithTriggers");
+ dumpRS(stmt.executeQuery("select * from table0WithTriggers"));
+ //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 updateTriggerInsertIntoThisTable");
+ dumpRS(stmt.executeQuery("select count(*) from updateTriggerInsertIntoThisTable"));
+ System.out.println("Verify that after rollback, table0WithTriggers is back to its original contents");
+ dumpRS(stmt.executeQuery("select * from table0WithTriggers"));
+ 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();
+ System.out.println("getXXX right after deleteRow will fail because resultset is not positioned on a row, instead it is right before the next row");
+ try {
+ System.out.println("column 1 (which is not nullable) after deleteRow is " + rs.getString(1));
+ }
+ catch (SQLException e) {
+ System.out.println("SQL State : " + e.getSQLState());
+ System.out.println("Got expected exception " + e.getMessage());
+ }
+ rs.close();
+
+ System.out.println("Positive Test18 - Test cancelRowUpdates method as the first updatable ResultSet api on a read-only resultset");
+ stmt = conn.createStatement();
+ rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable");
+ try {
+ rs.cancelRowUpdates();
+ System.out.println("Test failed - should not have reached here because cancelRowUpdates is being called on a read-only resultset");
+ } catch (SQLException e) {
System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
}
- rs.close();
-
- System.out.println("Positive Test19 - Test updateRow method as the first updatable ResultSet api on a read-only resultset");
- stmt = conn.createStatement();
- rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable");
- rs.next();
- try {
- rs.updateRow();
- System.out.println("Test failed - should not have reached here because updateRow is being called on a read-only resultset");
- return;
- } catch (Throwable e) {
+ rs.close();
+
+ System.out.println("Positive Test19 - Test updateRow method as the first updatable ResultSet api on a read-only resultset");
+ stmt = conn.createStatement();
+ rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable");
+ rs.next();
+ try {
+ rs.updateRow();
+ System.out.println("Test failed - should not have reached here because updateRow is being called on a read-only resultset");
+ return;
+ } catch (Throwable e) {
System.out.println(" Got expected exception : " + e.getMessage());
}
- rs.close();
-
- System.out.println("Positive Test20 - Test updateXXX methods as the first updatable ResultSet api on a read-only resultset");
- conn.setAutoCommit(false);
- stmt = conn.createStatement();
- for (int updateXXXName = 1; updateXXXName <= allUpdateXXXNames.length; updateXXXName++) {
- System.out.println(" Test " + allUpdateXXXNames[updateXXXName-1] + " on a readonly resultset");
- for (int indexOrName = 1; indexOrName <= 2; indexOrName++) {
+ rs.close();
+
+ System.out.println("Positive Test20 - Test updateXXX methods as the first updatable ResultSet api on a read-only resultset");
+ conn.setAutoCommit(false);
+ stmt = conn.createStatement();
+ for (int updateXXXName = 1; updateXXXName <= allUpdateXXXNames.length; updateXXXName++) {
+ System.out.println(" Test " + allUpdateXXXNames[updateXXXName-1] + " on a readonly resultset");
+ for (int indexOrName = 1; indexOrName <= 2; indexOrName++) {
rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable");
rs.next();
rs1 = stmt1.executeQuery("SELECT * FROM AllDataTypesNewValuesData");
@@ -1260,7 +1259,7 @@
System.out.println(" Using column position as first parameter to " + allUpdateXXXNames[updateXXXName-1]);
else
System.out.println(" Using column name as first parameter to " + allUpdateXXXNames[updateXXXName-1]);
- try {
+ try {
if (updateXXXName == 1) {//update column with updateShort methods
if (indexOrName == 1) //test by passing column position
rs.updateShort(1, rs1.getShort(updateXXXName));
@@ -1378,21 +1377,21 @@
rs.updateRef(ColumnNames[0], null);
}
System.out.println("Test failed - should not have reached here because updateXXX is being called on a read-only resultset");
- return;
- } catch (Throwable e) {
+ return;
+ } catch (Throwable e) {
System.out.println(" Got expected exception : " + e.getMessage());
}
- }
- }
- conn.setAutoCommit(true);
-
- System.out.println("Positive Test21 - Test all updateXXX(excluding updateObject) methods on all the supported sql datatypes");
- conn.setAutoCommit(false);
- stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
- stmt1 = conn.createStatement();
- for (int sqlType = 1, checkAgainstColumn = 1; sqlType <= allSQLTypes.length; sqlType++ ) {
- System.out.println("Next datatype to test is " + allSQLTypes[sqlType-1]);
- for (int updateXXXName = 1; updateXXXName <= allUpdateXXXNames.length; updateXXXName++) {
+ }
+ }
+ conn.setAutoCommit(true);
+
+ System.out.println("Positive Test21 - Test all updateXXX(excluding updateObject) methods on all the supported sql datatypes");
+ conn.setAutoCommit(false);
+ stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ stmt1 = conn.createStatement();
+ for (int sqlType = 1, checkAgainstColumn = 1; sqlType <= allSQLTypes.length; sqlType++ ) {
+ System.out.println("Next datatype to test is " + allSQLTypes[sqlType-1]);
+ for (int updateXXXName = 1; updateXXXName <= allUpdateXXXNames.length; updateXXXName++) {
checkAgainstColumn = updateXXXName;
System.out.println(" Testing " + allUpdateXXXNames[updateXXXName-1] + " on SQL type " + allSQLTypes[sqlType-1]);
for (int indexOrName = 1; indexOrName <= 2; indexOrName++) {
@@ -1528,10 +1527,10 @@
return;
}
if (verifyData(sqlType,checkAgainstColumn, "AllDataTypesNewValuesData") == false)
- {
- System.out.println("Test failed");
- return;
- }
+ {
+ System.out.println("Test failed");
+ return;
+ }
} catch (Throwable e) {
if (updateXXXRulesTable[sqlType-1][updateXXXName-1].equals("ERROR"))
System.out.println(" Got expected exception : " + e.getMessage());
@@ -1547,19 +1546,19 @@
}
}
rs.close();
- rs1.close();
+ rs1.close();
}
}
- conn.setAutoCommit(true);
-
- System.out.println("Positive Test22 - Test updateObject method");
- conn.setAutoCommit(false);
- stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
- stmt1 = conn.createStatement();
- String displayString;
- for (int sqlType = 1; sqlType <= allSQLTypes.length; sqlType++ ) {
- System.out.println("Next datatype to test is " + allSQLTypes[sqlType-1]);
- for (int updateXXXName = 1; updateXXXName <= allUpdateXXXNames.length; updateXXXName++) {
+ conn.setAutoCommit(true);
+
+ System.out.println("Positive Test22 - Test updateObject method");
+ conn.setAutoCommit(false);
+ stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ stmt1 = conn.createStatement();
+ String displayString;
+ for (int sqlType = 1; sqlType <= allSQLTypes.length; sqlType++ ) {
+ System.out.println("Next datatype to test is " + allSQLTypes[sqlType-1]);
+ for (int updateXXXName = 1; updateXXXName <= allUpdateXXXNames.length; updateXXXName++) {
for (int indexOrName = 1; indexOrName <= 2; indexOrName++) {
if (indexOrName == 1) //test by passing column position
displayString = " updateObject with column position &";
@@ -1681,10 +1680,10 @@
return;
}
if (verifyData(sqlType,updateXXXName, "AllDataTypesNewValuesData") == false)
- {
- System.out.println("Test failed");
- return;
- }
+ {
+ System.out.println("Test failed");
+ return;
+ }
} catch (Throwable e) {
if (updateXXXRulesTable[sqlType-1][updateXXXName-1].equals("ERROR"))
System.out.println(" Got expected exception : " + e.getMessage());
@@ -1698,18 +1697,18 @@
}
}
rs.close();
- rs1.close();
+ rs1.close();
}
}
}
- conn.setAutoCommit(true);
-
- System.out.println("Positive Test23 - Test cancelRowUpdates after updateXXX methods on all the supported sql datatypes");
- conn.setAutoCommit(false);
- reloadAllDataTypesForTestingTableData();
- stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
- stmt1 = conn.createStatement();
- rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable FOR UPDATE");
+ conn.setAutoCommit(true);
+
+ System.out.println("Positive Test23 - Test cancelRowUpdates after updateXXX methods on all the supported sql datatypes");
+ conn.setAutoCommit(false);
+ reloadAllDataTypesForTestingTableData();
+ stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ stmt1 = conn.createStatement();
+ rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable FOR UPDATE");
rs.next();
rs1 = stmt1.executeQuery("SELECT * FROM AllDataTypesNewValuesData");
rs1.next();
@@ -1869,9 +1868,9 @@
}
rs.close();
- rs1.close();
- conn.setAutoCommit(true);
-
+ rs1.close();
+ conn.setAutoCommit(true);
+
System.out.println("Positive Test24a - after updateXXX, try cancelRowUpdates and then deleteRow");
reloadData();
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
@@ -1885,13 +1884,13 @@
System.out.println("Since after cancelRowUpdates(), ResultSet is positioned on the same row, getXXX will pass");
System.out.println("column 1 on this row after cancelRowUpdates is " + rs.getInt(1));
System.out.println("Since after cancelRowUpdates(), ResultSet is positioned on the same row, a deleteRow at this point will pass");
- try {
- rs.deleteRow();
- System.out.println("PASS : deleteRow passed as expected");
- }
- catch (SQLException e) {
- dumpSQLExceptions(e);
- }
+ try {
+ rs.deleteRow();
+ System.out.println("PASS : deleteRow passed as expected");
+ }
+ catch (SQLException e) {
+ dumpSQLExceptions(e);
+ }
System.out.println("calling updateRow after deleteRow w/o first positioning the ResultSet on the next row will fail");
try {
rs.updateRow();
@@ -1904,39 +1903,39 @@
System.out.println("Position the ResultSet with next()");
rs.next();
System.out.println("Should be able to updateRow() on the current row now");
- rs.updateString(2,"234");
- rs.updateRow();
+ rs.updateString(2,"234");
+ rs.updateRow();
//have to close the resultset because by default, resultsets are held open over commit
rs.close();
-
+
System.out.println("Positive Test25 - issue cancelRowUpdates without any updateXXX");
reloadData();
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
rs.next();
- rs.cancelRowUpdates();
- //have to close the resultset because by default, resultsets are held open over commit
+ rs.cancelRowUpdates();
+ //have to close the resultset because by default, resultsets are held open over commit
rs.close();
-
+
System.out.println("Positive Test26 - issue updateRow without any updateXXX will not move the resultset position");
reloadData();
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
rs.next();
- rs.updateRow(); //this will not move the resultset to right before the next row because there were no updateXXX issued before updateRow
- rs.updateRow();
- //have to close the resultset because by default, resultsets are held open over commit
+ rs.updateRow(); //this will not move the resultset to right before the next row because there were no updateXXX issued before updateRow
+ rs.updateRow();
+ //have to close the resultset because by default, resultsets are held open over commit
rs.close();
-
+
System.out.println("Positive Test27 - issue updateXXX and then deleteRow");
reloadData();
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
rs.next();
- rs.updateInt(1,1234);
- rs.updateString(2,"aaaaa");
- rs.deleteRow();
- try {
+ rs.updateInt(1,1234);
+ rs.updateString(2,"aaaaa");
+ rs.deleteRow();
+ try {
rs.updateRow();
System.out.println("FAIL!!! deleteRow should have moved the ResultSet to right before the next row");
}
@@ -1944,7 +1943,7 @@
System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
}
- try {
+ try {
rs.updateInt(1,2345);
System.out.println("FAIL!!! deleteRow should have moved the ResultSet to right before the next row");
}
@@ -1952,7 +1951,7 @@
System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
}
- try {
+ try {
rs.getInt(1);
System.out.println("FAIL!!! deleteRow should have moved the ResultSet to right before the next row");
}
@@ -1960,48 +1959,48 @@
System.out.println("SQL State : " + e.getSQLState());
System.out.println("Got expected exception " + e.getMessage());
}
- //have to close the resultset because by default, resultsets are held open over commit
+ //have to close the resultset because by default, resultsets are held open over commit
rs.close();
-
+
System.out.println("Positive Test28 - issue updateXXXs and then move off the row, the changes should be ignored");
reloadData();
dumpRS(stmt.executeQuery("select * from t1"));
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
rs.next();
- System.out.println(" column 1 on this row before updateInt is " + rs.getInt(1));
- System.out.println(" Issue updateInt to change the column's value to 2345");
- rs.updateInt(1,2345);
- System.out.println(" Move to next row w/o issuing updateRow");
- rs.next(); //the changes made on the earlier row should have be ignored because we moved off that row without issuing updateRow
- //have to close the resultset because by default, resultsets are held open over commit
- rs.close();
- System.out.println(" Make sure that changes didn't make it to the database");
- dumpRS(stmt.executeQuery("select * from t1"));
-
+ System.out.println(" column 1 on this row before updateInt is " + rs.getInt(1));
+ System.out.println(" Issue updateInt to change the column's value to 2345");
+ rs.updateInt(1,2345);
+ System.out.println(" Move to next row w/o issuing updateRow");
+ rs.next(); //the changes made on the earlier row should have be ignored because we moved off that row without issuing updateRow
+ //have to close the resultset because by default, resultsets are held open over commit
+ rs.close();
+ System.out.println(" Make sure that changes didn't make it to the database");
+ dumpRS(stmt.executeQuery("select * from t1"));
+
System.out.println("Positive Test29 - issue multiple updateXXXs and then a updateRow");
reloadData();
dumpRS(stmt.executeQuery("select * from t1"));
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
rs.next();
- System.out.println(" column 1 on this row before updateInt is " + rs.getInt(1));
- System.out.println(" Issue updateInt to change the column's value to 2345");
- rs.updateInt(1,2345);
- System.out.println(" Issue another updateInt on the same row and column to change the column's value to 9999");
- rs.updateInt(1,9999);
- System.out.println(" Issue updateString to change the column's value to 'xxxxxxx'");
- rs.updateString(2,"xxxxxxx");
- System.out.println(" Now issue updateRow");
- rs.updateRow();
- //have to close the resultset because by default, resultsets are held open over commit
- rs.close();
- System.out.println(" Make sure that changes made it to the database correctly");
- dumpRS(stmt.executeQuery("select * from t1"));
-
+ System.out.println(" column 1 on this row before updateInt is " + rs.getInt(1));
+ System.out.println(" Issue updateInt to change the column's value to 2345");
+ rs.updateInt(1,2345);
+ System.out.println(" Issue another updateInt on the same row and column to change the column's value to 9999");
+ rs.updateInt(1,9999);
+ System.out.println(" Issue updateString to change the column's value to 'xxxxxxx'");
+ rs.updateString(2,"xxxxxxx");
+ System.out.println(" Now issue updateRow");
+ rs.updateRow();
+ //have to close the resultset because by default, resultsets are held open over commit
+ rs.close();
+ System.out.println(" Make sure that changes made it to the database correctly");
+ dumpRS(stmt.executeQuery("select * from t1"));
+
System.out.println("Positive Test30 - call updateXXX methods on only columns that correspond to a column in the table");
- dumpRS(stmt.executeQuery("select * from t1"));
- stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ dumpRS(stmt.executeQuery("select * from t1"));
+ stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT 1, 2, c1, c2 FROM t1 FOR UPDATE");
rs.next();
rs.updateInt(3,22);
@@ -2041,8 +2040,8 @@
dumpRS(stmt.executeQuery("select * from \" t 11 \""));
System.out.println("Positive Test32 - call updateXXX methods on column that is not in for update columns list");
- dumpRS(stmt.executeQuery("select * from t1"));
- stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ dumpRS(stmt.executeQuery("select * from t1"));
+ stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT c1, c2 FROM t1 FOR UPDATE of c1");
rs.next();
rs.updateInt(2,22);
@@ -2247,15 +2246,15 @@
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), (2,2)");
- stmt.executeUpdate("delete from table0WithTriggers");
- stmt.executeUpdate("insert into table0WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
- stmt.executeUpdate("delete from table1WithTriggers");
- stmt.executeUpdate("insert into table1WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
- stmt.executeUpdate("delete from table2WithTriggers");
- stmt.executeUpdate("insert into table2WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
- stmt.executeUpdate("delete from deleteTriggerInsertIntoThisTable");
- stmt.executeUpdate("delete from updateTriggerInsertIntoThisTable");
- }
+ stmt.executeUpdate("delete from table0WithTriggers");
+ stmt.executeUpdate("insert into table0WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
+ stmt.executeUpdate("delete from table1WithTriggers");
+ stmt.executeUpdate("insert into table1WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
+ stmt.executeUpdate("delete from table2WithTriggers");
+ stmt.executeUpdate("insert into table2WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
+ stmt.executeUpdate("delete from deleteTriggerInsertIntoThisTable");
+ stmt.executeUpdate("delete from updateTriggerInsertIntoThisTable");
+ }
static void setup(boolean first) throws SQLException {
Statement stmt = conn.createStatement();
@@ -2271,12 +2270,12 @@
stmt.executeUpdate("create trigger tr1 after delete on table0WithTriggers for each statement mode db2sql insert into deleteTriggerInsertIntoThisTable values (1)");
stmt.executeUpdate("create trigger tr2 after update on table0WithTriggers for each statement mode db2sql insert into updateTriggerInsertIntoThisTable values (1)");
stmt.executeUpdate("create table table1WithTriggers (c1 int, c2 bigint)");
- stmt.executeUpdate("create trigger tr3 after delete on table1WithTriggers for each statement mode db2sql delete from table1WithTriggers");
- stmt.executeUpdate("create table table2WithTriggers (c1 int, c2 bigint)");
- stmt.executeUpdate("create trigger tr4 after update on table2WithTriggers for each statement mode db2sql update table2WithTriggers set c1=1");
- stmt.executeUpdate("create table selfReferencingT1 (c1 char(2) not null, c2 char(2), constraint selfReferencingT1 primary key(c1), constraint manages1 foreign key(c2) references selfReferencingT1(c1) on delete cascade)");
- stmt.executeUpdate("create table selfReferencingT2 (c1 char(2) not null, c2 char(2), constraint selfReferencingT2 primary key(c1), constraint manages2 foreign key(c2) references selfReferencingT2(c1) on update restrict)");
-
+ stmt.executeUpdate("create trigger tr3 after delete on table1WithTriggers for each statement mode db2sql delete from table1WithTriggers");
+ stmt.executeUpdate("create table table2WithTriggers (c1 int, c2 bigint)");
+ stmt.executeUpdate("create trigger tr4 after update on table2WithTriggers for each statement mode db2sql update table2WithTriggers set c1=1");
+ stmt.executeUpdate("create table selfReferencingT1 (c1 char(2) not null, c2 char(2), constraint selfReferencingT1 primary key(c1), constraint manages1 foreign key(c2) references selfReferencingT1(c1) on delete cascade)");
+ stmt.executeUpdate("create table selfReferencingT2 (c1 char(2) not null, c2 char(2), constraint selfReferencingT2 primary key(c1), constraint manages2 foreign key(c2) references selfReferencingT2(c1) on update restrict)");
+
stmt.executeUpdate("insert into t1 values (1,'aa')");
stmt.executeUpdate("insert into t1 values (2,'bb')");
stmt.executeUpdate("insert into t1 values (3,'cc')");
@@ -2329,11 +2328,11 @@
stmt.executeUpdate("drop table deleteTriggerInsertIntoThisTable");
stmt.executeUpdate("drop table updateTriggerInsertIntoThisTable");
stmt.executeUpdate("drop table table0WithTriggers");
- stmt.executeUpdate("drop table table1WithTriggers");
- stmt.executeUpdate("drop table table2WithTriggers");
- stmt.executeUpdate("drop table selfReferencingT1");
- stmt.executeUpdate("drop table selfReferencingT2");
- conn.commit();
+ stmt.executeUpdate("drop table table1WithTriggers");
+ stmt.executeUpdate("drop table table2WithTriggers");
+ stmt.executeUpdate("drop table selfReferencingT1");
+ stmt.executeUpdate("drop table selfReferencingT2");
+ conn.commit();
stmt.close();
}