declareglobaltemptablejavajdbc30.java

derby database source code.good for you.

			dumpRS(rs1);

			System.out.println("  Rollback the transaction and should still see no data in t1");
			con.rollback();
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);

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

		try
		{
			System.out.println("TEST3Q : Prepared statement test - DML and rollback behavior");
			System.out.println(" In the transaction:");
			System.out.println("  Declare temp table t2, insert / update / delete data using various prepared statements and commit");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged on commit preserve rows");
			PreparedStatement pStmtInsert = con.prepareStatement("insert into SESSION.t2 values (?, ?)");
			pStmtInsert.setInt(1, 21);
			pStmtInsert.setInt(2, 1);
			pStmtInsert.execute();
			pStmtInsert.setInt(1, 22);
			pStmtInsert.setInt(2, 2);
			pStmtInsert.execute();
			pStmtInsert.setInt(1, 23);
			pStmtInsert.setInt(2, 2);
			pStmtInsert.execute();
			PreparedStatement pStmtUpdate = con.prepareStatement("UPDATE SESSION.t2 SET c22 = 3 where c21=?");
			pStmtUpdate.setInt(1, 23);
			pStmtUpdate.execute();
			PreparedStatement pStmtDelete = con.prepareStatement("DELETE FROM SESSION.t2 where c21 = ?");
			pStmtDelete.setInt(1, 23);
			pStmtDelete.execute();

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

			System.out.println(" In the next transaction:");
			System.out.println("  Create savepoint1 and insert some rows into t2 using prepared statement and inspect the data in t2");
			Savepoint savepoint1 = con.setSavepoint();
			pStmtInsert.setInt(1, 23);
			pStmtInsert.setInt(2, 2);
			pStmtInsert.execute();
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Create savepoint2 and update row inserted in savepoint1 using prepared statement and inspect the data in t2");
			Savepoint savepoint2 = con.setSavepoint();
			pStmtUpdate.setInt(1, 23);
			pStmtUpdate.execute();
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  rollback savepoint2 and should loose all the data from t2");
			con.rollback(savepoint2);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Create savepoint3 and insert some rows into t2 using prepared statement and inspect the data in t2");
			Savepoint savepoint3 = con.setSavepoint();
			pStmtInsert.setInt(1, 21);
			pStmtInsert.setInt(2, 1);
			pStmtInsert.execute();
			pStmtInsert.setInt(1, 22);
			pStmtInsert.setInt(2, 2);
			pStmtInsert.execute();
			pStmtInsert.setInt(1, 23);
			pStmtInsert.setInt(2, 333);
			pStmtInsert.execute();
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Create savepoint4 and update row inserted in savepoint3 using prepared statement and inspect the data in t2");
			Savepoint savepoint4 = con.setSavepoint();
			pStmtUpdate.setInt(1, 23);
			pStmtUpdate.execute();
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			System.out.println("  Release savepoint4 and inspect the data in t2, then delete a row from t2");
			con.releaseSavepoint(savepoint4);
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);
			pStmtDelete.setInt(1, 23);
			pStmtDelete.execute();

			System.out.println("  Commit transaction and should see data data in t2");
			con.commit();
			rs1 = s.executeQuery("select * from SESSION.t2");
			dumpRS(rs1);

			s.executeUpdate("drop table SESSION.t2");
			con.commit();
			System.out.println("TEST3Q PASSED");
		} catch (Throwable e)
		{
			System.out.println("FAIL " + 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("TEST3Q FAILED");
		}

		try
		{
			System.out.println("TEST4 : Test declared temporary table with ON COMMIT DELETE ROWS and holdable cursors and temp table as part of subquery");

			System.out.println("Temp table t1 with no direct held cursor open on it. Data should be deleted from t1 at commit time");
			Statement s1 = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
			ResultSet.HOLD_CURSORS_OVER_COMMIT );
			s1.executeUpdate("create table t1(c11 int, c12 int)");
			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 count(*) from t1"); //should return count of 0
			dumpRS(rs1);
			System.out.println("Insert into real table using temporary table data on a statement with holdability set to true");
			s1.executeUpdate("INSERT INTO T1 SELECT * FROM SESSION.T1");
			con.commit();

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

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

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

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

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

		return passed;
	}

	/**
	 * Test that global temporary tables declared in a connection handle to pooled connection are dropped at connection handle close time
	 * and are not available to next connection handle to the same pooled connection 
	 *
	 * @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 testPooledConnectionClose()
					throws SQLException {
		boolean passed = true;
		Connection con1 = null, con2 = null;

		try
		{
			System.out.println("TEST5 : Temporary tables declared in a pooled connection should get dropped when that pooled connection is closed");
			ConnectionPoolDataSource dsp;
			if (isDerbyNet) {
			/* following would require the IBM universal jdbc driver to be available during build...This section needs to be reworked for networkserver
				com.ibm.db2.jcc.DB2ConnectionPoolDataSource ds = new com.ibm.db2.jcc.DB2ConnectionPoolDataSource();
				ds.setDatabaseName("wombat");
				ds.setUser("cs");
				ds.setPassword("cs");

				ds.setServerName("localhost");
				ds.setPortNumber(1527);
				ds.setDriverType(4);
				dsp = ds;
			*/
				System.out.println("test will not build without universal driver");
				return passed;
			
			} else {
				EmbeddedConnectionPoolDataSource dscsp = new EmbeddedConnectionPoolDataSource();
				dscsp.setDatabaseName("wombat");
				//dscsp.setConnectionAttributes("unicode=true");
				dsp = dscsp;
			}

			PooledConnection pc = dsp.getPooledConnection();
			con1 = pc.getConnection();
			con1.setAutoCommit(false);
			Statement s = con1.createStatement();

			System.out.println(" In the first connection handle to the pooled connection, create physical session schema, create table t1 in it");
			System.out.println(" Insert some rows in physical SESSION.t1 table. Inspect the data.");
			s.executeUpdate("CREATE schema SESSION");
			s.executeUpdate("CREATE TABLE SESSION.t1(c21 int)");
			s.executeUpdate("insert into SESSION.t1 values(11)");
			s.executeUpdate("insert into SESSION.t1 values(12)");
			s.executeUpdate("insert into SESSION.t1 values(13)");
			ResultSet rs1 = s.executeQuery("select * from SESSION.t1"); //should return 3 rows for the physical table
			dumpRS(rs1);

			System.out.println(" Next declare a temp table with same name as physical table in SESSION schema.");
			System.out.println(" Insert some rows in temporary table SESSION.t1. Inspect the data");
			s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
			s.executeUpdate("insert into SESSION.t1 values(11,1)");
			rs1 = s.executeQuery("select * from SESSION.t1"); //should return 1 row for the temporary table
			dumpRS(rs1);
			System.out.println(" Now close the connection handle to the pooled connection");
			con1.commit();
			con1.close();
			con1=null;

			System.out.println(" Do another getConnection() to get a new connection handle to the pooled connection");
			con2 = pc.getConnection();
			s = con2.createStatement();
			System.out.println(" In this new handle, a select * from SESSION.t1 should be looking at the physical session table");
			rs1 = s.executeQuery("select * from SESSION.t1");
			dumpRS(rs1);

			s.executeUpdate("DROP TABLE SESSION.t1");
			if (isDerbyNet)
				s.executeUpdate("DROP TABLE SESSION.t1");

			s.executeUpdate("DROP schema SESSION restrict");
			con2.commit();
			con2.close();
			System.out.println("TEST5 PASSED");
		} catch (Throwable e)
		{
			System.out.println("Unexpected message: "+ e.getMessage());
			if (con1 != null) con1.rollback();
			if (con2 != null) con2.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST5 FAILED");
		}

		return passed;
	}

	static private void dumpExpectedSQLExceptions (SQLException se) {
		System.out.println("PASS -- expected exception");
		while (se != null)
		{
			System.out.println("SQLSTATE("+se.getSQLState()+"): "+se);
			se = se.getNextException();
		}
	}

	static private void dumpSQLExceptions (SQLException se) {
		System.out.println("FAIL -- unexpected exception");
		while (se != null) {
			System.out.print("SQLSTATE("+se.getSQLState()+"):");
			se.printStackTrace(System.out);
			se = se.getNextException();
		}
	}

	// lifted from the metadata test
	private static void dumpRS(ResultSet s) throws SQLException
	{
		if (s == null)
		{
			System.out.println("<NULL>");
			return;
		}

		ResultSetMetaData rsmd = s.getMetaData();

		// Get the number of columns in the result set
		int numCols = rsmd.getColumnCount();

		if (numCols <= 0) 
		{
			System.out.println("(no columns!)");
			return;
		}

		StringBuffer heading = new StringBuffer("\t ");
		StringBuffer underline = new StringBuffer("\t ");

		int len;
		// Display column headings
		for (int i=1; i<=numCols; i++) 
		{
			if (i > 1) 
			{
				heading.append(",");
				underline.append(" ");
			}
			len = heading.length();
			heading.append(rsmd.getColumnLabel(i));
			len = heading.length() - len;
			for (int j = len; j > 0; j--)
			{
				underline.append("-");
			}
		}
		System.out.println(heading.toString());
		System.out.println(underline.toString());
		
	
		StringBuffer row = new StringBuffer();
		// Display data, fetching until end of the result set
		while (s.next()) 
		{
			row.append("\t{");
			// Loop through each column, getting the
			// column data and displaying
			for (int i=1; i<=numCols; i++) 
			{
				if (i > 1) row.append(",");
				row.append(s.getString(i));
			}
			row.append("}\n");
		}
		System.out.println(row.toString());
		s.close();
	}
}