proceduretest.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.util.ProcedureTest

   Copyright 2003, 2004 The Apache Software Foundation or its licensors, as applicable.

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.

 */

package org.apache.derbyTesting.functionTests.util;

import org.apache.derbyTesting.functionTests.util.Formatters;

import java.sql.*;
import java.math.BigDecimal;
/**
	Java procedures for the procedure.sql test.
*/
public abstract class ProcedureTest implements ResultSet {

	public static void zeroArg() {
		System.out.println("zeroArg() called");
	}

	public static void insertRow(int p1) throws SQLException {
		insertRow(p1, "int");
	}

	public static void insertRow(int p1, String p2) throws SQLException {
		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		PreparedStatement ps = conn.prepareStatement("insert into t1 values (?, ?)");
		ps.setInt(1, p1);
		ps.setString(2, p2);
		ps.executeUpdate();
		ps.close();
		conn.close();
	}

	public static void maxMemPerTabTest() throws SQLException {
/*		StringBuffer sbA = new StringBuffer(20000);
		for (int i = 0; i < 20000; i++)
			sbA.append('a');
		String largeStringA20000 = new String(sbA);
    largeStringA20000.substring(0,2000);

		StringBuffer sbB = new StringBuffer(20000);
		for (int i = 0; i < 20000; i++)
			sbB.append('b');
		String largeStringB20000 = new String(sbB);
		String largeStringB2000 = largeStringB20000.substring(0,2000);

		StringBuffer sbC = new StringBuffer(20000);
		for (int i = 0; i < 20000; i++)
			sbC.append('b');
		String largeStringC20000 = new String(sbC);
		String largeStringC2000 = largeStringC20000.substring(0,2000);

		StringBuffer sbD = new StringBuffer(20000);
		for (int i = 0; i < 20000; i++)
			sbD.append('b');
		String largeStringD20000 = new String(sbD);
		String largeStringD2000 = largeStringD20000.substring(0,2000); */

		String largeStringA20000 = new String(Formatters.repeatChar("a",20000));
		String largeStringA2000 = new String(Formatters.repeatChar("a",2000));
		String largeStringB20000 = new String(Formatters.repeatChar("b",20000));
		String largeStringB2000 = new String(Formatters.repeatChar("b",2000));
		String largeStringC20000 = new String(Formatters.repeatChar("c",20000));
		String largeStringC2000 = new String(Formatters.repeatChar("c",2000));
		String largeStringD20000 = new String(Formatters.repeatChar("d",20000));
		String largeStringD2000 = new String(Formatters.repeatChar("d",2000));

		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		PreparedStatement ps = conn.prepareStatement("insert into tab1 values (?, ?)");
		ps.setInt(1, 1);
		ps.setString(2, largeStringA20000);
		ps.executeUpdate();
		ps.setInt(1, 2);
		ps.setString(2, largeStringB20000);
		ps.executeUpdate();
		ps.setInt(1, 3);
		ps.setString(2, largeStringC20000);
		ps.executeUpdate();
		ps.close();
		ps = conn.prepareStatement("insert into tab2 values (?, ?)");
		ps.setInt(1, 1);
		ps.setString(2, largeStringA20000);
		ps.executeUpdate();
		ps.setInt(1, 2);
		ps.setString(2, largeStringC20000);
		ps.executeUpdate();
		ps.setInt(1, 3);
		ps.setString(2, largeStringD20000);
		ps.executeUpdate();
		ps.close();
		ps = conn.prepareStatement("insert into tab3 values (?, ?)");
		ps.setInt(1, 1);
		ps.setString(2, largeStringA2000);
		ps.executeUpdate();
		ps.setInt(1, 2);
		ps.setString(2, largeStringB2000);
		ps.executeUpdate();
		ps.setInt(1, 3);
		ps.setString(2, largeStringC2000);
		ps.executeUpdate();
		ps.close();
		ps = conn.prepareStatement("insert into tab4 values (?, ?)");
		ps.setInt(1, 1);
		ps.setString(2, largeStringA2000);
		ps.executeUpdate();
		ps.setInt(1, 2);
		ps.setString(2, largeStringC2000);
		ps.executeUpdate();
		ps.setInt(1, 3);
		ps.setString(2, largeStringD2000);
		ps.executeUpdate();
		ps.close();
		conn.close();
	}

	private static void insertInBig(Connection conn, String A, String B, String C, String D) throws SQLException {
		PreparedStatement ps = conn.prepareStatement("insert into big values (?, ?, ?, ?)");
		ps.setString(1, A);
		ps.setString(2, B);
		ps.setString(3, C);
		ps.setString(4, D);
		ps.executeUpdate();
		ps.close();
	}

