declareglobaltemptablejava.java

derby database source code.good for you.

			con1.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST37B FAILED");
		} catch (Throwable e)
		{
			System.out.println("Expected message: "+ e.getMessage());
			s.executeUpdate("DROP TABLE SESSION.t2");
			con1.commit();
			System.out.println("TEST37B PASSED");
		}

		try
		{
			System.out.println("TEST38A : Rollback behavior - declare temp table, rollback, select should fail");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
			PreparedStatement pStmt = con1.prepareStatement("insert into SESSION.t2 values (?, ?)");
      pStmt.setInt(1, 21);
      pStmt.setInt(2, 1);
      pStmt.execute();

			ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			con1.rollback();

			System.out.println("TEST38A : select should fail since temp table got dropped as part of rollback");
			rs1 = s.executeQuery("select * from SESSION.t2"); //no temp table t2, should fail

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

		try
		{
			System.out.println("TEST38B : Rollback behavior - declare temp table, commit, drop temp table, rollback, select should pass");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
			PreparedStatement pStmt = con1.prepareStatement("insert into SESSION.t2 values (?, ?)");
      pStmt.setInt(1, 21);
      pStmt.setInt(2, 1);
      pStmt.execute();

			ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			con1.commit();

			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			s.executeUpdate("DROP TABLE SESSION.t2");

			con1.rollback();
			System.out.println("TEST38B : select should pass since temp table drop was rolled back");
			rs1 = s.executeQuery("select * from SESSION.t2"); //no temp table t2, should fail
			dumpRS(rs1);

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

		try
		{
			System.out.println("TEST38C : Rollback behavior");
			System.out.println(" In the transaction:");
			System.out.println("  Declare temp table t2 with 3 columns");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int, c23 int) on commit preserve rows not logged");
			s.executeUpdate("insert into session.t2 values(1,1,1)");
			ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);
			System.out.println("  Drop temp table t2 (with 3 columns)");
			s.executeUpdate("DROP TABLE SESSION.t2");
			try {
				rs1 = s.executeQuery("select * from SESSION.t2");
			} catch (Throwable e)
			{
				System.out.println("  Attempted to select from temp table t2 but it failed as expected with exception " + e.getMessage());
			}
			System.out.println("  Declare temp table t2 again but this time with 2 columns");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);
			System.out.println(" Commit the transaction. Should have temp table t2 with 2 columns");
			con1.commit();

			System.out.println(" In the next transaction:");
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);
			System.out.println("  Drop temp table t2 (with 2 columns)");
			s.executeUpdate("DROP TABLE SESSION.t2");
			System.out.println("  Declare temp table t2 again but this time with 1 column");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);
			System.out.println(" Rollback this transaction. Should have temp table t2 with 2 columns");
			con1.rollback();

			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);
			s.executeUpdate("DROP TABLE SESSION.t2");

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

		try
		{
			System.out.println("TEST38D : Rollback behavior for tables touched with DML");
			System.out.println(" In the transaction:");
			System.out.println("  Declare temp table t2 & t3 & t4 & t5 with preserve rows, insert data and commit");
			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) not logged on commit preserve rows on rollback delete rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4(c41 int, c42 int) not logged on rollback delete rows on commit preserve rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t5(c51 int, c52 int) on commit preserve rows not logged");
			s.executeUpdate("insert into session.t2 values(21,1)");
			s.executeUpdate("insert into session.t2 values(22,2)");
			s.executeUpdate("insert into session.t2 values(23,3)");
			s.executeUpdate("insert into session.t3 values(31,1)");
			s.executeUpdate("insert into session.t3 values(32,2)");
			s.executeUpdate("insert into session.t3 values(33,3)");
			s.executeUpdate("insert into session.t4 values(41,1)");
			s.executeUpdate("insert into session.t4 values(42,2)");
			s.executeUpdate("insert into session.t4 values(43,3)");
			s.executeUpdate("insert into session.t5 values(51,1)");
			s.executeUpdate("insert into session.t5 values(52,2)");
			s.executeUpdate("insert into session.t5 values(53,3)");
			ResultSet 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);
			rs1 = s.executeQuery("select * from SESSION.t5");
			dumpRS(rs1);
			con1.commit();

			System.out.println(" In the next transaction:");
			System.out.println("  Declare temp table t6 with preserve rows, insert data and inspect data in all the tables");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t6(c61 int, c62 int) on commit preserve rows not logged on rollback delete rows");
			s.executeUpdate("insert into session.t6 values(61,1)");
			s.executeUpdate("insert into session.t6 values(62,2)");
			s.executeUpdate("insert into session.t6 values(63,3)");
			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);
			rs1 = s.executeQuery("select * from SESSION.t5");
			dumpRS(rs1);
			rs1 = s.executeQuery("select * from SESSION.t6");
			dumpRS(rs1);

			System.out.println("  delete from t2 with t5 in it's where clause, look at t2");
			s.executeUpdate("DELETE FROM session.t2 WHERE c22> (select c52 from session.t5 where c52=2)");
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  delete with where clause from t3 so that no rows get deleted, look at the rows");
			s.executeUpdate("DELETE FROM session.t3 WHERE c32>3");
			rs1 = s.executeQuery("select * from SESSION.t3");
			dumpRS(rs1);

			System.out.println("  do not touch t4");

			System.out.println("  rollback this transaction, should not see any rows in temp table t2 after rollback");
			con1.rollback();
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  temp table t3 should have no rows because attempt was made to delete from it (even though nothing actually got deleted from it in the transaction)");
			rs1 = s.executeQuery("select * from SESSION.t3");
			dumpRS(rs1);

			System.out.println("  temp table t4 should have its data intact because it was not touched in the transaction that got rolled back");
			rs1 = s.executeQuery("select * from SESSION.t4");
			dumpRS(rs1);

			System.out.println("  temp table t5 should have its data intact because it was only used in where clause and not touched in the transaction that got rolled back");
			rs1 = s.executeQuery("select * from SESSION.t5");
			dumpRS(rs1);

			System.out.println("  temp table t6 got dropped as part of rollback of this transaction since it was declared in this same transaction");
			try {
				rs1 = s.executeQuery("select * from SESSION.t6");
			} catch (Throwable e)
			{
				System.out.println("  Attempted to select from temp table t6 but it failed as expected with exception " + e.getMessage());
			}

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

		try
		{
			System.out.println("TEST39A : Verify that there is no entry in system catalogs for temporary tables");
			System.out.println(" Declare a temp table T2 and check system catalogs. Shouldn't find anything. Then drop the temp table");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
			ResultSet rs1 = s.executeQuery("select * from sys.systables where tablename like 'T2'");
			dumpRS(rs1);
			rs1 = s.executeQuery("select tablename, schemaname from sys.systables t, sys.sysschemas s where t.tablename like 'T2' and t.schemaid=s.schemaid");
			dumpRS(rs1);
			s.executeUpdate("DROP TABLE SESSION.t2");
			System.out.println(" Create physical schema SESSION, create a physical table T2 in SESSION schema and check system catalogs. Should be there");
			s.executeUpdate("CREATE SCHEMA SESSION");
			s.executeUpdate("CREATE TABLE SESSION.t2(c21 int, c22 int)");
			rs1 = s.executeQuery("select * from sys.systables where tablename like 'T2'");
			dumpRS(rs1);
			s.executeUpdate("DROP TABLE SESSION.t2");
			s.executeUpdate("drop schema SESSION restrict");

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

		try
		{
			System.out.println("TEST39B : Verify that there is no entry in system catalogs for SESSION schmea after declare table");
			System.out.println(" Declare a temp table T2 and check system catalogs for SESSION schmea. Shouldn't find anything. Then drop the temp table");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
			ResultSet rs1 = s.executeQuery("select schemaname from sys.sysschemas where schemaname like 'SESSION'");
			dumpRS(rs1);
			s.executeUpdate("DROP TABLE SESSION.t2");

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

		try
		{
			System.out.println("TEST40 : DatabaseMetaData.getTables() should not return temporary tables");
			DatabaseMetaData databaseMetaData;
			databaseMetaData = con1.getMetaData();
			s.executeUpdate("CREATE SCHEMA SESSION");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
			s.executeUpdate("CREATE TABLE SESSION.t3(c31 int, c32 int)");
			System.out.println("getTables() with no types:");
 			dumpRS(databaseMetaData.getTables("", null, "%", null));

			s.executeUpdate("DROP TABLE SESSION.t2");
			s.executeUpdate("DROP TABLE SESSION.t3");
			s.executeUpdate("drop schema SESSION restrict");
			con1.commit();
			System.out.println("TEST40 PASSED");
		} catch (Throwable e)
		{
			System.out.println("Unexpected message: "+ e.getMessage());
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST40 FAILED");
		}   

		try
		{
			System.out.println("TEST41 : delete where current of on temporary tables");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
			s.executeUpdate("insert into SESSION.t2 values(21, 1)");
			s.executeUpdate("insert into SESSION.t2 values(22, 1)");
			ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);
			PreparedStatement pStmt1 = con1.prepareStatement("select c21 from session.t2 for update");
			ResultSet rs2 = pStmt1.executeQuery();
			rs2.next();
			PreparedStatement pStmt2 = con1.prepareStatement("delete from session.t2 where current of "+
									   rs2.getCursorName());
			pStmt2.executeUpdate();
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);
			rs2.next();
			pStmt2.executeUpdate();
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			rs2.close();
			s.executeUpdate("DROP TABLE SESSION.t2");
			con1.commit();
			System.out.println("TEST41 PASSED");
		} catch (Throwable e)
		{
			System.out.println("Unexpected message: "+ e.getMessage());
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST41 FAILED");
		}

		try
		{
			System.out.println("TEST42 : update where current of on temporary tables");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
			s.executeUpdate("insert into SESSION.t2 values(21, 1)");
			s.executeUpdate("insert into SESSION.t2 values(22, 1)");
			ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);
			PreparedStatement pStmt1 = con1.prepareStatement("select c21 from session.t2 for update");
			ResultSet rs2 = pStmt1.executeQuery();
			rs2.next();
			PreparedStatement pSt