procedure.java

derby database source code.good for you.

		// check that a procedure with dynamic result sets can not resolve to a method with no ResultSet argument.
		s.execute("create procedure irdrs(p1 int) dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.missingDynamicParameter' parameter style JAVA");
		callExceptionExpected(conn, "CALL IRDRS(?)");
		s.execute("drop procedure irdrs");

		// check that a procedure with dynamic result sets can not resolve to a method with an argument that is a ResultSet impl,
		s.execute("create procedure rsi(p1 int) dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.badDynamicParameter' parameter style JAVA");
		callExceptionExpected(conn, "CALL rsi(?)");
		s.execute("drop procedure rsi");

		// simple check for a no-arg method that has dynamic result sets but does not return any
		System.out.println("no dynamic result sets");
		s.execute("create procedure zadrs() dynamic result sets 4 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.zeroArgDynamicResult' parameter style  JAVA");
		CallableStatement zadrs = conn.prepareCall("CALL ZADRS()");
		executeProcedure(zadrs);
		zadrs.close();
		s.execute("drop procedure ZADRS");

		// return too many result sets
		System.out.println("Testing too many result sets");
		s.execute("create procedure way.toomany(p1 int, p2 int) READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows' parameter style  JAVA");
		CallableStatement toomany = conn.prepareCall("CALL way.toomany(?, ?)");
		toomany.setInt(1, 2);
		toomany.setInt(2, 6);
		System.out.println("... too many result sets");
		executeProcedure(toomany);

		System.out.println("... one additional closed result set");
		toomany.setInt(1, 2);
		toomany.setInt(2, 99); // will close the second result set.
		executeProcedure(toomany);

		toomany.close();
		s.execute("drop procedure way.toomany");

		testResultSetsWithLobs(conn);

		s.close();
		conn2.close();
	}


	private static void checkCommitWithMultipleResultSets(CallableStatement drs1, Connection conn2, String action) throws SQLException
	{
		Connection conn = drs1.getConnection();
    //Use reflection to set the holdability to false so that the test can run in jdk14 and lower jdks as well
    try {
				Method sh = conn.getClass().getMethod("setHoldability", CONN_PARAM);
				sh.invoke(conn, CONN_ARG);
    } catch (Exception e) {System.out.println("shouldn't get that error " + e.getMessage());}//for jdks prior to jdk14

		// check to see that the commit of the transaction happens at the correct time.
		// switch isolation levels to keep the locks around.
		int oldIsolation = conn.getTransactionIsolation();
		boolean oldAutoCommit = conn.getAutoCommit();
         
		if (action.equals("noautocommit"))
			conn.setAutoCommit(false);
		else
			conn.setAutoCommit(true);

		conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
		System.out.println("auto commit is " + conn.getAutoCommit());
		PreparedStatement psLocks = conn2.prepareStatement("select count(*) from new org.apache.derby.diag.LockTable() AS LT");

		showLocks(psLocks, "lock count before execution ");

		drs1.execute();

		showLocks(psLocks, "lock count after execution ");

		ResultSet rs = drs1.getResultSet();
		rs.next();
		showLocks(psLocks, "lock count after next on first rs ");

		boolean expectClosed = false;

		// execute another statement to ensure that the result sets close.
		if (action.equals("statement")) {
			System.out.println("executing statement to force auto commit on open CALL statement");

			conn.createStatement().executeQuery("values 1").next();
			expectClosed = true;
			showLocks(psLocks, "lock count after statement execution ");

			try {
				rs.next();
				System.out.println("FAIL - result set open in auto commit mode after another statement execution");
			} catch (SQLException sqle) {
				System.out.println("Expected - " + sqle.getMessage());
			}
		}



		boolean anyMore = drs1.getMoreResults();
		System.out.println("Is there a second result ? " + anyMore);
		showLocks(psLocks, "lock count after first getMoreResults() ");

		if (anyMore) {
		
			rs = drs1.getResultSet();
			try {
				rs.next();
				if (expectClosed)
					System.out.println("FAIL - result set open in auto commit mode after another statement execution");
			} catch (SQLException sqle) {
				if (expectClosed)
					System.out.println("Expected - " + sqle.getMessage());
				else
					throw sqle;
			}
			showLocks(psLocks, "lock count after next on second rs ");

			// should commit here since all results are closed
			boolean more = drs1.getMoreResults();
			System.out.println("more results (should be false) " + more);
			showLocks(psLocks, "lock count after second getMoreResults() ");

			conn.setTransactionIsolation(oldIsolation);
			conn.setAutoCommit(oldAutoCommit);
		}

		psLocks.close();
	}

	private static void showLocks(PreparedStatement psLocks, String where) throws SQLException {
		ResultSet locks = psLocks.executeQuery();
		locks.next();
		System.out.println(where + locks.getInt(1));
		locks.close();
	}

	private static void testParameterTypes(Connection conn) throws SQLException  {
		System.out.println("parameterTypes");
		Statement s = conn.createStatement();

		s.execute("create table PT1(A INTEGER not null primary key, B CHAR(10), C VARCHAR(20))"); 
		s.execute("create procedure PT1(IN a int, IN b char(10), c varchar(20)) parameter style java dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.parameter1' MODIFIES SQL DATA");
		showMatchingProcedures(conn, "PT1");

		CallableStatement pt1 = conn.prepareCall("CALL PT1(?, ?, ?)");

		pt1.setInt(1, 20);
		pt1.setString(2, "abc");
		pt1.setString(3, "efgh");
		executeProcedure(pt1);


		pt1.setInt(1, 30);
		pt1.setString(2, "abc   ");
		pt1.setString(3, "efgh  ");
		executeProcedure(pt1);

		pt1.setInt(1, 40);
		pt1.setString(2, "abc                                                                           ");
		pt1.setString(3, "efgh                                                                             ");
		executeProcedure(pt1);

		pt1.setInt(1, 50);
		pt1.setString(2, "0123456789X");
		pt1.setString(3, "efgh  ");
		executeProcedure(pt1);
		pt1.close();

		s.execute("DROP procedure PT1");

		s.execute("create procedure PT2(IN a int, IN b DECIMAL(4), c DECIMAL(7,3)) parameter style java dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.parameter2' MODIFIES SQL DATA");
		showMatchingProcedures(conn, "PT2");

		CallableStatement pt2 = conn.prepareCall("CALL PT2(?, ?, ?)");

		pt2.setInt(1, 60);
		pt2.setString(2, "34");
		pt2.setString(3, "54.1");
		executeProcedure(pt2);

		pt2.setInt(1, 70);
		pt2.setBigDecimal(2, new BigDecimal("831"));
		pt2.setBigDecimal(3, new BigDecimal("45.7"));
		executeProcedure(pt2);
		
		pt2.setInt(1, -1);
		pt2.setBigDecimal(2, new BigDecimal("10243"));
		pt2.setBigDecimal(3, null);
		try {
			executeProcedure(pt2);
			System.out.println("FAIL - too many digits in decimal value accepted");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}
		pt2.setInt(1, 80);
		pt2.setBigDecimal(2, new BigDecimal("993"));
		pt2.setBigDecimal(3, new BigDecimal("1234.5678"));
		executeProcedure(pt2);
		pt2.close();

		s.execute("DROP procedure PT2");
/*		
		s.execute("create procedure PTBOOL2(IN p_in BOOLEAN, INOUT p_inout BOOLEAN, OUT p_out BOOLEAN) parameter style java dynamic result sets 0 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.pBOOLEAN' NO SQL");
		showMatchingProcedures(conn, "PTBOOL%");

		{

		CallableStatement ptb = conn.prepareCall("CALL PTBOOL2(?, ?, ?)");
		ptb.registerOutParameter(2, Types.BIT); 
		ptb.registerOutParameter(3, Types.BIT);

		if (!isDerbyNet){ // bug 5437
		ptb.setObject(1, null);
		ptb.setObject(2, Boolean.FALSE);
		try {
			ptb.execute();
			System.out.println("FAIL NULL PASSED to  primitive");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}
		}

		ptb.setBoolean(1, true);
		ptb.setBoolean(2, false);
		ptb.execute();
		System.out.println("p_inout " + ptb.getObject(2) + " p_out " + ptb.getObject(3));
		ptb.setBoolean(2, false);
		ptb.execute();
		System.out.println("p_inout " + ptb.getBoolean(2) + " null?" + ptb.wasNull() + " p_out " + ptb.getBoolean(3) + " null?" + ptb.wasNull());
		ptb.close();
		}

		s.execute("DROP procedure PTBOOL2");

		s.execute("create procedure PTTINYINT2(IN p_in TINYINT, INOUT p_inout TINYINT, OUT p_out TINYINT) parameter style java dynamic result sets 0 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.pTINYINT' NO SQL");
		showMatchingProcedures(conn, "PTTINYINT%");


		CallableStatement ptti = conn.prepareCall("CALL PTTINYINT2(?, ?, ?)");
		ptti.registerOutParameter(2, Types.TINYINT); 
		ptti.registerOutParameter(3, Types.TINYINT);

		ptti.setNull(1, Types.TINYINT);
		ptti.setByte(2, (byte) 7);
		try {
			ptti.execute();
			System.out.println("FAIL NULL PASSED to  primitive");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}

		ptti.setByte(1, (byte) 4);
		ptti.setNull(2, Types.TINYINT);
		try {
			ptti.execute();
			System.out.println("FAIL NULL PASSED to  primitive");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}

		ptti.setByte(1, (byte) 6);
		ptti.setByte(2, (byte) 3);
		ptti.execute();
		System.out.println("p_inout " + ptti.getObject(2) + " p_out " + ptti.getObject(3));
		ptti.setByte(2, (byte) 3);
		ptti.execute();
		System.out.println("p_inout " + ptti.getByte(2) + " null?" + ptti.wasNull() + " p_out " + ptti.getByte(3) + " null?" + ptti.wasNull());
		ptti.close();


		s.execute("DROP procedure PTTINYINT2");

	*/	
		s.execute("create procedure PTSMALLINT2(IN p_in SMALLINT, INOUT p_inout SMALLINT, OUT p_out SMALLINT) parameter style java dynamic result sets 0 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.pSMALLINT' NO SQL");
		showMatchingProcedures(conn, "PTSMALLINT%");


		CallableStatement ptsi = conn.prepareCall("CALL PTSMALLINT2(?, ?, ?)");
		ptsi.registerOutParameter(2, Types.SMALLINT); 
		ptsi.registerOutParameter(3, Types.SMALLINT);

		ptsi.setNull(1, Types.SMALLINT);
		ptsi.setShort(2, (short) 7);
		try {
			ptsi.execute();
			System.out.println("FAIL NULL PASSED to  primitive");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: (" + sqle.getSQLState() + ") " + sqle.getMessage());
		}

		ptsi.setShort(1, (short) 4);
		ptsi.setNull(2, Types.SMALLINT);
		try {
			ptsi.execute();
			System.out.println("FAIL NULL PASSED to  primitive");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: (" + sqle.getSQLState() + ") " + sqle.getMessage());
		}

		ptsi.setShort(1, (short) 6);
		ptsi.setShort(2, (short) 3);
		ptsi.execute();
		System.out.println("p_inout " + ptsi.getObject(2) + " p_out " + ptsi.getObject(3));
		ptsi.setShort(2, (short) 3);
		ptsi.execute();
		System.out.println("p_inout " + ptsi.getByte(2) + " null?" + ptsi.wasNull() + " p_out " + ptsi.getByte(3) + " null?" + ptsi.wasNull());

		// with setObject . Beetle 5439
		ptsi.setObject(1, new Integer(6));
		ptsi.setObject(2, new Integer(3));
		
		ptsi.execute();
		System.out.println("p_inout " + ptsi.getByte(2) + " null?" + ptsi.wasNull() + " p_out " + ptsi.getByte(3) + " null?" + ptsi.wasNull());
		ptsi.close();
		
		s.execute("DROP procedure PTSMALLINT2");		
		s.execute("DROP TABLE PT1");

		s.close();

	}

	private static void testOutparams(Connection conn) throws SQLException  {


		System.out.println("outparams");

		Statement s = conn.createStatement();

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

		
		// check execute via a Statement fails for use of OUT parameter
		if (! isDerbyNet) { // bug 5263
		try {
			executeProcedure(s, "CALL OP1(?, ?)");
			System.out.println("FAIL execute succeeded on OUT 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 OUT parameter
		try {
			PreparedStatement ps = conn.prepareStatement("CALL OP1(?, ?)");
			System.out.println("FAIL prepare succeeded on OUT param with PreparedStatement");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}
		}