currentof.java

derby database source code.good for you.

		// because there is no order by (nor can there be)
		// the fact that this test prints out rows may someday
		// be a problem.  When that day comes, the row printing
		// can (should) be removed from this test.

		endCount = countRows ("select i, c from t for read only");
		System.out.println("Have "+endCount+" rows in table at start");

		// TEST: Updated column not found in for update of list
		caught = false;
		try {
			select = conn.prepareStatement("select I, C from t for update of I");
			cursor = select.executeQuery(); // cursor is now open
			update = conn.prepareStatement(
							"update t set C = 'abcde' where current of " +
							cursor.getCursorName());
		} catch (SQLException se) {
			String m = se.getSQLState();
			JDBCDisplayUtil.ShowSQLException(System.out,se);
			if ("42X31".equals(m)) {
				caught = true;
				System.out.println("PASS: update of non-existant column caught");
			} else {
				throw se;
			}
		} finally {
			if (! caught)
				System.out.println("FAIL: update of non-existant column not caught");
		}
		cursor.close();
		select.close();

		// TEST: Update of cursor declared READ ONLY
		caught = false;
		try {
			select = conn.prepareStatement("select I, C from t for read only");
			cursor = select.executeQuery(); // cursor is now open
			if (cursor.getCursorName() == null)
				{
				caught = true;
				System.out.println("PASS: update of read-only cursor caught");
				}
		} catch (SQLException se) {
			String m = se.getSQLState();
			JDBCDisplayUtil.ShowSQLException(System.out,se);
			throw se;
		} finally {
			if (! caught)
				System.out.println("FAIL: update of read-only cursor not caught");
		}
		cursor.close();
		select.close();

		// TEST: Update of cursor declared FETCH ONLY
		caught = false;
		try {
			select = conn.prepareStatement("select I, C from t for fetch only");
			cursor = select.executeQuery(); // cursor is now open
			if (cursor.getCursorName() == null)
				{
				caught = true;
				System.out.println("PASS: update of fetch-only cursor caught");
				}
		} catch (SQLException se) {
			String m = se.getSQLState();
			JDBCDisplayUtil.ShowSQLException(System.out,se);
			throw se;
		} finally {
			if (! caught)
				System.out.println("FAIL: update of fetch-only cursor not caught");
		}
		cursor.close();
		select.close();

		// TEST: Update of cursor with a union
		caught = false;
		try {
			select = conn.prepareStatement("select I, C from t union all select I, C from t");
			cursor = select.executeQuery(); // cursor is now open
			if (cursor.getCursorName() == null)
			{
				System.out.println("PASS: update of union cursor caught");
				caught = true;
			}
		} catch (SQLException se) {
			JDBCDisplayUtil.ShowSQLException(System.out,se);
			String m = se.getSQLState();
			throw se;
		} finally {
			if (! caught)
				System.out.println("FAIL: update of union cursor not caught");
		}
		cursor.close();
		select.close();

		// TEST: Update of cursor with a join
		caught = false;
		try {
			select = conn.prepareStatement("select t1.I, t1.C from t t1, t t2 where t1.I = t2.I");
			cursor = select.executeQuery(); // cursor is now open
			if (cursor.getCursorName() == null)
			{
				System.out.println("PASS: update of join cursor caught");
				caught = true;
			}
		} catch (SQLException se) {
			String m = se.getSQLState();
			JDBCDisplayUtil.ShowSQLException(System.out,se);
			throw se;
		} finally {
			if (! caught)
				System.out.println("FAIL: update of join cursor not caught");
		}
		cursor.close();
		select.close();

		// TEST: Update of cursor with a derived table
		caught = false;
		try {
			select = conn.prepareStatement("select I, C from (select * from t) t1");
			cursor = select.executeQuery(); // cursor is now open
			if (cursor.getCursorName() == null)
			{
				System.out.println("PASS: update of derived table cursor caught");
				caught = true;
			}
		} catch (SQLException se) {
			String m = se.getSQLState();
			JDBCDisplayUtil.ShowSQLException(System.out,se);
			throw se;
		} finally {
			if (! caught)
				System.out.println("FAIL: update of derived table cursor not caught");
		}
		cursor.close();
		select.close();

		// TEST: Update of cursor with a values clause
		caught = false;
		try {
			select = conn.prepareStatement("values (1, 2, 3)");
			cursor = select.executeQuery(); // cursor is now open
			if (cursor.getCursorName() == null)
			{
				caught = true;
				System.out.println("PASS: update of values clause cursor caught");
			}
		} catch (SQLException se) {
			String m = se.getSQLState();
			JDBCDisplayUtil.ShowSQLException(System.out,se);
			throw se;
		} finally {
			if (! caught)
				System.out.println("FAIL: update of values clause cursor not caught");
		}
		cursor.close();
		select.close();

		// TEST: Update of cursor with a subquery
		caught = false;
		try {
			select = conn.prepareStatement("select I, C from t where I in (select I from t)");
			cursor = select.executeQuery(); // cursor is now open
			if (cursor.getCursorName() == null)
			{
				caught = true;
				System.out.println("PASS: update of subquery cursor caught");
			}
		} catch (SQLException se) {
			JDBCDisplayUtil.ShowSQLException(System.out,se);
			throw se;
		} finally {
			if (! caught)
				System.out.println("FAIL: update of subquery cursor not caught");
		}
		cursor.close();
		select.close();

		select = conn.prepareStatement("select I, C from t for update");
		cursor = select.executeQuery(); // cursor is now open

		// would like to test a update attempt before the cursor
		// is open, but finagling to get the cursor name would
		// destroy the spirit of the rest of the tests,
		// which want to operate against the generated name.

		// TEST: cursor and target table mismatch
		caught = false;

		try {
			update = conn.prepareStatement("update s set i=1 where current of "+
									   cursor.getCursorName());
		} catch (SQLException se) {
			JDBCDisplayUtil.ShowSQLException(System.out,se);
			String m = se.getSQLState();
			if ("42X29".equals(m)) {
				caught = true;
				System.out.println("PASS: update table and cursor table mismatch caught");
			} else {
				throw se;
			}
		} finally {
			if (! caught)
				System.out.println("FAIL: update table and cursor table mismatch not caught");
		}
		// TEST: find the cursor during compilation
		update = conn.prepareStatement(
		    "update t set i=i+10, c='Gumby was here' where current of "+
			cursor.getCursorName());

		// TEST: update before the cursor is on a row
		caught = false;
		try {
			verifyCount("update before the cursor", update.executeUpdate(), 0); // no current row / closed
   		} catch (SQLException se) {
			String m = se.getSQLState();
			JDBCDisplayUtil.ShowSQLException(System.out,se);
			if ("XCL08".equals(m)) {
				caught = true;
				System.out.println("PASS: Attempt to update cursor before first row caught");
			} else {
				throw se;
			}
		} finally {
			if (! caught)
				System.out.println("FAIL: No error from update on cursor before first row");
		}

		// TEST: find the cursor during execution and it is on a row
		nextRow(cursor);
		verifyCount("update on row", update.executeUpdate(), 1);

		// TEST: update an already updated row; expect it to succeed.
		// will it have a cumulative effect?
		verifyCount("2nd update on row", update.executeUpdate(), 1);

		// skip a row and update another row so that two rows will
		// have been removed from the table when we are done.
		nextRow(cursor); // skip this row
		nextRow(cursor);

		verifyCount( "update after skipping", update.executeUpdate(), 1);

		// TEST: update past the last row
		nextRow(cursor); // skip this row
		verifyBoolean(cursor.next(), false); // past last row now
		caught = false;
		try {
			verifyCount("update: no current row", update.executeUpdate(), 0); // no current row / closed
   		} catch (SQLException se) {
			String m = se.getSQLState();
			JDBCDisplayUtil.ShowSQLException(System.out,se);
			if ("XCL08".equals(m)) {
				caught = true;
				System.out.println("PASS: Attempt to update cursor past last row caught");
			} else {
				throw se;
			}
		} finally {
			if (! caught)
				System.out.println("FAIL: No error from update on cursor past last row");
		}

		// TEST: update off a closed cursor
		cursor.close();
		select.close();
		caught = false;
		try {
			verifyCount("update on closed cursor", update.executeUpdate(),
						0);
		} catch (SQLException se) {
			String m = se.getSQLState();
			JDBCDisplayUtil.ShowSQLException(System.out,se);
			if ("XCL07".equals(m)) {
				caught = true;
				System.out.println("PASS: Attempt to update closed cursor caught");
			}
			if ("42X30".equals(m)) {
				caught = true;
				System.out.println("PASS: Attempt to update closed cursor caught");
			}
			
			if (!caught) {
				throw se;
			}
		} finally {
			if (! caught)
				System.out.println("FAIL: No error from update on closed cursor");
		}
		update.close();

		// TEST: no cursor with that name exists
		update2 = conn.createStatement();
		caught = false;
		try {
			update2.execute("update t set i=1 where current of nosuchcursor");
		} catch (SQLException se) {
			String m = se.getSQLState();
			JDBCDisplayUtil.ShowSQLException(System.out,se);
			if ("42X30".equals(m)) {
				caught = true;
				System.out.println("PASS: Attempt to update nonexistent cursor caught");
			} else {
				throw se;
			}
		} finally {
			if (! caught)
				System.out.println("FAIL: No error from update on nonexistent cursor");
		}

		endCount = countRows ("select i, c from t for read only");
		System.out.println("Have "+endCount+" rows in table at end");

		// TEST: attempt to do positioned update before cursor execute'd
		// TBD


		// TEST closing a cursor will close the related update
		bug4395(conn, "CS4395"); // Application provided cursor name
		bug4395(conn, null); // system provided cursor name


		System.out.println("PASS: update test complete");
	}

	private static void bug4395(Connection conn, String cursorName) throws SQLException {

		System.out.println("bug4395 Cursor Name " + (cursorName == null ? "System Generated" : "Application Defined"));

		PreparedStatement select = conn.prepareStatement("select I, C from t for update");
		if (cursorName != null)
			select.setCursorName(cursorName);

		ResultSet cursor = select.executeQuery(); // cursor is now open
		// TEST: find the cursor during compilation
		cursorName = cursor.getCursorName();
		PreparedStatement update = conn.prepareStatement("update t set i=i+?, c=? where current of "+
			cursorName);

		nextRow(cursor);
		update.setInt(1, 10);
		update.setString(2, "Dan was here");
		verifyCount("update: valid update", update.executeUpdate(), 1);
		cursor.close();

		// now prepare the a cursor with the same name but only column I for update
		PreparedStatement selectdd = conn.prepareStatement("select I, C from t for update of I");
		selectdd.setCursorName(cursorName);
		cursor = selectdd.executeQuery();
		nextRow(cursor);

		try {
			update.setInt(1, 7);
			update.setString(2, "no update");
			update.executeUpdate();
			System.out.println("FAIL update succeeded after cursor has been changed");
		} catch (SQLException se) {
			String m = se.getSQLState();
			JDBCDisplayUtil.ShowSQLException(System.out,se);
			if ("42X31".equals(m)) {
				System.out.println("PASS: Attempt to update changed invalid cursor caught");
			} else {
				throw se;
			}
		}

		cursor.close();
		cursor = selectdd.executeQuery();
		nextRow(cursor);
		cursor.close();

	}
}