/*
*
* 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 junit.framework.TestSuite;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
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() {
TestSuite suite = new TestSuite("SavepointJdbc30_JSR169Test suite");
// Get the tests for embedded
TestSuite embedded = new TestSuite(
"SavepointJdbc30_JSR169Test:embedded");
embedded.addTestSuite(SavepointJdbc30Test.class);
embedded.addTest(getEmbeddedSuite("SavepointJdbc30_JSR169Test:"
+ "embedded only"));
suite.addTest(embedded);
// Get the tests for client.
TestSuite client = new TestSuite("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 TestSuite(
"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 TestSuite("SavepointJdbc30_JSR169Test:client XADatasource");
client.addTestSuite(SavepointJdbc30Test.class);
suite.addTest(TestConfiguration.clientServerDecorator(TestConfiguration.connectionXADecorator(client)));
}
// return suite;
return new CleanDatabaseTestSetup(suite) {
/**
* 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) {
TestSuite embedded = new TestSuite(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;
}
/**
* setUp: Just keep AutoCommit off.
*/
protected void setUp() throws Exception {
super.setUp();
// Keep Autocommit off
getConnection().setAutoCommit(false);
}
/**
* 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);
}
}
}
/** ********************* */
/*
* 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();
}
}