outparams.java

derby database source code.good for you.

/*

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

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

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.

 */

package org.apache.derbyTesting.functionTests.tests.lang;
import java.sql.*;

import org.apache.derby.tools.ij;
import org.apache.derby.iapi.reference.JDBC30Translation;
import java.io.PrintStream;
import java.math.BigInteger;
import java.math.BigDecimal;

public class outparams
{
 
	static final String outputMethods[] =
	{
		"takesNothing",

		null, 
	
		null,
		null,
	
		"takesShortPrimitive",
		null,
	
		"takesIntegerPrimitive",
		null,
	
		"takesLongPrimitive",
		null,
	
		"takesFloatPrimitive",
		null,
	
		"takesDoublePrimitive",
		null,
	
		"takesBigDecimal",
	
		"takesByteArray",
		
		"takesString",
		
		"takesDate",
		
		"takesTimestamp",
	
		"takesTime",
	
		null
	};

	// parameter types for outputMethods.
	private static final String[] outputProcParam =
	{
		null, // "takesNothing",

		null, 
	
		null,
		null,
	
		"SMALLINT", // "takesShortPrimitive",
		null,
	
		"INT", // "takesIntegerPrimitive",
		null,
	
		"BIGINT", // "takesLongPrimitive",
		null,
	
		"REAL", // "takesFloatPrimitive",
		null,
	
		"DOUBLE", // "takesDoublePrimitive",
		null,
	
		"DECIMAL(10,4)", // "takesBigDecimal",
	
		"VARCHAR(40) FOR BIT DATA", // "takesByteArray",
		
		"VARCHAR(40)", // "takesString",
		
		"DATE", // "takesDate",
		
		"TIMESTAMP", // "takesTimestamp",
	
		"TIME", // "takesTime",
	
		null
	};

	
	static final String returnMethods[] =
	{
		"returnsNothing",

		null,
		null,
	
		"returnsShortP",
		null,
	
		"returnsIntegerP",
		null,
	
		"returnsLongP",
		null,
	
		"returnsFloatP",
		null,
	
		"returnsDoubleP",
		null,
	
		"returnsBigDecimal",
	
		"returnsByteArray",
		
		"returnsString",
		
		"returnsDate",
		
		"returnsTimestamp",
	
		"returnsTime",
	
		null
	};

	static final String[] returnMethodType =
	{
		null, // "returnsNothing",

		null, // "returnsBytePrimitive",
		null, // "returnsByte",
	
		"SMALLINT", // "returnsShortPrimitive",
		null, // "returnsShort",
	
		"INT", // "returnsIntegerPrimitive",
		null, // "returnsInteger",
	
		"BIGINT", // "returnsLongPrimitive",
		null, // "returnsLong",
	
		"REAL", // "returnsFloatPrimitive",
		null, // "returnsFloat",
	
		"DOUBLE", // "returnsDoublePrimitive",
		null, // "returnsDouble",
	
		"DECIMAL(10,2)", // "returnsBigDecimal",
	
		"VARCHAR(40) FOR BIT DATA", // "returnsByteArray",
		
		"VARCHAR(40)", // "returnsString",
		
		"DATE", // "returnsDate",
		
		"TIMESTAMP", // "returnsTimestamp",
	
		"TIME", // "returnsTime",
	
		null, // "returnsBigInteger"
	};
	
	static final int types[] =
	{
		Types.BIT,
		JDBC30Translation.SQL_TYPES_BOOLEAN,
		Types.TINYINT,
		Types.SMALLINT,
		Types.INTEGER,
		Types.BIGINT,
		Types.FLOAT,
		Types.REAL,
		Types.DOUBLE,
		Types.NUMERIC,
		Types.DECIMAL,
		Types.CHAR,
		Types.VARCHAR,
		Types.LONGVARCHAR,
		Types.DATE,
		Types.TIME, 
		Types.TIMESTAMP,
		Types.BINARY,
		Types.VARBINARY,
		Types.LONGVARBINARY,
		Types.OTHER
	};
	
	static final String typeNames[] =
	{
		"BIT",
		"BOOLEAN",
		"TINYINT",
		"SMALLINT",
		"INTEGER",
		"BIGINT",
		"FLOAT",
		"REAL",
		"DOUBLE",
		"NUMERIC",
		"DECIMAL",
		"CHAR",
		"VARCHAR",
		"LONGVARCHAR",
		"DATE",
		"TIME",
		"TIMESTAMP",
		"BINARY",
		"VARBINARY",
		"LONGVARBINARY",
		"OTHER"
	};

