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/15 22:54:41 UTC
svn commit: r157592 [6/6] - in incubator/derby/code/trunk/java:
engine/org/apache/derby/iapi/reference/ engine/org/apache/derby/iapi/types/
engine/org/apache/derby/impl/jdbc/ engine/org/apache/derby/loc/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/master/jdk14/
testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/
testing/org/apache/derbyTesting/functionTests/tests/lang/
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=157591&r2=157592
==============================================================================
--- 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 Tue Mar 15 13:54:36 2005
@@ -33,21 +33,169 @@
import org.apache.derby.tools.ij;
import org.apache.derby.tools.JDBCDisplayUtil;
+import org.apache.derby.iapi.services.info.JVMInfo;
+
+import java.math.BigDecimal;
+import java.sql.Array;
+import java.sql.Blob;
+import java.sql.Clob;
+import java.sql.Date;
+import java.sql.Time;
+import java.sql.Timestamp;
/**
- This tests JDBC 2.0 updateable resutlset - deleteRow api
+ This tests JDBC 2.0 updateable resutlset - deleteRow, updateRow api
*/
public class updatableResultSet {
private static Connection conn;
private static DatabaseMetaData dbmt;
private static Statement stmt, stmt1;
- private static ResultSet rs;
+ private static ResultSet rs, rs1;
private static PreparedStatement pStmt = null;
private static CallableStatement callStmt = null;
+ //test all the supported SQL datatypes using updateXXX methods
+ private static String[] allSQLTypes =
+ {
+ "SMALLINT",
+ "INTEGER",
+ "BIGINT",
+ "DECIMAL(10,5)",
+ "REAL",
+ "DOUBLE",
+ "CHAR(60)",
+ "VARCHAR(60)",
+ "LONG VARCHAR",
+ "CHAR(2) FOR BIT DATA",
+ "VARCHAR(2) FOR BIT DATA",
+ "LONG VARCHAR FOR BIT DATA",
+ "CLOB(1k)",
+ "DATE",
+ "TIME",
+ "TIMESTAMP",
+ "BLOB(1k)",
+
+ };
+
+ //names for column names to test all the supported SQL datatypes using updateXXX methods
+ private static String[] ColumnNames =
+ {
+ "SMALLINTCOL",
+ "INTEGERCOL",
+ "BIGINTCOL",
+ "DECIMALCOL",
+ "REALCOL",
+ "DOUBLECOL",
+ "CHARCOL",
+ "VARCHARCOL",
+ "LONGVARCHARCOL",
+ "CHARFORBITCOL",
+ "VARCHARFORBITCOL",
+ "LVARCHARFORBITCOL",
+ "CLOBCOL",
+ "DATECOL",
+ "TIMECOL",
+ "TIMESTAMPCOL",
+ "BLOBCOL",
+
+ };
+
+ //data to test all the supported SQL datatypes using updateXXX methods
+ private static String[][]SQLData =
+ {
+ {"11","22"}, // SMALLINT
+ {"111","1111"}, // INTEGER
+ {"22","222"}, // BIGINT
+ {"3.3","3.33"}, // DECIMAL(10,5)
+ {"4.4","4.44"}, // REAL,
+ {"5.5","5.55"}, // DOUBLE
+ {"'1992-01-06'","'1992'"}, // CHAR(60)
+ {"'1992-01-07'","'1992'"}, //VARCHAR(60)",
+ {"'1992-01-08'","'1992'"}, // LONG VARCHAR
+ {"X'10'","X'10aa'"}, // CHAR(2) FOR BIT DATA
+ {"X'10'","X'10bb'"}, // VARCHAR(2) FOR BIT DATA
+ {"X'10'","X'10cc'"}, //LONG VARCHAR FOR BIT DATA
+ {"'13'","'14'"}, //CLOB(1k)
+ {"'2000-01-01'","'2000-01-01'"}, // DATE
+ {"'15:30:20'","'15:30:20'"}, // TIME
+ {"'2000-01-01 15:30:20'","'2000-01-01 15:30:20'"}, // TIMESTAMP
+ {"X'1020'","X'10203040'"} // BLOB
+ };
+
+ //used for printing useful messages about the test
+ private static String[] allUpdateXXXNames =
+ {
+ "updateShort",
+ "updateInt",
+ "updateLong",
+ "updateBigDecimal",
+ "updateFloat",
+ "updateDouble",
+ "updateString",
+ "updateAsciiStream",
+ "updateCharacterStream",
+ "updateByte",
+ "updateBytes",
+ "updateBinaryStream",
+ "updateClob",
+ "updateDate",
+ "updateTime",
+ "updateTimestamp",
+ "updateBlob",
+ "updateBoolean",
+ "updateNull",
+ "updateArray",
+ "updateRef"
+
+ };
+
+
+ //I have constructed following table based on if combination of datatype and updateXXX method would work or not.
+ public static final String[][] updateXXXRulesTable = {
+
+ // Types. u u u u u u u u u u u u u u u u u u u u u
+ // p p p p p p p p p p p p p p p p p p p p p
+ // d d d d d d d d d d d d d d d d d d d d d
+ // a a a a a a a a a a a a a a a a a a a a a
+ // t t t t t t t t t t t t t t t t t t t t t
+ // e e e e e e e e e e e e e e e e e e e e e
+ // S I L B F D S A C B B B C D T T B B N A R
+ // h n o i l o t s h y y i l a i i l o u r e
+ // o t n g o u r c a t t n o t m m o o l r f
+ // r g D a b i i r e e a b e e e b l l a
+ // t e t l n i c s r s e y
+ // c e g S t y t a
+ // i t e S a n
+ // m r r t m
+ // a e S r p
+ // l a t e
+ // m r a
+ // e m
+ // a
+ // m
+/* 0 SMALLINT */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
+/* 1 INTEGER */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
+/* 2 BIGINT */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
+/* 3 DECIMAL */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
+/* 4 REAL */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
+/* 5 DOUBLE */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
+/* 6 CHAR */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
+/* 7 VARCHAR */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
+/* 8 LONGVARCHAR */ { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
+/* 9 CHAR FOR BIT */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
+/* 10 VARCH. BIT */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
+/* 11 LONGVAR. BIT */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
+/* 12 CLOB */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
+/* 13 DATE */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
+/* 14 TIME */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
+/* 15 TIMESTAMP */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
+/* 16 BLOB */ { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "PASS", "ERROR", "ERROR" },
+
+ };
+
public static void main(String[] args) {
- System.out.println("Start testing delete using JDBC2.0 updateable resultset apis");
+ System.out.println("Start testing delete and update using JDBC2.0 updateable resultset apis");
try {
// use the ij utility to read the property file and
@@ -67,15 +215,15 @@
warnings = warnings.getNextWarning();
}
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");
- rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
+ 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");
+ rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
rs.next();
try {
rs.deleteRow();
@@ -87,6 +235,16 @@
else
dumpSQLExceptions(e);
}
+ try {
+ rs.updateRow();
+ System.out.println("FAIL!!! updateRow should have failed because Derby does not yet support scroll insensitive updatable resultsets");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("XJ083"))
+ System.out.println("Got expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
rs.next();
//have to close the resultset because by default, resultsets are held open over commit
rs.close();
@@ -100,9 +258,9 @@
}
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("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 {
@@ -115,19 +273,9 @@
else
dumpSQLExceptions(e);
}
- rs.next();//make sure rs.next() does not fail because of earlier deleteRow
- //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 on it");
- stmt = conn.createStatement();//the default is a read only forward only resultset
- rs = stmt.executeQuery("select * from t1");
- 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.");
try {
- rs.deleteRow();
- System.out.println("FAIL!!! deleteRow should have failed because this is a read only resultset");
+ rs.updateRow();
+ System.out.println("FAIL!!! updateRow should have failed because Derby does not yet support scroll sensitive updatable resultsets");
}
catch (SQLException e) {
if (e.getSQLState().equals("XJ083"))
@@ -135,15 +283,16 @@
else
dumpSQLExceptions(e);
}
+ rs.next();
//have to close the resultset because by default, resultsets are held open over commit
rs.close();
- System.out.println("---Negative Test4 - request a read only resultset and send a sql with FOR UPDATE clause and attempt deleteRow on it");
+ 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
- rs = stmt.executeQuery("select * from t1 FOR UPDATE");
+ rs = stmt.executeQuery("select * from t1");
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.");
+ System.out.println("Now attempting to send a deleteRow on a read only resultset.");
try {
rs.deleteRow();
System.out.println("FAIL!!! deleteRow should have failed because this is a read only resultset");
@@ -154,25 +303,10 @@
else
dumpSQLExceptions(e);
}
- //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));
- 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.");
+ System.out.println("Now attempting to send an updateRow on a read only resultset.");
try {
- rs.deleteRow();
- System.out.println("FAIL!!! deleteRow should have failed on sql with no FOR UPDATE clause");
+ rs.updateRow();
+ System.out.println("FAIL!!! updateRow should have failed because this is a read only resultset");
}
catch (SQLException e) {
if (e.getSQLState().equals("XJ083"))
@@ -183,19 +317,86 @@
//have to close the resultset because by default, resultsets are held open over commit
rs.close();
+ 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) {
+ if (e.getSQLState().equals("XJ083"))
+ System.out.println("Got expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ 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) {
+ if (e.getSQLState().equals("XJ083"))
+ System.out.println("Got expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ //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));
+ 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) {
+ if (e.getSQLState().equals("XJ083"))
+ System.out.println("Got expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ 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) {
+ if (e.getSQLState() != null && e.getSQLState().equals("XJ083"))
+ System.out.println("Got expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ //have to close the resultset because by default, resultsets are held open over commit
+ rs.close();
+
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");
@@ -206,14 +407,25 @@
else
dumpSQLExceptions(e);
}
- //have to close the resultset because by default, resultsets are held open over commit
+ 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");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("XJ083"))
+ System.out.println("Got expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ //have to close the resultset because by default, resultsets are held open over commit
rs.close();
- System.out.println("---Negative Test7 - attempt to deleteRow on updatable resultset when the resultset is not positioned on a row");
+ System.out.println("---Negative Test7 - attempt to deleteRow & updateRow on updatable resultset when the resultset is not positioned on a row");
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");
@@ -224,7 +436,18 @@
else
dumpSQLExceptions(e);
}
- while (rs.next());//read all the rows from the resultset and position after the last row
+ 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");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("24000"))
+ System.out.println("Got expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ 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();
@@ -236,9 +459,20 @@
else
dumpSQLExceptions(e);
}
+ System.out.println("ResultSet is positioned after the last row. attempt to updateRow at this point should fail!");
+ try {
+ rs.updateRow();
+ System.out.println("FAIL!!! updateRow should have failed because resultset is after the last row");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("24000"))
+ System.out.println("Got expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
rs.close();
- System.out.println("---Negative Test8 - attempt deleteRow on updatable resultset after closing the resultset");
+ System.out.println("---Negative Test8 - attempt deleteRow & updateRow on updatable resultset after closing the resultset");
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));
@@ -254,6 +488,16 @@
else
dumpSQLExceptions(e);
}
+ try {
+ rs.updateRow();
+ System.out.println("FAIL!!! updateRow should have failed because resultset is closed");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("XCL16"))
+ System.out.println("Got expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
System.out.println("---Negative Test9 - try updatable resultset on system table");
try {
@@ -266,7 +510,7 @@
else
dumpSQLExceptions(e);
}
-
+
System.out.println("---Negative Test10 - try updatable resultset on a view");
try {
rs = stmt.executeQuery("SELECT * FROM v1 FOR UPDATE");
@@ -298,35 +542,74 @@
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");
}
catch (SQLException e) {
- if (e.getSQLState().equals("X0X95")) {
+ if (e.getSQLState().equals("X0X95")){
System.out.println("expected exception " + e.getMessage());
} else
dumpSQLExceptions(e);
}
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) {
+ if (e.getSQLState().equals("XCL16"))
+ System.out.println("expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+
try {
+ rs.deleteRow();
+ System.out.println("FAIL!!! resultset should have been closed at this point and deleteRow should have failed");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("XCL16"))
+ System.out.println("expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+
+ 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");
+ rs.next();
try {
rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should have failed because it will cause foreign key constraint failure");
}
catch (SQLException e) {
- if (e.getSQLState().equals("XCL16"))
+ if (e.getSQLState().equals("23503"))
System.out.println("expected exception " + e.getMessage());
else
dumpSQLExceptions(e);
}
-
- System.out.println("---Negative Test13 - foreign key constraint failure will cause deleteRow to fail");
+ 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) {
+ if (e.getSQLState().equals("XCL16"))
+ System.out.println("expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+
+ 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 1, 2 FROM tableWithPrimaryKey FOR UPDATE");
+ rs = stmt.executeQuery("SELECT c1, c2 FROM tableWithPrimaryKey FOR UPDATE");
rs.next();
+ rs.updateInt(1,11);
+ rs.updateInt(2,22);
try {
- rs.deleteRow();
- System.out.println("FAIL!!! deleteRow should have failed because it will cause foreign key constraint failure");
+ rs.updateRow();
+ System.out.println("FAIL!!! updateRow should have failed because it will cause foreign key constraint failure");
}
catch (SQLException e) {
if (e.getSQLState().equals("23503"))
@@ -335,23 +618,54 @@
dumpSQLExceptions(e);
}
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) {
+ if (e.getSQLState().equals("XCL16"))
+ System.out.println("expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+
+ 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);
+ rs = stmt.executeQuery("SELECT 1, 2 FROM tableWithPrimaryKey FOR UPDATE");
+ rs.next();
try {
- rs.next();
- System.out.println("FAIL!!! next should have failed because foreign key constraint failure resulted in a runtime rollback");
+ rs.updateInt(1,22);
+ System.out.println("FAIL!!! updateInt should have failed because it is trying to update a column that does not correspond to column in base table");
}
catch (SQLException e) {
- if (e.getSQLState().equals("XCL16"))
+ if (e.getSQLState().equals("XJ084"))
System.out.println("expected exception " + e.getMessage());
else
dumpSQLExceptions(e);
}
- System.out.println("---Positive Test1 - request updatable resultset for forward only type resultset");
+ System.out.println("---Negative Test16 - Call updateXXX method on out of the range column");
+ 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");
+ try {
+ rs.updateInt(3,22);
+ System.out.println("FAIL!!! updateInt should have failed because there are only 2 columns in the select list");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("XCL14"))
+ System.out.println("expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+
+ System.out.println("---Positive Test1a - request updatable resultset for forward only type resultset");
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
warnings = conn.getWarnings();
while (warnings != null)
{
- System.out.println("warnings = " + warnings);
+ System.out.println("Unexpected warnings = " + warnings);
warnings = warnings.getNextWarning();
}
System.out.println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
@@ -364,8 +678,19 @@
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) {
+ if (e.getSQLState().equals("24000"))
+ System.out.println("Got expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
try {
- System.out.println("column 1 on this deleted row is " + rs.getInt(1));
+ rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should have failed because ResultSet is not positioned on a row");
}
catch (SQLException e) {
if (e.getSQLState().equals("24000"))
@@ -373,10 +698,44 @@
else
dumpSQLExceptions(e);
}
- System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
+ 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();
+ //have to close the resultset because by default, resultsets are held open over commit
+ rs.close();
+
+ System.out.println("---Positive Test1b - request updatable resultset for forward only type resultset");
+ reloadData();
+ stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ warnings = conn.getWarnings();
+ while (warnings != null)
+ {
+ System.out.println("Unexpected warnings = " + warnings);
+ warnings = warnings.getNextWarning();
+ }
+ rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
+ rs.next();
+ System.out.println("column 1 on this row before updateInt is " + rs.getInt(1));
+ rs.updateInt(1,234);
+ System.out.println("column 1 on this row after updateInt is " + rs.getInt(1));
+ System.out.println("column 2 on this row before updateString is " + rs.getString(2));
+ 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) {
+ if (e.getSQLState().equals("24000"))
+ System.out.println("Got expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ System.out.println("calling updateRow 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");
+ rs.updateRow();
+ System.out.println("FAIL!!! updateRow should have failed because ResultSet is not positioned on a row");
}
catch (SQLException e) {
if (e.getSQLState().equals("24000"))
@@ -384,17 +743,18 @@
else
dumpSQLExceptions(e);
}
- System.out.println("Position the ResultSet with next()");
+ 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();
+ System.out.println("Should be able to updateRow() on the current row now");
+ 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));
@@ -402,9 +762,9 @@
//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 Test3 - use prepared statement with concur updatable status");
+ System.out.println("---Positive Test3a - use prepared statement with concur updatable status to test deleteRow");
reloadData();
pStmt = conn.prepareStatement("select * from t1 where c1>? for update", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
System.out.println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
@@ -416,8 +776,19 @@
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) {
+ if (e.getSQLState().equals("24000"))
+ System.out.println("Got expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
try {
- System.out.println("column 1 on this deleted row is " + rs.getInt(1));
+ rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should have failed because it can't be called more than once on the same row");
}
catch (SQLException e) {
if (e.getSQLState().equals("24000"))
@@ -425,10 +796,40 @@
else
dumpSQLExceptions(e);
}
- System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
+ 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();
+ //have to close the resultset because by default, resultsets are held open over commit
+ rs.close();
+
+ System.out.println("---Positive Test3b - use prepared statement with concur updatable status to test updateXXX");
+ reloadData();
+ pStmt = conn.prepareStatement("select * from t1 where c1>? for update", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ System.out.println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
+ System.out.println("got TYPE_FORWARD_ONLY? " + (pStmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY));
+ System.out.println("got CONCUR_UPDATABLE? " + (pStmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE));
+ pStmt.setInt(1,0);
+ rs = pStmt.executeQuery();
+ rs.next();
+ System.out.println("column 1 on this row is " + rs.getInt(1));
+ rs.updateInt(1,5);
+ 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) {
+ if (e.getSQLState().equals("24000"))
+ System.out.println("Got expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ System.out.println("calling updateRow/updateXXX 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");
+ rs.updateInt(1,0);
+ System.out.println("FAIL!!! updateXXX should have failed because resultset is not positioned on a row");
}
catch (SQLException e) {
if (e.getSQLState().equals("24000"))
@@ -436,10 +837,30 @@
else
dumpSQLExceptions(e);
}
- System.out.println("Position the ResultSet with next()");
+ try {
+ rs.updateRow();
+ System.out.println("FAIL!!! updateRow should have failed because resultset is not positioned on a row");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("24000"))
+ System.out.println("Got expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ try {
+ rs.cancelRowUpdates();
+ System.out.println("FAIL!!! cancelRowUpdates should have failed because resultset is not positioned on a row");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("24000"))
+ System.out.println("Got expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ 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();
+ System.out.println("Should be able to cancelRowUpdates() on the current row now");
+ rs.cancelRowUpdates();
//have to close the resultset because by default, resultsets are held open over commit
rs.close();
@@ -454,16 +875,16 @@
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) {
- if (e.getSQLState().equals("24000"))
- System.out.println("Got expected exception " + e.getMessage());
- else
- dumpSQLExceptions(e);
- }
- System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
+ try {
+ System.out.println("column 1 on this deleted row is " + rs.getInt(1));
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("24000"))
+ System.out.println("Got expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ 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");
@@ -477,7 +898,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();
@@ -489,27 +910,48 @@
System.out.println("column 1 on this row is " + rs.getInt(1));
System.out.println("now try to delete row when primary key is not selected for that row");
rs.deleteRow();
+ rs.next();
+ rs.updateLong(1,123);
+ rs.updateRow();
//have to close the resultset because by default, resultsets are held open over commit
rs.close();
- System.out.println("---Positive Test6 - 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("---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");
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 Test7 - delete using updatable resultset api from a temporary table");
+ 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");
+ 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);
+ 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("---Positive Test7a - delete using updatable resultset api from a temporary table");
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
stmt.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
stmt.executeUpdate("insert into SESSION.t2 values(21, 1)");
@@ -526,7 +968,26 @@
rs.close();
stmt.executeUpdate("DROP TABLE SESSION.t2");
- System.out.println("---Positive Test8 - change the name of the resultset and see if deleteRow still works");
+ System.out.println("---Positive Test7b - update using updatable resultset api from a temporary table");
+ stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ stmt.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged");
+ stmt.executeUpdate("insert into SESSION.t3 values(21, 1)");
+ stmt.executeUpdate("insert into SESSION.t3 values(22, 1)");
+ System.out.println("following rows in temp table before deleteRow");
+ dumpRS(stmt.executeQuery("select * from SESSION.t3"));
+ rs = stmt.executeQuery("select c31 from session.t3 for update");
+ rs.next();
+ rs.updateLong(1,123);
+ rs.updateRow();
+ rs.next();
+ rs.updateLong(1,123);
+ rs.updateRow();
+ System.out.println("As expected, updated rows in temp table after updateRow");
+ dumpRS(stmt.executeQuery("select * from SESSION.t3"));
+ rs.close();
+ stmt.executeUpdate("DROP TABLE SESSION.t3");
+
+ System.out.println("---Positive Test8a - change the name of the resultset and see if deleteRow still works");
reloadData();
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
System.out.println("change the cursor name(case sensitive name) with setCursorName and then try to deleteRow");
@@ -540,8 +1001,21 @@
rs.deleteRow();
rs.close();
- System.out.println("---Positive Test9 - using correlation name for the table in the select sql is not a problem");
+ System.out.println("---Positive Test8b - change the name of the resultset and see if updateRow still works");
reloadData();
+ System.out.println("change the cursor name one more time with setCursorName and then try to updateRow");
+ stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ System.out.println("change the cursor name(case sensitive name) with setCursorName and then try to updateRow");
+ stmt.setCursorName("CURSORNOUPDATe");//notice this name is case sensitive
+ rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
+ rs.next();
+ rs.updateLong(1,123);
+ stmt.setCursorName("CURSORNOUPDATE1");
+ rs.updateRow();
+ rs.close();
+
+ System.out.println("---Positive Test9a - using correlation name for the table in the select sql is not a problem");
+ reloadData();
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT 1, 2 FROM t1 abcde FOR UPDATE of c1");
rs.next();
@@ -549,6 +1023,29 @@
System.out.println("now try to deleteRow");
rs.deleteRow();
rs.close();
+
+ System.out.println("---Positive Test9b - using correlation name for column names is not allowed with updateXXX");
+ reloadData();
+ stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ System.out.println("Table t1 has following rows");
+ dumpRS(stmt.executeQuery("select * from t1"));
+ rs = stmt.executeQuery("SELECT c1 as col1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
+ rs.next();
+ System.out.println("column 1 on this row is " + rs.getInt(1));
+ try {
+ System.out.println("attempt to send updateXXX on correlation name column will fail");
+ rs.updateShort(1, (new Integer(123)).shortValue());
+ System.out.println("FAIL!!! updateXXX should have failed");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("XJ084"))
+ System.out.println("Got expected exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ rs.close();
+ System.out.println("Table t1 after updateRow has following rows");
+ dumpRS(stmt.executeQuery("select * from t1"));
System.out.println("---Positive Test10 - 2 updatable resultsets going against the same table, will they conflict?");
conn.setAutoCommit(false);
@@ -557,14 +1054,14 @@
stmt1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
rs.next();
- ResultSet rs1 = stmt1.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
+ rs1 = stmt1.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
rs1.next();
System.out.println("delete using first resultset");
rs.deleteRow();
try {
System.out.println("attempt to send deleteRow on the same row through a different resultset should throw an exception");
rs1.deleteRow();
- System.out.println("FAIL!!! delete using second resultset succedded? " + rs1.rowDeleted());
+ System.out.println("FAIL!!! delete using second resultset succedded? ");
}
catch (SQLException e) {
if (e.getSQLState().equals("XCL08"))
@@ -573,8 +1070,8 @@
dumpSQLExceptions(e);
}
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);
@@ -591,12 +1088,12 @@
rs.close();
stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
- System.out.println("---Positive Test12 - make sure delete trigger gets fired when deleteRow is issued");
+ System.out.println("---Positive Test12a - 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"));
- rs = stmt.executeQuery("SELECT * FROM tableWithDeleteTriggers FOR UPDATE");
+ 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");
@@ -607,121 +1104,1088 @@
//have to close the resultset because by default, resultsets are held open over commit
rs.close();
- System.out.println("---Positive Test13 - Another test case for delete trigger");
+ System.out.println("---Positive Test12b - make sure update trigger gets fired when updateRow is issued");
+ reloadData();
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
- rs = stmt.executeQuery("SELECT * FROM anotherTableWithDeleteTriggers FOR UPDATE");
+ System.out.println("Verify that before update trigger got fired, row count is 0 in updateTriggerInsertIntoThisTable");
+ dumpRS(stmt.executeQuery("select count(*) from updateTriggerInsertIntoThisTable"));
+ 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("this delete row will fire the delete trigger which will delete all the rows from the table and from the resultset");
- rs.deleteRow();
+ 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"));
+ //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) {
+ 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 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) {
+ if (e.getSQLState().equals("24000")) {
+ System.out.println("expected exception " + e.getMessage());
+ } else
+ dumpSQLExceptions(e);
+ }
+ 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) {
+ 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 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) {
+ if (e.getSQLState().equals("23503")) {
+ System.out.println("expected exception " + e.getMessage());
+ } else
+ dumpSQLExceptions(e);
+ }
+ //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) {
+ if (e.getSQLState().equals("X0X95")) {
+ System.out.println("expected exception " + e.getMessage());
+ } else
+ dumpSQLExceptions(e);
+ }
+ 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) {
+ if (e.getSQLState().equals("24000")) {
+ System.out.println("expected exception " + e.getMessage());
+ } else
+ dumpSQLExceptions(e);
+ }
+ 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) {
+ if (e.getSQLState().equals("XJ083")) {
+ System.out.println("expected exception " + e.getMessage());
+ } else
+ dumpSQLExceptions(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 = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable");
+ rs.next();
+ rs1 = stmt1.executeQuery("SELECT * FROM AllDataTypesNewValuesData");
+ rs1.next();
+ if (indexOrName == 1) //test by passing column position
+ 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 {
+ if (updateXXXName == 1) {//update column with updateShort methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateShort(1, rs1.getShort(updateXXXName));
+ else //test by passing column name
+ rs.updateShort(ColumnNames[0], rs1.getShort(updateXXXName));
+ } else if (updateXXXName == 2){ //update column with updateInt methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateInt(1, rs1.getInt(updateXXXName));
+ else //test by passing column name
+ rs.updateInt(ColumnNames[0], rs1.getInt(updateXXXName));
+ } else if (updateXXXName == 3){ //update column with updateLong methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateLong(1, rs1.getLong(updateXXXName));
+ else //test by passing column name
+ rs.updateLong(ColumnNames[0], rs1.getLong(updateXXXName));
+ } else if (updateXXXName == 4){ //update column with updateBigDecimal methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateBigDecimal(1, rs1.getBigDecimal(updateXXXName));
+ else //test by passing column name
+ rs.updateBigDecimal(ColumnNames[0], rs1.getBigDecimal(updateXXXName));
+ } else if (updateXXXName == 5){ //update column with updateFloat methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateFloat(1, rs1.getFloat(updateXXXName));
+ else //test by passing column name
+ rs.updateFloat(ColumnNames[0], rs1.getFloat(updateXXXName));
+ } else if (updateXXXName == 6){ //update column with updateDouble methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateDouble(1, rs1.getDouble(updateXXXName));
+ else //test by passing column name
+ rs.updateDouble(ColumnNames[0], rs1.getDouble(updateXXXName));
+ } else if (updateXXXName == 7){ //update column with updateString methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateString(1, rs1.getString(updateXXXName));
+ else //test by passing column name
+ rs.updateString(ColumnNames[0], rs1.getString(updateXXXName));
+ } else if (updateXXXName == 8){ //update column with updateAsciiStream methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateAsciiStream(1,rs1.getAsciiStream(updateXXXName), 4);
+ else //test by passing column name
+ rs.updateAsciiStream(ColumnNames[0],rs1.getAsciiStream(updateXXXName), 4);
+ } else if (updateXXXName == 9){ //update column with updateCharacterStream methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateCharacterStream(1,rs1.getCharacterStream(updateXXXName), 4);
+ else //test by passing column name
+ rs.updateCharacterStream(ColumnNames[0],rs1.getCharacterStream(updateXXXName), 4);
+ } else if (updateXXXName == 10){ //update column with updateByte methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateByte(1,rs1.getByte(1));
+ else //test by passing column name
+ rs.updateByte(ColumnNames[0],rs1.getByte(1));
+ } else if (updateXXXName == 11){ //update column with updateBytes methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateBytes(1,rs1.getBytes(updateXXXName));
+ else //test by passing column name
+ rs.updateBytes(ColumnNames[0],rs1.getBytes(updateXXXName));
+ } else if (updateXXXName == 12){ //update column with updateBinaryStream methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateBinaryStream(1,rs1.getBinaryStream(updateXXXName), 2);
+ else //test by passing column name
+ rs.updateBinaryStream(ColumnNames[0],rs1.getBinaryStream(updateXXXName), 2);
+ } else if (updateXXXName == 13){ //update column with updateClob methods
+ if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
+ continue;
+ if (indexOrName == 1) //test by passing column position
+ rs.updateClob(1,rs1.getClob(updateXXXName));
+ else //test by passing column name
+ rs.updateClob(ColumnNames[0],rs1.getClob(updateXXXName));
+ } else if (updateXXXName == 14){ //update column with updateDate methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateDate(1,rs1.getDate(updateXXXName));
+ else //test by passing column name
+ rs.updateDate(ColumnNames[0],rs1.getDate(updateXXXName));
+ } else if (updateXXXName == 15){ //update column with updateTime methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateTime(1,rs1.getTime(updateXXXName));
+ else //test by passing column name
+ rs.updateTime(ColumnNames[0],rs1.getTime(updateXXXName));
+ } else if (updateXXXName == 16){ //update column with updateTimestamp methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateTimestamp(1,rs1.getTimestamp(updateXXXName));
+ else //test by passing column name
+ rs.updateTimestamp(ColumnNames[0],rs1.getTimestamp(updateXXXName));
+ } else if (updateXXXName == 17){ //update column with updateBlob methods
+ if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
+ continue;
+ if (indexOrName == 1) //test by passing column position
+ rs.updateBlob(1,rs1.getBlob(updateXXXName));
+ else //test by passing column name
+ rs.updateBlob(ColumnNames[0],rs1.getBlob(updateXXXName));
+ } else if (updateXXXName == 18){ //update column with getBoolean methods
+ //use SHORT sql type column's value for testing boolean since Derby don't support boolean datatype
+ //Since Derby does not support Boolean datatype, this method is going to fail with the syntax error
+ if (indexOrName == 1) //test by passing column position
+ rs.updateBoolean(1, rs1.getBoolean(1));
+ else //test by passing column name
+ rs.updateBoolean(ColumnNames[0], rs1.getBoolean(1));
+ } else if (updateXXXName == 19){ //update column with updateNull methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateNull(1);
+ else //test by passing column name
+ rs.updateNull(ColumnNames[0]);
+ } else if (updateXXXName == 20){ //update column with updateArray methods - should get not implemented exception
+ if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
+ continue;
+ if (indexOrName == 1) //test by passing column position
+ rs.updateArray(1, null);
+ else //test by passing column name
+ rs.updateArray(ColumnNames[0], null);
+ } else if (updateXXXName == 21){ //update column with updateRef methods - should get not implemented exception
+ if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
+ continue;
+ if (indexOrName == 1) //test by passing column position
+ rs.updateRef(1, null);
+ else //test by passing column name
+ 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) {
+ 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++) {
+ checkAgainstColumn = updateXXXName;
+ System.out.println(" Testing " + allUpdateXXXNames[updateXXXName-1] + " on SQL type " + allSQLTypes[sqlType-1]);
+ for (int indexOrName = 1; indexOrName <= 2; indexOrName++) {
+ if (indexOrName == 1) //test by passing column position
+ 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]);
+ rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable FOR UPDATE");
+ rs.next();
+ rs1 = stmt1.executeQuery("SELECT * FROM AllDataTypesNewValuesData");
+ rs1.next();
+ try {
+ if (updateXXXName == 1) {//update column with updateShort methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateShort(sqlType, rs1.getShort(updateXXXName));
+ else //test by passing column name
+ rs.updateShort(ColumnNames[sqlType-1], rs1.getShort(updateXXXName));
+ } else if (updateXXXName == 2){ //update column with updateInt methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateInt(sqlType, rs1.getInt(updateXXXName));
+ else //test by passing column name
+ rs.updateInt(ColumnNames[sqlType-1], rs1.getInt(updateXXXName));
+ } else if (updateXXXName == 3){ //update column with updateLong methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateLong(sqlType, rs1.getLong(updateXXXName));
+ else //test by passing column name
+ rs.updateLong(ColumnNames[sqlType-1], rs1.getLong(updateXXXName));
+ } else if (updateXXXName == 4){ //update column with updateBigDecimal methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateBigDecimal(sqlType, rs1.getBigDecimal(updateXXXName));
+ else //test by passing column name
+ rs.updateBigDecimal(ColumnNames[sqlType-1], rs1.getBigDecimal(updateXXXName));
+ } else if (updateXXXName == 5){ //update column with updateFloat methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateFloat(sqlType, rs1.getFloat(updateXXXName));
+ else //test by passing column name
+ rs.updateFloat(ColumnNames[sqlType-1], rs1.getFloat(updateXXXName));
+ } else if (updateXXXName == 6){ //update column with updateDouble methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateDouble(sqlType, rs1.getDouble(updateXXXName));
+ else //test by passing column name
+ rs.updateDouble(ColumnNames[sqlType-1], rs1.getDouble(updateXXXName));
+ } else if (updateXXXName == 7){ //update column with updateString methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateString(sqlType, rs1.getString(updateXXXName));
+ else //test by passing column name
+ rs.updateString(ColumnNames[sqlType-1], rs1.getString(updateXXXName));
+ } else if (updateXXXName == 8){ //update column with updateAsciiStream methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateAsciiStream(sqlType,rs1.getAsciiStream(updateXXXName), 4);
+ else //test by passing column name
+ rs.updateAsciiStream(ColumnNames[sqlType-1],rs1.getAsciiStream(updateXXXName), 4);
+ } else if (updateXXXName == 9){ //update column with updateCharacterStream methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateCharacterStream(sqlType,rs1.getCharacterStream(updateXXXName), 4);
+ else //test by passing column name
+ rs.updateCharacterStream(ColumnNames[sqlType-1],rs1.getCharacterStream(updateXXXName), 4);
+ } else if (updateXXXName == 10){ //update column with updateByte methods
+ checkAgainstColumn = 1;
+ if (indexOrName == 1) //test by passing column position
+ rs.updateByte(sqlType,rs1.getByte(checkAgainstColumn));
+ else //test by passing column name
+ rs.updateByte(ColumnNames[sqlType-1],rs1.getByte(checkAgainstColumn));
+ } else if (updateXXXName == 11){ //update column with updateBytes methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateBytes(sqlType,rs1.getBytes(updateXXXName));
+ else //test by passing column name
+ rs.updateBytes(ColumnNames[sqlType-1],rs1.getBytes(updateXXXName));
+ } else if (updateXXXName == 12){ //update column with updateBinaryStream methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateBinaryStream(sqlType,rs1.getBinaryStream(updateXXXName), 2);
+ else //test by passing column name
+ rs.updateBinaryStream(ColumnNames[sqlType-1],rs1.getBinaryStream(updateXXXName), 2);
+ } else if (updateXXXName == 13){ //update column with updateClob methods
+ if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
+ continue;
+ if (indexOrName == 1) //test by passing column position
+ rs.updateClob(sqlType,rs1.getClob(updateXXXName));
+ else //test by passing column name
+ rs.updateClob(ColumnNames[sqlType-1],rs1.getClob(updateXXXName));
+ } else if (updateXXXName == 14){ //update column with updateDate methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateDate(sqlType,rs1.getDate(updateXXXName));
+ else //test by passing column name
+ rs.updateDate(ColumnNames[sqlType-1],rs1.getDate(updateXXXName));
+ } else if (updateXXXName == 15){ //update column with updateTime methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateTime(sqlType,rs1.getTime(updateXXXName));
+ else //test by passing column name
+ rs.updateTime(ColumnNames[sqlType-1],rs1.getTime(updateXXXName));
+ } else if (updateXXXName == 16){ //update column with updateTimestamp methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateTimestamp(sqlType,rs1.getTimestamp(updateXXXName));
+ else //test by passing column name
+ rs.updateTimestamp(ColumnNames[sqlType-1],rs1.getTimestamp(updateXXXName));
+ } else if (updateXXXName == 17){ //update column with updateBlob methods
+ if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
+ continue;
+ if (indexOrName == 1) //test by passing column position
+ rs.updateBlob(sqlType,rs1.getBlob(updateXXXName));
+ else //test by passing column name
+ rs.updateBlob(ColumnNames[sqlType-1],rs1.getBlob(updateXXXName));
+ } else if (updateXXXName == 18){ //update column with getBoolean methods
+ //use SHORT sql type column's value for testing boolean since Derby don't support boolean datatype
+ //Since Derby does not support Boolean datatype, this method is going to fail with the syntax error
+ if (indexOrName == 1) //test by passing column position
+ rs.updateBoolean(sqlType, rs1.getBoolean(1));
+ else //test by passing column name
+ rs.updateBoolean(ColumnNames[sqlType-1], rs1.getBoolean(1));
+ } else if (updateXXXName == 19){ //update column with updateNull methods
+ if (indexOrName == 1) //test by passing column position
+ rs.updateNull(sqlType);
+ else //test by passing column name
+ rs.updateNull(ColumnNames[sqlType-1]);
+ } else if (updateXXXName == 20){ //update column with updateArray methods - should get not implemented exception
+ if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
+ continue;
+ if (indexOrName == 1) //test by passing column position
+ rs.updateArray(sqlType, null);
+ else //test by passing column name
+ rs.updateArray(ColumnNames[sqlType-1], null);
+ } else if (updateXXXName == 21){ //update column with updateRef methods - should get not implemented exception
+ if (JVMInfo.JDK_ID == 2) //Don't test this method because running JDK1.3 and this jvm does not support the method
+ continue;
+ if (indexOrName == 1) //test by passing column position
+ rs.updateRef(sqlType, null);
+ else //test by passing column name
+ rs.updateRef(ColumnNames[sqlType-1], null);
+ }
+ rs.updateRow();
+ if (updateXXXRulesTable[sqlType-1][updateXXXName-1].equals("ERROR")) {
+ System.out.println("FAILURE : We shouldn't reach here. The test should have failed earlier on updateXXX or updateRow call");
+ return;
+ }
+ if (verifyData(sqlType,checkAgainstColumn, "AllDataTypesNewValuesData") == false)
+ {
+ 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());
+ else {
+ if ((sqlType == 14 || sqlType == 15 || sqlType == 16) && //we are dealing with DATE/TIME/TIMESTAMP column types
+ checkAgainstColumn == 7) //we are dealing with updateString. The failure is because string does not represent a valid datetime value
+ System.out.println(" Got expected exception : " + e.getMessage());
+ else {
+ System.out.println(" Got UNexpected exception : " + e.getMessage());
+ return;
+ }
+ }
+ }
+ }
+ rs.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++) {
+ for (int indexOrName = 1; indexOrName <= 2; indexOrName++) {
+ if (indexOrName == 1) //test by passing column position
+ displayString = " updateObject with column position &";
+ else
+ displayString = " updateObject with column name &";
+ rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable FOR UPDATE");
+ rs.next();
+ rs1 = stmt1.executeQuery("SELECT * FROM AllDataTypesNewValuesData");
+ rs1.next();
+ try {
+ if (updateXXXName == 1){ //updateObject using Short object
+ System.out.println(displayString + " Short object as parameters");
+ if (indexOrName == 1) //test by passing column position
+ rs.updateObject(sqlType, new Short(rs1.getShort(updateXXXName)));
+ else //test by passing column name
+ rs.updateObject(ColumnNames[sqlType-1], new Short(rs1.getShort(updateXXXName)));
+ } else if (updateXXXName == 2){ //updateObject using Integer object
+ System.out.println(displayString + " Integer object as parameters");
+ if (indexOrName == 1) //test by passing column position
+ rs.updateObject(sqlType, new Integer(rs1.getInt(updateXXXName)));
+ else //test by passing column name
+ rs.updateObject(ColumnNames[sqlType-1], new Integer(rs1.getInt(updateXXXName)));
+ } else if (updateXXXName == 3){ //updateObject using Long object
+ System.out.println(displayString + " Long object as parameters");
+ if (indexOrName == 1) //test by passing column position
+ rs.updateObject(sqlType, new Long(rs1.getLong(updateXXXName)));
+ else //test by passing column name
+ rs.updateObject(ColumnNames[sqlType-1], new Long(rs1.getLong(updateXXXName)));
+ } else if (updateXXXName == 4){ //updateObject using BigDecimal object
[... 795 lines stripped ...]