declareglobaltemptablejavajdbc30.java

derby database source code.good for you.

		{
			System.out.println("Expected message: "+ e.getMessage());
			con.commit();
			System.out.println("TEST3A PASSED");
		}

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

			System.out.println(" In the transaction:");
			System.out.println("  Create savepoint1 and declare temp table t1");
			Savepoint savepoint1 = con.setSavepoint();
			s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");

			System.out.println("  Create savepoint2 and declare temp table t2");
			Savepoint savepoint2 = con.setSavepoint();
			s.executeUpdate("declare global temporary table SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");

			System.out.println("  Release savepoint 1 and select from temp table t1 & t2");
      con.releaseSavepoint(savepoint1);
			ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Drop temp table t2(explicit drop), rollback transaction(implicit drop of t1)");
			s.executeUpdate("drop table SESSION.t2");
      con.rollback();

			System.out.println("  Select from temp table t1 and t2 will fail");
			try {
				rs1 = s.executeQuery("select * from SESSION.t1");
			} catch (Throwable e)
			{
				System.out.println("Expected message: "+ e.getMessage());
			}
			try {
				rs1 = s.executeQuery("select * from SESSION.t2");
			} catch (Throwable e)
			{
				System.out.println("Expected message: "+ e.getMessage());
			}
			con.commit();
			System.out.println("TEST3B 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("TEST3B FAILED");
		}

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

			System.out.println(" In the transaction:");
			System.out.println("  Create savepoint1 and declare temp table t1");
			Savepoint savepoint1 = con.setSavepoint();
			s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");

			System.out.println("  Create savepoint2 and declare temp table t2");
			Savepoint savepoint2 = con.setSavepoint();
			s.executeUpdate("declare global temporary table SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");

			System.out.println("  Release savepoint 1 and select from temp table t1 and t2");
      con.releaseSavepoint(savepoint1);
			ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Create savepoint3 and rollback savepoint3(should not touch t1 and t2)");
			Savepoint savepoint3 = con.setSavepoint();
      con.rollback(savepoint3);

			System.out.println("  select from temp tables t1 and t2 should pass");
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Rollback transaction and select from temp tables t1 and t2 should fail");
      con.rollback();
			try {
				rs1 = s.executeQuery("select * from SESSION.t1");
			} catch (Throwable e)
			{
				System.out.println("Expected message: "+ e.getMessage());
			}
			try {
				rs1 = s.executeQuery("select * from SESSION.t2");
			} catch (Throwable e)
			{
				System.out.println("Expected message: "+ e.getMessage());
			}

			con.commit();
			System.out.println("TEST3C 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("TEST3C FAILED");
		}

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

			System.out.println(" In the transaction:");
			System.out.println("  Create savepoint1 and declare temp table t1");
			Savepoint savepoint1 = con.setSavepoint();
			s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");

			System.out.println("  Create savepoint2 and drop temp table t1");
			Savepoint savepoint2 = con.setSavepoint();
			s.executeUpdate("drop table SESSION.t1");

			System.out.println("  Rollback savepoint2 and select temp table t1");
      con.rollback(savepoint2);
			ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);

			System.out.println(" Commit transaction and select temp table t1");
			con.commit();
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);

			s.executeUpdate("drop table SESSION.t1");
			con.commit();
			System.out.println("TEST3D 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("TEST3D FAILED");
		}

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

			System.out.println(" In the transaction:");
			System.out.println("  Create savepoint1 and declare temp table t1");
			Savepoint savepoint1 = con.setSavepoint();
			s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");

			System.out.println("  Create savepoint2 and drop temp table t1");
			Savepoint savepoint2 = con.setSavepoint();
			s.executeUpdate("drop table SESSION.t1");

			System.out.println("  Rollback savepoint 1 and select from temp table t1 should fail");
      con.rollback(savepoint1);
			ResultSet rs1 = s.executeQuery("select * from SESSION.t1");

			con.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST3E FAILED");
		} catch (Throwable e)
		{
			System.out.println("Expected message: "+ e.getMessage());
			con.commit();
			System.out.println("TEST3E PASSED");
		}

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

			System.out.println(" In the transaction:");
			System.out.println("  declare temp table t1 and drop temp table t1");
			s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
			ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			s.executeUpdate("drop table SESSION.t1");
			System.out.println("  rollback, select on t1 should fail");
			con.rollback();
			rs1 = s.executeQuery("select * from SESSION.t1");

			con.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST3F FAILED");
		} catch (Throwable e)
		{
			System.out.println("Expected message: "+ e.getMessage());
			con.commit();
			System.out.println("TEST3F PASSED");
		}

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

			System.out.println(" In the transaction:");
			System.out.println("  declare temp table t1 and commit");
			s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
			ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			con.commit();
			System.out.println(" In the transaction:");
			System.out.println("  drop temp table t1 and rollback, select on t1 should pass");
			s.executeUpdate("drop table SESSION.t1");
			con.rollback();
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);

			s.executeUpdate("drop table SESSION.t1");
			con.commit();
			System.out.println("TEST3G 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("TEST3G FAILED");
		}

		try
		{
			System.out.println("TEST3H : Savepoint and commit behavior");

			System.out.println(" In the transaction:");
			System.out.println("  declare temp table t1 and commit");
			s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
			ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			con.commit();
			System.out.println(" In the transaction:");
			System.out.println("  drop temp table t1 and commit, select on t1 should fail");
			s.executeUpdate("drop table SESSION.t1");
			con.commit();
			rs1 = s.executeQuery("select * from SESSION.t1");

			con.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST3H FAILED");
		} catch (Throwable e)
		{
			System.out.println("Expected message: "+ e.getMessage());
			con.commit();
			System.out.println("TEST3H PASSED");
		}

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

			System.out.println(" In the transaction:");
			System.out.println("  declare temp table t1 and rollback");
			s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
			ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			con.rollback();
			rs1 = s.executeQuery("select * from SESSION.t1");

			con.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST3I FAILED");
		} catch (Throwable e)
		{
			System.out.println("Expected message: "+ e.getMessage());
			con.commit();
			System.out.println("TEST3I PASSED");
		}

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

			System.out.println(" In the transaction:");
			System.out.println("  declare temp table t1 with 2 columns 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, 11)");
			ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			con.commit();
			System.out.println("  Create savepoint1 and drop temp table t1 with 2 columns");
			Savepoint savepoint1 = con.setSavepoint();
			s.executeUpdate("drop table SESSION.t1");
			System.out.println("  declare temp table t1 but this time with 3 columns");
			s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int, c13 int not null) on commit preserve rows not logged");
			s.executeUpdate("insert into SESSION.t1 values(22, 22, 22)");
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			System.out.println("  Create savepoint2 and drop temp table t1 with 3 columns");
			Savepoint savepoint2 = con.setSavepoint();
			s.executeUpdate("drop table SESSION.t1");
			con.rollback();
			System.out.println("  select from temp table t1 here should have 2 columns");
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
			s.executeUpdate("drop table SESSION.t1");

			con.commit();
			System.out.println("TEST3J 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("TEST3J FAILED");
		}

		try
		{
			System.out.println("TEST3K : 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");
			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");