	public static void bigTestData(int i) throws SQLException {
		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		switch (i)
		{
			case 1:
				String largeStringA10000 = new String(Formatters.repeatChar("a",10000));
				String largeStringB10000 = new String(Formatters.repeatChar("b",10000));
				String largeStringC10000 = new String(Formatters.repeatChar("c",10000));
				String largeStringD10000 = new String(Formatters.repeatChar("d",10000));
				insertInBig(conn, largeStringA10000, largeStringB10000, largeStringC10000, largeStringD10000);
				break;
			case 2:
				largeStringA10000 = new String(Formatters.repeatChar("e",10000));
				largeStringB10000 = new String(Formatters.repeatChar("f",10000));
				largeStringC10000 = new String(Formatters.repeatChar("g",10000));
				largeStringD10000 = new String(Formatters.repeatChar("h",10000));
				insertInBig(conn, largeStringA10000, largeStringB10000, largeStringC10000, largeStringD10000);
				break;
			case 3:
				largeStringA10000 = new String(Formatters.repeatChar("i",10000));
				largeStringB10000 = new String(Formatters.repeatChar("j",10000));
				largeStringC10000 = new String(Formatters.repeatChar("k",10000));
				largeStringD10000 = new String(Formatters.repeatChar("l",10000));
				insertInBig(conn, largeStringA10000, largeStringB10000, largeStringC10000, largeStringD10000);
				break;
			case 4:
				largeStringA10000 = new String(Formatters.repeatChar("m",10000));
				largeStringB10000 = new String(Formatters.repeatChar("n",10000));
				largeStringC10000 = new String(Formatters.repeatChar("o",10000));
				largeStringD10000 = new String(Formatters.repeatChar("p",10000));
				insertInBig(conn, largeStringA10000, largeStringB10000, largeStringC10000, largeStringD10000);
				break;
			case 5:
				String largeStringA30000 = new String(Formatters.repeatChar("a",30000));
				String largeStringB2752 = new String(Formatters.repeatChar("b",2752));
				PreparedStatement ps = conn.prepareStatement("insert into big values (?, ?)");
				ps.setString(1, largeStringA30000);
				ps.setString(2, largeStringB2752);
				ps.executeUpdate();
				ps.close();
				break;
			case 6:
				largeStringA30000 = new String(Formatters.repeatChar("a",30000));
				String largeStringB2750 = new String(Formatters.repeatChar("b",2750));
				ps = conn.prepareStatement("insert into big values (?, ?)");
				ps.setString(1, largeStringA30000);
				ps.setString(2, largeStringB2750);
				ps.executeUpdate();
				ps.close();
				break;
			case 7:
				String largeStringA40000 = new String(Formatters.repeatChar("a",40000));
				ps = conn.prepareStatement("insert into big values (?)");
				ps.setString(1, largeStringA40000);
				ps.executeUpdate();
				ps.close();
				break;
			case 8:
				largeStringA40000 = new String(Formatters.repeatChar("a",40000));
				String largeStringB40000 = new String(Formatters.repeatChar("b",40000));
				String largeStringC40000 = new String(Formatters.repeatChar("c",40000));
				ps = conn.prepareStatement("insert into big values (?, ?, ?)");
				ps.setString(1, largeStringA40000);
				ps.setString(2, largeStringB40000);
				ps.setString(3, largeStringC40000);
				ps.executeUpdate();
				largeStringA40000 = new String(Formatters.repeatChar("d",40000));
				largeStringB40000 = new String(Formatters.repeatChar("e",40000));
				largeStringC40000 = new String(Formatters.repeatChar("f",40000));
				ps.setString(1, largeStringA40000);
				ps.setString(2, largeStringB40000);
				ps.setString(3, largeStringC40000);
				ps.executeUpdate();
				ps.close();
				break;
			case 9:
				String lStringA32672 = new String(Formatters.repeatChar("a",32672));
				String lStringB32672 = new String(Formatters.repeatChar("b",32672));
				String lStringC32672 = new String(Formatters.repeatChar("c",32672));
				String lStringD32672 = new String(Formatters.repeatChar("d",32672));
				insertInBig(conn, lStringA32672, lStringB32672, lStringC32672, lStringD32672);
				break;
		}
		conn.close();
	}

	//public static void selectRows_coll(int p1, java.util.Collection rs) throws SQLException {

	//	ResultSet[] d1 = new ResultSet[1];
	//	selectRows(p1, d1);
	//	rs.add(d1[0]);
	//}

