declareglobaltemptablejavajdbc30.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.declareGlobalTempTableJavaJDBC30

   Copyright 2003, 2004 The Apache Software Foundation or its licensors, as applicable.

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.

 */

package org.apache.derbyTesting.functionTests.tests.lang;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.SQLException;

import javax.sql.ConnectionPoolDataSource;
import javax.sql.PooledConnection;

import org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource;

import org.apache.derby.tools.ij;
import org.apache.derby.tools.JDBCDisplayUtil;
import org.apache.derbyTesting.functionTests.util.TestUtil;
/**
 * Test for declared global temporary tables (introduced in Cloudscape 5.2) and pooled connection close and jdbc 3.0 specific features
 * The jdbc3.0 specific featuers are holdable cursors, savepoints.
 * The rest of the temp table test are in declareGlobalTempTableJava class. The reason for a different test
 * class is that the holdability and savepoint support is under jdk14 and higher. But we want to be able to run the non-holdable
 * tests under all the jdks we support and hence splitting the tests into two separate tests. Also, the reason for pooled connection close
 * is because DRDA doesn't yet have support for pooled connection and hence can't pull this test into other temp table test which runs under
 * both DRDA and plain Cloudscape.
 */


public class declareGlobalTempTableJavaJDBC30 {

	static private boolean isDerbyNet = false;

	/*
	** There is a small description prior to each sub-test describing what is being tested.
	*/
	public static void main(String[] args) {
		boolean		passed = true;

		Connection con = null;
		Statement  s = null;

		/* Run all parts of this test, and catch any exceptions */
		try {
			System.out.println("Test declaredGlobalTempTableJava starting");

			/* Load the JDBC Driver class */
			// use the ij utility to read the property file and
			// make the initial connection.
      
			ij.getPropertyArg(args);
			con = ij.startJBMS();
			isDerbyNet = TestUtil.isNetFramework();

			con.setAutoCommit(false);
			s = con.createStatement();

			/* Test temp tables with holdable cursors and with ON COMMIT DELETE ROWS and ON COMMIT PRESERVE ROWS */
			/* Test temp tables rollback behavior in combination with savepoints */
			passed = testHoldableCursorsAndSavepoints(con, s) && passed;

			/* Test pooled connection close behavior */
			passed = testPooledConnectionClose() && passed;

			con.close();

		} catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception "+e);
			JDBCDisplayUtil.ShowException(System.out, e);
			e.printStackTrace();
			passed = false;
		}

		if (passed)
			System.out.println("PASS");

