/* 

   Derby - Class org.apache.derbyTesting.functionTests.tests.store.StreamingColumnTest

   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.store;

import java.io.ByteArrayInputStream;
import java.io.CharArrayReader;
import java.io.File;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Properties;
import junit.framework.Test;
import org.apache.derbyTesting.functionTests.util.Formatters;
import org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests;
import org.apache.derbyTesting.functionTests.util.TestUtil;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
import org.apache.derbyTesting.junit.SupportFilesSetup;
import org.apache.derbyTesting.junit.SystemPropertyTestSetup;
import org.apache.derbyTesting.junit.TestConfiguration;

/**
 * Test of JDBC result set Stream calls.
 * 
 */
public class StreamingColumnTest extends BaseJDBCTestCase {

    public static final int DB2_LONGVARCHAR_MAXWIDTH = 32700;
    public static final int DB2_VARCHAR_MAXWIDTH = 32672;

    public StreamingColumnTest(String name) {
        super(name);
    }

    // set up a short (fit in one page) inputstream for insert
    static String[] fileName;
    static long[] fileLength;

    static {
        int numFiles = 4;
        fileName = new String[numFiles];
        fileLength = new long[numFiles];

        fileName[0] = "extin/short.data"; // set up a short (fit in one page)
        // inputstream for insert
        fileName[1] = "extin/shortbanner"; // set up a long (longer than a
        // page) inputstream for insert
        fileName[2] = "extin/derby.banner"; // set up a really long (over 300K)
        // inputstream for insert
        fileName[3] = "extin/empty.data"; // set up a file with nothing in it
    }

    private static final int LONGVARCHAR = 1;
    private static final int CLOB = 2;
    private static final int VARCHAR = 3;

    /**
     * Test inserting data into long varchar columns from FileInputStreams, and
     * retrieving them back as InputStreams. Check if the length of the
     * retrieved streams is the same as their respective files. Also, retrieve
     * as Strings and compare lengths. <p/> Check insertion and updating of long
     * strings.
     * 
     * @throws Exception
     *             If an unexpected error occurs.
     */
    public void testStream1() throws Exception {
        ResultSet rs;
        Statement stmt;
        stmt = createStatement();
        for (int i = 0; i < fileName.length; i++) {

            // prepare an InputStream from the file
            final File file = new File(fileName[i]);
            fileLength[i] = PrivilegedFileOpsForTests.length(file);
            InputStream fileIn = PrivilegedFileOpsForTests
                    .getFileInputStream(file);
            println("===>  testing " + fileName[i] + " with length = "
                    + fileLength[i]);

            // insert a streaming column
            PreparedStatement ps = prepareStatement("insert into testLongVarChar1 values(?, ?)");
            ps.setInt(1, 100 + i);
            ps.setAsciiStream(2, fileIn, (int) fileLength[i]);
            try {// if trying to insert data > 32700, there will be an
                // exception
                ps.executeUpdate();
                if (DB2_LONGVARCHAR_MAXWIDTH < fileLength[i]) {
                    fail("Attempting to insert data longer than "
                            + DB2_LONGVARCHAR_MAXWIDTH + " should have thrown"
                            + "an exception.");
                }
                println("No truncation and hence no error");
            } catch (SQLException e) {
                if (fileLength[i] > DB2_LONGVARCHAR_MAXWIDTH) {
                    if (i == 2 && usingDerbyNetClient()) {
                        assertSQLState("XJ001", e); // was getting data longer
                        // than maxValueAllowed
                    } else {
                        assertSQLState("22001", e); // was getting data longer
                        // than maxValueAllowed
                    }
                    println("expected exception for data > "
                            + DB2_LONGVARCHAR_MAXWIDTH + " in length");
                } else {
                    throw e;
                }
            } finally {
                fileIn.close();
            }
        }

        rs = stmt.executeQuery("select a, b from testLongVarChar1");
        byte[] buff = new byte[128];

        // fetch all rows back, get the long varchar columns as streams.
        while (rs.next()) {

            // get the first column as an int
            int a = rs.getInt("a");

            // get the second column as a stream
            InputStream fin = rs.getAsciiStream(2);
            int columnSize = 0;
            for (;;) {
                int size = fin.read(buff);
                if (size == -1)
                    break;
                columnSize += size;
            }
            verifyLength(a, columnSize, fileLength);
        }

        rs = stmt.executeQuery("select a, b from testLongVarChar1 order by a");
        rs.getMetaData();

        // fetch all rows back in order, get the long varchar columns as
        // streams.
        while (rs.next()) {
            // get the first column as an int
            int a = rs.getInt("a");
            // get the second column as a stream
            InputStream fin = rs.getAsciiStream(2);
            int columnSize = 0;
            for (;;) {
                int size = fin.read(buff);
                if (size == -1)
                    break;
                columnSize += size;
            }
            verifyLength(a, columnSize, fileLength);
        }

        rs = stmt.executeQuery("select a, b from testLongVarChar1");
        // fetch all rows back, get the long varchar columns as Strings.
        while (rs.next()) {
            // JDBC columns use 1-based counting

            // get the first column as an int
            int a = rs.getInt("a");

            // get the second column as a string
            String resultString = rs.getString(2);
            verifyLength(a, resultString.length(), fileLength);
        }

        rs = stmt.executeQuery("select a, b from testLongVarChar1 "
                + "order by a");
        // fetch all rows back in order, get the long varchar columns as
        // Strings.
        while (rs.next()) {

            // JDBC columns use 1-based counting
            // get the first column as an int
            int a = rs.getInt("a");

            // get the second column as a string
            String resultString = rs.getString(2);
            verifyLength(a, resultString.length(), fileLength);
        }

        // should return one row.
        rs = stmt.executeQuery("select a, b from testLongVarChar1 "
                + "where b like 'test data: a string column inserted "
                + "as an object'");

        while (rs.next()) {

            // JDBC columns use 1-based counting
            // get the first column as an int
            int a = rs.getInt("a");

            // get the second column as a string
            String resultString = rs.getString(2);
            verifyLength(a, resultString.length(), fileLength);
        }

        // tests on table foo1
        insertLongString(10, "ssssssssss", false, "foo1");
        insertLongString(0, "", false, "foo1");
        insertLongString(1, "1", false, "foo1");
        insertLongString(-1, null, false, "foo1");
        insertLongString(20, "XXXXXXXXXXXXXXXXXXXX", false, "foo1");

        rs = stmt.executeQuery("select a, b from foo1");

        println("Expect to get null string back");
        while (rs.next()) {
            int a = rs.getInt("a");
            String resultString = rs.getString(2);
            assertEquals("FAIL - failed to get string back, expect "
                    + a
                    + " got "
                    + (resultString == null ? "null resultString" : ""
                            + resultString.length()), a,
                    resultString == null ? -1 : resultString.length());
        }

        updateLongString(1, 3000, "foo1");
        updateLongString(0, 800, "foo1");
        updateLongString(3000, 0, "foo1");
        updateLongString(0, 51, "foo1");
        updateLongString(20, 0, "foo1");
        rs = stmt.executeQuery("select a, b from foo1");
        while (rs.next()) {
            int a = rs.getInt("a");
            String resultString = rs.getString(2);
            assertEquals("FAIL - failed to get string back, expect "
                    + a
                    + " got "
                    + (resultString == null ? "null resultString" : ""
                            + resultString.length()), a,
                    resultString == null ? -1 : resultString.length());
        }
        rs.close();
        stmt.close();
    }

    /**
     * test column size 1500 bytes. Run streamTest2 with padding length 1500.
     * 
     * @throws Exception
     */
    public void testStream2_1500() throws Exception {
        long length = 1500;
        streamTest2(length, "foo2_1500");
    }

    /**
     * test column size 5000 bytes.Run streamTest2 with padding length 5000
     * 
     * @throws Exception
     */
    public void testStream2_5000() throws Exception {
        long length = 5000;
        streamTest2(length, "foo2_5000");
    }

    /**
     * test column size 10000 bytes. Run streamTest2 with padding length 10000
     * 
     * @throws Exception
     */
    public void testStream2_10000() throws Exception {
        long length = 10000;
        streamTest2(length, "foo2_10000");
    }

    /**
     * Insert strings padded with various lengths and verify their existence.
     * 
     * @param length
     *            How long the string should be padded.
     * @param tableName
     *            The table to enter the strings.
     * @throws Exception
     *             If any unexpected errors occur.
     */
    private void streamTest2(long length, String tableName) throws Exception {
        Statement sourceStmt = createStatement();

        insertLongString(1, pad("Broadway", length), false, tableName);
        insertLongString(2, pad("Franklin", length), false, tableName);
        insertLongString(3, pad("Webster", length), false, tableName);

        sourceStmt.executeUpdate("insert into " + tableName + " select a+100, "
                + "b from " + tableName);

        verifyExistence(1, "Broadway", length, tableName);
        verifyExistence(2, "Franklin", length, tableName);
        verifyExistence(3, "Webster", length, tableName);
        verifyExistence(101, "Broadway", length, tableName);
        verifyExistence(102, "Franklin", length, tableName);
        verifyExistence(103, "Webster", length, tableName);
    }

    /**
     * Run streamTest3 with padding length 0
     * 
     * @throws Exception
     */
    public void testStream3_0() throws Exception {
        final long length = 0;
        final String tableName = "foo3_0";
        streamTest3(length, tableName);
    }

