savepointjdbc30.java

derby database source code.good for you.

		// Test 14 cause a transaction rollback and that should release the internal savepoint array
		System.out.println("Test 14 A non-user initiated transaction rollback should release the internal savepoint array");
		Statement s1, s2;
		s1 = con.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();
		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");
			System.out.println("FAIL 14 should have gotten lock time out");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		try {//the transaction rollback above should have removed the savepoint MyName
			con2.releaseSavepoint(savepoint1);
			System.out.println("FAIL 14 A non-user initiated transaction rollback should release the internal savepoint array");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		con.rollback();
		con2.rollback();
		s.execute("delete from t1");
		con.commit();

		// Test 15 check savepoints in batch
		System.out.println("Test 15 check savepoints in batch");
		s.execute("delete from t1");
		s.addBatch("insert into t1 values(1,1)");
		s.addBatch("insert into t1 values(1,1)");
		savepoint1 = con.setSavepoint();
		s.addBatch("insert into t1 values(1,1)");
		s.executeBatch();
		con.rollback(savepoint1);
		int val = count(con,s);
		if (val != 0)
			System.out.println("FAIL 15 savepoint should have been set before batch");
		con.rollback();

		// Test 16 grammar check for savepoint sq1
		System.out.println("Test 16 grammar check for savepoint sq1");
		try {
			s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS");
			System.out.println("FAIL 16 Should have gotten exception for missing ON ROLLBACK RETAIN CURSORS");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		try {
			s.executeUpdate("SAVEPOINT s1 UNIQUE ON ROLLBACK RETAIN CURSORS ON ROLLBACK RETAIN CURSORS");
			System.out.println("FAIL 16 Should have gotten exception for multiple ON ROLLBACK RETAIN CURSORS");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		try {
			s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN LOCKS");
			System.out.println("FAIL 16 Should have gotten exception for multiple ON ROLLBACK RETAIN LOCKS");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		try {
			s.executeUpdate("SAVEPOINT s1 UNIQUE UNIQUE ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
			System.out.println("FAIL 16 Should have gotten exception for multiple UNIQUE keywords");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN CURSORS ON ROLLBACK RETAIN LOCKS");
		s.executeUpdate("RELEASE TO SAVEPOINT s1");
		con.rollback();

		// Test 17
		System.out.println("Test 17 No nested savepoints allowed when using SQL to set savepoints.");
		System.out.println("Test 17a Test with UNIQUE clause.");
		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");
			System.out.println("FAIL 17a Should have gotten exception for nested savepoints");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		s.executeUpdate("RELEASE TO SAVEPOINT s1");
		s.executeUpdate("SAVEPOINT s2 UNIQUE ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
		con.rollback();

		System.out.println("Test 17b Test without UNIQUE clause.");
		System.out.println("Since no nesting is allowed, skipping UNIQUE still gives error for trying to define another savepoint");
		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");
			System.out.println("FAIL 17b Should have gotten exception for nested savepoints");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		con.rollback();

		// Test 18
		System.out.println("Test 18 No nested SQL savepoints allowed inside JDBC savepoint.");
		savepoint1 = con.setSavepoint();
		System.out.println("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");
			System.out.println("FAIL 18 shouldn't be able set SQL savepoint nested inside JDBC savepoints");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		//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
		System.out.println("Test 19 No nested SQL savepoints allowed inside SQL savepoint.");
		s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
		System.out.println("Following SQL savepoint will fail because we are trying to nest it inside SQL savepoint");
		try {
			s.executeUpdate("SAVEPOINT s2 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
			System.out.println("FAIL 19 shouldn't be able set SQL savepoint nested inside SQL savepoint");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		//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");
		con.rollback();

		// Test 20
		System.out.println("Test 20 Rollback of SQL savepoint works same as rollback of JDBC savepoint.");
		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(2,1)");
		s.executeUpdate("INSERT INTO T1 VALUES(3,1)");
		//Rollback to SQL savepoint and should see changes rolledback
		s.executeUpdate("ROLLBACK TO SAVEPOINT s1");
		rs1 = s.executeQuery("select count(*) from t1");
		rs1.next();
		if(rs1.getInt(1) != 0) {
			System.out.println("ERROR: There should have been 0 rows in the table, but found " + rs1.getInt(1) + " rows");
			return;
		}
		con.rollback();

		// Test 21
		System.out.println("Test 21 After releasing the SQL savepoint, rollback the transaction and should see everything undone.");
		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();
		rs1 = s.executeQuery("select count(*) from t1");
		rs1.next();
		if(rs1.getInt(1) != 0) {
			System.out.println("ERROR: There should have been 0 rows in the table, but found " + rs1.getInt(1) + " rows");
			return;
		}
		con.rollback();

		// Test 22
		System.out.println("Test 22 Should not be able to create a SQL savepoint starting with name SYS");
		try {
			s.executeUpdate("SAVEPOINT SYSs2 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
			System.out.println("FAIL 22 shouldn't be able to create a SQL savepoint starting with name SYS");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		con.rollback();

		// Test 23 - bug 5817 - make savepoint and release non-reserved keywords
		System.out.println("Test 23 Should be able to use non-reserved keywords savepoint and release as identifiers");
		System.out.println("Create table with savepoint and release as identifiers");
		s.execute("create table savepoint (savepoint int, release int)");
		rs1 = s.executeQuery("select count(*) from savepoint");
		rs1.next();
		if(rs1.getInt(1) != 0) {
			System.out.println("ERROR: There should have been 0 rows in the table, but found " + rs1.getInt(1) + " rows");
			return;
		}
		System.out.println("Create a savepoint with name savepoint");
		s.execute("SAVEPOINT savepoint ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
		s.executeUpdate("INSERT INTO savepoint VALUES(1,1)");
		System.out.println("Release the savepoint with name savepoint");
		s.execute("RELEASE SAVEPOINT savepoint");
		rs1 = s.executeQuery("select count(*) from savepoint");
		rs1.next();
		if(rs1.getInt(1) != 1) {
			System.out.println("ERROR: There should have been 1 rows in the table, but found " + rs1.getInt(1) + " rows");
			return;
		}
		System.out.println("Create a savepoint with name release");
		s.execute("SAVEPOINT release ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
		s.executeUpdate("INSERT INTO savepoint VALUES(2,1)");
		System.out.println("Rollback to the savepoint with name release");
		s.execute("ROLLBACK TO SAVEPOINT release");
		rs1 = s.executeQuery("select count(*) from savepoint");
		rs1.next();
		if(rs1.getInt(1) != 1) {
			System.out.println("ERROR: There should have been 1 rows in the table, but found " + rs1.getInt(1) + " rows");
			return;
		}
		System.out.println("Release the savepoint with name release");
		s.execute("RELEASE SAVEPOINT release");
		con.rollback();

		// Test 24
		System.out.println("Test 24 Savepoint name can't exceed 128 characters");
		try {
			savepoint1 = con.setSavepoint("MyName1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890");
			System.out.println("FAIL 24 shouldn't be able to create a SQL savepoint with name exceeding 128 characters");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		con.rollback();

		// Test 25
		System.out.println("Test 25 Should not be able to create a SQL savepoint starting with name SYS through jdbc");
		try {
			savepoint1 = con.setSavepoint("SYSs2");
			System.out.println("FAIL 25 shouldn't be able to create a SQL savepoint starting with name SYS through jdbc");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		con.rollback();

		s1.close();
		s2.close();

		// 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" 
		System.out.println("Test 26a rollback of null savepoint");
		try {
			con.rollback((Savepoint) null);
			System.out.println("FAIL 26a rollback of null savepoint did not raise error ");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		// Test 26b pass Null value to releaseSavepoint
		System.out.println("Test 26b release  of null savepoint");
		try {
			con.releaseSavepoint((Savepoint) null);
			System.out.println("FAIL 26b release of null savepoint did not raise error ");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
	}

	//Set up the test by creating the table used by the rest of the test.
	static void setUpTest(Statement s)
					throws SQLException {
		/* drop and create tables */
	    try {
	        s.execute("drop table t1");
	    }catch (SQLException e){};
	    
	    try {
	        s.execute("drop table t2");
	    }catch (SQLException e){};
	    
		s.execute("create table t1 (c11 int, c12 smallint)");
		s.execute("create table t2 (c11 int)");

	}

	static private int count(Connection con, Statement s) throws SQLException {
		int count = 0;
		ResultSet rs = s.executeQuery("select count(*) from t1");
		rs.next();
		count = rs.getInt(1);
		rs.close();
		return count;
	}
  
	public static void doConnectionSetSavepointUnnamed() throws Throwable
	{
		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		Savepoint s1 = conn.setSavepoint();
		Statement s = conn.createStatement();
		s.executeUpdate("insert into t2 values(1)");
		conn.rollback(s1);
	}

	public static void doConnectionSetSavepointNamed() throws Throwable
	{
		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		Savepoint s1 = conn.setSavepoint("s1");
		Statement s = conn.createStatement();
		s.executeUpdate("insert into t2 values(1)");
		conn.rollback(s1);
	}

	public static void doConnectionRollbackSavepoint() throws Throwable
	{
		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		conn.rollback((Savepoint) null);
		Statement s = conn.createStatement();
		s.executeUpdate("insert into t2 values(1)");
	}

	public static void doConnectionReleaseSavepoint() throws Throwable
	{
		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		conn.releaseSavepoint((Savepoint) null);
		Statement s = conn.createStatement();
		s.executeUpdate("insert into t2 values(1)");
	}

	static private void dumpSQLExceptions (SQLException se) {
		System.out.println("FAIL -- unexpected exception");
		while (se != null) {
			System.out.print("SQLSTATE("+se.getSQLState()+"):");
			se.printStackTrace(System.out);
			se = se.getNextException();
		}
	}
}