scrollcursors2.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.scrollCursors2

   Copyright 1999, 2005 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.lang;

import java.io.IOException;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Types;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;

import org.apache.derby.tools.ij;
import org.apache.derby.tools.JDBCDisplayUtil;

/**
 * Test of scroll cursors.
 *
 * @author Jerry Brenner
 */

public class scrollCursors2 { 

	public static void main(String[] args) {
		boolean		passed = true;
		Connection	conn = null;
		Statement	s_i_r = null;

		/* Run all parts of this test, and catch any exceptions */
		try {
			System.out.println("Test scrollCurors2 starting");

			// use the ij utility to read the property file and
			// make the initial connection.
			ij.getPropertyArg(args);
			conn = ij.startJBMS();
			conn.setAutoCommit(false);

			/* Create the table and do any other set-up */
			passed = passed && setUpTest(conn);

			// Negative tests with forward only cursors.
			passed = passed && forwardOnlyNegative(conn);

			// Positive tests with forward only cursors.
			passed = passed && forwardOnlyPositive(conn);

			// Tests with scroll sensitive cursors
			passed = passed && scrollSensitiveTest(conn);

			// Positive tests for scroll insensitive cursors
			passed = passed && scrollInsensitivePositive(conn);

			// Negative tests for scroll insensitive cursors
			passed = passed && scrollInsensitiveNegative(conn);

			// "test" scrolling and CallableStatements
			passed = passed && testCallableStatements(conn);

			// tests for PreparedStatement.getMetaData()
			passed = passed && getMetaDataTests(conn);
			

		} 
		catch (SQLException se) 
		{
			passed = false;
			dumpSQLExceptions(se);
		} 
		catch (Throwable e) 
		{
			System.out.println("FAIL -- unexpected exception caught in main():\n");
			System.out.println(e.getMessage());
			e.printStackTrace();
			passed = false;
		} 
		finally 
		{

			/* Test is finished - clean up after ourselves */
			passed = passed && cleanUp(conn, s_i_r);
		}

		if (passed)
			System.out.println("PASS");

		System.out.println("Test scrollCursors2 finished");
	}

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

	/**
	 * Set up the test.
	 *
	 * This method creates the table used by the rest of the test.
	 *
	 * @param conn	The Connection
	 *
	 * @return	true if it succeeds, false if it doesn't
	 *
	 * @exception SQLException	Thrown if some unexpected error happens
	 */

	static boolean setUpTest(Connection conn)
					throws SQLException 
	{
		boolean	passed = true;
		int		rows;
		PreparedStatement	ps;
		Statement			s_i_r;

		s_i_r = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
									 ResultSet.CONCUR_READ_ONLY);

		/* Create a table */
		s_i_r.execute("create table t (i int, c50 char(50))");

	    /* Populate the table */
		s_i_r.execute("insert into t (i) values (2), (3), (4), (5), (6)");
		s_i_r.execute("update t set c50 = RTRIM(CAST (i AS CHAR(50)))");
		s_i_r.close();