    /**
     * Run streamTest3 with padding length 1500
     * 
     * @throws Exception
     */
    public void testStream3_1500() throws Exception {
        final long length = 1500;
        final String tableName = "foo3_1500";
        streamTest3(length, tableName);
    }

    /**
     * Run streamTest3 with padding length 5000
     * 
     * @throws Exception
     */
    public void testStream3_5000() throws Exception {
        final long length = 5000;
        final String tableName = "foo3_5000";
        streamTest3(length, tableName);
    }

    /**
     * Run streamTest3 with padding length 10000
     * 
     * @throws Exception
     */
    public void testStream3_10000() throws Exception {
        final long length = 10000;
        final String tableName = "foo3_10000";
        streamTest3(length, tableName);
    }

    /**
     * Similar to streamTest2 apart from the insertion of file data as ascii
     * streams.
     * 
     * @param length
     * @param tableName
     * @throws Exception
     */
    private void streamTest3(final long length, final String tableName)
            throws Exception {
        insertLongString(1, pad("Broadway", length), false, tableName);
        insertLongString(2, pad("Franklin", length), false, tableName);
        insertLongString(3, pad("Webster", length), false, tableName);
        PreparedStatement ps = prepareStatement("update " + tableName + " set "
                + "a=a+1000, b=? where a<99 and a in (select a from "
                + tableName + ")");

        File file = new File("extin/short.data");
        InputStream fileIn = PrivilegedFileOpsForTests.getFileInputStream(file);
        ps.setAsciiStream(1, fileIn, (int) (PrivilegedFileOpsForTests
                .length(file)));
        ps.executeUpdate();
        fileIn.close();

        ps = prepareStatement("update " + tableName
                + " set a=a+1000, b=? where a<99 and a " + "in (select a from "
                + tableName + ")");
        file = new File("extin/shortbanner");
        fileIn = PrivilegedFileOpsForTests.getFileInputStream(file);
        ps.setAsciiStream(1, fileIn, (int) (PrivilegedFileOpsForTests
                .length(file)));
        ps.executeUpdate();
        fileIn.close();
    }

    /**
     * Insert data from streams to a BLOB field.
     * 
     * @throws Exception
     */
    public void testStream4() throws Exception {

        ResultSet rs;
        Statement stmt;
        stmt = createStatement();

        // insert an empty string
        stmt.execute("insert into testLongVarBinary4 values(1, CAST ("
                + TestUtil.stringToHexLiteral("") + "AS BLOB(1G)))");

        // insert a short text string
        stmt.execute("insert into testLongVarBinary4 values(2,CAST ("
                + TestUtil.stringToHexLiteral("test data: a string column "
                        + "inserted as an object") + "AS BLOB(1G)))");

        for (int i = 0; i < fileName.length; i++) {

            // prepare an InputStream from the file
            File file = new File(fileName[i]);
            fileLength[i] = PrivilegedFileOpsForTests.length(file);
            InputStream fileIn = PrivilegedFileOpsForTests
                    .getFileInputStream(file);

            println("Testing with " + fileName[i] + " length = "
                    + fileLength[i]);

            // insert a streaming column
            PreparedStatement ps = prepareStatement("insert into testLongVarBinary4 values(?, ?)");
            ps.setInt(1, 100 + i);
            ps.setBinaryStream(2, fileIn, (int) fileLength[i]);
            ps.executeUpdate();
            fileIn.close();
        }

        rs = stmt.executeQuery("select a, b from testLongVarBinary4");
        rs.getMetaData();
        byte[] buff = new byte[128];

        // fetch all rows back, get the long varchar columns as streams.
        while (rs.next()) {

            // get the first column as an int
            int a = rs.getInt("a");

            // get the second column as a stream
            InputStream fin = rs.getBinaryStream(2);
            int columnSize = 0;
            for (;;) {
                int size = fin.read(buff, 0, 100);
                if (size == -1)
                    break;
                columnSize += size;
            }
            verifyLength(a, columnSize, fileLength);
        }

        rs = stmt
                .executeQuery("select a, b from testLongVarBinary4 order by a");
        rs.getMetaData();

        // fetch all rows back in order, get the long varchar columns as
        // streams.
        while (rs.next()) {

            // get the first column as an int
            int a = rs.getInt("a");

            // get the second column as a stream
            InputStream fin = rs.getBinaryStream(2);
            int columnSize = 0;
            for (;;) {
                int size = fin.read(buff);
                if (size == -1)
                    break;
                columnSize += size;
            }
            verifyLength(a, columnSize, fileLength);
        }

        rs = stmt.executeQuery("select a, b from testLongVarBinary4");

        // fetch all rows back, get the long varchar columns as Strings.

        while (rs.next()) {

            // JDBC columns use 1-based counting
            // get the first column as an int
            int a = rs.getInt("a");

            // get the second column as a string
            String resultString = rs.getString(2);

            // dividing string length by 2 as the binary column's hex digits are
            // each represented by a character
            // e.g. the hex number 0xA0 would be represented by a string of
            // length 2, "AO"
            verifyLength(a, resultString.length() / 2, fileLength);
        }

        rs = stmt
                .executeQuery("select a, b from testLongVarBinary4 order by a");
        // fetch all rows back in order, get the long varchar columns as
        // Strings.

        while (rs.next()) {

            // JDBC columns use 1-based counting
            // get the first column as an int
            int a = rs.getInt("a");

            // get the second column as a string
            String resultString = rs.getString(2);
            println(resultString);

            // dividing string length by 2 as the binary column's hex digits are
            // each represented by a character
            // e.g. the hex number 0xA0 would be represented by a string of
            // length 2, "AO"
            verifyLength(a, resultString.length() / 2, fileLength);
        }

        rs.close();
        stmt.close();
    }

    /**
     * Run streamTest5 with padding length 0.
     * 
     * @throws Exception
     */
    public void testStream5_0() throws Exception {
        final long length = 0;
        final String tableName = "foo5_0";
        streamTest5(length, tableName);
    }

    /**
     * Run streamTest5 with padding length 1500.
     * 
     * @throws Exception
     */
    public void testStream5_1500() throws Exception {
        final long length = 1500;
        final String tableName = "foo5_1500";
        streamTest5(length, tableName);
    }

    /**
     * Run streamTest5 with padding length 5000.
     * 
     * @throws Exception
     */
    public void testStream5_5000() throws Exception {
        final long length = 5000;
        final String tableName = "foo5_5000";
        streamTest5(length, tableName);
    }

    /**
     * Run streamTest5 with padding length 100000.
     * 
     * @throws Exception
     */
    // This test fails when running w/ derby.language.logStatementText=true
    // see DERBY-595
    // public void testStream5_100000() throws Exception {
    // final long length = 100000;
    // final String tableName = "foo5_100000";
    // streamTest5(length, tableName);
    // }
    /**
     * If length &gt; 32700 insert to a BLOB field. Else, a long varchar field.
     * 
     * @param length
     *            Padding length
     * @param tableName
     *            Name of table
     * @throws Exception
     */
    private void streamTest5(long length, String tableName) throws Exception {
        InputStream fileIn = null;
        try {
            insertLongString(1, pad("Broadway", length), true, tableName);
            insertLongString(2, pad("Franklin", length), true, tableName);
            insertLongString(3, pad("Webster", length), true, tableName);
            insertLongString(4, pad("Broadway", length), true, tableName);
            insertLongString(5, pad("Franklin", length), true, tableName);
            insertLongString(6, pad("Webster", length), true, tableName);
            PreparedStatement ps = prepareStatement("update " + tableName
                    + " set a=a+1000, "
                    + "b=? where a<99 and a in (select a from " + tableName
                    + ")");
            File file = new File("extin/short.data");
            fileIn = PrivilegedFileOpsForTests.getFileInputStream(file);
            ps.setBinaryStream(1, fileIn, (int) (PrivilegedFileOpsForTests
                    .length(file)));
            ps.executeUpdate();
            fileIn.close();

            ps = prepareStatement("update " + tableName
                    + " set a=a+1000, b=? where a<99 "
                    + "and a in (select a from " + tableName + ")");
            file = new File("extin/shortbanner");
            fileIn = PrivilegedFileOpsForTests.getFileInputStream(file);
            ps.setBinaryStream(1, fileIn, (int) (PrivilegedFileOpsForTests
                    .length(file)));
            ps.executeUpdate();
            ps.close();
        } finally {
            fileIn.close();
        }
    }

    /**
     * Test getting a ByteArrayInputStream from data and inserting.
     * 
     * @throws Exception
     */
    public void testStream6() throws Exception {
        final long length = 5000;
        final String tableName = "foo_6";
        Statement sourceStmt = createStatement();

        insertLongString(1, pad("Broadway", length), false, tableName);
        insertLongString(2, pad("Franklin", length), false, tableName);
        insertLongString(3, pad("Webster", length), false, tableName);
        PreparedStatement ps = prepareStatement("update foo_6 set a=a+1000, "
                + "b=? where a<99 and a in (select a from foo_6)");

        streamInLongCol(ps, pad("Grand", length));
        ps.close();
        sourceStmt.close();
    }

