| /* |
| |
| 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.SQLException; |
| import java.sql.Savepoint; |
| import java.sql.Statement; |
| import javax.sql.ConnectionPoolDataSource; |
| import javax.sql.PooledConnection; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.J2EEDataSource; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| /** |
| * 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() { |
| BaseTestSuite suite = new BaseTestSuite(); |
| 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() { |
| BaseTestSuite suite = new BaseTestSuite("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"); |
| } |
| |
| /** |
| * Test that we don't get an NPE when re-using a PreparedStatement |
| * on a temp table declared and then rolled back. See DERBY-6189. |
| */ |
| public void test_derby_6189() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| conn.prepareStatement |
| ( "DECLARE GLOBAL TEMPORARY TABLE SESSION.t6189( c21 int, c22 int) not logged on commit preserve rows" ) |
| .execute(); |
| PreparedStatement pStmtInsert = conn.prepareStatement( "insert into SESSION.t6189 values (23, 1)" ); |
| |
| pStmtInsert.execute(); |
| |
| conn.rollback(); |
| |
| assertStatementError("42X05", pStmtInsert); |
| } |
| |
| } |
