/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.derbynet.PrepareStatementTest

   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You 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.tests.derbynet;

import java.io.ByteArrayInputStream;
import java.io.DataInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.BatchUpdateException;
import java.sql.CallableStatement;
import java.sql.DataTruncation;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Arrays;
import junit.framework.Test;
import org.apache.derbyTesting.functionTests.util.Formatters;
import org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetStream;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;

/**
 * This Junit test class tests the JDBC PreparedStatement.  This test
 * is a Junit version of the old prepStmt.java test.  That test tested
 * prepared statements in client/server context, and many of the test
 * cases is specifically testing corner cases in client/server
 * communication.  However, this Junit test is set up to run as part
 * of both the embedded and client/server test suites.
 */

public class PrepareStatementTest extends BaseJDBCTestCase
{

    /**
     * Creates a new instance of PrepareStatementTest
     *
     * @param name name of the test.
     */
    public PrepareStatementTest(String name)
    {
        super(name);
    }


    /**
     * Adds this class to the default suite.  That is, all test cases will be
     * run in both embedded and client/server.
     */
    public static Test suite()
    {
        if ( JDBC.vmSupportsJSR169())
            // see DERBY-2233 for details
                        return new BaseTestSuite(
                "empty PrepareStatementTest - client not supported on JSR169");
        else
        {
            BaseTestSuite suite = new BaseTestSuite("PrepareStatementTest");
            suite.addTest(TestConfiguration.defaultSuite(PrepareStatementTest.class));
            suite.addTest(TestConfiguration.clientServerDecorator(
                TestConfiguration.connectionCPDecorator(new CleanDatabaseTestSetup(
                    new BaseTestSuite(PrepareStatementTest.class)))));
            return suite;
        }
    }


    /**
     * Test basic prepare mechanism with executeUpdate and executeQuery with
     * and without simple parameters.
     */
    public void testBasicPrepare() throws Exception
    {
        // executeUpdate() without parameters
        PreparedStatement pSt
            = prepareStatement("create table t1(c1 int, c2 int, c3 int)");
        assertUpdateCount(pSt, 0);
        pSt.close();

        // Rows to be inserted in table t1 for this test
        final Integer[][] t1_rows = {
            {99, 5, 9},
            {2, 6, 10},
            {7, 5, 8}
        };

        // executeUpdate() with parameters
        pSt = prepareStatement("insert into t1 values (?, " + t1_rows[0][1]
                               + ", ?)");
        pSt.setInt(1, t1_rows[0][0].intValue());
        pSt.setInt(2, t1_rows[0][2].intValue());
        assertUpdateCount(pSt, 1);
        pSt.close();

        // execute() with parameters, no result set returned
        pSt = prepareStatement("insert into t1 values (" + t1_rows[1][0] + ", "
                               + t1_rows[1][1] + ", ?), (?, " + t1_rows[2][1]
                               + ", " + t1_rows[2][2] + ")");
        pSt.setInt(1, t1_rows[1][2].intValue());
        pSt.setInt(2, t1_rows[2][0].intValue());
        boolean hasResultSet = pSt.execute();
        while (hasResultSet)
        {
            ResultSet rs = pSt.getResultSet();
            assertFalse(rs.next());
            rs.close();
            hasResultSet = pSt.getMoreResults();
        }
        assertEquals(2, pSt.getUpdateCount());
        pSt.close();

        // executeQuery() without parameters
        pSt = prepareStatement("select * from t1");
        ResultSet rs = pSt.executeQuery();
        JDBC.assertFullResultSet(rs, t1_rows, false);
        rs.close();
        pSt.close();

        // Create table with subset of rows to be selected in query below
        Integer[][] t1filter_rows = new Integer[2][];
        for (int i=0, j=0; i < t1_rows.length; ++i) {
            if (t1_rows[i][1].intValue() == 5) {
                t1filter_rows[j++] = t1_rows[i];
            }
        }

        // executeQuery() with parameters
        pSt = prepareStatement("select * from t1 where c2 = ?");
        pSt.setInt(1, 5);
        rs = pSt.executeQuery();
        JDBC.assertFullResultSet(rs, t1filter_rows, false);
        rs.close();
        pSt.close();

        // execute() with parameters, with result set returned
        pSt = prepareStatement("select * from t1 where c2 = ?");
        pSt.setInt(1, 5);
        assertTrue(pSt.execute());
        rs = pSt.getResultSet();
        JDBC.assertFullResultSet(rs, t1filter_rows, false);
        rs.close();
        assertFalse(pSt.getMoreResults());
        assertEquals(-1, pSt.getUpdateCount());
        pSt.close();
    }


