| /* |
| * |
| * Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi/SavepointJdbc30Test |
| * |
| * 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.jdbcapi; |
| |
| import java.lang.reflect.Method; |
| import java.sql.Connection; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Savepoint; |
| import java.sql.Statement; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.CleanDatabaseTestSetup; |
| import org.apache.derbyTesting.junit.DatabasePropertyTestSetup; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| /** |
| * Test the new class Savepoint in JDBC 30. Also, test some mix and match of |
| * defining savepoints through JDBC and SQL testing both callable and prepared |
| * statements meta data |
| * |
| */ |
| |
| public class SavepointJdbc30Test extends BaseJDBCTestCase { |
| |
| /** |
| * Create a test |
| * |
| * @param name |
| */ |
| public SavepointJdbc30Test(String name) { |
| super(name); |
| } |
| |
| /** |
| * Set up the test suite for embedded mode, client mode, and embedded mode |
| * with XADataSources |
| * |
| * @return A suite containing embedded, client and embedded with XA suites |
| */ |
| public static Test suite() { |
| BaseTestSuite suite = |
| new BaseTestSuite("SavepointJdbc30_JSR169Test suite"); |
| |
| // Get the tests for embedded |
| BaseTestSuite embedded = new BaseTestSuite( |
| "SavepointJdbc30_JSR169Test:embedded"); |
| embedded.addTestSuite(SavepointJdbc30Test.class); |
| embedded.addTest(getEmbeddedSuite("SavepointJdbc30_JSR169Test:" |
| + "embedded only")); |
| suite.addTest(embedded); |
| |
| // Get the tests for client. |
| BaseTestSuite client = |
| new BaseTestSuite("SavepointJdbc30_JSR169Test:client"); |
| |
| client.addTestSuite(SavepointJdbc30Test.class); |
| suite.addTest(TestConfiguration.clientServerDecorator(client)); |
| |
| // Repeat the embedded tests obtaining a connection from |
| // an XA data source if it is supported. This is not supported |
| // under JSR169. |
| if (JDBC.vmSupportsJDBC3()) { |
| embedded = new BaseTestSuite( |
| "SavepointJdbc30_JSR169Test:embedded XADataSource"); |
| embedded.addTestSuite(SavepointJdbc30Test.class); |
| embedded.addTest(getEmbeddedSuite("SavepointJdbc30_JSR169Test:" |
| + "embedded only XADataSource")); |
| suite.addTest(TestConfiguration.connectionXADecorator(embedded)); |
| // Repeat the client tests obtaining a connection from |
| // an XA data source if it is supported. This is not supported |
| // under JSR169. |
| client = new BaseTestSuite( |
| "SavepointJdbc30_JSR169Test:client XADatasource"); |
| |
| client.addTestSuite(SavepointJdbc30Test.class); |
| suite.addTest(TestConfiguration.clientServerDecorator(TestConfiguration.connectionXADecorator(client))); |
| } |
| |
| return new CleanDatabaseTestSetup( |
| DatabasePropertyTestSetup.setLockTimeouts(suite, 1, 2)) { |
| /** |
| * Creates the database objects used in the test cases. |
| * |
| * @throws SQLException |
| */ |
| protected void decorateSQL(Statement s) throws SQLException { |
| /* Create a table */ |
| s.execute("create table t1 (c11 int, c12 smallint)"); |
| s.execute("create table t2 (c11 int)"); |
| getConnection().commit(); |
| |
| } |
| }; |
| |
| } |
| |
| /** |
| * Create a testsuite containing the tests that can only run in embedded |
| * mode. These tests have names starting with x and are added automatically. |
| */ |
| private static Test getEmbeddedSuite(String name) { |
| BaseTestSuite embedded = new BaseTestSuite(name); |
| Method[] methods = SavepointJdbc30Test.class.getMethods(); |
| for (int i = 0; i < methods.length; i++) { |
| Method m = methods[i]; |
| if (m.getParameterTypes().length > 0 |
| || !m.getReturnType().equals(Void.TYPE)) { |
| continue; |
| } |
| String methodName = m.getName(); |
| if (methodName.startsWith("x")) { |
| embedded.addTest(new SavepointJdbc30Test(methodName)); |
| } |
| } |
| return embedded; |
| } |
| |
| /** |
| * Set up the test environment. |
| */ |
| protected void setUp() throws Exception { |
| super.setUp(); |
| // Keep Autocommit off |
| getConnection().setAutoCommit(false); |
| // Clear the tables created by the decorator |
| Statement s = createStatement(); |
| s.execute("truncate table t1"); |
| s.execute("truncate table t2"); |
| commit(); |
| } |
| |
| /** |
| * Test1. It should not be possible to set a savepoint if autocommit is on. |
| */ |
| public void testNoSavepointsIfAutoCommit() throws SQLException { |
| Connection con = getConnection(); |
| con.setAutoCommit(true); |
| try { |
| con.setSavepoint(); // will throw exception because auto commit is |
| // true |
| fail("No unnamed savepoints allowed if autocommit is true"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("XJ010", se); |
| } |
| // Test 1a |
| try { |
| con.setSavepoint("notallowed"); // will throw exception because auto |
| // commit is true |
| fail("No named savepoints allowed if autocommit is true"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("XJ010", se); |
| } |
| } |
| |
| /** |
| * Test2 - After releasing a savepoint, should be able to reuse it. |
| */ |
| public void testReusingSavepoints() throws SQLException { |
| Connection con = getConnection(); |
| Savepoint savepoint1 = con.setSavepoint("s1"); |
| con.releaseSavepoint(savepoint1); |
| con.setSavepoint("s1"); |
| con.rollback(); |
| } |
| |
| /** |
| * Test3 - Named savepoints can't pass null for name |
| */ |
| public void testNullName() throws SQLException { |
| Connection con = getConnection(); |
| try { |
| con.setSavepoint(null); |
| fail("FAIL 3 Null savepoint"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("XJ011", se); |
| } |
| con.rollback(); |
| } |
| |
| /** |
| * Test4 - Verify names/ids of named/unnamed savepoints named savepoints |
| * don't have an id. unnamed savepoints don't have a name (internally, all |
| * our savepoints have names, but for unnamed savepoint, that is not exposed |
| * through jdbc api) |
| * |
| * @throws SQLException |
| */ |
| public void testNamesAndIds() throws SQLException { |
| Connection con = getConnection(); |
| try { |
| Savepoint savepoint1 = con.setSavepoint(); |
| savepoint1.getSavepointId(); |
| // following should throw exception for unnamed savepoint |
| savepoint1.getSavepointName(); |
| fail("FAIL 4 getSavepointName on id savepoint"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("XJ014", se); |
| } |
| con.rollback(); |
| try { |
| Savepoint savepoint1 = con.setSavepoint("s1"); |
| savepoint1.getSavepointName(); |
| // following should throw exception for named savepoint |
| savepoint1.getSavepointId(); |
| fail("FAIL 4 getSavepointId on named savepoint "); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("XJ013", se); |
| } |
| con.rollback(); |
| } |
| |
| /** |
| * TEST 5a and 5b for bug 4465 test 5a - create two savepoints in two |
| * different transactions and release the first one in the subsequent |
| * transaction |
| */ |
| public void testBug4465() throws SQLException { |
| Connection con = getConnection(); |
| Savepoint savepoint1 = con.setSavepoint("s1"); |
| con.commit(); |
| // The following savepoint was earlier named s1. Changed it to s2 while |
| // working on DRDA support |
| // for savepoints. The reason for that is as follows |
| // The client translates all savepoint jdbc calls to equivalent sql and |
| // hence |
| // if the 2 savepoints in |
| // different connections are named the same, then the release savepoint |
| // below will get converted to |
| // RELEASE TO SAVEPOINT s1 and that succeeds because the 2nd connection |
| // does have a savepoint named s1. |
| // Hence we don't really check what we intended to check which is trying |
| // to release a savepoint created |
| // in a different transaction |
| con.setSavepoint("s2"); |
| Statement s = createStatement(); |
| s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); |
| try { |
| con.releaseSavepoint(savepoint1); |
| fail("FAIL 5a - release savepoint from a different transaction " |
| + "did not raise error"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("3B001", se); |
| } |
| con.commit(); |
| |
| // test 5b - create two savepoints in two different transactions |
| // and rollback the first one in the subsequent transaction |
| savepoint1 = con.setSavepoint("s1"); |
| con.commit(); |
| // The following savepoint was earlier named s1. Changed it to s2 while |
| // working on DRDA support |
| // for savepoints. The reason for that is as follows |
| // The client translates all savepoint jdbc calls to equivalent sql and |
| // hence |
| // if the 2 savepoints in |
| // different connections are named the same, then the rollback savepoint |
| // below will get converted to |
| // ROLLBACK TO SAVEPOINT s1 and that succeeds because the 2nd connection |
| // does have a savepoint named s1. |
| // Hence we don't really check what we intended to check which is trying |
| // to rollback a savepoint created |
| // in a different transaction |
| con.setSavepoint("s2"); |
| s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); |
| try { |
| con.rollback(savepoint1); |
| fail("FAIL 5b - rollback savepoint from a different transaction " |
| + "did not raise error"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("3B001", se); |
| } |
| con.commit(); |
| } |
| |
| /** |
| * test 6a - create a savepoint release it and then create another with the |
| * same name. and release the first one |
| */ |
| public void testReleaseReleasedSavepoint() throws SQLException { |
| Connection con = getConnection(); |
| Savepoint savepoint1 = con.setSavepoint("s1"); |
| con.releaseSavepoint(savepoint1); |
| // The following savepoint was earlier named s1. Changed it to s2 while |
| // working on DRDA support |
| // for savepoints. The reason for that is as follows |
| // The client translates all savepoint jdbc calls to equivalent sql and |
| // hence |
| // if the 2 savepoints in |
| // a transaction are named the same, then the release savepoint below |
| // will get converted to |
| // RELEASE TO SAVEPOINT s1 and that succeeds because there is a valid |
| // savepoint named s1. |
| con.setSavepoint("s2"); |
| Statement s = createStatement(); |
| s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); |
| try { |
| con.releaseSavepoint(savepoint1); |
| fail("FAIL 6a - releasing a released savepoint did not raise error"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("3B001", se); |
| } |
| con.commit(); |
| } |
| |
| /** |
| * test 6b - create a savepoints release it and then create another with the |
| * same name. and rollback the first one |
| */ |
| public void testRollbackReleasedSavepoint() throws SQLException { |
| Connection con = getConnection(); |
| Savepoint savepoint1 = con.setSavepoint("s1"); |
| con.releaseSavepoint(savepoint1); |
| // The following savepoint was earlier named s1. Changed it to s2 while |
| // working on DRDA support |
| // for savepoints. The reason for that is as follows |
| // The client translates all savepoint jdbc calls to equivalent sql and |
| // hence |
| // if the 2 savepoints in |
| // a transaction are named the same, then the rollback savepoint below |
| // will get converted to |
| // ROLLBACK TO SAVEPOINT s1 and that succeeds because there is a valid |
| // savepoint named s1. |
| con.setSavepoint("s2"); |
| Statement s = createStatement(); |
| s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); |
| try { |
| con.rollback(savepoint1); |
| fail("FAIL 6b - rollback a released savepoint did not raise error"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("3B001", se); |
| } |
| con.commit(); |
| } |
| |
| /** |
| * Test 6c: TEST case just for bug 4467 // Test 10 - create a named |
| * savepoint with the a generated name savepoint1 = |
| * con2.setSavepoint("SAVEPT0"); // what exactly is the correct behaviour |
| * here? try { savepoint2 = con2.setSavepoint(); } catch (SQLException se) { |
| * System.out.println("Expected Exception is " + se.getMessage()); } |
| * con2.commit(); |
| */ |
| public void testReleaseSavepointFromOtherTransaction() throws SQLException { |
| Connection con = getConnection(); |
| Savepoint savepoint1 = con.setSavepoint("s1"); |
| Statement s = createStatement(); |
| s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); |
| Connection con2 = openDefaultConnection(); |
| try { |
| con2.releaseSavepoint(savepoint1); |
| fail("FAIL 6c - releasing another transaction's savepoint did " |
| + "not raise error"); |
| } catch (SQLException se) { |
| // Expected exception. |
| if (usingEmbedded()) { |
| assertSQLState("XJ010", se); |
| } else if (usingDerbyNetClient()) { |
| assertSQLState("XJ008", se); |
| } |
| } |
| con.commit(); |
| con2.commit(); |
| } |
| |
| /** |
| * Test 7a: BUG 4468 - should not be able to pass a savepoint from a |
| * different transaction for release/rollback |
| */ |
| public void testSwapSavepointsAcrossConnectionAndRelease() |
| throws SQLException { |
| Connection con = getConnection(); |
| Connection con2 = openDefaultConnection(); |
| con2.setAutoCommit(false); |
| Savepoint savepoint1 = con2.setSavepoint("s1"); |
| Statement s = createStatement(); |
| s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); |
| con.setSavepoint("s1"); |
| try { |
| con.releaseSavepoint(savepoint1); |
| fail("FAIL 7a - releasing a another transaction's savepoint did " |
| + "not raise error"); |
| } catch (SQLException se) { |
| // Expected exception. |
| if (usingEmbedded()) { |
| assertSQLState("3B502", se); |
| } else if (usingDerbyNetClient()) { |
| assertSQLState("XJ097", se); |
| } |
| |
| } |
| con.commit(); |
| con2.commit(); |
| } |
| |
| /** |
| * Test 7b - swap savepoints across connections |
| */ |
| public void testSwapSavepointsAcrossConnectionsAndRollback() |
| throws SQLException { |
| Connection con = getConnection(); |
| Connection con2 = openDefaultConnection(); |
| con2.setAutoCommit(false); |
| Savepoint savepoint1 = con2.setSavepoint("s1"); |
| Statement s = createStatement(); |
| s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); |
| con.setSavepoint("s1"); |
| try { |
| con.rollback(savepoint1); |
| fail("FAIL 7b - rolling back a another transaction's savepoint " |
| + "did not raise error"); |
| } catch (SQLException se) { |
| // Expected exception. |
| if (usingEmbedded()) { |
| assertSQLState("3B502", se); |
| } else if (usingDerbyNetClient()) { |
| assertSQLState("XJ097", se); |
| } |
| } |
| con.commit(); |
| con2.commit(); |
| } |
| |
| /* |
| * following section attempts to call statement in a method to do a negative |
| * test because savepoints are not supported in a trigger however, this |
| * cannot be done because a call is not supported in a trigger. leaving the |
| * test here for later reference for when we support the SQL version // bug |
| * 4507 - Test 8 test all 4 savepoint commands inside the trigger code |
| * System.out.println("Test 8a set savepoint(unnamed) command inside the |
| * trigger code"); s.executeUpdate("create trigger trig1 before insert on t1 |
| * for each statement call |
| * org.apache.derbyTesting.functionTests.tests.jdbcapi.savepointJdbc30::doConnectionSetSavepointUnnamed()"); |
| * try { |
| * |
| * s.executeUpdate("insert into t1 values(1,1)"); System.out.println("FAIL |
| * 8a set savepoint(unnamed) command inside the trigger code"); } catch |
| * (SQLException se) { System.out.println("Expected Exception is " + |
| * se.getMessage()); } s.executeUpdate("drop trigger trig1"); |
| * |
| * System.out.println("Test 8b set savepoint(named) command inside the |
| * trigger code"); s.executeUpdate("create trigger trig2 before insert on t1 |
| * for each statement call |
| * org.apache.derbyTesting.functionTests.tests.jdbcapi.savepointJdbc30::doConnectionSetSavepointNamed()"); |
| * try { s.executeUpdate("insert into t1 values(1,1)"); |
| * System.out.println("FAIL 8b set savepoint(named) command inside the |
| * trigger code"); } catch (SQLException se) { System.out.println("Expected |
| * Exception is " + se.getMessage()); } s.executeUpdate("drop trigger |
| * trig2"); |
| * |
| * System.out.println("Test 8c release savepoint command inside the trigger |
| * code"); s.executeUpdate("create trigger trig3 before insert on t1 for |
| * each statement call |
| * org.apache.derbyTesting.functionTests.tests.jdbcapi.savepointJdbc30::doConnectionReleaseSavepoint()"); |
| * try { s.executeUpdate("insert into t1 values(1,1)"); |
| * System.out.println("FAIL 8c release savepoint command inside the trigger |
| * code"); } catch (SQLException se) { System.out.println("Expected |
| * Exception is " + se.getMessage()); } s.executeUpdate("drop trigger |
| * trig3"); |
| * |
| * System.out.println("Test 8d rollback savepoint command inside the trigger |
| * code"); s.executeUpdate("create trigger trig4 before insert on t1 for |
| * each statement call |
| * org.apache.derbyTesting.functionTests.tests.jdbcapi.savepointJdbc30::doConnectionRollbackSavepoint()"); |
| * try { s.executeUpdate("insert into t1 values(1,1)"); |
| * System.out.println("FAIL 8d rollback savepoint command inside the trigger |
| * code"); } catch (SQLException se) { System.out.println("Expected |
| * Exception is " + se.getMessage()); } s.executeUpdate("drop trigger |
| * trig4"); con.rollback(); |
| */// end commented out test 8 |
| /** |
| * Test 9 test savepoint name and verify case sensitivity |
| */ |
| public void testSavepointName() throws SQLException { |
| Connection con = getConnection(); |
| Savepoint savepoint1 = con.setSavepoint("myname"); |
| String savepointName = savepoint1.getSavepointName(); |
| assertEquals(savepointName, "myname"); |
| con.rollback(); |
| } |
| |
| /** |
| * Test 10 test savepoint name case sensitivity |
| */ |
| public void testNameCaseSensitivity() throws SQLException { |
| Connection con = getConnection(); |
| Savepoint savepoint1 = con.setSavepoint("MyName"); |
| String savepointName = savepoint1.getSavepointName(); |
| assertEquals(savepointName, "MyName"); |
| con.rollback(); |
| } |
| |
| /** |
| * Test 11 rolling back a savepoint multiple times - should work |
| */ |
| public void testRollbackMultipleTimes() throws SQLException { |
| Connection con = getConnection(); |
| Savepoint savepoint1 = con.setSavepoint("MyName"); |
| con.rollback(savepoint1); |
| con.rollback(savepoint1); |
| con.rollback(); |
| } |
| |
| /** |
| * Test 12 releasing a savepoint multiple times - should not work |
| */ |
| public void testReleaseMultipleTimes() throws SQLException { |
| Connection con = getConnection(); |
| Savepoint savepoint1 = con.setSavepoint("MyName"); |
| con.releaseSavepoint(savepoint1); |
| try { |
| con.releaseSavepoint(savepoint1); |
| fail("FAIL 12 releasing a savepoint multiple times should fail"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("3B001", se); |
| } |
| con.rollback(); |
| } |
| |
| /** |
| * Test 13 shouldn't be able to use a savepoint from earlier transaction |
| * after setting autocommit on and off |
| */ |
| public void testSavepointFromEarlierTransactionAfterToggleAutocommit() |
| throws SQLException { |
| Connection con = getConnection(); |
| Savepoint savepoint1 = con.setSavepoint("MyName"); |
| con.setAutoCommit(true); |
| con.setAutoCommit(false); |
| Savepoint savepoint2 = con.setSavepoint("MyName1"); |
| try {// shouldn't be able to use savepoint from earlier tranasaction |
| // after setting autocommit on and off |
| con.releaseSavepoint(savepoint1); |
| fail("FAIL 13 shouldn't be able to use a savepoint from earlier " |
| + "transaction after setting autocommit on and off"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("3B001", se); |
| } |
| con.releaseSavepoint(savepoint2); |
| con.rollback(); |
| } |
| |
| /** |
| * Test 14 cause a transaction rollback and that should release the internal |
| * savepoint array |
| */ |
| public void testRollbackReleasesSavepointArray() throws SQLException { |
| Connection con = getConnection(); |
| Connection con2 = openDefaultConnection(); |
| con2.setAutoCommit(false); |
| Statement s1, s2; |
| s1 = createStatement(); |
| s1.executeUpdate("insert into t1 values(1,1)"); |
| s1.executeUpdate("insert into t1 values(2,0)"); |
| con.commit(); |
| s1.executeUpdate("update t1 set c11=c11+1 where c12 > 0"); |
| s2 = con2.createStatement(); |
| Savepoint savepoint1 = con2.setSavepoint("MyName"); |
| try {// following will get lock timeout which will rollback |
| // transaction on c2 |
| s2.executeUpdate("update t1 set c11=c11+1 where c12 < 1"); |
| fail("FAIL 14 should have gotten lock time out"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("40XL1", se); |
| } |
| try {// the transaction rollback above should have removed the |
| // savepoint MyName |
| con2.releaseSavepoint(savepoint1); |
| fail("FAIL 14 A non-user initiated transaction rollback should " |
| + "release the internal savepoint array"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("3B001", se); |
| } |
| con.rollback(); |
| con2.rollback(); |
| s1.execute("delete from t1"); |
| con.commit(); |
| } |
| |
| /** Test 15 Check savepoints in batch */ |
| public void testSavepointsInBatch() throws SQLException { |
| Connection con = getConnection(); |
| Statement s = createStatement(); |
| s.execute("delete from t1"); |
| s.addBatch("insert into t1 values(1,1)"); |
| s.addBatch("insert into t1 values(2,2)"); |
| Savepoint savepoint1 = con.setSavepoint(); |
| s.addBatch("insert into t1 values(3,3)"); |
| s.executeBatch(); |
| con.rollback(savepoint1); |
| |
| assertTableRowCount("T1", 0); |
| con.rollback(); |
| } |
| |
| /** Test 16 grammar check for savepoint sq1 */ |
| public void testGrammarCheck() throws SQLException { |
| Statement s = getConnection().createStatement(); |
| try { |
| s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS"); |
| fail("FAIL 16 Should have gotten exception for missing ON ROLLBACK " |
| + "RETAIN CURSORS"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("42X01", se); |
| } |
| try { |
| s.executeUpdate("SAVEPOINT s1 UNIQUE ON ROLLBACK RETAIN CURSORS " |
| + "ON ROLLBACK RETAIN CURSORS"); |
| fail("FAIL 16 Should have gotten exception for multiple ON ROLLBACK " |
| + "RETAIN CURSORS"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("42613", se); |
| } |
| try { |
| s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK" |
| + " RETAIN LOCKS"); |
| fail("FAIL 16 Should have gotten exception for multiple ON ROLLBACK " |
| + "RETAIN LOCKS"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("42613", se); |
| } |
| try { |
| s.executeUpdate("SAVEPOINT s1 UNIQUE UNIQUE ON ROLLBACK RETAIN " |
| + "LOCKS ON ROLLBACK RETAIN CURSORS"); |
| fail("FAIL 16 Should have gotten exception for multiple UNIQUE keywords"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("42613", se); |
| } |
| s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN CURSORS ON ROLLBACK " |
| + "RETAIN LOCKS"); |
| s.executeUpdate("RELEASE TO SAVEPOINT s1"); |
| getConnection().rollback(); |
| } |
| |
| /** Test 17 */ |
| public void testNoNestedSavepointsWhenUsingSQL() throws SQLException { |
| Statement s = getConnection().createStatement(); |
| s.executeUpdate("SAVEPOINT s1 UNIQUE ON ROLLBACK RETAIN LOCKS ON " |
| + "ROLLBACK RETAIN CURSORS"); |
| try { |
| s.executeUpdate("SAVEPOINT s2 UNIQUE ON ROLLBACK RETAIN " |
| + "LOCKS ON ROLLBACK RETAIN CURSORS"); |
| fail("FAIL 17a Should have gotten exception for nested savepoints"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("3B002", se); |
| } |
| s.executeUpdate("RELEASE TO SAVEPOINT s1"); |
| s.executeUpdate("SAVEPOINT s2 UNIQUE ON ROLLBACK RETAIN LOCKS ON " |
| + "ROLLBACK RETAIN CURSORS"); |
| getConnection().rollback(); |
| |
| s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK " |
| + "RETAIN CURSORS"); |
| try { |
| s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON " |
| + "ROLLBACK RETAIN CURSORS"); |
| fail("FAIL 17b Should have gotten exception for nested savepoints"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("3B002", se); |
| } |
| getConnection().rollback(); |
| } |
| |
| /** Test 18 */ |
| public void testNoNestedSavepointsInsideJdbcSavepoint() throws SQLException { |
| Connection con = getConnection(); |
| Savepoint savepoint1 = con.setSavepoint(); |
| Statement s = getConnection().createStatement(); |
| // Following SQL savepoint will fail because we are trying to nest it |
| // inside JDBC savepoint |
| try { |
| s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK" |
| + " RETAIN CURSORS"); |
| fail("FAIL 18 shouldn't be able set SQL savepoint nested inside " |
| + "JDBC savepoints"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("3B002", se); |
| } |
| // rollback the JDBC savepoint. Now since there are no user defined |
| // savepoints, we can define SQL savepoint |
| con.releaseSavepoint(savepoint1); |
| s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON " |
| + "ROLLBACK RETAIN CURSORS"); |
| con.rollback(); |
| } |
| |
| /** Test 19 */ |
| public void testNoNestedSavepointsInsideSqlSavepoint() throws SQLException { |
| Statement s = getConnection().createStatement(); |
| s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK " |
| + "RETAIN CURSORS"); |
| try { |
| s.executeUpdate("SAVEPOINT s2 ON ROLLBACK RETAIN LOCKS ON ROLLBACK" |
| + " RETAIN CURSORS"); |
| fail("FAIL 19 shouldn't be able set SQL savepoint nested inside " |
| + "SQL savepoint"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("3B002", se); |
| } |
| // rollback the SQL savepoint. Now since there are no user defined |
| // savepoints, we can define SQL savepoint |
| s.executeUpdate("RELEASE TO SAVEPOINT s1"); |
| s.executeUpdate("SAVEPOINT s2 ON ROLLBACK RETAIN LOCKS ON ROLLBACK " |
| + "RETAIN CURSORS"); |
| getConnection().rollback(); |
| } |
| |
| /** Test 20 */ |
| public void testRollbackSqlSavepointSameAsJdbc() throws SQLException { |
| Connection con = getConnection(); |
| Statement s = createStatement(); |
| s.executeUpdate("DELETE FROM T1"); |
| con.commit(); |
| s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK " |
| + "RETAIN CURSORS"); |
| s.executeUpdate("INSERT INTO T1 VALUES(1,1)"); |
| s.executeUpdate("INSERT INTO T1 VALUES(1,1)"); |
| s.executeUpdate("INSERT INTO T1 VALUES(1,1)"); |
| // Rollback to SQL savepoint and should see changes rolledback |
| s.execute("ROLLBACK TO SAVEPOINT s1"); |
| |
| ResultSet rs1 = s.executeQuery("select count(*) from t1"); |
| rs1.next(); |
| assertEquals(rs1.getInt(1), 0); |
| con.rollback(); |
| } |
| |
| /** Test 21 */ |
| public void testReleaseSqlSavepointAndRollback() throws SQLException { |
| Connection con = getConnection(); |
| Statement s = createStatement(); |
| s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK " |
| + "RETAIN CURSORS"); |
| s.executeUpdate("INSERT INTO T1 VALUES(1,1)"); |
| s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); |
| s.executeUpdate("INSERT INTO T1 VALUES(3,1)"); |
| // Release the SQL savepoint and then rollback the transaction and |
| // should see changes rolledback |
| s.executeUpdate("RELEASE TO SAVEPOINT s1"); |
| con.rollback(); |
| ResultSet rs1 = s.executeQuery("select count(*) from t1"); |
| rs1.next(); |
| assertEquals(rs1.getInt(1), 0); |
| con.rollback(); |
| } |
| |
| /** Test 22 */ |
| public void testNoSqlSavepointStartingWithSYS() throws SQLException { |
| Statement s = createStatement(); |
| try { |
| s.executeUpdate("SAVEPOINT SYSs2 ON ROLLBACK RETAIN LOCKS ON " |
| + "ROLLBACK RETAIN CURSORS"); |
| fail("FAIL 22 shouldn't be able to create a SQL savepoint starting " |
| + "with name SYS"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("42939", se); |
| } |
| getConnection().rollback(); |
| } |
| |
| /** |
| * Test 23 - bug 5817 - make savepoint and release non-reserved keywords |
| */ |
| public void testBug5817() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("create table savepoint (savepoint int, release int)"); |
| ResultSet rs1 = s.executeQuery("select count(*) from savepoint"); |
| rs1.next(); |
| assertEquals(" There should have been 0 rows in the table, but found " |
| + rs1.getInt(1), rs1.getInt(1), 0); |
| |
| s.execute("SAVEPOINT savepoint ON ROLLBACK RETAIN LOCKS ON ROLLBACK " |
| + "RETAIN CURSORS"); |
| s.executeUpdate("INSERT INTO savepoint VALUES(1,1)"); |
| s.execute("RELEASE SAVEPOINT savepoint"); |
| rs1 = s.executeQuery("select count(*) from savepoint"); |
| rs1.next(); |
| assertEquals("There should have been 1 rows in the table, but found " |
| + rs1.getInt(1), rs1.getInt(1), 1); |
| |
| s.execute("SAVEPOINT release ON ROLLBACK RETAIN LOCKS ON ROLLBACK " |
| + "RETAIN CURSORS"); |
| s.executeUpdate("INSERT INTO savepoint VALUES(2,1)"); |
| s.execute("ROLLBACK TO SAVEPOINT release"); |
| rs1 = s.executeQuery("select count(*) from savepoint"); |
| rs1.next(); |
| assertEquals( |
| "ERROR: There should have been 1 rows in the table, but found " |
| + rs1.getInt(1), rs1.getInt(1), 1); |
| |
| s.execute("RELEASE SAVEPOINT release"); |
| getConnection().rollback(); |
| } |
| |
| /** |
| * Test 24 Savepoint name can't exceed 128 characters |
| */ |
| public void testNameLengthMax128Chars() throws SQLException { |
| try { |
| getConnection() |
| .setSavepoint( |
| "MyName12345678901234567890123456789" |
| + "01234567890123456789012345678901234567890123456789012345" |
| + "678901234567890123456789012345678901234567890"); |
| fail("FAIL 24 shouldn't be able to create a SQL savepoint with " |
| + "name exceeding 128 characters"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("42622", se); |
| } |
| getConnection().rollback(); |
| } |
| |
| /** Test 25 */ |
| public void testNoSqlSavepointStartingWithSYSThroughJdbc() |
| throws SQLException { |
| try { |
| getConnection().setSavepoint("SYSs2"); |
| fail("FAIL 25 shouldn't be able to create a SQL savepoint starting with name SYS through jdbc"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("42939", se); |
| } |
| getConnection().rollback(); |
| } |
| |
| /** |
| * bug 4451 - Test 26a pass Null value to rollback bug 5374 - Passing a null |
| * savepoint to rollback or release method used to give a npe in JCC it |
| * should give a SQLException aying "Cannot rollback to a null savepoint" |
| */ |
| public void testRollbackNullSavepoint() throws SQLException { |
| try { |
| getConnection().rollback((Savepoint) null); |
| fail("FAIL 26a rollback of null savepoint did not raise error "); |
| } catch (SQLException se) { |
| // Expected exception. |
| if (usingEmbedded()) { |
| assertSQLState("3B001", se); |
| } else if (usingDerbyNetClient()) { |
| assertSQLState("3B502", se); |
| } |
| } |
| } |
| |
| /** |
| * Test 26b pass Null value to releaseSavepoint |
| */ |
| public void testReleaseNullSavepoint() throws SQLException { |
| try { |
| getConnection().releaseSavepoint((Savepoint) null); |
| fail("FAIL 26b release of null savepoint did not raise error "); |
| } catch (SQLException se) { |
| // Expected exception. |
| if (usingEmbedded()) { |
| assertSQLState("3B001", se); |
| } else if (usingDerbyNetClient()) { |
| assertSQLState("3B502", se); |
| } |
| } |
| } |
| |
| /** |
| * Test that savepoint names can have double-quote characters. The client |
| * driver used to fail with a syntax error when the names contained such |
| * characters. DERBY-5170. |
| */ |
| public void testQuotes() throws SQLException { |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| s.execute("create table test_quotes(x int)"); |
| s.execute("insert into test_quotes values 1"); |
| |
| Savepoint sp = getConnection().setSavepoint("a \" b ' c"); |
| |
| s.execute("insert into test_quotes values 2"); |
| |
| getConnection().rollback(sp); |
| |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("select * from test_quotes"), |
| "1"); |
| |
| getConnection().releaseSavepoint(sp); |
| } |
| |
| /** ********************* */ |
| |
| /* |
| * The following tests have nested savepoints through JDBC calls. When |
| * coming through the network client these nested JDBC savepoint calls are |
| * translated into equivalent SQL savepoint statements. But we do not allow |
| * nested savepoints coming through SQL statements and hence these tests |
| * can't be run under DRDA framework. |
| */ |
| |
| /** |
| * Test40 - We internally generate a unique name for unnamed savepoints. If |
| * a named savepoint uses the currently used internal savepoint name, we |
| * won't get an exception thrown for it because we prepend external saves |
| * with "e." to avoid name conflicts. |
| */ |
| public void xtestNoConflictWithGeneratedName() throws SQLException { |
| Connection con = getConnection(); |
| |
| con.setSavepoint(); |
| con.setSavepoint("i.SAVEPT0"); |
| con.rollback(); |
| } |
| |
| /** |
| * Test41 - Rolling back to a savepoint will release all the savepoints |
| * created after that savepoint. |
| */ |
| public void xtestRollbackWillReleaseLaterSavepoints() throws SQLException { |
| Connection con = getConnection(); |
| |
| Statement s = createStatement(); |
| |
| // Make sure T1 is empty (testcase running order might have left content!): |
| s.execute("DELETE FROM T1"); |
| |
| Savepoint savepoint1 = con.setSavepoint(); |
| |
| s.executeUpdate("INSERT INTO T1 VALUES(1,1)"); |
| |
| Savepoint savepoint2 = con.setSavepoint("s1"); |
| s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); |
| |
| Savepoint savepoint3 = con.setSavepoint("s2"); |
| s.executeUpdate("INSERT INTO T1 VALUES(3,1)"); |
| |
| // Rollback to first named savepoint s1. This will internally release |
| // the second named savepoint s2. |
| con.rollback(savepoint2); |
| assertTableRowCount("T1", 1); |
| |
| // Trying to release second named savepoint s2 should throw exception. |
| try { |
| con.releaseSavepoint(savepoint3); |
| fail("FAIL 41a release of rolled back savepoint"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("3B001", se); |
| } |
| // Trying to rollback second named savepoint s2 should throw exception. |
| try { |
| con.rollback(savepoint3); |
| fail("FAIL 41b release of rolled back savepoint"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("3B001", se); |
| } |
| |
| // Release the unnamed named savepoint. |
| con.rollback(savepoint1); |
| assertTableRowCount("T1", 0); |
| con.rollback(); |
| } |
| |
| /** |
| * Test42 - Rollback on a connection will release all the savepoints created |
| * for that transaction |
| */ |
| public void xtestRollbackWillReleaseActiveSavepoints() throws SQLException { |
| Connection con = getConnection(); |
| Savepoint savepoint1 = con.setSavepoint(); |
| con.rollback(); |
| try { |
| con.rollback(savepoint1); |
| fail("FAIL 42 release of rolled back savepoint"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("3B001", se); |
| } |
| } |
| |
| /** |
| * Test42a - Commit on a connection will release all the savepoints created |
| * for that transaction |
| */ |
| public void xtestCommitWillReleaseActiveSavepoints() throws SQLException { |
| Connection con = getConnection(); |
| Savepoint savepoint1 = con.setSavepoint(); |
| con.commit(); |
| try { |
| con.rollback(savepoint1); |
| fail("FAIL 42a Rollback after commit."); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("3B001", se); |
| } |
| } |
| |
| /** |
| * Test43 - After releasing a savepoint, should be able to reuse it. |
| */ |
| public void xtestReuseNameAfterRelease() throws SQLException { |
| Connection con = getConnection(); |
| Savepoint savepoint1 = con.setSavepoint("s1"); |
| try { |
| con.setSavepoint("s1"); |
| fail("Should not be able to set two savepoints with the same name"); |
| } catch (SQLException se) { |
| // Expected exception. |
| if (usingEmbedded()) { |
| assertSQLState("3B501", se); |
| } else if (usingDerbyNetClient()) { |
| assertSQLState("3B002", se); |
| } |
| } |
| con.releaseSavepoint(savepoint1); |
| con.setSavepoint("s1"); |
| con.rollback(); |
| } |
| |
| /** |
| * Test 45 reuse savepoint name after rollback - should not work |
| */ |
| public void xtestReuseNameAfterRollback() throws SQLException { |
| Connection con = getConnection(); |
| Savepoint savepoint1 = con.setSavepoint("MyName"); |
| con.rollback(savepoint1); |
| try { |
| con.setSavepoint("MyName"); |
| fail("FAIL 45 reuse of savepoint name after rollback should fail"); |
| } catch (SQLException se) { |
| // Expected exception. |
| if (usingEmbedded()) { |
| assertSQLState("3B501", se); |
| } else if (usingDerbyNetClient()) { |
| assertSQLState("3B002", se); |
| } |
| } |
| con.rollback(); |
| } |
| |
| /** |
| * Test 46 bug 5145 Cursors declared before and within the savepoint unit |
| * will be closed when rolling back the savepoint |
| */ |
| public void xtestCursorsCloseOnRollback() throws SQLException { |
| Connection con = getConnection(); |
| Statement sWithHold = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, |
| ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT); |
| Statement s = createStatement(); |
| s.executeUpdate("DELETE FROM T1"); |
| s.executeUpdate("INSERT INTO T1 VALUES(19,1)"); |
| s.executeUpdate("INSERT INTO T1 VALUES(19,2)"); |
| s.executeUpdate("INSERT INTO T1 VALUES(19,3)"); |
| ResultSet rs1 = s.executeQuery("select * from t1"); |
| rs1.next(); |
| ResultSet rs1WithHold = sWithHold.executeQuery("select * from t1"); |
| rs1WithHold.next(); |
| Savepoint savepoint1 = con.setSavepoint(); |
| ResultSet rs2 = s.executeQuery("select * from t1"); |
| rs2.next(); |
| ResultSet rs2WithHold = sWithHold.executeQuery("select * from t1"); |
| rs2WithHold.next(); |
| con.rollback(savepoint1); |
| try {// resultset declared outside the savepoint unit should be |
| // closed at this point after the rollback to savepoint |
| rs1.next(); |
| fail("FAIL 46 shouldn't be able to use a resultset (declared " |
| + "before the savepoint unit) after the rollback to savepoint"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("XCL16", se); |
| } |
| try {// holdable resultset declared outside the savepoint unit should |
| // be closed at this point after the rollback to savepoint |
| rs1WithHold.next(); |
| fail("FAIL 46 shouldn't be able to use a holdable resultset " |
| + "(declared before the savepoint unit) after the rollback " |
| + "to savepoint"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("XCL16", se); |
| } |
| try {// resultset declared within the savepoint unit should be closed |
| // at this point after the rollback to savepoint |
| rs2.next(); |
| fail("FAIL 46 shouldn't be able to use a resultset (declared within " |
| + "the savepoint unit) after the rollback to savepoint"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("XCL16", se); |
| } |
| try {// holdable resultset declared within the savepoint unit should |
| // be closed at this point after the rollback to savepoint |
| rs2WithHold.next(); |
| fail("FAIL 46 shouldn't be able to use a holdable resultset " |
| + "(declared within the savepoint unit) after the rollback " |
| + "to savepoint"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("XCL16", se); |
| } |
| con.rollback(); |
| } |
| |
| /** |
| * Test 47 multiple tests for getSavepointId() |
| */ |
| public void xtestGetSavepoint() throws SQLException { |
| Connection con = getConnection(); |
| Savepoint savepoint1 = con.setSavepoint(); |
| Savepoint savepoint2 = con.setSavepoint(); |
| savepoint1.getSavepointId(); |
| savepoint2.getSavepointId(); |
| con.releaseSavepoint(savepoint2); |
| savepoint2 = con.setSavepoint(); |
| savepoint2.getSavepointId(); |
| con.commit(); |
| savepoint2 = con.setSavepoint(); |
| savepoint2.getSavepointId(); |
| con.rollback(); |
| savepoint2 = con.setSavepoint(); |
| savepoint2.getSavepointId(); |
| con.rollback(); |
| } |
| |
| /** |
| * Test 48 |
| */ |
| public void xtestNestedSavepoints() throws SQLException { |
| Connection con = getConnection(); |
| Savepoint savepoint1 = con.setSavepoint(); |
| Savepoint savepoint2 = con.setSavepoint(); |
| Statement s = createStatement(); |
| try { |
| s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK" |
| + " RETAIN CURSORS"); |
| fail("FAIL 48 shouldn't be able set SQL savepoint nested inside " |
| + "JDBC/SQL savepoints"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("3B002", se); |
| } |
| // rollback JDBC savepoint but still can't have SQL savepoint because |
| // there is still one JDBC savepoint |
| con.releaseSavepoint(savepoint2); |
| try { |
| s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK" |
| + " RETAIN CURSORS"); |
| fail("FAIL 48 Should have gotten exception for nested SQL savepoint"); |
| } catch (SQLException se) { |
| // Expected exception. |
| assertSQLState("3B002", se); |
| |
| } |
| con.releaseSavepoint(savepoint1); // rollback last JDBC savepoint and |
| // now try SQL savepoint again |
| s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK " |
| + "RETAIN CURSORS"); |
| con.rollback(); |
| } |
| |
| } |