prepstmt.java

derby database source code.good for you.

						  expectedValue).printStackTrace();
		
	}

	private static void checkBigDecimalMatch(BigDecimal expectedValue,
											 BigDecimal actualValue) 	{
		if (actualValue == expectedValue || 
			(actualValue.compareTo(expectedValue) == 0))
			System.out.println("PASS: Actual value " + actualValue + " matches expected value: " + expectedValue);
		else
			new Exception("FAIL: Actual value: " + actualValue +
							" does not match expected value:" + 
						  expectedValue).printStackTrace();
		
	}

	private static String bytesToString(byte[] ba)
	{
		String s = null;
		if (ba == null)
			return s;
		s = new String();
		for (int i = 0; i < ba.length; i++)
			s += (Integer.toHexString(ba[i] & 0x00ff));
		return s;
	}

	
	// Derby bug 614 has to do with how the server responds when the
	// client closes the statement in between split QRYDTA blocks. We
	// have to cause a split QRYDTA block, which we can do by having a
	// bunch of moderately-sized rows which mostly fill a 32K block
	// followed by a single giant row which overflows the block. Then,
	// we fetch some of the rows, then close the result set.
    private static void jira614Test(Connection conn)
	    throws Exception
    {
	    Statement stmt = conn.createStatement();
            PreparedStatement ps ;
	    try {
		    stmt.execute("drop table jira614");
	    } catch (Throwable t) { }
	    ps = conn.prepareStatement(
			    "create table jira614 (c1 varchar(10000))");
	    ps.executeUpdate();
	    String workString = genString("a", 150);
	    ps = conn.prepareStatement("insert into jira614 values (?)");
	    ps.setString(1, workString);
	    for (int row = 0; row < 210; row++)
		    ps.executeUpdate();
	    workString = genString("b", 10000);
	    ps.setString(1, workString);
	    ps.executeUpdate();
	    ps = conn.prepareStatement("select * from jira614");
            ResultSet rs = ps.executeQuery();

            int rowNum = 0;
            while (rs.next())
            {
                rowNum++;
                if (rowNum == 26)
                    break;
            }
            rs.close(); // This statement actually triggers the bug.
	    System.out.println("Test jira614 completed successfully -- no Distributed Protocol Exception occurred");
    }
    private static String genString(String c, int howMany)
    {
	    StringBuffer buf = new StringBuffer();
	    for (int i = 0; i < howMany; i++)
		    buf.append(c);
	    return buf.toString();
    }


	// Part two of the regression test for bug 614 verifies that the
	// server-side statement state is cleaned up when a statement is
	// re-used. Specifically, we set up a statement which has a non-null
	// splitQRYDTA value, then we close that statement and re-use it for
	// a totally unrelated query. If the splitQRYDTA wasn't cleaned up
	// properly, it comes flooding back as the response to that unrelated
	// query, causing a protocol parsing exception on the client.
	private static void jira614Test_a(Connection conn)
		throws Exception
	{
		// 1: set up a second table to use for an unrelated query:
	    Statement stmt = conn.createStatement();
		PreparedStatement ps ;
	    try { stmt.execute("drop table jira614_a"); } catch (Throwable t) { }
	    stmt.execute("create table jira614_a (c1 int)");
	    ps = conn.prepareStatement("insert into jira614_a values (?)");
	    for (int row = 1; row <= 5; row++)
		{
			ps.setInt(1, row);
		    ps.executeUpdate();
		}

		// 2: get the first statement into a splitQRYDTA state:
		ResultSet rs = stmt.executeQuery("select * from jira614");
		int rowNum = 0;
		while (rs.next())
		{
			rowNum++;
			if (rowNum == 26)
				break;
		}
		// 3: Now re-use the statement for some totally different purpose:
		stmt.close();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select * from jira614_a");
		while (rs.next());
		ps.close();
		rs.close();
		stmt.close();
	}

    // Jira-170 has to do with how the server handles re-synchronization of
    // the data stream when an enormous parameter value follows a failed
    // prepare statement. Note that it is deliberate here that we are preparing
    // a statement referring to a non-existing table.
    private static void jira170Test(Connection conn)
        throws Exception
    {
        Statement stmt = conn.createStatement();
        PreparedStatement ps = null ;
	    try {
		    stmt.execute("drop table jira170");
	    } catch (Throwable t) { }
        // Create a huge array of chars to be used as the input parameter
        char []cData = new char[1000000];
        for (int i = 0; i < cData.length; i++)
            cData[i] = Character.forDigit(i%10, 10);
        // The behavior of this test program depends on how the JDBC driver
        // handles statement prepares. The DB2 Universal JDBC driver implements
        // something called "deferred prepares" by default. This means that it
        // doesn't do the prepare of the statement until the statement is
        // actually executed. Other drivers, such as the standard Derby client
        // driver, do the prepare at the time of the prepare. This means that,
        // depending on which driver we're using and what the driver's
        // configuration is, we'll get the "table not found" error either on
        // the prepare or on the execute. It doesn't really matter for the
        // purposes of the test, because the whole point is that we *dont*
        // get a DRDA Protocol Exception, but rather a table-not-found
        // exception.
        try {
            ps = conn.prepareStatement("insert into jira170 values (?)");
            ps.setString(1, new String(cData));
            ps.execute();
            System.out.println("Test Jira170 failed: no exception when trying to execute a failed prepare with an enormous parameter");
        }
        catch (SQLException e)
        {
            if (e.getSQLState().equals("42X05"))
                System.out.println("Jira170: caught expected table not found");
            else
                e.printStackTrace();
        }
    }
	/**
	  * Jira-1454 is an off-by-one bug in the splitQRYDTA processing in the
	  * Network Server writeQRYDTA code, and is related to previous bugs
	  * 614, 170, 491, and 492. The issue is that if the DSS block is exactly
	  * the maximum DSS length (32767), then the writeQRYDTA code erroneously
	  * thinks the DSS needs to be split when in fact it doesn't.
	  *
	  * The repro case sets up the boundary scenario; we run the case three
	  * times, once with the value 1 less than the max DSS, once with the
	  * value 1 greater than the max DSS, and once with the exact DSS length.
	  * Only the third case triggers the JIRA-1454 bug; the other two tests
	  * are for completeness.
	  */
	private static void jira1454Test(Connection conn)
		throws Exception
	{
		tickleDSSLength(conn, 12748);
		tickleDSSLength(conn, 12750);
		tickleDSSLength(conn, 12749);
	}
	private static void tickleDSSLength(Connection conn, int c2Len)
		throws Exception
	{
		System.out.println("JIRA-1454 repro with c2 len=" + c2Len);
		Statement st = conn.createStatement();

		try {
			    st.execute("drop table jira1454");
		} catch (SQLException se) {}
		st.execute(
				"create table jira1454(c1 varchar(20000),c2 varchar(30000))");

		char [] c1 = new char[20000];
		for (int i = 0; i < c1.length; i++)
			    c1[i] = Character.forDigit(i%10, 10);
		char [] c2 = new char[30000];
		for (int i = 0; i < c2Len; i++)
			    c2[i] = Character.forDigit(i%10, 10);

		PreparedStatement pSt =
			conn.prepareStatement("insert into jira1454 values (?,?)");
		pSt.setString(1, new String(c1));
		pSt.setString(2, new String(c2,0, c2Len));

		pSt.execute();
		pSt.close();
		ResultSet rs = st.executeQuery("select * from jira1454");
		while (rs.next())
			    System.out.println("Fetched a row, c2.length=" +
						            rs.getString("c2").length());
		rs.close();
		st.close();
	}
	/**
	 * Jira-125 has to do with proper use of continuation headers 
	 * for very large reply messages, such as the SQLDARD which is
	 * returned for a prepared statement with an enormous number of
	 * parameter markers. This test generates a multi-segment SQLDARD
	 * response message from the server, to verify that the code in
	 * DDMWriter.finalizeDSSLength is executed.
	 *
	 * Repro for DERBY-125 off-by-one error.  This repro runs in
	 * two iterations.  The first iteration, we use a table name
	 * and a column name that are extra long, so that the server-
	 * side buffer has more data in it.  The second iteration, we
	 * use simpler names for the table and column, which take up
	 * less space in the server buffer.  Then, since the server-
	 * side bytes array was previously used for a larger amount of
	 * data, then the unused bytes contain old data.  Since we
	 * intentionally put the "larger amount of data" into the buffer
	 * during the first iteration, we know what the old data bytes
	 * are going to be.  Thus, by using specific lengths for the 
	 * table and column names, we can 'shift' the old data until we
	 * reach a point where the off-by-one error manifests itself:
	 * namely, we end up incorrectly leaving a non-zero data byte
	 * in the last position of the current server buffer, which
	 * is wrong.
	 */

    private static void jira125Test(Connection conn)
        throws Exception
    {
		jira125Test_a(conn);
		jira125Test_b(conn);
    }

    private static void jira125Test_b(Connection conn)
	    throws Exception
    {
	    Statement stmt = conn.createStatement();
        PreparedStatement ps ;
	    try {
		    stmt.execute("drop table jira125");
	    } catch (Throwable t) { }
		try {
	        stmt.execute("create table jira125 (id integer)");
			stmt.execute("insert into jira125 values 1, 2, 3");
		} catch (Throwable t) { }
        StringBuffer buf = new StringBuffer();
        buf.append("SELECT id FROM jira125 WHERE id IN ( ");

		// Must have at least 551 columns here, in order to force
		// server buffer beyond 32k.  NOTE: Changing this number
		// could cause the test to "pass" even if a regression
		// occurs--so only change it if needed!
        int nCols = 556;
        for (int i = 0; i < nCols; i++) buf.append("?,");
        buf.append("?)");
        ps = conn.prepareStatement(buf.toString());
        // Note that we actually have nCols+1 parameter markers
        for (int i = 0; i <= nCols; i++) ps.setInt(i+1, 1);
        ResultSet rs = ps.executeQuery();
        while (rs.next());
        System.out.println("Test jira125 successful: " + (nCols + 1) +
			" parameter markers successfully prepared and executed.");
    }

    private static void jira125Test_a(Connection conn)
	    throws Exception
    {
	    Statement stmt = conn.createStatement();

		// Build a column name that is 99 characters long;
		// the length of the column name and the length of
		// the table name are important to the repro--so
		// do not change these unless you can confirm that
		// the new values will behave in the same way.
		StringBuffer id = new StringBuffer();
		for (int i = 0; i < 49; i++)
			id.append("id");
		id.append("i");

		// Build a table name that is 97 characters long;
		// the length of the column name and the length of
		// the table name are important to the repro--so
		// do not change these unless you can confirm that
		// the new values will behave in the same way.
		StringBuffer tabName = new StringBuffer("jira");
		for (int i = 0; i < 31; i++)
			tabName.append("125");

	    try {
		    stmt.execute("drop table " + tabName.toString());
	    } catch (Throwable t) { }
		try {
	        stmt.execute("create table " + tabName.toString() + " (" +
				id.toString() + " integer)");
			stmt.execute("insert into " + tabName.toString() + " values 1, 2, 3");
		} catch (Throwable t) { }

        PreparedStatement ps;
        StringBuffer buf = new StringBuffer();
        buf.append("SELECT " + id.toString() + " FROM " +
			tabName.toString() + " WHERE " + id.toString() + " IN ( ");

		// Must have at least 551 columns here, in order to force
		// server buffer beyond 32k.  NOTE: Changing this number
		// could cause the test to "pass" even if a regression
		// occurs--so only change it if needed!
        int nCols = 554;
        for (int i = 0; i < nCols; i++) buf.append("?,");
        buf.append("?)");
        ps = conn.prepareStatement(buf.toString());
        // Note that we actually have nCols+1 parameter markers
        for (int i = 0; i <= nCols; i++) ps.setInt(i+1, 1);
        ResultSet rs = ps.executeQuery();
        while (rs.next());
        System.out.println("Iteration 1 successful: " + (nCols + 1) +
			" parameter markers successfully prepared and executed.");
    }
}