    /**
     * Test insertion of a long string to a long varchar field in a table
     * created with pagesize 1024.
     * 
     * @throws Exception
     */
    public void testStream7() throws Exception {
        setAutoCommit(false);
        println("streamTest7");

        PreparedStatement ps1 = prepareStatement("insert into testlvc7 values (?, "
                + "'filler for column b on null column', null, 'filler for column d')");
        PreparedStatement ps2 = prepareStatement("insert into testlvc7 values (?, "
                + "'filler for column b on empty string column', ?, 'filler2 for column d')");

        for (int i = 0; i < 100; i++) {
            ps1.setInt(1, i);
            ps1.executeUpdate();

            ByteArrayInputStream emptyString = new ByteArrayInputStream(
                    new byte[0]);
            ps2.setInt(1, i);
            ps2.setAsciiStream(2, emptyString, 0);
            ps2.executeUpdate();
        }
        ps1.close();
        ps2.close();

        commit();

        PreparedStatement ps = prepareStatement("update testlvc7 set lvc = ? where a = ?");

        String longString = "this is a relatively long string, hopefully "
                + "the row will be split or otherwise become long ???  "
                + "I don't think it will become long but maybe if it rolls "
                + "back it will become strange";
        for (int i = 0; i < 100; i++) {
            ByteArrayInputStream string1 = new ByteArrayInputStream(longString
                    .getBytes("US-ASCII"));
            ps.setAsciiStream(1, string1, longString.length());
            ps.setInt(2, i);
            ps.executeUpdate();
            if ((i % 2) == 0) {
                rollback();
            } else {
                commit();
            }

            ByteArrayInputStream emptyString = new ByteArrayInputStream(
                    new byte[0]);
            ps.setAsciiStream(1, emptyString, 0);
            ps.executeUpdate();
            if ((i % 3) == 0) {
                rollback();
            } else {
                commit();
            }
        }
        ps.close();
    }

    /**
     * long row test of insert/backout case, using setAsciiStream().
     * <p>
     * The heap tries to make rows all fit on one page if possible. So it first
     * asks raw store to try inserting without overflowing rows or columns. If
     * that doesn't work it then asks raw store for a mostly empty page and
     * tries to insert it there with overflow, If that doesn't work then an
     * empty page is picked.
     * <p>
     * If parameters are 10,2500 - then the second row inserted will have the
     * 1st column fit, but the second not fit which caused track #2240.
     * 
     */
    public void testStream8_10_2500() throws Exception {
        int stream1_len = 10;
        int stream2_len = 2500;
        String tableName = "t8_10_2500";
        println("Starting testStream8_10_2500(" + stream1_len + ", "
                + stream2_len + ")");
        streamTest8(stream1_len, stream2_len, tableName);
        println("Finishing testStream8_10_2500(" + stream1_len + ", "
                + stream2_len + ")");
    }

    /**
     * long row test of insert/backout case, using setAsciiStream().
     * <p>
     * The heap tries to make rows all fit on one page if possible. So it first
     * asks raw store to try inserting without overflowing rows or columns. If
     * that doesn't work it then asks raw store for a mostly empty page and
     * tries to insert it there with overflow, If that doesn't work then an
     * empty page is picked.
     * <p>
     * If parameters are 10,2500 - then the second row inserted will have the
     * 1st column fit, but the second not fit which caused track #2240.
     * 
     * @exception StandardException
     *                Standard exception policy.
     */
    public void testStream8_2500_10() throws Exception {
        int stream1_len = 2500;
        int stream2_len = 10;
        String tableName = "t8_2500_10";
        println("Starting streamTest8_2500_10(" + stream1_len + ", "
                + stream2_len + ")");
        streamTest8(stream1_len, stream2_len, tableName);
        println("Finishing streamTest8_2500_10(" + stream1_len + ", "
                + stream2_len + ")");
    }

    /**
     * Method called by testStream8_10_2500 and testStream8_2500_10
     * 
     * @param stream1_len
     *            Length of the 1st stream
     * @param stream2_len
     *            Length of the 2nd stream
     * @param tableName
     *            Name of table
     * @throws Exception
     */
    private void streamTest8(int stream1_len, int stream2_len, String tableName)
            throws Exception {
        println("Starting streamTest8(" + stream1_len + ", " + stream2_len
                + ")");

        ResultSet rs;
        Statement stmt;

        String insertsql = new String("insert into " + tableName
                + " values (?, ?, ?) ");

        int numStrings = 10;

        byte[][] stream1_byte_array = new byte[numStrings][];
        byte[][] stream2_byte_array = new byte[numStrings][];

        // make string size match input sizes.
        for (int i = 0; i < numStrings; i++) {
            stream1_byte_array[i] = new byte[stream1_len];

            for (int j = 0; j < stream1_len; j++)
                stream1_byte_array[i][j] = (byte) ('a' + i);

            stream2_byte_array[i] = new byte[stream2_len];
            for (int j = 0; j < stream2_len; j++)
                stream2_byte_array[i][j] = (byte) ('A' + i);
        }

        setAutoCommit(false);
        stmt = createStatement();

        PreparedStatement insert_ps = prepareStatement(insertsql);

        for (int i = 0; i < numStrings; i++) {
            // create the stream and insert it
            insert_ps.setInt(1, i);

            // create the stream and insert it
            insert_ps.setAsciiStream(2, new ByteArrayInputStream(
                    stream1_byte_array[i]), stream1_len);

            // create the stream and insert it
            insert_ps.setAsciiStream(3, new ByteArrayInputStream(
                    stream2_byte_array[i]), stream2_len);

            insert_ps.executeUpdate();

            // just force a scan of the table, no insert is done.
            String checkSQL = "insert into " + tableName + " select * from "
                    + tableName + " where a = -6363";
            stmt.execute(checkSQL);
        }

        insert_ps.close();
        commit();

        rs = stmt.executeQuery("select a, b, c from " + tableName);

        // should return one row.
        while (rs.next()) {

            // JDBC columns use 1-based counting
            // get the first column as an int
            int a = rs.getInt("a");

            // get the second column as a string
            String resultString = rs.getString(2);

            // compare result with expected, using fixed length string from
            // the streamed byte array
            String canon = new String(stream1_byte_array[a], "US-ASCII");

            assertEquals("FAIL -- bad result string:" + "canon: " + canon
                    + "resultString: " + resultString, 0, canon
                    .compareTo(resultString));

            // get the second column as a string
            resultString = rs.getString(3);

            // compare result with expected, using fixed length string from
            // the second streamed byte array.
            canon = new String(stream2_byte_array[a], "US-ASCII");

            assertEquals("FAIL -- bad result string:" + "canon: " + canon
                    + "resultString: " + resultString, 0, canon
                    .compareTo(resultString));
        }

        rs.close();

        stmt.execute("insert into " + tableName + " select * from " + tableName
                + " ");
        stmt.close();
        commit();

        println("Finishing streamTest8(" + stream1_len + ", " + stream2_len
                + ")");
    }

    /**
     * long row test of insert/backout case, using setBinaryStream().
     * <p>
     * The heap tries to make rows all fit on one page if possible. So it first
     * asks raw store to try inserting without overflowing rows or columns. If
     * that doesn't work it then asks raw store for a mostly empty page and
     * tries to insert it there with overflow, If that doesn't work then an
     * empty page is picked.
     * <p>
     * If input parameters are 10,2500 - then the second row inserted will have
     * the 1st column fit, but the second not fit which caused track #2240.
     * 
     * @exception StandardException
     *                Standard exception policy.
     */
    public void testStream9_10_2500() throws Exception {
        int stream1_len = 10, stream2_len = 2500;
        String tableName = "t9_10_2500";
        println("Starting testStream9_10_2500(" + stream1_len + ", "
                + stream2_len + ")");
        streamTest9(stream1_len, stream2_len, tableName);
        println("Finishing testStream_10_2500(" + stream1_len + ", "
                + stream2_len + ")");
    }

    /**
     * long row test of insert/backout case, using setBinaryStream().
     * <p>
     * The heap tries to make rows all fit on one page if possible. So it first
     * asks raw store to try inserting without overflowing rows or columns. If
     * that doesn't work it then asks raw store for a mostly empty page and
     * tries to insert it there with overflow, If that doesn't work then an
     * empty page is picked.
     * <p>
     * If input parameters are 10,2500 - then the second row inserted will have
     * the 1st column fit, but the second not fit which caused track #2240.
     * 
     * @exception StandardException
     *                Standard exception policy.
     */
    public void testStream9_2500_10() throws Exception {
        int stream1_len = 2500, stream2_len = 10;
        String tableName = "t9_2500_10";
        println("Starting streamTest9_2500_10(" + stream1_len + ", "
                + stream2_len + ")");
        streamTest9(stream1_len, stream2_len, tableName);
        println("Finishing testStream9_2500_10(" + stream1_len + ", "
                + stream2_len + ")");
    }