    /**
     * Tests different data types for input parameters of a PreparedStatement.
     */
    public void testParameterTypes() throws Exception
    {
        PreparedStatement pSt = prepareStatement(
            "create table t2(si smallint,i int, bi bigint, r real, f float, "
            + "d double precision, n5_2 numeric(5,2), dec10_3 decimal(10,3), "
            + "ch20 char(20),vc varchar(20), lvc long varchar, "
            + "b20 char(23) for bit data, vb varchar(23) for bit data, "
            + "lvb long varchar for bit data,  dt date, tm time, "
            + "ts timestamp not null)");
        assertUpdateCount(pSt, 0);
        pSt.close();

        // byte array for binary values.
        byte[] ba = new byte[] { 0x0,0x1,0x2,0x3,0x4,0x5,0x6,0x7,0x8,0x9,0xa,
                                 0xb,0xc,0xd,0xe,0xf,0x10,0x11,0x12,0x13 };

        byte[] bapad = new byte[23];  // For padded byte data
        System.arraycopy(ba, 0, bapad, 0, ba.length);
        // Pad with space!!!
        Arrays.fill(bapad, ba.length, bapad.length, (byte )0x20);

        // Values to be inserted
        Object[][] t2_rows = {
            {1, 2, 3L, 4.0F,
             5.0, 6.0, new BigDecimal("77.77"),
             new BigDecimal("8.100"), "column9string       ",
             "column10vcstring", "column11lvcstring", bapad, ba, ba,
             Date.valueOf("2002-04-12"), Time.valueOf("11:44:30"),
             Timestamp.valueOf("2002-04-12 11:44:30.000000000")},
            {1, 2, 3L, 4.0F,
             5.0, 6.0, new BigDecimal("77.77"),
             new BigDecimal("8.100"), "column11string      ",
             "column10vcstring", "column11lvcstring", bapad, ba, ba,
             Date.valueOf("2002-04-12"), Time.valueOf("11:44:30"),
             Timestamp.valueOf("2002-04-12 11:44:30.000000000")},
            {null, null, null, null, null, null, null, null, null, null, null,
             null, null, null, null, null,
             Timestamp.valueOf("2002-04-12 11:44:31.000000000")}
        };


        pSt = prepareStatement(
            "insert into t2 values (?, ?, ?, ?,  ?, ?, ?, ?, ?, ?, ?, ?, ?, "
            + "?, ? ,? , ?)");
        pSt.setShort(1, ((Integer )t2_rows[0][0]).shortValue());
        pSt.setInt(2, ((Integer )t2_rows[0][1]).intValue());
        pSt.setLong(3, ((Long)t2_rows[0][2]).longValue());
        pSt.setFloat(4, ((Float )t2_rows[0][3]).floatValue());
        pSt.setDouble(5, ((Double )t2_rows[0][4]).doubleValue());
        pSt.setDouble(6, ((Double )t2_rows[0][5]).doubleValue());
        pSt.setBigDecimal(7, (BigDecimal )t2_rows[0][6]);
        pSt.setBigDecimal(8, new BigDecimal("8.1")); // Diff. precision
        pSt.setString(9, "column9string");  // Without padding
        byte[] c10ba = ((String )t2_rows[0][9]).getBytes("UTF-8");
        int len = c10ba.length;
        pSt.setAsciiStream(10, new ByteArrayInputStream(c10ba), len);
        byte[] c11ba = ((String )t2_rows[0][10]).getBytes("UTF-8");
        len = c11ba.length;
        pSt.setCharacterStream(11, new InputStreamReader
                               (new ByteArrayInputStream(c11ba),"UTF-8"),len);
        pSt.setBytes(12, ba);
        pSt.setBinaryStream(13, new ByteArrayInputStream(ba), ba.length);
        pSt.setBytes(14, ba);
        pSt.setDate(15, ((Date )t2_rows[0][14]));
        pSt.setTime(16, ((Time )t2_rows[0][15]));
        pSt.setTimestamp(17, ((Timestamp )t2_rows[0][16]));
        assertUpdateCount(pSt, 1);

        // test setObject on different datatypes of the input parameters of
        // PreparedStatement
        for (int i=0; i<17; ++i) {
            pSt.setObject(i+1, t2_rows[1][i]);
        }
        assertUpdateCount(pSt, 1);

        // test setNull on different datatypes of the input parameters of
        // PreparedStatement
        pSt.setNull(1, java.sql.Types.SMALLINT);
        pSt.setNull(2, java.sql.Types.INTEGER);
        pSt.setNull(3, java.sql.Types.BIGINT);
        pSt.setNull(4, java.sql.Types.REAL);
        pSt.setNull(5, java.sql.Types.FLOAT);
        pSt.setNull(6, java.sql.Types.DOUBLE);
        pSt.setNull(7, java.sql.Types.NUMERIC);
        pSt.setNull(8, java.sql.Types.DECIMAL);
        pSt.setNull(9, java.sql.Types.CHAR);
        pSt.setNull(10, java.sql.Types.VARCHAR);
        pSt.setNull(11, java.sql.Types.LONGVARCHAR);
        pSt.setNull(12, java.sql.Types.BINARY);
        pSt.setNull(13, java.sql.Types.VARBINARY);
        pSt.setNull(14, java.sql.Types.LONGVARBINARY);
        pSt.setNull(15, java.sql.Types.DATE);
        pSt.setNull(16, java.sql.Types.TIME);

        pSt.setTimestamp(17, ((Timestamp )t2_rows[2][16]));
        assertFalse(pSt.execute());
        assertEquals(1, pSt.getUpdateCount());
        pSt.close();

        pSt = prepareStatement("select * from t2");
        ResultSet rs = pSt.executeQuery();
        JDBC.assertFullResultSet(rs, t2_rows, false);
        rs.close();
        pSt.close();

        // negative test cases with no parameters set
        try {
            pSt = prepareStatement("select * from t2 where i = ?");
            rs = pSt.executeQuery();
            fail("Exception expected above!");
        } catch (SQLException e) {
            assertSQLState("07000", e);
        }
        rs.close();
        pSt.close();


        try {
            pSt = prepareStatement(
                "insert into t2 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
                + "?, ?, ?, ?, ?)");
            pSt.executeUpdate();
            fail("Exception expected above!");
        } catch (SQLException e) {
            assertSQLState("07000", e);
        }
        pSt.close();

        // Some test cases for DERBY-2558, involving validation of the
        // parameterIndex argument to the 4-argument overload of setObject
        //
        pSt = prepareStatement("create table d2558 (i int)");
        assertUpdateCount(pSt, 0);
        pSt.close();
        pSt = prepareStatement("insert into d2558 values (3), (4)");
        assertUpdateCount(pSt, 2);
        pSt.close();
        pSt = prepareStatement("select * from d2558 where i = ?");
        pSt.setObject(1,3,java.sql.Types.INTEGER,0);
        try {
            // There's only 1 parameter marker, so this should fail:
            pSt.setObject(2,4, java.sql.Types.INTEGER,0);
            rs = pSt.executeQuery();
            rs.close();
            fail("Exception expected above!");
        } catch (SQLException e) {
            assertSQLState("XCL13", e);
        }
        pSt.close();

    }


