procedure.java

derby database source code.good for you.

		CallableStatement op = conn.prepareCall("CALL OP1(?, ?)");

		op.registerOutParameter(1, Types.INTEGER);
		op.setInt(2, 7);

		executeProcedure(op);

		System.out.println("OP1 " + op.getInt(1) + " null ? " + op.wasNull());

		op.close();

		s.execute("create procedure OP2(INOUT a int, IN b int) parameter style java language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.inoutparams2'");
		showMatchingProcedures(conn, "OP2");

		// check execute via a Statement fails for use of INOUT parameter
		if (!isDerbyNet) { // bug 5263
		try {
			executeProcedure(s, "CALL OP2(?, ?)");
			System.out.println("FAIL execute succeeded on INOUT param with Statement");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}
		}

		if (! isDerbyNet) { // bug 5276

		// check execute via a PreparedStatement fails for use of INOUT parameter
		try {
			PreparedStatement ps = conn.prepareStatement("CALL OP2(?, ?)");
			System.out.println("FAIL prepare succeeded on INOUT param with PreparedStatement");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}
		}

		op = conn.prepareCall("CALL OP2(?, ?)");

		op.registerOutParameter(1, Types.INTEGER);
		op.setInt(1, 3);
		op.setInt(2, 7);

		executeProcedure(op);
		System.out.println("OP2 " + op.getInt(1) + " null ? " + op.wasNull());
		op.close();

		// INOUT & OUT procedures with variable length
		s.execute("create procedure OP3(INOUT a CHAR(10), IN b int) parameter style java language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.inoutparams3'");
		showMatchingProcedures(conn, "OP3");

		op = conn.prepareCall("CALL OP3(?, ?)");

		op.registerOutParameter(1, Types.CHAR);
		op.setString(1, "dan");
		op.setInt(2, 8);

		executeProcedure(op);
		System.out.println("OP3 >" + op.getString(1) + "< null ? " + op.wasNull());
		op.close();

		// INOUT & OUT DECIMAL procedures with variable length
		s.execute("create procedure OP4(OUT a DECIMAL(4,2), IN b VARCHAR(255)) parameter style java language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.inoutparams4'");
		showMatchingProcedures(conn, "OP4");

		op = conn.prepareCall("CALL OP4(?, ?)");

		op.registerOutParameter(1, Types.DECIMAL);
		op.setString(2, null);
		executeProcedure(op);
		System.out.println("OP4 null >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());

		op.setString(2, "14");
		executeProcedure(op);
		System.out.println("OP4 14 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());

		op.setString(2, "11.3");
		executeProcedure(op);
		System.out.println("OP4 11.3 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());

		op.setString(2, "39.345");
		executeProcedure(op);
		System.out.println("OP4 39.345 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());

		op.setString(2, "83");
		try {
			executeProcedure(op);
			System.out.println("FAIL - execution ok on out of range out parameter");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}

		if (!isDerbyNet) {
		// Bug 5316 - JCC clears registration with  clearParameters()
		op.clearParameters();
		try {
			// b not set
			executeProcedure(op);
			System.out.println("FAIL - b not set");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}

		// try to set an OUT param
		try {
			op.setBigDecimal(1, new BigDecimal("22.32"));
			System.out.println("FAIL - set OUT param to value");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}

		try {
			op.setBigDecimal(1, null);
			System.out.println("FAIL - set OUT param to null value");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}
		try {
			op.setNull(1, Types.DECIMAL);
			System.out.println("FAIL - set OUT param to null");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}
		}

		// can we get an IN param?
		op.setString(2, "49.345");
		executeProcedure(op);
		System.out.println("OP4 49.345 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());
		try {
			System.out.println("FAIL OP4 GET 49.345 >" + op.getString(2) + "< null ? " + op.wasNull());
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}
		op.close();

		// check to see that a registration is required first for the out parameter.
		op = conn.prepareCall("CALL OP4(?, ?)");
		op.setString(2, "14");
		try {
			executeProcedure(op);
			System.out.println("FAIL - execute succeeded without registration of out parameter");
		} catch (SQLException sqle) {
			expectedException(sqle);
		}
		op.close();

		s.execute("create procedure OP4INOUT(INOUT a DECIMAL(4,2), IN b VARCHAR(255)) parameter style java language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.inoutparams4'");
		showMatchingProcedures(conn, "OP4INOUT");

		// bug 5264 - first execution fails with parameter not set.

		op = conn.prepareCall("CALL OP4INOUT(?, ?)");
		op.registerOutParameter(1, Types.DECIMAL);

		op.setString(2, null);


		op.setBigDecimal(1, null);
		executeProcedure(op);
		System.out.println("OP4INOUT null >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());


		op.setBigDecimal(1, new BigDecimal("99"));
		executeProcedure(op);
		System.out.println("OP4INOUT null(2) >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());

		op.setString(2, "23.5");
		op.setBigDecimal(1, new BigDecimal("14"));
		executeProcedure(op);
		System.out.println("OP4INOUT 14+23.5 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());
		
		op.setString(2, "23.505");
		op.setBigDecimal(1, new BigDecimal("9"));
		executeProcedure(op);
		System.out.println("OP4INOUT 9+23.505 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());

		if (! isDerbyNet) { // with the network server it retains its old value of 9 
		// repeat execution. INOUT parameter now has the value 32.50
		executeProcedure(op);
		System.out.println("OP4INOUT 32.50+23.505 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());

		} // end bug 5264

		op.setString(2, "67.99");
		op.setBigDecimal(1, new BigDecimal("32.01"));
		try {
			executeProcedure(op);
			System.out.println("FAIL OP4INOUT 32.01+67.99 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}

		op.setString(2, "1");
		op.setBigDecimal(1, new BigDecimal("102.33"));
		try {
			executeProcedure(op);
			System.out.println("FAIL OP4INOUT 1+102.33 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}

		if (! isDerbyNet) {
		// now some checks to requirements for parameter setting.
		op.clearParameters();
		try {
			// a,b not set
			executeProcedure(op);
			System.out.println("FAIL - a,b not set");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}

		op.setString(2, "2");
		try {
			// a not set
			executeProcedure(op);
			System.out.println("FAIL - a  not set");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}

		op.clearParameters();
		op.setBigDecimal(1, new BigDecimal("33"));
		try {
			// b not set
			executeProcedure(op);
			System.out.println("FAIL - b  not set");
		} catch (SQLException sqle) {
			expectedException(sqle);
		}

		} // end bug 5264


		op.close();

		op = conn.prepareCall("CALL OP4INOUT(?, ?)");
		op.setString(2, "14");
		try {
			executeProcedure(op);
			System.out.println("FAIL - execute succeeded without registration of INOUT parameter");
		} catch (SQLException sqle) {
			expectedException(sqle);
		}
		op.close();

		s.execute("DROP PROCEDURE OP1");
		s.execute("DROP PROCEDURE OP2");
		s.execute("DROP PROCEDURE OP3");
		s.execute("DROP PROCEDURE OP4");
		s.execute("DROP PROCEDURE OP4INOUT");
		s.close();

	}

	private static final String[] LITERALS = 
	{"12" /* INTEGER */, "23.43e1" /* DOUBLE */, "176.3" /* DECIMAL */, "'12.34'" /* VARCHAR */};
	private static final String[] LIT_PROC_TYPES = 
	{"SMALLINT", "INTEGER", "BIGINT", "REAL", "DOUBLE", "DECIMAL", "CHAR", "VARCHAR"};
	private static void testLiterals(Connection conn) throws SQLException  {


		System.out.println("literals");

		Statement s = conn.createStatement();

		s.execute("CREATE PROCEDURE LITT.TY_SMALLINT(IN P1 SMALLINT, OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
		s.execute("CREATE PROCEDURE LITT.TY_INTEGER(IN P1 INTEGER, OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
		s.execute("CREATE PROCEDURE LITT.TY_BIGINT(IN P1 BIGINT, OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
		s.execute("CREATE PROCEDURE LITT.TY_REAL(IN P1 REAL, OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
		s.execute("CREATE PROCEDURE LITT.TY_DOUBLE(IN P1 DOUBLE, OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
		s.execute("CREATE PROCEDURE LITT.TY_DECIMAL(IN P1 DECIMAL(5,2), OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
		s.execute("CREATE PROCEDURE LITT.TY_CHAR(IN P1 CHAR(10), OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
		s.execute("CREATE PROCEDURE LITT.TY_VARCHAR(IN P1 VARCHAR(10), OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");

		showMatchingProcedures(conn, "TY_%");

		for (int t = 0; t < LIT_PROC_TYPES.length; t++) {

			String type = LIT_PROC_TYPES[t];

			String sql = "CALL LITT.TY_" + type + " (null, ?)";
			System.out.print(sql);

			try {
				CallableStatement cs = conn.prepareCall(sql);
				cs.registerOutParameter(1, Types.VARCHAR);
				cs.execute();
				String val = cs.getString(1);
				cs.close();
				System.out.println("=" + (val == null ? "<NULL>" : val));
			} catch (SQLException sqle) {
				System.out.println(" (" + sqle.getSQLState() + ") " + sqle.getMessage());
				// more code should be added to check on assignments
				// for now, commenting out the print of the stack, to prevent 
				// failures due to differences between jvms.
				// sqle.printStackTrace(System.out);
			}
		}

		for (int l = 0; l < LITERALS.length; l++) {
			String literal = LITERALS[l];
			for (int t = 0; t < LIT_PROC_TYPES.length; t++) {

				String type = LIT_PROC_TYPES[t];

				String sql = "CALL LITT.TY_" + type + " (" + literal + ", ?)";
				System.out.print(sql);

				try {
					CallableStatement cs = conn.prepareCall(sql);
					cs.registerOutParameter(1, Types.VARCHAR);
					cs.execute();
					String val = cs.getString(1);
					cs.close();
					System.out.println("=" + (val == null ? "<NULL>" : val));
				} catch (SQLException sqle) {
					System.out.println(" (" + sqle.getSQLState() + ") " + sqle.getMessage());
					// code should be added to show the expected errors, now commenting 
					// out the stack print to prevent false failures with different jvms
					//sqle.printStackTrace(System.out);
				}
			}
		}
	}



	private static void expectedException(SQLException sqle) {
		String sqlState = sqle.getSQLState();
		if (sqlState == null) {
			sqlState = "<NULL>";
		}
		System.out.println("EXPECTED SQL Exception: (" + sqlState + ") " + sqle.getMessage());
	}
	
	private static void testSQLControl(Connection conn) throws SQLException  {


		System.out.println("SQL Control");


		Statement s = conn.createStatement();

		s.execute("CREATE SCHEMA SQLC");
		s.execute("CREATE TABLE SQLC.SQLCONTROL_DML(I INT)");
		s.execute("INSERT INTO SQLC.SQLCONTROL_DML VALUES 4");

		String[] control = {"", "NO SQL", "CONTAINS SQL", "READS SQL DATA", "MODIFIES SQL DATA"};