updatableresultset.java

derby database source code.good for you.

			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}

			System.out.println("Negative Test9 - try updatable resultset on system table");
			try {
				rs = stmt.executeQuery("SELECT * FROM sys.systables FOR UPDATE");
				System.out.println("FAIL!!! trying to open an updatable resultset on a system table should have failed because system tables can't be updated by a user");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}

			System.out.println("Negative Test10 - try updatable resultset on a view");
			try {
				rs = stmt.executeQuery("SELECT * FROM v1 FOR UPDATE");
				System.out.println("FAIL!!! trying to open an updatable resultset on a view should have failed because Derby doesnot support updates to views yet");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			stmt.executeUpdate("drop view v1");

			System.out.println("Negative Test11 - attempt to open updatable resultset when there is join in the select query should fail");
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			try {
				rs = stmt.executeQuery("SELECT c1 FROM t1,t2 where t1.c1 = t2.c21 FOR UPDATE");
				System.out.println("FAIL!!! trying to open an updatable resultset should have failed because updatable resultset donot support join in the select query");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}

			System.out.println("Negative Test12 - With autocommit on, attempt to drop a table when there is an open updatable resultset on it");
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
      rs = stmt.executeQuery("SELECT c1 FROM t1 FOR UPDATE");
			rs.next();
			rs.updateInt(1,123);
			System.out.println("Opened an updatable resultset. Now trying to drop that table through another Statement");
			stmt1 = conn.createStatement();
			try {
				stmt1.executeUpdate("drop table t1");
				System.out.println("FAIL!!! drop table should have failed because the updatable resultset is still open");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			System.out.println("Since autocommit is on, the drop table exception resulted in a runtime rollback causing updatable resultset object to close");
			try {
				rs.updateRow();
				System.out.println("FAIL!!! resultset should have been closed at this point and updateRow should have failed");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			try {
				rs.deleteRow();
				System.out.println("FAIL!!! resultset should have been closed at this point and deleteRow should have failed");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}

			System.out.println("Negative Test13 - foreign key constraint failure will cause deleteRow to fail");
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
      rs = stmt.executeQuery("SELECT * 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) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			System.out.println("Since autocommit is on, the constraint exception resulted in a runtime rollback causing updatable resultset object to close");
			try {
				rs.next();
				if (TestUtil.isNetFramework())
					System.out.println("Jira entry Derby-160 : for Network Server because next should have failed");
				System.out.println("FAIL!!! next should have failed because foreign key constraint failure resulted in a runtime rollback");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}

			System.out.println("Negative Test14 - foreign key constraint failure will cause updateRow to fail");
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			rs = stmt.executeQuery("SELECT c1, c2 FROM tableWithPrimaryKey FOR UPDATE");
			rs.next();
			rs.updateInt(1,11);
			rs.updateInt(2,22);
			try {
				rs.updateRow();
				System.out.println("FAIL!!! updateRow should have failed because it will cause foreign key constraint failure");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			System.out.println("Since autocommit is on, the constraint exception resulted in a runtime rollback causing updatable resultset object to close");
			try {
				rs.next();
				if (TestUtil.isNetFramework())
					System.out.println("Jira entry Derby-160 : for Network Server because next should have failed");
				System.out.println("FAIL!!! next should have failed because foreign key constraint failure resulted in a runtime rollback");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}

			System.out.println("Negative Test15 - Can't call updateXXX methods on columns that do not correspond to a column in the table");
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
			rs.next();
			try {
				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) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}

			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) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}

			System.out.println("Positive Test1a - request updatable resultset for forward only type resultset");
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			JDBCDisplayUtil.ShowWarnings(System.out, conn);
      System.out.println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
      System.out.println("got TYPE_FORWARD_ONLY? " +  (stmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY));
      System.out.println("got CONCUR_UPDATABLE? " +  (stmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE));
      rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
			System.out.println("JDBC 2.0 updatable resultset apis on this ResultSet object will pass because this is an updatable resultset");
			rs.next();
      System.out.println("column 1 on this row before deleteRow is " + rs.getInt(1));
      System.out.println("column 2 on this row before deleteRow is " + rs.getString(2));
			rs.deleteRow();
      System.out.println("Since after deleteRow(), in embedded mode and Network "+
				"Server mode using Derby Net Client, 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 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 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);
			JDBCDisplayUtil.ShowWarnings(System.out, conn);
			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(), in embedded mode and Network "+
				"Server mode using Derby Net Client, ResultSet is positioned before " +
				"the next row, getXXX will fail");
			try {
				System.out.println("column 1 on this updateRow row is " + rs.getInt(1));
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			System.out.println("calling updateRow again w/o first positioning the ResultSet on the next row will fail");
			try {
				rs.updateRow();
				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());
			}
			System.out.println("Position the ResultSet with next()");
			rs.next();
			System.out.println("Should be able to updateRow() on the current row now");
			rs.updateString(2,"234");
			rs.updateRow();
			//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("Will work in embedded mode because target table is "+
				"not derived from the columns in the select list");
			System.out.println("Will not work in network server mode because it " +
				"derives the target table from the columns in the select list");
      System.out.println("total number of rows in 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));
			try {
				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) {