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 ...]
|