	//public static Connection conn;

	public static void main (String[] argv) throws Throwable
	{
   		ij.getPropertyArg(argv); 
        Connection conn = ij.startJBMS();

        runTests( conn);
    }

    public static void runTests( Connection conn) throws Throwable
    {        
		conn.setAutoCommit(false);	

		testMisc(conn);
		testNull(conn);
		testUpdate(conn);
		testEachOutputType(conn);
		testReturnTypes(conn);
		testOtherOutputType(conn);
		testManyOut(conn);
		test5116(conn);
	}

	private static void testMisc(Connection conn) throws Throwable
	{
		System.out.println("==============================================");
		System.out.println("TESTING BOUNDARY CONDITIONS");
		System.out.println("==============================================\n");

		Statement scp = conn.createStatement();

		scp.execute("CREATE PROCEDURE takesString(OUT P1 VARCHAR(40), IN P2 INT) " +
						"EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.outparams.takesString'" +
						" NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");

		CallableStatement cs = conn.prepareCall("call takesString(?,?)");

		// register a normal int as an output param, should fail
		boolean failed = false;
		try
		{
			cs.registerOutParameter(2, Types.INTEGER);
		}
		catch (SQLException se)
		{
			failed = true;
			System.out.println("Expected exception "+se);
		}
		if (!failed)
		{
			System.out.println("registerOutParameter on non-output didn't fail");
		}

		// invalid param number
		failed = false;
		try
		{
			cs.registerOutParameter(9, Types.INTEGER);
		}
		catch (SQLException se)
		{
			failed = true;
			System.out.println("Expected exception "+se);
		}
		if (!failed)
		{
			System.out.println("registerOutParameter on bad value didn't fail");
		}

		// invalid param number
		failed = false;
		try
		{
			cs.registerOutParameter(0, Types.INTEGER);
		}
		catch (SQLException se)
		{
			failed = true;
			System.out.println("Expected exception "+se);
		}
		if (!failed)
		{
			System.out.println("registerOutParameter on bad value didn't fail");
		}

		// set before register, bad type, should fail as is output parameter.	
		try
		{
			cs.setDouble(1, 1);
			System.out.println("FAIL setDouble() on takesString() accepted");
		}
		catch (SQLException se)
		{
			System.out.println("Expected exception "+se);
		}

		// set before register, should fail as is output parameter.
		try
		{
			cs.setString(1, "hello");
			System.out.println("FAIL setString() on takesString() accepted");
		}
		catch (SQLException se)
		{
			System.out.println("Expected exception "+se);
		}

		cs.registerOutParameter(1, Types.CHAR);
		cs.setInt(2, Types.INTEGER);
		try
		{
			cs.execute();
		}
		catch (SQLException se)
		{
			System.out.println("cs.execute() got unexpected exception: "+se);
		}

		// shouldn't have to reregister the type, and shouldn't
		// need to set the output parameters
		cs.clearParameters();
		cs.setInt(2, Types.INTEGER);
		try
		{
			cs.execute();
		}
		catch (SQLException se)
		{
			System.out.println("cs.execute() got unexpected exception: "+se);
		}
		cs.close();
		scp.execute("DROP PROCEDURE takesString");

		scp.execute("CREATE FUNCTION returnsBigDecimal(P2 INT) RETURNS DECIMAL(10,2) " +
						"EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.outparams.returnsBigDecimal'" +
						" NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
		// return output params -- cannot do set on return output param
		cs = conn.prepareCall("? = call returnsBigDecimal(?)");
		try
		{
			cs.setBigDecimal(1, new BigDecimal(1d));
			System.out.println("ERROR: setBigDecimal() on return output parameter succeeded");
		}
		catch (SQLException se)
		{
			System.out.println("Expected exception on setBigDecimal() on a return output param: "+se);
		}
		cs.close();
		scp.execute("DROP FUNCTION returnsBigDecimal");

		// lets try ? = call syntax on a call that doesn't return anything
		
		scp.execute("CREATE PROCEDURE returnsNothing() " +
						"EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.outparams.returnsNothing'" +
						" NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
		try
		{
			cs = conn.prepareCall("? = call returnsNothing()");
			System.out.println("ERROR: no exception on prepare of '? = call returnsNothing()");
		}
		catch (SQLException se)
		{
			System.out.println("Expected exception on prepare of '? = call returnsNothing()': "+se);
		}
		scp.execute("DROP PROCEDURE returnsNothing");
	}

	
	private static void testNull(Connection conn) throws Throwable
	{
		System.out.println("==============================================");
		System.out.println("TESTING NULLS");
		System.out.println("==============================================\n");
		System.out.println("Test for bug 4317, passing null value for a parameter");

		Statement scp = conn.createStatement();

		scp.execute("CREATE PROCEDURE testNullBug4317(IN P1 VARCHAR(10)) " +
						"EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.outparams.testNullBug4317'" +
						" NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");


		CallableStatement cs0 = conn.prepareCall("call testNullBug4317(?)");
		try
		{
			cs0.setString(1, null);		// passing in null
			cs0.execute();
		}
		catch (SQLException se)
		{
			System.out.println("cs0.execute() got unexpected exception: "+se);
		}

		try
		{
			// BUG 5928 - setNull throws an exception - fixed.
			cs0.setNull(1, java.sql.Types.VARCHAR);		// passing in null
			cs0.execute();
		}
		catch (SQLException se)
		{
			System.out.println("cs0.execute() got unexpected exception: "+se);
		}
		cs0.close();
		scp.execute("DROP PROCEDURE testNullBug4317");


	}

	// test: do we get an appropriate update count when using ?=call?
	private static void testUpdate(Connection conn) throws Throwable
	{
		System.out.println("==============================================");
		System.out.println("TESTING UPDATE COUNT");
		System.out.println("==============================================\n");

		Statement scp = conn.createStatement();

		scp.execute("CREATE FUNCTION returnsIntegerP(P1 INT) RETURNS INTEGER " +
						"EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.outparams.returnsIntegerP'" +
						" NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");



		CallableStatement cs = conn.prepareCall("? = call returnsIntegerP(0)");
		cs.registerOutParameter(1, Types.INTEGER);
		try
		{
			int updCount = cs.executeUpdate();
			System.out.println("executeUpdate on ? = call returnsIntegerP returned "+updCount);
			System.out.println("getString(1) returned "+cs.getString(1));
		}
		catch (SQLException se)
		{
			System.out.println("cs.execute() got unexpected exception: "+se);
		}

		cs.close();
		scp.execute("DROP FUNCTION returnsIntegerP");
		scp.close();
	}

	// should do get setString() to use a string that is appropriate for
	//	the target type
	private static void testEachOutputType(Connection conn) throws Throwable
	{
		System.out.println("==============================================");
		System.out.println("TESTING NORMAL OUTPUT PARAMETERS");
		System.out.println("==============================================\n");
		CallableStatement cs = null;

		for (int doSetObject = 0; doSetObject < 3; doSetObject++)
		{
			switch (doSetObject)
			{
				case 0:
					System.out.println("...starting doing setXXX for each type xxx");
					break;
				case 1:
					System.out.println("...now doing setObject on each type xxx");
					break;
				case 2:
					System.out.println("...not doing any setXXX, just OUT parameters, not IN/OUT");
					break;
			}

			for (int method = 0; method < outputMethods.length; method++)
			{
				String methodName = outputMethods[method];
				if (methodName == null)
					continue;

				System.out.println("\n------------------------------------");

				Statement scp = conn.createStatement();
				String str;
				if (methodName.indexOf("Nothing") == -1)
				{

					scp.execute("CREATE PROCEDURE " + methodName + "(INOUT P1 " + outputProcParam[method] + ", IN P2 INT) " +
						"EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.outparams." + methodName +
						"' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");


					if (method%2 == 0)
						str = "call "+methodName+"(?,?)";
					else
						str = "{call "+methodName+"(?,?)}";
				}
				else
				{
					scp.execute("CREATE PROCEDURE " + methodName + "() " +
						"EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.outparams." + methodName +
						"' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
					str = "{call "+methodName+"()}";
				}


	
				System.out.println(str);
				try 
				{
					cs = conn.prepareCall(str);
				}
				catch (SQLException se)
				{
					System.out.println("ERROR: unexpected exception "+se);
					throw se;
				}
	
				for (int type = 0; type < types.length; type++)
				{
					cs.clearParameters();
					System.out.println();
					try
					{
						System.out.println("\n\tcs.registerOutParameter(1, "+typeNames[type]+")");
						cs.registerOutParameter(1, types[type]);
					} 
					catch (SQLException se)
					{
						System.out.println("\tException "+se);
						continue;