updatableresultset.java

derby database source code.good for you.

					System.out.println("Got expected exception " + e.getMessage());
				} else
					System.out.println("Got unexpected exception " + e.getMessage());
			}
			rs.close();

			System.out.println("Positive Test8b - change the name of the statement " +
				"when the resultset is open and see if updateRow still works");
			System.out.println("This test works in embedded mode since Derby can " +
				"handle the change in the name of the statement with an open resultset");
			System.out.println("But it fails under Network Server mode because JCC " +
				"and Derby Net Client do not allow statement name change when there " +
				"an open resultset against it");
      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);
			try {
				stmt.setCursorName("CURSORNOUPDATE1");
				rs.updateRow();
				if (TestUtil.isNetFramework())
					System.out.println("FAIL!!! should have failed in network server");
				else
					System.out.println("PASS!!! passed in embedded mode");
			}
			catch (SQLException e) {
				if (TestUtil.isNetFramework()) {
					System.out.println("SQL State : " + e.getSQLState());
					System.out.println("Got expected exception " + e.getMessage());
				} else
					System.out.println("Got unexpected exception " + e.getMessage());
			}
			rs.close();

			System.out.println("Positive Test9a - using correlation name for the " +
				"table in the select sql works in embedded mode and Network Server " +
				"using Derby Net Client driver");
			System.out.println("Correlation name for table does not work in Network "+
				"Server mode (using JCC) because the drivers construct the delete sql "+
				"with the correlation name rather than the base table name");
			reloadData();
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
      rs = stmt.executeQuery("SELECT * FROM t1 abcde FOR UPDATE of c1");
			rs.next();
      System.out.println("column 1 on this row is " + rs.getInt(1));
      System.out.println("now try to deleteRow");
			try {
				rs.deleteRow();
				if (TestUtil.isJCCFramework())
					System.out.println("FAIL!!! should have failed in network server");
				else
					System.out.println("PASS!!! passed in embedded mode");
			}
			catch (SQLException e) {
				if (TestUtil.isJCCFramework()) {
					System.out.println("SQL State : " + e.getSQLState());
					System.out.println("Got expected exception " + e.getMessage());
				} else
					System.out.println("Got unexpected exception " + e.getMessage());
			}
			rs.close();

			System.out.println("Positive Test9b - using correlation name for " +
				"updatable columns is not allowed.");
			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"));
			try {
				System.out.println("attempt to get an updatable resultset using correlation name for an updatable column");
				System.out.println("The sql is SELECT c1 as col1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
				rs = stmt.executeQuery("SELECT c1 as col1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
				System.out.println("FAIL!!! executeQuery should have failed");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			System.out.println("attempt to get an updatable resultset using correlation name for an readonly column. It should work");
			System.out.println("The sql is SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
			rs = stmt.executeQuery("SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
			rs.next();
			rs.updateInt(1,11);
			rs.updateRow();
			rs.close();
			System.out.println("Table t1 after updateRow has following rows");
			dumpRS(stmt.executeQuery("select * from t1"));

			System.out.println("Positive Test9c - try to updateXXX on a readonly column. Should get error");
			reloadData();
			rs = stmt.executeQuery("SELECT c1, c2 FROM t1 abcde FOR UPDATE of c1");
			rs.next();
			try {
				rs.updateString(2,"bbbb");
				System.out.println("FAIL!!! updateString on readonly column should have failed");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			System.out.println("attempt to get an updatable resultset using correlation name for an readonly column. It should work");
			System.out.println("The sql is SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
			rs = stmt.executeQuery("SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
			rs.next();
			rs.updateInt(1,11);
			rs.updateRow();
			rs.close();
			System.out.println("Table t1 after updateRow has following rows");
			dumpRS(stmt.executeQuery("select * from t1"));

			System.out.println("Positive Test9d - try to updateXXX on a readonly column with correlation name. Should get error");
			reloadData();
			rs = stmt.executeQuery("SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
			rs.next();
			try {
				rs.updateString(2,"bbbb");
				System.out.println("FAIL!!! updateString on readonly column should have failed");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			rs.close();
			System.out.println("Table t1 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);
      reloadData();
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			stmt1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
      rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
			rs.next();
			rs1 = stmt1.executeQuery("SELECT * 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? ");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				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();
			rs.close();
			rs1.close();
			conn.setAutoCommit(true);

			System.out.println("Positive Test11 - setting the fetch size to > 1 will be ignored by updatable resultset. Same as updatable cursors");
      reloadData();
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
			stmt.setFetchSize(200);
      rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
			System.out.println("Notice the Fetch Size in run time statistics output.");
      showScanStatistics(rs, conn);
			System.out.println("statement's fetch size is " + stmt.getFetchSize());
			rs.close();
			stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");

			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 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"));
			//have to close the resultset because by default, resultsets are held open over commit
			rs.close();

			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);
			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("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) {
				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 fire