    /**
     * Test prepared statements with a large number of input parameters.
     */
    public void testBigTable() throws Exception
    {
        int tabSize = 1000;
        StringBuffer createBigTabSql
            = new StringBuffer("create table bigtab (");
        for (int i = 1; i <= tabSize; ++i) {
            createBigTabSql.append("c");
            createBigTabSql.append(i);
            createBigTabSql.append(" int");
            createBigTabSql.append((i != tabSize) ? ", " : " )");
        }

        PreparedStatement pSt = prepareStatement(createBigTabSql.toString());
        assertUpdateCount(pSt, 0);
        pSt.close();

        insertTab("bigtab", 50);
        insertTab("bigtab", 200);
        insertTab("bigtab", 300);
        insertTab("bigtab", 500);
        // prepared Statement with many  params (bug 4863)
        insertTab("bigtab", 1000);
        selectFromBigTab();

        // Negative Cases
        try {
            insertTab("bigtab", 1001);
            fail("Exception expected above!");
        } catch (SQLException e) {
            assertSQLState("42X14", e);
        }
        // this one will give a sytax error
        try {
            insertTab("bigtab", 0);
            fail("Exception expected above!");
        } catch (SQLException e) {
            assertSQLState("42X01", e);
        }
        // table doesn't exist
        try {
            insertTab("wrongtab",1000);
            fail("Exception expected above!");
        } catch (SQLException e) {
            assertSQLState("42X05", e);
        }
    }

    /**
     * Insert 1 row with the given number of columns into the given table.
     * It is assumed that the table has numCols, named c1, c2, ...
     * This is a helper method for the testBigTable test case.
     *
     * @param tabName Table name
     * @param numCols Number of columns in table.
     */
    private void insertTab(String tabName, int numCols)
        throws SQLException
    {
        StringBuffer insertSql
            = new StringBuffer("insert into " + tabName + "(");
        for (int i = 1; i <= numCols; ++i)
        {
            insertSql.append(" c");
            insertSql.append(i);
            insertSql.append((i != numCols) ? ", " : ")");
        }
        insertSql.append("  values (");
        for (int i = 1; i <= numCols; ++i)
        {
            insertSql.append("?");
            insertSql.append((i != numCols) ? ", " : " )");
        }

        PreparedStatement pSt = prepareStatement(insertSql.toString());
        for (int i = 1; i <= numCols; ++i) {
            pSt.setInt(i, i);
        }
        assertUpdateCount(pSt, 1);
        pSt.close();
    }

    /**
     * Test that the table bigtab contains the expected tuples for the test
     * case testBigTable.
     */
    private void selectFromBigTab() throws SQLException
    {
        String selectSQL = "select * from bigtab";
        PreparedStatement pSt = prepareStatement(selectSQL);
        ResultSet rs = pSt.executeQuery();

        int i = 0;
        while (rs.next())
        {
            switch(++i) {
                case 1:
                case 2:
                case 3:
                    assertNull(rs.getObject(500));
                    assertNull(rs.getObject(1000));
                    break;
                case 4:
                    assertEquals(rs.getInt(500), 500);
                    assertNull(rs.getObject(1000));
                    break;
                case 5:
                    assertEquals(rs.getInt(500), 500);
                    assertEquals(rs.getInt(1000), 1000);
                    break;
                default:
                    fail("Too many rows in bigTab");
            }
        }
        assertEquals(i, 5);

        rs.close();
        pSt.close();

    }


    /**
     * Check that values are preserved when BigDecimal values
     * which have more than 31 digits are converted to Double
     * with setObject.
     */
    public void testBigDecimalSetObject() throws SQLException
    {
        getConnection().setAutoCommit(false);
        String sql = "CREATE TABLE doubletab (i int, doubleVal DOUBLE)";
        Statement stmt = createStatement();
        assertUpdateCount(stmt, 0, sql);
        stmt.close();
        commit();

        // Insert various double values
        double[] doubleVals = {1.0E-130, 1.0E125, 0, -1.0E124};
        BigDecimal[] bigDecimalVals =
            { new BigDecimal(1.0E-130),
              new BigDecimal(1.0E125),
              new BigDecimal(-1.0E124),
              new BigDecimal("12345678901234567890123456789012"),
              new BigDecimal("1.2345678901234567890123456789012")
        };

        String isql = "INSERT INTO doubletab VALUES (?, ?)";
        PreparedStatement insPs = prepareStatement(isql);
        String ssql = "SELECT doubleVal FROM doubletab";
        PreparedStatement selPs = prepareStatement(ssql);
        String dsql = "DELETE FROM doubletab";
        PreparedStatement delPs = prepareStatement(dsql);
        for (int i = 0; i < bigDecimalVals.length; ++i)
        {
            BigDecimal bd = bigDecimalVals[i];
            insPs.setInt(1,i);
            insPs.setObject(2,bd,java.sql.Types.DOUBLE);
            assertUpdateCount(insPs, 1);
            // Check Value
            ResultSet rs = selPs.executeQuery();
            rs.next();
            assertEquals(bd.doubleValue(), rs.getDouble(1), 0.0);
            rs.close();
            // Clear out the table;
            assertUpdateCount(delPs, 1);
        }
        insPs.close();
        selPs.close();
        delPs.close();
        commit();
    }


    /**
     * Test BigDecimal with scale as parameter.
     */
    public void testBigDecimalSetObjectWithScale() throws Exception
    {
        getConnection().setAutoCommit(false);
        String sql = "CREATE TABLE numtab (num NUMERIC(10,6))";
        Statement stmt = createStatement();
        assertUpdateCount(stmt, 0, sql);
        stmt.close();
        commit();

        // make a big decimal from string
        BigDecimal bdFromString = new BigDecimal("2.33333333");

        sql = "INSERT INTO  numtab  VALUES(?)";
        PreparedStatement ps =  prepareStatement(sql);
        // setObject using the big decimal value
        int scale = 2;
        ps.setObject(1, bdFromString, java.sql.Types.DECIMAL, scale);
        assertUpdateCount(ps, 1);
        ps.close();
        // check the value
        sql = "SELECT num FROM numtab";
        stmt = createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        rs.next();
        // Check that the correct scale was set
        BigDecimal expected
            = bdFromString.setScale(scale, RoundingMode.DOWN);
        BigDecimal actual = (BigDecimal)rs.getObject(1);
        assertEquals("Actual value: " + actual
                     + "does not match expected value: " + expected,
                     expected.compareTo(actual), 0);
        rs.close();
        stmt.close();

        commit();
    }


