declareglobaltemptablejavajdbc30.java

derby database source code.good for you.

			dumpRS(rs1);

			System.out.println("  update t2 with where clause such that no rows get modified in t2 and inspect the data");
			s.executeUpdate("UPDATE SESSION.t2 SET c22 = 3 where c22>2");
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Rollback to savepoint1 and we should loose all the rows in t1");
			con.rollback(savepoint1);
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			System.out.println("  temp table t2 should also have no rows because attempt was made to modify it (even though nothing actually got modified in t2 in the savepoint)");
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Commit the transaction and should see no data in t1 and t2");
			con.commit();
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			s.executeUpdate("drop table SESSION.t1");
			s.executeUpdate("drop table SESSION.t2");
			con.commit();
			System.out.println("TEST3K PASSED");
		} catch (Throwable e)
		{
			System.out.println("Unexpected message: "+ e.getMessage());
			con.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST3K FAILED");
		}

		try
		{
			System.out.println("TEST3L : Savepoint and Rollback behavior");

			System.out.println(" In the transaction:");
			System.out.println("  declare temp table t1 & t2, insert few rows and commit");
			s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
			s.executeUpdate("insert into SESSION.t1 values(11, 1)");
			s.executeUpdate("insert into session.t2 values(21, 1)");
			ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);
			con.commit();

			System.out.println(" In the next transaction, insert couple more rows in t1 & t2 and ");
			s.executeUpdate("insert into SESSION.t1 values(12, 2)");
			s.executeUpdate("insert into session.t2 values(22, 2)");
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Create savepoint1 and update some rows in t1 and inspect the data");
			Savepoint savepoint1 = con.setSavepoint();
			s.executeUpdate("UPDATE SESSION.t1 SET c12 = 3 where c12>1");
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);

			System.out.println("  update t2 with where clause such that no rows get modified in t2 and inspect the data");
			s.executeUpdate("UPDATE SESSION.t2 SET c22 = 3 where c22>3");
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Rollback to savepoint1 and we should loose all the rows in t1");
			con.rollback(savepoint1);
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			System.out.println("  temp table t2 should also have no rows because attempt was made to modfiy it (even though nothing actually got modified in t2 in the savepoint)");
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Rollback the transaction and should see no data in t1 and t2");
			con.rollback();
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			s.executeUpdate("drop table SESSION.t1");
			s.executeUpdate("drop table SESSION.t2");
			con.commit();
			System.out.println("TEST3L PASSED");
		} catch (Throwable e)
		{
			System.out.println("Unexpected message: "+ e.getMessage());
			con.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST3L FAILED");
		}

		try
		{
			System.out.println("TEST3M : Savepoint and Rollback behavior");

			System.out.println(" In the transaction:");
			System.out.println("  declare temp table t1 & t2 & t3 & t4, insert few rows and commit");
			s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged on rollback delete rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4(c41 int, c42 int) on commit preserve rows not logged on rollback delete rows");
			s.executeUpdate("insert into SESSION.t1 values(11, 1)");
			s.executeUpdate("insert into SESSION.t2 values(21, 1)");
			s.executeUpdate("insert into SESSION.t3 values(31, 1)");
			s.executeUpdate("insert into SESSION.t4 values(41, 1)");
			ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t3");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t4");
			dumpRS(rs1);
			con.commit();

			System.out.println(" In the next transaction, insert couple more rows in t1 & t2 & t3 and ");
			s.executeUpdate("insert into SESSION.t1 values(12, 2)");
			s.executeUpdate("insert into session.t2 values(22, 2)");
			s.executeUpdate("insert into session.t3 values(32, 2)");
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t3");
			dumpRS(rs1);

			System.out.println("  Create savepoint1 and delete some rows from t1 and inspect the data in t1");
			Savepoint savepoint1 = con.setSavepoint();
			s.executeUpdate("DELETE FROM SESSION.t1 where c12>1");
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);

			System.out.println("  Create savepoint2 and delete some rows from t2 this time and inspect the data in t2");
			Savepoint savepoint2 = con.setSavepoint();
			s.executeUpdate("DELETE FROM SESSION.t2 where c22>1");
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Release savepoint2 and now savepoint1 should keep track of changes made to t1 and t2, inspect the data in t1 & t2");
			con.releaseSavepoint(savepoint2);
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Rollback savepoint1 and should see no data in t1 and t2, inspect the data");
			con.rollback(savepoint1);
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Should see data in t3 since it was not touched in the savepoint that was rolled back");
			rs1 = s.executeQuery("select * from SESSION.t3");
			dumpRS(rs1);

			System.out.println("  Rollback the transaction and should see no data in t1 and t2 and t3");
			con.rollback();
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t3");
			dumpRS(rs1);

			System.out.println("  Should see data in t4 since it was not touched in the transaction that was rolled back");
			rs1 = s.executeQuery("select * from SESSION.t4");
			dumpRS(rs1);

			s.executeUpdate("drop table SESSION.t1");
			s.executeUpdate("drop table SESSION.t2");
			s.executeUpdate("drop table SESSION.t3");
			s.executeUpdate("drop table SESSION.t4");
			con.commit();
			System.out.println("TEST3M PASSED");
		} catch (Throwable e)
		{
			System.out.println("Unexpected message: "+ e.getMessage());
			con.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST3M FAILED");
		}

		try
		{
			System.out.println("TEST3N : Savepoint and Rollback behavior");

			System.out.println(" In the transaction:");
			System.out.println("  declare temp table t1 & t2 & t3 & t4, insert few rows and commit");
			s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged on rollback delete rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4(c41 int, c42 int) on commit preserve rows not logged on rollback delete rows");
			s.executeUpdate("insert into SESSION.t1 values(11, 1)");
			s.executeUpdate("insert into SESSION.t1 values(12, 2)");
			s.executeUpdate("insert into SESSION.t2 values(21, 1)");
			s.executeUpdate("insert into SESSION.t2 values(22, 2)");
			s.executeUpdate("insert into SESSION.t3 values(31, 1)");
			s.executeUpdate("insert into SESSION.t4 values(41, 1)");
			ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t3");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t4");
			dumpRS(rs1);
			con.commit();

			System.out.println(" In the next transaction, insert couple more rows in t3 ");
			s.executeUpdate("insert into SESSION.t3 values(31, 2)");
			rs1 = s.executeQuery("select * from SESSION.t3");
			dumpRS(rs1);

			System.out.println("  Create savepoint1 and delete some rows from t1 and inspect the data in t1");
			Savepoint savepoint1 = con.setSavepoint();
			s.executeUpdate("DELETE FROM SESSION.t1 where c12>1");
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);

			System.out.println("  delete from t2 with where clause such that no rows are deleted from t2 and inspect the data in t2");
			s.executeUpdate("DELETE FROM SESSION.t2 where c22>3");
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Create savepoint2 and delete some rows from t2 this time and inspect the data in t2");
			Savepoint savepoint2 = con.setSavepoint();
			s.executeUpdate("DELETE FROM SESSION.t2 where c22>1");
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Rollback the transaction and should see no data in t1 and t2 and t3");
			con.rollback();
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t3");
			dumpRS(rs1);

			System.out.println("  Should see data in t4 since it was not touched in the transaction that was rolled back");
			rs1 = s.executeQuery("select * from SESSION.t4");
			dumpRS(rs1);

			s.executeUpdate("drop table SESSION.t1");
			s.executeUpdate("drop table SESSION.t2");
			s.executeUpdate("drop table SESSION.t3");
			s.executeUpdate("drop table SESSION.t4");
			con.commit();
			System.out.println("TEST3N PASSED");
		} catch (Throwable e)
		{
			System.out.println("Unexpected message: "+ e.getMessage());
			con.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST3N FAILED");
		}

		try
		{
			System.out.println("TEST3O : Savepoint and Rollback behavior");

			System.out.println(" In the transaction:");
			System.out.println("  declare temp table t1 & t2, insert few rows and commit");
			s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
			s.executeUpdate("insert into SESSION.t1 values(11, 1)");
			s.executeUpdate("insert into SESSION.t2 values(21, 1)");
			ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);
			con.commit();

			System.out.println(" In the next transaction, insert couple more rows in t1 ");
			s.executeUpdate("insert into SESSION.t1 values(12, 2)");
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);

			System.out.println("  Create savepoint1 and insert one row in t2 and inspect the data in t2");
			Savepoint savepoint1 = con.setSavepoint();
			s.executeUpdate("insert into SESSION.t2 values(22, 2)");
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Rollback savepoint1 and should see no data in t2 but t1 should have data, inspect the data");
			con.rollback(savepoint1);
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Commit the transaction and should see no data in t2 but t1 should have data");
			con.commit();
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			s.executeUpdate("drop table SESSION.t1");
			s.executeUpdate("drop table SESSION.t2");
			con.commit();
			System.out.println("TEST3O PASSED");
		} catch (Throwable e)
		{
			System.out.println("Unexpected message: "+ e.getMessage());
			con.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST3O FAILED");
		}

		try
		{
			System.out.println("TEST3P : Savepoint and Rollback behavior");

			System.out.println(" In the transaction:");
			System.out.println("  declare temp table t1, insert few rows and commit");
			s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
			s.executeUpdate("insert into SESSION.t1 values(11, 1)");
			s.executeUpdate("insert into SESSION.t1 values(12, 2)");
			con.commit();

			System.out.println(" In the transaction:");
			System.out.println("  Create savepoint1 and insert some rows into t1 and inspect the data in t1");
			Savepoint savepoint1 = con.setSavepoint();
			s.executeUpdate("insert into SESSION.t1 values(13, 3)");

			System.out.println("  Release savepoint1 and now transaction should keep track of changes made to t1, inspect the data in t1");
			con.releaseSavepoint(savepoint1);
			ResultSet rs1 = s.executeQuery("select * from SESSION.t1");