		return passed;
	}

	/**
	 * Negative tests for forward only cursors.
	 *
	 * This method tests forward only cursors.
	 *
	 * @param conn	The Connection
	 *
	 * @return	true if it succeeds, false if it doesn't
	 *
	 * @exception SQLException	Thrown if some unexpected error happens
	 */

	static boolean forwardOnlyNegative( Connection conn)
		throws SQLException 
	{
		boolean		passed = true;
		PreparedStatement	ps_f_r = null;
		ResultSet	rs;
		SQLWarning	warning;
		Statement	s_f_r = null;

		s_f_r = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
									 ResultSet.CONCUR_READ_ONLY);
		// We should have gotten no warnings and a read only forward only cursor
		warning = conn.getWarnings();
		while (warning != null)
		{
			System.out.println("warning = " + warning);
			warning = warning.getNextWarning();
		}
		conn.clearWarnings();

		// Verify that setMaxRows(-1) fails
		try
		{
			s_f_r.setMaxRows(-1);
			// Should never get here
			System.out.println("setMaxRows(-1) expected to fail");
			passed = false;
		}
		catch (SQLException sqle)
		{
			/* Check to be sure the exception is the one we expect */
			passed = passed && checkException(sqle, "XJ063");
		}
		// Verify maxRows still 0
		if (s_f_r.getMaxRows() != 0)
		{
			System.out.println("getMaxRows() expected to return 0");
			passed = false;
		}

		// Verify that result set from statement is 
		// scroll insensitive and read only
		rs = s_f_r.executeQuery("select * from t");
		if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY)
		{
			System.out.println("cursor type = " + rs.getType() +
							   ", not " + ResultSet.TYPE_FORWARD_ONLY);
		}
		if (rs.getConcurrency() != ResultSet.CONCUR_READ_ONLY)
		{
			System.out.println("concurrency = " + rs.getConcurrency() +
							   ", not " + ResultSet.CONCUR_READ_ONLY);
		}

		// Verify that first(), etc. don't work
		try
		{
			rs.first();
			// Should never get here
			System.out.println("first() expected to fail");
			passed = false;
		}
		catch (SQLException sqle)
		{
			/* Check to be sure the exception is the one we expect */
			passed = passed && checkException(sqle, "XJ061");
		}
		try
		{
			rs.beforeFirst();
			// Should never get here
			System.out.println("beforeFirst() expected to fail");
			passed = false;
		}
		catch (SQLException sqle)
		{
			/* Check to be sure the exception is the one we expect */
			passed = passed && checkException(sqle, "XJ061");
		}
		try
		{
			rs.isBeforeFirst();
			// Should never get here
			System.out.println("isBeforeFirst() expected to fail");
			passed = false;
		}
		catch (SQLException sqle)
		{
			/* Check to be sure the exception is the one we expect */
			passed = passed && checkException(sqle, "XJ061");
		}
		try
		{
			rs.isAfterLast();
			// Should never get here
			System.out.println("isAfterLast() expected to fail");
			passed = false;
		}
		catch (SQLException sqle)
		{
			/* Check to be sure the exception is the one we expect */
			passed = passed && checkException(sqle, "XJ061");
		}
		try
		{
			rs.isFirst();
			// Should never get here
			System.out.println("isFirst() expected to fail");
			passed = false;
		}
		catch (SQLException sqle)
		{
			/* Check to be sure the exception is the one we expect */
			passed = passed && checkException(sqle, "XJ061");
		}
		try
		{
			rs.isLast();
			// Should never get here
			System.out.println("isLast() expected to fail");
			passed = false;
		}
		catch (SQLException sqle)
		{
			/* Check to be sure the exception is the one we expect */
			passed = passed && checkException(sqle, "XJ061");
		}
		try
		{
			rs.absolute(1);
			// Should never get here
			System.out.println("absolute() expected to fail");
			passed = false;
		}
		catch (SQLException sqle)
		{
			/* Check to be sure the exception is the one we expect */
			passed = passed && checkException(sqle, "XJ061");
		}
		try
		{
			rs.relative(1);
			// Should never get here
			System.out.println("relative() expected to fail");
			passed = false;
		}
		catch (SQLException sqle)
		{
			/* Check to be sure the exception is the one we expect */
			passed = passed && checkException(sqle, "XJ061");
		}

		// setFetchDirection should fail
		try
		{
			rs.setFetchDirection(ResultSet.FETCH_FORWARD);
			// Should never get here
			System.out.println("setFetchDirection() expected to fail");
			passed = false;
		}
		catch (SQLException sqle)
		{
			/* Check to be sure the exception is the one we expect */
			passed = passed && checkException(sqle, "XJ061");
		}

		/* Book says that getFetchDirection(), getFetchSize() and
		 * setFetchSize() are all okay.
		 */
		if (rs.getFetchSize() != 1)
	 	{
			System.out.println("getFetchSize() expected to return 1");
			passed = false;
		}
		rs.setFetchSize(5);
		if (rs.getFetchSize() != 5)
	 	{
			System.out.println("getFetchSize() expected to return 5");
			passed = false;
		}

		if (rs.getFetchDirection() != ResultSet.FETCH_FORWARD)
		{
			System.out.println(
				"getFetchDirection() expected to return FETCH_FORWARD, not " +
				rs.getFetchDirection());
			passed = false;
		}

		rs.close();
		s_f_r.close();

		ps_f_r = conn.prepareStatement(
									 "select * from t",
									 ResultSet.TYPE_FORWARD_ONLY,
									 ResultSet.CONCUR_READ_ONLY);
		// We should have gotten no warnings and a read only forward only cursor
		warning = conn.getWarnings();
		while (warning != null)
		{
			System.out.println("warning = " + warning);
			warning = warning.getNextWarning();
		}
		conn.clearWarnings();

		// Verify that result set from statement is 
		// scroll insensitive and read only
		rs = ps_f_r.executeQuery();
		if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY)
		{
			System.out.println("cursor type = " + rs.getType() +
							   ", not " + ResultSet.TYPE_FORWARD_ONLY);
		}
		if (rs.getConcurrency() != ResultSet.CONCUR_READ_ONLY)
		{
			System.out.println("concurrency = " + rs.getConcurrency() +
							   ", not " + ResultSet.CONCUR_READ_ONLY);
		}

		// Verify that first() doesn't work
		try
		{
			rs.first();
			// Should never get here
			System.out.println("first() expected to fail");
			passed = false;
		}
		catch (SQLException sqle)
		{
			/* Check to be sure the exception is the one we expect */
			passed = passed && checkException(sqle, "XJ061");
		}
		rs.close();
		ps_f_r.close();

		return passed;
	}

	/**
	 * Positive tests for forward only cursors.
	 *
	 * This method tests forward only cursors.
	 *
	 * @param conn	The Connection
	 *
	 * @return	true if it succeeds, false if it doesn't
	 *
	 * @exception SQLException	Thrown if some unexpected error happens
	 */

	static boolean forwardOnlyPositive( Connection conn)
		throws SQLException 
	{
		boolean		passed = true;
		ResultSet	rs;
		SQLWarning	warning;
		Statement	s_f_r = null;

		s_f_r = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
									 ResultSet.CONCUR_READ_ONLY);
		// We should have gotten no warnings and a read only forward only cursor
		warning = conn.getWarnings();
		while (warning != null)
		{
			System.out.println("warning = " + warning);
			warning = warning.getNextWarning();
		}
		conn.clearWarnings();

		// Verify that setMaxRows(4) succeeds
		s_f_r.setMaxRows(5);
		if (s_f_r.getMaxRows() != 5)
		{
			System.out.println("getMaxRows() expected to return 5");
			passed = false;
		}