		System.out.println("Test declaredGlobalTempTable finished");
	}

	/**
	 * Test temp tables with holdable cursors and with ON COMMIT DELETE ROWS and ON COMMIT PRESERVE ROWS
	 * Test temp tables rollback behavior in combination with savepoints
	 *
	 * @param conn	The Connection
	 * @param s		A Statement on the Connection
	 *
	 * @return	true if it succeeds, false if it doesn't
	 *
	 * @exception SQLException	Thrown if some unexpected error happens
	 */

	static boolean testHoldableCursorsAndSavepoints(Connection con, Statement s)
					throws SQLException {
		boolean passed = true;

		try
		{
			System.out.println("TEST1 : Test declared temporary table with ON COMMIT DELETE ROWS and holdable cursors");
                     
			System.out.println("Temp table t1 with held open cursors on it. Data should be preserved in t1 at commit time");
      //create a statement with hold cursors over commit
      Statement s1 = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
      ResultSet.HOLD_CURSORS_OVER_COMMIT );
			s1.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
			s1.executeUpdate("insert into session.t1 values(11, 1)");
			s1.executeUpdate("insert into session.t1 values(12, 2)");
			ResultSet rs1 = s1.executeQuery("select count(*) from SESSION.t1"); //should return count of 2
      dumpRS(rs1);

			rs1 = s1.executeQuery("select * from SESSION.t1"); //hold cursor open on t1. Commit should preserve the rows
			rs1.next();

			System.out.println("Temp tables t2 & t3 with one held open cursor on them together. Data should be preserved in t2 & t3 at commit time");
      Statement s2 = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
      ResultSet.HOLD_CURSORS_OVER_COMMIT );
			s2.executeUpdate("declare global temporary table SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
			s2.executeUpdate("insert into session.t2 values(21, 1)");
			s2.executeUpdate("insert into session.t2 values(22, 2)");
			ResultSet rs23 = s2.executeQuery("select count(*) from SESSION.t2"); //should return count of 2
      dumpRS(rs23);

			s2.executeUpdate("declare global temporary table SESSION.t3(c31 int, c32 int) on commit delete rows not logged");
			s2.executeUpdate("insert into session.t3 values(31, 1)");
			s2.executeUpdate("insert into session.t3 values(32, 2)");
			rs23 = s2.executeQuery("select count(*) from SESSION.t3"); //should return count of 2
      dumpRS(rs23);

			rs23 = s2.executeQuery("select * from SESSION.t2, SESSION.t3 where c22=c32"); //hold cursor open on t2 & t3. Commit should preseve the rows
			rs23.next(); 

			System.out.println("Temp table t4 with one held cursor but it is closed before commit. Data should be deleted from t4 at commit time");
      Statement s3 = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
      ResultSet.HOLD_CURSORS_OVER_COMMIT );
			s3.executeUpdate("declare global temporary table SESSION.t4(c41 int, c42 int) on commit delete rows not logged");
			s3.executeUpdate("insert into session.t4 values(41, 1)");
			s3.executeUpdate("insert into session.t4 values(42, 2)");
			ResultSet rs4 = s3.executeQuery("select count(*) from SESSION.t4"); //should return count of 2
      dumpRS(rs4);

			rs4 = s3.executeQuery("select * from SESSION.t4"); //hold cursor open on t4 but close it before commit.
			rs4.next();
			rs4.close();

			con.commit();

			System.out.println("After commit, verify all the 4 tables");

			System.out.println("Temp table t1 will have the data intact after commit");
			rs1 = s1.executeQuery("select count(*) from SESSION.t1"); //should return count of 2
      dumpRS(rs1);

			System.out.println("Temp table t2 will have the data intact after commit");
			rs23 = s2.executeQuery("select count(*) from SESSION.t2"); //should return count of 2
      dumpRS(rs23);

			System.out.println("Temp table t3 will have the data intact after commit");
			rs23 = s2.executeQuery("select count(*) from SESSION.t3"); //should return count of 2
      dumpRS(rs23);

			System.out.println("Temp table t4 will have no data after commit");
			rs4 = s3.executeQuery("select count(*) from SESSION.t4"); //should return count of 0
      dumpRS(rs4);

			s.executeUpdate("drop table SESSION.t1");
			s.executeUpdate("drop table SESSION.t2");
			s.executeUpdate("drop table SESSION.t3");   
			s.executeUpdate("drop table SESSION.t4");

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

		try
		{
			System.out.println("TEST1a : Test declared temporary table with ON COMMIT DELETE ROWS and holdable cursors on prepared statement");

			System.out.println("Temp table t1 with held open cursors on it. Data should be preserved in t1 at commit time");
			Statement s1 = con.createStatement();
			s1.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
			s1.executeUpdate("insert into session.t1 values(11, 1)");
			s1.executeUpdate("insert into session.t1 values(12, 2)");
      
			//create a prepared statement with hold cursors over commit
			PreparedStatement ps1 = con.prepareStatement("select count(*) from SESSION.t1",
			ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT );
			ResultSet rs1 = ps1.executeQuery(); //should return count of 2
			dumpRS(rs1);

			PreparedStatement ps2 = con.prepareStatement("select * from SESSION.t1",
			ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT );
			ResultSet rs11 = ps2.executeQuery(); //hold cursor open on t1. Commit should preserve the rows
			rs11.next(); //notice that we didn't close rs11 with hold cursor on commit

			System.out.println("Temp table t2 with one held cursor but it is closed before commit. Data should be deleted from t2 at commit time");
			s1.executeUpdate("declare global temporary table SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
			s1.executeUpdate("insert into session.t2 values(21, 1)");
			s1.executeUpdate("insert into session.t2 values(22, 2)");
      
			//create a prepared statement with hold cursors over commit
			PreparedStatement ps3 = con.prepareStatement("select count(*) from SESSION.t2",
			ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT );
			ResultSet rs2 = ps3.executeQuery(); //should return count of 2
			dumpRS(rs2);

			PreparedStatement ps4 = con.prepareStatement("select * from SESSION.t2",
			ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT );
			rs2 = ps4.executeQuery(); //hold cursor open on t2 but close it before commit.
			rs2.next();
			rs2.close();

			con.commit();

			System.out.println("After commit, verify both the tables");

			System.out.println("Temp table t1 will have the data intact after commit");
			rs1 = s1.executeQuery("select count(*) from SESSION.t1"); //should return count of 2
			dumpRS(rs1);
			//Need to close the held cursor on t1 before t1 can be dropped
			rs11.close();

			System.out.println("Temp table t2 will have no data after commit");
			rs2 = s1.executeQuery("select count(*) from SESSION.t2"); //should return count of 0
			dumpRS(rs2);

			s.executeUpdate("drop table SESSION.t1");
			s.executeUpdate("drop table SESSION.t2");

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

		try
		{
			System.out.println("TEST2 : Declare a temporary table with ON COMMIT PRESERVE ROWS and various combinations of holdability");

			System.out.println("Temp table t1 with held open cursors on it. Data should be preserved, holdability shouldn't matter");
      //create a statement with hold cursors over commit
      Statement s1 = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
      ResultSet.HOLD_CURSORS_OVER_COMMIT );
			s1.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
			s1.executeUpdate("insert into session.t1 values(11, 1)");
			s1.executeUpdate("insert into session.t1 values(12, 2)");
			ResultSet rs1 = s1.executeQuery("select count(*) from SESSION.t1"); //should return count of 2
      dumpRS(rs1);

			rs1 = s1.executeQuery("select * from SESSION.t1"); //hold cursor open on t1.
			rs1.next();

			con.commit();

			System.out.println("After commit, verify the table");

			System.out.println("Temp table t1 will have data after commit");
			rs1 = s1.executeQuery("select count(*) from SESSION.t1"); //should return count of 2
      dumpRS(rs1);

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

		try
		{
			System.out.println("TEST3A : 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");
			PreparedStatement pStmt = con.prepareStatement("insert into SESSION.t1 values (?, ?)");
      pStmt.setInt(1, 11);
      pStmt.setInt(2, 1);
      pStmt.execute();
			ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);

			System.out.println("  Create savepoint 2, drop temp table t1, rollback savepoint 2");
			Savepoint savepoint2 = con.setSavepoint();
			s.executeUpdate("drop table SESSION.t1");
			try {
				rs1 = s.executeQuery("select * from SESSION.t1");
			} catch (Throwable e)
			{
				System.out.println("Expected message: "+ e.getMessage());
			}
      con.rollback(savepoint2);

			System.out.println("  select should pass, rollback savepoint 1, select should fail");
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);
      con.rollback(savepoint1);
			rs1 = s.executeQuery("select * from SESSION.t1");

			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST3A FAILED");
		} catch (Throwable e)