java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMetaDataJdbc30Test.java

/*

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

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.CallableStatement;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import junit.framework.Test;
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;

/**
 * Test the ParameterMetaData class in JDBC 30.
 * This test converts the old jdbcapi/parameterMetaDataJdbc30.java 
 * test to JUnit. 
 */

public class ParameterMetaDataJdbc30Test extends BaseJDBCTestCase {

	/**
         * Create a test with the given name.
         *
         * @param name name of the test.
         */

        public ParameterMetaDataJdbc30Test(String name) {
                super(name);
        }
        
	/**
         * Create suite containing client and embedded tests and to run
	 * all tests in this class
         */
	public static Test suite() {
        BaseTestSuite suite =
            new BaseTestSuite("ParameterMetaDataJdbc30Test");

        if (JDBC.vmSupportsJSR169()) {
            return new BaseTestSuite(
                "Empty ParameterMetaDataJDBC30. " +
                "JSR169 does not support ParameterMetaData");
        } else {
        		suite.addTest(baseSuite("ParameterMetaDataJdbc30Test:embedded"));
        		suite.addTest(
                		TestConfiguration.clientServerDecorator(
                        		baseSuite("ParameterMetaDataJdbc30Test:client")));
		}
        	return suite;
    	}

	private static Test baseSuite(String name)
    	{
            BaseTestSuite suite = new BaseTestSuite(name);

        	suite.addTestSuite(ParameterMetaDataJdbc30Test.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 stmt) throws SQLException {
            		
				Connection conn = getConnection();

                // Create procedure used by
                // testParameterMetadataWithDECIMALParameters() and
                // testParameterMetadataWithLITERALParameters().
                stmt.execute(
                    "CREATE PROCEDURE PMDD(IN pmdI_1 DECIMAL(5,3), " +
                    "IN pmdI_2 DECIMAL(4,2), INOUT pmdI_3 DECIMAL(9,0), " +
                    "OUT pmdI_4 DECIMAL(10,2)) language java " +
                    "parameter style java external name " +
                    "'org.apache.derbyTesting.functionTests.tests.jdbcapi." +
                    "ParameterMetaDataJdbc30Test.dummyDecimal'");

				/**
		                 * Creates the table used in the test cases.
               			 *
                 		 */
                		stmt.execute("create table t ( "+
                          		/* 1 */ "c char(5), "+
                          		/* 2 */ "iNoNull int not null, "+
                          		/* 3 */ "i int, "+
                          		/* 4 */ "de decimal, "+
                          		/* 5 */ "d date)");
                		stmt.executeUpdate("create function RDB(P1 INT) " +
                                		   "RETURNS DECIMAL(10,2) " +
                                  		   "language java external name " +
                                  		   "'org.apache.derbyTesting.functionTests." +
                                  		   "tests.lang.outparams30.returnsBigDecimal' " +
                                   		   "parameter style java");
            		}
        	};
  	}

       /**
     	* Testing a callable statement by calling a SQL procedure with 
	* IN parameters, OUT parameters and IN_OUT parameters.
        *
	* @exception SQLException if error occurs
     	*/
	public void testCallableStatement () throws SQLException {

        	Statement stmt = createStatement();

        	stmt.executeUpdate("create procedure dummyint( " + 
				   "in a integer, in b integer, " + 
				   "out c integer, inout d integer) " + 
				   "language java external name "+ 
				   "'org.apache.derbyTesting.functionTests." +
				   "tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyint' " +
				   "parameter style java");
       		CallableStatement cs = prepareCall("CALL dummyint(?,?,?,?)");
		// parameters 1 and 2 are input only
      		cs.setInt(1,1);
      		cs.setInt(2,1);
      		//parameter 3 is output only
      		cs.registerOutParameter(3,Types.INTEGER);
      		// parameter 4 is input and output
      		Object x = 1;
      		cs.setObject(4,x, Types.INTEGER);
      		cs.registerOutParameter(4,Types.INTEGER);

      		//verify the meta data for the parameters
      		ParameterMetaData paramMetaData = cs.getParameterMetaData();
		assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());

		//expected values to be stored in a 2dim. array	
		String [][] parameterMetaDataArray0 = {
                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
		{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
		{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
		{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_OUT"},
		{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN_OUT"}};
			
		testParameterMetaData(paramMetaData, parameterMetaDataArray0);

      		cs.execute();

		/*
		/* bug 4450 - parameter meta data info for the return parameter was giving
      		/* null pointer exception. In the past, we didn't need to keep the return
      		/* parameter info for callable statement execution and hence we never
      		/* generated the meta data for it. To fix the problem, at the parsing time,
      		/* I set a flag if the call statement is of ? = form. If so, the first
      		/* parameter is a return parameter and save it's meta data rather than
      		/* discarding it.
	 	 */	
	
      		cs = prepareCall("? = call RDB(?)");
      		paramMetaData = cs.getParameterMetaData();
		assertEquals("Unexpected parameter count", 2, paramMetaData.getParameterCount());

		//expected values to be stored in a 2dim. array
                String parameterMetaDataArray1 [][] = {
                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
                {"PARAMETER_NULLABLE", "true", "31", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"},
                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"}};

                testParameterMetaData(paramMetaData, parameterMetaDataArray1);

        stmt.execute("drop procedure dummyint");

		stmt.close();
		cs.close();
	}
	/**
         * Testing a prepared statement.
     	 *
	 * @exception SQLException if database access errors or other errors occur
         */
	public void testPreparedStatement () throws SQLException {
		//next testing a prepared statement
      		PreparedStatement ps = prepareStatement("insert into t values(?, ?, ?, ?, ?)");
      		ps.setNull(1, java.sql.Types.CHAR);
      		ps.setInt(2, 1);
      		ps.setNull(3, java.sql.Types.INTEGER);
      		ps.setBigDecimal(4,new BigDecimal("1"));
      		ps.setNull(5, java.sql.Types.DATE);

      		ParameterMetaData paramMetaData = ps.getParameterMetaData();
		assertEquals("Unexpected parameter count", 5, paramMetaData.getParameterCount());

		//expected values to be stored in a 2dim. array
                String [][] parameterMetaDataArray0 = {
                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
                {"PARAMETER_NULLABLE", "false", "5", "0", "1", "CHAR", "java.lang.String", "PARAMETER_MODE_IN"},
                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
                {"PARAMETER_NULLABLE", "true", "5", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
                {"PARAMETER_NULLABLE", "false", "10", "0", "91", "DATE", "java.sql.Date", "PARAMETER_MODE_IN"}};

                testParameterMetaData(paramMetaData, parameterMetaDataArray0);
	
		/*
      		 *  JCC seems to report these parameters as MODE_UNKNOWN, where as Derby uses MODE_IN
      		 *  JCC behaviour with network server matches its behaviour with DB2
      		 *  getPrecision() returns 0 for CHAR/DATE/BIT types for Derby. JCC shows maxlen
		 */
      		ps.execute();

		/*
		 * bug 4533 - associated parameters should not be included in the parameter meta data list
      		 * Following statement systab will generate 4 associated parameters for the 2
      		 * user parameters. This results in total 6 parameters for the prepared statement
      		 * internally. But we should only show 2 user visible parameters through
      		 * getParameterMetaData().
		 */
      		ps = prepareStatement("select * from sys.systables where " +
             			      " CAST(tablename AS VARCHAR(128)) like ? and CAST(tableID AS CHAR(36)) like ?");
      		ps.setString (1, "SYS%");
      		ps.setString (2, "8000001%");
      		paramMetaData = ps.getParameterMetaData();
		assertEquals("Unexpected parameter count", 2, paramMetaData.getParameterCount());

		//expected values to be stored in a 2dim. array
                String parameterMetaDataArray1 [][] = {
                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
                {"PARAMETER_NULLABLE", "false", "128", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_IN"},
                {"PARAMETER_NULLABLE", "false", "36", "0", "1", "CHAR", "java.lang.String", "PARAMETER_MODE_IN"}};

                testParameterMetaData(paramMetaData, parameterMetaDataArray1);

      		ps.execute();

		ps.close();
	}

	/**
         * Test boolean parameters
     	 *
	 * @exception SQLException if database access errors or other errors occur
         */
	public void testBooleanParameters() throws SQLException
    {
        Statement stmt = createStatement();
        stmt.executeUpdate
            (
             "create function booleanValue( b boolean )\n" +
             "returns varchar( 100 ) language java parameter style java no sql\n" +
             "external name 'org.apache.derbyTesting.functionTests.tests.lang.BooleanValuesTest.booleanValue'\n"
             );
        stmt.executeUpdate
            (
             "create table t_boolean( b boolean )"
             );
        stmt.close();

        //
        // Test metadata for parameters to boolean-valued functions.
        //
        PreparedStatement ps = prepareStatement("values( booleanValue( ? ) )");

        ParameterMetaData paramMetaData = ps.getParameterMetaData();
		assertEquals("Unexpected parameter count", 1, paramMetaData.getParameterCount());

		//expected values to be stored in a 2dim. array
        String [][] parameterMetaDataArray0 =
            {
                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
                {"PARAMETER_NULLABLE", "false", "1", "0", "16", "BOOLEAN", "java.lang.Boolean", "PARAMETER_MODE_IN"},
            };

        testParameterMetaData(paramMetaData, parameterMetaDataArray0);

		ps.close();

        //
        // Test metadata for boolean-valued columns.
        //
        ps = prepareStatement("insert into t_boolean values(?)");
        ps.setBoolean(1, true);

        paramMetaData = ps.getParameterMetaData();
		assertEquals("Unexpected parameter count", 1, paramMetaData.getParameterCount());
        parameterMetaDataArray0 = new String[][]
            {
                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
                {"PARAMETER_NULLABLE", "false", "1", "0", "16", "BOOLEAN", "java.lang.Boolean", "PARAMETER_MODE_IN"},
            };
        testParameterMetaData(paramMetaData, parameterMetaDataArray0);

        ps.close();
	}

    /**
     * Testing a callable statement by calling a SQL procedure with 
     * boolean IN parameters, OUT parameters and IN_OUT parameters.
     */
	public void testBooleanProcedureArgs() throws Exception
    {
        Statement stmt = createStatement();

        stmt.executeUpdate("create procedure booleanProc" + 
                           "( in inarg boolean, out outarg boolean, inout inoutarg boolean )" + 
                           "language java external name "+ 
                           "'org.apache.derbyTesting.functionTests." +
                           "tests.jdbcapi.ParameterMetaDataJdbc30Test.booleanProc' " +
                           "parameter style java");
        
        CallableStatement cs = prepareCall("CALL booleanProc(?,?,?)");
        cs.registerOutParameter(2,Types.BOOLEAN);
        cs.registerOutParameter(3,Types.BOOLEAN);

        //verify the meta data for the parameters
        ParameterMetaData paramMetaData = cs.getParameterMetaData();
		assertEquals("Unexpected parameter count", 3, paramMetaData.getParameterCount());
        
		//expected values to be stored in a 2dim. array	
		String [][] parameterMetaDataArray0 =
            {
                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
                {"PARAMETER_NULLABLE", "false", "1", "0", "16", "BOOLEAN", "java.lang.Boolean", "PARAMETER_MODE_IN"},
                {"PARAMETER_NULLABLE", "false", "1", "0", "16", "BOOLEAN", "java.lang.Boolean", "PARAMETER_MODE_OUT"},
                {"PARAMETER_NULLABLE", "false", "1", "0", "16", "BOOLEAN", "java.lang.Boolean", "PARAMETER_MODE_IN_OUT"},
            };
        
		testParameterMetaData(paramMetaData, parameterMetaDataArray0);

        cs.setBoolean( 1,  true );
        cs.setBoolean( 3, true );
        cs.execute();
        assertEquals( true, cs.getBoolean( 2 ) );
        assertEquals( false, cs.getBoolean( 3 ) );

        cs.setObject( 1,  Boolean.FALSE );
        cs.setObject( 3, Boolean.FALSE );
        cs.execute();
        assertEquals( false, cs.getBoolean( 2 ) );
        assertEquals( true, cs.getBoolean( 3 ) );

		stmt.close();
		cs.close();
	}

	/** 
	 * DERBY-44 added support for SELECT ... WHERE column LIKE ? ESCAPE ?
         * This test case tests
         *   a) that such a statement compiles, and
         *   b) that we get the correct error message if the escape
         *      sequence is an empty string (at one point this would
         *      lead to a StringIndexOutOfBoundsException)`
	 *
	 * @exception SQLException if error occurs
	 */
	public void testLikeEscaleStatement () throws SQLException {

      		//variation 1, testing DERBY-44 
      		PreparedStatement ps = prepareStatement("select * from sys.systables " +
							"where CAST(tablename AS VARCHAR(128)) like ? escape CAST(? AS VARCHAR(128))");
      		ps.setString (1, "SYS%");
      		ps.setString (2, "");
      		ParameterMetaData paramMetaData = ps.getParameterMetaData();
		assertEquals("Unexpected parameter count", 2, paramMetaData.getParameterCount());

		//expected values to be stored in a 2dim. array
                String parameterMetaDataArray0 [][] = {
                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
                {"PARAMETER_NULLABLE", "false", "128", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_IN"},
                {"PARAMETER_NULLABLE", "false", "128", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_IN"}};

                testParameterMetaData(paramMetaData, parameterMetaDataArray0);

      		try {
          		ResultSet rs = ps.executeQuery();
          		rs.next();
			fail("DERBY-44 failed (didn't get SQLSTATE 22019)");
          		rs.close();
      		} catch (SQLException e) {
			assertSQLState("22019", e);
      		}
		ps.close();
	}

	 /** 
          * test execute statements that no parameters would be returned if 
          * prepareStatement("execute statement systab using values('SYS%','8000001%')");
	  *
	  * @exception SQLException if error occurs
          */

	public void testExecuteStatementUsing () throws SQLException {
	
		/*
		 * the test no longer tests 4552, but kept as an interesting test scenario
                 * bug 4552 - no parameters would be returned for execute statement using
                 * System.out.println("Bug 4552 - no parameters would be returned for execute statement using");
                 * orig: ps = con.prepareStatement("execute statement systab using values('SYS%','8000001%')");
		 */
      		PreparedStatement ps = prepareStatement("select * from sys.systables " + 
							"where CAST(tablename AS VARCHAR(128)) like 'SYS%' and " + 
							"CAST(tableID AS VARCHAR(128)) like '8000001%'");

      		ParameterMetaData paramMetaData = ps.getParameterMetaData();
		assertEquals("Unexpected parameter count", 0, paramMetaData.getParameterCount());

		//expected values to be stored in a 2dim. array
                String parameterMetaDataArray0 [][] = null;

                testParameterMetaData(paramMetaData, parameterMetaDataArray0);

      		ps.execute();

		ps.close();
	}
	/** 
         * testing SELECT statements for BOOLEANs (1 for true, 0 for false) 
	 *
	 * @exception SQLException if error occurs
	 */ 
	public void testSelectStatementUsingBoolean () throws SQLException {

		/*
		 * Bug 4654 - Null Pointer exception while executuing a select with a
         	 * where clause parameter of type 'TRUE' or 'FALSE' constants. The existing prior to
         	 * exposing parameter metadata didn't need to fill in metadata information for where
         	 * clause parameter in the example above.
         	 * This no longer makes sense, for we cannot take BOOLEANs anymore.
         	 * replace with a simple where 1 = ?. Which would take either 1 for true, or 0 for false
		 */
      		PreparedStatement ps = prepareStatement("select * from t "+  
							"where 1=? for update");

      		ParameterMetaData paramMetaData = ps.getParameterMetaData();
		assertEquals("Unexpected parameter count", 1, paramMetaData.getParameterCount());

		//expected values to be stored in a 2dim. array
                String parameterMetaDataArray0 [][] = {
                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"}};

                testParameterMetaData(paramMetaData, parameterMetaDataArray0);

      		dumpParameterMetaDataNegative(paramMetaData);
      		ps.setInt(1,1);
      		ps.execute();

		ps.close();
	}
	/** 
         * test: no parameter for the statement and then do getParameterMetaData() 
	 *
	 * @exception SQLException if error occurs
         */
	public void testSelectStatementUsingNoParameter () throws SQLException {

      		PreparedStatement ps = prepareStatement("select * from t");
      		ParameterMetaData paramMetaData = ps.getParameterMetaData();
		assertEquals("Unexpected parameter count", 0, paramMetaData.getParameterCount());
		
		//expected values to be stored in a 2dim. array
                String parameterMetaDataArray0 [][] = null; 

                testParameterMetaData(paramMetaData, parameterMetaDataArray0);

      		ps.execute();

		ps.close();
	}
	/**
         * test: the scale returned should be the one set by registerOutParameter 
	 *
 	 * @exception SQLException
         */
	public void testCallableStatementReturnedScale () throws SQLException {

		/*
                 *  DERBY-2810 - getParameterType behavior is different in Embedded and 
                 *  Network Client when set by registerOutParameter 
                 *  temporarily disabling Network Client.
                 */
		if (!usingDerbyNetClient()) {
			Statement stmt = createStatement();
      			stmt.executeUpdate("create procedure dummy_numeric_Proc(out a NUMERIC(30,15), out b NUMERIC(30,15)) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummy_numeric_Proc'");
      			CallableStatement cs = prepareCall("CALL dummy_numeric_Proc(?,?)");
      			cs.registerOutParameter(1, Types.NUMERIC);
      			cs.registerOutParameter(2, Types.NUMERIC,15);
      			cs.execute();
			assertEquals("Unexpected parameter count", 2, cs.getParameterMetaData().getParameterCount());

			//expected values to be stored in a 2dim. array
                	String parameterMetaDataArray0 [][] = {
                	//isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
                	{"PARAMETER_NULLABLE", "true", "30", "15", "2", "NUMERIC", "java.math.BigDecimal", "PARAMETER_MODE_OUT"},
                	{"PARAMETER_NULLABLE", "true", "30", "15", "2", "NUMERIC", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

                	testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);
		
			cs.close();
		}
	}
	/**
         * test behaviour of meta data and out params after re-compile 
	 *
	 * @exception SQLException if error occurs
         */
	public void testMetatdataAfterProcRecompile () throws SQLException {

		Statement stmt = createStatement();

        stmt.executeUpdate("create procedure dummyint( "
                + "in a integer, in b integer, "
                + "out c integer, inout d integer) "
                + "language java external name "
                + "'org.apache.derbyTesting.functionTests."
                + "tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyint' "
                + "parameter style java");

      		CallableStatement cs = prepareCall("CALL dummyint(?,?,?,?)");
          	cs.registerOutParameter(3,Types.INTEGER);
      		cs.registerOutParameter(4,Types.INTEGER);
      		cs.setInt(1,1);
      		cs.setInt(2,1);
          	cs.setInt(4,4);
	
		//expected values to be stored in a 2dim. array
		String parameterMetaDataArray0 [][] = {
                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_OUT"},
                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN_OUT"}};

                testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);

          	cs.execute();
		assertEquals("Unexpected DUMMYINT alias returned", 11111, cs.getInt(4));

		/* 
		 *  DERBY-2786 - Behaviour of inout parameters in Embedded and Network client is
         	 *  different if parameters are set but the CallableStatment is not executed.
		 *  temporarily disabling Network Client. 
         	 */
		if (!usingDerbyNetClient()) {
      			stmt.executeUpdate("drop procedure dummyint");
      			stmt.executeUpdate("create procedure dummyint(in a integer, in b integer, out c integer, inout d integer) language java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyint2' parameter style java");
      			cs.execute();

			String parameterMetaDataArray1 [][] = {
                	//isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
                	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
                	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
                	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_OUT"},
                	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN_OUT"}};

                	testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray1);

      			cs.setInt(4, 6);
      			// following is incorrect sequence, should execute first, then get
      			// but leaving it in as an additional negative test. see beetle 5886
			assertEquals("Unexpected DUMMYINT alias returned", 6, cs.getInt(4));

      			cs.execute();
			assertEquals("Unexpected DUMMYINT alias returned", 22222, cs.getInt(4));
		}
      		cs.close();

        stmt.execute("drop procedure dummyint");
	}
	/**
         * test ParameterMetaData for Java procedures with INTEGER parameters 
	 * 
	 * @exception SQLException if error occurs
         */
	public void testParameterMetadataWithINTParameters () throws SQLException {

		Statement stmt = createStatement();
       		stmt.execute("CREATE PROCEDURE PMDI(IN pmdI_1 INTEGER, IN pmdI_2 INTEGER, INOUT pmdI_3 INTEGER, OUT pmdI_4 INTEGER) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyint'");
      		CallableStatement cs = prepareCall("CALL PMDI(?, ?, ?, ?)");
	
		// parameters 1 and 2 are input only
               	cs.setInt(1,1);
        	cs.setInt(2,1);
		// parameter 3 is input and output
                Object x = 1;
                cs.setObject(3,x, Types.INTEGER);
                cs.registerOutParameter(3,Types.INTEGER);
               	//parameter 4 is output only
               	cs.registerOutParameter(4,Types.INTEGER);

               	//verify the meta data for the parameters
               	ParameterMetaData paramMetaData = cs.getParameterMetaData();
               	assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());

		//expected values to be stored in a 2dim. array
                String parameterMetaDataArray0 [][] = {
                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN_OUT"},
                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_OUT"}};

                testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);
			
       		cs.close();
       		stmt.execute("DROP PROCEDURE PMDI");
            stmt.close();
	}
	/**
         * test ParameterMetaData for Java procedures with CHAR parameters
	 *
	 * @exception SQLException if error occurs
         */
	 public void testParameterMetadataWithCHARParameters () throws SQLException {

		Statement stmt = createStatement();
       		stmt.execute("CREATE PROCEDURE PMDC(IN pmdI_1 CHAR(10), IN pmdI_2 VARCHAR(25), INOUT pmdI_3 CHAR(19), OUT pmdI_4 VARCHAR(32)) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyString'");
      		CallableStatement cs = prepareCall("CALL PMDC(?, ?, ?, ?)");
		// parameters 1 and 2 are input only	
		cs.setString(1, "TEST0");
		cs.setString(2, "TEST1");
		// parameter 3 is input and output
                Object x = new String("TEST");
                cs.setObject(3,x, Types.CHAR);
                cs.registerOutParameter(3,Types.CHAR);
                //parameter 4 is output only
                cs.registerOutParameter(4,Types.CHAR);
 		//verify the meta data for the parameters
               	ParameterMetaData paramMetaData = cs.getParameterMetaData();
		assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());
	
		//expected values to be stored in a 2dim. array
                String parameterMetaDataArray0 [][] = {
                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
                {"PARAMETER_NULLABLE", "false", "10", "0", "1", "CHAR", "java.lang.String", "PARAMETER_MODE_IN"},
                {"PARAMETER_NULLABLE", "false", "25", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_IN"},
                {"PARAMETER_NULLABLE", "false", "19", "0", "1", "CHAR", "java.lang.String", "PARAMETER_MODE_IN_OUT"},
                {"PARAMETER_NULLABLE", "false", "32", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_OUT"}};

		testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);

       		cs.close();
       		stmt.execute("DROP PROCEDURE PMDC");
            stmt.close();
	}

	/**
         * test ParameterMetaData for Java procedures with TIMESTAMP parameters
	 *
	 * @exception SQLException if error occurs
         */
    public void testParameterMetadataWithTimestampParameters () throws SQLException
    {
        Statement stmt = createStatement();
        stmt.execute("CREATE PROCEDURE PMDT(IN pmdI_1 TIMESTAMP, INOUT pmdI_2 TIMESTAMP, OUT pmdI_3 TIMESTAMP) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyString'");
        CallableStatement cs = prepareCall("CALL PMDT(?, ?, ?)");
        // parameter 1 is input only	
        cs.setTimestamp(1, new Timestamp( 0L ) );
        // parameter 2 is input and output
        Object x = new Timestamp( 2L );
        cs.setObject(2,x, Types.CHAR);
        cs.registerOutParameter(2,Types.TIMESTAMP);
        //parameter 3 is output only
        cs.registerOutParameter(3,Types.TIMESTAMP);
        //verify the meta data for the parameters
        ParameterMetaData paramMetaData = cs.getParameterMetaData();
        assertEquals("Unexpected parameter count", 3, paramMetaData.getParameterCount());
        
        //expected values to be stored in a 2dim. array
        String parameterMetaDataArray0 [][] = {
            //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
            {"PARAMETER_NULLABLE", "false", "29", "9", "93", "TIMESTAMP", "java.sql.Timestamp", "PARAMETER_MODE_IN"},
            {"PARAMETER_NULLABLE", "false", "29", "9", "93", "TIMESTAMP", "java.sql.Timestamp", "PARAMETER_MODE_IN_OUT"},
            {"PARAMETER_NULLABLE", "false", "29", "9", "93", "TIMESTAMP", "java.sql.Timestamp", "PARAMETER_MODE_OUT"}};
        
        testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);
        
        cs.close();
        stmt.execute("DROP PROCEDURE PMDT");
        stmt.close();
    }
    
	/**
         *  test ParameterMetaData for Java procedures with DECIMAL parameters
	 *
	 * @exception SQLException if error occurs
         */
	public void testParameterMetadataWithDECIMALParameters () throws SQLException {

      		CallableStatement cs = prepareCall("CALL PMDD(?, ?, ?, ?)");

		// parameters 1 and 2 are input only
                cs.setBigDecimal(1,new BigDecimal("1"));;
                cs.setBigDecimal(2,new BigDecimal("1"));;
                // parameter 3 is input and output
                Object x = new BigDecimal(1.1);
                cs.setObject(3,x, Types.DECIMAL);
                cs.registerOutParameter(3,Types.DECIMAL);
                //parameter 4 is output only
                cs.registerOutParameter(4,Types.DECIMAL);
		//verify the meta data for the parameters
              	ParameterMetaData paramMetaData = cs.getParameterMetaData();
		assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());

		//expected values to be stored in a 2dim. array
                String parameterMetaDataArray0 [][] = {
                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
                {"PARAMETER_NULLABLE", "true", "5", "3", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
                {"PARAMETER_NULLABLE", "true", "4", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
                {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
                {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

                testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);

       		cs.close();
	}
	/**
         * test ParameterMetaData for Java procedures with some literal parameters
	 *
	 * @exception SQLException if error occurs
         */
	public void testParameterMetadataWithLITERALParameters () throws SQLException {

      		CallableStatement cs = prepareCall("CALL PMDD(32.4, ?, ?, ?)");
		// parameters 2 is input only
                cs.setBigDecimal(1,new BigDecimal("1"));;
                // parameter 3 is input and output
                Object x = new BigDecimal(1.1);
                cs.setObject(2,x, Types.DECIMAL);
                cs.registerOutParameter(2,Types.DECIMAL);
                //parameter 4 is output only
                cs.registerOutParameter(3,Types.DECIMAL);

		//verify the meta data for the parameters
                ParameterMetaData paramMetaData = cs.getParameterMetaData();
                assertEquals("Unexpected parameter count", 3, paramMetaData.getParameterCount());

		//expected values to be stored in a 2dim. array
                String parameterMetaDataArray0 [][] = {
                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
                {"PARAMETER_NULLABLE", "true", "4", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
                {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
                {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

                testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);

          	cs.close();

      		cs = prepareCall("CALL PMDD(32.4, 47.9, ?, ?)");
		// parameter 3 is input and output
                Object y = new BigDecimal(1.1);
                cs.setObject(1,y, Types.DECIMAL);
                cs.registerOutParameter(1,Types.DECIMAL);
                //parameter 4 is output only
                cs.registerOutParameter(2,Types.DECIMAL);
		paramMetaData = cs.getParameterMetaData();
                assertEquals("Unexpected parameter count", 2, paramMetaData.getParameterCount());

		//expected values to be stored in a 2dim. array
                String parameterMetaDataArray1 [][] = {
                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
                {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
                {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

                testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray1);

          	cs.close();

      		cs = prepareCall("CALL PMDD(?, 38.2, ?, ?)");
		// parameters 1 is input only
                cs.setBigDecimal(1,new BigDecimal("1"));;
                // parameter 3 is input and output
                Object z = new BigDecimal(1.1);
                cs.setObject(2,z, Types.DECIMAL);
                cs.registerOutParameter(2,Types.DECIMAL);
                //parameter 4 is output only
                cs.registerOutParameter(3,Types.DECIMAL);

		//verify the meta data for the parameters
                paramMetaData = cs.getParameterMetaData();
		assertEquals("Unexpected parameter count", 3, paramMetaData.getParameterCount());

		//expected values to be stored in a 2dim. array
                String parameterMetaDataArray2 [][] = {
                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
                {"PARAMETER_NULLABLE", "true", "5", "3", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
                {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
                {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

                testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray2);

          	cs.close();
	}
	/**
         * print the parameter isNullable value in human readable form
	 *
	 * @param nullabilityValue 
         */
	// @return the nullability status of the given parameter
	static String parameterIsNullableInStringForm(int nullabilityValue){
		if (nullabilityValue ==  ParameterMetaData.parameterNoNulls)
				  return("PARAMETER_NO_NULLS");
		else if (nullabilityValue ==  ParameterMetaData.parameterNullable)
				  return("PARAMETER_NULLABLE");
		else if (nullabilityValue ==  ParameterMetaData.parameterNullableUnknown)
				  return("PARAMETER_NULLABLE_UNKNOWN");
		else
				  return("ERROR: donot recognize this parameter isNullable() value");
  	}

	/**
         * print the parameter mode in human readable form
         *
         * @param mode identifies parameter's mode (IN, OUT, or IN_OUT)
	 * @return     the parameter mode in readable form
         *             
         */
	static String parameterModeInStringForm(int mode){
		if (mode ==  ParameterMetaData.parameterModeIn)
				  return("PARAMETER_MODE_IN");
		else if (mode ==  ParameterMetaData.parameterModeInOut )
				  return("PARAMETER_MODE_IN_OUT");
		else if (mode ==  ParameterMetaData.parameterModeOut)
				  return("PARAMETER_MODE_OUT");
		else if (mode ==  ParameterMetaData.parameterModeUnknown)
				  return("PARAMETER_MODE_UNKNOWN");
		else
				  return("ERROR: donot recognize this parameter mode");
  	}
	/**
         * tests parameterMetaData and reports error if the ParameterMetaData results
	 * does not match the expected results.
         *
         * @param paramMetaData ParameterMetadata object
	 * @param paramMetaDataArray 2 dimensional array containing expected test results. 
	 * @exception SQLException if any error occurs
         */
	static void testParameterMetaData(ParameterMetaData paramMetaData, String [][] paramMetaDataArray) throws SQLException {
		int numParam = paramMetaData.getParameterCount();
		
		for (int i=0, j=0; i<numParam; i++) {	
                       	assertEquals("Unexpected parameter isNullable", paramMetaDataArray[i][j++], parameterIsNullableInStringForm(paramMetaData.isNullable(i+1)));
                       	assertEquals("Unexpected parameter isSigned", Boolean.valueOf(paramMetaDataArray[i][j++]).booleanValue(), paramMetaData.isSigned(i+1));
                       	assertEquals("Unexpected parameter getPrecision", Integer.parseInt(paramMetaDataArray[i][j++]) ,  paramMetaData.getPrecision(i+1));
                       	assertEquals("Unexpected parameter getScale", Integer.parseInt(paramMetaDataArray[i][j++]) , paramMetaData.getScale(i+1));
                       	assertEquals("Unexpected parameter getParameterType", Integer.parseInt(paramMetaDataArray[i][j++]) , paramMetaData.getParameterType(i+1));
                       	assertEquals("Unexpected parameter getParameterTypeName", paramMetaDataArray[i][j++] , paramMetaData.getParameterTypeName(i+1));
                       	assertEquals("Unexpected parameter getParameterClassName", paramMetaDataArray[i][j++] , paramMetaData.getParameterClassName(i+1));
                       	assertEquals("Unexpected parameter getParameterMode", paramMetaDataArray[i][j++] , parameterModeInStringForm(paramMetaData.getParameterMode(i+1)));
			
			j=0;
		}
	}
 	/**
         * ParameterMetaData Negative Test 
         *
         * @param paramMetaData ParameterMetadata object
         * @exception SQLException if any error occurs
         */	
	static void dumpParameterMetaDataNegative(ParameterMetaData paramMetaData) throws SQLException {

                int numParam = paramMetaData.getParameterCount();

		/*
                *  DERBY-3039 - ParameterMetaData.isNullable() returns differenet SQLState in Embedded  
                *  and Network Client 
                */
		String expectedSQLState = (usingEmbedded())?"XCL13":"XCL14";
	
		try {
       			paramMetaData.isNullable(-1);
       			fail("parameterMetaData.isNullable(-1) should have failed");
   		} catch (SQLException se)
       		{
       			assertSQLState(expectedSQLState, se);
       		}
		try {
                        paramMetaData.isNullable(0);
                        fail("parameterMetaData.isNullable(0) should have failed");
                } catch (SQLException se)
                {
                        assertSQLState(expectedSQLState, se);
                }
		try {
                        paramMetaData.isNullable(numParam+1);
                        fail("parameterMetaData.isNullable("+(numParam+1)+") should have failed");
                } catch (SQLException se)
                {
                        assertSQLState(expectedSQLState, se);
                }

	}
	/**
         * A simple method to test callable statement. This is the Java method 
	 * for procedure dummyint.
         *
         * @param in_param 
	 * @param in_param2  
	 * @param in_param3 
	 * @param in_param4
         * @exception SQLException
         */
        public static void dummyint (int in_param, int in_param2, int[] in_param3, int[] in_param4)
                                                                   throws SQLException {

                in_param4[0] = 11111;
        }

	/**
         *  This is the Java method for procedure dummyint.
         *
         * @param in_param  
         * @param in_param2 
         * @param in_param3 
         * @param in_param4 
         * @exception SQLException
         */
        public static void dummyint2 (int in_param, int in_param2, int[] in_param3, int[] in_param4)
                                                                   throws SQLException {
                in_param4[0] = 22222;
        }

	/**
	 * A really simple method to test callable statement. This is the Java method
         * for procedure dummy_numeric_Proc.
         *
         * @param max_param  
         * @param min_param  
         * @exception SQLException
         */
        public static void dummy_numeric_Proc (BigDecimal[] max_param,BigDecimal[] min_param)
                                                                 throws SQLException {
        }
	
	/**
         * Java method for procedure PMDC which tests ParameterMetaData for Java procedures 
         * with CHAR parameters.  
	 * 
         * @param in_param 
         * @param in_param2
         * @param in_param3 
         * @param in_param4 
         */
        public static void dummyString (String in_param, String in_param2, String[] in_param3, String[] in_param4) {
        }
	
	/**
     * Java method for procedure PMDT which tests ParameterMetaData for Java procedures 
     * with TIMESTAMP parameters.  
     */
    public static void dummyString (Timestamp in_param1, Timestamp[] inout_param2, Timestamp[] out_param3) {}

	
	/**
         * Java method for procedure PMDD which tests ParameterMetaData for Java procedures
	 * with DECIMAL parameters. 
         *
         * @param in_param  
         * @param in_param2 
         * @param in_param3 
         * @param in_param4 
         */
        public static void dummyDecimal(BigDecimal in_param, BigDecimal in_param2, BigDecimal[] in_param3, BigDecimal[] in_param4) {
	 }

    /** Java method for testing procedures with boolean args */
    public static void booleanProc( boolean inarg, boolean[] outarg, boolean[] inoutarg )
    {
        outarg[ 0 ] = inarg;
        inoutarg[ 0 ] = !inoutarg[ 0 ];
    }
}