procedure.java

derby database source code.good for you.

		ir1.setInt(1, 1);
		executeProcedure(ir1);

		ir1.setInt(1,2);
		executeProcedure(ir1);
		try {
			ir1.execute();
			System.out.println("FAIL - duplicate key insertion through ir");
		} catch (SQLException sqle) {
			System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
		}

		ir1.setString(1, "3");
		executeProcedure(ir1);

		ir1.close();

		ir1 = conn.prepareCall("CALL APP.IR(?)");
		ir1.setInt(1, 7);
		executeProcedure(ir1);

		CallableStatement ir2 = conn.prepareCall("CALL IR2(?, ?)");

		ir2.setInt(1, 4);
		ir2.setInt(2, 4);
		executeProcedure(ir2);

		ir2.setInt(1, 5);
		ir2.setString(2, "ir2");
		executeProcedure(ir2);


		ir2.setInt(1, 6);
		ir2.setString(2, "'012345678990'");
		executeProcedure(ir2);

		ir1.close();
		ir2.close();

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



		ResultSet rs = s.executeQuery("select * from t1");
		org.apache.derby.tools.JDBCDisplayUtil.DisplayResults(System.out, rs, conn);

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

		callExceptionExpected(conn, "CALL IR2(2, 'no way')");
		callExceptionExpected(conn, "CALL IR2(?, 'no way')");
		callExceptionExpected(conn, "CALL IR2(2, ?)");

		s.execute("drop procedure IR");
		s.execute("drop procedure IR2");

		s.close();
	}
    // This test case provides tests for bugs DERBY-491 and DERBY-492. These
    // two bug reports describe different symptoms, but the underlying bug
    // is identical: the network server's implementation of LMTBLKPRC was
    // incorrectly manipulating DDMWriter's bytes buffer. Depending on the
    // details, the symptom of this bug was generally a hang, because the
    // server mistakenly truncated the unsent data in its network buffer and
    // hence sent only a partial transmission, causing the client to hang,
    // waiting for data that would never arrive. A more detailed analysis
    // of some other possible symptoms that could arise from these tests is
    // available in the bug notes for bug 491 in JIRA at:
    // http://issues.apache.org/jira/browse/DERBY-491
    //
    private static void jira_491_492(Connection conn)
        throws SQLException
    {
        Statement st = conn.createStatement();
        PreparedStatement pSt = null;

        // JIRA-491: Result set has a row that is approx 32K long.
        // When originally filed, this bug script caused  a protocol
        // exception and connection deallocation, but that was because the
        // bug script provoked both JIRA-614 *and* JIRA-491. If you have
        // the fix for JIRA-614, but JIRA-491 has regressed, you will hang.

        try {
            st.execute("drop table testtable1");
        } catch (SQLException se) {}

        // Create an array of chars to be used as the input parameter.
        // Note that the array should roughly 32K or larger.
        char [] cData = new char[32500];
        for (int i = 0; i < cData.length; i++)
            cData[i] = Character.forDigit(i%10, 10);

        try {
            st.execute("create table jira491 (int1 integer, varchar32k varchar(32500))");
            pSt=conn.prepareStatement("insert into jira491 values (?,?)");
            for (int i = 1; i <= 5; i++) {
                pSt.setInt(1, i);
                pSt.setString(2, new String(cData));
                pSt.execute();
            }
        } catch (SQLException se) {
            System.out.println("JIRA-491: FAILURE in data generation:");
            se.printStackTrace(System.out);
        }

        try {
            st.execute("drop procedure TEST_PROC_JIRA_491");
        } catch (SQLException se) {} // Ignore "proc does not exist" errors
	
        try {
            st.execute("create procedure TEST_PROC_JIRA_491(in i int) " +
						"language java parameter style java external name " +
						"'org.apache.derbyTesting.functionTests.util.ProcedureTest.BIG_COL_491' result sets 2");
        } catch (SQLException se) {
            System.out.println("JIRA-491: FAILURE in procedure creation:");
            se.printStackTrace(System.out);
        }

        // Call setupStatementReuse which will make the server to reuse an existing statement. 
        setupStatementReuse(conn);
        CallableStatement cSt = conn.prepareCall("call TEST_PROC_JIRA_491(?)");
        cSt.setInt(1, 3);
        try {
            cSt.execute();
            do {
                ResultSet rs = cSt.getResultSet();
                while (rs.next()) {
                    String s = rs.getString(2);
                }
            } while (cSt.getMoreResults());
            System.out.println("JIRA-491 Successful.");
        }
        catch (Exception e)
        {
            System.out.println("JIRA-491 FAILURE: Caught Exception:");
            e.printStackTrace(System.out);
        }
	
        // JIRA-492: Result set has hundreds of columns.
        // This test case, when originally filed, exposed several problems:
        // - first, this test case causes the server to respond with a very
        // long response message which gets handled using DRDA Layer B DSS
        // segmentation. This long message was corrupted due to bug DERBY-125.
        // - then, the test case causes the server to perform LMTBLKPRC
        // message truncation in a situation in which there are multiple
        // chained messages in the DDMWriter buffer. Due to bug DERBY-491/2,
        // the message truncation logic truncated not only the last DSS block,
        // but also the multi-segment long message which was still sitting
        // unsent in the buffer.This then caused a HANG in the client, which
        // waited forever for the never-to-be-sent truncated data.

        try {
            st.execute("drop table jira492");
        } catch (SQLException se) {}

        try {
            st.execute("create table jira492 (id integer, nsi smallint, " +
                "ni integer, nbi DECIMAL(19,0), nd decimal(7,2), nr real, " +
                "ndo double)");
            st.execute("insert into jira492 values (" +
						"1, 2, 3, 4.5, 6.7, 8.9, 10.11)");
        } catch (SQLException se) {
            System.out.println("JIRA-492: FAILURE in data setup:");
            se.printStackTrace(System.out);
        }
	
        try {
            st.execute("drop procedure TEST_PROC_JIRA_492");
        } catch (SQLException se) {}
	
        try {
            st.execute("create procedure TEST_PROC_JIRA_492() " +
                    "language java parameter style java external name " +
                    "'org.apache.derbyTesting.functionTests.util.ProcedureTest.LOTS_O_COLS_492' result sets 1");
        } catch (SQLException se) {
            System.out.println("JIRA-492: FAILURE in procedure creation:");
            se.printStackTrace(System.out);
        }
	
        cSt = conn.prepareCall("call TEST_PROC_JIRA_492()");
        cSt.execute();
        System.out.println("JIRA-492 successful -- no hang!");
    }

	private static void executeProcedure(Statement s, String sql) throws SQLException {
		boolean firstResultIsAResultSet = s.execute(sql);

		procedureResults(s, firstResultIsAResultSet);
	}


	private static void executeProcedure(PreparedStatement ps) throws SQLException {
		boolean firstResultIsAResultSet = ps.execute();

		procedureResults(ps, firstResultIsAResultSet);
	}


	private static void procedureResults(Statement ps, boolean firstResultIsAResultSet) throws SQLException {

		org.apache.derby.tools.JDBCDisplayUtil.ShowWarnings(System.out, ps);

		boolean sawOneResult = false;
		boolean isFirst = true;
		do {

			boolean gotResult = false;

			ResultSet rs = ps.getResultSet();
			int updateCount = ps.getUpdateCount();
			if (rs == null) {

				if (isFirst && firstResultIsAResultSet) {
					System.out.println("FAIL - execute() indicated first result was a result set but getResultSet() returned null");
				}

				if (updateCount != -1) {
					gotResult = true;
					sawOneResult = true;
					System.out.println("UPDATE COUNT " + updateCount);
				}
			}
			else {

				if (updateCount != -1)
					System.out.println("FAIL - HAVE RESULT SET AND UPDATE COUNT OF " + updateCount);
				org.apache.derby.tools.JDBCDisplayUtil.DisplayResults(System.out, rs, ps.getConnection());
				gotResult = true;
				sawOneResult = true;
			}

			// if we did not get a result and this is not the first result then
			// there is a bug since the getMoreResults() returned true.
			//
			// This may also be an error on the first pass but maybe it's
			// ok to have no results at all?
			if (!gotResult && !isFirst) {
				System.out.println("FAIL - getMoreResults indicated more results but none was found");
			}

			isFirst = false;

		} while (ps.getMoreResults());
		SQLWarning warnings = ps.getWarnings();
		if (warnings != null)
			System.out.println("SQLWarning :" + warnings.getMessage());

		if (!sawOneResult)
			System.out.println("No ResultSet or update count returned");
	}

	/**
		1. basic testing
		2. correct auto commit logic
		3. correct holdability (JDBC 3)
	*/
	private static void dynamicResultSets(Connection conn, Connection conn2) throws SQLException {

		System.out.println("dynamicResultSets - parameter style JAVA");

		Statement s = conn.createStatement();

		statementExceptionExpected(s, "create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets -1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'");

		s.execute("create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'");

		showMatchingProcedures(conn, "DRS");

		callExceptionExpected(conn, "CALL DRS()");
		callExceptionExpected(conn, "CALL DRS(?,?)");

		CallableStatement drs1 = conn.prepareCall("CALL DRS(?)");

		drs1.setInt(1, 3);
		executeProcedure(drs1);
		drs1.close();

		s.execute("create procedure DRS2(p1 int, p2 int) parameter style JAVA READS SQL DATA dynamic result sets 2 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'");
		showMatchingProcedures(conn, "DRS2");

		drs1 = conn.prepareCall("CALL DRS2(?, ?)");
		drs1.setInt(1, 2);
		drs1.setInt(2, 6);
		executeProcedure(drs1);
			
		// execute it returning one closed result set
		drs1.setInt(1, 2);
		drs1.setInt(2, 99); // will close the second result set
		executeProcedure(drs1);

		// execute it returning no result sets
		if (! isDerbyNet)
		{
			//RESOLVE there appears to be a JCC Bug when returning no 
			// resultSets.
			drs1.setInt(1, 2);
			drs1.setInt(2, 199); // return no results at all
			executeProcedure(drs1);
		}
		// execute it returning two result sets but with the order swapped in the parameters
		// doesnot affect display order.
		drs1.setInt(1, 2);
		drs1.setInt(2, 299); // swap results
		executeProcedure(drs1);
		
		if (!isDerbyNet)
		{
		// execute it returning two result sets, and check to see the result set is closed after getMoreResults.
		drs1.setInt(1, 2);
		drs1.setInt(2, 2);
		drs1.execute();
		ResultSet lastResultSet = null;
		int pass = 1;
		do {

			if (lastResultSet != null) {
				try {
					lastResultSet.next();
					System.out.println("FAILED - result set should be closed");
				} catch (SQLException sqle) {
					System.out.println("EXPECTED : " + sqle.getMessage());
				}
			}

			lastResultSet = drs1.getResultSet();
			System.out.println("pass " + (pass++) + " got result set " + (lastResultSet != null));

		} while (drs1.getMoreResults() || lastResultSet != null);

		checkCommitWithMultipleResultSets(drs1, conn2, "autocommit");
		checkCommitWithMultipleResultSets(drs1, conn2, "noautocommit");
		checkCommitWithMultipleResultSets(drs1, conn2, "statement");
		}




		drs1.close();

		// use escape syntax
		drs1 = conn.prepareCall("{call DRS2(?, ?)}");
		drs1.setInt(1, 2);
		drs1.setInt(2, 6);
		executeProcedure(drs1);
		drs1.close();