    /**
     * Metjod called by testStream9_10_2500 and testStream9_2500_10
     * 
     * @param stream1_len
     *            Length of 1st stream
     * @param stream2_len
     *            Length of 2nd stream
     * @param tableName
     *            name of table
     * @throws SQLException
     */
    private void streamTest9(int stream1_len, int stream2_len, String tableName)
            throws SQLException {
        ResultSet rs;
        Statement stmt;

        String insertsql = new String("insert into " + tableName
                + " values (?, ?, ?) ");
        int numStrings = 10;

        byte[][] stream1_byte_array = new byte[numStrings][];
        byte[][] stream2_byte_array = new byte[numStrings][];

        // make string size match input sizes.
        for (int i = 0; i < numStrings; i++) {
            stream1_byte_array[i] = new byte[stream1_len];

            for (int j = 0; j < stream1_len; j++)
                stream1_byte_array[i][j] = (byte) ('a' + i);

            stream2_byte_array[i] = new byte[stream2_len];
            for (int j = 0; j < stream2_len; j++)
                stream2_byte_array[i][j] = (byte) ('A' + i);
        }

        setAutoCommit(false);
        stmt = createStatement();
        PreparedStatement insert_ps = prepareStatement(insertsql);

        for (int i = 0; i < numStrings; i++) {
            // create the stream and insert it
            insert_ps.setInt(1, i);

            // create the stream and insert it
            insert_ps.setBinaryStream(2, new ByteArrayInputStream(
                    stream1_byte_array[i]), stream1_len);

            // create the stream and insert it
            insert_ps.setBinaryStream(3, new ByteArrayInputStream(
                    stream2_byte_array[i]), stream2_len);

            insert_ps.executeUpdate();

            // just force a scan of the table, no insert is done.
            String checkSQL = "insert into " + tableName + " select * from "
                    + tableName + " where a = -6363";
            stmt.execute(checkSQL);
        }

        insert_ps.close();
        commit();
        rs = stmt.executeQuery("select a, b, c from " + tableName);

        // should return one row.
        while (rs.next()) {
            // JDBC columns use 1-based counting

            // get the first column as an int
            int a = rs.getInt("a");

            // get the second column as a string
            byte[] resultString = rs.getBytes(2);

            // compare result with expected
            byte[] canon = stream1_byte_array[a];
            assertTrue("FAIL -- bad result byte array 1:" + "canon: " + canon
                    + "resultString: " + resultString, Arrays.equals(canon,
                    resultString));

            // get the second column as a string
            resultString = rs.getBytes(3);

            // compare result with expected
            canon = stream2_byte_array[a];
            assertTrue("FAIL -- bad result byte array 2:" + "canon: " + canon
                    + "resultString: " + resultString, Arrays.equals(canon,
                    resultString));
        }
        rs.close();
        stmt
                .execute("insert into " + tableName + " select * from "
                        + tableName);
        stmt.close();
        commit();

        println("Finishing streamTest9(" + stream1_len + ", " + stream2_len
                + ")");
    }

    /**
     * table with multiple indexes, indexes share columns table has more than 4
     * rows, insert stream into table compress table and verify that each index
     * is valid .
     */
    public void testStream10() throws Exception {
        Statement stmt;
        println("Test 10 starts from here");
        stmt = createStatement();

        // insert stream into table
        for (int i = 0; i < fileName.length; i++) {
            println("i:" + i + " fileName:" + fileName[i]);

            // prepare an InputStream from the file
            File file = new File(fileName[i]);
            fileLength[i] = PrivilegedFileOpsForTests.length(file);
            InputStream fileIn = PrivilegedFileOpsForTests
                    .getFileInputStream(file);
            println("===> testing " + fileName[i] + " length = "
                    + fileLength[i]);

            // insert a streaming column
            PreparedStatement ps = prepareStatement("insert into tab10 values(?, ?, ?)");
            ps.setInt(1, 100 + i);
            ps.setInt(2, 100 + i);
            ps.setAsciiStream(3, fileIn, (int) fileLength[i]);
            try {// if trying to insert data > 32700, there will be an
                // exception
                println(ps.toString());
                ps.executeUpdate();
                if (i == 2) {
                    fail("Length 414000 should have thrown a truncation error!");
                }
                println("No truncation and hence no error");
            } catch (SQLException e) {
                println(i + " " + fileName[i]);
                if (fileLength[i] > DB2_LONGVARCHAR_MAXWIDTH) {
                    if (usingDerbyNetClient() && i == 2) {
                        assertSQLState("XJ001", e);
                    } else {
                        assertSQLState("22001", e);
                    }
                    // was getting data longer than maxValueAllowed
                    println("expected exception for data > "
                            + DB2_LONGVARCHAR_MAXWIDTH + " in length");
                } else {
                    throw e;
                }
            } finally {
                fileIn.close();
            }
        }

        // execute the compress command
        CallableStatement cs = prepareCall("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)");
        cs.setString(1, "APP");
        cs.setString(2, "TESTLONGVARCHAR1");
        cs.setInt(3, 0);
        cs.execute();

        // do consistency checking
        stmt
                .execute("CREATE FUNCTION ConsistencyChecker() "
                        + "RETURNS VARCHAR(128) EXTERNAL NAME "
                        + "'org.apache.derbyTesting.functionTests.util.T_ConsistencyChecker.runConsistencyChecker' "
                        + "LANGUAGE JAVA PARAMETER STYLE JAVA");
        stmt.execute("VALUES ConsistencyChecker()");
        stmt.close();

        println("Test 10 ends here");
    }

    /**
     * Test the passing of negative values for stream lengths to various
     * setXXXStream methods.
     */
    public void testStream11() throws Exception {

        println("Test 11 - Can't pass negative length as the stream length "
                + "for various setXXXStream methods");

        // prepare an InputStream from the file
        File file = new File("extin/short.data");
        InputStream fileIn = PrivilegedFileOpsForTests.getFileInputStream(file);
        PreparedStatement ps = prepareStatement("insert into "
                + "testLongVarCharInvalidStreamLength11 values(?, ?, ?)");
        ps.setInt(1, 100);
        try {
            println("===> testing using setAsciiStream with -2 as length");

            // The follwoing test depends on patch for DERBY-3705 being applied
            // to pass
            ps.setAsciiStream(2, fileIn, -2); // test specifically for
            // Cloudscape bug 4250
            fail("FAIL -- should have gotten exception for -2 "
                    + "param value to setAsciiStream");
        } catch (SQLException e) {
            assertSQLState("XJ025", e);
            println("Expected exception:" + e.toString());
        } finally {
            fileIn.close();
        }

        Reader filer = new InputStreamReader(fileIn, "US-ASCII");
        try {
            println("===> testing using setCharacterStream with -1 as length");
            ps.setCharacterStream(2, filer, -1);
            fail("FAIL -- should have gotten exception for -1 param "
                    + "value to setCharacterStream");
        } catch (SQLException e) {
            assertSQLState("XJ025", e);
            println("PASS -- expected exception:" + e.toString());
        } finally {
            fileIn.close();
        }

        try {
            println("===> testing using setBinaryStream with -1 as length");
            ps.setBinaryStream(3, fileIn, -1);
            fail("FAIL -- should have gotten exception for -1 param "
                    + "value to setBinaryStream");
        } catch (SQLException e) {
            assertSQLState("XJ025", e);
            println("Expected exception:" + e.toString());
        } finally {
            fileIn.close();
        }
        println("Test 11 - negative stream length tests end in here");
    }

    /**
     * Test truncation from files with trailing blanks and non-blanks.
     */
    public void testStream12() throws Exception {

        ResultSet rs;
        Statement stmt;

        // The following 2 files are for testing the truncation in varchar.
        // only non-blank character truncation will throw an exception for
        // varchars.
        // max value allowed in varchars is 32672 characters long

        // set up a file 32675 characters long but with last 3 characters as
        // blanks
        String fileName1 = "extin/char32675trailingblanks.data";

        // set up a file 32675 characters long with 3 extra non-blank characters
        // trailing in the end
        String fileName2 = "extin/char32675.data";
        println("Test 12 - varchar truncation tests start from here");

        stmt = createStatement();
        String largeStringA16350 = new String(Formatters.repeatChar("a", 16350));
        String largeStringA16336 = new String(Formatters.repeatChar("a", 16336));
        PreparedStatement ps = prepareStatement("insert into testConcatenation12 values (?, ?, ?, ?)");
        ps.setString(1, largeStringA16350);
        ps.setString(2, largeStringA16350);
        ps.setString(3, largeStringA16336);
        ps.setString(4, largeStringA16336);
        ps.executeUpdate();

        ps = prepareStatement("insert into testVarChar12 values(?, ?)");

        // prepare an InputStream from the file which has 3 trailing blanks
        // in the end, so after blank truncation, there won't be any
        // overflow
        // try this using setAsciiStream, setCharacterStream, setString and
        // setObject
        insertDataUsingAsciiStream(ps, 1, fileName1, DB2_VARCHAR_MAXWIDTH, 12);
        insertDataUsingCharacterStream(ps, 2, fileName1, DB2_VARCHAR_MAXWIDTH,
                12);
        insertDataUsingStringOrObject(ps, 3, DB2_VARCHAR_MAXWIDTH, true, true,
                12);
        insertDataUsingStringOrObject(ps, 4, DB2_VARCHAR_MAXWIDTH, true, false,
                12);
        println("===> testing trailing blanks using concatenation");
        insertDataUsingConcat(stmt, 5, DB2_VARCHAR_MAXWIDTH, true, VARCHAR,
                "testConcatenation12");

        // prepare an InputStream from the file which has 3 trailing
        // non-blanks in the end, and hence there would be overflow
        // exception
        // try this using setAsciiStream, setCharacterStream, setString and
        // setObject
        insertDataUsingAsciiStream(ps, 6, fileName2, DB2_VARCHAR_MAXWIDTH, 12);
        insertDataUsingCharacterStream(ps, 7, fileName2, DB2_VARCHAR_MAXWIDTH,
                12);
        insertDataUsingStringOrObject(ps, 8, DB2_VARCHAR_MAXWIDTH, false, true,
                12);
        insertDataUsingStringOrObject(ps, 9, DB2_VARCHAR_MAXWIDTH, false,
                false, 12);
        println("===> testing trailing non-blank characters using concatenation");
        insertDataUsingConcat(stmt, 10, DB2_VARCHAR_MAXWIDTH, false, VARCHAR,
                "testConcatenation12");

        rs = stmt.executeQuery("select a, b from testVarChar12");
        streamTestDataVerification(rs, DB2_VARCHAR_MAXWIDTH);

        println("Test 12 - varchar truncation tests end in here");
    }

