db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kmars...@apache.org
Subject svn commit: r666225 [2/3] - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting: functionTests/master/ functionTests/suites/ functionTests/tests/store/ functionTests/util/ junit/
Date Tue, 10 Jun 2008 17:28:10 GMT
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/StreamingColumnTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/StreamingColumnTest.java?rev=666225&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/StreamingColumnTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/StreamingColumnTest.java Tue Jun 10 10:28:09 2008
@@ -0,0 +1,2273 @@
+/* 
+
+   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.ResultSet;
+import java.sql.Statement;
+import java.sql.SQLException;
+import java.sql.PreparedStatement;
+import java.util.Arrays;
+import java.util.Properties;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+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.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 > 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()) {
+                assertSQLState("XJ215", sqle);
+                println("Expected Exception: You cannot invoke other "
+                        + "java.sql.Clob/java.sql.Blob methods after calling the "
+                        + "free() method or after the Blob/Clob's transaction has"
+                        + " been committed or rolled back.");
+            } 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();
+        } catch (SQLException sqle) {
+            if (usingDerbyNetClient()) {
+                assertSQLState("XJ215", sqle);
+                println("Expected Exception: You cannot invoke other "
+                        + "java.sql.Clob/java.sql.Blob methods after calling the "
+                        + "free() method or after the Blob/Clob's transaction has"
+                        + " been committed or rolled back.");
+            } 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");
+        TestSuite suite = new TestSuite("StreamingColumnTest");
+        suite.addTest(baseSuite("StreamingColumnTest:embedded"));
+        suite
+                .addTest(TestConfiguration
+                        .clientServerDecorator(baseSuite("StreamingColumnTest:client")));
+        return new SystemPropertyTestSetup(suite, strColProperties);
+    }
+
+    protected static Test baseSuite(String name) {
+        TestSuite suite = new TestSuite(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

[... 37 lines stripped ...]


Mime
View raw message