savepointjdbc30.java

derby database source code.good for you.

	//These tests do not allow savepoint nesting and hence can be run under DRDA too
	static void genericTests(Connection con, Connection con2, Statement s)
					throws SQLException {

		ResultSet rs1, rs2, rs1WithHold, rs2WithHold;
		Savepoint savepoint1, savepoint2, savepoint3, savepoint4;

		//Test1 and Test1a fail under DRDA (bug 5384).
		//Test1 - No savepoint allowed when auto commit is true
		con.setAutoCommit(true); // make sure it is true
		try
		{
			System.out.println("Test1 - no unnamed savepoints allowed if autocommit is true");
			con.setSavepoint(); // will throw exception because auto commit is true
			System.out.println("FAIL 1 - auto commit on");
		}
		catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		//Test1a - No savepoint allowed when auto commit is true
		try {
			System.out.println("Test1a - no named savepoints allowed if autocommit is true");
			con.setSavepoint("notallowed"); // will throw exception because auto commit is true
			System.out.println("FAIL 1a - auto commit on");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}

		con.setAutoCommit(false); // make sure it is false

		//Test2 - After releasing a savepoint, should be able to reuse it.
		System.out.println("Test2 - Release and reuse a savepoint name");
		savepoint1 = con.setSavepoint("s1");
		con.releaseSavepoint(savepoint1);
		savepoint2 = con.setSavepoint("s1");
		con.rollback();

		//Test3 - Named savepoints can't pass null for name
		try {
			System.out.println("Test3 - null name not allowed for named savepoints");
			con.setSavepoint(null);
			System.out.println("FAIL 3 null savepoint ");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		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 thro jdbc api)
		System.out.println("Test4 - Verify names/ids of named/unnamed savepoints");
		try {
			savepoint1 = con.setSavepoint();
			savepoint1.getSavepointId();
			//following should throw exception for un-named savepoint
			savepoint1.getSavepointName();
			System.out.println("FAIL 4 getSavepointName on id savepoint ");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		con.rollback();
		try {
			savepoint1 = con.setSavepoint("s1");
			savepoint1.getSavepointName();
			//following should throw exception for named savepoint
			savepoint1.getSavepointId();
			System.out.println("FAIL 4 getSavepointId on named savepoint ");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		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
		System.out.println("Test5a - create two savepoints in two different transactions" +
			" and release 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
		//JCC 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
		savepoint2 = con.setSavepoint("s2");
		s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
		try	{
			con.releaseSavepoint(savepoint1);
			System.out.println("FAIL 5a - release savepoint from a different transaction did not raise error");
		}	catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		con.commit();

		// test 5b - create two savepoints in two different transactions
		// and rollback the first one in the subsequent transaction
		System.out.println("Test5b - 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
		//JCC 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
		savepoint2 = con.setSavepoint("s2");
		s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
		try {
			con.rollback(savepoint1);
			System.out.println("FAIL 5b - rollback savepoint from a different transaction did not raise error");
		}	catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		con.commit();

		// test 6a - create a savepoint release it and then create another with the same name.
		// and release the first one
		System.out.println("Test6a - create a savepoint, release it, create another with" +
			" same name and release the first one");
		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
		//JCC 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.
		savepoint2 = con.setSavepoint("s2");
		s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
		try {
			con.releaseSavepoint(savepoint1);
			System.out.println("FAIL 6a - releasing a released savepoint did not raise error");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		con.commit();

		// test 6b - create a savepoints release it and then create another with the same name.
		// and rollback the first one
		System.out.println("Test6b - create a savepoint, release it, create another with" +
			" same name and rollback the first one");
		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
		//JCC 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.
		savepoint2 = con.setSavepoint("s2");
		s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
		try {
			con.rollback(savepoint1);
			System.out.println("FAIL 6b - rollback a released savepoint did not raise error");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		con.commit();

/* 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();
*/

		System.out.println("Test6c - Try to use a savepoint from another connection for release");
		savepoint1 = con.setSavepoint("s1");
		s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
		try {
			con2.releaseSavepoint(savepoint1);
			System.out.println("FAIL 6c - releasing another transaction's savepoint did not raise error");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		con.commit();
		con2.commit();

		/* BUG 4468 - should not be able to pass a savepoint from a different transaction for release/rollback */
		// Test 7a - swap savepoints across connections
		System.out.println("Test7a - swap savepoints across connections with release");
		savepoint1 = con2.setSavepoint("s1");
		s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
		savepoint2 = con.setSavepoint("s1");
		try {
			con.releaseSavepoint(savepoint1);
			System.out.println("FAIL 7a - releasing a another transaction's savepoint did not raise error");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		con.commit();
		con2.commit();

		// Test 7b - swap savepoints across connections
		System.out.println("Test7b - swap savepoints across connections with rollback");
		savepoint1 = con2.setSavepoint("s1");
		s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
		savepoint2 = con.setSavepoint("s1");
		try {
			con.rollback(savepoint1);
			System.out.println("FAIL 7b - rolling back a another transaction's savepoint did not raise error");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		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
		System.out.println("Test 9 test savepoint name");
		savepoint1 = con.setSavepoint("myname");
		String savepointName = savepoint1.getSavepointName();
		if (!savepointName.equals("myname"))
			System.out.println("fail - savepoint name mismatch");
		con.rollback();

		// Test 10 test savepoint name case sensitivity
		System.out.println("Test 10 test savepoint name case sensitivity");
		savepoint1 = con.setSavepoint("MyName");
		savepointName = savepoint1.getSavepointName();
		if (!savepointName.equals("MyName"))
			System.out.println("fail - savepoint name mismatch");
		con.rollback();

		// Test 11 rolling back a savepoint multiple times - should work
		System.out.println("Test 11 rolling back a savepoint multiple times - should work");
		savepoint1 = con.setSavepoint("MyName");
		con.rollback(savepoint1);
		try {
			con.rollback(savepoint1);
		} catch (SQLException se) {
			System.out.println("FAIL 11 second rollback failed");
			System.out.println("Exception is " + se.getMessage());
		}
		con.rollback();

		// Test 12 releasing a savepoint multiple times - should not work
		System.out.println("Test 12 releasing a savepoint multiple times - should not work");
		savepoint1 = con.setSavepoint("MyName");
		con.releaseSavepoint(savepoint1);
		try {
			con.releaseSavepoint(savepoint1);
			System.out.println("FAIL 12 releasing a savepoint multiple times should fail");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		con.rollback();

		// Test 13 shouldn't be able to use a savepoint from earlier transaction after setting autocommit on and off
		System.out.println("Test 13 shouldn't be able to use a savepoint from earlier transaction after setting autocommit on and off");
		savepoint1 = con.setSavepoint("MyName");
		con.setAutoCommit(true);
		con.setAutoCommit(false);
		savepoint2 = con.setSavepoint("MyName1");
		try {//shouldn't be able to use savepoint from earlier tranasaction after setting autocommit on and off
			con.releaseSavepoint(savepoint1);
			System.out.println("FAIL 13 shouldn't be able to use a savepoint from earlier transaction after setting autocommit on and off");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		con.releaseSavepoint(savepoint2);
		con.rollback();