    /**
     * Test truncation from files to long varchar.
     * 
     * @throws Exception
     */
    public void testStream13() throws Exception {

        ResultSet rs;
        Statement stmt;

        // The following 2 files are for testing the truncation in long varchar.
        // any character truncation (including blanks characters) will throw an
        // exception for long varchars.
        // max value allowed in long varchars is 32700 characters long

        // set up a file 32703 characters long but with last 3 characters
        // as blanks
        String fileName1 = "extin/char32703trailingblanks.data";

        // set up a file 32703 characters long with 3 extra non-blank
        // characters trailing in the end
        String fileName2 = "extin/char32703.data";
        println("testStream13 - long varchar truncation tests start from here");
        stmt = createStatement();
        PreparedStatement ps = prepareStatement("insert into testLongVarChars13 values(?, ?)");

        // prepare an InputStream from the file which has 3 trailing blanks
        // in the end. For long varchar, this would throw a truncation error
        // try this using setAsciiStream, setCharacterStream, setString and
        // setObject
        insertDataUsingAsciiStream(ps, 1, fileName1, DB2_LONGVARCHAR_MAXWIDTH,
                13);
        insertDataUsingCharacterStream(ps, 2, fileName1,
                DB2_LONGVARCHAR_MAXWIDTH, 13);
        insertDataUsingStringOrObject(ps, 3, DB2_LONGVARCHAR_MAXWIDTH, true,
                true, 13);
        insertDataUsingStringOrObject(ps, 4, DB2_LONGVARCHAR_MAXWIDTH, true,
                false, 13);

        // bug 5600- Can't test data overflow in longvarchar using
        // concatenation because longvarchar concatenated string can't be
        // longer than 32700
        // println("===> testing trailing blanks using
        // concatenation");
        // insertDataUsingConcat(stmt, 5, DB2_LONGVARCHAR_MAXWIDTH,
        // true, LONGVARCHAR, "testLongVarChars13");

        // prepare an InputStream from the file which has 3 trailing
        // non-blanks in the end, and hence there would be overflow
        // exception
        // try this using setAsciiStream, setCharacterStream, setString and
        // setObject
        insertDataUsingAsciiStream(ps, 6, fileName2, DB2_LONGVARCHAR_MAXWIDTH,
                13);
        insertDataUsingCharacterStream(ps, 7, fileName2,
                DB2_LONGVARCHAR_MAXWIDTH, 13);
        insertDataUsingStringOrObject(ps, 7, DB2_LONGVARCHAR_MAXWIDTH, false,
                true, 13);
        insertDataUsingStringOrObject(ps, 9, DB2_LONGVARCHAR_MAXWIDTH, false,
                false, 13);

        // bug 5600 - Can't test data overflow in longvarchar using
        // concatenation because longvarchar concatenated string can't be
        // longer than 32700
        // println("===> testing trailing non-blank characters
        // using concatenation");
        // insertDataUsingConcat(stmt, 10, DB2_LONGVARCHAR_MAXWIDTH,
        // false, LONGVARCHAR, "testLongVarChars13");
        rs = stmt.executeQuery("select a, b from testLongVarChars13");
        streamTestDataVerification(rs, DB2_LONGVARCHAR_MAXWIDTH);

        println("Test 13 - long varchar truncation tests end in here");
    }

    /**
     * Test truncation behavior for clobs Test is similar to streamTest12 except
     * that this test tests for clob column
     * 
     */
    public void testStream14() throws Exception {

        ResultSet rs;
        Statement stmt;

        // The following 2 files are for testing the truncation in clob
        // only non-blank character truncation will throw an exception for clob.
        // max value allowed in clob is 2G-1

        // set up a file 32675 characters long but with last 3 characters as
        // blanks
        String fileName1 = "extin/char32675trailingblanks.data";

        // set up a file 32675 characters long with 3 extra non-blank characters
        // trailing in the end
        String fileName2 = "extin/char32675.data";

        println("testStream 14 - clob truncation tests start from here");

        stmt = createStatement();
        String largeStringA16350 = new String(Formatters.repeatChar("a", 16350));
        String largeStringA16336 = new String(Formatters.repeatChar("a", 16336));
        PreparedStatement ps = prepareStatement("insert into testConcatenation14 values (?, ?, ?, ?)");
        ps.setString(1, largeStringA16350);
        ps.setString(2, largeStringA16350);
        ps.setString(3, largeStringA16336);
        ps.setString(4, largeStringA16336);
        ps.executeUpdate();

        ps = prepareStatement("insert into testClob14 values(?, ?)");

        // prepare an InputStream from the file which has 3 trailing blanks
        // in the end, so after blank truncation, there won't be any
        // overflow
        // try this using setAsciiStream, setCharacterStream, setString and
        // setObject
        insertDataUsingAsciiStream(ps, 1, fileName1, DB2_VARCHAR_MAXWIDTH, 14);
        insertDataUsingCharacterStream(ps, 2, fileName1, DB2_VARCHAR_MAXWIDTH,
                14);
        insertDataUsingStringOrObject(ps, 3, DB2_VARCHAR_MAXWIDTH, true, true,
                14);
        insertDataUsingStringOrObject(ps, 4, DB2_VARCHAR_MAXWIDTH, true, false,
                14);
        println("testStream14 - Testing trailing blanks using concatenation");
        insertDataUsingConcat(stmt, 5, DB2_VARCHAR_MAXWIDTH, true, CLOB,
                "testConcatenation14");

        // prepare an InputStream from the file which has 3 trailing
        // non-blanks in the end, and hence there would be overflow
        // exception
        // try this using setAsciiStream, setCharacterStream, setString and
        // setObject
        insertDataUsingAsciiStream(ps, 6, fileName2, DB2_VARCHAR_MAXWIDTH, 14);
        insertDataUsingCharacterStream(ps, 7, fileName2, DB2_VARCHAR_MAXWIDTH,
                14);
        insertDataUsingStringOrObject(ps, 8, DB2_VARCHAR_MAXWIDTH, false, true,
                14);
        insertDataUsingStringOrObject(ps, 9, DB2_VARCHAR_MAXWIDTH, false,
                false, 14);
        println("testStream14 - Testing trailing non-blank characters using concatenation");
        insertDataUsingConcat(stmt, 10, DB2_VARCHAR_MAXWIDTH, false, CLOB,
                "testConcatenation14");

        rs = stmt.executeQuery("select a, b from testVarChar12");
        streamTestDataVerification(rs, DB2_VARCHAR_MAXWIDTH);

        println("Test 14 - clob truncation tests end in here");
    }

    /**
     * Streams are not re-used. This test tests the fix for DERBY-500. If an
     * update statement has multiple rows that is affected, and one of the
     * parameter values is a stream, the update will fail because streams are
     * not re-used.
     */
    public void testDerby500() throws Exception {
        Statement stmt;
        println("======================================");
        println("START  DERBY-500 TEST ");
        stmt = createStatement();
        setAutoCommit(false);
        PreparedStatement ps = prepareStatement("insert into test500 "
                + "values (?,?,?,?,?)");

        // insert 10 rows.
        int rowCount = 0;

        // use blob and clob values
        int len = 10000;
        byte buf[] = new byte[len];
        char cbuf[] = new char[len];
        char orig = 'c';
        for (int i = 0; i < len; i++) {
            buf[i] = (byte) orig;
            cbuf[i] = orig;
        }
        int randomOffset = 9998;
        buf[randomOffset] = (byte) 'e';
        cbuf[randomOffset] = 'e';
        println("Inserting rows ");
        for (int i = 0; i < 10; i++) {
            ps.setInt(1, i);
            ps.setString(2, "mname" + i);
            ps.setInt(3, 0);
            ps.setBinaryStream(4, new ByteArrayInputStream(buf), len);
            ps.setAsciiStream(5, new ByteArrayInputStream(buf), len);
            rowCount += ps.executeUpdate();
        }
        commit();
        println("Rows inserted =" + rowCount);

        PreparedStatement pss = prepareStatement(" select chardata,bytedata from test500 where id = ?");
        verifyDerby500Test(pss, buf, cbuf, 0, 10, true);

        // do the update, update must qualify more than 1 row and update
        // will fail as currently we don't allow stream values to be re-used
        PreparedStatement psu = prepareStatement("update test500 set bytedata = ? "
                + ", chardata = ? where mvalue = ?  ");

        buf[randomOffset + 1] = (byte) 'u';
        cbuf[randomOffset + 1] = 'u';
        rowCount = 0;
        println("Update qualifies many rows + streams");

        try {
            psu.setBinaryStream(1, new ByteArrayInputStream(buf), len);
            psu.setCharacterStream(2, new CharArrayReader(cbuf), len);
            psu.setInt(3, 0);
            rowCount += psu.executeUpdate();
            println("DERBY500 #1 Rows updated  =" + rowCount);
            fail("Attempting to reuse stream should have thrown an exception!");
        } catch (SQLException sqle) {
            assertSQLState("XJ001", sqle);
            println("EXPECTED EXCEPTION - streams cannot be re-used");
            rollback();
        }

        // verify data
        // set back buffer value to what was inserted.
        buf[randomOffset + 1] = (byte) orig;
        cbuf[randomOffset + 1] = orig;

        verifyDerby500Test(pss, buf, cbuf, 0, 10, true);

        PreparedStatement psu2 = prepareStatement("update test500 set "
                + "bytedata = ? , chardata = ? where id = ?  ");

        buf[randomOffset + 1] = (byte) 'u';
        cbuf[randomOffset + 1] = 'u';

        rowCount = 0;
        psu2.setBinaryStream(1, new ByteArrayInputStream(buf), len);
        psu2.setAsciiStream(2, new ByteArrayInputStream(buf), len);
        psu2.setInt(3, 0);
        rowCount += psu2.executeUpdate();
        println("DERBY500 #2 Rows updated  =" + rowCount);

        commit();
        verifyDerby500Test(pss, buf, cbuf, 0, 1, true);

        // delete, as currently we dont allow stream values to be re-used
        PreparedStatement psd = prepareStatement("delete from test500 where "
                + "mvalue = ?");

        rowCount = 0;
        psd.setInt(1, 0);
        rowCount += psd.executeUpdate();
        rowCount += psd.executeUpdate();
        println("DERBY500 #3 Rows deleted =" + rowCount);

        commit();

        // verify data
        verifyDerby500Test(pss, buf, cbuf, 0, 10, true);

        PreparedStatement psd2 = prepareStatement("delete from test500 "
                + "where id = ?");
        rowCount = 0;

        psd2.setInt(1, 0);
        rowCount += psd2.executeUpdate();
        println("DERBY500 #4 Rows deleted  =" + rowCount);

        commit();
        verifyDerby500Test(pss, buf, cbuf, 1, 2, true);

        try {
            ps.setInt(1, 11);
            rowCount += ps.executeUpdate();
            fail("Attempting to reuse stream should have thrown an exception!");
        } catch (SQLException sqle) {
            if (usingDerbyNetClient()) {
            	// DERBY-4315.  This SQLState is wrong for client.
            	// It should throw XJ001 like embedded.
            	// Also client inserts bad data.
            	// Remove special case when DERBY-4315
            	// is fixed.
                assertSQLState("XN017", sqle);                
            } else {
                assertSQLState("XJ001", sqle);
                println("EXPECTED EXCEPTION - streams cannot be re-used");
            }
            rollback();
        }
        commit();
        stmt.close();
        pss.close();
        psu2.close();
        psu.close();
        psd.close();
        psd2.close();
        println("END  DERBY-500 TEST ");
        println("======================================");
    }

