declareglobaltemptablejava.java

derby database source code.good for you.

		{
			System.out.println("TEST29A : DELETE FROM global temporary table allowed.");

			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 decimal) not logged");
			s.executeUpdate("insert into SESSION.t2 values(1, 1.1)");
			s.executeUpdate("insert into SESSION.t2 values(2, 2.2)");

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

			s.executeUpdate("DELETE FROM SESSION.t2 where c21 > 0");

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

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

		try
		{
			System.out.println("TEST31A : UPDATE on global temporary table allowed.");

			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
			s.executeUpdate("insert into SESSION.t2 values(1, 1)");
			s.executeUpdate("insert into SESSION.t2 values(2, 1)");

			ResultSet rs2 = s.executeQuery("select count(*) from SESSION.t2 where c22 = 1");
			rs2.next();
			if (rs2.getInt(1) != 2)
				System.out.println("TEST31A FAILED: count should have been 2.");

			s.executeUpdate("UPDATE SESSION.t2 SET c22 = 2 where c21>0");

			rs2 = s.executeQuery("select count(*) from SESSION.t2 where c22 = 1");
			rs2.next();
			if (rs2.getInt(1) != 0)
				System.out.println("TEST31A FAILED: count should have been 0.");

			rs2 = s.executeQuery("select count(*) from SESSION.t2 where c22 = 2");
			rs2.next();
			if (rs2.getInt(1) != 2)
				System.out.println("TEST31A FAILED: count should have been 2.");

			s.executeUpdate("DROP TABLE SESSION.t2");
			con1.commit();
			System.out.println("TEST31A 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("TEST31A FAILED");
		}
/*
		try
		{
			System.out.println("TEST32A : SET TRIGGERS not allowed on global temporary tables");

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

			con1.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST32A FAILED");
		} catch (Throwable e)
		{
			System.out.println("Expected message: "+ e.getMessage());
			s.executeUpdate("DROP TABLE SESSION.t2");
			con1.commit();
			System.out.println("TEST32A PASSED");
		}
		try
		{
			System.out.println("TEST32C : SET TRIGGERS on physical table in SESSION schema should work");

			s.executeUpdate("CREATE schema SESSION");
			s.executeUpdate("CREATE TABLE SESSION.t2(c21 int)");
			s.executeUpdate("SET TRIGGERS FOR SESSION.t2 ENABLED");
			s.executeUpdate("DROP TABLE SESSION.t2");
			s.executeUpdate("drop schema SESSION restrict");

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

		System.out.println("Multiple tests to make sure we do not do statement caching for statement referencing SESSION schema tables");
		try
		{
			System.out.println("TEST34A : CREATE physical table and then DECLARE GLOBAL TEMPORARY TABLE with the same name in session schema.");

			con1.setAutoCommit(true);
			//Need to do following 3 in autocommit mode otherwise the data dictionary will be in write mode and statements won't get
			//cached. I need to have statement caching enabled here to make sure that tables with same names do not conflict
			s.executeUpdate("CREATE schema SESSION");
			s.executeUpdate("CREATE TABLE SESSION.t2 (c21 int)");
			s.executeUpdate("INSERT into SESSION.t2 values(21)");

			con1.setAutoCommit(false);
			//select will return data from physical table t2
			s.execute("select * from SESSION.t2");
			dumpRS(s.getResultSet());

			//declare temporary table with same name as a physical table in SESSION schema
			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)");
			//select will return data from temp table t2
			s.execute("select c21,c22 from SESSION.t2");
			dumpRS(s.getResultSet());
			//select will return data from temp table t2
			s.execute("select * from SESSION.t2");
			dumpRS(s.getResultSet());

			//drop the temp table t2
			s.executeUpdate("DROP TABLE SESSION.t2");
			//select will return data from physical table t2 because temp table has been deleted
			s.execute("select * from SESSION.t2");
			dumpRS(s.getResultSet());

			//cleanup
			s.executeUpdate("DROP TABLE SESSION.t2");
			s.executeUpdate("drop schema SESSION restrict");
			con1.commit();
			System.out.println("TEST34A PASSED");
		} catch (Throwable e)
		{
			System.out.println("Unexpected message: " + e.getMessage());
			con1.rollback();
			passed = false; //we shouldn't have reached here. Return false to indicate failure
			System.out.println("TEST34A FAILED");
		}
		try
		{
			System.out.println("TEST34B : Physical table & TEMPORARY TABLE with the same name in session schema, try insert.");

			con1.setAutoCommit(true);
			//Need to do following 3 in autocommit mode otherwise the data dictionary will be in write mode and statements won't get
			//cached. I need to have statement caching enabled here to make sure that tables with same names do not conflict
			s.executeUpdate("CREATE schema SESSION");
			s.executeUpdate("CREATE TABLE SESSION.t2 (c21 int)");
			s.executeUpdate("INSERT into SESSION.t2 values(21)");

			con1.setAutoCommit(false);
			//select will return data from physical table t2
			s.execute("select * from SESSION.t2");
			dumpRS(s.getResultSet());

			//declare temporary table with same name as a physical table in SESSION schema
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
			//select will return data from temp table t2
			s.execute("select * from SESSION.t2");
			dumpRS(s.getResultSet());
			s.executeUpdate("INSERT into SESSION.t2 values(99)");
			s.execute("select * from SESSION.t2");
			dumpRS(s.getResultSet());

			//drop the temp table t2
			s.executeUpdate("DROP TABLE SESSION.t2");
			//select will return data from physical table t2 because temp table has been deleted
			s.execute("select * from SESSION.t2");
			dumpRS(s.getResultSet());

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

		try
		{
			System.out.println("TEST35A : Temporary table created in one connection should not be available in another connection");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
			s.executeUpdate("insert into SESSION.t2 values(22, 22)");

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

			Statement s2 = con2.createStatement();
			ResultSet rs2 = s2.executeQuery("select count(*) from SESSION.t2"); //con2 should not find temp table declared in con1

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

		try
		{
			System.out.println("TEST35B : Temp table in one connection should not conflict with temp table with same name in another connection");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
			s.executeUpdate("insert into SESSION.t2 values(22, 22)");

			ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1); //should return 22, 22

			Statement s2 = con2.createStatement();
			s2.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) not logged");
			s2.executeUpdate("insert into SESSION.t2 values(99)");
			ResultSet rs2 = s2.executeQuery("select * from SESSION.t2");
      dumpRS(rs2); //should return 99

			rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1); //should return 22, 22

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

		try
		{
			System.out.println("TEST36 : After creating SESSION schema and making it current schema, temporary tables should not require SESSION qualification");

			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
			s.executeUpdate("insert into SESSION.t2 values(21, 21)");
			s.executeUpdate("insert into SESSION.t2 values(22, 22)");

			ResultSet rs1 = s.executeQuery("select count(*) from SESSION.t2");
			rs1.next();
			if (rs1.getInt(1) != 2)
				System.out.println("TEST36 FAILED: count should have been 2.");

			s.executeUpdate("CREATE SCHEMA SESSION");
			s.executeUpdate("SET SCHEMA SESSION");

			rs1 = s.executeQuery("select count(*) from t2"); //no need to qualify temp table here because we are in SESSION schema
			rs1.next();
			if (rs1.getInt(1) != 2)
				System.out.println("TEST36 FAILED: count should have been 2.");

			s.executeUpdate("DROP TABLE t2");
			s.executeUpdate("SET SCHEMA APP");
			s.executeUpdate("drop schema SESSION restrict");
			con1.commit();
			System.out.println("TEST36 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("TEST36 FAILED");
		}

		try
		{
			System.out.println("TEST37A : Prepared statement test - drop the temp table underneath");
			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);

			s.executeUpdate("DROP TABLE SESSION.t2");
      pStmt.setInt(1, 22);
      pStmt.setInt(2, 2);
      pStmt.execute();
			System.out.println("TEST37A : Should not reach here because SESSION.t2 has been dropped underneath the prepared statement");

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

		try
		{
			System.out.println("TEST37B : Prepared statement test - drop and recreate the temp table with different definition underneath");
			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);

			s.executeUpdate("DROP TABLE SESSION.t2");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int, c23 int) not logged");
      pStmt.setInt(1, 22);
      pStmt.setInt(2, 2);
      pStmt.execute();

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

			s.executeUpdate("DROP TABLE SESSION.t2");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int, c23 int, c24 int not null) not logged");
      pStmt.setInt(1, 22);
      pStmt.setInt(2, 2);
      pStmt.execute();
			System.out.println("TEST37B : Should not reach here because SESSION.t2 has been recreated with not null column");