/*
 * Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.ProcedureTest
 *
 * 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.math.BigDecimal;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import junit.framework.Test;
import org.apache.derby.iapi.types.HarmonySerialBlob;
import org.apache.derby.iapi.types.HarmonySerialClob;
import org.apache.derbyTesting.functionTests.tests.lang.Price;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;

/**
 * Tests of stored procedures.
 */
public class ProcedureTest extends BaseJDBCTestCase {

    /**
     * Creates a new <code>ProcedureTest</code> instance.
     *
     * @param name name of the test
     */
    public ProcedureTest(String name) {
        super(name);
    }

    // TESTS

    /**
     * Tests that <code>Statement.executeQuery()</code> fails when no
     * result sets are returned.
     * @exception SQLException if a database error occurs
     */
    public void testExecuteQueryWithNoDynamicResultSets() throws SQLException {
        Statement stmt = createStatement();
        try {
            stmt.executeQuery("CALL RETRIEVE_DYNAMIC_RESULTS(0)");
            fail("executeQuery() didn't fail.");
        } catch (SQLException sqle) {
            assertNoResultSetFromExecuteQuery(sqle);
        }
    }

    /**
     * Tests that <code>Statement.executeQuery()</code> succeeds when
     * one result set is returned from a stored procedure.
     * @exception SQLException if a database error occurs
     */
    public void testExecuteQueryWithOneDynamicResultSet() throws SQLException {
        Statement stmt = createStatement();
        ResultSet rs = stmt.executeQuery("CALL RETRIEVE_DYNAMIC_RESULTS(1)");
        assertNotNull("executeQuery() returned null.", rs);
        assertSame(stmt, rs.getStatement());
        JDBC.assertDrainResultsHasData(rs);
    }

    /**
     * Tests that <code>Statement.executeQuery()</code> fails when
     * multiple result sets are returned.
     * @exception SQLException if a database error occurs
     */
    public void testExecuteQueryWithMoreThanOneDynamicResultSet()
        throws SQLException
    {
        Statement stmt = createStatement();
        try {
            stmt.executeQuery("CALL RETRIEVE_DYNAMIC_RESULTS(2)");
            fail("executeQuery() didn't fail.");
        } catch (SQLException sqle) {
            assertMultipleResultsFromExecuteQuery(sqle);
        }
    }

    /**
     * Tests that <code>Statement.executeUpdate()</code> succeeds when
     * no result sets are returned.
     *
     * @exception SQLException if a database error occurs
     */
    public void testExecuteUpdateWithNoDynamicResultSets()
        throws SQLException
    {
        Statement stmt = createStatement();
        assertUpdateCount(stmt, 0, "CALL RETRIEVE_DYNAMIC_RESULTS(0)");
        JDBC.assertNoMoreResults(stmt);
    }

    /**
     * Tests that <code>Statement.executeUpdate()</code> fails when a
     * result set is returned from a stored procedure.
     * @exception SQLException if a database error occurs
     */
    public void testExecuteUpdateWithOneDynamicResultSet() throws SQLException {
        Statement stmt = createStatement();
        try {
            stmt.executeUpdate("CALL RETRIEVE_DYNAMIC_RESULTS(1)");
            fail("executeUpdate() didn't fail.");
        } catch (SQLException sqle) {
            assertResultsFromExecuteUpdate(sqle);
        }
    }