	public static void selectRows(int p1, ResultSet[] data) throws SQLException {

		System.out.println("selectRows - 1 arg - 1 rs");

		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		PreparedStatement ps = conn.prepareStatement("select * from t1 where i = ?");
		ps.setInt(1, p1);
		data[0] = ps.executeQuery();
		conn.close();
	}
	public static void selectRows(int p1, int p2, ResultSet[] data1, ResultSet[] data2) throws SQLException {

		System.out.println("selectRows - 2 arg - 2 rs");

		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		PreparedStatement ps = conn.prepareStatement("select * from t1 where i = ?");
		ps.setInt(1, p1);
		data1[0] = ps.executeQuery();

		ps = conn.prepareStatement("select * from t1 where i >= ?");
		ps.setInt(1, p2);
		data2[0] = ps.executeQuery();

		if (p2 == 99)
			data2[0].close();

		// return no results
		if (p2 == 199) {
			data1[0] = null;
			data2[0] = null;
		}

		// swap results
		if (p2 == 299) {
			ResultSet rs = data1[0];
			data1[0] = data2[0];
			data2[0] = rs;
		}

		conn.close();
	}

	// select all rows from a table
	public static void selectRows(String table, ResultSet[] rs)
		throws SQLException
	{
		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		Statement stmt = conn.createStatement();
		rs[0] = stmt.executeQuery("SELECT * FROM " + table);
		conn.close();
	}
	
	public static void fivejp(ResultSet[] data1, ResultSet[] data2, ResultSet[] data3, ResultSet[] data4, ResultSet[] data5) throws SQLException {

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

		PreparedStatement ps1 = conn.prepareStatement("select * from MRS.FIVERS where i > ?");
		ps1.setInt(1, 1);
		data1[0] = ps1.executeQuery();

		PreparedStatement ps2 = conn.prepareStatement("select * from MRS.FIVERS  where i > ?");
		ps2.setInt(1, 2);
		data2[0] = ps2.executeQuery();

		PreparedStatement ps3 = conn.prepareStatement("select * from MRS.FIVERS  where i > ?");
		ps3.setInt(1, 3);
		data3[0] = ps3.executeQuery();

		PreparedStatement ps4 = conn.prepareStatement("select * from MRS.FIVERS  where i > ?");
		ps4.setInt(1, 4);
		data4[0] = ps4.executeQuery();

		PreparedStatement ps5 = conn.prepareStatement("select * from MRS.FIVERS  where i > ?");
		ps5.setInt(1, 5);
		data5[0] = ps5.executeQuery();

		conn.close();
	}

	public static void parameter1(int a, String b, String c, java.sql.ResultSet[] rs) throws SQLException {

		System.out.print("PT1 a=" + a);
		if (b == null)
			System.out.println(" b = null");
		else
			System.out.print(" b=<"+b+">("+b.length()+")");
		if (c == null)
			System.out.println(" c = null");
		else
			System.out.print(" c=<"+c+">("+c.length()+")");

		System.out.println("");



		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		PreparedStatement ps = conn.prepareStatement("insert into PT1 values (?, ?, ?)");
		ps.setInt(1, a);
		ps.setString(2, b);
		ps.setString(3, c);
		ps.executeUpdate();
		ps.close();
		ps = conn.prepareStatement("select a,b, length(b), c, length(c) from PT1 where a = ?");
		ps.setInt(1, a);
		rs[0] = ps.executeQuery();
		conn.close();
	}

	public static void parameter2(int a, java.math.BigDecimal b, java.math.BigDecimal c, java.sql.ResultSet[] rs) throws SQLException {
		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		PreparedStatement ps = conn.prepareStatement("insert into PT1 values (?, ?, ?)");
		ps.setInt(1, a);
		ps.setString(2, b.toString());
		ps.setString(3, c.toString());
		ps.executeUpdate();
		ps.close();
		ps = conn.prepareStatement("select a,b,c from PT1 where a = ?");
		ps.setInt(1, a);
		rs[0] = ps.executeQuery();
		conn.close();
	}


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

		p1[0] = p2 * 2;
	}

	// Test for CLOB being returned.
	public static void clobselect (ResultSet[] results, 
								   ResultSet[] results1,
								   ResultSet[] results2
								   )
		throws Exception
	{

        Connection conn = DriverManager.getConnection(
			"jdbc:default:connection");
		PreparedStatement st = conn.prepareStatement("select * from lobCheckOne");
		results[0] = st.executeQuery();
		// Just some regular data
		PreparedStatement st1 = conn.prepareStatement("select count(*) from lobCheckOne");
		results1[0] = st1.executeQuery();
		// Now more Clobs
		PreparedStatement st2 = conn.prepareStatement("select * from lobCheckOne");
		results2[0] = st2.executeQuery();
		conn.close();
		return;

	}

	// Test for BLOB being returned.
	public static void blobselect (ResultSet[] results)
		throws Exception
	{

        Connection conn = DriverManager.getConnection(
			"jdbc:default:connection");
		PreparedStatement st = conn.prepareStatement("select * from lobCheckTwo");
		results[0] = st.executeQuery();
		conn.close();
		return;