updatableresultset.java

derby database source code.good for you.

			// use the ij utility to read the property file and
			// make the initial connection.
			ij.getPropertyArg(args);
			conn = ij.startJBMS();

			setup(true);

			System.out.println("Negative Testl - request for scroll insensitive updatable resultset will give a read only scroll insensitive resultset");
			conn.clearWarnings();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			JDBCDisplayUtil.ShowWarnings(System.out, conn);
			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();
				System.out.println("FAIL!!! deleteRow should have failed because Derby does not yet support scroll insensitive updatable resultsets");
			}
			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 Derby does not yet support scroll insensitive updatable resultsets");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			rs.next();
			//have to close the resultset because by default, resultsets are held open over commit
			rs.close();

			System.out.println("Negative Test2 - request for scroll sensitive updatable resultset will give a read only scroll insensitive resultset");
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			JDBCDisplayUtil.ShowWarnings(System.out, conn);
      System.out.println("requested TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE but that is not supported");
			System.out.println("Jira issue Derby-154 : When client connects to Network Server using JCC, it incorrectly shows support for scroll sensitive updatable resultsets");
      System.out.println("Make sure that we got TYPE_SCROLL_INSENSITIVE? " +  (stmt.getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE));
      System.out.println("Make sure that we got CONCUR_READ_ONLY? " +  (stmt.getResultSetConcurrency() == ResultSet.CONCUR_READ_ONLY));
			System.out.println("JDBC 2.0 updatable resultset api will fail on this resultset because this is not an updatable resultset");
      rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
			rs.next();
			try {
				rs.deleteRow();
				System.out.println("FAIL!!! deleteRow should have failed because Derby does not yet support scroll sensitive updatable resultsets");
			}
			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 Derby does not yet support scroll sensitive updatable resultsets");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			rs.next();
			//have to close the resultset because by default, resultsets are held open over commit
			rs.close();

			System.out.println("Negative Test3 - request a read only resultset and attempt deleteRow and updateRow on it");
			stmt = conn.createStatement();//the default is a read only forward only resultset
			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");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
      System.out.println("Now attempting to send an updateRow on a read only resultset.");
			try {
				rs.updateRow();
				System.out.println("FAIL!!! updateRow should have failed because this is a read only resultset");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			//have to close the resultset because by default, resultsets are held open over commit
			rs.close();

			System.out.println("Negative Test4 - request a read only resultset and send a sql with FOR UPDATE clause and attempt deleteRow/updateRow on it");
			stmt = conn.createStatement();//the default is a read only forward only resultset
			rs = stmt.executeQuery("select * from t1 FOR UPDATE");
			System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
			rs.next();
      System.out.println("Now attempting to send a deleteRow on a read only resultset with FOR UPDATE clause in the SELECT sql.");
			try {
				rs.deleteRow();
				System.out.println("FAIL!!! deleteRow should have failed because this is a read only resultset");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			System.out.println("Now attempting to send a updateRow on a read only resultset with FOR UPDATE clause in the SELECT sql.");
			try {
				rs.updateRow();
				System.out.println("FAIL!!! updateRow should have failed because this is a read only resultset");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			//have to close the resultset because by default, resultsets are held open over commit
			rs.close();

			System.out.println("Negative Test5 - request updatable resultset for sql with no FOR UPDATE clause");
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			rs = stmt.executeQuery("select * from t1");//notice that we forgot to give mandatory FOR UPDATE clause for updatable resultset
			System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
			System.out.println("Jira issue Derby-159 : Warnings raised by Derby are not getting passed to the Client in Network Server Mode");
			System.out.println("Will see the warnings in embedded mode only");
			JDBCDisplayUtil.ShowWarnings(System.out, rs);
			rs.next();
      System.out.println("Now attempting to send a delete on a sql with no FOR UPDATE clause.");
			try {
				rs.deleteRow();
				System.out.println("FAIL!!! deleteRow should have failed on sql with no FOR UPDATE clause");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
      System.out.println("Now attempting to send a updateRow on a sql with no FOR UPDATE clause.");
			try {
				rs.updateRow();
				System.out.println("FAIL!!! updateRow should have failed on sql with no FOR UPDATE clause");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			//have to close the resultset because by default, resultsets are held open over commit
			rs.close();

			System.out.println("Negative 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));
			System.out.println("Jira issue Derby-159 : Warnings raised by Derby are not getting passed to the Client in Network Server Mode");
			System.out.println("Will see the warnings in embedded mode only");
			JDBCDisplayUtil.ShowWarnings(System.out, rs);
			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");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			System.out.println("Now attempting to send a updateRow on a 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) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			//have to close the resultset because by default, resultsets are held open over commit
			rs.close();

			System.out.println("Negative 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 * 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.");
			try {
				rs.deleteRow();
				System.out.println("FAIL!!! deleteRow should have failed because resultset is not on a row");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
      System.out.println("Now attempt a updateRow without first doing next on the resultset.");
			System.out.println("updateRow will check if it is on a row or not even " +
				"though no changes have been made to the row using updateXXX");
			try {
				rs.updateRow();
				System.out.println("FAIL!!! updateRow should have failed because " +
						"resultset is not on a row");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			while (rs.next());//read all the rows from the resultset and position after the last row
      System.out.println("ResultSet is positioned after the last row. attempt to deleteRow at this point should fail!");
			try {
				rs.deleteRow();
				System.out.println("FAIL!!! deleteRow should have failed because resultset is after the last row");
			}
			catch (SQLException e) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			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) {
				System.out.println("SQL State : " + e.getSQLState());
				System.out.println("Got expected exception " + e.getMessage());
			}
			rs.close();

			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 * FROM t1 FOR UPDATE");
			System.out.println("Make sure that we got CONCUR_UPDATABLE? " + (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE));
			rs.next();
			rs.close();
			try {
				rs.deleteRow();
				System.out.println("FAIL!!! deleteRow should have failed because resultset is closed");
			}
			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 closed");
			}