java/testing/org/apache/derbyTesting/functionTests/tests/lang/CurrentOfTest.java - derby - Git at Google

 /*
  *
  * Derby - Class org.apache.derbyTesting.functionTests.tests.lang.CurrentOfTest
  *
  * Licensed to the Apache Software Foundation (ASF) under one or more
  * contributor license agreements.  See the NOTICE file distributed with
  * this work for additional information regarding copyright ownership.
  * The ASF licenses this file to You under the Apache License, Version 2.0
  * (the "License"); you may not use this file ecept in compliance with
  * the License.  You may obtain a copy of the License at
  *
  *    http://www.apache.org/licenses/LICENSE-2.0
  *
  * Unless required by applicable law or agreed to in writing,
  * software distributed under the License is distributed on an
  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
  * either express or implied. See the License for the specific
  * language governing permissions and limitations under the License.
  */

 package org.apache.derbyTesting.functionTests.tests.lang;

 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import junit.framework.Test;
 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
 import org.apache.derbyTesting.junit.BaseTestSuite;
 import org.apache.derbyTesting.junit.JDBC;

 /** This tests the current of statements, i.e.
      * delete where current of and update where current of.
  * Not done in ij since the cursor names may not be stable.
  */
 public class CurrentOfTest extends BaseJDBCTestCase {


 	/**
      * Public constructor required for running test as standalone JUnit.
      */
 	public CurrentOfTest(String name) {
 		super(name);
 	}
 	/**
      * Create a suite of tests.
      */
 	public static Test suite() {
         BaseTestSuite suite = new BaseTestSuite("CurrentOfTest");
 		suite.addTestSuite(CurrentOfTest.class);
 		//To run the test in both embedded and client/server mode
 		//commenting it for the time being sicne the test fails in the client/server mode
 		//return   TestConfiguration.defaultSuite(CurrentOfTest.class);
 		return suite;
 	}
 	 /**
      * Set the fixture up with tables t and s and insert 4 rows in table t.
      */
 	protected void setUp() throws SQLException {
 		getConnection().setAutoCommit(false);
 		Statement stmt = createStatement();
 		stmt.executeUpdate("create table t (i int, c char(50))");
 		stmt.executeUpdate("create table s (i int, c char(50))");
 		stmt.executeUpdate("insert into t values (1956, 'hello world')");
 		stmt.executeUpdate("insert into t values (456, 'hi yourself')");
 		stmt.executeUpdate("insert into t values (180, 'rubber ducky')");
 		stmt.executeUpdate("insert into t values (3, 'you are the one')");
 		stmt.close();
 		commit();
 	}
 	/**
      * Tear-down the fixture by removing the tables
      */
 	protected void tearDown() throws Exception {
         JDBC.dropSchema(getConnection().getMetaData(),
                 getTestConfiguration().getUserName());
 		super.tearDown();
 	}

 	/**
      * Test read only statements.
 	 */
 	public void testReadOnlyCursors() throws SQLException {

 		String[] readOnlySQL =
 		{
             "select I, C from t for read only",
             "select I, C from t for fetch only",
             "select I, C FROM T ORDER BY 1",
             "values (1, 2, 3)",

             // TEST: Update of cursor with a union
             "select I, C from t union all select I, C from t",
             // TEST: Update of cursor with a join
             "select t1.I, t1.C from t t1, t t2 where t1.I = t2.I",
             // TEST: Update of cursor with a derived table
             "select I, C from (select * from t) t1",
             // TEST: Update of cursor with a subquery
             "select I, C from t where I in (select I from t)"

 		};

         // NOTE: JDK 1.4 javadoc for ResultSet.getCursorName()
         // says it will throw an execption if the statement
         // cannot support a positioned update. However that
         // line was removed in JavaSE 6 (JDBC 4) javadoc.

         for (int i = 0; i < readOnlySQL.length; i++)
         {
             // The system will not give a cursor name
             // to a read only statement.
             PreparedStatement select = prepareStatement(readOnlySQL[i]);
             ResultSet cursor = select.executeQuery();
             assertNull(readOnlySQL[i], cursor.getCursorName());
             cursor.close();

             // but will if the user supplies one.
             select.setCursorName("PLEASE_UPDATE");
             cursor = select.executeQuery();
             assertEquals(readOnlySQL[i], "PLEASE_UPDATE", cursor.getCursorName());

             // but the cursor is marked as read only so positioned
             // statements will fail.
             assertCompileError("42X23",
                     "DELETE FROM T WHERE CURRENT OF PLEASE_UPDATE");

             assertCompileError("42X23",
                 "UPDATE T SET I = 3 WHERE CURRENT OF PLEASE_UPDATE");

             cursor.close();
             select.close();
         }
 	}
 	/**
     * Test delete with the current of statements.
     * Also do some negative testing to see whether correct
     * exceptions are thrown or not.
     * @throws Exception
     */
 	public void testDelete() throws SQLException {
 		PreparedStatement select, delete;
 		Statement delete1,delete2;
 		ResultSet cursor;
 		String tableRows = "select i, c from t for read only";

 		delete1 = createStatement();
 		Object[][] expectedRows = new Object[][]{{new String("1956"),new String("hello world")},
 												 {new String("456"),new String("hi yourself")},
 												 {new String("180"),new String("rubber ducky")},
 												 {new String("3"),new String("you are the one")}};
 		JDBC.assertFullResultSet(delete1.executeQuery(tableRows), expectedRows, true);

 		select = prepareStatement("select i, c from t for update");
 		cursor = select.executeQuery(); // cursor is now open

 		// would like to test a delete 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

 		assertCompileError("42X28","delete from s where current of " + cursor.getCursorName());

 		// TEST: find the cursor during compilation
 		delete = prepareStatement("delete from t where current of "
 				+ cursor.getCursorName());
 		// TEST: delete before the cursor is on a row
 		assertStatementError("24000", delete);
 		cursor.next();
 		assertEquals(1956, cursor.getInt(1));

 		// TEST: find the cursor during execution and it is on a row
 		assertUpdateCount(delete, 1);
 		// skip a row and delete another row so that two rows will
 		// have been removed from the table when we are done.
 		// skip this row
 		cursor.next();
 		assertEquals(456, cursor.getInt(1));
 		cursor.next();
 		assertEquals(180, cursor.getInt(1));
 		assertUpdateCount(delete, 1);

 		// TEST: delete past the last row
 		cursor.next();// skip this row
 		assertEquals(3, cursor.getInt(1));
 		assertFalse(cursor.next());
 		if (usingEmbedded())
 			assertStatementError("24000", delete);
 		else
 			assertStatementError("42X30", delete);


 		// TEST: delete off a closed cursor
 		// Once this is closed then the cursor no longer exists.
 		cursor.close();
 		if (usingEmbedded())
 			assertStatementError("42X30", delete);
 		else
 			assertStatementError("XCL16", delete);

 		// TEST: no cursor with that name exists
 		delete2 = createStatement();
 		assertStatementError("42X30", delete2,"delete from t where current of myCursor" );
 		expectedRows = new Object[][]{{new String("456"),new String("hi yourself")},
 				   					  {new String("3"),new String("you are the one")}};
 		JDBC.assertFullResultSet(delete1.executeQuery(tableRows), expectedRows, true);
 		delete.close();
 		delete2.close();
 		select.close();

         // Test a cursor where not all the columns can be updated.
         // Positioned DELETE is still allowed.
         select = prepareStatement("SELECT I, C FROM T FOR UPDATE OF I");
         cursor = select.executeQuery();
         delete = prepareStatement("delete from t where current of "
                 + cursor.getCursorName());
         assertTrue(cursor.next());
         assertUpdateCount(delete, 1);

         delete.close();
         delete1.close();
         cursor.close();
         select.close();


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

 	}
 	/**
 	    * Test update with the current of statements.
 	    * Also do some negative testing to see whether correct
 	    * exceptions are thrown or not.
 	    * @throws Exception
 	    */
 	public void testUpdate() throws SQLException {
 		PreparedStatement select;
 		PreparedStatement update;
 		Statement select1,update2;
 		ResultSet cursor;
 		String tableRows = "select i, c from t for read only";

 		// these are basic tests without a where clause on the select.
 		// all rows are in and stay in the cursor's set when updated.

 		// 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.

 		// TEST: Updated column not found in for update of list

 		select = prepareStatement("select I, C from t for update of I");
 		cursor = select.executeQuery(); // cursor is now open
 		assertCompileError("42X31", "update t set C = 'abcde' where current of "+ cursor.getCursorName());
 		cursor.close();
 		select.close();

 		//Making sure we have the correct rows in the table to begin with
 		select1 = createStatement();
 		Object[][] expectedRows = new Object[][]{{new String("1956"),new String("hello world")},
 				 {new String("456"),new String("hi yourself")},
 				 {new String("180"),new String("rubber ducky")},
 				 {new String("3"),new String("you are the one")}};
 		JDBC.assertFullResultSet(select1.executeQuery(tableRows), expectedRows, true);

 		select = 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

 		assertCompileError("42X29","update s set i=1 where current of " + cursor.getCursorName());

 		// TEST: find the cursor during compilation
 		update = 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
 		assertStatementError("24000", update);

 		// TEST: find the cursor during execution and it is on a row
 		cursor.next();
 		assertEquals(1956,cursor.getInt(1));
 		assertUpdateCount(update, 1);

 		// TEST: update an already updated row; expect it to succeed.
 		// will it have a cumulative effect?
 		assertUpdateCount(update, 1);
 		// skip a row and update another row so that two rows will
 		// have been removed from the table when we are done.
 		cursor.next(); // skip this row
 		assertEquals(456,cursor.getInt(1));
 		cursor.next();
 		assertEquals(180,cursor.getInt(1));
 		assertUpdateCount(update, 1);

 		// TEST: update past the last row
 		cursor.next(); // skip this row
 		assertEquals(3,cursor.getInt(1));
 		assertFalse(cursor.next());
 		assertStatementError("24000", update);

 		// TEST: update off a closed cursor
 		cursor.close();
 		select.close();
 		assertStatementError("42X30", update);
 		update.close();

 		// TEST: no cursor with that name exists
 		update2 = createStatement();
 		assertStatementError("42X30", update2,"update t set i=1 where current of nosuchcursor");
 		update2.close();

 		//Verifyin we have the correct updated rows in the table at the end
 		expectedRows = new Object[][]{{new String("1976"),new String("Gumby was here")},
 				 {new String("456"),new String("hi yourself")},
 				 {new String("190"),new String("Gumby was here")},
 				 {new String("3"),new String("you are the one")}};
 		JDBC.assertFullResultSet(select1.executeQuery(tableRows), expectedRows, true);
 		// TEST: attempt to do positioned update before cursor execute'd
 		// TBD

 		cursor.close();

 	}

     /**
      * Test the positioned update correctly recompiles when an index is added.
      */
     public void testUpdateRecompileCreateIndex() throws Exception
     {
         recompile("UPDATE T SET I = I + 1 WHERE CURRENT OF ",
                 "CREATE INDEX IT ON T(I)", null);
     }

     /**
      * Test the positioned update correctly recompiles when the
      * definition of a function is changed.
      */
     public void testUpdateRecompileChangeFunction() throws Exception
     {
         Statement s = createStatement();
         s.execute("CREATE FUNCTION F(V INTEGER) RETURNS INTEGER " +
                 "NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA " +
                 "EXTERNAL NAME '" + getClass().getName() + ".doubleValue'");
         commit();
         String changeSQL = "CREATE FUNCTION F(V INTEGER) RETURNS INTEGER " +
             "NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA " +
             "EXTERNAL NAME '" + getClass().getName() + ".tripleValue'";
         int firstI = recompile("UPDATE T SET I = F(I) WHERE CURRENT OF ",
                 "DROP FUNCTION F", changeSQL);

         String[][] values = new String[][]
               {{"3"}, {"180"}, {"456"}, {"1956"}};

         if (firstI == 180)
         {
             // 180 doubled to 360
             // 456 tripled to 1368
             values[1] = new String[] {"360"};
             values[2] = new String[] {"1368"};
         }
         else
         {
             // 456 doubled to 912
             // 180 tripled to 540
             values[1] = new String[] {"540"};
             values[2] = new String[] {"912"};

         }

         JDBC.assertFullResultSet(s.executeQuery("SELECT I FROM T ORDER BY I"),
                 values);

         s.close();
     }
     /**
      * Test the positioned delete correctly recompiles when an index is added.
      */
     public void testDeleteRecompileCreateIndex()  throws Exception
     {
         recompile("DELETE FROM T WHERE CURRENT OF ",
                 "CREATE INDEX IT ON T(I)", null);
     }

     /**
      * Execute a select and then the positioned statement against it.
      * Then execute the changeSQL that should force a recompile of the
      * positioned statement. Then execute the positioned statement
      * again and finally check all is ok with check table.
      *
      * The positioned statements are executed against the rows that
      * have I=180 and I=456.
      *
      * @return the value of I for the first row that had the positioned
      * statement executed against it, ie. before the change SQl was executed.
      */
     private int recompile(String positionedSQL, String changeSQL1, String changeSQL2)
          throws SQLException
     {
         Statement s = createStatement();
         PreparedStatement select = prepareStatement("select I, C from t for update");
         ResultSet cursor = select.executeQuery();


         PreparedStatement update = prepareStatement(
                 positionedSQL + cursor.getCursorName());

         // Execute the positioned statement against one row,
         // either i=180 or 456, which ever comes first.
         int firstRowI = -1;
         while (cursor.next())
         {
             int i = cursor.getInt(1);
             if (i == 180 || i == 456) {
                 update.execute();
                 firstRowI = i;
                 break;
             }
         }
         assertTrue(firstRowI == 180 || firstRowI == 456);

         s.execute(changeSQL1);
         if (changeSQL2 != null)
             s.execute(changeSQL2);

         // And one more execute against one more row
         // either 180 or 456.
         int secondRowI = -1;
         while (cursor.next())
         {
             int i = cursor.getInt(1);
             if (i == 180 || i == 456) {
                 update.execute();
                 secondRowI = i;
                 break;
             }
         }
         assertTrue(firstRowI !=secondRowI);
         assertTrue(secondRowI == 180 || secondRowI == 456);

         update.close();
         cursor.close();
         select.close();
         commit();
         s.close();

         assertCheckTable("T");

         return firstRowI;
     }

 	/**
 	 * Change the current cursor from the one the positioned
      * UPDATE and DELETE was compiled against to one that only has a
      * subset of the columns being updatable.
 	 */
 	public void testCursorChangeUpdateList() throws SQLException {

         // Update will fail
         cursorChange(
                 "42X31",
                 "select I, C from t for update",
                 "update t set i=i+19, c='OLD' || cast(i as CHAR(20)) where current of ",
                 "select I, C from t for update of I"
                 );

         // Delete will succeed.
         cursorChange(
                 null,
                 "select I, C from t for update",
                 "DELETE FROM t WHERE CURRENT OF ",
                 "select I, C from t for update of I"
                 );
 	}

     /**
      * Change the current cursor from the one the positioned
      * UPDATE/DELETE was compiled against to one that is read only
      * against the same table.
      */
     public void testCursorChangeToReadOnly() throws SQLException {

         cursorChange(
             "42X23", // cursor is read only
             "select I, C from t for update",
             "update t set i=i+23 where current of ",
             "select I, C from t for fetch only"
             );

         cursorChange(
                 "42X23", // cursor is read only
                 "select I, C from t for update",
                 "DELETE FROM t WHERE CURRENT OF ",
                 "select I, C from t for fetch only"
                 );
     }

     /**
      * Change the current cursor from the one the positioned
      * UPDATE was compiled against to one that is against
      * a different table.
      *
      * test FAILS - once fixed should be renamed to lose
      * the FAIL prefix. Fails as the positioned update wor
      */
     public void testCursorChangeToDifferentTable() throws SQLException {

         Statement s = createStatement();
         s.executeUpdate("INSERT INTO S(I,C) SELECT I,C FROM T");
         s.close();
         commit();

         cursorChange(
             "42X29",  // this is the error testUpdate() sees.
             "select I, C from t for update",
             "update t set i=i+23 where current of ",
             "SELECT I, C FROM S FOR UPDATE"
             );

         cursorChange(
             "42X28", // this is the error testDelete() sees.
             "select I, C from t for update",
             "DELETE FROM t WHERE CURRENT OF ",
             "SELECT I, C FROM S FOR UPDATE"
            );

     }


     /**
      * Run cursorChange() with an application provided name
      * and a system provided name.
      *
      */
     private void cursorChange(String sqlState,
             String initialCursor,
             String positionedStatement,
             String changeToCursor) throws SQLException
     {
         // Since these tests delete rows we add a couple more to
         // ensure any cursor we open has at least one row.
         Statement s = createStatement();
         s.executeUpdate("insert into t values (425, 'apache db derby')");
         s.executeUpdate("insert into t values (280, 'derby-user users')");
         s.close();
         commit();

         cursorChange(sqlState, "CHANGE_ME", initialCursor, positionedStatement, changeToCursor);
         cursorChange(sqlState, null, initialCursor, positionedStatement, changeToCursor);
     }

     /**
      * Test what happens to a positioned update when the cursor
      * it is compiled against changes to the SQL provided, changeToSQL.
      * This test first prepares a cursor initialCursor
      * using with the given name (or system name if null is passed in)
      * A cursor is opened and a positioned update is opened that updates.
      *
      * If sqlState is null then no error is expected and thus the
      * positioned statement must update a single row.
      * Otherwise sqlState is the exected exception for the update.
      *
      * If no error is expected then three rows will be either
      * updated or deleted depending on the positioned statement.
      *
      * If an error is expected then two rows will be updated
      * or deleted.
      */
 	private void cursorChange(String sqlState,
             String cursorName,
             String initialCursor,
             String positionedStatement,
             String changeToCursor) throws SQLException {

 		PreparedStatement select = prepareStatement(initialCursor);
 		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 = prepareStatement(
                 positionedStatement + cursorName);
 		assertTrue(cursor.next());
 		assertUpdateCount(update, 1);
 		cursor.close();

 		// now prepare the a cursor with the same name but different SQL.
 		PreparedStatement selectdd = prepareStatement(changeToCursor);
 		selectdd.setCursorName(cursorName);
 		cursor = selectdd.executeQuery();
 		assertTrue(cursor.next());
         if (sqlState != null)
 		    assertStatementError(sqlState,update);
         else
             assertUpdateCount(update, 1);

 		cursor.close();

 		// now execute the original statement again and the positioned update
 		// will work.
 		cursor = select.executeQuery();
 		cursor.next();
 		assertUpdateCount(update, 1);

 		cursor.close();
 		update.close();
 		selectdd.close();
 		select.close();

 	}

     /*
     ** Routines
     */

     public static int doubleValue(int i)
     {
         return i * 2;
     }
     public static int tripleValue(int i)
     {
         return i * 3;
     }
 }
	/*
	*
	* Derby - Class org.apache.derbyTesting.functionTests.tests.lang.CurrentOfTest
	*
	* Licensed to the Apache Software Foundation (ASF) under one or more
	* contributor license agreements. See the NOTICE file distributed with
	* this work for additional information regarding copyright ownership.
	* The ASF licenses this file to You under the Apache License, Version 2.0
	* (the "License"); you may not use this file ecept in compliance with
	* the License. You may obtain a copy of the License at
	*
	* http://www.apache.org/licenses/LICENSE-2.0
	*
	* Unless required by applicable law or agreed to in writing,
	* software distributed under the License is distributed on an
	* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
	* either express or implied. See the License for the specific
	* language governing permissions and limitations under the License.
	*/

	package org.apache.derbyTesting.functionTests.tests.lang;

	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	import junit.framework.Test;
	import org.apache.derbyTesting.junit.BaseJDBCTestCase;
	import org.apache.derbyTesting.junit.BaseTestSuite;
	import org.apache.derbyTesting.junit.JDBC;

	/** This tests the current of statements, i.e.
	* delete where current of and update where current of.
	* Not done in ij since the cursor names may not be stable.
	*/
	public class CurrentOfTest extends BaseJDBCTestCase {


	/**
	* Public constructor required for running test as standalone JUnit.
	*/
	public CurrentOfTest(String name) {
	super(name);
	}
	/**
	* Create a suite of tests.
	*/
	public static Test suite() {
	BaseTestSuite suite = new BaseTestSuite("CurrentOfTest");
	suite.addTestSuite(CurrentOfTest.class);
	//To run the test in both embedded and client/server mode
	//commenting it for the time being sicne the test fails in the client/server mode
	//return TestConfiguration.defaultSuite(CurrentOfTest.class);
	return suite;
	}
	/**
	* Set the fixture up with tables t and s and insert 4 rows in table t.
	*/
	protected void setUp() throws SQLException {
	getConnection().setAutoCommit(false);
	Statement stmt = createStatement();
	stmt.executeUpdate("create table t (i int, c char(50))");
	stmt.executeUpdate("create table s (i int, c char(50))");
	stmt.executeUpdate("insert into t values (1956, 'hello world')");
	stmt.executeUpdate("insert into t values (456, 'hi yourself')");
	stmt.executeUpdate("insert into t values (180, 'rubber ducky')");
	stmt.executeUpdate("insert into t values (3, 'you are the one')");
	stmt.close();
	commit();
	}
	/**
	* Tear-down the fixture by removing the tables
	*/
	protected void tearDown() throws Exception {
	JDBC.dropSchema(getConnection().getMetaData(),
	getTestConfiguration().getUserName());
	super.tearDown();
	}

	/**
	* Test read only statements.
	*/
	public void testReadOnlyCursors() throws SQLException {

	String[] readOnlySQL =
	{
	"select I, C from t for read only",
	"select I, C from t for fetch only",
	"select I, C FROM T ORDER BY 1",
	"values (1, 2, 3)",

	// TEST: Update of cursor with a union
	"select I, C from t union all select I, C from t",
	// TEST: Update of cursor with a join
	"select t1.I, t1.C from t t1, t t2 where t1.I = t2.I",
	// TEST: Update of cursor with a derived table
	"select I, C from (select * from t) t1",
	// TEST: Update of cursor with a subquery
	"select I, C from t where I in (select I from t)"

	};

	// NOTE: JDK 1.4 javadoc for ResultSet.getCursorName()
	// says it will throw an execption if the statement
	// cannot support a positioned update. However that
	// line was removed in JavaSE 6 (JDBC 4) javadoc.

	for (int i = 0; i < readOnlySQL.length; i++)
	{
	// The system will not give a cursor name
	// to a read only statement.
	PreparedStatement select = prepareStatement(readOnlySQL[i]);
	ResultSet cursor = select.executeQuery();
	assertNull(readOnlySQL[i], cursor.getCursorName());
	cursor.close();

	// but will if the user supplies one.
	select.setCursorName("PLEASE_UPDATE");
	cursor = select.executeQuery();
	assertEquals(readOnlySQL[i], "PLEASE_UPDATE", cursor.getCursorName());

	// but the cursor is marked as read only so positioned
	// statements will fail.
	assertCompileError("42X23",
	"DELETE FROM T WHERE CURRENT OF PLEASE_UPDATE");

	assertCompileError("42X23",
	"UPDATE T SET I = 3 WHERE CURRENT OF PLEASE_UPDATE");

	cursor.close();
	select.close();
	}
	}
	/**
	* Test delete with the current of statements.
	* Also do some negative testing to see whether correct
	* exceptions are thrown or not.
	* @throws Exception
	*/
	public void testDelete() throws SQLException {
	PreparedStatement select, delete;
	Statement delete1,delete2;
	ResultSet cursor;
	String tableRows = "select i, c from t for read only";

	delete1 = createStatement();
	Object[][] expectedRows = new Object[][]{{new String("1956"),new String("hello world")},
	{new String("456"),new String("hi yourself")},
	{new String("180"),new String("rubber ducky")},
	{new String("3"),new String("you are the one")}};
	JDBC.assertFullResultSet(delete1.executeQuery(tableRows), expectedRows, true);

	select = prepareStatement("select i, c from t for update");
	cursor = select.executeQuery(); // cursor is now open

	// would like to test a delete 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

	assertCompileError("42X28","delete from s where current of " + cursor.getCursorName());

	// TEST: find the cursor during compilation
	delete = prepareStatement("delete from t where current of "
	+ cursor.getCursorName());
	// TEST: delete before the cursor is on a row
	assertStatementError("24000", delete);
	cursor.next();
	assertEquals(1956, cursor.getInt(1));

	// TEST: find the cursor during execution and it is on a row
	assertUpdateCount(delete, 1);
	// skip a row and delete another row so that two rows will
	// have been removed from the table when we are done.
	// skip this row
	cursor.next();
	assertEquals(456, cursor.getInt(1));
	cursor.next();
	assertEquals(180, cursor.getInt(1));
	assertUpdateCount(delete, 1);

	// TEST: delete past the last row
	cursor.next();// skip this row
	assertEquals(3, cursor.getInt(1));
	assertFalse(cursor.next());
	if (usingEmbedded())
	assertStatementError("24000", delete);
	else
	assertStatementError("42X30", delete);


	// TEST: delete off a closed cursor
	// Once this is closed then the cursor no longer exists.
	cursor.close();
	if (usingEmbedded())
	assertStatementError("42X30", delete);
	else
	assertStatementError("XCL16", delete);

	// TEST: no cursor with that name exists
	delete2 = createStatement();
	assertStatementError("42X30", delete2,"delete from t where current of myCursor" );
	expectedRows = new Object[][]{{new String("456"),new String("hi yourself")},
	{new String("3"),new String("you are the one")}};
	JDBC.assertFullResultSet(delete1.executeQuery(tableRows), expectedRows, true);
	delete.close();
	delete2.close();
	select.close();

	// Test a cursor where not all the columns can be updated.
	// Positioned DELETE is still allowed.
	select = prepareStatement("SELECT I, C FROM T FOR UPDATE OF I");
	cursor = select.executeQuery();
	delete = prepareStatement("delete from t where current of "
	+ cursor.getCursorName());
	assertTrue(cursor.next());
	assertUpdateCount(delete, 1);

	delete.close();
	delete1.close();
	cursor.close();
	select.close();


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

	}
	/**
	* Test update with the current of statements.
	* Also do some negative testing to see whether correct
	* exceptions are thrown or not.
	* @throws Exception
	*/
	public void testUpdate() throws SQLException {
	PreparedStatement select;
	PreparedStatement update;
	Statement select1,update2;
	ResultSet cursor;
	String tableRows = "select i, c from t for read only";

	// these are basic tests without a where clause on the select.
	// all rows are in and stay in the cursor's set when updated.

	// 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.

	// TEST: Updated column not found in for update of list

	select = prepareStatement("select I, C from t for update of I");
	cursor = select.executeQuery(); // cursor is now open
	assertCompileError("42X31", "update t set C = 'abcde' where current of "+ cursor.getCursorName());
	cursor.close();
	select.close();

	//Making sure we have the correct rows in the table to begin with
	select1 = createStatement();
	Object[][] expectedRows = new Object[][]{{new String("1956"),new String("hello world")},
	{new String("456"),new String("hi yourself")},
	{new String("180"),new String("rubber ducky")},
	{new String("3"),new String("you are the one")}};
	JDBC.assertFullResultSet(select1.executeQuery(tableRows), expectedRows, true);

	select = 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

	assertCompileError("42X29","update s set i=1 where current of " + cursor.getCursorName());

	// TEST: find the cursor during compilation
	update = 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
	assertStatementError("24000", update);

	// TEST: find the cursor during execution and it is on a row
	cursor.next();
	assertEquals(1956,cursor.getInt(1));
	assertUpdateCount(update, 1);

	// TEST: update an already updated row; expect it to succeed.
	// will it have a cumulative effect?
	assertUpdateCount(update, 1);
	// skip a row and update another row so that two rows will
	// have been removed from the table when we are done.
	cursor.next(); // skip this row
	assertEquals(456,cursor.getInt(1));
	cursor.next();
	assertEquals(180,cursor.getInt(1));
	assertUpdateCount(update, 1);

	// TEST: update past the last row
	cursor.next(); // skip this row
	assertEquals(3,cursor.getInt(1));
	assertFalse(cursor.next());
	assertStatementError("24000", update);

	// TEST: update off a closed cursor
	cursor.close();
	select.close();
	assertStatementError("42X30", update);
	update.close();

	// TEST: no cursor with that name exists
	update2 = createStatement();
	assertStatementError("42X30", update2,"update t set i=1 where current of nosuchcursor");
	update2.close();

	//Verifyin we have the correct updated rows in the table at the end
	expectedRows = new Object[][]{{new String("1976"),new String("Gumby was here")},
	{new String("456"),new String("hi yourself")},
	{new String("190"),new String("Gumby was here")},
	{new String("3"),new String("you are the one")}};
	JDBC.assertFullResultSet(select1.executeQuery(tableRows), expectedRows, true);
	// TEST: attempt to do positioned update before cursor execute'd
	// TBD

	cursor.close();

	}

	/**
	* Test the positioned update correctly recompiles when an index is added.
	*/
	public void testUpdateRecompileCreateIndex() throws Exception
	{
	recompile("UPDATE T SET I = I + 1 WHERE CURRENT OF ",
	"CREATE INDEX IT ON T(I)", null);
	}

	/**
	* Test the positioned update correctly recompiles when the
	* definition of a function is changed.
	*/
	public void testUpdateRecompileChangeFunction() throws Exception
	{
	Statement s = createStatement();
	s.execute("CREATE FUNCTION F(V INTEGER) RETURNS INTEGER " +
	"NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA " +
	"EXTERNAL NAME '" + getClass().getName() + ".doubleValue'");
	commit();
	String changeSQL = "CREATE FUNCTION F(V INTEGER) RETURNS INTEGER " +
	"NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA " +
	"EXTERNAL NAME '" + getClass().getName() + ".tripleValue'";
	int firstI = recompile("UPDATE T SET I = F(I) WHERE CURRENT OF ",
	"DROP FUNCTION F", changeSQL);

	String[][] values = new String[][]
	{{"3"}, {"180"}, {"456"}, {"1956"}};

	if (firstI == 180)
	{
	// 180 doubled to 360
	// 456 tripled to 1368
	values[1] = new String[] {"360"};
	values[2] = new String[] {"1368"};
	}
	else
	{
	// 456 doubled to 912
	// 180 tripled to 540
	values[1] = new String[] {"540"};
	values[2] = new String[] {"912"};

	}

	JDBC.assertFullResultSet(s.executeQuery("SELECT I FROM T ORDER BY I"),
	values);

	s.close();
	}
	/**
	* Test the positioned delete correctly recompiles when an index is added.
	*/
	public void testDeleteRecompileCreateIndex() throws Exception
	{
	recompile("DELETE FROM T WHERE CURRENT OF ",
	"CREATE INDEX IT ON T(I)", null);
	}

	/**
	* Execute a select and then the positioned statement against it.
	* Then execute the changeSQL that should force a recompile of the
	* positioned statement. Then execute the positioned statement
	* again and finally check all is ok with check table.
	*
	* The positioned statements are executed against the rows that
	* have I=180 and I=456.
	*
	* @return the value of I for the first row that had the positioned
	* statement executed against it, ie. before the change SQl was executed.
	*/
	private int recompile(String positionedSQL, String changeSQL1, String changeSQL2)
	throws SQLException
	{
	Statement s = createStatement();
	PreparedStatement select = prepareStatement("select I, C from t for update");
	ResultSet cursor = select.executeQuery();


	PreparedStatement update = prepareStatement(
	positionedSQL + cursor.getCursorName());

	// Execute the positioned statement against one row,
	// either i=180 or 456, which ever comes first.
	int firstRowI = -1;
	while (cursor.next())
	{
	int i = cursor.getInt(1);
	if (i == 180 \|\| i == 456) {
	update.execute();
	firstRowI = i;
	break;
	}
	}
	assertTrue(firstRowI == 180 \|\| firstRowI == 456);

	s.execute(changeSQL1);
	if (changeSQL2 != null)
	s.execute(changeSQL2);

	// And one more execute against one more row
	// either 180 or 456.
	int secondRowI = -1;
	while (cursor.next())
	{
	int i = cursor.getInt(1);
	if (i == 180 \|\| i == 456) {
	update.execute();
	secondRowI = i;
	break;
	}
	}
	assertTrue(firstRowI !=secondRowI);
	assertTrue(secondRowI == 180 \|\| secondRowI == 456);

	update.close();
	cursor.close();
	select.close();
	commit();
	s.close();

	assertCheckTable("T");

	return firstRowI;
	}

	/**
	* Change the current cursor from the one the positioned
	* UPDATE and DELETE was compiled against to one that only has a
	* subset of the columns being updatable.
	*/
	public void testCursorChangeUpdateList() throws SQLException {

	// Update will fail
	cursorChange(
	"42X31",
	"select I, C from t for update",
	"update t set i=i+19, c='OLD' \|\| cast(i as CHAR(20)) where current of ",
	"select I, C from t for update of I"
	);

	// Delete will succeed.
	cursorChange(
	null,
	"select I, C from t for update",
	"DELETE FROM t WHERE CURRENT OF ",
	"select I, C from t for update of I"
	);
	}

	/**
	* Change the current cursor from the one the positioned
	* UPDATE/DELETE was compiled against to one that is read only
	* against the same table.
	*/
	public void testCursorChangeToReadOnly() throws SQLException {

	cursorChange(
	"42X23", // cursor is read only
	"select I, C from t for update",
	"update t set i=i+23 where current of ",
	"select I, C from t for fetch only"
	);

	cursorChange(
	"42X23", // cursor is read only
	"select I, C from t for update",
	"DELETE FROM t WHERE CURRENT OF ",
	"select I, C from t for fetch only"
	);
	}

	/**
	* Change the current cursor from the one the positioned
	* UPDATE was compiled against to one that is against
	* a different table.
	*
	* test FAILS - once fixed should be renamed to lose
	* the FAIL prefix. Fails as the positioned update wor
	*/
	public void testCursorChangeToDifferentTable() throws SQLException {

	Statement s = createStatement();
	s.executeUpdate("INSERT INTO S(I,C) SELECT I,C FROM T");
	s.close();
	commit();

	cursorChange(
	"42X29", // this is the error testUpdate() sees.
	"select I, C from t for update",
	"update t set i=i+23 where current of ",
	"SELECT I, C FROM S FOR UPDATE"
	);

	cursorChange(
	"42X28", // this is the error testDelete() sees.
	"select I, C from t for update",
	"DELETE FROM t WHERE CURRENT OF ",
	"SELECT I, C FROM S FOR UPDATE"
	);

	}


	/**
	* Run cursorChange() with an application provided name
	* and a system provided name.
	*
	*/
	private void cursorChange(String sqlState,
	String initialCursor,
	String positionedStatement,
	String changeToCursor) throws SQLException
	{
	// Since these tests delete rows we add a couple more to
	// ensure any cursor we open has at least one row.
	Statement s = createStatement();
	s.executeUpdate("insert into t values (425, 'apache db derby')");
	s.executeUpdate("insert into t values (280, 'derby-user users')");
	s.close();
	commit();

	cursorChange(sqlState, "CHANGE_ME", initialCursor, positionedStatement, changeToCursor);
	cursorChange(sqlState, null, initialCursor, positionedStatement, changeToCursor);
	}

	/**
	* Test what happens to a positioned update when the cursor
	* it is compiled against changes to the SQL provided, changeToSQL.
	* This test first prepares a cursor initialCursor
	* using with the given name (or system name if null is passed in)
	* A cursor is opened and a positioned update is opened that updates.
	*
	* If sqlState is null then no error is expected and thus the
	* positioned statement must update a single row.
	* Otherwise sqlState is the exected exception for the update.
	*
	* If no error is expected then three rows will be either
	* updated or deleted depending on the positioned statement.
	*
	* If an error is expected then two rows will be updated
	* or deleted.
	*/
	private void cursorChange(String sqlState,
	String cursorName,
	String initialCursor,
	String positionedStatement,
	String changeToCursor) throws SQLException {

	PreparedStatement select = prepareStatement(initialCursor);
	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 = prepareStatement(
	positionedStatement + cursorName);
	assertTrue(cursor.next());
	assertUpdateCount(update, 1);
	cursor.close();

	// now prepare the a cursor with the same name but different SQL.
	PreparedStatement selectdd = prepareStatement(changeToCursor);
	selectdd.setCursorName(cursorName);
	cursor = selectdd.executeQuery();
	assertTrue(cursor.next());
	if (sqlState != null)
	assertStatementError(sqlState,update);
	else
	assertUpdateCount(update, 1);

	cursor.close();

	// now execute the original statement again and the positioned update
	// will work.
	cursor = select.executeQuery();
	cursor.next();
	assertUpdateCount(update, 1);

	cursor.close();
	update.close();
	selectdd.close();
	select.close();

	}

	/*
	** Routines
	*/

	public static int doubleValue(int i)
	{
	return i * 2;
	}
	public static int tripleValue(int i)
	{
	return i * 3;
	}
	}