You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by my...@apache.org on 2009/09/10 19:56:23 UTC
svn commit: r813528 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests:
master/ suites/ tests/lang/
Author: myrnavl
Date: Thu Sep 10 17:56:22 2009
New Revision: 813528
URL: http://svn.apache.org/viewvc?rev=813528&view=rev
Log:
DERBY-3895 ; convert lang/declareGlobalTempTableJavaJDBC30.java to JUnit
original patch contributed by Ravinder Reddy
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DeclareGlobalTempTableJavaJDBC30Test.java (with props)
Removed:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/declareGlobalTempTableJavaJDBC30.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/declareGlobalTempTableJavaJDBC30.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/declareGlobalTempTableJavaJDBC30_app.properties
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DeclareGlobalTempTableJavaTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?rev=813528&r1=813527&r2=813528&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall Thu Sep 10 17:56:22 2009
@@ -8,7 +8,6 @@
lang/cursorerrors.sql
lang/db2Compatibility.sql
lang/ddlTableLockMode.sql
-lang/declareGlobalTempTableJavaJDBC30.java
lang/desc_index.sql
lang/dropTable.sql
lang/emptyStatistics.sql
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DeclareGlobalTempTableJavaJDBC30Test.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DeclareGlobalTempTableJavaJDBC30Test.java?rev=813528&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DeclareGlobalTempTableJavaJDBC30Test.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DeclareGlobalTempTableJavaJDBC30Test.java Thu Sep 10 17:56:22 2009
@@ -0,0 +1,931 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.functionTests.tests.lang.declareGlobalTempTableJavaJDBC30Test
+
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You 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.Savepoint;
+import java.sql.Statement;
+import java.sql.SQLException;
+
+import javax.sql.ConnectionPoolDataSource;
+import javax.sql.PooledConnection;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.junit.*;
+
+/**
+ * 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 declareGlobalTempTableJavaTest 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.
+ */
+
+
+public class DeclareGlobalTempTableJavaJDBC30Test extends BaseJDBCTestCase {
+
+ public DeclareGlobalTempTableJavaJDBC30Test(String name)
+ {
+ super(name);
+ }
+ public static Test suite() {
+ TestSuite suite = new TestSuite();
+ suite.addTest(TestConfiguration.embeddedSuite(
+ DeclareGlobalTempTableJavaJDBC30Test.class));
+ suite.addTest(TestConfiguration.clientServerDecorator(
+ getClientSuite()));
+ return suite;
+ }
+
+ /**
+ * Return a suite of tests that are run with client only
+ *
+ * @return A suite of tests being run with client only
+ */
+ private static Test getClientSuite() {
+ TestSuite suite = new TestSuite("Client/Server");
+ // skip the tests with more than 1 savepoint -
+ // see (lack of support described in) DERBY-3687
+ // so, only do the following with network server/client:
+ suite.addTest(new DeclareGlobalTempTableJavaJDBC30Test(
+ "testOnCommitPreserveRowsWithHoldability"));
+ suite.addTest(new DeclareGlobalTempTableJavaJDBC30Test(
+ "testSavepointRollbackbehaviour7"));
+ suite.addTest(new DeclareGlobalTempTableJavaJDBC30Test(
+ "testSavepointRollbackbehaviour8"));
+ suite.addTest(new DeclareGlobalTempTableJavaJDBC30Test(
+ "testSavepointRollbackbehaviour11"));
+ suite.addTest(new DeclareGlobalTempTableJavaJDBC30Test(
+ "testSavepointRollbackbehaviour12"));
+ suite.addTest(new DeclareGlobalTempTableJavaJDBC30Test(
+ "testTest4"));
+ suite.addTest(new DeclareGlobalTempTableJavaJDBC30Test(
+ "testPooledConnectionClosed"));
+
+ // following 2 fail with network server; see DERBY-4373
+ /*
+ suite.addTest(new DeclareGlobalTempTableJavaJDBC30Test(
+ "testOnCommitDeleteRowsWithHoldableOpenCursor"));
+ suite.addTest(new DeclareGlobalTempTableJavaJDBC30Test(
+ "testOnCommitDeleteRowsHoldabilityWithPreparedStatements"));
+ */
+ return suite;
+ }
+
+ protected void setUp() throws Exception {
+ super.setUp();
+ getConnection().setAutoCommit(false);
+ }
+ protected void tearDown() throws Exception {
+ super.tearDown();
+ }
+ /**
+ * Tests that If a Global Temp table(with on commi delete rows) has open
+ * cursors held on it
+ * Data should be preserved in at commit time
+ *
+ * @throws SQLException
+ */
+ public void testOnCommitDeleteRowsWithHoldableOpenCursor()
+ throws SQLException {
+ Statement s1 = getConnection().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)");
+ JDBC.assertSingleValueResultSet(s1.executeQuery(
+ "select count(*) from SESSION.t1") , "2");
+ //hold cursor open on t1. Commit should preserve the rows
+ ResultSet rs1 = s1.executeQuery("select * from SESSION.t1");
+ rs1.next();
+ //cursor opened
+ //Temp tables t2 & t3 with one held open cursor on them together. Data
+ //should be preserved in t2 & t3 at commit time
+ Statement s2 = getConnection().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)");
+ JDBC.assertSingleValueResultSet(s2.executeQuery(
+ "select count(*) from SESSION.t2"), "2");
+ 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)");
+ JDBC.assertSingleValueResultSet(s2.executeQuery(
+ "select count(*) from SESSION.t3") , "2");
+ //hold cursor open on t2 & t3. Commit should preseve the rows
+ ResultSet rs23 = s2.executeQuery(
+ "select * from SESSION.t2, SESSION.t3 where c22=c32");
+ rs23.next();
+ //cursor Opened
+ Statement s3 = getConnection().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");
+ JDBC.assertSingleValueResultSet(rs4 , "2");
+ //hold cursor open on t4 but close it before commit,
+ //Data should be deleted after commit
+ rs4 = s3.executeQuery("select * from SESSION.t4");
+ rs4.next();
+ rs4.close();
+ //rs4 closed before committing.
+ commit();
+ JDBC.assertSingleValueResultSet(s1.executeQuery(
+ "select count(*) from SESSION.t1") , "2");
+ JDBC.assertSingleValueResultSet(s2.executeQuery(
+ "select count(*) from SESSION.t2") , "2");
+ JDBC.assertSingleValueResultSet(s2.executeQuery(
+ "select count(*) from SESSION.t3") , "2");
+ JDBC.assertSingleValueResultSet(s3.executeQuery(
+ "select count(*) from SESSION.t4") , "0");
+ s1.executeUpdate("drop table SESSION.t1");
+ s2.executeUpdate("drop table SESSION.t2");
+ s3.executeUpdate("drop table SESSION.t3");
+ s3.executeUpdate("drop table SESSION.t4");
+ rs1.close(); rs23.close(); rs4.close();
+ commit();
+ s1.close(); s2.close(); s3.close();
+ }
+ /**
+ * Test declared temporary table with ON COMMIT DELETE ROWS and holdable
+ * cursors on prepared statement
+ *
+ * @throws SQLException
+ */
+ public void testOnCommitDeleteRowsHoldabilityWithPreparedStatements()
+ throws SQLException {
+ Statement s1 = 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 = getConnection().prepareStatement(
+ "select count(*) from SESSION.t1",
+ ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
+ ResultSet.HOLD_CURSORS_OVER_COMMIT );
+ ResultSet rs1 = ps1.executeQuery();
+ JDBC.assertSingleValueResultSet(rs1 , "2");
+ PreparedStatement ps2 = getConnection().prepareStatement(
+ "select * from SESSION.t1", ResultSet.TYPE_FORWARD_ONLY,
+ ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
+ //hold cursor open on t1. Commit should preserve the rows
+ ResultSet rs11 = ps2.executeQuery();
+ rs11.next();
+ //Cursor was Opened.
+ //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 = getConnection().prepareStatement(
+ "select count(*) from SESSION.t2",
+ ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
+ ResultSet.HOLD_CURSORS_OVER_COMMIT );
+ ResultSet rs2 = ps3.executeQuery();
+ JDBC.assertSingleValueResultSet(rs2 , "2");
+ PreparedStatement ps4 = getConnection().prepareStatement(
+ "select * from SESSION.t2", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT );
+ //hold cursor open on t2 but close it before commit
+ rs2 = ps4.executeQuery();
+ rs2.next();
+ rs2.close();
+ //commiting Point
+ commit();
+ JDBC.assertSingleValueResultSet(s1.executeQuery(
+ "select count(*) from SESSION.t1") , "2");
+ //Need to close the held cursor on t1 before t1 can be dropped
+ rs11.close();
+ rs1.close();
+ JDBC.assertSingleValueResultSet(s1.executeQuery(
+ "select count(*) from SESSION.t2") , "0");
+ s1.executeUpdate("drop table SESSION.t1");
+ s1.executeUpdate("drop table SESSION.t2");
+ }
+ /**
+ * Tests a temporary table with ON COMMIT PRESERVE ROWS and various
+ * combinations of holdability.
+ * Temp table t1 with held open cursors on it. Data should be preserved,
+ * holdability shouldn't matter
+ *
+ * @throws SQLException
+ */
+ public void testOnCommitPreserveRowsWithHoldability() throws SQLException {
+ //create a statement with hold cursors over commit
+ Statement s1 = getConnection().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)");
+ JDBC.assertSingleValueResultSet(s1.executeQuery(
+ "select count(*) from SESSION.t1") , "2");
+ //Opening the Cursor
+ ResultSet rs1 = s1.executeQuery("select * from SESSION.t1");
+ rs1.next();
+ //create a statement without hold cursors.
+ Statement s2 = createStatement();
+ s1.executeUpdate("declare global temporary table SESSION.t2(" +
+ "c11 int, c12 int) on commit preserve rows not logged");
+ s1.executeUpdate("insert into session.t2 values(11, 1)");
+ s1.executeUpdate("insert into session.t2 values(12, 2)");
+ JDBC.assertSingleValueResultSet(s2.executeQuery(
+ "select count(*) from SESSION.t2") , "2");
+ //Opening The Cursor
+ ResultSet rs2 = s2.executeQuery("select * from SESSION.t2");
+ rs2.next();
+ //commiting point
+ commit();
+ JDBC.assertSingleValueResultSet(s1.executeQuery(
+ "select count(*) from SESSION.t1") , "2");
+ JDBC.assertSingleValueResultSet(s2.executeQuery(
+ "select count(*) from SESSION.t2") , "2");
+ s1.executeUpdate("drop table SESSION.t1");
+ s2.executeUpdate("drop table SESSION.t2");
+ }
+ /**
+ * Savepoint and Rollback behavior - 1 (3A)
+ * In the transaction Create savepoint1 and declare temp table t1.
+ * Create savepoint 2, drop temp table t1, rollback savepoint 2
+ * select should pass, rollback savepoint 1, select should fail.
+ *
+ * @throws SQLException
+ */
+ public void testSavepointRollbackbehaviour1() throws SQLException {
+ Statement s = createStatement();
+ //Set The First savepoint
+ Savepoint savepoint1 = getConnection().setSavepoint();
+ //create a temp table t1
+ s.executeUpdate("declare global temporary table SESSION.t1(" +
+ "c11 int, c12 int) on commit preserve rows not logged");
+ PreparedStatement pStmt = prepareStatement(
+ "insert into SESSION.t1 values (?, ?)");
+ pStmt.setInt(1, 11);
+ pStmt.setInt(2, 1);
+ pStmt.execute();
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "1");
+ //Set The Second savepoint
+ Savepoint savepoint2 = getConnection().setSavepoint();
+ //drop the temp table t1
+ s.executeUpdate("drop table SESSION.t1");
+ assertStatementError("42X05" , s , "select * from SESSION.t1");
+ //Rollback to the second savepoint - drop table Operation is rolled
+ //back hence we should have t1
+ getConnection().rollback(savepoint2);
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "0");
+ //Rollback to the First savepoint - create table Operation is also
+ //rolled back hence there is no t1
+ getConnection().rollback(savepoint1);
+ assertStatementError("42X05" , s , "select * from SESSION.t1");
+ }
+ /**
+ * Savepoint and Rollback behavior - 2 (3B)
+ * In the transaction Create savepoint1 and declare temp table t1.
+ * Create savepoint2 and declare temp table t2
+ * Release savepoint 1 and select from temp table t1 and t2.
+ * Drop temp table t2(explicit drop), rollback transaction(implicit drop
+ * of t1) Select from temp table t1 and t2 will fail.
+ *
+ *
+ * @throws SQLException
+ */
+ public void testSavepointRollbackbehaviour2() throws SQLException {
+ Statement s = createStatement();
+ //Set First savepoint (save point - 1)
+ Savepoint savepoint1 = getConnection().setSavepoint();
+ s.executeUpdate("declare global temporary table SESSION.t1(" +
+ "c11 int, c12 int) on commit preserve rows not logged");
+ //Set Second savepoint (save point - 2)
+ Savepoint savepoint2 = getConnection().setSavepoint();
+ s.executeUpdate("declare global temporary table SESSION.t2(" +
+ "c21 int, c22 int) on commit preserve rows not logged");
+ //Release First savepoint
+ getConnection().releaseSavepoint(savepoint1);
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "0");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "0");
+ //Drop the Temp Table t2 Explicitly and t1 Implicitly(by rolling back)
+ s.executeUpdate("drop table SESSION.t2");
+ rollback();
+ assertStatementError("42X05" , s , "select * from SESSION.t1");
+ assertStatementError("42X05" , s , "select * from SESSION.t2");
+ }
+ /**
+ * Savepoint and Rollback behavior - 3 (3C)
+ * In the transaction Create savepoint1 and declare temp table t1.
+ * Create savepoint2 and declare temp table t2.
+ * Release savepoint 1 and select from temp table t1 and t2 should pass.
+ * Create savepoint3 and rollback savepoint3(should not touch t1 and t2)
+ * select from temp tables t1 and t2 should pass.
+ * Rollback transaction and select from temp tables t1 and t2 should fail.
+ *
+ * @throws SQLException
+ */
+ public void testSavepointRollbackbehaviour3() throws SQLException {
+ Statement s = createStatement();
+ //set first savepoint
+ Savepoint savepoint1 = getConnection().setSavepoint();
+ s.executeUpdate("declare global temporary table SESSION.t1(" +
+ "c11 int, c12 int) on commit preserve rows not logged");
+ //set second savepoint
+ Savepoint savepoint2 = getConnection().setSavepoint();
+ s.executeUpdate("declare global temporary table SESSION.t2(" +
+ "c21 int, c22 int) on commit preserve rows not logged");
+ //release the first savepoint
+ getConnection().releaseSavepoint(savepoint1);
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "0");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "0");
+ //set third savepoint
+ Savepoint savepoint3 = getConnection().setSavepoint();
+ //rollback to third savepoint - (should not touch t1 and t2)
+ getConnection().rollback(savepoint3);
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "0");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "0");
+ //rollback the entire transaction - this rolls back create statements
+ //also hence t1 , t2 got deleted
+ rollback();
+ assertStatementError("42X05" , s , "select * from SESSION.t1");
+ assertStatementError("42X05" , s , "select * from SESSION.t2");
+ }
+ /**
+ * Savepoint and Rollback behavior - 4 (3D)
+ *
+ * @throws SQLException
+ */
+ public void testSavepointRollbackbehaviour4() throws SQLException {
+ Statement s = createStatement();
+ //set first savepoint
+ Savepoint savepoint1 = getConnection().setSavepoint();
+ s.executeUpdate("declare global temporary table SESSION.t1(" +
+ "c11 int, c12 int) on commit preserve rows not logged");
+ //set second savepoint
+ Savepoint savepoint2 = getConnection().setSavepoint();
+ s.executeUpdate("drop table SESSION.t1");
+ //rollback to second savepoint
+ getConnection().rollback(savepoint2);
+ JDBC.assertSingleValueResultSet(s.executeQuery("" +
+ "select count(*) from SESSION.t1") , "0");
+ //commit
+ commit();
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "0");
+ s.executeUpdate("drop table SESSION.t1");
+ }
+ /**
+ * Savepoint and Rollback behavior - 5 - 3E
+ * Tests the basic definition of savepoint in combination with Rollback.
+ *
+ * @throws SQLException
+ */
+ public void testSavepointRollbackbehaviour5() throws SQLException {
+ Statement s = createStatement();
+ //set first savepoint
+ Savepoint savepoint1 = getConnection().setSavepoint();
+ s.executeUpdate("declare global temporary table SESSION.t1(" +
+ "c11 int, c12 int) on commit preserve rows not logged");
+ //set second savepoint
+ Savepoint savepoint2 = getConnection().setSavepoint();
+ s.executeUpdate("drop table SESSION.t1");
+ //rollback to first savepoint
+ getConnection().rollback(savepoint1);
+ assertStatementError("42X05" , s , "select * from SESSION.t1");
+ }
+
+ /**
+ * Savepoint and Rollback behavior - 6 - 3J
+ * In the transaction: declare temp table t1 with 2 columns and commit.
+ * Create savepoint1 and drop temp table t1 with 2 columns, declare temp
+ * table t1 but this time with 3 columns.
+ * Create savepoint2 and drop temp table t1 with 3 columns, rollback,
+ * select from temp table t1 here should have 2 columns
+ *
+ * @throws SQLException
+ */
+ public void testSavepointRollbackbehaviour6() throws SQLException {
+ String[] s1 = {"C11" , "C12"};
+ String[] s2 = {"C11" , "C12" , "C13"};
+ Statement s = createStatement();
+ // declare temp table t1 with 2 columns
+ 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, 11)");
+ ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
+ JDBC.assertColumnNames(rs1 , s1);
+ //commiting point
+ getConnection().commit();
+ //set first savepoint
+ Savepoint savepoint1 = getConnection().setSavepoint();
+ s.executeUpdate("drop table SESSION.t1");
+ //declare temp table t1 with 3 columns
+ s.executeUpdate("declare global temporary table SESSION.t1(" +
+ "c11 int, c12 int, c13 int not null) on commit preserve rows " +
+ "not logged");
+ s.executeUpdate("insert into SESSION.t1 values(22, 22, 22)");
+ rs1 = s.executeQuery("select * from SESSION.t1");
+ JDBC.assertColumnNames(rs1 , s2);
+ //set second savepoint
+ Savepoint savepoint2 = getConnection().setSavepoint();
+ s.executeUpdate("drop table SESSION.t1");
+ //rollback the transaction
+ rollback();
+ rs1 = s.executeQuery("select * from SESSION.t1");
+ JDBC.assertColumnNames(rs1 , s1);
+ s.executeUpdate("drop table SESSION.t1");
+ }
+ /**
+ * Savepoint and Rollback behavior - 7 - 3K
+ * tests the savepoint and rollback behaviour with update command
+ *
+ * @throws SQLException
+ */
+ public void testSavepointRollbackbehaviour7() throws SQLException {
+ Statement s = createStatement();
+ //declare temp table t1 & t2, insert few rows and commit
+ s.executeUpdate("declare global temporary table SESSION.t1(" +
+ "c11 int, c12 int) on commit preserve rows not logged " +
+ "on rollback delete rows");
+ s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(" +
+ "c21 int, c22 int) on commit preserve rows not logged");
+ s.executeUpdate("insert into SESSION.t1 values(11, 1)");
+ s.executeUpdate("insert into session.t2 values(21, 1)");
+ commit();
+ //committed - the first transaction has been completed
+ //In the next transaction, insert couple more rows in t1 & t2
+ s.executeUpdate("insert into SESSION.t1 values(12, 2)");
+ s.executeUpdate("insert into SESSION.t2 values(22, 2)");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "2");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "2");
+ //set first savepoint
+ Savepoint savepoint1 = getConnection().setSavepoint();
+ s.executeUpdate("UPDATE SESSION.t1 SET c12 = 3 where c12>1");
+ s.executeUpdate("UPDATE SESSION.t2 SET c22 = 3 where c22>2");
+ //rollback to first savepoint
+ getConnection().rollback(savepoint1);
+ //Rollback to savepoint1 and we should loose all the rows in t1 temp
+ //table t2 should also have no rows because attempt was made to modify
+ //it (even though nothing actually got modified in t2 in the savepoint)
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "0");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "0");
+ commit();
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "0");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "0");
+ s.executeUpdate("drop table SESSION.t1");
+ s.executeUpdate("drop table SESSION.t2");
+ }
+ /**
+ * Savepoint and Rollback behavior - 8 - 3L
+ *
+ * @throws SQLException
+ */
+ public void testSavepointRollbackbehaviour8() throws SQLException {
+ Statement s = createStatement();
+ s.executeUpdate("declare global temporary table SESSION.t1(" +
+ "c11 int, c12 int) on commit preserve rows not logged on " +
+ "rollback delete rows");
+ s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(" +
+ "c21 int, c22 int) on commit preserve rows not logged on " +
+ "rollback delete rows");
+ s.executeUpdate("insert into SESSION.t1 values(11, 1)");
+ s.executeUpdate("insert into session.t2 values(21, 1)");
+ commit();
+ //insert couple more rows in t1 & t2 and
+ //Create savepoint1 and update some rows in t1 and inspect the data
+ s.executeUpdate("insert into SESSION.t1 values(12, 2)");
+ s.executeUpdate("insert into session.t2 values(22, 2)");
+ //set first savepoint
+ Savepoint savepoint1 = getConnection().setSavepoint();
+ s.executeUpdate("UPDATE SESSION.t1 SET c12 = 3 where c12>1");
+ s.executeUpdate("UPDATE SESSION.t2 SET c22 = 3 where c22>3");
+ //rollback to first savepoint
+ getConnection().rollback(savepoint1);
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "0");
+ JDBC.assertSingleValueResultSet(s.executeQuery("select count(*) from SESSION.t1") , "0");
+ rollback();
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "0");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "0");
+ //cleanUp
+ s.executeUpdate("drop table SESSION.t1");
+ s.executeUpdate("drop table SESSION.t2");
+ }
+ /**
+ * Savepoint and Rollback behavior - 9 - 3M
+ *
+ * @throws SQLException
+ */
+ public void testSavepointRollbackbehaviour9() throws SQLException {
+ Statement s = createStatement();
+ s.executeUpdate("declare global temporary table SESSION.t1(" +
+ "c11 int, c12 int) on commit preserve rows not logged " +
+ "on rollback delete rows");
+ s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(" +
+ "c21 int, c22 int) on commit preserve rows not logged on " +
+ "rollback delete rows");
+ s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(" +
+ "c31 int, c32 int) on commit preserve rows not logged on " +
+ "rollback delete rows");
+ s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4(" +
+ "c41 int, c42 int) on commit preserve rows not logged on " +
+ "rollback delete rows");
+ s.executeUpdate("insert into SESSION.t1 values(11, 1)");
+ s.executeUpdate("insert into SESSION.t2 values(21, 1)");
+ s.executeUpdate("insert into SESSION.t3 values(31, 1)");
+ s.executeUpdate("insert into SESSION.t4 values(41, 1)");
+ commit();
+ //Beginning of second transaction
+ //Insert a couple of more rows into t1 , t2 and t3
+ s.executeUpdate("insert into SESSION.t1 values(12, 2)");
+ s.executeUpdate("insert into session.t2 values(22, 2)");
+ s.executeUpdate("insert into session.t3 values(32, 2)");
+ //set first savepoint
+ Savepoint savepoint1 = getConnection().setSavepoint();
+ s.executeUpdate("DELETE FROM SESSION.t1 where c12>1");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "1");
+ //set second savepoint
+ Savepoint savepoint2 = getConnection().setSavepoint();
+ s.executeUpdate("DELETE FROM SESSION.t2 where c22>1");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "1");
+ //Release savepoint2 and now savepoint1 should keep track of changes
+ //made to t1 and t2
+ getConnection().releaseSavepoint(savepoint2);
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "1");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "1");
+ // Rollback savepoint1 and should see no data in t1 and t2
+ getConnection().rollback(savepoint1);
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "0");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "0");
+ //Should see data in t3 since it was not touched in the savepoint that
+ //was rolled back
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t3") , "2");
+ rollback();
+ //Rolling back the transaction we should see no data in t1 and t2 and t3
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "0");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "0");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t3") , "0");
+ //Should see data in t4 since it was not touched in the transaction that was rolled back
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t4") , "1");
+ s.executeUpdate("drop table SESSION.t1");
+ s.executeUpdate("drop table SESSION.t2");
+ s.executeUpdate("drop table SESSION.t3");
+ s.executeUpdate("drop table SESSION.t4");
+ }
+ /**
+ * Savepoint and Rollback behavior - 10 - 3N
+ *
+ * @throws SQLException
+ */
+ public void testSavepointRollbackbehaviour10() throws SQLException {
+ Statement s = createStatement();
+ s.executeUpdate("declare global temporary table SESSION.t1(" +
+ "c11 int, c12 int) on commit preserve rows not logged on " +
+ "rollback delete rows");
+ s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(" +
+ "c21 int, c22 int) on commit preserve rows not logged on " +
+ "rollback delete rows");
+ s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(" +
+ "c31 int, c32 int) on commit preserve rows not logged on " +
+ "rollback delete rows");
+ s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4(" +
+ "c41 int, c42 int) on commit preserve rows not logged on " +
+ "rollback delete rows");
+ s.executeUpdate("insert into SESSION.t1 values(11, 1)");
+ s.executeUpdate("insert into SESSION.t1 values(12, 2)");
+ s.executeUpdate("insert into SESSION.t2 values(21, 1)");
+ s.executeUpdate("insert into SESSION.t2 values(22, 2)");
+ s.executeUpdate("insert into SESSION.t3 values(31, 1)");
+ s.executeUpdate("insert into SESSION.t4 values(41, 1)");
+ commit();
+ //In the next transaction, insert couple more rows in t3
+ s.executeUpdate("insert into SESSION.t3 values(31, 2)");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t3") , "2");
+ //Create savepoint1 and delete some rows from t1
+ Savepoint savepoint1 = getConnection().setSavepoint();
+ s.executeUpdate("DELETE FROM SESSION.t1 where c12>1");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "1");
+ s.executeUpdate("DELETE FROM SESSION.t2 where c22>3");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "2");
+ //Create savepoint2 and delete some rows from t2
+ Savepoint savepoint2 = getConnection().setSavepoint();
+ s.executeUpdate("DELETE FROM SESSION.t2 where c22>1");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "1");
+ //Rollback the transaction and should see no data in t1 and t2 and t3
+ rollback();
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "0");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "0");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t3") , "0");
+ //Should see data in t4 since it was not touched in the transaction
+ //that was rolled back
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t4") , "1");
+ s.executeUpdate("drop table SESSION.t1");
+ s.executeUpdate("drop table SESSION.t2");
+ s.executeUpdate("drop table SESSION.t3");
+ s.executeUpdate("drop table SESSION.t4");
+ }
+ /**
+ * Savepoint and Rollback behavior - 11 - 3O
+ *
+ * @throws SQLException
+ */
+ public void testSavepointRollbackbehaviour11() throws SQLException {
+ Statement s = createStatement();
+ s.executeUpdate("declare global temporary table SESSION.t1(" +
+ "c11 int, c12 int) on commit preserve rows not logged on " +
+ "rollback delete rows");
+ s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(" +
+ "c21 int, c22 int) on commit preserve rows not logged on " +
+ "rollback delete rows");
+ s.executeUpdate("insert into SESSION.t1 values(11, 1)");
+ s.executeUpdate("insert into SESSION.t2 values(21, 1)");
+ commit();
+ s.executeUpdate("insert into SESSION.t1 values(12, 2)");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "2");
+ //set the first savepoint here
+ Savepoint savepoint1 = getConnection().setSavepoint();
+ s.executeUpdate("insert into SESSION.t2 values(22, 2)");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "2");
+ //Rollback savepoint1; expect no data in t2 but t1 should have data
+ getConnection().rollback(savepoint1);
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "2");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "0");
+ //Commit the transaction; expect no data in t2 but t1 should have data
+ commit();
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "2");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "0");
+ s.executeUpdate("drop table SESSION.t1");
+ s.executeUpdate("drop table SESSION.t2");
+ }
+ /**
+ * Savepoint and Rollback behavior - 12 - 3P
+ *
+ * @throws SQLException
+ */
+ public void testSavepointRollbackbehaviour12() throws SQLException {
+ Statement s = createStatement();
+ 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)");
+ s.executeUpdate("insert into SESSION.t1 values(12, 2)");
+ commit();
+ //set the first savepoint
+ Savepoint savepoint1 = getConnection().setSavepoint();
+ s.executeUpdate("insert into SESSION.t1 values(13, 3)");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "3");
+ //release the savepoint - now transaction should keep track of changes
+ //made to t1
+ getConnection().releaseSavepoint(savepoint1);
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "3");
+ //Rollback the transaction and should still see no data in t1
+ rollback();
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t1") , "0");
+ s.executeUpdate("drop table SESSION.t1");
+ }
+ /**
+ * Savepoint and Rollback behavior - 13 - 3Q
+ * tests the rollback , savepoint behaviour with prepartedStatement
+ *
+ * @throws SQLException
+ */
+ public void testSavepointRollbackbehaviour13() throws SQLException {
+ Statement s = createStatement();
+ s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(" +
+ "c21 int, c22 int) not logged on commit preserve rows");
+ PreparedStatement pStmtInsert = 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 = prepareStatement(
+ "UPDATE SESSION.t2 SET c22 = 3 where c21=?");
+ pStmtUpdate.setInt(1, 23);
+ pStmtUpdate.execute();
+ PreparedStatement pStmtDelete = prepareStatement(
+ "DELETE FROM SESSION.t2 where c21 = ?");
+ pStmtDelete.setInt(1, 23);
+ pStmtDelete.execute();
+ commit();//committing point
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "2");
+ //set the first savepoint
+ Savepoint savepoint1 = getConnection().setSavepoint();
+ pStmtInsert.setInt(1, 23);
+ pStmtInsert.setInt(2, 2);
+ pStmtInsert.execute();
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "3");
+ //set the second savepoint
+ Savepoint savepoint2 = getConnection().setSavepoint();
+ pStmtUpdate.setInt(1, 23);
+ pStmtUpdate.execute();
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "3");
+ //rollback savepoint2 and should loose all the data from t2");
+ getConnection().rollback(savepoint2);
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "0");
+ //Create savepoint3, insert some rows into t2 using prepared statement
+ Savepoint savepoint3 = getConnection().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();
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "3");
+ //Create savepoint4 and update row inserted in savepoint3 using prepared
+ //statement and inspect the data in t2
+ Savepoint savepoint4 = getConnection().setSavepoint();
+ pStmtUpdate.setInt(1, 23);
+ pStmtUpdate.execute();
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "3");
+ //release savepoint4
+ getConnection().releaseSavepoint(savepoint4);
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "3");
+ pStmtDelete.setInt(1, 23);
+ pStmtDelete.execute();
+ //Commit transaction and should see data in t2
+ commit();
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.t2") , "2");
+ s.executeUpdate("drop table SESSION.t2");
+ }
+ /**
+ * Test declared temporary table with ON COMMIT DELETE ROWS and holdable
+ * cursors and temp table as part of subquery
+ * Temp table t1 with no direct held cursor open on it.
+ * Data should be deleted from t1 at commit time
+ *
+ * @throws SQLException
+ */
+ public void testTest4() throws SQLException {
+ Statement s1 = getConnection().createStatement(
+ ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
+ ResultSet.HOLD_CURSORS_OVER_COMMIT );
+ try {
+ s1.executeUpdate("drop table t1");
+ }
+ catch(SQLException e)
+ {
+ assertSQLState("42Y55" , e);
+ }
+ 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)");
+ JDBC.assertSingleValueResultSet(s1.executeQuery(
+ "select count(*) from SESSION.t1") , "2");
+ JDBC.assertSingleValueResultSet(s1.executeQuery(
+ "select count(*) from t1") , "0");
+ //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");
+ commit();
+ //Temp table t1 will have no data after commit
+ JDBC.assertSingleValueResultSet(s1.executeQuery(
+ "select count(*) from SESSION.t1") , "0");
+ //Physical table t1 will have 2 rows after commit
+ JDBC.assertSingleValueResultSet(s1.executeQuery(
+ "select count(*) from t1") , "2");
+ s1.executeUpdate("drop table SESSION.t1");
+ s1.executeUpdate("drop table t1");
+ }
+ /**
+ * Temporary tables declared in a pooled connection should get dropped
+ * when that pooled connection is closed.
+ *
+ * @throws SQLException
+ */
+ public void testPooledConnectionClosed() throws SQLException {
+
+ ConnectionPoolDataSource dscsp =
+ J2EEDataSource.getConnectionPoolDataSource();
+ //In the first connection handle to the pooled connection, create
+ //physical session schema, create table t1 in it
+ PooledConnection pc = dscsp.getPooledConnection();
+ Connection pcon = pc.getConnection();
+ Statement s = pcon.createStatement();
+ try {
+ s.executeUpdate("CREATE schema SESSION");
+ }
+ catch(SQLException e)
+ {
+ assertSQLState("X0Y68" , e);
+ }
+ s.executeUpdate("CREATE TABLE SESSION.tx(c21 int)");
+ s.executeUpdate("insert into SESSION.tx values(11)");
+ s.executeUpdate("insert into SESSION.tx values(12)");
+ s.executeUpdate("insert into SESSION.tx values(13)");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.tx") , "3");
+ //Declare temp table with same name as physical table in SESSION schema
+ s.executeUpdate("declare global temporary table SESSION.tx(" +
+ "c11 int, c12 int) on commit preserve rows not logged");
+ s.executeUpdate("insert into SESSION.tx values(11,1)");
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.tx") , "1");
+ commit();
+ //Now close the connection handle to the pooled connection
+ getConnection().close();
+ //Do another getConnection() to get a new connection handle to the
+ //pooled connection
+ s = getConnection().createStatement();
+ //In this new handle, a select * from SESSION.tx should be looking at
+ //the physical session table
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from SESSION.tx") , "3");
+ s.executeUpdate("drop table SESSION.tx");
+ }
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DeclareGlobalTempTableJavaJDBC30Test.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DeclareGlobalTempTableJavaTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DeclareGlobalTempTableJavaTest.java?rev=813528&r1=813527&r2=813528&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DeclareGlobalTempTableJavaTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DeclareGlobalTempTableJavaTest.java Thu Sep 10 17:56:22 2009
@@ -1,6 +1,6 @@
/*
- Derby - Class org.apache.derbyTesting.functionTests.tests.lang.DeclareGlobalTempTableJava
+ Derby - Class org.apache.derbyTesting.functionTests.tests.lang.DeclareGlobalTempTableJavaTest
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
@@ -54,7 +54,7 @@
}
public static Test suite() {
- return TestConfiguration.embeddedSuite(DeclareGlobalTempTableJavaTest.class);
+ return TestConfiguration.defaultSuite(DeclareGlobalTempTableJavaTest.class);
}
protected void setUp() throws Exception {
super.setUp();
@@ -754,6 +754,74 @@
}
}
/**
+ * Temporary table create and drop Rollback behaviour
+ * Tests the basic function of temporary table with holdability
+ *
+ * @throws SQLException
+ */
+ public void testTempTableDDLRollbackbehaviour1() throws SQLException {
+ Statement s = createStatement();
+ s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
+ JDBC.assertSingleValueResultSet(s.executeQuery("select count(*) from SESSION.t1") , "0");
+ s.executeUpdate("drop table SESSION.t1");
+ // we've removed the table, so select should fail
+ assertStatementError("42X05" , s , "select * from SESSION.t1");
+ rollback();
+ // should still fail after rollback; we don't have savepoints and
+ // we rolled back entire transaction, incl. creation of temp table
+ assertStatementError("42X05" , s , "select * from SESSION.t1");
+ }
+
+ /**
+ * Temporary table create and drop Rollback behaviour
+ * Tests drop of temp table and rollback - select should still work
+ *
+ * @throws SQLException
+ */
+ public void testTempTableDDLRollbackbehaviour2() throws SQLException {
+ Statement s = createStatement();
+ s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
+ commit();
+ // drop table and rollback - select should still work
+ s.executeUpdate("drop table SESSION.t1");
+ assertStatementError("42X05" , s , "select * from SESSION.t1");
+ rollback();
+ // select should work again
+ JDBC.assertSingleValueResultSet(s.executeQuery("select count(*) from SESSION.t1") , "0");
+ }
+
+ /**
+ * Temporary table create and drop Rollback behaviour
+ * Tests drop of temp table and commit - select should no longer work
+ *
+ * @throws SQLException
+ */
+ public void testTempTableDDLRollbackbehaviour3() throws SQLException {
+ Statement s = createStatement();
+ s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
+ JDBC.assertSingleValueResultSet(s.executeQuery("select count(*) from SESSION.t1") , "0");
+ commit();
+ // drop table and commit - select should no longer work
+ s.executeUpdate("drop table SESSION.t1");
+ commit();
+ assertStatementError("42X05" , s , "select * from SESSION.t1");
+ }
+
+ /**
+ * Temporary table create and drop Rollback behaviour
+ * Tests create of temp table and rollback - select should fail
+ *
+ * @throws SQLException
+ */
+ public void testTempTableDDLRollbackbehaviour4() throws SQLException {
+ Statement s = createStatement();
+ s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
+ JDBC.assertSingleValueResultSet(s.executeQuery("select count(*) from SESSION.t1") , "0");
+ rollback();
+ assertStatementError("42X05" , s , "select * from SESSION.t1");
+ }
+
+ /**
* Rollback behavior - declare temp table, rollback, select should fail.
*
* @throws SQLException
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=813528&r1=813527&r2=813528&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Thu Sep 10 17:56:22 2009
@@ -182,6 +182,9 @@
// Test uses DriverManager to connect to database in jar.
suite.addTest(DBInJarTest.suite());
suite.addTest(ConnectTest.suite());
+
+ // test uses PooledConnections and Savepoints
+ suite.addTest(DeclareGlobalTempTableJavaJDBC30Test.suite());
}
suite.addTest(BigDataTest.suite());