declareglobaltemptablejava.java

derby database source code.good for you.

		}

		try
		{
			System.out.print("TEST16 : Create a physical SESSION schema, drop it. Next attempt to drop SESSION schema will throw ");
			System.out.println("an exception because now we are dealing with in-memory SESSION schema and it can not be dropped by drop schema.");

			s.executeUpdate("CREATE schema SESSION");
			s.executeUpdate("drop schema SESSION restrict");

			System.out.println("In TEST16, now attempting to drop in-memory SESSION schema");
			s.executeUpdate("drop schema SESSION restrict"); //this should fail

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

		try
		{
			System.out.println("TEST17A : Check constraint not allowed on global temporary table");

			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int check (c21 > 0)) on commit delete rows not logged");

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

		try
		{
			System.out.println("TEST17B : Check constraint allowed on physical SESSION schema table");

			s.executeUpdate("CREATE schema SESSION");
			s.executeUpdate("CREATE TABLE SESSION.t2(c21 int check (c21 > 0))");
			s.executeUpdate("DROP TABLE SESSION.t2");
			s.executeUpdate("drop schema SESSION restrict");

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

		try
		{
			System.out.println("TEST18 : Test declared temporary table with ON COMMIT DELETE ROWS with and without open cursors");
			System.out.println("Tests with holdable cursor are in a different class since holdability support is only under jdk14 and higher");

			System.out.println("Temp table t2 with not holdable cursor open on it. Data should get deleted from t2 at commit time");
			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(22, 22)");
			s.executeUpdate("insert into SESSION.t2 values(23, 23)");

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

			rs2 = s.executeQuery("select * from SESSION.t2"); //eventhough this cursor is open, it is not a hold cursor. Commit should delete the rows
			rs2.next();

			System.out.println("Temp table t3 with no open cursors of any kind on it. Data should get deleted from t3 at commit time");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit delete rows not logged");
			s.executeUpdate("insert into SESSION.t3 values(32, 32)");
			s.executeUpdate("insert into SESSION.t3 values(33, 33)");

			ResultSet rs3 = s.executeQuery("select count(*) from SESSION.t3"); 
      dumpRS(rs3);

			con1.commit();

			System.out.println("After commit, verify the 2 tables");
			System.out.println("Temp table t2 will have no data after commit");
			rs2 = s.executeQuery("select count(*) from SESSION.t2"); 
      dumpRS(rs2);

			System.out.println("Temp table t3 will have no data after commit");
			rs3 = s.executeQuery("select count(*) from SESSION.t3"); 
      dumpRS(rs3);

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

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

		try
		{
			System.out.println("TEST19 : Declare a temporary table with ON COMMIT PRESERVE ROWS with and without open cursors");
			System.out.println("Tests with holdable cursor are in a different class since holdability support is only under jdk14 and higher");

			System.out.println("Temp table t2 with not holdable cursor open on it. Data should be preserved, holdability shouldn't matter");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
			s.executeUpdate("insert into SESSION.t2 values(22, 22)");
			s.executeUpdate("insert into SESSION.t2 values(23, 23)");

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

			rs2 = s.executeQuery("select * from SESSION.t2"); //eventhough this cursor is open, it is not a hold cursor.
			rs2.next();

			System.out.println("Temp table t3 with no open cursors of any kind on it. Data should be preserved, holdability shouldn't matter");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged");
			s.executeUpdate("insert into SESSION.t3 values(32, 32)");
			s.executeUpdate("insert into SESSION.t3 values(33, 33)");

			ResultSet rs3 = s.executeQuery("select count(*) from SESSION.t3"); 
      dumpRS(rs3);

			con1.commit();

			System.out.println("After commit, verify the 2 tables");
			System.out.println("Temp table t2 will have data after commit");
			rs2 = s.executeQuery("select count(*) from SESSION.t2"); 
      dumpRS(rs2);

			System.out.println("Temp table t3 will have data after commit");
			rs3 = s.executeQuery("select count(*) from SESSION.t3"); 
      dumpRS(rs3);

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

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

		try
		{
			System.out.println("TEST20A : CREATE INDEX not allowed on global temporary table.");

			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
			s.executeUpdate("CREATE index t2i1 on SESSION.t2 (c21)");

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

		try
		{
			System.out.println("TEST21A : CREATE INDEX on physical table in SESSION schema should work");

			s.executeUpdate("CREATE schema SESSION");
			s.executeUpdate("CREATE TABLE SESSION.t3 (c31 int)");
			s.executeUpdate("CREATE index t3i1 on SESSION.t3 (c31)");  
			s.executeUpdate("DROP TABLE SESSION.t3");
			s.executeUpdate("drop schema SESSION restrict");

			con1.commit();
			System.out.println("TEST21A 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("TEST21A FAILED");
		}
/*
		try
		{
			System.out.println("TEST22A : CREATE TRIGGER not allowed on global temporary table.");

			s.executeUpdate("CREATE TABLE t1(c11 int)");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
			s.executeUpdate("CREATE TRIGGER t2tr1 before insert on SESSION.t2 for each statement insert into t1 values(1)");

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

		try
		{
			System.out.println("TEST23A : CREATE TRIGGER not allowed on physical table in SESSION schema");

			s.executeUpdate("CREATE schema SESSION");
			s.executeUpdate("CREATE TABLE SESSION.t3 (c31 int)");
			s.executeUpdate("CREATE TABLE SESSION.t4 (c41 int)");
			s.executeUpdate("CREATE TRIGGER t3tr1 before insert on SESSION.t3 for each statement insert into SESSION.t4 values(1)");

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

		try
		{
			System.out.println("TEST24A : Temporary tables can not be referenced in trigger action");

			s.executeUpdate("CREATE TABLE t3 (c31 int)");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4 (c41 int) not logged");
			s.executeUpdate("CREATE TRIGGER t3tr1 before insert on t3 for each statement insert into SESSION.t4 values(1)");

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

		try
		{
			System.out.println("TEST24B : SESSION schema persistent tables can not be referenced in trigger action");

			s.executeUpdate("CREATE TABLE t3 (c31 int)"); //not a SESSION schema table
			s.executeUpdate("CREATE SCHEMA SESSION");
			s.executeUpdate("CREATE TABLE SESSION.t4 (c41 int)");
			s.executeUpdate("CREATE TRIGGER t3tr1 before insert on t3 for each statement delete from SESSION.t4");

			con1.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST24B FAILED");
		} catch (Throwable e)
		{
			System.out.println("Expected message: "+ e.getMessage());
			s.executeUpdate("DROP TABLE t3");
			s.executeUpdate("DROP TABLE SESSION.t4");
			s.executeUpdate("drop schema SESSION restrict");
			con1.commit();
			System.out.println("TEST24B PASSED");
		}
*/
		try
		{
			System.out.println("TEST26A : CREATE VIEW not allowed on global temporary table.");

			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
			s.executeUpdate("CREATE VIEW t2v1 as select * from SESSION.t2");

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

		try
		{
			System.out.println("TEST27A : CREATE VIEW not allowed on physical table in SESSION schema");

			s.executeUpdate("CREATE schema SESSION");
			s.executeUpdate("CREATE TABLE SESSION.t3 (c31 int)");
			s.executeUpdate("CREATE VIEW t3v1 as select * from SESSION.t3");

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

		//Derby424 - Queryplan for a query using SESSION schema view is incorrectly put in statement cache. This 
		//could cause incorrect plan getting executed later if a temp. table is created with that name.
		System.out.println("TEST28A : CREATE VIEW in SESSION schema referencing a table outside of SESSION schema");
		s.executeUpdate("CREATE TABLE t28A (c28 int)");
		s.executeUpdate("INSERT INTO t28A VALUES (280),(281)");
		s.executeUpdate("CREATE VIEW SESSION.t28v1 as select * from t28A");
		System.out.println("SELECT * from SESSION.t28v1 should show contents of view");
		dumpRS(s.executeQuery("SELECT * from SESSION.t28v1"));
		System.out.println("Now declare a global temporary table with same name as the view in SESSION schema");
		s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t28v1(c21 int, c22 int) not logged");
		s.executeUpdate("INSERT INTO SESSION.t28v1 VALUES (280,1),(281,2)");
		System.out.println("SELECT * from SESSION.t28v1 should show contents of global temporary table");
		dumpRS(s.executeQuery("SELECT * from SESSION.t28v1"));
		s.executeUpdate("DROP TABLE SESSION.t28v1");
		System.out.println("We have dropped global temporary table hence SESSION.t28v1 should point to view at this point");
		dumpRS(s.executeQuery("SELECT * from SESSION.t28v1"));
		s.executeUpdate("DROP VIEW SESSION.t28v1");
		con1.rollback();
		con1.commit();
		System.out.println("TEST28A PASSED");

		try