import java.sql.*;

public class d498 {

	public static void main(String [] args) {

		try {

			Connection conn = null;
			if ((args.length > 0) && args[0].equalsIgnoreCase("server")) {
				Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
				conn = DriverManager.getConnection(
					"jdbc:derby://localhost:1527/testdb;create=true", "bah", "humbug");
			}
			else {
				Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
				conn = DriverManager.getConnection(
					"jdbc:derby:testdb;create=true;user=blah;password=humbug");
			}

			Statement st = conn.createStatement();
			PreparedStatement pSt = null;

			conn.setAutoCommit(false);

			// In embedded mode, it doesn't matter what the connection
			// holdability is set to, because it's overridden by the
			// statement-level holdability set within the stored procedure.
			// But for server, this connection holdability overrides
			// statement holdability within the stord procedure,
			// which seems wrong...
			conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
//			conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
			System.out.println("\nHoldability on parent connection: " +
				conn.getHoldability());

			st = conn.createStatement();

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

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

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

				try {
					st.execute("create table testtable1 (id integer, vc varchar(100))");
					st.execute("insert into testtable1 values (1, 'one'), (2, 'two'), (3, 'three')");
				} catch (SQLException se) { System.out.println(se.getSQLState()); }
	
				try {
					st.execute("create table testtable2 (id integer, nsi smallint)");
					st.execute("insert into testtable2 values (1, 1), (2, 2), (3, 3)");
				} catch (SQLException se) { System.out.println(se.getSQLState()); }
	
				try {
					st.execute("create table testtable3 (bi bigint, si smallint)");
					st.execute("insert into testtable3 values (4, 4), (5, 5), (6, 6)");
				} catch (SQLException se) { System.out.println(se.getSQLState()); }
	
				try {
					st.execute("drop procedure MYPROC");
				} catch (SQLException se) {}
	
				try {
					st.execute("create procedure MYPROC() " +
						"language java parameter style java external name " +
						"'d498.p2' result sets 3");
				} catch (SQLException se) { System.out.println(se.getSQLState()); }

				conn.commit();

				System.out.println("\n***> Doing selects directly...\n");
				Statement st1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
				ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
				st1.executeQuery("select * from testtable1");

				Statement st2 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
				st2.execute("select * from testtable2");

				Statement st3 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
				st3.execute("select * from testtable3");

				System.out.println("Executed all selects, now doing a commit.");
				conn.commit();

				System.out.println("Dumping result sets...");
				dumpRS(st1.getResultSet(), 1);
				dumpRS(st2.getResultSet(), 2);
				dumpRS(st3.getResultSet(), 3);

				conn.commit();

				System.out.println("\n***> Doing selects within a procedure call...\n");
				CallableStatement cSt = conn.prepareCall("call MYPROC()");
				cSt.execute();

				System.out.println("\nExecuted procedure, now doing a commit.");
				conn.commit();

				System.out.println("Dumping result sets...");
				dumpRS(cSt.getResultSet(), 1);
				if (cSt.getMoreResults())
					dumpRS(cSt.getResultSet(), 2);
				if (cSt.getMoreResults())
					dumpRS(cSt.getResultSet(), 3);

				conn.commit();
				System.out.println("\nAll done.\n");

			} catch (Exception e) {

				System.out.println("-=- Ooops, not sure what this is:");
				e.printStackTrace();
				conn.rollback();

			}

			st.close();
			conn.close();
	
		} catch (Exception e) {

			System.out.println("-=- OOPS: Unexpected problem while trying " +
				"to reproduce: ");
			e.printStackTrace();

		}

	}

	public static void p2(ResultSet[] rs1, ResultSet[] rs2,
		ResultSet[] rs3) throws Exception
	{
		Connection conn = DriverManager.getConnection(
			"jdbc:default:connection");

		System.out.println("Auto-commit on default connection: " +
			conn.getAutoCommit());

		System.out.println("Holdability on default connection: " +
			conn.getHoldability());

		Statement st1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
			ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
		rs1[0] = st1.executeQuery("select * from testtable1");

		Statement st2 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
			ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
		rs2[0] = st2.executeQuery("select * from testtable2");

		Statement st3 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
			ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
		rs3[0] = st3.executeQuery("select * from testtable3");

		return;

	}

	private static void dumpRS(ResultSet rs, int i) {

		try {
			System.out.println("\nGot result set #" + i + ":\n");
			while ((rs != null) && rs.next()) {
				System.out.println(rs.getString(1) + ", " + rs.getString(2));
			}
		} catch (SQLException se) {
			System.out.println(se.getMessage());
		}

	}
}