proceduretest.java

derby database source code.good for you.

	}

	public static void inoutparams2(int[] p1, int p2) {

		p1[0] = p1[0] + (p2 * 2);
	}
	public static void inoutparams3(String[] p1, int p2) {

		if (p1[0] == null)
			System.out.println("p1 is NULL");
		else
			System.out.println("p1= >" + p1[0] + "< length " + p1[0].length());
		
		if (p2 == 8)
			p1[0] = "nad";
		else if (p2 == 9)
			p1[0] = null;
		else if (p2 == 10)
			p1[0] = "abcdefghijklmnopqrstuvwzyz";
	}
	public static void inoutparams4(java.math.BigDecimal[] p1, String p2) {
		if (p2 == null)
			p1[0] = null;
		else {
			if (p1[0] == null)
				p1[0] = new BigDecimal(p2).add(new BigDecimal("17"));
			else
				p1[0] = new BigDecimal(p2).add(p1[0]);
		}
	}

	public static void ambigious1(int p1, String p2, ResultSet[] data1, ResultSet[] data2) {}
	public static void ambigious1(int p1, String p2, ResultSet[] data1) {}


	public static void ambigious2(int p1, Integer p2) {
        System.out.println("ambigious2(int,Integer) called");
    };
	public static void ambigious2(Integer p1, int p2) {
        System.out.println("ambigious2(Integer,int) called");
    };

	public static void missingDynamicParameter(int p1)  {}
	public static void missingDynamicParameter(int p1, Object p2)  {}

	public static void badDynamicParameter(int p1, ProcedureTest[] data) {}

	public static void zeroArgDynamicResult(ResultSet[] data1, ResultSet[] data2, ResultSet[] data3, ResultSet[] data4) {
		System.out.println("zeroArgDynamicResult called");
	}


	public static void sqlControl(String[] e1, String[] e2, String[] e3, String[] e4, String[] e5, String[] e6, String[] e7)
		throws SQLException {

		Connection conn = DriverManager.getConnection("jdbc:default:connection");

		Statement s = conn.createStatement();

		executeStatement(s, "CREATE TABLE SQLCONTROL_DDL (I INT)", e1);
		executeStatement(s, "ALTER TABLE SQLC.SQLCONTROL_DML ADD COLUMN B INT DEFAULT NULL", e2);

		executeStatement(s, "INSERT INTO SQLC.SQLCONTROL_DML(I) VALUES (1)", e3);
		executeStatement(s, "UPDATE SQLC.SQLCONTROL_DML SET I = I + 11", e4);
		executeStatement(s, "SELECT * FROM SQLC.SQLCONTROL_DML", e5);
		executeStatement(s, "DELETE FROM SQLC.SQLCONTROL_DML", e6);

		executeStatement(s, "DROP TABLE SQLC.SQLCONTROL_DML", e7);

		conn.close();

	}
	public static void sqlControl2(String[] e1, String[] e2, String[] e3, String[] e4, String[] e5, String[] e6, String[] e7)
		throws SQLException {

		Connection conn = DriverManager.getConnection("jdbc:default:connection");

		Statement s = conn.createStatement();

		executeStatement(s, "CREATE VIEW SQLCONTROL_VIEW AS SELECT * FROM SQLC.SQLCONTROL_DML", e1);
		executeStatement(s, "DROP VIEW SQLCONTROL_VIEW", e2);

		executeStatement(s, "LOCK TABLE SQLC.SQLCONTROL_DML IN EXCLUSIVE MODE", e3);
		executeStatement(s, "VALUES 1,2,3", e4);
		executeStatement(s, "SET SCHEMA SQLC", e5);
		executeStatement(s, "CREATE SCHEMA SQLC_M", e6);
		executeStatement(s, "DROP SCHEMA SQLC_M RESTRICT", e7);

		conn.close();

	}
	public static void sqlControl3(String[] e1, String[] e2, String[] e3, String[] e4, String[] e5, String[] e6, String[] e7)
		throws SQLException {

		Connection conn = DriverManager.getConnection("jdbc:default:connection");

		Statement s = conn.createStatement();

		e1[0] = "IBM CS FEATURE";
		e2[0] = "IBM CS FEATURE";

		executeStatement(s, "SET ISOLATION CS", e3);
		executeStatement(s, "SET RUNTIMESTATISTICS OFF", e4);
		executeStatement(s, "SET STATISTICS TIMING OFF", e5);
		executeStatement(s, "VALUES 1", e6);

		executeStatement(s, "VALUES 1", e7);

		conn.close();

	}
	public static void sqlControl4(int sqlc, String[] e1, String[] e2, String[] e3, String[] e4, String[] e5, String[] e6, String[] e7, String[] e8)
		throws SQLException {

		Connection conn = DriverManager.getConnection("jdbc:default:connection");

		Statement s = conn.createStatement();

		String sql = "CALL SQLC.SQLCONTROL2_"+sqlc+" (?, ?, ?, ?, ?, ?, ?) ";

		e1[0] = sql;

		CallableStatement cs1 = conn.prepareCall(sql);
		try {
			for (int rop = 1; rop <= 7; rop++) {
				cs1.registerOutParameter(rop, Types.VARCHAR);
			}
			cs1.execute();

			e2[0] = cs1.getString(1);
			e3[0] = cs1.getString(2);
			e4[0] = cs1.getString(3);
			e5[0] = cs1.getString(4);
			e6[0] = cs1.getString(5);
			e7[0] = cs1.getString(6);
			e8[0] = cs1.getString(7);
		} catch (SQLException sqle) {
			StringBuffer sb = new StringBuffer(128);
			sb.append("STATE");
			do {
				sb.append("-");
				String ss = sqle.getSQLState();
				if (ss == null)
					ss= "?????";
				sb.append(ss);
				sqle = sqle.getNextException();
			} while (sqle != null);
			e2[0] = sb.toString();
		}

		cs1.close();

		conn.close();

	}
	private static void executeStatement(Statement s, String sql, String[] result) {

		StringBuffer sb = new StringBuffer(128);

		int len = sql.length();
		if (len > 15)
			len = 15;

		sb.append(sql.substring(0, len));
		try {
			if (s.execute(sql)) {
				ResultSet rs = s.getResultSet();
				while (rs.next())
					sb.append("- ROW(" + rs.getString(1) + ")");
				rs.close();
			} else {
				sb.append("-UPDATE " + s.getUpdateCount());
			}

			sb.append("-EXECUTE OK");

		} catch (SQLException sqle) {

			
			do {
				sb.append("-");
				String ss = sqle.getSQLState();
				if (ss == null)
					ss= "?????";
				sb.append(ss);
				sqle = sqle.getNextException();
			} while (sqle != null);

			
		}
		result[0] = sb.toString();
	}


	public static void oBOOLEAN(Boolean in, Boolean[] inout, Boolean[] out) throws SQLException {

		if (out[0] != null)
			throw new SQLException ("oBOOLEAN expected out[] to be null");

		out[0] = in;
		if (in == null)
			inout[0] = null;
		else
			inout[0] = new Boolean(inout[0].booleanValue() && in.booleanValue());

	}

	public static void pBOOLEAN(boolean in, boolean[] inout, boolean[] out) throws SQLException {

		if (out[0] != false)
			throw new SQLException ("pBOOLEAN expected out[] to be null");

		out[0] = in;
		inout[0] =inout[0] && in;

	}
	public static void oALLINT(Integer in, Integer[] inout, Integer[] out) throws SQLException {

		if (out[0] != null)
			throw new SQLException ("oALLINT expected out[] to be null");

		out[0] = in;
		if (in == null)
			;//inout[0] = null;
		else if (inout[0] == null)
			inout[0] = new Integer(3 * in.intValue());
		else
			inout[0] = new Integer(inout[0].intValue() + in.intValue());
	}
	public static void pTINYINT(byte in, byte[] inout, byte[] out) throws SQLException {

		out[0] = in;
		inout[0] += in;
	}
	public static void pSMALLINT(short in, short[] inout, short[] out) throws SQLException {

		out[0] = in;
		inout[0] += in;
	}

	/*
	** Procedures for testing literals passed to procedures as IN parameters
	*/

	public static void literalTest(int p1, String[] p2) {
		p2[0] = ">" + Integer.toString(p1) + "<";
	}
	public static void literalTest(long p1, String[] p2) {
		p2[0] = ">" + Long.toString(p1) + "<";
	}
	public static void literalTest(float p1, String[] p2) {
		p2[0] = ">" + Float.toString(p1) + "<";
	}
	public static void literalTest(double p1, String[] p2) {
		p2[0] = ">" + Double.toString(p1) + "<";
	}
	public static void literalTest(BigDecimal p1, String[] p2) {
		String s = p1 == null ? "NULL" : p1.toString();
		p2[0] = ">" + s + "<";
	}
	public static void literalTest(String p1, String[] p2) {
		String s = p1 == null ? "NULL" : p1.toString();
		p2[0] = ">" + s + "<";
	}
	public static void literalTest(java.sql.Date p1, String[] p2) {
		String s = p1 == null ? "NULL" : p1.toString();
		p2[0] = ">" + s + "<";
	}
	public static void literalTest(java.sql.Time p1, String[] p2) {
		String s = p1 == null ? "NULL" : p1.toString();
		p2[0] = ">" + s + "<";
	}
	public static void literalTest(java.sql.Timestamp p1, String[] p2) {

		String s = p1 == null ? "NULL" : p1.toString();
		p2[0] = ">" + s + "<";
	}


	/*
	** Procedures for parameter mapping testing.
	*/

	public static void pmap(short in, short[] inout, short[] out) {

		inout[0] += 6;
		out[0] = 77;
	}
	public static void pmap(int in, int[] inout, int[] out) {
		inout[0] += 9;
		out[0] = 88;

	}
	public static void pmap(long in, long[] inout, long[] out) {
		inout[0] += 8;
		out[0] = 99;
	}
	public static void pmap(float in, float[] inout, float[] out) {
		inout[0] += 9.9f;
		out[0] = 88.8f;
	}
	public static void pmap(double in, double[] inout, double[] out) {
		inout[0] += 3.9;
		out[0] = 66.8;
	}
	public static void pmap(BigDecimal in, BigDecimal[] inout, BigDecimal[] out) {
		inout[0] = inout[0].add(new BigDecimal(2.3));
		out[0] = new BigDecimal(84.1);
	}
	public static void pmap(byte[] in, byte[][] inout, byte[][] out) {

		inout[0][2] = 0x56;
		out[0] = new byte[4];
		out[0][0] = (byte) 0x09;
		out[0][1] = (byte) 0xfe;
		out[0][2] = (byte) 0xed;
		out[0][3] = (byte) 0x02;

	}
	public static void pmap(Date in, Date[] inout, Date[] out) {

		inout[0] = java.sql.Date.valueOf("2004-03-08");
		out[0] = java.sql.Date.valueOf("2005-03-08");

	}
	public static void pmap(Time in, Time[] inout, Time[] out) {
		inout[0] = java.sql.Time.valueOf("19:44:42");
		out[0] = java.sql.Time.valueOf("20:44:42");
	}
	public static void pmap(Timestamp in, Timestamp[] inout, Timestamp[] out) {

		inout[0] = java.sql.Timestamp.valueOf("2004-03-12 21:14:24.938222433");
		out[0] = java.sql.Timestamp.valueOf("2004-04-12 04:25:26.462983731");
	}
	public static void pmap(String in, String[] inout, String[] out) {
		inout[0] = inout[0].trim().concat("P2-PMAP");
		out[0] = "P3-PMAP";
	}


	public static int countRows(String schema, String table) throws SQLException
	{
		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		Statement s = conn.createStatement();
		ResultSet rs = s.executeQuery("SELECT COUNT(*) FROM " + schema + "." + table);
		rs.next();
		int count = rs.getInt(1);
		rs.close();
		s.close();
		conn.close();
		return count;
	}

     // Procedure used by the test for bug JIRA-491. The client side part
     // of this test is in lang/procedure.java

	public static void BIG_COL_491 (int i, ResultSet [] rs1, ResultSet [] rs2)
		throws SQLException
	{
		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		Statement st1 = conn.createStatement();
		rs1[0] = st1.executeQuery(
			"select int1, varchar32k from jira491 where int1 < " + i + " order by 1");

		Statement st2 = conn.createStatement();
		rs2[0] = st2.executeQuery(
			"select int1, varchar32k from jira491 where int1 > " + i + " order by 1");
	}

    // Procedure used by the test for bug JIRA-492. The client side part of
    // this test is in lang/procedure.java

	public static void LOTS_O_COLS_492(ResultSet [] rs)
        throws SQLException
    {
		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		Statement st1 = conn.createStatement();

		StringBuffer query = new StringBuffer("SELECT ");
        for (int i = 0; i < 100; i++)
        {
            int cno = 1000 + (i * 10);
            if (i > 0) query.append(", ");
            query.append("id AS col").append(cno).append(", nsi as col").
                append(cno+1).append(", ni AS col").append(cno+2).
                append(", nbi AS col").append(cno+3).append(", nd AS col").
                append(cno+4).append(", nr AS col").append(cno+5).
                append(", ndo AS col").append(cno+6).append(" ");
        }
		query.append("FROM jira492 a WHERE a.id = 0");

		rs[0] = st1.executeQuery(query.toString());
	}
}