updatableresultset.java

derby database source code.good for you.

				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());
			}
			//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"));

			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");
      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.deleteRow();
      System.out.println("Since after deleteRow(), ResultSet is positioned before the next row, getXXX will fail");
			try {
				System.out.println("column 1 on this deleted row is " + rs.getInt(1));
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
      System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
			try {
				rs.deleteRow();
				System.out.println("FAIL!!! deleteRow should have failed because it can't be called more than once on the same row");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
      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) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			System.out.println("calling updateRow/updateXXX again w/o first positioning the ResultSet on the next row will fail");
			try {
				rs.updateInt(1,0);
				System.out.println("FAIL!!! updateXXX should have failed because resultset is not positioned on a row");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			try {
				rs.updateRow();
				System.out.println("FAIL!!! updateRow should have failed because resultset is not positioned on a row");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			try {
				rs.cancelRowUpdates();
				System.out.println("FAIL!!! cancelRowUpdates should have failed because resultset is not positioned on a row");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			System.out.println("Position the ResultSet with next()");
			rs.next();
			System.out.println("Should be able to cancelRowUpdates() on the current row now");
			rs.cancelRowUpdates();
			//have to close the resultset because by default, resultsets are held open over commit
			rs.close();

			System.out.println("Positive Test4 - use callable statement with concur updatable status");
      reloadData();
			callStmt = conn.prepareCall("select * from t1 for update", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
      rs = callStmt.executeQuery();
      System.out.println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
      System.out.println("got TYPE_FORWARD_ONLY? " +  (callStmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY));
      System.out.println("got CONCUR_UPDATABLE? " +  (callStmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE));
			rs.next();
      System.out.println("column 1 on this row is " + rs.getInt(1));
			rs.deleteRow();
      System.out.println("Since after deleteRow(), ResultSet is positioned before the next row, getXXX will fail");
			try {
				System.out.println("column 1 on this deleted row is " + rs.getInt(1));
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
      System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
			try {
				rs.deleteRow();
				System.out.println("FAIL!!! deleteRow should have failed because it can't be called more than once on the same row");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
      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 Test5 - donot have to select primary key to get an updatable resultset");
      reloadData();
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
      rs = stmt.executeQuery("SELECT c32 FROM t3 FOR UPDATE");
			rs.next();
      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 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 * FROM t1 FOR UPDATE of c1");
			rs.next();
      System.out.println("The JDBC program should look at rowDeleted only if deletesAreDetected returns true");
      System.out.println("Since Derby returns false for detlesAreDetected for FORWARD_ONLY updatable resultset,the program should not rely on rs.rowDeleted() for FORWARD_ONLY updatable resultsets");
      System.out.println("Have this call to rs.rowDeleted() just to make sure the method does always return false? " + rs.rowDeleted());
			rs.deleteRow();
      System.out.println("Have this call to rs.rowDeleted() just to make sure the method does always return false? " + rs.rowDeleted());
			rs.close();

			System.out.println("Positive Test6b - For Forward Only resultsets, DatabaseMetaData will return false for ownUpdatesAreVisible and updatesAreDetected");
			System.out.println("This is because, after updateRow, we position the ResultSet before the next row");
			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)");
			stmt.executeUpdate("insert into SESSION.t2 values(22, 1)");
			System.out.println("following rows in temp table before deleteRow");
			dumpRS(stmt.executeQuery("select * from SESSION.t2"));
			rs = stmt.executeQuery("select c21 from session.t2 for update");
			rs.next();
			rs.deleteRow();
			rs.next();
			rs.deleteRow();
			System.out.println("As expected, no rows in temp table after deleteRow");
			dumpRS(stmt.executeQuery("select * from SESSION.t2"));
			rs.close();
			stmt.executeUpdate("DROP TABLE SESSION.t2");

			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 statement " +
				"when the resultset is open and see if deleteRow 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();
			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");
			stmt.setCursorName("CURSORNOUPDATe");//notice this name is case sensitive
      rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
			rs.next();
			rs.deleteRow();
      System.out.println("change the cursor name one more time with setCursorName and then try to deleteRow");
			try {
				stmt.setCursorName("CURSORNOUPDATE1");
				rs.next();
				rs.deleteRow();
				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());