    /**
     * Test execution of batch update where the type of
     * a parameter varies for difference entries in the batch.
     */
     public void testVaryingClientParameterTypeBatch() throws Exception
     {
         Statement stmt = createStatement();
         String createSql
             = "create table varcharclobtab (c1 varchar(100), c2 clob)";
         assertUpdateCount(stmt, 0, createSql);
         stmt.close();

         PreparedStatement pStmt
             = prepareStatement("insert into varcharclobtab VALUES(?,?)");

         pStmt.setNull(1, java.sql.Types.VARCHAR);
         pStmt.setString(2, "clob");
         pStmt.addBatch();

         pStmt.setString(1, "varchar");
         pStmt.setNull(2, java.sql.Types.CLOB);
         pStmt.addBatch();

         // The following statement should not throw an exception.
         pStmt.executeBatch();

         pStmt.close();
     }


    /**
     * Test small (close to 0) BigDecimal parameters.
     */
    public void testSmallBigDecimal() throws Exception
    {
        Statement stmt = createStatement();
        String createTableSQL
            = "create table Numeric_Tab (MAX_VAL NUMERIC(30,15), MIN_VAL "
            + "NUMERIC(30,15), NULL_VAL NUMERIC(30,15) DEFAULT NULL)";
        // to create the Numeric Table
        assertUpdateCount(stmt, 0, createTableSQL);

        String insertSQL
            = "insert into Numeric_Tab "
            + "values(999999999999999, 0.000000000000001, null)";
        assertUpdateCount(stmt, 1, insertSQL);

        //to extract the Maximum Value of BigDecimal to be Updated
        String sminBigDecimalVal = "0.000000000000001";
        BigDecimal minBigDecimalVal = new BigDecimal(sminBigDecimalVal);

        // to update Null value column with Minimum value
        String sPrepStmt = "update Numeric_Tab set NULL_VAL=?";

        // Uncomment and prepare the below statement instead to see JCC bug on
        // setObject for decimal
        // String sPrepStmt ="update Numeric_Tab set NULL_VAL="
        //                    + sminBigDecimalVal +" where 0.0 != ?";

        // get the PreparedStatement object
        PreparedStatement pstmt = prepareStatement(sPrepStmt);
        pstmt.setObject(1, minBigDecimalVal);
        pstmt.executeUpdate();
        pstmt.close();

        //to query from the database to check the call of pstmt.executeUpdate
        //to get the query string
        String Null_Val_Query = "Select NULL_VAL from Numeric_Tab";
        ResultSet rs = stmt.executeQuery(Null_Val_Query);
        rs.next();

        BigDecimal rBigDecimalVal = (BigDecimal )rs.getObject(1);
        assertEquals(rBigDecimalVal, minBigDecimalVal);
        rs.close();
        stmt.close();
    }


    /**
     * Test creation and execution of many Prepared Statements.
     * (Beetle 5130).
     */
    public void testManyPreparedStatements () throws Exception
    {
        int numOfPreparedStatement = 500;
        PreparedStatement[] tempPreparedStatement
            = new PreparedStatement[numOfPreparedStatement];

        for (int i = 0; i < numOfPreparedStatement; ++i) {
            tempPreparedStatement[i] = getConnection()
                .prepareStatement("SELECT COUNT(*) from SYS.SYSTABLES",
                                   ResultSet.TYPE_SCROLL_INSENSITIVE,
                                   ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = tempPreparedStatement[i].executeQuery();
            rs.close();
        }
        for (int i = 0; i < numOfPreparedStatement; ++i) {
            tempPreparedStatement[i].close();
        }
    }


    /**
     * Test invalid Timestamp parameters.
     */
    public void testInvalidTimestamp() throws Exception
    {
        Statement stmt = createStatement();
        assertUpdateCount(stmt, 0,
                          "CREATE TABLE TSTAB "
                          + "(I int, STATUS_TS  Timestamp, "
                          + " PROPERTY_TS Timestamp)" );
        assertUpdateCount(stmt, 1,
                          "INSERT INTO TSTAB "
                          + "VALUES(1 , '2003-08-15 21:20:00',"
                          + "       '2003-08-15 21:20:00')");
        assertUpdateCount(stmt, 1,
                          "INSERT INTO TSTAB "
                          + "VALUES(2 ,'1969-12-31 16:00:00.0',"
                          + "       '2003-08-15 21:20:00')");
        stmt.close();

        String timestamp = "20";
        String query =
            "select STATUS_TS from TSTAB "
            + "where  (STATUS_TS >= ? or PROPERTY_TS < ?)";

        PreparedStatement ps = prepareStatement(query);
        try {
            // Embedded will fail in setString
            // Client/server will fail in executeQuery
            ps.setString(1, timestamp);
            ps.setString(2, timestamp);
            ResultSet rs = ps.executeQuery();
            rs.close();
            fail("Exception expected above!");
        }
        catch (SQLException e) {
            assertSQLState("22007", e);
        }
        ps.close();
    }

    /**
     * <p>
     * Regression test for DERBY-614. The test consists of two parts:
     * </p>
     *
     * <p>
     * <b>Part 1:</b>
     * Test 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.
     * </p>
     *
     * <p>
     * <b>Part 2:</b>
     * 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.
     * </p>
     */
    public void testSplitQRYDTABlock() throws Exception
    {
        // Part 1:

        PreparedStatement ps
            = prepareStatement("create table jira614 (c1 varchar(10000))");
        assertUpdateCount(ps, 0);
        ps.close();

        String workString = Formatters.repeatChar("a", 150);
        ps = prepareStatement("insert into jira614 values (?)");
        ps.setString(1, workString);
        for (int row = 0; row < 210; ++row) ps.executeUpdate();

        workString = Formatters.repeatChar("b", 10000);
        ps.setString(1, workString);
        ps.executeUpdate();
        ps.close();

        ps = prepareStatement("select * from jira614");
        ResultSet rs = ps.executeQuery();

        int rowNum = 0;
        while (rs.next()) {
            if (++rowNum == 26) break;
        }
        rs.close(); // This statement actually triggers the bug.
        ps.close();

        // Part 2:

        // 1: set up a second table to use for an unrelated query:
        Statement stmt = createStatement();
        stmt.execute("create table jira614_a (c1 int)");

        ps = 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:
        rs = stmt.executeQuery("select * from jira614");
        rowNum = 0;
        while (rs.next())
        {
            if (++rowNum == 26) break;
        }

        // 3: Now re-use the statement for some totally different purpose:
        stmt.close();
        stmt = createStatement();
        rs = stmt.executeQuery("select * from jira614_a");
        while (rs.next());
        ps.close();
        rs.close();
        stmt.close();
    }


    /**
     * Test 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.
     * (This is a test case for Jira-170)
     */
    public void testExcpetionWithBigParameter() throws Exception
    {
        // 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.
        PreparedStatement ps = null ;
        try {
            ps = prepareStatement("insert into jira170 values (?)");
            ps.setString(1, new String(cData));
            ps.execute();
            ps.close();
            fail("No exception when executing a failed prepare with "
                 + "an enormous parameter");
        } catch (SQLException e) { // Should get "Table not Found"
            assertSQLState("42X05", e);
        }
    }

    /**
     * Test the 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.
     */
    public void testLargeReplies() throws Exception
    {
        jira125Test_a();
        jira125Test_b();
    }

    /**
     * First iteration of testLargeReplies test case.
     */
    private void jira125Test_a() throws Exception
    {
        // 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");

        Statement stmt = createStatement();
        stmt.execute("create table " + tabName.toString() + " (" +
                     id.toString() + " integer)");
        stmt.execute("insert into " + tabName.toString() + " values 1, 2, 3");
        stmt.close();

        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("?)");
        PreparedStatement ps = 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());
        rs.close();
        ps.close();
    }