    /**
     * Test that DERBY500 fix did not change the behavior for varchar, char,
     * long varchar types when stream api is used. Currently, for char,varchar
     * and long varchar - the stream is read once and materialized, hence the
     * materialized stream value will/can be used for multiple executions of the
     * prepared statement
     */
    public void testDerby500_verifyVarcharStreams() throws Exception {
        Statement stmt;
        println("======================================");
        println("START  DERBY-500 TEST for varchar ");

        stmt = createStatement();
        PreparedStatement ps = prepareStatement("insert into test500_verify "
                + "values (?,?,?,?,?)");

        // insert 10 rows.
        int rowCount = 0;

        // use blob and clob values
        int len = 10000;
        byte buf[] = new byte[len];
        char cbuf[] = new char[len];
        char orig = 'c';
        for (int i = 0; i < len; i++) {
            buf[i] = (byte) orig;
            cbuf[i] = orig;
        }
        int randomOffset = 9998;
        buf[randomOffset] = (byte) 'e';
        cbuf[randomOffset] = 'e';
        for (int i = 0; i < 10; i++) {
            ps.setInt(1, i);
            ps.setString(2, "mname" + i);
            ps.setInt(3, 0);
            ps.setCharacterStream(4, new CharArrayReader(cbuf), len);
            ps.setAsciiStream(5, new ByteArrayInputStream(buf), len);
            rowCount += ps.executeUpdate();
        }
        commit();
        println("Rows inserted =" + rowCount);

        try {
            ps.setInt(1, 11);
            rowCount += ps.executeUpdate();
            // The check below is just to detect a change in behavior for the
            // client driver (this succeeds with the embedded driver due to
            // a different implementation). With the client driver the source
            // stream is read twice, whereas the embedded driver will "cache"
            // the stream content and can thus use it for a second insert.
            if (usingDerbyNetClient()) {
                fail("Expected second executeUpdate with client driver to fail");
            }
        } catch (SQLException sqle) {
            if (usingDerbyNetClient()) {
            	// DERBY-4315.  This SQLState is wrong for client.
            	// It should have the same behavior as embedded.
            	// That may rquire some additional work in addition
            	// to DERBY-4315. 
            	// Remove special case when DERBY-4315
            	// is fixed or at least throw XJ001 and
            	// avoid bad data insert.

                // DERBY-4531: Depending on whether the finalizer has been run
                //             or not, the SQLState will differ.
                //             Don't care about this here, accept both.
                String expectedState = "XN017";
                if (sqle.getSQLState().equals("XN014")) {
                    expectedState = "XN014";
                }
                assertSQLState(expectedState, sqle);
            } else {
                println("UNEXPECTED EXCEPTION - streams cannot be "
                        + "re-used but in case of varchar, stream is materialized the"
                        + " first time around. So multiple executions using streams should "
                        + " work fine. ");
                throw sqle;
            }
        }

        PreparedStatement pss = prepareStatement(" select lvc,vc from test500_verify where "
                + "id = ?");
        verifyDerby500Test(pss, buf, cbuf, 0, 10, false);

        // do the update, update must qualify more than 1 row and update will
        // pass for char,varchar,long varchar columns.
        PreparedStatement psu = prepareStatement("update test500_verify set vc = ? "
                + ", lvc = ? where mvalue = ?  ");

        buf[randomOffset + 1] = (byte) 'u';
        cbuf[randomOffset + 1] = 'u';
        rowCount = 0;
        psu.setAsciiStream(1, new ByteArrayInputStream(buf), len);
        psu.setCharacterStream(2, new CharArrayReader(cbuf), len);
        psu.setInt(3, 0);
        rowCount += psu.executeUpdate();

        println("DERBY500 for varchar #1 Rows updated  =" + rowCount);

        // verify data
        verifyDerby500Test(pss, buf, cbuf, 0, 10, false);

        PreparedStatement psu2 = prepareStatement("update test500_verify set vc = ? "
                + ", lvc = ? where id = ?  ");

        buf[randomOffset + 1] = (byte) 'h';
        cbuf[randomOffset + 1] = 'h';

        rowCount = 0;
        psu2.setAsciiStream(1, new ByteArrayInputStream(buf), len);
        psu2.setAsciiStream(2, new ByteArrayInputStream(buf), len);
        psu2.setInt(3, 0);
        rowCount += psu2.executeUpdate();

        commit();
        println("DERBY500 for varchar #2 Rows updated  =" + rowCount);
        verifyDerby500Test(pss, buf, cbuf, 0, 1, false);

        // delete, as currently we dont allow stream values to be re-used
        PreparedStatement psd = prepareStatement("delete from test500_verify "
                + "where mvalue = ?");

        rowCount = 0;
        psd.setInt(1, 0);
        rowCount += psd.executeUpdate();
        rowCount += psd.executeUpdate();

        println("DERBY500 for varchar #3 Rows deleted =" + rowCount);

        // verify data
        verifyDerby500Test(pss, buf, cbuf, 0, 10, false);
        PreparedStatement psd2 = prepareStatement("delete from test500_verify where id = ?");
        rowCount = 0;
        psd2.setInt(1, 0);
        rowCount += psd2.executeUpdate();

        commit();
        println("DERBY500 for varchar #4 Rows deleted  =" + rowCount);
        verifyDerby500Test(pss, buf, cbuf, 1, 2, false);
        commit();
        stmt.close();
        pss.close();
        psu2.close();
        psu.close();
        psd.close();
        psd2.close();
        println("END  DERBY-500 TEST  for varchar");
        println("======================================");

    }

    /**
     * verify the data in the derby500Test
     * 
     * @param ps
     *            select preparedstatement
     * @param buf
     *            byte array to compare the blob data
     * @param cbuf
     *            char array to compare the clob data
     * @param startId
     *            start id of the row to check data for
     * @param endId
     *            end id of the row to check data for
     * @param binaryType
     *            flag to indicate if the second column in resultset is a binary
     *            type or not. true for binary type
     * @throws Exception
     */
    private void verifyDerby500Test(PreparedStatement ps, byte[] buf,
            char[] cbuf, int startId, int endId, boolean binaryType)
            throws Exception {
        int rowCount = 0;
        ResultSet rs = null;
        for (int i = startId; i < endId; i++) {
            ps.setInt(1, i);
            rs = ps.executeQuery();
            if (rs.next()) {
                compareCharArray(rs.getCharacterStream(1), cbuf, cbuf.length);
                if (binaryType) {
                    Arrays.equals(rs.getBytes(2), buf);
                    // byteArrayEquals(rs.getBytes(2), 0, buf.length, buf, 0,
                    // buf.length);
                } else {
                    compareCharArray(rs.getCharacterStream(2), cbuf,
                            cbuf.length);
                }
                rowCount++;
            }
        }
        println("Rows selected =" + rowCount);
        rs.close();
    }

    /**
     * compare char data
     * 
     * @param stream
     *            data from stream to compare
     * @param compare
     *            base data to compare against
     * @param length
     *            compare length number of chars.
     * @throws Exception
     */
    private static void compareCharArray(Reader stream, char[] compare,
            int length) throws Exception {
        int c1 = 0;
        int i = 0;
        do {
            c1 = stream.read();
            assertEquals("MISMATCH in data stored versus data retrieved at "
                    + (i - 1), c1, compare[i++]);
            length--;
        } while (c1 != -1 && length > 0);
    }

