procedure.java

derby database source code.good for you.

		for (int i = 0; i < control.length; i++) {

			StringBuffer cp = new StringBuffer(256);
			cp.append("CREATE PROCEDURE SQLC.SQLCONTROL1_");
			cp.append(i);
			cp.append(" (OUT E1 VARCHAR(128), OUT E2 VARCHAR(128), OUT E3 VARCHAR(128), OUT E4 VARCHAR(128), OUT E5 VARCHAR(128), OUT E6 VARCHAR(128), OUT E7 VARCHAR(128)) ");
			cp.append(control[i]);
			cp.append(" PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.sqlControl'");

			String cpsql = cp.toString();
			System.out.println(cpsql);

			s.execute(cpsql);
			
			cp.setLength(0);
			cp.append("CREATE PROCEDURE SQLC.SQLCONTROL2_");
			cp.append(i);
			cp.append(" (OUT E1 VARCHAR(128), OUT E2 VARCHAR(128), OUT E3 VARCHAR(128), OUT E4 VARCHAR(128), OUT E5 VARCHAR(128), OUT E6 VARCHAR(128), OUT E7 VARCHAR(128)) ");
			cp.append(control[i]);
			cp.append(" PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.sqlControl2'");

			cpsql = cp.toString();
			System.out.println(cpsql);

			s.execute(cpsql);

			cp.setLength(0);
			cp.append("CREATE PROCEDURE SQLC.SQLCONTROL3_");
			cp.append(i);
			cp.append(" (OUT E1 VARCHAR(128), OUT E2 VARCHAR(128), OUT E3 VARCHAR(128), OUT E4 VARCHAR(128), OUT E5 VARCHAR(128), OUT E6 VARCHAR(128), OUT E7 VARCHAR(128)) ");
			cp.append(control[i]);
			cp.append(" PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.sqlControl3'");

			cpsql = cp.toString();
			System.out.println(cpsql);

			s.execute(cpsql);

			cp.setLength(0);
			cp.append("CREATE PROCEDURE SQLC.SQLCONTROL4_");
			cp.append(i);
			cp.append(" (IN SQLC INTEGER, OUT E1 VARCHAR(128), OUT E2 VARCHAR(128), OUT E3 VARCHAR(128), OUT E4 VARCHAR(128), OUT E5 VARCHAR(128), OUT E6 VARCHAR(128), OUT E7 VARCHAR(128), OUT E8 VARCHAR(128)) ");
			cp.append(control[i]);
			cp.append(" PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.sqlControl4'");

			cpsql = cp.toString();
			System.out.println(cpsql);

			s.execute(cpsql);
		}
		showMatchingProcedures(conn, "SQLCONTROL1_%");
		showMatchingProcedures(conn, "SQLCONTROL2_%");
		showMatchingProcedures(conn, "SQLCONTROL3_%");
		showMatchingProcedures(conn, "SQLCONTROL4_%");

		if (!conn.getAutoCommit())
			conn.commit();

		for (int i = 0; i < control.length; i++) {
			String type = control[i];
			if (type.length() == 0)
				type = "DEFAULT (MODIFIES SQL DATA)";

			System.out.println("** SQL ** " + type);
			for (int k = 1; k <=3; k++) {
				CallableStatement cs = conn.prepareCall("CALL SQLC.SQLCONTROL" + k + "_" + i + " (?, ?, ?, ?, ?, ?, ?)");
				for (int rop = 1; rop <=7 ; rop++) {
					cs.registerOutParameter(rop, Types.VARCHAR);
				}
				cs.execute();
				for (int p = 1; p <= 7; p++) {
					System.out.println("    " + cs.getString(p));
				}
				cs.close();
			}
			
		}

		// test procedures that call others, e.g. to ensure that within a READS SQL DATA procedure, a MODIFIES SQL DATA cannot be called.
		// table was dropped by previous executions.
		s.execute("CREATE TABLE SQLC.SQLCONTROL_DML(I INT)");
		s.execute("INSERT INTO SQLC.SQLCONTROL_DML VALUES 4");
		for (int i = 0; i < control.length; i++) {
			String type = control[i];
			if (type.length() == 0)
				type = "DEFAULT (MODIFIES SQL DATA)";

			System.out.println("CALL ** " + type);
			for (int t = 0; t < control.length; t++) {

				String ttype = control[t];
				if (ttype.length() == 0)
					ttype = "DEFAULT (MODIFIES SQL DATA)";
				System.out.println("    CALLLING " + ttype);
				CallableStatement cs = conn.prepareCall("CALL SQLC.SQLCONTROL4_" + i + " (?, ?, ?, ?, ?, ?, ?, ?, ?)");
				cs.setInt(1, t);
				for (int rop = 2; rop <=9 ; rop++) {
					cs.registerOutParameter(rop, Types.VARCHAR);
				}

				cs.execute();
				for (int p = 2; p <= 9; p++) {
					String so = cs.getString(p);
					if (so == null)
						continue;
					System.out.println("         " + so);
				}
				cs.close();
			}
		}
		// Make sure we throw proper error with network server 
		// if params are not registered
		testBug5280(conn);

		s.execute("DROP TABLE SQLC.SQLCONTROL_DML");

		for (int i = 0; i < control.length; i++) {
			s.execute("DROP PROCEDURE SQLCONTROL1_" + i);
			s.execute("DROP PROCEDURE SQLCONTROL2_" + i);
			s.execute("DROP PROCEDURE SQLCONTROL4_" + i);
		}
		s.execute("DROP TABLE SQLC.SQLCONTROL_DDL");
		s.execute("SET SCHEMA APP");
		s.execute("DROP SCHEMA SQLC RESTRICT");

		s.close();
	}

	private static void showMatchingProcedures(Connection conn, String procedureName) throws SQLException {
		// Until cs defaults to hold cursor we need to turn autocommit off 
		// while we do this because one metadata call will close the other's
		// cursor
		boolean saveAutoCommit = conn.getAutoCommit();
		conn.setAutoCommit(false);
		System.out.println("DEFINED PROCEDURES FOR " + procedureName);
		PreparedStatement ps = conn.prepareStatement("select schemaname, alias, CAST (((javaclassname || '.' ) || CAST (aliasinfo AS VARCHAR(1000))) AS VARCHAR(2000)) AS SIGNATURE " + 
								" from sys.sysaliases A, sys.sysschemas S where alias like ? and A.schemaid = S.schemaid ORDER BY 1,2,3");

		ps.setString(1, procedureName);

		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			System.out.println("  " + rs.getString(1) + "." + rs.getString(2) + " AS " + rs.getString(3));
		}
		rs.close();

		System.out.println("DATABASE METATDATA PROCEDURES FOR " + procedureName);
		DatabaseMetaData dmd = conn.getMetaData();

		rs = dmd.getProcedures(null, null, procedureName);
		// with jcc 2.1 for now this will fail on the second round, 
		// because the resultset gets closed when we do getProcedureColumns. 
		// thus, catch that gracefully...
		try {
		while (rs.next()) {
			String schema = rs.getString(2);
			String name = rs.getString(3);
			System.out.println("  " + schema + "." + name + " AS " + rs.getString(7) + " type " + TYPE(rs.getShort(8)));
			// get the column information.
			ResultSet rsc = dmd.getProcedureColumns(null, schema, name, null);
			while (rsc.next()) {
				System.out.println("    " + PARAMTYPE(rsc.getShort(5)) + " " + rsc.getString(4) +  " " + rsc.getString(7));
			}
			rsc.close();
		}
		rs.close();
		// restore previous autocommit mode
		conn.setAutoCommit(saveAutoCommit);
		} catch (SQLException sqle) {
			System.out.println("FAILure: ");
			sqle.printStackTrace();
		}

		System.out.println("------------");
	}

	static String TYPE(short type) {
		switch (type) {
		case DatabaseMetaData.procedureResultUnknown:
			return "procedureResultUnknown";
		case DatabaseMetaData.procedureNoResult:
			return "procedureNoResult";
		case DatabaseMetaData.procedureReturnsResult:
			return "procedureReturnsResult";
		default:
			return "??????";
		}

	}
	static String PARAMTYPE(short type) {
		switch (type) {
		case DatabaseMetaData.procedureColumnUnknown: return "procedureColumnUnknown";
		case DatabaseMetaData.procedureColumnIn: return "procedureColumnIn";
		case DatabaseMetaData.procedureColumnInOut: return "procedureColumnInOut";
		case DatabaseMetaData.procedureColumnOut: return "procedureColumnOut";
		case DatabaseMetaData.procedureColumnReturn : return "procedureColumnReturn";
		case DatabaseMetaData.procedureColumnResult : return "procedureColumnResult";
		default: return "???";
		}
	}

	private static void statementExceptionExpected(Statement s, String sql) {
		System.out.println(sql);
		try {
			s.execute(sql);
			System.out.println("FAIL - SQL expected to throw exception");
		} catch (SQLException sqle) {
			expectedException(sqle);
		}
	}
	private static void callExceptionExpected(Connection conn, String callSQL) throws SQLException {
		System.out.println(callSQL);
		try {
			CallableStatement cs = conn.prepareCall(callSQL);
			executeProcedure(cs);
			cs.close();
			System.out.println("FAIL - SQL expected to throw exception ");
		} catch (SQLException sqle) {
			expectedException(sqle);
		}
	}

	/* ****
	 * Beetle 5292 (for Network Server): Check for the return
	 * of LOB columns in a result set.
	 */

	private static void testResultSetsWithLobs(Connection conn) {

		Statement s = null;

		// Create objects.
		try {
			s = conn.createStatement();
			
			// Clob.
			s.execute("create table lobCheckOne (c clob(30))");
			s.execute("insert into lobCheckOne values (cast " +
					  "('yayorsomething' as clob(30)))");
			s.execute("insert into lobCheckOne values (cast " +
						  "('yayorsomething2' as clob(30)))");
			s.execute("create procedure clobproc () parameter style java " +
				"language java external name " +
					  "'org.apache.derbyTesting.functionTests.util.ProcedureTest.clobselect' " +
					  "dynamic result sets 3 reads sql data");
			// Blob.
			s.execute("create table lobCheckTwo (b blob(30))");
			s.execute("insert into lobCheckTwo values (cast " + "(" + 
					  TestUtil.stringToHexLiteral("101010001101") +
					  " as blob(30)))");
			s.execute("insert into lobCheckTwo values (cast " +
					  "(" +
					  TestUtil.stringToHexLiteral("101010001101") +
					  " as blob(30)))");
			s.execute("create procedure blobproc () parameter style java " +
				"language java external name " +
				"'org.apache.derbyTesting.functionTests.util.ProcedureTest.blobselect' " +
				"dynamic result sets 1 reads sql data");

		} catch (SQLException e) {
			System.out.println("FAIL: Couldn't create required objects:");
			e.printStackTrace();
		}

		// Run 5292 Tests.
		try {

			// Clobs.

			System.out.println("Stored Procedure w/ CLOB in result set.");
			CallableStatement cs = conn.prepareCall("CALL clobproc()");
			executeProcedure(cs);
			cs.close();
			
			// Blobs.

			System.out.println("Stored Procedure w/ BLOB in result set.");
			cs = conn.prepareCall("CALL blobproc()");
			executeProcedure(cs);
			cs.close();

		} catch (Exception e) {
			System.out.println("FAIL: Encountered exception:");
			e.printStackTrace();
		}

		try {
		// Clean up.
			s.execute("drop table lobCheckOne");
			s.execute("drop table lobCheckTwo");
			s.execute("drop procedure clobproc");
			s.execute("drop procedure blobproc");
			s.close();
		} catch (Exception e) {
			System.out.println("FAIL: Cleanup for lob result sets test:");
			e.printStackTrace();
		}

		return;

	}

    /**
     * This method is used to set up an environment which can be used to test 
     * DERBY-1002. It creates statements and closes them to provoke the client
     * driver to re-use sections which in turn will make the network server to
     * re-use statements and result sets. It does not test anything by itself.
     * It just sets up an environment where the statements used in this test 
     * will be re-used in later tests. It is called from methods 
     * 'jira_491_492' and 'testImplicitClose'. When the re-use was not happening 
     * correctly, 'jira_491_492' and 'testImplicitClose' were giving following 
     * errors:
     * 
     * 1. In the test for jira491, client expects a QRYDTA for the CNTQRY request. 
     * Instead, it recieves a QRYNOPRM reply because server closes the query 
     * wrongly.
     * 2. In testImplicitClose, the query is not supposed to be closed in case
     * of EXCSQLSTT commands. If re-use happens wrongly, server closes the query 
     * for EXCSQLSTT commands too.
     *   
     * @param conn Connection
     */
    private static void setupStatementReuse(Connection conn)
    							throws SQLException{
    	
    	Statement stmt = conn.createStatement();
		try {
			stmt.execute("drop table test_table_jira_1002");
		} catch (SQLException se) { }

		try {
			stmt.execute("drop procedure test_proc_jira_1002");
		} catch (SQLException se) { }

		stmt.execute("create table test_table_jira_1002(id int)");
		stmt.execute("insert into test_table_jira_1002 values(1) , (2)");

		//create a procedure which returns a result set
		stmt.execute("create procedure test_proc_jira_1002(name varchar(50)) " +
					"language java parameter style java external name " +
					"'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'" +
					"dynamic result sets 1");
    	
    	
		// Create a select statement to make currentDrdaRs.qryclsimp=CodePoint.QRYCLSIMP_YES
    	Statement st_opnqry = conn.createStatement();
		ResultSet rs_opnqry = st_opnqry.executeQuery("SELECT * FROM TEST_TABLE_JIR