    /**
     * Tests that <code>PreparedStatement.executeQuery()</code> fails
     * when no result sets are returned.
     * @exception SQLException if a database error occurs
     */
    public void testExecuteQueryWithNoDynamicResultSets_prepared()
        throws SQLException
    {
        PreparedStatement ps =
            prepareStatement("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
        ps.setInt(1, 0);
        try {
            ps.executeQuery();
            fail("executeQuery() didn't fail.");
        } catch (SQLException sqle) {
            assertNoResultSetFromExecuteQuery(sqle);
        }
    }

    /**
     * Tests that <code>PreparedStatement.executeQuery()</code>
     * succeeds when one result set is returned from a stored
     * procedure.
     * @exception SQLException if a database error occurs
     */
    public void testExecuteQueryWithOneDynamicResultSet_prepared()
        throws SQLException
    {
        PreparedStatement ps =
            prepareStatement("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
        ps.setInt(1, 1);
        ResultSet rs = ps.executeQuery();
        assertNotNull("executeQuery() returned null.", rs);
        assertSame(ps, rs.getStatement());
        JDBC.assertDrainResultsHasData(rs);

    }

    /**
     * Tests that <code>PreparedStatement.executeQuery()</code> fails
     * when multiple result sets are returned.
     * @exception SQLException if a database error occurs
     */
    public void testExecuteQueryWithMoreThanOneDynamicResultSet_prepared()
        throws SQLException
    {
        PreparedStatement ps =
            prepareStatement("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
        ps.setInt(1, 2);
        try {
            ps.executeQuery();
            fail("executeQuery() didn't fail.");
        } catch (SQLException sqle) {
            assertMultipleResultsFromExecuteQuery(sqle);
        }
    }

    /**
     * Tests that <code>PreparedStatement.executeUpdate()</code>
     * succeeds when no result sets are returned.
     *
     * @exception SQLException if a database error occurs
     */
    public void testExecuteUpdateWithNoDynamicResultSets_prepared()
        throws SQLException
    {
        PreparedStatement ps =
            prepareStatement("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
        ps.setInt(1, 0);
        assertUpdateCount(ps, 0);
        JDBC.assertNoMoreResults(ps);
    }

    /**
     * Tests that <code>PreparedStatement.executeUpdate()</code> fails
     * when a result set is returned from a stored procedure.
     *
     * @exception SQLException if a database error occurs
     */
    public void testExecuteUpdateWithOneDynamicResultSet_prepared()
        throws SQLException
    {
        PreparedStatement ps =
            prepareStatement("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
        ps.setInt(1, 1);
        try {
            ps.executeUpdate();
            fail("executeUpdate() didn't fail.");
        } catch (SQLException sqle) {
            assertResultsFromExecuteUpdate(sqle);
        }
    }

    /**
     * Tests that <code>CallableStatement.executeQuery()</code> fails
     * when no result sets are returned.
     * @exception SQLException if a database error occurs
     */
    public void testExecuteQueryWithNoDynamicResultSets_callable()
        throws SQLException
    {
        CallableStatement cs =
            prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
        cs.setInt(1, 0);
        try {
            cs.executeQuery();
            fail("executeQuery() didn't fail.");
        } catch (SQLException sqle) {
            assertNoResultSetFromExecuteQuery(sqle);
        }
    }

    /**
     * Tests that <code>CallableStatement.executeQuery()</code>
     * succeeds when one result set is returned from a stored
     * procedure.
     * @exception SQLException if a database error occurs
     */
    public void testExecuteQueryWithOneDynamicResultSet_callable()
        throws SQLException
    {
        CallableStatement cs =
            prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
        cs.setInt(1, 1);
        ResultSet rs = cs.executeQuery();
        assertNotNull("executeQuery() returned null.", rs);
        assertSame(cs, rs.getStatement());
        JDBC.assertDrainResultsHasData(rs);
    }

    /**
     * Tests that <code>CallableStatement.executeQuery()</code> fails
     * when multiple result sets are returned.
     * @exception SQLException if a database error occurs
     */
    public void testExecuteQueryWithMoreThanOneDynamicResultSet_callable()
        throws SQLException
    {
        CallableStatement cs =
            prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
        cs.setInt(1, 2);
        try {
            cs.executeQuery();
            fail("executeQuery() didn't fail.");
        } catch (SQLException sqle) {
            assertMultipleResultsFromExecuteQuery(sqle);
        }
    }

    /**
     * Tests that <code>CallableStatement.executeUpdate()</code>
     * succeeds when no result sets are returned.
     *
     * @exception SQLException if a database error occurs
     */
    public void testExecuteUpdateWithNoDynamicResultSets_callable()
        throws SQLException
    {
        CallableStatement cs =
            prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
        cs.setInt(1, 0);
        assertUpdateCount(cs, 0);
        JDBC.assertNoMoreResults(cs);
    }

    /**
     * Tests that <code>CallableStatement.executeUpdate()</code> fails
     * when a result set is returned from a stored procedure.
     * @exception SQLException if a database error occurs
     */
    public void testExecuteUpdateWithOneDynamicResultSet_callable()
        throws SQLException
    {
        CallableStatement cs =
            prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
        cs.setInt(1, 1);
        try {
            cs.executeUpdate();
            fail("executeUpdate() didn't fail.");
        } catch (SQLException sqle) {
            assertResultsFromExecuteUpdate(sqle);
        }
    }

    /**
     * Tests that the effects of executing a stored procedure with
     * <code>executeQuery()</code> are correctly rolled back when
     * <code>Connection.rollback()</code> is called.
     * @exception SQLException if a database error occurs
     */
    public void testRollbackStoredProcWithExecuteQuery() throws SQLException {

        Statement stmt = createStatement();
        ResultSet rs = stmt.executeQuery("CALL PROC_WITH_SIDE_EFFECTS(1)");
        rs.close();
        rollback();
        
        // Expect Side effects from stored procedure to be rolled back.
        JDBC.assertEmpty(stmt.executeQuery("SELECT * FROM SIMPLE_TABLE"));
 
    }

    /**
     * Tests that the effects of executing a stored procedure with
     * <code>executeUpdate()</code> are correctly rolled back when
     * <code>Connection.rollback()</code> is called.
     * @exception SQLException if a database error occurs
     */
    public void testRollbackStoredProcWithExecuteUpdate() throws SQLException {
        Statement stmt = createStatement();
        stmt.executeUpdate("CALL PROC_WITH_SIDE_EFFECTS(0)");
        rollback();
        
        // Expect Side effects from stored procedure to be rolled back.
        JDBC.assertEmpty(stmt.executeQuery("SELECT * FROM SIMPLE_TABLE"));
 
    }

    /**
     * Tests that the effects of executing a stored procedure with
     * <code>executeQuery()</code> are correctly rolled back when the
     * query fails because the number of returned result sets is zero.
     *
     * @exception SQLException if a database error occurs
     */
    public void testRollbackStoredProcWhenExecuteQueryReturnsNothing()
        throws SQLException
    {
        Connection conn = getConnection();
        conn.setAutoCommit(true);
        Statement stmt = createStatement();
        try {
            stmt.executeQuery("CALL PROC_WITH_SIDE_EFFECTS(0)");
            fail("executeQuery() didn't fail.");
        } catch (SQLException sqle) {
            assertNoResultSetFromExecuteQuery(sqle);
        }

        // Expect Side effects from stored procedure to be rolled back.
        JDBC.assertEmpty(stmt.executeQuery("SELECT * FROM SIMPLE_TABLE"));
    }

    /**
     * Tests that the effects of executing a stored procedure with
     * <code>executeQuery()</code> are correctly rolled back when the
     * query fails because the number of returned result sets is more
     * than one.
     *
     * @exception SQLException if a database error occurs
     */
    public void testRollbackStoredProcWhenExecuteQueryReturnsTooMuch()
        throws SQLException
    {
        Connection conn = getConnection();
        conn.setAutoCommit(true);
        Statement stmt = createStatement();
        try {
            stmt.executeQuery("CALL PROC_WITH_SIDE_EFFECTS(2)");
            fail("executeQuery() didn't fail.");
        } catch (SQLException sqle) {
            assertMultipleResultsFromExecuteQuery(sqle);
        }
        // Expect Side effects from stored procedure to be rolled back.
        JDBC.assertEmpty(stmt.executeQuery("SELECT * FROM SIMPLE_TABLE"));
 
    }

    /**
     * Tests that the effects of executing a stored procedure with
     * <code>executeUpdate()</code> are correctly rolled back when the
     * query fails because the stored procedure returned a result set.
     *
     * @exception SQLException if a database error occurs
     */
    public void testRollbackStoredProcWhenExecuteUpdateReturnsResults()
        throws SQLException
    {
        Connection conn = getConnection();
        conn.setAutoCommit(true);
        Statement stmt = createStatement();
        try {
            stmt.executeUpdate("CALL PROC_WITH_SIDE_EFFECTS(1)");
            fail("executeUpdate() didn't fail.");
        } catch (SQLException sqle) {
            assertResultsFromExecuteUpdate(sqle);
        }
        // Expect Side effects from stored procedure to be rolled back.
        JDBC.assertEmpty(stmt.executeQuery("SELECT * FROM SIMPLE_TABLE"));
 
    }

    /**
     * Tests that the effects of executing a stored procedure with
     * <code>executeQuery()</code> are correctly rolled back when the
     * query fails because the number of returned result sets is zero.
     *
     * @exception SQLException if a database error occurs
     */
    public void testRollbackStoredProcWhenExecuteQueryReturnsNothing_prepared()
        throws SQLException
    {
        Connection conn = getConnection();
        conn.setAutoCommit(true);
        PreparedStatement ps =
            prepareStatement("CALL PROC_WITH_SIDE_EFFECTS(?)");
        ps.setInt(1, 0);
        try {
            ps.executeQuery();
            fail("executeQuery() didn't fail.");
        } catch (SQLException sqle) {
            assertNoResultSetFromExecuteQuery(sqle);
        }
        Statement stmt = createStatement();
        // Expect Side effects from stored procedure to be rolled back.
        JDBC.assertEmpty(stmt.executeQuery("SELECT * FROM SIMPLE_TABLE"));
 
    }

    /**
     * Tests that the effects of executing a stored procedure with
     * <code>executeQuery()</code> are correctly rolled back when the
     * query fails because the number of returned result sets is more
     * than one.
     *
     * @exception SQLException if a database error occurs
     */
    public void testRollbackStoredProcWhenExecuteQueryReturnsTooMuch_prepared()
        throws SQLException
    {
        Connection conn = getConnection();
        conn.setAutoCommit(true);
        PreparedStatement ps =
            prepareStatement("CALL PROC_WITH_SIDE_EFFECTS(?)");
        ps.setInt(1, 2);
        try {
            ps.executeQuery();
            fail("executeQuery() didn't fail.");
        } catch (SQLException sqle) {
            assertMultipleResultsFromExecuteQuery(sqle);
        }
        Statement stmt = createStatement();
        // Expect Side effects from stored procedure to be rolled back.
        JDBC.assertEmpty(stmt.executeQuery("SELECT * FROM SIMPLE_TABLE"));
     }

    /**
     * Tests that the effects of executing a stored procedure with
     * <code>executeUpdate()</code> are correctly rolled back when the
     * query fails because the stored procedure returned a result set.
     *
     * @exception SQLException if a database error occurs
     */
    public void
        testRollbackStoredProcWhenExecuteUpdateReturnsResults_prepared()
        throws SQLException
    {
        Connection conn = getConnection();
        conn.setAutoCommit(true);
        PreparedStatement ps =
            prepareStatement("CALL PROC_WITH_SIDE_EFFECTS(?)");
        ps.setInt(1, 1);
        try {
            ps.executeUpdate();
            fail("executeUpdate() didn't fail.");
        } catch (SQLException sqle) {
            assertResultsFromExecuteUpdate(sqle);
        }
        Statement stmt = createStatement();
        // Expect Side effects from stored procedure to be rolled back.
        JDBC.assertEmpty(stmt.executeQuery("SELECT * FROM SIMPLE_TABLE"));
 
    }

    /**
     * Tests that closed result sets are not returned when calling
     * <code>executeQuery()</code>.
     * @exception SQLException if a database error occurs
     */
    public void testClosedDynamicResultSetsFromExecuteQuery()
        throws SQLException
    {
        Statement stmt = createStatement();
        try {
            ResultSet rs = stmt.executeQuery("CALL RETRIEVE_CLOSED_RESULT()");
            fail("executeQuery() didn't fail.");
        } catch (SQLException sqle) {
            assertNoResultSetFromExecuteQuery(sqle);
        }
    }

    /**
     * Tests that closed result sets are ignored when calling
     * <code>executeUpdate()</code>.
     * @exception SQLException if a database error occurs
     */
    public void testClosedDynamicResultSetsFromExecuteUpdate()
        throws SQLException
    {
        Statement stmt = createStatement();
        stmt.executeUpdate("CALL RETRIEVE_CLOSED_RESULT()");
        JDBC.assertNoMoreResults(stmt);
    }

    /**
     * Tests that dynamic result sets from other connections are
     * ignored when calling <code>executeQuery</code>.
     * @exception SQLException if a database error occurs
     */
    public void testDynamicResultSetsFromOtherConnectionWithExecuteQuery()
        throws SQLException
    {
        PreparedStatement ps =
            prepareStatement("CALL RETRIEVE_EXTERNAL_RESULT(?,?,?)");
        
        ps.setString(1, getTestConfiguration().getDefaultDatabaseName());
        ps.setString(2, getTestConfiguration().getUserName());
        ps.setString(3, getTestConfiguration().getUserPassword());
        try {
            ps.executeQuery();
            fail("executeQuery() didn't fail.");
        } catch (SQLException sqle) {
            assertNoResultSetFromExecuteQuery(sqle);
        }
    }

    /**
     * Tests that dynamic result sets from other connections are
     * ignored when calling <code>executeUpdate</code>.
     * @exception SQLException if a database error occurs
     */
    public void testDynamicResultSetsFromOtherConnectionWithExecuteUpdate()
        throws SQLException
    {
        PreparedStatement ps =
            prepareStatement("CALL RETRIEVE_EXTERNAL_RESULT(?,?,?)");
        
        ps.setString(1, getTestConfiguration().getDefaultDatabaseName());
        ps.setString(2, getTestConfiguration().getUserName());
        ps.setString(3, getTestConfiguration().getUserPassword());
        
        ps.executeUpdate();
        
        JDBC.assertNoMoreResults(ps);
    }

    /**
     * Test that a call to getBlob() to retrieve the value of a non-BLOB
     * parameter fails with the expected SQLException. Used to throw
     * ClassCastException, see DERBY-4970.
     */
    public void testGetBlobFromIntParameter() throws SQLException {
        CallableStatement cs = prepareCall("call int_out(?)");
        cs.registerOutParameter(1, Types.INTEGER);
        cs.execute();
        try {
            cs.getBlob(1);
            fail("getBlob() on int parameter expected to fail");
        } catch (SQLException sqle) {
            assertSQLState("22005", sqle);
        }
    }

    /**
     * Test that a call to getClob() to retrieve the value of a non-CLOB
     * parameter fails with the expected SQLException. Used to throw
     * ClassCastException, see DERBY-4970.
     */
    public void testGetClobFromIntParameter() throws SQLException {
        CallableStatement cs = prepareCall("call int_out(?)");
        cs.registerOutParameter(1, Types.INTEGER);
        cs.execute();
        try {
            cs.getClob(1);
            fail("getClob() on int parameter expected to fail");
        } catch (SQLException sqle) {
            assertSQLState("22005", sqle);
        }
    }

    /**
     * Test that a statement severity error inside a procedure doesn't kill
     * the top-level statement that executes the stored procedure. Regression
     * test case for DERBY-5280.
     */
    public void testStatementSeverityErrorInProcedure() throws SQLException {
        Statement s = createStatement();
        s.execute("create procedure proc_5280() language java " +
                  "parameter style java external name '" +
                  getClass().getName() + ".proc_5280' reads sql data");
        s.execute("call proc_5280()");
    }

    /**
     * Procedure that drops a non-existent table and ignores the exception
     * thrown because of it. Used by the regression test case for DERBY-5280.
     */
    public static void proc_5280() throws SQLException {
        Connection c = DriverManager.getConnection("jdbc:default:connection");
        Statement s = c.createStatement();

        // Drop a non-existent table and verify that it fails with the
        // expected exception. Ignore the exception.
        try {
            s.execute("drop table this_table_does_not_exist");
            fail("dropping non-existent table should fail");
        } catch (SQLException sqle) {
            assertSQLState("42Y55", sqle);
        }

        // The statement should still work.
        JDBC.assertSingleValueResultSet(s.executeQuery("values 1"), "1");
    }

    /**
     * Test that INOUT args are preserved over procedure invocations.
     * See DERBY-2515.
     */
    public  void    test_2515()   throws Exception
    {
        Connection  conn = getConnection();
        
        PreparedStatement ps = conn.prepareStatement
            (
             "create type price_2515 external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n"
             );
        ps.execute();
        ps.close();
        
        ps = conn.prepareStatement
            (
             "create procedure proc_2515\n" +
             "(\n" +
             "\tin passNumber int,\n" +
             "\tout returnMessage varchar( 32672 ),\n" +
             "\tinout bigintArg bigint,\n" +
             "\tinout blobArg blob,\n" +
             "inout booleanArg boolean,\n" +
             "inout charArg char( 6 ),\n" +
             "inout charForBitDataArg char( 3 ) for bit data,\n" +
             "inout clobArg clob,\n" +
             "inout dateArg date,\n" +
             "inout decimalArg decimal,\n" +
             "inout doubleArg double,\n" +
             "inout intArg int,\n" +
             "inout longVarcharArg long varchar,\n" +
             "inout longVarcharForBitDataArg long varchar for bit data,\n" +
             "inout realArg real,\n" +
             "inout smallintArg smallint,\n" +
             "inout timeArg time,\n" +
             "inout timestampArg timestamp,\n" +
             "inout priceArg price_2515,\n" +
             "inout varcharArg varchar( 20 ),\n" +
             "inout varcharForBitDataArg varchar( 3 ) for bit data\n" +
            ")\n" +
            "parameter style java language java no sql\n" +
            "external name '" + ProcedureTest.class.getName() + ".proc_2515'"
             );
        ps.execute();
        ps.close();

        CallableStatement   cs = conn.prepareCall
            ( "call proc_2515( ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ? )" );
        AllTypesTuple   firstArgs = makeFirstAllTypesTuple();

        int     idx = 2;
        
        cs.registerOutParameter( idx++, Types.VARCHAR );

        cs.registerOutParameter( idx, Types.BIGINT );
        cs.setLong( idx++, firstArgs.get_bigintArg().longValue() );

        cs.registerOutParameter( idx, Types.BLOB );
        cs.setBlob( idx++, firstArgs.get_blobArg() );

        cs.registerOutParameter( idx, Types.BOOLEAN );
        cs.setBoolean( idx++, firstArgs.get_booleanArg().booleanValue() );

        cs.registerOutParameter( idx, Types.CHAR );
        cs.setString( idx++, firstArgs.get_charArg() );

        cs.registerOutParameter( idx, Types.BINARY );
        cs.setBytes( idx++, firstArgs.get_charForBitDataArg() );

        cs.registerOutParameter( idx, Types.CLOB );
        cs.setClob( idx++, firstArgs.get_clobArg() );

        cs.registerOutParameter( idx, Types.DATE );
        cs.setDate( idx++, firstArgs.get_dateArg() );

        cs.registerOutParameter( idx, Types.DECIMAL );
        cs.setBigDecimal( idx++, firstArgs.get_decimalArg() );

        cs.registerOutParameter( idx, Types.DOUBLE );
        cs.setDouble( idx++, firstArgs.get_doubleArg().doubleValue() );

        cs.registerOutParameter( idx, Types.INTEGER );
        cs.setInt( idx++, firstArgs.get_intArg().intValue() );

        cs.registerOutParameter( idx, Types.LONGVARCHAR );
        cs.setString( idx++, firstArgs.get_longVarcharArg() );

        cs.registerOutParameter( idx, Types.LONGVARBINARY );
        cs.setBytes( idx++, firstArgs.get_longVarcharForBitDataArg() );

        cs.registerOutParameter( idx, Types.REAL );
        cs.setFloat( idx++, firstArgs.get_realArg().floatValue() );

        cs.registerOutParameter( idx, Types.SMALLINT );
        cs.setShort( idx++, firstArgs.get_smallintArg().shortValue() );

        cs.registerOutParameter( idx, Types.TIME );
        cs.setTime( idx++, firstArgs.get_timeArg() );

        cs.registerOutParameter( idx, Types.TIMESTAMP );
        cs.setTimestamp( idx++, firstArgs.get_timestampArg() );

        cs.registerOutParameter( idx, Types.JAVA_OBJECT );
        cs.setObject( idx++, firstArgs.get_priceArg() );

        cs.registerOutParameter( idx, Types.VARCHAR );
        cs.setString( idx++, firstArgs.get_varcharArg() );

        cs.registerOutParameter( idx, Types.VARBINARY );
        cs.setBytes( idx++, firstArgs.get_varcharForBitDataArg() );

        cs.setInt( 1, 0 );
        cs.execute();
        assertEquals( "", cs.getString( 2 ) );  // the return message should be empty, meaning the call args were what the procedure expected
        assertEquals( "", makeSecondAllTypesTuple().compare( getActualReturnArgs( cs ) ) );

        cs.setInt( 1, 1 );
        cs.execute();
        assertEquals( "", cs.getString( 2 ) );  // the return message should be empty, meaning the call args were what the procedure expected
        assertEquals( "", makeThirdAllTypesTuple().compare( getActualReturnArgs( cs ) ) );

        cs.setInt( 1, 2 );
        cs.execute();
        assertEquals( "", cs.getString( 2 ) );  // the return message should be empty, meaning the call args were what the procedure expected
        assertEquals( "", makeFourthAllTypesTuple().compare( getActualReturnArgs( cs ) ) );

        ps = conn.prepareStatement( "drop procedure proc_2515" );
        ps.execute();
        ps.close();

        ps = conn.prepareStatement( "drop type price_2515 restrict" );
        ps.execute();
        ps.close();
    }
    private AllTypesTuple   getActualReturnArgs( CallableStatement cs )
        throws Exception
    {
        int idx = 3;
        
        return new AllTypesTuple
            (
             (Long) cs.getObject( idx++ ),
             (Blob) cs.getObject( idx++ ),
             (Boolean) cs.getObject( idx++ ),
             (String) cs.getObject( idx++ ),
             (byte[]) cs.getObject( idx++ ),
             (Clob) cs.getObject( idx++ ),
             (Date) cs.getObject( idx++ ),
             (BigDecimal) cs.getObject( idx++ ),
             (Double) cs.getObject( idx++ ),
             (Integer) cs.getObject( idx++ ),
             (String) cs.getObject( idx++ ),
             (byte[]) cs.getObject( idx++ ),
             (Float) cs.getObject( idx++ ),
             (Integer) cs.getObject( idx++ ),
             (Time) cs.getObject( idx++ ),
             (Timestamp) cs.getObject( idx++ ),
             (Price) cs.getObject( idx++ ),
             (String) cs.getObject( idx++ ),
             (byte[]) cs.getObject( idx++ )
             );
    }

    /**
     * Regression test case for DERBY-2516. If an INOUT parameter had been
     * registered as an output parameter, but no input value had been assigned
     * to it, the client driver would go ahead and execute the statement
     * using null as input.
     */
    public void testInOutParamNotSet() throws SQLException {
        setAutoCommit(false);

        Statement s = createStatement();
        s.execute("create procedure proc_2516 (inout i int) " +
                  "language java parameter style java external name '" +
                  getClass().getName() + ".proc_2516' no sql");

        // Register an INOUT parameter, but don't set it. Expect failure.
        // Client used to execute without error.
        CallableStatement cs = prepareCall("call proc_2516(?)");
        cs.registerOutParameter(1, Types.INTEGER);
        assertStatementError("07000", cs);

        // Should work if the parameter has been set.
        cs.setInt(1, 0);
        cs.execute();
        assertEquals(10, cs.getInt(1));

        // After clearing the parameters, execution should fail. Client used
        // to succeed.
        cs.clearParameters();
        assertStatementError("07000", cs);

        // Setting the parameter again should make it work.
        cs.setInt(1, 1);
        cs.execute();
        assertEquals(10, cs.getInt(1));
    }

    /**
     * Stored procedure used by the regression test case for DERBY-2516.
     *
     * @param i INOUT parameter that gets set to 10 by the procedure
     */
    public static void proc_2516(Integer[] i) {
        i[0] = 10;
    }

    /**
     * Test that we create and execute stored procedures with as many
     * parameters as the Java specification allows.
     */
    public void testMaxNumberOfParameters() throws SQLException {
        // Test with the maximum number of parameters allowed by the
        // Java Virtual Machine specification. That is, 255 parameters.
        testMaxNumberOfParameters(255, true);

        // Test with one more parameter than allowed. Since we have no way
        // to declare a method with that many parameters, expect execution to
        // fail gracefully. The DDL will work, however.
        testMaxNumberOfParameters(256, false);

        // Test with a very high number of parameters. Again, expect DDL to
        // succeed and execution to fail gracefully.
        testMaxNumberOfParameters(10000, false);
    }

    /**
     * Create and execute a stored procedure backed by a Java method with the
     * specified number of parameters.
     *
     * @param params the number of parameters
     * @param methodExists whether or not a method called
     * {@code procWithManyParams} with the specified number of parameters
     * exists
     */
    private void testMaxNumberOfParameters(int params, boolean methodExists)
            throws SQLException {
        final String javaMethod = getClass().getName() + ".procWithManyParams";
        final String sqlProc = "PROC_WITH_LOTS_OF_PARAMETERS";

        // Disable auto-commit for easy cleanup with rollback().
        setAutoCommit(false);

        // Create a procedure with many parameters.

        StringBuffer sb = new StringBuffer("create procedure ");
        sb.append(sqlProc).append('(');
        for (int i = 0; i < params; i++) {
            if (i > 0) {
                sb.append(',');
            }
            sb.append('p').append(i).append(" int");
        }
        sb.append(") language java parameter style java external name '");
        sb.append(javaMethod).append("' no sql");

        Statement s = createStatement();
        s.execute(sb.toString());

        // Check that the database meta-data has correct information.
        DatabaseMetaData dmd = getConnection().getMetaData();

        JDBC.assertFullResultSet(
            dmd.getProcedures(
                null, null, sqlProc),
            new Object[][] {{
                "", "APP", sqlProc, null, null, null,
                javaMethod,
                Integer.valueOf(DatabaseMetaData.procedureNoResult),
                new JDBC.GeneratedId()
            }},
            false);

        JDBC.assertDrainResults(
                dmd.getProcedureColumns(null, null, sqlProc, "%"),
                params);

        // Execute the procedure.
        sb.setLength(0);
        sb.append("call ").append(sqlProc).append('(');
        for (int i = 0; i < params; i++) {
            if (i > 0) {
                sb.append(',');
            }
            sb.append(i);
        }
        sb.append(')');

        if (methodExists) {
            s.execute(sb.toString());
        } else {
            assertCallError("42X50", sb.toString());
        }

        rollback();
    }

    public static void procWithManyParams(
        int p001, int p002, int p003, int p004, int p005, int p006, int p007,
        int p008, int p009, int p010, int p011, int p012, int p013, int p014,
        int p015, int p016, int p017, int p018, int p019, int p020, int p021,
        int p022, int p023, int p024, int p025, int p026, int p027, int p028,
        int p029, int p030, int p031, int p032, int p033, int p034, int p035,
        int p036, int p037, int p038, int p039, int p040, int p041, int p042,
        int p043, int p044, int p045, int p046, int p047, int p048, int p049,
        int p050, int p051, int p052, int p053, int p054, int p055, int p056,
        int p057, int p058, int p059, int p060, int p061, int p062, int p063,
        int p064, int p065, int p066, int p067, int p068, int p069, int p070,
        int p071, int p072, int p073, int p074, int p075, int p076, int p077,
        int p078, int p079, int p080, int p081, int p082, int p083, int p084,
        int p085, int p086, int p087, int p088, int p089, int p090, int p091,
        int p092, int p093, int p094, int p095, int p096, int p097, int p098,
        int p099, int p100, int p101, int p102, int p103, int p104, int p105,
        int p106, int p107, int p108, int p109, int p110, int p111, int p112,
        int p113, int p114, int p115, int p116, int p117, int p118, int p119,
        int p120, int p121, int p122, int p123, int p124, int p125, int p126,
        int p127, int p128, int p129, int p130, int p131, int p132, int p133,
        int p134, int p135, int p136, int p137, int p138, int p139, int p140,
        int p141, int p142, int p143, int p144, int p145, int p146, int p147,
        int p148, int p149, int p150, int p151, int p152, int p153, int p154,
        int p155, int p156, int p157, int p158, int p159, int p160, int p161,
        int p162, int p163, int p164, int p165, int p166, int p167, int p168,
        int p169, int p170, int p171, int p172, int p173, int p174, int p175,
        int p176, int p177, int p178, int p179, int p180, int p181, int p182,
        int p183, int p184, int p185, int p186, int p187, int p188, int p189,
        int p190, int p191, int p192, int p193, int p194, int p195, int p196,
        int p197, int p198, int p199, int p200, int p201, int p202, int p203,
        int p204, int p205, int p206, int p207, int p208, int p209, int p210,
        int p211, int p212, int p213, int p214, int p215, int p216, int p217,
        int p218, int p219, int p220, int p221, int p222, int p223, int p224,
        int p225, int p226, int p227, int p228, int p229, int p230, int p231,
        int p232, int p233, int p234, int p235, int p236, int p237, int p238,
        int p239, int p240, int p241, int p242, int p243, int p244, int p245,
        int p246, int p247, int p248, int p249, int p250, int p251, int p252,
        int p253, int p254, int p255)
    {
    }

    // UTILITY METHODS

    /**
     * Raises an exception if the exception is not caused by
     * <code>executeQuery()</code> returning no result set.
     *
     * @param sqle a <code>SQLException</code> value
     */
    private void assertNoResultSetFromExecuteQuery(SQLException sqle) {
        assertSQLState("Unexpected SQL state.", "X0Y78", sqle);        
    }

    /**
     * Raises an exception if the exception is not caused by
     * <code>executeQuery()</code> returning multiple result sets.
     *
     * @param sqle a <code>SQLException</code> value
     */
    private void assertMultipleResultsFromExecuteQuery(SQLException sqle)
    {
        assertSQLState("Unexpected SQL state.", "X0Y78", sqle);        
    }

    /**
     * Raises an exception if the exception is not caused by
     * <code>executeUpdate()</code> returning result sets.
     *
     * @param sqle a <code>SQLException</code> value
     */
    private void assertResultsFromExecuteUpdate(SQLException sqle) {
        assertSQLState("Unexpected SQL state.", "X0Y79", sqle);
    }

    // SETUP

    /**
     * Runs the test fixtures in embedded and client.
     * @return test suite
     */
    public static Test suite() {
        BaseTestSuite suite = new BaseTestSuite("ProcedureTest");

        suite.addTest(baseSuite("ProcedureTest:embedded"));

        suite.addTest(
                TestConfiguration.clientServerDecorator(
                        baseSuite("ProcedureTest:client")));    
        return suite;
    }

    /**
     * Creates the test suite and wraps it in a <code>TestSetup</code>
     * instance which sets up and tears down the test environment.
     * @return test suite
     */
    private static Test baseSuite(String name)
    {
        BaseTestSuite suite = new BaseTestSuite(name);
        
        // Need JDBC DriverManager to run these tests
        if (!JDBC.vmSupportsJDBC3())
            return suite;
        
        suite.addTestSuite(ProcedureTest.class);
        
        return new CleanDatabaseTestSetup(suite) {
            /**
             * Creates the tables and the stored procedures used in the test
             * cases.
             * @exception SQLException if a database error occurs
             */
            protected void decorateSQL(Statement s) throws SQLException
            {
                for (int i = 0; i < PROCEDURES.length; i++) {
                    s.execute(PROCEDURES[i]);
                }
                for (int i = 0; i < TABLES.length; i++) {
                    s.execute(TABLES[i][1]);
                }
            }
        };
    }

    /**
     * Sets up the connection for a test case and clears all tables
     * used in the test cases.
     * @exception SQLException if a database error occurs
     */
    public void setUp() throws SQLException {
        Connection conn = getConnection();
        conn.setAutoCommit(false);
        Statement s = createStatement();
        for (int i = 0; i < TABLES.length; i++) {
            s.execute("DELETE FROM " + TABLES[i][0]);
        }
        commit();
    }

    /**
     * Procedures that should be created before the tests are run and
     * dropped when the tests have finished. First element in each row
     * is the name of the procedure, second element is SQL which
     * creates it.
     */
    private static final String[] PROCEDURES = {
       
          "CREATE PROCEDURE RETRIEVE_DYNAMIC_RESULTS(number INT) " +
          "LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '" +
          ProcedureTest.class.getName() + ".retrieveDynamicResults' " +
          "DYNAMIC RESULT SETS 4",


          "CREATE PROCEDURE RETRIEVE_CLOSED_RESULT() LANGUAGE JAVA " +
          "PARAMETER STYLE JAVA EXTERNAL NAME '" +
          ProcedureTest.class.getName() + ".retrieveClosedResult' " +
          "DYNAMIC RESULT SETS 1",

          "CREATE PROCEDURE RETRIEVE_EXTERNAL_RESULT(" +
          "DBNAME VARCHAR(128), DBUSER VARCHAR(128), DBPWD VARCHAR(128)) LANGUAGE JAVA " +
          "PARAMETER STYLE JAVA EXTERNAL NAME '" +
          ProcedureTest.class.getName() + ".retrieveExternalResult' " +
          "DYNAMIC RESULT SETS 1",

          "CREATE PROCEDURE PROC_WITH_SIDE_EFFECTS(ret INT) LANGUAGE JAVA " +
          "PARAMETER STYLE JAVA EXTERNAL NAME '" +
          ProcedureTest.class.getName() + ".procWithSideEffects' " +
          "DYNAMIC RESULT SETS 2",
          
          "CREATE PROCEDURE NESTED_RESULT_SETS(proctext VARCHAR(128)) LANGUAGE JAVA " +
          "PARAMETER STYLE JAVA EXTERNAL NAME '" +
          ProcedureTest.class.getName() + ".nestedDynamicResultSets' " +
          "DYNAMIC RESULT SETS 6",

          "CREATE PROCEDURE INT_OUT(OUT X INTEGER) LANGUAGE JAVA " +
          "PARAMETER STYLE JAVA EXTERNAL NAME '" +
          ProcedureTest.class.getName() + ".intOut'",
    };

    /**
     * Tables that should be created before the tests are run and
     * dropped when the tests have finished. The tables will be
     * cleared before each test case is run. First element in each row
     * is the name of the table, second element is the SQL text which
     * creates it.
     */
    private static final String[][] TABLES = {
        // SIMPLE_TABLE is used by PROC_WITH_SIDE_EFFECTS
        { "SIMPLE_TABLE", "CREATE TABLE SIMPLE_TABLE (id INT)" },
    };

    // PROCEDURES

    /**
     * Stored procedure which returns 0, 1, 2, 3 or 4 <code>ResultSet</code>s.
     *
     * @param number the number of <code>ResultSet</code>s to return
     * @param rs1 first <code>ResultSet</code>
     * @param rs2 second <code>ResultSet</code>
     * @param rs3 third <code>ResultSet</code>
     * @param rs4 fourth <code>ResultSet</code>
     * @exception SQLException if a database error occurs
     */
    public static void retrieveDynamicResults(int number,
                                              ResultSet[] rs1,
                                              ResultSet[] rs2,
                                              ResultSet[] rs3,
                                              ResultSet[] rs4)
        throws SQLException
    {
        Connection c = DriverManager.getConnection("jdbc:default:connection");
        if (number > 0) {
            rs1[0] = c.createStatement().executeQuery("VALUES(1)");
        }
        if (number > 1) {
            rs2[0] = c.createStatement().executeQuery("VALUES(1)");
        }
        if (number > 2) {
            rs3[0] = c.createStatement().executeQuery("VALUES(1)");
        }
        if (number > 3) {
            rs4[0] = c.createStatement().executeQuery("VALUES(1)");
        }
        c.close();
    }

    /**
     * Stored procedure which produces a closed result set.
     *
     * @param closed holder for the closed result set
     * @exception SQLException if a database error occurs
     */
    public static void retrieveClosedResult(ResultSet[] closed)
        throws SQLException
    {
        Connection c = DriverManager.getConnection("jdbc:default:connection");
        closed[0] = c.createStatement().executeQuery("VALUES(1)");
        closed[0].close();
        c.close();
    }

    /**
     * Stored procedure which produces a result set in another
     * connection.
     *
     * @param external result set from another connection
     * @exception SQLException if a database error occurs
     */
    public static void retrieveExternalResult(String dbName, 
            String user, String password, ResultSet[] external)
        throws SQLException
    {
        // Use a server-side connection to the same database.
        String url = "jdbc:derby:" + dbName;
        
        Connection conn = DriverManager.getConnection(url, user, password);
        
        external[0] =
            conn.createStatement().executeQuery("VALUES(1)");
    }

    /**
     * Stored procedure which inserts a row into SIMPLE_TABLE and
     * optionally returns result sets.
     *
     * @param returnResults if one, return one result set; if greater
     * than one, return two result sets; otherwise, return no result
     * set
     * @param rs1 first result set to return
     * @param rs2 second result set to return
     * @exception SQLException if a database error occurs
     */
    public static void procWithSideEffects(int returnResults,
                                           ResultSet[] rs1,
                                           ResultSet[] rs2)
        throws SQLException
    {
        Connection c = DriverManager.getConnection("jdbc:default:connection");
        Statement stmt = c.createStatement();
        stmt.executeUpdate("INSERT INTO SIMPLE_TABLE VALUES (42)");
        if (returnResults > 0) {
            rs1[0] = c.createStatement().executeQuery("VALUES(1)");
        }
        if (returnResults > 1) {
            rs2[0] = c.createStatement().executeQuery("VALUES(1)");
        }
        c.close();
    }
    
    /**
     * Method for a Java procedure that calls another procedure
     * and just passes on the dynamic results from that call.
     */
    public static void nestedDynamicResultSets(String procedureText,
            ResultSet[] rs1, ResultSet[] rs2, ResultSet[] rs3, ResultSet[] rs4,
            ResultSet[] rs5, ResultSet[] rs6)
    throws SQLException
    {
        Connection c = DriverManager.getConnection("jdbc:default:connection");
        
        CallableStatement cs = c.prepareCall("CALL " + procedureText);
        
        cs.execute();
        
        // Mix up the order of the result sets in the returned
        // parameters, ensures order is defined by creation
        // and not parameter order.
        rs6[0] = cs.getResultSet();
        if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
            return;
        rs3[0] = cs.getResultSet();
        if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
            return;
        rs4[0] = cs.getResultSet();
        if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
            return;
        rs2[0] = cs.getResultSet();
        if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
            return;
        rs1[0] = cs.getResultSet();
        if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
            return;
        rs5[0] = cs.getResultSet();
    
    }

    /**
     * Stored procedure with an integer output parameter.
     * @param out an output parameter
     */
    public static void intOut(int[] out) {
        out[0] = 42;
    }

    /**
     * Procedure to test that INOUT args preserve their value when the
     * procedure is re-executed (DERBY-2515). If you add a new datatype
     * to Derby, you will need to add a new argument at the end of this
     * procedure's signature.
     */
    public static  AllTypesTuple   makeFirstAllTypesTuple() throws Exception
    {
        return new AllTypesTuple
            (
             1L,
             new HarmonySerialBlob( new byte[] { (byte) 1, (byte) 1, (byte) 1 } ),
             Boolean.TRUE,
             "firstt",
             new byte[] { (byte) 1, (byte) 1, (byte) 1 },
             new HarmonySerialClob( "firstt" ),
             new Date( 1L ),
             new BigDecimal( "1" ),
             1.0,
             1 ,
             new String( "firstt" ),
             new byte[] { (byte) 1, (byte) 1, (byte) 1 },
             1.0F,
             1,
             new Time( 1L ),
             new Timestamp( 1L ),
             new Price( "USD", new BigDecimal( "1" ), new Timestamp( 1 ) ),
             "firstt",
             new byte[] { (byte) 1, (byte) 1, (byte) 1 }
             );
    }
    public static  AllTypesTuple   makeSecondAllTypesTuple() throws Exception
    {
        return new AllTypesTuple
            (
             2L,
             new HarmonySerialBlob( new byte[] { (byte) 2, (byte) 2, (byte) 2 } ),
             Boolean.FALSE,
             "second",
             new byte[] { (byte) 2, (byte) 2, (byte) 2 },
             new HarmonySerialClob( "second" ),
             new Date( 2L ),
             new BigDecimal( "2" ),
             2.0,
             2,
             new String( "second" ),
             new byte[] { (byte) 2, (byte) 2, (byte) 2 },
             2.0F,
             2,
             new Time( 2L ),
             new Timestamp( 2L ),
             new Price( "USD", new BigDecimal( "2" ), new Timestamp( 2 ) ),
             "second",
             new byte[] { (byte) 2, (byte) 2, (byte) 2 }
             );
    }
    public static  AllTypesTuple   makeThirdAllTypesTuple() throws Exception
    {
        return new AllTypesTuple
            (
             null,
             null,
             null,
             null,
             null,
             null,
             null,
             null,
             null,
             null,
             null,
             null,
             null,
             null,
             null,
             null,
             null,
             null,
             null
             );
    }
    public static  AllTypesTuple   makeFourthAllTypesTuple() throws Exception
    {
        return makeFirstAllTypesTuple();
    }
    public  static  void    proc_2515
        (
         int passNumber,
         String[] message,
         
         Long[] bigintArg,
         Blob[] blobArg,
         Boolean[] booleanArg,
         String[] charArg,
         byte[][] charForBitDataArg,
         Clob[] clobArg,
         Date[] dateArg,
         BigDecimal[] decimalArg,
         Double[] doubleArg,
         Integer[] intArg,
         String[] longVarcharArg,
         byte[][] longVarcharForBitDataArg,
         Float[] realArg,
         Integer[] smallintArg,
         Time[] timeArg,
         Timestamp[] timestampArg,
         Price[] priceArg,
         String[] varcharArg,
         byte[][] varcharForBitDataArg
         )
        throws Exception
    {
        AllTypesTuple   actualCallSignature = new AllTypesTuple
            (
             bigintArg[ 0 ],
             blobArg[ 0 ],
             booleanArg[ 0 ],
             charArg[ 0 ],
             charForBitDataArg[ 0 ],
             clobArg[ 0 ],
             dateArg[ 0 ],
             decimalArg[ 0 ],
             doubleArg[ 0 ],
             intArg[ 0 ],
             longVarcharArg[ 0 ],
             longVarcharForBitDataArg[ 0 ],
             realArg[ 0 ],
             smallintArg[ 0 ],
             timeArg[ 0 ],
             timestampArg[ 0 ],
             priceArg[ 0 ],
             varcharArg[ 0 ],
             varcharForBitDataArg[ 0 ]
             );
        AllTypesTuple   expectedCallSignature;
        AllTypesTuple   returnSignature;

        switch( passNumber )
        {
        case 0:
            expectedCallSignature = makeFirstAllTypesTuple();
            returnSignature = makeSecondAllTypesTuple();
            break;
        case 1:
            expectedCallSignature = makeSecondAllTypesTuple();
            returnSignature = makeThirdAllTypesTuple();
            break;
        case 2:
        default:
            expectedCallSignature = makeThirdAllTypesTuple();
            returnSignature = makeFourthAllTypesTuple();
            break;
        }

        message[ 0 ] = expectedCallSignature.compare( actualCallSignature );
        
        bigintArg[ 0 ] = returnSignature.get_bigintArg();
        blobArg[ 0 ] = returnSignature.get_blobArg();
        booleanArg[ 0 ] = returnSignature.get_booleanArg();
        charArg[ 0 ] = returnSignature.get_charArg();
        charForBitDataArg[ 0 ] = returnSignature.get_charForBitDataArg();
        clobArg[ 0 ] = returnSignature.get_clobArg();
        dateArg[ 0 ] = returnSignature.get_dateArg();
        decimalArg[ 0 ] = returnSignature.get_decimalArg();
        doubleArg[ 0 ] = returnSignature.get_doubleArg();
        intArg[ 0 ] = returnSignature.get_intArg();
        longVarcharArg[ 0 ] = returnSignature.get_longVarcharArg();
        longVarcharForBitDataArg[ 0 ] = returnSignature.get_longVarcharForBitDataArg();
        realArg[ 0 ] = returnSignature.get_realArg();
        smallintArg[ 0 ] = returnSignature.get_smallintArg();
        timeArg[ 0 ] = returnSignature.get_timeArg();
        timestampArg[ 0 ] = returnSignature.get_timestampArg();
        priceArg[ 0 ] = returnSignature.get_priceArg();
        varcharArg[ 0 ] = returnSignature.get_varcharArg();
        varcharForBitDataArg[ 0 ] = returnSignature.get_varcharForBitDataArg();
    }
    
        /**
         * Test various combinations of getMoreResults
         * 
         * @throws SQLException
         */
        public void testGetMoreResults() throws SQLException {

                Statement s = createStatement();
                

                s.executeUpdate("create table MRS.FIVERS(i integer)");
                PreparedStatement ps = prepareStatement("insert into MRS.FIVERS values (?)");
                for (int i = 1; i <= 20; i++) {
                        ps.setInt(1, i);
                        ps.executeUpdate();
                }

                // create a procedure that returns 5 result sets.
                        
                s.executeUpdate("create procedure MRS.FIVEJP() parameter style JAVA READS SQL DATA dynamic result sets 5 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.fivejp'");


                CallableStatement cs = prepareCall("CALL MRS.FIVEJP()");
                ResultSet[] allRS = new ResultSet[5];

                defaultGetMoreResults(cs, allRS);
                java.util.Arrays.fill(allRS, null);
                closeCurrentGetMoreResults(cs, allRS);
                java.util.Arrays.fill(allRS, null);
                keepCurrentGetMoreResults(cs, allRS);                              
                java.util.Arrays.fill(allRS, null);
                mixedGetMoreResults(cs, allRS);
                java.util.Arrays.fill(allRS, null);
                checkExecuteClosesResults(cs, allRS);
                java.util.Arrays.fill(allRS, null);
                checkCSCloseClosesResults(cs,allRS);
                java.util.Arrays.fill(allRS, null);
                
                // a procedure that calls another procedure that returns
                // dynamic result sets, see if the result sets are handled
                // correctly through the nesting.
                CallableStatement nestedCs = prepareCall(
                        "CALL NESTED_RESULT_SETS('MRS.FIVEJP()')");
                defaultGetMoreResults(nestedCs, allRS);
                
        }

        
        /**
         * Check that CallableStatement.execute() closes results
         * @param cs
         * @param allRS
         * @throws SQLException
         */
        private void checkExecuteClosesResults(CallableStatement cs, ResultSet[] allRS) throws SQLException {
            //Fetching result sets with getMoreResults(Statement.KEEP_CURRENT_RESULT) and checking that cs.execute() closes them");          
            cs.execute();
            int pass = 0;
            do {

                    allRS[pass++] = cs.getResultSet();      
                    assertSame(cs, allRS[pass-1].getStatement());
                    // expect everything to stay open.                        

            } while (cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
            //fetched all results
            // All should still be open.
            for (int i = 0; i < 5; i++)
                JDBC.assertDrainResults(allRS[i]);                
            
            cs.execute();
            // all should be closed.
            for (int i = 0; i < 5; i++)
                JDBC.assertClosed(allRS[i]);
        }

        /**
         * Check that CallableStatement.close() closes results
         * @param cs
         * @param allRS
         * @throws SQLException
         */
        private void checkCSCloseClosesResults(CallableStatement cs, ResultSet[] allRS) throws SQLException {
            cs.execute();
            int pass = 0;
            do {

                    allRS[pass++] = cs.getResultSet();         
                    assertSame(cs, allRS[pass-1].getStatement());
                    // expect everything to stay open.                        

            } while (cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
            //fetched all results
            // All should still be open.
            for (int i = 0; i < 5; i++)
                JDBC.assertDrainResults(allRS[i]);                
            
            cs.close();
            // all should be closed.
            for (int i = 0; i < 5; i++)
                JDBC.assertClosed(allRS[i]);
        }

        private void mixedGetMoreResults(CallableStatement cs, ResultSet[] allRS) throws SQLException {
            //Fetching result sets with getMoreResults(<mixture>)"
            cs.execute();

            //first two with KEEP_CURRENT_RESULT"
            allRS[0] = cs.getResultSet();
            assertSame(cs, allRS[0].getStatement());
            boolean moreRS = cs.getMoreResults(Statement.KEEP_CURRENT_RESULT);
            if (!moreRS)
                    fail("FAIL - no second result set");
            allRS[1] = cs.getResultSet();   
            assertSame(cs, allRS[1].getStatement());
            // two open
            allRS[0].next();
            assertEquals(2,allRS[0].getInt(1));
            allRS[1].next();
            assertEquals(3,allRS[1].getInt(1));
            
            //third with CLOSE_CURRENT_RESULT"
            moreRS = cs.getMoreResults(Statement.CLOSE_CURRENT_RESULT);
            if (!moreRS)
                    fail("FAIL - no third result set");
            // first and third open
            allRS[2] = cs.getResultSet();
            assertSame(cs, allRS[2].getStatement());
            assertEquals(2,allRS[0].getInt(1));
            JDBC.assertClosed(allRS[1]);
            allRS[2].next();
            assertEquals(4,allRS[2].getInt(1));

            
            //fourth with KEEP_CURRENT_RESULT"
            moreRS = cs.getMoreResults(Statement.KEEP_CURRENT_RESULT);
            if (!moreRS)
                    fail("FAIL - no fourth result set");
            allRS[3] = cs.getResultSet();
            assertSame(cs, allRS[3].getStatement());
            allRS[3].next();
            // first, third and fourth open, second closed
            assertEquals(2,allRS[0].getInt(1));
            JDBC.assertClosed(allRS[1]);
            assertEquals(4,allRS[2].getInt(1));
            assertEquals(5,allRS[3].getInt(1));
            
            //fifth with CLOSE_ALL_RESULTS"
            moreRS = cs.getMoreResults(Statement.CLOSE_ALL_RESULTS);
            if (!moreRS)
                   fail("FAIL - no fifth result set");
            allRS[4] = cs.getResultSet();
            assertSame(cs, allRS[4].getStatement());
            allRS[4].next();
            // only fifth open
            JDBC.assertClosed(allRS[0]);
            JDBC.assertClosed(allRS[1]);
            JDBC.assertClosed(allRS[2]);
            JDBC.assertClosed(allRS[3]);
            assertEquals(6,allRS[4].getInt(1));
            
            //no more results with with KEEP_CURRENT_RESULT"
            moreRS = cs.getMoreResults(Statement.KEEP_CURRENT_RESULT);
            if (moreRS)
                    fail("FAIL - too many result sets");
            // only fifth open
            JDBC.assertClosed(allRS[0]);
            JDBC.assertClosed(allRS[1]);
            JDBC.assertClosed(allRS[2]);
            JDBC.assertClosed(allRS[3]);
            assertEquals(6,allRS[4].getInt(1));
            
            allRS[4].close();
        }

        /**
         * Check getMoreResults(Statement.KEEP_CURRENT_RESULT)  
         * 
         * @param cs
         * @param allRS
         * @throws SQLException
         */
        private void keepCurrentGetMoreResults(CallableStatement cs, ResultSet[] allRS) throws SQLException {
            cs.execute();
            
            for (int i = 0; i < 5; i++)
            {
                allRS[i] = cs.getResultSet();
                assertSame(cs, allRS[i].getStatement());
                allRS[i].next();
                assertEquals(2+i, allRS[i].getInt(1));
                
                if (i < 4)
                    assertTrue(cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
                else
                    assertFalse(cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
            }            
            
            // resultSets should still be open
            for (int i = 0; i < 5; i++)
                JDBC.assertDrainResults(allRS[i]);
        }

        private void closeCurrentGetMoreResults(CallableStatement cs, ResultSet[] allRS) throws SQLException {
            cs.execute();
            
            for (int i = 0; i < 5; i++)
            {
                allRS[i] = cs.getResultSet();
                assertSame(cs, allRS[i].getStatement());
                allRS[i].next();
                assertEquals(2+i, allRS[i].getInt(1));
                
                if (i < 4)
                    assertTrue(cs.getMoreResults(Statement.CLOSE_CURRENT_RESULT));
                else
                    assertFalse(cs.getMoreResults(Statement.CLOSE_CURRENT_RESULT));
            }
            
            // verify resultSets are closed
            for (int i = 0; i < 5; i++)
                JDBC.assertClosed(allRS[i]);
        }

        /**
         * Test default getMoreResults() closes result set.
         * @param cs
         * @param allRS
         * @throws SQLException
         */
        private void defaultGetMoreResults(CallableStatement cs, ResultSet[] allRS) throws SQLException {
            // execute the procedure that returns 5 result sets and then use the various
            // options of getMoreResults().

            cs.execute();
            
            for (int i = 0; i < 5; i++)
            {
                allRS[i] = cs.getResultSet();
                assertSame(cs, allRS[i].getStatement());
                allRS[i].next();
                assertEquals(2+i, allRS[i].getInt(1));
                
                if (i < 4)
                    assertTrue(cs.getMoreResults());
                else
                    assertFalse(cs.getMoreResults());
            } 
                        
            // verify resultSets are closed
            for (int i = 0; i < 5; i++)
                JDBC.assertClosed(allRS[i]);
        }

    ////////////////////////////////////////////
    //
    // Nested classes.
    //
    ////////////////////////////////////////////

    public  static  final   class   AllTypesTuple
    {
        private Long _bigintArg;
        private Blob _blobArg;
        private Boolean _booleanArg;
        private String _charArg;
        private byte[] _charForBitDataArg;
        private Clob _clobArg;
        private Date _dateArg;
        private BigDecimal _decimalArg;
        private Double _doubleArg;
        private Integer _intArg;
        private String _longVarcharArg;
        private byte[] _longVarcharForBitDataArg;
        private Float _realArg;
        private Integer _smallintArg;
        private Time _timeArg;
        private Timestamp _timestampArg;
        private Price _priceArg;
        private String _varcharArg;
        private byte[] _varcharForBitDataArg;

        public AllTypesTuple
            (
             Long  bigintArg,
             Blob  blobArg,
             Boolean  booleanArg,
             String  charArg,
             byte[]   charForBitDataArg,
             Clob  clobArg,
             Date  dateArg,
             BigDecimal  decimalArg,
             Double  doubleArg,
             Integer  intArg,
             String  longVarcharArg,
             byte[]   longVarcharForBitDataArg,
             Float  realArg,
             Integer  smallintArg,
             Time  timeArg,
             Timestamp  timestampArg,
             Price  priceArg,
             String  varcharArg,
             byte[]   varcharForBitDataArg
             )
        {
            _bigintArg = bigintArg;
            _blobArg = blobArg;
            _booleanArg = booleanArg;
            _charArg = charArg;
            _charForBitDataArg = charForBitDataArg;
            _clobArg = clobArg;
            _dateArg = dateArg;
            _decimalArg = decimalArg;
            _doubleArg = doubleArg;
            _intArg = intArg;
            _longVarcharArg = longVarcharArg;
            _longVarcharForBitDataArg = longVarcharForBitDataArg;
            _realArg = realArg;
            _smallintArg = smallintArg;
            _timeArg = timeArg;
            _timestampArg = timestampArg;
            _priceArg = priceArg;
            _varcharArg = varcharArg;
            _varcharForBitDataArg = varcharForBitDataArg;
        }

        public Long get_bigintArg() { return _bigintArg; }
        public Blob get_blobArg() { return _blobArg; }
        public Boolean get_booleanArg() { return _booleanArg; }
        public String get_charArg() { return _charArg; }
        public byte[] get_charForBitDataArg() { return _charForBitDataArg; }
        public Clob get_clobArg() { return _clobArg; }
        public Date get_dateArg() { return _dateArg; }
        public BigDecimal get_decimalArg() { return _decimalArg; }
        public Double get_doubleArg() { return _doubleArg; }
        public Integer get_intArg() { return _intArg; }
        public String get_longVarcharArg() { return _longVarcharArg; }
        public byte[] get_longVarcharForBitDataArg() { return _longVarcharForBitDataArg; }
        public Float get_realArg() { return _realArg; }
        public Integer get_smallintArg() { return _smallintArg; }
        public Time get_timeArg() { return _timeArg; }
        public Timestamp get_timestampArg() { return _timestampArg; }
        public Price get_priceArg() { return _priceArg; }
        public String get_varcharArg() { return _varcharArg; }
        public byte[] get_varcharForBitDataArg() { return _varcharForBitDataArg; }

        public  String  compare( AllTypesTuple that ) throws Exception
        {
            String  message = "";

            message = message + compare( "_bigintArg", this._bigintArg, that._bigintArg );
            message = message + compare( "_blobArg", this.getBlobBytes(), that.getBlobBytes() );
            message = message + compare( "_booleanArg", this._booleanArg, that._booleanArg );
            message = message + compare( "_charArg", this._charArg, that._charArg );
            message = message + compare( "_charForBitDataArg", this._charForBitDataArg, that._charForBitDataArg );
            message = message + compare( "_clobArg", this.getClobString(), that.getClobString() );
            message = message + compare( "_dateArg", this.getDateString(), that.getDateString() );
            message = message + compare( "_decimalArg", this._decimalArg, that._decimalArg );
            message = message + compare( "_doubleArg", this._doubleArg, that._doubleArg );
            message = message + compare( "_intArg", this._intArg, that._intArg );
            message = message + compare( "_longVarcharArg", this._longVarcharArg, that._longVarcharArg );
            message = message + compare( "_longVarcharForBitDataArg", this._longVarcharForBitDataArg, that._longVarcharForBitDataArg );
            message = message + compare( "_realArg", this._realArg, that._realArg );
            message = message + compare( "_smallintArg", this._smallintArg, that._smallintArg );
            message = message + compare( "_timeArg", this.getTimeString(), that.getTimeString() );
            message = message + compare( "_timestampArg", this._timestampArg, that._timestampArg );
            message = message + compare( "_priceArg", this._priceArg, that._priceArg );
            message = message + compare( "_varcharArg", this._varcharArg, that._varcharArg );
            message = message + compare( "_varcharForBitDataArg", this._varcharForBitDataArg, that._varcharForBitDataArg );

            return message;
        }
        private byte[]  getBlobBytes() throws Exception
        {
            if ( _blobArg == null ) { return null; }
            else { return _blobArg.getBytes( 1, (int) _blobArg.length() ); }
        }
        private String  getClobString() throws Exception
        {
            if ( _clobArg == null ) { return null; }
            else { return _clobArg.getSubString( 1, (int) _clobArg.length() ); }
        }
        private String  getDateString()
        {
            if ( _dateArg ==  null ) { return null; }
            else { return _dateArg.toString(); }
        }
        private String  getTimeString()
        {
            if ( _timeArg == null ) { return null; }
            else { return _timeArg.toString(); }
        }
        private String  compare( String argName, Object left, Object right )
        {
            if ( left == null )
            {
                if ( right == null ) { return ""; }
                return (argName + ": left was null but right was " + right);
            }
            if ( right == null ) { return (argName + ": left = " + left + " but right is null" ); }
            if ( left instanceof byte[] ) { return compareBytes( argName, (byte[]) left, (byte[]) right ); }

            if ( left.equals( right ) ) { return ""; }

            return (argName + ": left = " + left + " but right = " + right);
        }
        private String  compareBytes( String argName, byte[] left, byte[] right )
        {
            int count = left.length;

            if ( count != right.length )
            {
                return (argName + ": left count = " + count + " but right count = " + right.length );
            }
            for ( int i = 0; i < count; i++ )
            {
                if ( left[ i ] != right[ i ] )
                {
                    return (argName + ": left[ " + i + " ] = " + left[ i ] + " but right[ " + i + " ] = " + right[ i ] );
                }
            }

            return "";
        }
    }

    

}