    private static void streamTestDataVerification(ResultSet rs,
            int maxValueAllowed) throws Exception {

        rs.getMetaData();
        byte[] buff = new byte[128];
        // fetch all rows back, get the varchar and/ long varchar columns as
        // streams.
        while (rs.next()) {
            // get the first column as an int
            int a = rs.getInt("a");
            // get the second column as a stream
            InputStream fin = rs.getAsciiStream(2);
            int columnSize = 0;
            for (;;) {
                int size = fin.read(buff);
                if (size == -1)
                    break;
                columnSize += size;
            }
            if ((a >= 1 && a <= 5) && columnSize == maxValueAllowed)
                println("===> verified length " + maxValueAllowed);
            else
                println("test failed, columnSize should be " + maxValueAllowed
                        + " but it is" + columnSize);
        }
    }

    /**
     * blankPadding true means excess trailing blanks false means excess
     * trailing non-blank characters
     * 
     * @param tblType
     *            table type, depending on the table type, the corresponding
     *            table is used. for varchar - testVarChar , for long varchar -
     *            testVarChars,
     */
    private static void insertDataUsingConcat(Statement stmt, int intValue,
            int maxValueAllowed, boolean blankPadding, int tblType,
            String tableName) throws Exception {
        String sql;
        boolean throwsException = false;
        switch (tblType) {
        case LONGVARCHAR:
            sql = "insert into testLongVarChars13 select " + intValue
                    + ", a||b||";
            break;
        case CLOB:
            sql = "insert into testClob14 select " + intValue + ", c||d||";
            throwsException = true;
            break;
        default:
            sql = "insert into testVarChar12 select " + intValue + ", c||d||";
            throwsException = true;
        }

        if (blankPadding) { // try overflow with trailing blanks
            sql = sql.concat("'   ' from " + tableName);
        } else {
            // try overflow with trailing non-blank characters
            sql = sql.concat("'123' from " + tableName);
        }

        // for varchars, trailing blank truncation will not throw an exception.
        // Only non-blank characters will cause truncation error
        // for long varchars, any character truncation will throw an exception.
        try {
            stmt.execute(sql);
            if (throwsException && !blankPadding) {
                fail("Truncation sould have thrown an exception!");
            }
            println("No truncation and hence no error.");
        } catch (SQLException e) {
            assertSQLState("22001", e); // truncation error
            println("expected exception for data > " + maxValueAllowed
                    + " in length");
        }
    }

    // blankPadding: true means excess trailing blanks
    // false means excess trailing non-blank characters
    // testUsingString: true means try setString method for overflow
    // false means try setObject method for overflow
    private static void insertDataUsingStringOrObject(PreparedStatement ps,
            int intValue, int maxValueAllowed, boolean blankPadding,
            boolean testUsingString, int test) throws Exception {
        StringBuffer sb = new StringBuffer(maxValueAllowed);
        for (int i = 0; i < maxValueAllowed; i++) {
            sb.append('q');
        }

        String largeString = new String(sb);
        if (blankPadding) {
            largeString = largeString.concat("   ");
            println("===> testing trailing blanks(using ");
        } else {
            largeString = largeString.concat("123");
            println("===> testing trailing non-blanks(using ");
        }

        ps.setInt(1, intValue);
        if (testUsingString) {
            println("setString) length = " + largeString.length());
            ps.setString(2, largeString);
        } else {
            println("setObject) length = " + largeString.length());
            ps.setObject(2, largeString);
        }

        // for varchars, trailing blank truncation will not throw an exception.
        // Only non-blank characters cause truncation error
        // for long varchars, any character truncation will throw an exception.
        try {
            ps.executeUpdate();
            if (!blankPadding) {
                fail("Should have thrown a truncation error");
            }
            println("No truncation and hence no error");
        } catch (SQLException e) {
            if (largeString.length() > maxValueAllowed) {
                if (!blankPadding && usingDerbyNetClient()) {
                    assertSQLState("XJ001", e); // truncation error
                } else if (test == 13 && usingDerbyNetClient()) {
                    assertSQLState("XJ001", e); // truncation error
                } else {
                    assertSQLState("22001", e); // truncation error
                }
                println("expected exception for data > " + maxValueAllowed
                        + " in length");
            } else {
                throw e;
            }
        }
    }

    /**
     * Method used by testStream12, testStream13, testStream14 to insert data
     * from a file using a character stream
     */
    private static void insertDataUsingCharacterStream(PreparedStatement ps,
            int intValue, String fileName, int maxValueAllowed, int test)
            throws Exception {
        File file = new File(fileName);
        InputStream fileIn = PrivilegedFileOpsForTests.getFileInputStream(file);
        Reader filer = new InputStreamReader(fileIn, "US-ASCII");
        println("===> testing(using setCharacterStream) " + fileName
                + " length = " + PrivilegedFileOpsForTests.length(file));
        ps.setInt(1, intValue);
        // insert a streaming column
        ps.setCharacterStream(2, filer, (int) PrivilegedFileOpsForTests
                .length(file));
        // for varchars, trailing blank truncation will not throw an exception.
        // Only non-blank characters cause truncation error
        // for long varchars, any character truncation will throw an exception.
        try {
            ps.executeUpdate();
            if ("extin/char32675.data".equals(fileName)) {
                fail("Should have thrown a truncation error");
            }
            println("No truncation and hence no error");
        } catch (SQLException e) {
            if (PrivilegedFileOpsForTests.length(file) > maxValueAllowed) {
                if (test == 12) {
                    if (usingDerbyNetClient()
                            && "extin/char32675.data".equals(fileName)) {
                        assertSQLState("XJ001", e); // truncation error
                    } else {
                        assertSQLState("22001", e); // truncation error
                    }
                } else if (test == 13) {
                    if (usingDerbyNetClient()) {
                        assertSQLState("XJ001", e); // truncation error
                    } else {
                        assertSQLState("22001", e); // truncation error
                    }
                } else {
                    assertSQLState("XJ001", e); // truncation error
                }
                println("expected exception for data > " + maxValueAllowed
                        + " in length");
            } else {
                throw e;
            }
        } finally {
            filer.close();
        }
    }

    /**
     * Method used by testStream12, testStream13, testStream14 to insert data
     * from a file using an ASCII stream
     */
    private static void insertDataUsingAsciiStream(PreparedStatement ps,
            int intValue, String fileName, int maxValueAllowed, int test)
            throws Exception {
        File file = new File(fileName);
        InputStream fileIn = PrivilegedFileOpsForTests.getFileInputStream(file);
        println("===> testing(using setAsciiStream) " + fileName + " length = "
                + PrivilegedFileOpsForTests.length(file));
        // insert a streaming column
        ps.setInt(1, intValue);
        ps.setAsciiStream(2, fileIn, (int) PrivilegedFileOpsForTests
                .length(file));

        // for varchars, trailing blank truncation will not throw an exception.
        // Only non-blank characters cause truncation error
        // for long varchars, any character truncation will throw an exception.
        try {
            ps.executeUpdate();
            if ("extin/char32675.data".equals(fileName)) {
                fail("Should have thrown a truncation error");
            }
            println("No truncation and hence no error");
        } catch (SQLException e) {
            if (PrivilegedFileOpsForTests.length(file) > maxValueAllowed) {
                if (test == 12) {
                    if (usingDerbyNetClient()
                            && "extin/char32675.data".equals(fileName)) {
                        assertSQLState("XJ001", e); // truncation error
                    } else {
                        assertSQLState("22001", e); // truncation error
                    }
                } else if (test == 13) {
                    if (usingDerbyNetClient()) { // "extin/char32675.data".equals(fileName))
                        // {
                        assertSQLState("XJ001", e); // truncation error
                    } else {
                        assertSQLState("22001", e); // truncation error
                    }
                } else if (test == 14) {
                    if ("extin/char32675.data".equals(fileName)) {
                        assertSQLState("XJ001", e); // truncation error
                    } else {
                        assertSQLState("22001", e); // truncation error
                    }
                }
                println("expected exception for data > " + maxValueAllowed
                        + " in length");
            } else {
                throw e;
            }
        } finally {
            fileIn.close();
        }
    }

    private void verifyLength(int a, int columnSize, long[] fileLength) {

        for (int i = 0; i < fileLength.length; i++) {
            if ((a == (100 + i)) || (a == (10000 + i))) {
                assertEquals("ColumnSize should be " + fileLength[i]
                        + ", but it is " + columnSize + ", i = " + i,
                        fileLength[i], columnSize);
            }
        }
    }

    private void verifyExistence(int key, String base, long length,
            String tableName) throws Exception {
        assertEquals("failed to find value " + base + "... at key " + key, pad(
                base, length), getLongString(key, tableName));
    }

    private String getLongString(int key, String tableName) throws Exception {
        Statement s = createStatement();
        ResultSet rs = s.executeQuery("select b from " + tableName
                + " where a = " + key);
        assertTrue("There weren't any rows for key = " + key, rs.next());
        String answer = rs.getString(1);
        assertFalse("There were multiple rows for key = " + key, rs.next());
        rs.close();
        s.close();
        return answer;
    }

    static String pad(String base, long length) {
        StringBuffer b = new StringBuffer(base);
        for (long i = 1; b.length() < length; i++)
            b.append(" " + i);
        return b.toString();
    }

    private int insertLongString(int key, String data, boolean binaryColumn,
            String tableName) throws Exception {
        PreparedStatement ps = prepareStatement("insert into " + tableName
                + " values(" + key + ", ?)");
        return streamInStringCol(ps, data, binaryColumn);
    }

