procedure.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.harness.procedure

   Copyright 2003, 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 org.apache.derbyTesting.functionTests.util.TestUtil;
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;

import java.lang.reflect.*;

import org.apache.derbyTesting.functionTests.tests.jdbcapi.parameterMetaDataJdbc30;

public class procedure
{

  private static Class[] CONN_PARAM = { Integer.TYPE };
  private static Object[] CONN_ARG = { new Integer(JDBC30Translation.CLOSE_CURSORS_AT_COMMIT)};

	static private boolean isDerbyNet = false;

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

		// DB2 !!
		// com.ibm.db2.jcc.DB2DataSource ds = new com.ibm.db2.jcc.DB2DataSource();

		// ds.setDatabaseName("testdb");

		// ds.setServerName("localhost");
		//ds.setPortNumber(1527);
		// ds.setDriverType(4);

		 // Connection conn = ds.getConnection("db2admin", "password");

		//Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
		//Connection conn = DriverManager.getConnection("jdbc:db2:testdb", "USER", "XXXXX");


        runTests( conn);
    }

    public static void runTests( Connection conn) throws Throwable
    {
		try {
			testNegative(conn);
			testDelayedClassChecking(conn);
			testDuplicates(conn);
			ambigiousMethods(conn);
			zeroArgProcedures(conn);
			sqlProcedures(conn);
			dynamicResultSets(conn, ij.startJBMS());

			testParameterTypes(conn);
			testOutparams(conn);

			testSQLControl(conn);

				testLiterals(conn);
                        jira_491_492(conn);
		} catch (SQLException sqle) {
			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
			sqle.printStackTrace(System.out);
		}
		
	}

	public static void testNegative(Connection conn) throws SQLException {

		System.out.println("testNegative");

		Statement s = conn.createStatement();

		// no '.' in path/method
		statementExceptionExpected(s, "create procedure asdf() language java external name 'asdfasdf' parameter style java");

		// trailing '.'
		statementExceptionExpected(s, "create procedure asdf() language java external name 'asdfasdf.' parameter style java");

		// procedure name too long
		statementExceptionExpected(s, "create procedure a23456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789() language java external name 'asdf.asdf' parameter style java");

		// -- missing parens on procedure name
		statementExceptionExpected(s, "create procedure asdf language java external name java.lang.Thread.currentThread parameter style java");

		// -- incorrect language, (almost) straight from DB2 docs 

		statementExceptionExpected(s, "CREATE PROCEDURE ASSEMBLY_PARTS (IN ASSEMBLY_NUM INTEGER, OUT NUM_PARTS INTEGER, OUT COST DOUBLE) EXTERNAL NAME 'parts!assembly' DYNAMIC RESULT SETS 1 LANGUAGE C PARAMETER STYLE GENERAL");

		// invalid schema 
		statementExceptionExpected(s, "create procedure sys.proc1() language java external name 'java.lang.System.gc' parameter style java");

		// repeated elements
		statementExceptionExpected(s, "create procedure noclass() language java external name 'asdf.asdf' parameter style java language java");
		statementExceptionExpected(s, "create procedure noclass() parameter style java language java external name 'asdf.asdf' parameter style java");
		statementExceptionExpected(s, "create procedure noclass() external name 'asdf.xxxx' language java external name 'asdf.asdf' parameter style java");
		statementExceptionExpected(s, "create procedure noclass() parameter style java language java external name 'asdf.asdf' parameter style derby_rs_collection");

		// missing elements
		statementExceptionExpected(s, "create procedure missing01()");
		statementExceptionExpected(s, "create procedure missing02() language java");
		statementExceptionExpected(s, "create procedure missing03() language java parameter style java");
		statementExceptionExpected(s, "create procedure missing04() language java external name 'foo.bar'");
		statementExceptionExpected(s, "create procedure missing05() parameter style java");
		statementExceptionExpected(s, "create procedure missing06() parameter style java external name 'foo.bar'");
		statementExceptionExpected(s, "create procedure missing07() external name 'goo.bar'");
		statementExceptionExpected(s, "create procedure missing08() dynamic result sets 1");
		//statementExceptionExpected(s, "create procedure missing09() specific name fred");


		// no BLOB/CLOB/ long parameters
		statementExceptionExpected(s, "create procedure NO_BLOB(IN P1 BLOB(3k)) language java parameter style java external name 'no.blob'");
		statementExceptionExpected(s, "create procedure NO_CLOB(IN P1 CLOB(3k)) language java parameter style java external name 'no.clob'");
		statementExceptionExpected(s, "create procedure NO_LVC(IN P1 LONG VARCHAR) language java parameter style java external name 'no.lvc'");

		// duplicate names
		statementExceptionExpected(s, "create procedure DUP_P1(IN FRED INT, OUT RON CHAR(10), IN FRED INT) language java parameter style java external name 'no.dup1'");
		statementExceptionExpected(s, "create procedure D2.DUP_P2(IN \"FreD\" INT, OUT RON CHAR(10), IN \"FreD\" INT) language java parameter style java external name 'no.dup2'");
		statementExceptionExpected(s, "create procedure D3.DUP_P3(IN \"FRED\" INT, OUT RON CHAR(10), IN fred INT) language java parameter style java external name 'no.dup3'");
		s.execute("create procedure DUP_POK(IN \"FreD\" INT, OUT RON CHAR(10), IN fred INT) language java parameter style java external name 'no.dupok'");
		s.execute("drop procedure DUP_POK");

		// procedure not found with explicit schema name
		statementExceptionExpected(s, "CALL APP.NSP(?, ?)");

		// bug 5760 - this caused a null pointer exception at one time.
		statementExceptionExpected(s, "call syscs_util.syscs_set_database_property(\"foo\", \"bar\")");
		
		// Derby-258 specific signatures with types not matching JDBC spec.
		System.out.println("signature mismatched types");
		s.execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_A(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.load(java.lang.String)'");
		statementExceptionExpected(s, "CALL APP.SIGNATURE_BUG_DERBY_258_A(4)");
		s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_A");

		// signature with wrong number of arguments, too many
		System.out.println("signature too many parameters");
		s.execute("CREATE FUNCTION SIGNATURE_BUG_DERBY_258_B(A INT) RETURNS VARCHAR(128) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.Integer.toString(int, int)'");
		statementExceptionExpected(s, "VALUES APP.SIGNATURE_BUG_DERBY_258_B(4)");
		s.execute("DROP FUNCTION SIGNATURE_BUG_DERBY_258_B");

		// and too few
		System.out.println("signature too few parameters");
		s.execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_C(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.gc()'");
		statementExceptionExpected(s, "CALL APP.SIGNATURE_BUG_DERBY_258_C(4)");
		s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_C");

		// only a leading paren
		System.out.println("signature invalid format");
		s.execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_F(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.gc('");
		statementExceptionExpected(s, "CALL APP.SIGNATURE_BUG_DERBY_258_F(4)");
		s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_F");

		// signature of (,,)
		System.out.println("signature invalid format");
		s.execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_G(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.gc(,,)'");
		statementExceptionExpected(s, "CALL APP.SIGNATURE_BUG_DERBY_258_G(4)");
		s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_G");

		// signature of (, ,)
		System.out.println("signature invalid format");
		s.execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_H(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.gc(, ,)'");
		statementExceptionExpected(s, "CALL APP.SIGNATURE_BUG_DERBY_258_H(4)");
		s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_H");

		// signature of (int,)
		System.out.println("signature invalid format");
		s.execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_I(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.gc(int ,)'");
		statementExceptionExpected(s, "CALL APP.SIGNATURE_BUG_DERBY_258_I(4)");
		s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_I");
		
		s.close();
		
	}
	
   
	public static void testBug5280(Connection conn) throws SQLException
	{
		String csString = "CALL SQLCONTROL3_0 (?, ?, ?, ?, ?, ?, ?)";
		// Bug 5280 If we don't register the outparams
		// we don't get an error with network server.
		//for (int p = 1; p <= 7; p++) {
		//	cs.registerOutParameter(p,Types.VARCHAR);
		//}
		callExceptionExpected(conn, csString);
	}

	public static void testDelayedClassChecking(Connection conn) throws SQLException {

		System.out.println("testDelayedClassChecking");


		Statement s = conn.createStatement();
		// -- procedures do not check if the class or method exists at create time.
		s.execute("create procedure noclass() language java external name 'asdf.asdf' parameter style java");
		s.execute("create procedure nomethod() language java external name 'java.lang.Integer.asdf' parameter style java");
		s.execute("create procedure notstatic() language java external name 'java.lang.Integer.equals' parameter style java");
		s.execute("create procedure notvoid() language java external name 'java.lang.Runtime.getRuntime' parameter style java");

		//  - but they are checked at runtime
		callExceptionExpected(conn, "call noclass()");
		callExceptionExpected(conn, "call nomethod()");
		callExceptionExpected(conn, "call notstatic()");
		callExceptionExpected(conn, "call notvoid()");

		// CHECK SYSALIAS
		s.execute("drop procedure noclass");
		s.execute("drop procedure nomethod");
		s.execute("drop procedure notstatic");
		s.execute("drop procedure notvoid");

		s.close();

	}

	public static void testDuplicates(Connection conn) throws SQLException {
		System.out.println("testDuplicates");


		Statement s = conn.createStatement();

		s.execute("create schema S1");
		s.execute("create schema S2");

		s.execute("create procedure PROCDUP() language java external name 'okAPP.ok0' parameter style java");
		s.execute("create procedure s1.PROCDUP() language java external name 'oks1.ok0' parameter style java");
		s.execute("create procedure s2.PROCDUP() language java external name 'oks2.ok0' parameter style java");

		statementExceptionExpected(s, "create procedure PROCDUP() language java external name 'failAPP.fail0' parameter style java");
		statementExceptionExpected(s, "create procedure s1.PROCDUP() language java external name 'fails1.fail0' parameter style java");
		statementExceptionExpected(s, "create procedure s2.PROCDUP() language java external name 'fails2.fail0' parameter style java");

		showMatchingProcedures(conn, "PROCDUP");

		statementExceptionExpected(s, "create procedure S1.NOTYET() SPECIFIC fred language java external name 'failAPP.fail0' parameter style java");
		
		s.execute("drop procedure s1.PROCDUP");
		s.execute("drop procedure s2.PROCDUP");

		s.execute("drop schema S1 RESTRICT");
		s.execute("drop schema S2 RESTRICT");
		s.close();


	}

	public static void ambigiousMethods(Connection conn) throws SQLException {
		System.out.println("ambigiousMethods");

		Statement s = conn.createStatement();

		// ambigious resolution - with result sets
		s.execute("create procedure ambigious01(p1 INTEGER, p2 CHAR(20)) dynamic result sets 1 language java parameter style java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.ambigious1'");
		callExceptionExpected(conn, "call AMBIGIOUS01(?, ?)");
		s.execute("drop procedure AMBIGIOUS01");

		// ambigious in defined parameters
		s.execute("create procedure ambigious02(p1 INTEGER, p2 INTEGER) dynamic result sets 1 language java parameter style java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.ambigious2'");
		callExceptionExpected(conn, "call AMBIGIOUS02(?, ?)");
		s.execute("drop procedure AMBIGIOUS02");

        // verify we can find it with a Java signature
        s.execute("create procedure ambigious03(p1 INTEGER, p2 INTEGER) dynamic result sets 1 language java parameter style java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.ambigious2(int,java.lang.Integer)'");
        executeProcedure(s, "{call ambigious03(1, NULL)}");
        s.execute("drop procedure AMBIGIOUS03");
        s.execute("create procedure ambigious04(p1 INTEGER, p2 INTEGER) dynamic result sets 1 language java parameter style java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.ambigious2(java.lang.Integer,int)'");
        executeProcedure(s, "{call ambigious04(NULL, 1)}");
        s.execute("drop procedure AMBIGIOUS04");
        s.close();
	}

	public static void zeroArgProcedures(Connection conn) throws SQLException {
		System.out.println("zeroArgProcedures");

		Statement s = conn.createStatement();
		s.execute("create procedure za() language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.zeroArg' parameter style java");

		executeProcedure(s, "call za()");
		PreparedStatement ps = conn.prepareStatement("call za()");
		executeProcedure(ps);
		ps.close();

		ps = conn.prepareStatement("{call za()}");
		executeProcedure(ps);
		ps.close();


		try {
			ps = conn.prepareStatement("call za(?)");
			System.out.println("FAIL - prepareStatement call za(?)");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}

		CallableStatement cs = conn.prepareCall("call za()");
		executeProcedure(cs);
		cs.close();

		cs = conn.prepareCall("{call za()}");
		executeProcedure(cs);
		cs.close();

		showMatchingProcedures(conn, "ZA");
		s.execute("drop procedure za");
		showMatchingProcedures(conn, "ZA");

		s.close();

	}

	private static void sqlProcedures(Connection conn) throws SQLException {

		System.out.println("sqlProcedures()");

		Statement s = conn.createStatement();

		s.execute("create table t1(i int not null primary key, b char(15))");
		s.execute("create procedure ir(p1 int) MODIFIES SQL DATA dynamic result sets 0 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.insertRow' parameter style java");
		s.execute("create procedure ir2(p1 int, p2 char(10)) language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.insertRow' MODIFIES SQL DATA parameter style java");

		showMatchingProcedures(conn, "IR%");

		callExceptionExpected(conn, "CALL IR()");

		CallableStatement ir1 = conn.prepareCall("CALL IR(?)");