savepointjdbc30.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.savepointJdbc30

   Copyright 2002, 2004 The Apache Software Foundation or its licensors, as applicable.

   Licensed 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.sql.Connection;
import java.sql.DriverManager;
import java.sql.Savepoint;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Properties;

import javax.sql.XADataSource;

import org.apache.derby.tools.ij;
import org.apache.derby.tools.JDBCDisplayUtil;
import org.apache.derbyTesting.functionTests.util.TestUtil;
/**
 * 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
 *
 * @author mamta
 */


public class savepointJdbc30 {

	static private boolean isDerbyNet = false;

	public static void main(String[] args) {
	    // Check savepoints for both regular connections and XA
	    
	    Connection con = null, con2 = null;
	    isDerbyNet = TestUtil.isNetFramework();
	    
	    try {
	    //	  use the ij utility to read the property file and
		// make the initial connection.
		ij.getPropertyArg(args);
		con = ij.startJBMS();
		con2 = ij.startJBMS();
		runTests("regular connections", con,con2);
		con.close();
		con2.close();
		if (TestUtil.isJCCFramework())  // no xa for jcc
		    return;
		
		// Test connections obtained via XADataSource DERBY-899
		Properties dsprops = new Properties();
		dsprops.setProperty("databaseName","wombat");
		XADataSource ds  = TestUtil.getXADataSource(dsprops);
		con = ds.getXAConnection().getConnection();
		con2 = ds.getXAConnection().getConnection();
		runTests("connections from XADataSource (local tranasaction)", 
				 con, con2);
		con.close();
		con2.close();
	    }
		catch (SQLException e) {
		    dumpSQLExceptions(e);
				}
		catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception:");
			e.printStackTrace(System.out);
		}

	}
	public static void runTests(String tag, Connection con, Connection con2) 
	throws SQLException {
		Statement  s;
		System.out.println("Test savepointJdbc30 starting for "  + tag);
		
			con.setAutoCommit(true); // make sure it is true
			s = con.createStatement();
			con2.setAutoCommit(false);
			/* Create the table and do any other set-up */
			setUpTest(s);

			//JCC translates the JDBC savepoint calls into equivalent SQL statements.
			//In addition, we do not allow nested savepoints when
			//coming through SQL statements. Because of this restriction, we can't run most of the
			//JDBC savepoint tests under DRDA framework. The JDBC tests have nested JDBC savepoint
			//calls and they fail when run under JCC(because they get translated into nested SQL savepoints).
			//Hence, splitting the test cases into non-DRDA and more generic tests.
			System.out.println("Tests common to DRDA and embedded Cloudscape");
			genericTests(con, con2, s);

			System.out.println("Next try non-DRDA tests");
			if (!isDerbyNet)
				nonDRDATests(con, s);

			s.close();
			
	
		System.out.println("Test savepointJdbc30 finished for " + tag);
	}

	//The following tests have nested savepoints through JDBC calls. When coming through JCC,
	//these nested JDBC savepoint calls are translated into equivalent SQL savepoint statements.
	//But we do not allow nested savepoints coming through SQL statments
	//and hence these tests can't be run under DRDA framework.
	static void nonDRDATests(Connection con, Statement s)
					throws SQLException {
		ResultSet rs1, rs2, rs1WithHold, rs2WithHold;
		Savepoint savepoint1, savepoint2, savepoint3, savepoint4;

		//Setting autocommit to false will allow savepoints
		con.setAutoCommit(false); // make sure it is false

		//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.
		System.out.println("Test40 - named savepoint can't conflict with internally generated name for unnamed savepoints");
		savepoint1 = con.setSavepoint();
		savepoint2 = con.setSavepoint("i.SAVEPT0");
		con.rollback();

		//Test41 - Rolling back to a savepoint will release all the savepoints created after that savepoint.
		System.out.println("Test41a - Rollback to a savepoint, then try to release savepoint created after that savepoint");

		savepoint1 = con.setSavepoint();
		s.executeUpdate("INSERT INTO T1 VALUES(1,1)");

		savepoint2 = con.setSavepoint("s1");
		s.executeUpdate("INSERT INTO T1 VALUES(2,1)");

		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);
		rs1 = s.executeQuery("select count(*) from t1");
		rs1.next();
		if(rs1.getInt(1) != 1) {
			System.out.println("ERROR: There should have been 1 row in the table, but found " + rs1.getInt(1) + " rows");
			return;
		}

		//Trying to release second named savepoint s2 should throw exception.
		try
		{
			con.releaseSavepoint(savepoint3);
			System.out.println("FAIL 41a release of rolled back savepoint");
		}
		catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}

		//Trying to rollback second named savepoint s2 should throw exception.
		System.out.println("Test41b - Rollback to a savepoint, then try to rollback savepoint created after that savepoint");
		try
		{
			con.rollback(savepoint3);
			System.out.println("FAIL 41b release of rolled back savepoint");
		}
		catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}

		//Release the unnamed named savepoint.
		con.rollback(savepoint1);
		rs1 = s.executeQuery("select count(*) from t1");
		rs1.next();
		if(rs1.getInt(1) != 0) {
			System.out.println("ERROR: There should have been no rows in the table, but found " + rs1.getInt(1) + " rows");
			return;
		}
		con.rollback();

		//Test42 - Rollback/commit on a connection will release all the savepoints created for that transaction
		System.out.println("Test42 - Rollback/commit the transaction, then try to use savepoint from that transaction");
		savepoint1 = con.setSavepoint();
		savepoint2 = con.setSavepoint("s1");
		con.rollback();
		try {
			con.rollback(savepoint1);
			System.out.println("FAIL 42 release of rolled back savepoint");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		//Testing commit next
		savepoint1 = con.setSavepoint();
		savepoint2 = con.setSavepoint("s1");
		con.commit();
		try {
			con.rollback(savepoint1);
			System.out.println("FAIL 42 rollback of rolled back savepoint");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}

		//Test43 - After releasing a savepoint, should be able to reuse it.
		System.out.println("Test43 - Release and reuse a savepoint name");
		savepoint1 = con.setSavepoint("s1");
		try {
			savepoint2 = con.setSavepoint("s1");
			System.out.println("FAIL 43");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		con.releaseSavepoint(savepoint1);
		savepoint2 = con.setSavepoint("s1");
		con.rollback();

		// Test 45 reuse savepoint name after rollback - should not work
		System.out.println("Test 45 reuse savepoint name after rollback - should not work");
		savepoint1 = con.setSavepoint("MyName");
		con.rollback(savepoint1);
		try {
			savepoint2 = con.setSavepoint("MyName");
			System.out.println("FAIL 45 reuse of savepoint name after rollback should fail");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		con.rollback();

		// Test 46 bug 5145 Cursors declared before and within the savepoint unit will be closed when rolling back the savepoint
		System.out.println("Test 46 Cursors declared before and within the savepoint unit will be closed when rolling back the savepoint");
		Statement sWithHold = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT );
		con.setAutoCommit(false);
		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)");
		rs1 = s.executeQuery("select * from t1");
		rs1.next();
		rs1WithHold = sWithHold.executeQuery("select * from t1");
		rs1WithHold.next();
		savepoint1 = con.setSavepoint();
		rs2 = s.executeQuery("select * from t1");
		rs2.next();
		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();
			System.out.println("FAIL 46 shouldn't be able to use a resultset (declared before the savepoint unit) after the rollback to savepoint");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		try {//holdable resultset declared outside the savepoint unit should be closed at this point after the rollback to savepoint
			rs1WithHold.next();
			System.out.println("FAIL 46 shouldn't be able to use a holdable resultset (declared before the savepoint unit) after the rollback to savepoint");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		try {//resultset declared within the savepoint unit should be closed at this point after the rollback to savepoint
			rs2.next();
			System.out.println("FAIL 46 shouldn't be able to use a resultset (declared within the savepoint unit) after the rollback to savepoint");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		try {//holdable resultset declared within the savepoint unit should be closed at this point after the rollback to savepoint
			rs2WithHold.next();
			System.out.println("FAIL 46 shouldn't be able to use a holdable resultset (declared within the savepoint unit) after the rollback to savepoint");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		con.rollback();

		// Test 47 multiple tests for getSavepointId()
		System.out.println("Test 47 multiple tests for getSavepointId()");
		savepoint1 = con.setSavepoint();
		savepoint2 = con.setSavepoint();
		System.out.println(savepoint1.getSavepointId());
		System.out.println(savepoint2.getSavepointId());
		con.releaseSavepoint(savepoint2);
		savepoint2 = con.setSavepoint();
		System.out.println(savepoint2.getSavepointId());
		con.commit();
		savepoint2 = con.setSavepoint();
		System.out.println(savepoint2.getSavepointId());
		con.rollback();
		savepoint2 = con.setSavepoint();
		System.out.println(savepoint2.getSavepointId());
		con.rollback();

		// Test 48
		System.out.println("Test 48 No nested SQL savepoints allowed.");
		savepoint1 = con.setSavepoint();
		savepoint2 = 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 48 shouldn't be able set SQL savepoint nested inside JDBC/SQL savepoints");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		//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");
			System.out.println("FAIL 48 Should have gotten exception for nested SQL savepoint");
		} catch (SQLException se) {
			System.out.println("Expected Exception is " + se.getMessage());
		}
		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();
	}