    /**
     * Second iteration of testLargeReplies test case.
     */
    private void jira125Test_b() throws Exception
    {
        Statement stmt = createStatement();
        stmt.execute("create table jira125 (id integer)");
        stmt.execute("insert into jira125 values 1, 2, 3");

        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("?)");
        PreparedStatement ps = 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());
        rs.close();
        ps.close();
    }


    /**
     * This test case ensures that the bug introduced by the first patch for
     * Jira-815 has not been re-introduced.  The bug resulted in a hang if a
     * prepared statement was first executed with a lob value, and then
     * re-executed with a null-value in place of the lob.
     */
    public void testAlternatingLobValuesAndNull()  throws Exception
    {
        getConnection().setAutoCommit(false);
        Statement st = createStatement();
        st.execute("create table tt1 (CLICOL01 smallint not null)");
        st.execute("alter table tt1 add clicol02 smallint");
        st.execute("alter table tt1 add clicol03 int not null default 1");
        st.execute("alter table tt1 add clicol04 int");
        st.execute("alter table tt1 add clicol05 decimal(10,0) not null default 1");
        st.execute("alter table tt1 add clicol51 blob(1G)");
        st.execute("alter table tt1 add clicol52 blob(50)");
        st.execute("alter table tt1 add clicol53 clob(2G) not null default ''");
        st.execute("alter table tt1 add clicol54 clob(60)");
        commit();

        PreparedStatement pSt =
            prepareStatement("insert into tt1 values (?,?,?,?,?,?,?,?,?)");
        pSt.setShort(1, (short)500);
        pSt.setShort(2, (short)501);
        pSt.setInt(3, 496);
        pSt.setInt(4, 497);
        pSt.setDouble(5, 484);
        pSt.setBytes(6, "404 bit".getBytes());
        pSt.setBytes(7, "405 bit".getBytes());
        pSt.setString(8, "408 bit");
        pSt.setString(9, "409 bit");

        // Inserting first row
        assertUpdateCount(pSt, 1);

        pSt.setNull(2, java.sql.Types.SMALLINT);
        pSt.setNull(4, java.sql.Types.DOUBLE);
        pSt.setNull(7, java.sql.Types.BLOB);
        pSt.setNull(9, java.sql.Types.CLOB);

        // Inserting second row
        assertUpdateCount(pSt, 1);

        // Now inserting 3rd row, using lobs from 1st row
        ResultSet rs = st.executeQuery("select * from tt1");
        rs.next();
        pSt.setShort(1, rs.getShort(1));
        pSt.setShort(2, rs.getShort(2));
        pSt.setInt(3, rs.getInt(3));
        pSt.setInt(4, rs.getInt(4));
        pSt.setDouble(5, rs.getDouble(5));
        pSt.setBlob(6, rs.getBlob(6));
        pSt.setBlob(7, rs.getBlob(7));
        pSt.setClob(8, rs.getClob(8));
        pSt.setClob(9, rs.getClob(9));
        pSt.execute();

        // Now inserting 4th row, using lobs from 2nd row
        rs.next();
        pSt.setNull(2, java.sql.Types.SMALLINT);
        pSt.setNull(4, java.sql.Types.DOUBLE);
        pSt.setBlob(6, rs.getBlob(6));
        pSt.setNull(7, java.sql.Types.BLOB);
        pSt.setClob(8, rs.getClob(8));
        pSt.setNull(9, java.sql.Types.CLOB);
        pSt.execute();

        rs.close();
        pSt.close();

        commit();
    }


    /**
     * Test large batch sizes for Statement.addBatch and
     * Statement.executeBatch.  (This is a test for Jira 428.) Currently,
     * there is a hard DRDA limit of 65535 statements per batch (prior to
     * DERBY-428, the server failed at around 9000 statements). The different
     * JDBC clients support slightly lower limits: the Network Client supports
     * 65534 statements in a single batch, the DB2JCC driver v2.4 supports
     * 65532 statements, the DB2JCC driver v2.6 supports 32765 statements.
     * This test just verifies that a batch of 32765 statements works, and
     * that a batch of 100000 statements gets a BatchUpdateException from the
     * Network Client.
     */
    public void testLargeBatch() throws Exception
    {
        Statement stmt = createStatement();
        stmt.execute("create table jira428 (i integer)");
        getConnection().setAutoCommit(false);

        PreparedStatement ps
            = prepareStatement("insert into jira428 values (?)");
        for (int i = 0; i < 32765; ++i) {
            ps.setInt(1, i);
            ps.addBatch();
        }
        ps.executeBatch();
        ps.close();
        commit();

        // The below cannot be run as part of the test for the JCC client
        // because the exception forces the connection closed. For
        // DerbyNetClient, it's a clean exception that we can catch and
        // recover from, so we test that code path:
        ps = prepareStatement("insert into jira428 values (?)");
        for (int i = 0; i < 100000; ++i) {
            ps.setInt(1, i);
            ps.addBatch();
        }
        try {
            ps.executeBatch();
            assertFalse("Expected exception when >65534 statements per batch",
                        usingDerbyNetClient());
        } catch (BatchUpdateException bue) {
            assertSQLState("XJ116", bue);
            assertFalse("Unexpected exception in embedded framework",
                        usingEmbedded());
        }
        ps.close();
        commit();
    }


    /**
     * Test for an off-by-one bug in the splitQRYDTA processing in the Network
     * Server writeQRYDTA code (Jira-1454), 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.
     */
    public void testDSSLength() throws Exception
    {
        // Create table to be used in this test case
        Statement st = createStatement();
        st.execute(
            "create table jira1454(c1 varchar(20000), c2 varchar(30000))");
        st.close();

        tickleDSSLength(12748);
        tickleDSSLength(12750);
        tickleDSSLength(12749);
    }

    /**
     * Helper method for testDSSLength test case.  Inserts a record into a
     * table, and reads the content of the table before the content is
     * deleted.  (I.e., the table will empty when the method returns.  Use the
     * given length for the second parameter to the insert statement to test
     * different sizes for DSS blocks.
     *
     * @param c2Len Length to be used for the second
     */
    private void tickleDSSLength(int c2Len) throws Exception
    {
        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 =
            prepareStatement("insert into jira1454 values (?,?)");
        pSt.setString(1, new String(c1));
        pSt.setString(2, new String(c2, 0, c2Len));
        pSt.execute();
        pSt.close();

        Statement st = createStatement();
        ResultSet rs = st.executeQuery("select * from jira1454");
        while (rs.next()) {
            assertEquals(rs.getString("c2").length(), c2Len);
        }
        rs.close();

        // Clean up so table can be reused
        st.execute("delete from jira1454");
        st.close();
    }

    /**
     * A test case for DERBY-3046
     * We were running into null pointer exception if the parameter count
     * for PreparedStatement was 0 and the user tried doing setObject
     * 
     * @throws Exception
     */
    public void testVariationOfSetObject() throws Exception
    {
        Statement stmt = createStatement();
        String createString = "CREATE TABLE WISH_LIST  "
        	+  "(WISH_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY " 
        	+  "   CONSTRAINT WISH_PK PRIMARY KEY, " 
        	+  " ENTRY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "
        	+  " WISH_ITEM VARCHAR(32) NOT NULL) " ;
        
        stmt.executeUpdate(createString);
        PreparedStatement ps = prepareStatement("insert into WISH_LIST(WISH_ITEM) values (?)");
        //this won't raise any errors because there is one parameter in ps
        ps.setString(1, "aaa");
        ps.executeUpdate();
        
        //Negative test case. There are no parameter in the following ps
        ps = prepareStatement("insert into WISH_LIST(WISH_ITEM) values ('bb')");
        //Try setString when no parameters in ps
        try {
        	ps.setString(1, "aaa");
            fail("Exception expected above!");
        } catch (SQLException e)  {  
        	if (usingDerbyNetClient())
        		//note that SQLState is XCL14. For setObject below, the 
        		//SQLState is XCL13. I have entered DERBY-3139 for this
        		//difference in SQLState.
        		assertSQLState("XCL14", e);
        	else
        		assertSQLState("07009", e);
        }
        //Try setObject when no parameters in ps
        try {
        	ps.setObject(1,"cc",java.sql.Types.VARCHAR); 
            fail("Exception expected above!");
        } catch (SQLException e)  {   
    		assertSQLState("07009", e);
        }
    }

    /**
     * Test two different bugs regarding the handling of large
     * amounts of parameter data: first, the Network Server was incorrectly
     * handling the desegmentation of continued DSS segments, and second,
     * the Network Server was using the wrong heuristic to determine whether
     * long string data was being flowed in-line or externalized (Jira 1533).
     *
     * Tests "a" and "b" provoke two different forms of this problem, one
     * with just a single continued segment, and one with several continuations
     */
    public void testLargeParameters_a() throws Exception
    {
        Statement stmt = createStatement();
        stmt.execute("create table jira1533_a ("
                     + "aa BIGINT NOT NULL, "
                     + "bbbbbb BIGINT DEFAULT 0 NOT NULL,"
                     + "cccc  VARCHAR(40), ddddddddddd BIGINT, "
                     + "eeeeee VARCHAR(128), ffffffffffffffffff VARCHAR(128),"
                     + "ggggggggg  BLOB(2G), hhhhhhhhh VARCHAR(128), "
                     + "iiiiiiii VARCHAR(128), jjjjjjjjjjjjjj BIGINT,"
                     + "kkkkkkkk CHAR(1) DEFAULT 'f', "
                     + "llllllll CHAR(1) DEFAULT 'f', "
                     + "mmmmmmmmmmmmm  CHAR(1) DEFAULT 'f')");
         stmt.close();

         PreparedStatement ps = prepareStatement(
             "INSERT INTO jira1533_a (aa, bbbbbb, cccc, ddddddddddd, eeeeee,"
             + "                      ffffffffffffffffff,"
             + "                      ggggggggg, hhhhhhhhh, iiiiiiii, "
             + "                      jjjjjjjjjjjjjj, kkkkkkkk,"
             + "                      llllllll,mmmmmmmmmmmmm)"
             + "          VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)");
         String blobStr = makeString(32584);
         ps.setLong(1, 5);
         ps.setLong(2, 1);
         ps.setString(3, "AAAAAAAAAAA");
         ps.setLong(4, 30000);
         ps.setString(5, "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
         ps.setString(6, "AAAAAAAAAAA");
         ps.setBytes(7, blobStr.getBytes());
         ps.setString(8, "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
         ps.setString(9, "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
         ps.setLong(10, 1);
         ps.setString(11, "1");
         ps.setString(12, "1");
         ps.setString(13, "1");
         ps.execute();
         ps.close();
    }

    /**
     *  @see #testLargeParameters_a()
     */
    public void testLargeParameters_b() throws Exception
    {
        Statement stmt = createStatement();
        stmt.execute("create table jira1533_b ("
                     + "aa BIGINT NOT NULL, bbbbbb BIGINT DEFAULT 0 NOT NULL, "
                     + "cccc VARCHAR(40), ddddddddddd BIGINT, "
                     + "eeeeee VARCHAR(128), ffffffffffffffffff VARCHAR(128), "
                     + "g1 BLOB(2G), g2 BLOB(2G), g3 BLOB(2G), g4 BLOB(2G), "
                     + "ggggggggg  BLOB(2G), hhhhhhhhh VARCHAR(128), "
                     + "iiiiiiii VARCHAR(128), jjjjjjjjjjjjjj BIGINT,"
                     + "kkkkkkkk CHAR(1) DEFAULT 'f', "
                     + "llllllll CHAR(1) DEFAULT 'f', "
                     + "mmmmmmmmmmmmm  CHAR(1) DEFAULT 'f')");
        stmt.close();

        PreparedStatement ps = prepareStatement(
            "INSERT INTO jira1533_b (aa, bbbbbb, cccc, ddddddddddd, eeeeee,"
            + "                      ffffffffffffffffff,"
            + "                      g1, g2, g3, g4,"
            + "                      ggggggggg, hhhhhhhhh, iiiiiiii,"
            + "                      jjjjjjjjjjjjjj, kkkkkkkk,"
            + "                      llllllll,mmmmmmmmmmmmm)"
            + "          VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        String blobStr = makeString(32584);
        ps.setLong(1, 5);
        ps.setLong(2, 1);
        ps.setString(3, "AAAAAAAAAAA");
        ps.setLong(4, 30000);
        ps.setString(5, "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
        ps.setString(6, "AAAAAAAAAAA");
        ps.setBytes(7, blobStr.getBytes());
        ps.setBytes(8, blobStr.getBytes());
        ps.setBytes(9, blobStr.getBytes());
        ps.setBytes(10 ,blobStr.getBytes());
        ps.setBytes(11 ,blobStr.getBytes());
        ps.setString(12, "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
        ps.setString(13, "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
        ps.setLong(14, 1);
        ps.setString(15, "1");
        ps.setString(16, "1");
        ps.setString(17, "1");
        ps.execute();
        ps.close();
    }

    /**
     * Test fix for protocol error if splitQRYDTA occurs during DRDAConnThread.doneData()
     * DERBY-3230
     * @throws SQLException
     */
    public void testDerby3230() throws SQLException {
        Statement s = createStatement();
        s.executeUpdate("CREATE TABLE TAB (col1 varchar(32672) NOT NULL)");
        PreparedStatement ps = prepareStatement("INSERT INTO TAB VALUES(?)");
        ps.setString(1,makeString(15000));
        ps.executeUpdate();
        ps.setString(1,makeString(7500));
        ps.executeUpdate();
        ps.setString(1,makeString(5000));
        ps.executeUpdate();
        ps.setString(1,makeString(2000));
        ps.executeUpdate();
        ps.setString(1,makeString(1600));
        ps.executeUpdate();
        ps.setString(1,makeString(800));
        ps.executeUpdate();
        ps.setString(1,makeString(400));
        ps.executeUpdate();
        ps.setString(1,makeString(200));
        ps.executeUpdate();
        ps.setString(1,makeString(100));
        ps.executeUpdate();
        ps.setString(1,makeString(56));
        ps.executeUpdate();
            
        ResultSet rs = s.executeQuery("SELECT * from tab");
        // drain the resultset
        JDBC.assertDrainResults(rs);
                   
    }
    /**
     * Return a string of the given length.  The string will contain just 'X'
     * characters.
     *
     * @param length Length of string to be returned.
     * @return String of given length.
     */
    private static String makeString(int length)
    {
        return makeString(length, 'X');
    }

    /**
     * Return a string of the given length filled with the specified character.
     */
    private static String makeString(int length, char ch) {
        char[] buf = new char[length];
        Arrays.fill(buf, ch);
        return new String(buf);
    }

    /**
     * Test fix for DERBY-4088 where an ArrayIndexOutOfBoundsException was
     * thrown by DDMReader.readBytes() when reading a BLOB value whose length
     * was close to the maximum length of a DSS.
     */
    public void testReadBlobCloseToMaxDssLength() throws Exception {
        final int length = 32766; // max DSS length is 32767

        // Create test data with the requested length
        DataInputStream stream1 =
                new DataInputStream(new LoopingAlphabetStream(length));
        byte[] bytes = new byte[length];
        stream1.readFully(bytes);

        // See if the test data can be sent to the server and back with
        // no errors.
        PreparedStatement ps = prepareStatement("values cast(? as blob)");
        ps.setBytes(1, bytes);
        ResultSet rs = ps.executeQuery();
        assertTrue("empty result set", rs.next());
        InputStream stream2 = rs.getBinaryStream(1);
        assertEquals(new LoopingAlphabetStream(length), stream2);
        assertFalse("too many rows", rs.next());
        rs.close();
    }

    /**
     * Verify that string values aren't truncated when their UTF-8 encoded
     * representation exceeds 32KB. DERBY-5236.
     */
    public void testLongColumn() throws Exception {
        PreparedStatement ps = prepareStatement(
                "values cast(? as varchar(32672))");

        String s1 = makeString(20000, '\u4e10');
        ps.setString(1, s1);
        JDBC.assertSingleValueResultSet(ps.executeQuery(), s1);

        // 64K-1 bytes, should be OK.
        String s2 =
                s1 + makeString(64 * 1024 - s1.getBytes("UTF-8").length - 1);
        ps.setString(1, s2);
        JDBC.assertSingleValueResultSet(ps.executeQuery(), s2);

        // 64K bytes, will be truncated to 64K-1 by the client driver because
        // of limitation in the protocol.
        String s3 = s2 + 'X';
        ps.setString(1, s3);
        if (usingDerbyNetClient()) {
            String expected = s3.substring(0, s3.length() - 1);
            ResultSet rs = ps.executeQuery();
            assertTrue("Empty result", rs.next());
            assertDataTruncation(
                    new String[] { expected },
                    new String[] { rs.getString(1) },
                    1, false, true, s3.length(), expected.length(),
                    rs.getWarnings());
            assertFalse("Too many rows", rs.next());
            rs.close();
        } else {
            // Embedded is OK. No truncation.
            JDBC.assertSingleValueResultSet(ps.executeQuery(), s3);
        }

        // 64K+1 bytes, will be truncated by the client driver because of
        // limitation in the protocol. Should be truncated to to 64K-2 to
        // match the character boundary.
        String s4 = s3.substring(0, s3.length() - 2) + '\u4e10';
        ps.setString(1, s4);
        if (usingDerbyNetClient()) {
            String expected = s4.substring(0, s4.length() - 1);
            ResultSet rs = ps.executeQuery();
            assertTrue("Empty result", rs.next());
            assertDataTruncation(
                    new String[] { expected },
                    new String[] { rs.getString(1) },
                    1, false, true, s4.length(), expected.length(),
                    rs.getWarnings());
            assertFalse("Too many rows", rs.next());
            rs.close();
        } else {
            // Embedded is OK. No truncation.
            JDBC.assertSingleValueResultSet(ps.executeQuery(), s4);
        }

        // Try two columns at 64K+1 bytes. Expect same result as above.
        PreparedStatement ps2 = prepareStatement(
                "values (cast(? as varchar(32672)), " +
                "cast(? as varchar(32672)))");
        ps2.setString(1, s4);
        ps2.setString(2, s4);
        if (usingDerbyNetClient()) {
            String expected = s4.substring(0, s4.length() - 1);
            ResultSet rs = ps2.executeQuery();
            assertTrue("Empty result", rs.next());
            // We should actually have received two warnings here, but the
            // network client driver currently only supports one warning.
            assertDataTruncation(
                    new String[] { expected, expected },
                    new String[] { rs.getString(1), rs.getString(2) },
                    1, false, true, s4.length(), expected.length(),
                    rs.getWarnings());
            assertFalse("Too many rows", rs.next());
            rs.close();
        } else {
            String[][] expectedRow = {{s4, s4}};
            JDBC.assertFullResultSet(ps2.executeQuery(), expectedRow);
        }

        // Now test 64KB in a procedure call. Will be truncated to 64KB-1 on
        // the network client.
        Statement s = createStatement();
        s.execute("create procedure derby_5236_proc" +
                  "(in x varchar(32672), out y varchar(32672))" +
                  "language java parameter style java external name '" +
                  getClass().getName() + ".copyString'");
        CallableStatement cs = prepareCall("call derby_5236_proc(?,?)");
        cs.setString(1, s3);
        cs.registerOutParameter(2, Types.VARCHAR);
        cs.execute();
        if (usingDerbyNetClient()) {
            assertDataTruncation(
                    new String[] { s3.substring(0, s3.length() - 1) },
                    new String[] { cs.getString(2) },
                    2, true, true, s3.length(), s3.length() - 1,
                    cs.getWarnings());
        } else {
            assertEquals(s3, cs.getString(2));
        }
    }

    /**
     * Copy a string value from {@code in} to {@code out[0}}. Used as a
     * stored procedure in {@link #testLongColumn()}.
     *
     * @param in stored procedure input parameter
     * @param out stored procedure output parameter
     */
    public static void copyString(String in, String[] out) {
        out[0] = in;
    }

    /**
     * Assert that data returned from the server was truncated, and that the
     * proper warning came with the result.
     *
     * @param expectedRow the expected values
     * @param actualRow   the actual values returned
     * @param index       the expected column/parameter index in the warning
     * @param parameter   whether the values came from a procedure parameter
     * @param read        whether the values came from a read operation
     * @param dataSize    the expected full size of the truncated value
     * @param transferSize the expected size of the value after truncation
     * @param warning     the received warning
     */
    private static void assertDataTruncation(
            String[] expectedRow, String[] actualRow,
            int index, boolean parameter, boolean read,
            int dataSize, int transferSize, SQLWarning warning) {
        assertEquals("Wrong number of columns",
                     expectedRow.length, actualRow.length);
        assertNotNull("Expected data truncation warning", warning);
        for (int i = 0; i < expectedRow.length; i++) {
            assertEquals("column #" + (i + 1), expectedRow[i], actualRow[i]);

            if (warning instanceof DataTruncation) {
                DataTruncation dt = (DataTruncation) warning;
                assertEquals("index", index, dt.getIndex());
                assertEquals("parameter", parameter, dt.getParameter());
                assertEquals("read", read, dt.getRead());
                assertEquals("dataSize", dataSize, dt.getDataSize());
                assertEquals("transferSize", transferSize, dt.getTransferSize());
            } else {
                fail("Unexpected warning", warning);
            }

            assertNull("Chained warnings not expected on network client",
                       warning.getNextWarning());
        }
    }
}