    private int updateLongString(int oldkey, int newkey, String tableName)
            throws Exception {
        PreparedStatement ps = prepareStatement("update " + tableName
                + " set a = ?, b = ? where a = " + oldkey);

        String updateString = pad("", newkey);
        ByteArrayInputStream bais = new ByteArrayInputStream(updateString
                .getBytes("US-ASCII"));
        ps.setInt(1, newkey);
        ps.setAsciiStream(2, bais, updateString.length());
        int nRows = ps.executeUpdate();
        ps.close();
        return nRows;
    }

    private int streamInStringCol(PreparedStatement ps, String data,
            boolean binaryColumn) throws Exception {
        int nRows = 0;
        if (data == null) {
            ps.setAsciiStream(1, null, 0);
            nRows = ps.executeUpdate();
        } else {
            ByteArrayInputStream bais = new ByteArrayInputStream(data
                    .getBytes("US-ASCII"));
            if (binaryColumn) {
                ps.setBinaryStream(1, bais, data.length());
            } else {
                ps.setAsciiStream(1, bais, data.length());
            }
            nRows = ps.executeUpdate();
            bais.close();
        }
        return nRows;
    }

    /**
     * 
     * @param ps
     *            PreparedStatement
     * @param data
     *            Data to be padded and inserted
     * @return Number of rows
     * @throws Exception
     */
    private static int streamInLongCol(PreparedStatement ps, Object data)
            throws Exception {
        String s = (String) data;
        ByteArrayInputStream bais = new ByteArrayInputStream(s
                .getBytes("US-ASCII"));
        ps.setAsciiStream(1, bais, s.length());
        int nRows = ps.executeUpdate();
        bais.close();
        return nRows;
    }

    /**
     * Runs the test fixtures in embedded and client.
     * 
     * @return test suite
     */
    public static Test suite() {
        Properties strColProperties = new Properties();
        strColProperties.setProperty("derby.storage.sortBufferMax", "5");
        strColProperties.setProperty("derby.debug.true", "testSort");
        BaseTestSuite suite = new BaseTestSuite("StreamingColumnTest");
        suite.addTest(baseSuite("StreamingColumnTest:embedded"));
        suite
                .addTest(TestConfiguration
                        .clientServerDecorator(baseSuite("StreamingColumnTest:client")));
        return new SystemPropertyTestSetup(suite, strColProperties);
    }

    protected static Test baseSuite(String name) {
        BaseTestSuite suite = new BaseTestSuite(name);
        suite.addTestSuite(StreamingColumnTest.class);
        Test test = new SupportFilesSetup(suite, new String[] {
                "functionTests/tests/store/short.data",
                "functionTests/tests/store/shortbanner",
                "functionTests/tests/store/derby.banner",
                "functionTests/tests/store/empty.data",
                "functionTests/tests/store/char32703trailingblanks.data",
                "functionTests/tests/store/char32703.data",
                "functionTests/tests/store/char32675trailingblanks.data",
                "functionTests/tests/store/char32675.data" });
        return new CleanDatabaseTestSetup(DatabasePropertyTestSetup
                .setLockTimeouts(test, 2, 4)) {
            /**
             * Creates the tables used in the test cases.
             * 
             * @exception SQLException
             *                if a database error occurs
             */
            protected void decorateSQL(Statement stmt) throws SQLException {

                // testStream1
                stmt
                        .execute("create table testLongVarChar1 (a int, b long varchar)");
                // insert a null long varchar
                stmt.execute("insert into testLongVarChar1 values(1, '')");
                // insert a long varchar with a short text string
                stmt
                        .execute("insert into testLongVarChar1 values(2, "
                                + "'test data: a string column inserted as an object')");
                // todo use setProperty method
                stmt
                        .execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024')");
                stmt
                        .execute("create table foo1 (a int not null, b long varchar, primary key (a))");
                stmt
                        .execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)");

                // testStream2_1500
                stmt.execute("create table foo2_1500 (a int not null, "
                        + "b long varchar, primary key (a))");

                // testStream2_5000
                stmt.execute("create table foo2_5000 (a int not null, "
                        + "b long varchar, primary key (a))");

                // testStream2_10000
                stmt.execute("create table foo2_10000 (a int not null, "
                        + "b long varchar, primary key (a))");

                // testStream3_0
                stmt.execute("create table foo3_0 (a int not null "
                        + "constraint pk3_0 primary key, b long varchar)");

                // testStream3_1500
                stmt.execute("create table foo3_1500 (a int not null "
                        + "constraint pk3_1500 primary key, b long varchar)");

                // testStream3_5000
                stmt.execute("create table foo3_5000 (a int not null "
                        + "constraint pk3_5000 primary key, b long varchar)");

                // testStream3_10000
                stmt.execute("create table foo3_10000 (a int not null "
                        + "constraint pk3_10000 primary key, b long varchar)");

                // testStream4
                stmt
                        .execute("create table testLongVarBinary4 (a int, b BLOB(1G))");

                // testStream5_0
                long length = 0;
                String binaryType = length > 32700 ? "BLOB(1G)"
                        : "long varchar for bit data";
                stmt.execute("create table foo5_0 (a int not null "
                        + "constraint pk5_0 primary key, b " + binaryType
                        + " )");

                // testStream5_1500
                length = 1500;
                binaryType = length > 32700 ? "BLOB(1G)"
                        : "long varchar for bit data";
                stmt.execute("create table foo5_1500 (a int not null "
                        + "constraint pk5_1500 primary key, b " + binaryType
                        + " )");

                // testStream5_5000
                length = 5000;
                binaryType = length > 32700 ? "BLOB(1G)"
                        : "long varchar for bit data";
                stmt.executeUpdate("create table foo5_5000 (a int not null "
                        + "constraint pk5_5000 primary key, b " + binaryType
                        + " )");

                // testStream5_100000
                length = 100000;
                binaryType = length > 32700 ? "BLOB(1G)"
                        : "long varchar for bit data";
                stmt.executeUpdate("create table foo5_100000 (a int not null "
                        + "constraint pk5_100000 primary key, b " + binaryType
                        + " )");

                // testStream6
                stmt
                        .executeUpdate("create table foo_6 (a int not null constraint"
                                + " pk6 primary key, b long varchar)");

                // testStream7
                stmt
                        .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024')");
                stmt
                        .execute("create table testlvc7 (a int, b char(100), lvc long varchar, d char(100))");
                stmt
                        .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)");

                // testStream8_10_2500
                stmt
                        .execute("create table t8_10_2500(a int, b long varchar, c long varchar)");

                // testStream8_2500_10
                stmt
                        .execute("create table t8_2500_10(a int, b long varchar, c long varchar)");

                // testStream9_10_2500
                stmt
                        .execute("create table t9_10_2500(a int, b long varchar for bit data, "
                                + "c long varchar for bit data)");

                // testStream9_2500_10
                stmt
                        .execute("create table t9_2500_10(a int, b long varchar for bit data, "
                                + "c long varchar for bit data)");

                // testStream10
                stmt
                        .execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024')");
                stmt
                        .execute("create table tab10 (a int, b int, c long   varchar)");
                stmt
                        .execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)");
                // create the indexes which shares columns
                stmt
                        .execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096')");
                stmt.execute("create index i_a on tab10 (a)");
                stmt.execute("create index i_ab on tab10 (a, b)");
                stmt
                        .execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)");
                // insert a null long varchar
                stmt.execute("insert into tab10 values(1, 1, '')");
                // insert a long varchar with a short text string
                stmt
                        .execute("insert into tab10 values(2, 2, 'test data: a string column inserted as an object')");

                // testStream11
                stmt
                        .execute("create table testLongVarCharInvalidStreamLength11 "
                                + "(a int, b long varchar, c long varchar for bit data)");

                // testStream12
                stmt
                        .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096')");
                stmt
                        .execute("create table testVarChar12 (a int, b varchar(32672))");
                // create a table with 4 varchars. This table will be used to
                // try
                // overflow through concatenation
                stmt
                        .execute("create table testConcatenation12 (a varchar(16350), b varchar(16350), c varchar(16336), d varchar(16336))");
                stmt
                        .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)");

                // testStream13
                stmt
                        .execute("create table testLongVarChars13 (a int, b long varchar)");

                // testStream14
                stmt
                        .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096')");
                stmt.execute("create table testClob14 (a int, b clob(32672))");
                // create a table with 4 varchars. This table will be used to
                // try
                // overflow through concatenation

                stmt
                        .execute("create table testConcatenation14 (a clob(16350), b clob(16350), c clob(16336), d clob(16336))");
                stmt
                        .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)");

                // testDerby500
                stmt.execute("CREATE TABLE test500 (" + "id INTEGER NOT NULL,"
                        + "mname VARCHAR( 254 ) NOT NULL,"
                        + "mvalue INT NOT NULL," + "bytedata BLOB NOT NULL,"
                        + "chardata CLOB NOT NULL," + "PRIMARY KEY ( id ))");

                // testDerby500_verifyVarcharStreams
                stmt.execute("CREATE TABLE test500_verify ("
                        + "id INTEGER NOT NULL,"
                        + "mname VARCHAR( 254 ) NOT NULL,"
                        + "mvalue INT NOT NULL," + "vc varchar(32500),"
                        + "lvc long varchar NOT NULL," + "PRIMARY KEY ( id ))");
            }
        };
    }

}