declareglobaltemptablejava.java

derby database source code.good for you.

			s.executeUpdate("DROP schema SESSION restrict");

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

		try
		{
			System.out.println("TEST6A : RENAME TABLE not allowed on global temporary tables");

			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
			s.executeUpdate("RENAME TABLE SESSION.t2 TO t3");

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

		try
		{
			System.out.println("TEST6B : RENAME TABLE on physical table in SESSION schema should work");

			s.executeUpdate("CREATE schema SESSION");
			s.executeUpdate("CREATE TABLE SESSION.t2(c21 int)");
			s.executeUpdate("RENAME TABLE SESSION.t2 TO t3");
			s.executeUpdate("DROP TABLE SESSION.t3");
			s.executeUpdate("drop schema SESSION restrict");

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

		try
		{
			System.out.println("TEST6C : RENAME COLUMN on physical table in SESSION schema should work");

			s.executeUpdate("CREATE schema SESSION");
			s.executeUpdate("SET schema SESSION");
			s.executeUpdate("CREATE TABLE t2(c21 int)");
			//s.executeUpdate("RENAME COLUMN t2.c21 TO c22");
			s.executeUpdate("SET schema APP");
			s.executeUpdate("DROP TABLE SESSION.t2");
			s.executeUpdate("drop schema SESSION restrict");

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

		try
		{
			System.out.println("TEST8 : generated always as identity not supported for declared global temporary tables");

			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int generated always as identity) 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("TEST8 FAILED");
		} catch (Throwable e)
		{
			System.out.println("Expected message: "+ e.getMessage());
			con1.commit();
			System.out.println("TEST8 PASSED");
		}

		try
		{
			System.out.println("TEST9 : long datatypes not supported for declared global temporary tables");

			try {
				s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 blob(3k)) on commit delete rows not logged");
			} catch (Throwable e)
			{
				System.out.println("  Expected exception. Attempted to declare a temp table with blob. " + e.getMessage());
			}

			try {
				s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 clob(3k)) on commit delete rows not logged");
			} catch (Throwable e)
			{
				System.out.println("  Expected exception. Attempted to declare a temp table with clob. " + e.getMessage());
			}

			try {
				s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 long varchar) on commit delete rows not logged");
			} catch (Throwable e)
			{
				System.out.println("  Expected exception. Attempted to declare a temp table with long varchar. " + e.getMessage());
			}

			try {
				s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 \"org.apache.derbyTesting.functionTests.util.ShortHolder\") on commit delete rows not logged");
			} catch (Throwable e)
			{
				System.out.println("  Expected exception. Attempted to declare a temp table with user defined type. " + e.getMessage());
			}

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

		try
		{
			System.out.println("TEST10A : Primary key constraint not allowed on a declared global temporary table.");

			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int not null, constraint pk primary key (c21)) 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("TEST10A FAILED");
		} catch (Throwable e)
		{
			System.out.println("Expected message: "+ e.getMessage());
			con1.commit();
			System.out.println("TEST10A PASSED");
		}

		try
		{
			System.out.println("TEST10B : Primary key constraint allowed on a physical table in SESSION schema.");

			s.executeUpdate("CREATE SCHEMA SESSION");
			s.executeUpdate("CREATE TABLE SESSION.t2(c21 int not null, constraint pk primary key (c21))");
			s.executeUpdate("DROP TABLE SESSION.t2");
			s.executeUpdate("drop schema SESSION restrict");

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

		try
		{
			System.out.println("TEST10C : Unique key constraint not allowed on a declared global temporary table.");

			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int not null unique) 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("TEST10C FAILED");
		} catch (Throwable e)
		{
			System.out.println("Expected message: "+ e.getMessage());
			con1.commit();
			System.out.println("TEST10C PASSED");
		}

		try
		{
			System.out.println("TEST10D : Foreign key constraint not allowed on a declared global temporary table.");

			s.executeUpdate("CREATE TABLE t1(c11 int not null unique)");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int references t1(c11)) 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("TEST10D FAILED");
		} catch (Throwable e)
		{
			System.out.println("Expected message: "+ e.getMessage());
			s.executeUpdate("DROP TABLE t1");
			con1.commit();
			System.out.println("TEST10D PASSED");
		}

		try
		{
			System.out.println("TEST11 : Attempt to declare the same global temporary table twice will fail. Plan to support WITH REPLACE in future");

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

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

		try
		{
			System.out.println("TEST12 : Try to drop a declared global temporary table that doesn't exist.");

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

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

		try
		{
			System.out.println("TEST13A : insert into declared global temporary table will pass.");

			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 char(2)) on commit delete rows not logged");
			s.executeUpdate("insert into SESSION.t2 values (1, 'aa')");
			s.executeUpdate("insert into SESSION.t2 values (2, 'bb'),(3, 'cc'),(4, null)");
			s.executeUpdate("CREATE TABLE t1(c11 int, c22 char(2))");
			s.executeUpdate("insert into t1 values (5, null),(6, null),(7, 'gg')");
			s.executeUpdate("insert into SESSION.t2 (select * from t1 where c11>4)");
			s.executeUpdate("insert into SESSION.t2 select * from SESSION.t2");
			ResultSet rs1 = s.executeQuery("select sum(c21) from SESSION.t2");
      dumpRS(rs1);
			s.executeUpdate("DROP TABLE SESSION.t2");
			s.executeUpdate("DROP TABLE t1");

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

		try
		{
			System.out.println("TEST13B : attempt to insert null into non-null column in declared global temporary table will fail.");
			System.out.println("Declare the table with non-null column, insert a row and commit");

			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 char(2) not null) on commit delete rows not logged");
			s.executeUpdate("insert into SESSION.t2 values (1, 'aa')");
			con1.commit();
			System.out.println("In the next transaction, attempt to insert a null value in the table will fail and we will loose all the rows from the table as part of internal rollback");
			s.executeUpdate("insert into SESSION.t2 values (2, null)");

			con1.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST13B FAILED");
		} catch (Throwable e)
		{
			System.out.println("Expected message: "+ e.getMessage());
			System.out.println("should see no data in t2");

			ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);
			s.executeUpdate("DROP TABLE SESSION.t2");
			con1.commit();
			System.out.println("TEST13B PASSED");
		}

		try
		{
			System.out.println("TEST13C : declare a temporary table with default and then insert into it.");

			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 char(2) default 'aa', c23 varchar(20) default user ) on commit delete rows not logged");
			s.executeUpdate("insert into SESSION.t2 values (1, 'aa', null)");
			s.executeUpdate("insert into SESSION.t2(c21) values (2)");
			ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);

			s.executeUpdate("DROP TABLE SESSION.t2");
			con1.commit();
			System.out.println("TEST13C 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("TEST13C FAILED");
		}

		try
		{
			System.out.println("TEST14 : Should be able to create Session schema manually.");

			s.executeUpdate("CREATE schema SESSION");

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

		try
		{
			System.out.println("TEST15 : Session schema can be dropped like any other user-defined schema.");

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

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