0001: /*
0002:
0003: Derby - Class org.apache.derbyTesting.functionTests.tests.store.streamingColumn
0004:
0005: Licensed to the Apache Software Foundation (ASF) under one or more
0006: contributor license agreements. See the NOTICE file distributed with
0007: this work for additional information regarding copyright ownership.
0008: The ASF licenses this file to You under the Apache License, Version 2.0
0009: (the "License"); you may not use this file except in compliance with
0010: the License. You may obtain a copy of the License at
0011:
0012: http://www.apache.org/licenses/LICENSE-2.0
0013:
0014: Unless required by applicable law or agreed to in writing, software
0015: distributed under the License is distributed on an "AS IS" BASIS,
0016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0017: See the License for the specific language governing permissions and
0018: limitations under the License.
0019:
0020: */
0021:
0022: package org.apache.derbyTesting.functionTests.tests.store;
0023:
0024: import java.sql.Connection;
0025: import java.sql.DriverManager;
0026: import java.sql.ResultSetMetaData;
0027: import java.sql.ResultSet;
0028: import java.sql.Statement;
0029: import java.sql.SQLException;
0030: import java.sql.Types;
0031:
0032: import org.apache.derby.tools.ij;
0033: import org.apache.derby.tools.JDBCDisplayUtil;
0034: import org.apache.derbyTesting.functionTests.util.Formatters;
0035: import org.apache.derbyTesting.functionTests.util.TestUtil;
0036: import org.apache.derby.iapi.reference.Limits;
0037: import java.io.*;
0038: import java.sql.CallableStatement;
0039: import java.sql.PreparedStatement;
0040: import java.util.zip.CRC32;
0041: import java.util.Properties;
0042:
0043: /**
0044: * Test of JDBC result set Stream calls.
0045: *
0046: * @author djd
0047: */
0048:
0049: public class streamingColumn {
0050:
0051: // set up a short (fit in one page) inputstream for insert
0052: static String[] fileName;
0053: static long[] fileLength;
0054:
0055: static {
0056: int numFiles = 4;
0057: fileName = new String[numFiles];
0058: fileLength = new long[numFiles];
0059:
0060: fileName[0] = "extin/short.data"; // set up a short (fit in one page) inputstream for insert
0061: fileName[1] = "extin/shortbanner"; // set up a long (longer than a page) inputstream for insert
0062: fileName[2] = "extin/derby.banner"; // set up a really long (over 300K) inputstream for insert
0063: fileName[3] = "extin/empty.data"; // set up a file with nothing in it
0064: }
0065:
0066: private static final int LONGVARCHAR = 1;
0067: private static final int CLOB = 2;
0068: private static final int VARCHAR = 3;
0069:
0070: public static void main(String[] args) {
0071:
0072: System.out.println("Test streamingColumn starting");
0073:
0074: try {
0075: // use the ij utility to read the property file and
0076: // make the initial connection.
0077: ij.getPropertyArg(args);
0078: Connection conn = ij.startJBMS();
0079:
0080: streamTest1(conn);
0081:
0082: // test column size 1500 bytes
0083: streamTest2(conn, 1500);
0084: // test column size 5000 butes
0085: streamTest2(conn, 5000);
0086: streamTest2(conn, 10000);
0087:
0088: streamTest3(conn, 0);
0089: streamTest3(conn, 1500);
0090: streamTest3(conn, 5000);
0091: streamTest3(conn, 10000);
0092:
0093: streamTest4(conn);
0094:
0095: streamTest5(conn, 0);
0096: streamTest5(conn, 1500);
0097: streamTest5(conn, 5000);
0098: // This test fails when running w/ derby.language.logStatementText=true
0099: // see DERBY-595
0100: //streamTest5(conn, 100000);
0101:
0102: streamTest6(conn, 5000);
0103: streamTest7(conn);
0104:
0105: // test 1st column fit, second column doesn't
0106: streamTest8(conn, 10, 2500);
0107: streamTest9(conn, 10, 2500);
0108:
0109: // test 1st column doesn't fit, second column does
0110: streamTest8(conn, 2500, 10);
0111: streamTest9(conn, 2500, 10);
0112:
0113: // test compressTable
0114: streamTest10(conn);
0115:
0116: // bug 5592 test negativte length for the setXXStream methods. Should fail.
0117: streamTest11(conn);
0118:
0119: // bug 5592 test - only non-blank character truncation should give error for varchars
0120: streamTest12(conn);
0121:
0122: // bug 5592 test - any character(including blank character) truncation should give error for long varchars
0123: streamTest13(conn);
0124:
0125: // Test clob truncation, behavior similar to varchar
0126: // trailingspaces are truncated but if there are trailing non-blanks then
0127: // exception is thrown
0128: // This test is similar to streamTest12.
0129: streamTest14(conn);
0130:
0131: // Derby500
0132: // user supplied stream parameter values are not re-used
0133: derby500Test(conn);
0134:
0135: // currently in case of char,varchar,long varchar types
0136: // stream paramter value is materialized the first time around
0137: // and used for executions. Hence verify that the fix to
0138: // DERBY-500 did not change the behavior for char,varchar
0139: // and long varchar types when using streams.
0140: derby500_verifyVarcharStreams(conn);
0141:
0142: // turn autocommit on because in JCC, java.sql.Connection.close() can not be
0143: // requested while a transaction is in progress on the connection.
0144: // If autocommit is off in JCC, the transaction remains active,
0145: // and the connection cannot be closed.
0146: // If autocommit is off in Derby, an invalid transaction state SQL exception is thrown.
0147: conn.setAutoCommit(true);
0148: conn.close();
0149:
0150: } catch (SQLException e) {
0151: dumpSQLExceptions(e);
0152: } catch (Throwable e) {
0153: System.out.println("FAIL -- unexpected exception:"
0154: + e.toString());
0155: }
0156:
0157: System.out.println("Test streamingColumn finished");
0158: }
0159:
0160: private static void streamTest1(Connection conn) {
0161:
0162: ResultSetMetaData met;
0163: ResultSet rs;
0164: Statement stmt;
0165:
0166: try {
0167: stmt = conn.createStatement();
0168: stmt
0169: .execute("create table testLongVarChar (a int, b long varchar)");
0170: // insert a null long varchar
0171: stmt.execute("insert into testLongVarChar values(1, '')");
0172: // insert a long varchar with a short text string
0173: stmt
0174: .execute("insert into testLongVarChar values(2, 'test data: a string column inserted as an object')");
0175:
0176: for (int i = 0; i < fileName.length; i++) {
0177: // prepare an InputStream from the file
0178: File file = new File(fileName[i]);
0179: fileLength[i] = file.length();
0180: InputStream fileIn = new FileInputStream(file);
0181:
0182: System.out.println("===> testing " + fileName[i]
0183: + " length = " + fileLength[i]);
0184:
0185: // insert a streaming column
0186: PreparedStatement ps = conn
0187: .prepareStatement("insert into testLongVarChar values(?, ?)");
0188: ps.setInt(1, 100 + i);
0189: ps.setAsciiStream(2, fileIn, (int) fileLength[i]);
0190: try {//if trying to insert data > 32700, there will be an exception
0191: ps.executeUpdate();
0192: System.out
0193: .println("No truncation and hence no error");
0194: } catch (SQLException e) {
0195: if (fileLength[i] > Limits.DB2_LONGVARCHAR_MAXWIDTH
0196: && e.getSQLState().equals("22001")) //was getting data longer than maxValueAllowed
0197: System.out
0198: .println("expected exception for data > "
0199: + Limits.DB2_LONGVARCHAR_MAXWIDTH
0200: + " in length");
0201: else
0202: dumpSQLExceptions(e);
0203: }
0204: fileIn.close();
0205: }
0206:
0207: rs = stmt.executeQuery("select a, b from testLongVarChar");
0208: met = rs.getMetaData();
0209: byte[] buff = new byte[128];
0210: // fetch all rows back, get the long varchar columns as streams.
0211: while (rs.next()) {
0212: // get the first column as an int
0213: int a = rs.getInt("a");
0214: // get the second column as a stream
0215: InputStream fin = rs.getAsciiStream(2);
0216: int columnSize = 0;
0217: for (;;) {
0218: int size = fin.read(buff);
0219: if (size == -1)
0220: break;
0221: columnSize += size;
0222: }
0223: verifyLength(a, columnSize, fileLength);
0224: }
0225:
0226: rs = stmt
0227: .executeQuery("select a, b from testLongVarChar order by a");
0228: met = rs.getMetaData();
0229: // fetch all rows back in order, get the long varchar columns as streams.
0230: while (rs.next()) {
0231: // get the first column as an int
0232: int a = rs.getInt("a");
0233: // get the second column as a stream
0234: InputStream fin = rs.getAsciiStream(2);
0235: int columnSize = 0;
0236: for (;;) {
0237: int size = fin.read(buff);
0238: if (size == -1)
0239: break;
0240: columnSize += size;
0241: }
0242: verifyLength(a, columnSize, fileLength);
0243: }
0244:
0245: rs = stmt.executeQuery("select a, b from testLongVarChar");
0246: // fetch all rows back, get the long varchar columns as Strings.
0247: while (rs.next()) {
0248: // JDBC columns use 1-based counting
0249:
0250: // get the first column as an int
0251: int a = rs.getInt("a");
0252:
0253: // get the second column as a string
0254: String resultString = rs.getString(2);
0255: verifyLength(a, resultString.length(), fileLength);
0256: }
0257:
0258: rs = stmt
0259: .executeQuery("select a, b from testLongVarChar order by a");
0260: // fetch all rows back in order, get the long varchar columns as Strings.
0261: while (rs.next()) {
0262: // JDBC columns use 1-based counting
0263:
0264: // get the first column as an int
0265: int a = rs.getInt("a");
0266:
0267: // get the second column as a string
0268: String resultString = rs.getString(2);
0269: verifyLength(a, resultString.length(), fileLength);
0270: }
0271:
0272: rs = stmt
0273: .executeQuery("select a, b from testLongVarChar where b like 'test data: a string column inserted as an object'");
0274: // should return one row.
0275: while (rs.next()) {
0276: // JDBC columns use 1-based counting
0277:
0278: // get the first column as an int
0279: int a = rs.getInt("a");
0280:
0281: // get the second column as a string
0282: String resultString = rs.getString(2);
0283: verifyLength(a, resultString.length(), fileLength);
0284: }
0285:
0286: stmt
0287: .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024')");
0288: stmt
0289: .executeUpdate("create table foo (a int not null, b long varchar, primary key (a))");
0290: stmt
0291: .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)");
0292: insertLongString(conn, 10, "ssssssssss", false);
0293:
0294: insertLongString(conn, 0, "", false);
0295: insertLongString(conn, 1, "1", false);
0296: insertLongString(conn, -1, null, false);
0297: insertLongString(conn, 20, "XXXXXXXXXXXXXXXXXXXX", false);
0298:
0299: rs = stmt.executeQuery("select a, b from foo");
0300:
0301: System.out.println("expect to get null string back");
0302: while (rs.next()) {
0303: int a = rs.getInt("a");
0304: String resultString = rs.getString(2);
0305: if (resultString == null) {
0306: System.out.println("a = " + a
0307: + " got null string back");
0308: } else if (resultString.length() != a) {
0309: System.out
0310: .println("FAIL - failed to get string back, expect "
0311: + a
0312: + " got "
0313: + resultString.length());
0314: }
0315: }
0316:
0317: updateLongString(conn, 1, 3000);
0318: updateLongString(conn, 0, 800);
0319: updateLongString(conn, 3000, 0);
0320: updateLongString(conn, 0, 51);
0321: updateLongString(conn, 20, 0);
0322: rs = stmt.executeQuery("select a, b from foo");
0323: while (rs.next()) {
0324: int a = rs.getInt("a");
0325: String resultString = rs.getString(2);
0326: if (resultString == null) {
0327: System.out.println("a = " + a
0328: + " got null string back");
0329: } else if (resultString.length() != a) {
0330: System.out
0331: .println("FAIL - failed to get string back, expect "
0332: + a
0333: + " got "
0334: + resultString.length()
0335: + " "
0336: + resultString);
0337: }
0338: }
0339:
0340: stmt.executeUpdate("drop table foo");
0341:
0342: rs.close();
0343: stmt.close();
0344:
0345: } catch (SQLException e) {
0346: dumpSQLExceptions(e);
0347: } catch (Throwable e) {
0348: System.out.println("FAIL -- unexpected exception:"
0349: + e.toString());
0350: }
0351: }
0352:
0353: static void streamTest2(Connection conn, long length)
0354: throws Exception {
0355: Statement sourceStmt = conn.createStatement();
0356:
0357: sourceStmt
0358: .executeUpdate("create table foo (a int not null, b long varchar, primary key (a))");
0359:
0360: insertLongString(conn, 1, pad("Broadway", length), false);
0361: insertLongString(conn, 2, pad("Franklin", length), false);
0362: insertLongString(conn, 3, pad("Webster", length), false);
0363:
0364: sourceStmt
0365: .executeUpdate("insert into foo select a+100, b from foo");
0366:
0367: verifyExistence(conn, 1, "Broadway", length);
0368: verifyExistence(conn, 2, "Franklin", length);
0369: verifyExistence(conn, 3, "Webster", length);
0370: verifyExistence(conn, 101, "Broadway", length);
0371: verifyExistence(conn, 102, "Franklin", length);
0372: verifyExistence(conn, 103, "Webster", length);
0373:
0374: sourceStmt.executeUpdate("drop table foo");
0375: }
0376:
0377: static void streamTest3(Connection conn, long length)
0378: throws Exception {
0379: Statement sourceStmt = conn.createStatement();
0380: sourceStmt
0381: .executeUpdate("create table foo (a int not null constraint pk primary key, b long varchar)");
0382:
0383: insertLongString(conn, 1, pad("Broadway", length), false);
0384: insertLongString(conn, 2, pad("Franklin", length), false);
0385: insertLongString(conn, 3, pad("Webster", length), false);
0386: PreparedStatement ps = conn
0387: .prepareStatement("update foo set a=a+1000, b=? where a<99 and a in (select a from foo)");
0388:
0389: File file = new File("extin/short.data");
0390: InputStream fileIn = new FileInputStream(file);
0391: ps.setAsciiStream(1, fileIn, (int) (file.length()));
0392: ps.executeUpdate();
0393: fileIn.close();
0394:
0395: ps = conn
0396: .prepareStatement("update foo set a=a+1000, b=? where a<99 and a in (select a from foo)");
0397: file = new File("extin/shortbanner");
0398: fileIn = new FileInputStream(file);
0399: ps.setAsciiStream(1, fileIn, (int) (file.length()));
0400: ps.executeUpdate();
0401: fileIn.close();
0402:
0403: sourceStmt.executeUpdate("drop table foo");
0404: }
0405:
0406: private static void streamTest4(Connection conn) {
0407:
0408: ResultSetMetaData met;
0409: ResultSet rs;
0410: Statement stmt;
0411:
0412: try {
0413: stmt = conn.createStatement();
0414: stmt
0415: .execute("create table testLongVarBinary (a int, b BLOB(1G))");
0416: // insert an empty string
0417: stmt
0418: .execute("insert into testLongVarBinary values(1, CAST ("
0419: + TestUtil.stringToHexLiteral("")
0420: + "AS BLOB(1G)))");
0421: // insert a short text string
0422: stmt
0423: .execute("insert into testLongVarBinary values(2,CAST ("
0424: + TestUtil
0425: .stringToHexLiteral("test data: a string column inserted as an object")
0426: + "AS BLOB(1G)))");
0427:
0428: for (int i = 0; i < fileName.length; i++) {
0429: // prepare an InputStream from the file
0430: File file = new File(fileName[i]);
0431: fileLength[i] = file.length();
0432: InputStream fileIn = new FileInputStream(file);
0433:
0434: System.out.println("===> testing " + fileName[i]
0435: + " length = " + fileLength[i]);
0436:
0437: // insert a streaming column
0438: PreparedStatement ps = conn
0439: .prepareStatement("insert into testLongVarBinary values(?, ?)");
0440: ps.setInt(1, 100 + i);
0441: ps.setBinaryStream(2, fileIn, (int) fileLength[i]);
0442: ps.executeUpdate();
0443: fileIn.close();
0444: }
0445:
0446: rs = stmt
0447: .executeQuery("select a, b from testLongVarBinary");
0448: met = rs.getMetaData();
0449: byte[] buff = new byte[128];
0450: // fetch all rows back, get the long varchar columns as streams.
0451: while (rs.next()) {
0452: // get the first column as an int
0453: int a = rs.getInt("a");
0454: // get the second column as a stream
0455: InputStream fin = rs.getBinaryStream(2);
0456: int columnSize = 0;
0457: for (;;) {
0458: int size = fin.read(buff, 0, 100);
0459: if (size == -1)
0460: break;
0461: columnSize += size;
0462: }
0463: }
0464:
0465: rs = stmt
0466: .executeQuery("select a, b from testLongVarBinary order by a");
0467: met = rs.getMetaData();
0468: // fetch all rows back in order, get the long varchar columns as streams.
0469: while (rs.next()) {
0470: // get the first column as an int
0471: int a = rs.getInt("a");
0472: // get the second column as a stream
0473: InputStream fin = rs.getBinaryStream(2);
0474: int columnSize = 0;
0475: for (;;) {
0476: int size = fin.read(buff);
0477: if (size == -1)
0478: break;
0479: columnSize += size;
0480: }
0481: }
0482:
0483: rs = stmt
0484: .executeQuery("select a, b from testLongVarBinary");
0485: // fetch all rows back, get the long varchar columns as Strings.
0486: while (rs.next()) {
0487: // JDBC columns use 1-based counting
0488:
0489: // get the first column as an int
0490: int a = rs.getInt("a");
0491:
0492: // get the second column as a string
0493: String resultString = rs.getString(2);
0494: }
0495:
0496: rs = stmt
0497: .executeQuery("select a, b from testLongVarBinary order by a");
0498: // fetch all rows back in order, get the long varchar columns as Strings.
0499: while (rs.next()) {
0500: // JDBC columns use 1-based counting
0501:
0502: // get the first column as an int
0503: int a = rs.getInt("a");
0504:
0505: // get the second column as a string
0506: String resultString = rs.getString(2);
0507: }
0508:
0509: rs.close();
0510: stmt.close();
0511:
0512: } catch (SQLException e) {
0513: dumpSQLExceptions(e);
0514: } catch (Throwable e) {
0515: System.out.println("FAIL -- unexpected exception:"
0516: + e.toString());
0517: }
0518: }
0519:
0520: static void streamTest5(Connection conn, long length)
0521: throws Exception {
0522: Statement sourceStmt = conn.createStatement();
0523: String binaryType = length > 32700 ? "BLOB(1G)"
0524: : "long varchar for bit data";
0525: sourceStmt
0526: .executeUpdate("create table foo (a int not null constraint pk primary key, b "
0527: + binaryType + " )");
0528:
0529: insertLongString(conn, 1, pad("Broadway", length), true);
0530: insertLongString(conn, 2, pad("Franklin", length), true);
0531: insertLongString(conn, 3, pad("Webster", length), true);
0532: insertLongString(conn, 4, pad("Broadway", length), true);
0533: insertLongString(conn, 5, pad("Franklin", length), true);
0534: insertLongString(conn, 6, pad("Webster", length), true);
0535: PreparedStatement ps = conn
0536: .prepareStatement("update foo set a=a+1000, b=? where a<99 and a in (select a from foo)");
0537: File file = new File("extin/short.data");
0538: InputStream fileIn = new FileInputStream(file);
0539: ps.setBinaryStream(1, fileIn, (int) (file.length()));
0540: ps.executeUpdate();
0541: fileIn.close();
0542:
0543: ps = conn
0544: .prepareStatement("update foo set a=a+1000, b=? where a<99 and a in (select a from foo)");
0545: file = new File("extin/shortbanner");
0546: fileIn = new FileInputStream(file);
0547: ps.setBinaryStream(1, fileIn, (int) (file.length()));
0548: ps.executeUpdate();
0549: ps.close();
0550: fileIn.close();
0551:
0552: sourceStmt.executeUpdate("drop table foo");
0553: }
0554:
0555: static void streamTest6(Connection conn, long length)
0556: throws Exception {
0557: Statement sourceStmt = conn.createStatement();
0558: sourceStmt
0559: .executeUpdate("create table foo (a int not null constraint pk primary key, b long varchar)");
0560:
0561: insertLongString(conn, 1, pad("Broadway", length), false);
0562: insertLongString(conn, 2, pad("Franklin", length), false);
0563: insertLongString(conn, 3, pad("Webster", length), false);
0564: PreparedStatement ps = conn
0565: .prepareStatement("update foo set a=a+1000, b=? where a<99 and a in (select a from foo)");
0566:
0567: streamInLongCol(ps, pad("Grand", length));
0568: ps.close();
0569: sourceStmt.close();
0570: }
0571:
0572: static void streamTest7(Connection conn) throws Exception {
0573: conn.setAutoCommit(false);
0574:
0575: System.out.println("streamTest7");
0576:
0577: Statement s = conn.createStatement();
0578: s
0579: .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024')");
0580: s
0581: .execute("create table testlvc (a int, b char(100), lvc long varchar, d char(100))");
0582: s
0583: .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)");
0584: s.close();
0585: conn.commit();
0586:
0587: PreparedStatement ps1 = conn
0588: .prepareStatement("insert into testlvc values (?, 'filler for column b on null column', null, 'filler for column d')");
0589:
0590: PreparedStatement ps2 = conn
0591: .prepareStatement("insert into testlvc values (?, 'filler for column b on empty string column', ?, 'filler2 for column d')");
0592:
0593: for (int i = 0; i < 100; i++) {
0594: ps1.setInt(1, i);
0595: ps1.executeUpdate();
0596:
0597: ByteArrayInputStream emptyString = new ByteArrayInputStream(
0598: new byte[0]);
0599: ps2.setInt(1, i);
0600: ps2.setAsciiStream(2, emptyString, 0);
0601: ps2.executeUpdate();
0602: }
0603: ps1.close();
0604: ps2.close();
0605:
0606: conn.commit();
0607:
0608: PreparedStatement ps = conn
0609: .prepareStatement("update testlvc set lvc = ? where a = ?");
0610:
0611: 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";
0612: for (int i = 0; i < 100; i++) {
0613: ByteArrayInputStream string1 = new ByteArrayInputStream(
0614: longString.getBytes("US-ASCII"));
0615: ps.setAsciiStream(1, string1, longString.length());
0616: ps.setInt(2, i);
0617: ps.executeUpdate();
0618: if ((i % 2) == 0)
0619: conn.rollback();
0620: else
0621: conn.commit();
0622:
0623: ByteArrayInputStream emptyString = new ByteArrayInputStream(
0624: new byte[0]);
0625: ps.setAsciiStream(1, emptyString, 0);
0626: ps.executeUpdate();
0627: if ((i % 3) == 0)
0628: conn.rollback();
0629: else
0630: conn.commit();
0631: }
0632:
0633: ps.close();
0634: }
0635:
0636: /**
0637: * long row test of insert/backout case, using setAsciiStream().
0638: * <p>
0639: * The heap tries to make rows all fit on one page if possible. So it
0640: * first asks raw store to try inserting without overflowing rows or
0641: * columns. If that doesn't work it then asks raw store for a mostly
0642: * empty page and tries to insert it there with overflow, If that doesn't
0643: * work then an empty page is picked.
0644: * <p>
0645: * If input parameters are conn,10,2500 - then the second row inserted
0646: * will have the 1st column fit, but the second not fit which caused
0647: * track #2240.
0648: *
0649: * @exception StandardException Standard exception policy.
0650: **/
0651: static void streamTest8(Connection conn, int stream1_len,
0652: int stream2_len) {
0653: System.out.println("Starting streamTest8(conn, " + stream1_len
0654: + ", " + stream2_len + ")");
0655:
0656: ResultSetMetaData met;
0657: ResultSet rs;
0658: Statement stmt;
0659:
0660: String createsql = new String(
0661: "create table t8(a int, b long varchar, c long varchar)");
0662:
0663: String insertsql = new String(
0664: "insert into t8 values (?, ?, ?) ");
0665:
0666: int numStrings = 10;
0667:
0668: byte[][] stream1_byte_array = new byte[numStrings][];
0669: byte[][] stream2_byte_array = new byte[numStrings][];
0670:
0671: // make string size match input sizes.
0672: for (int i = 0; i < numStrings; i++) {
0673: stream1_byte_array[i] = new byte[stream1_len];
0674:
0675: for (int j = 0; j < stream1_len; j++)
0676: stream1_byte_array[i][j] = (byte) ('a' + i);
0677:
0678: stream2_byte_array[i] = new byte[stream2_len];
0679: for (int j = 0; j < stream2_len; j++)
0680: stream2_byte_array[i][j] = (byte) ('A' + i);
0681: }
0682:
0683: try {
0684: conn.setAutoCommit(false);
0685: stmt = conn.createStatement();
0686: stmt.execute(createsql);
0687: conn.commit();
0688:
0689: PreparedStatement insert_ps = conn
0690: .prepareStatement(insertsql);
0691:
0692: for (int i = 0; i < numStrings; i++) {
0693: // create the stream and insert it
0694: insert_ps.setInt(1, i);
0695:
0696: // create the stream and insert it
0697: insert_ps.setAsciiStream(2, new ByteArrayInputStream(
0698: stream1_byte_array[i]), stream1_len);
0699:
0700: // create the stream and insert it
0701: insert_ps.setAsciiStream(3, new ByteArrayInputStream(
0702: stream2_byte_array[i]), stream2_len);
0703:
0704: insert_ps.executeUpdate();
0705:
0706: // just force a scan of the table, no insert is done.
0707: String checkSQL = "insert into t8 select * from t8 where a = -6363";
0708: stmt.execute(checkSQL);
0709: }
0710:
0711: insert_ps.close();
0712: conn.commit();
0713:
0714: rs = stmt.executeQuery("select a, b, c from t8");
0715:
0716: // should return one row.
0717: while (rs.next()) {
0718: // JDBC columns use 1-based counting
0719:
0720: // get the first column as an int
0721: int a = rs.getInt("a");
0722:
0723: // get the second column as a string
0724: String resultString = rs.getString(2);
0725:
0726: // compare result with expected, using fixed length string from
0727: // the streamed byte array
0728: String canon = new String(stream1_byte_array[a],
0729: "US-ASCII");
0730:
0731: if (canon.compareTo(resultString) != 0) {
0732: System.out.println("FAIL -- bad result string:"
0733: + "canon: " + canon + "resultString: "
0734: + resultString);
0735: }
0736:
0737: // get the second column as a string
0738: resultString = rs.getString(3);
0739:
0740: // compare result with expected, using fixed length string from
0741: // the second streamed byte array.
0742: canon = new String(stream2_byte_array[a], "US-ASCII");
0743:
0744: if (canon.compareTo(resultString) != 0) {
0745: System.out.println("FAIL -- bad result string:"
0746: + "canon: " + canon + "resultString: "
0747: + resultString);
0748: }
0749: }
0750:
0751: rs.close();
0752:
0753: stmt.execute("insert into t8 select * from t8");
0754:
0755: stmt.executeUpdate("drop table t8");
0756:
0757: stmt.close();
0758: conn.commit();
0759: } catch (SQLException e) {
0760: dumpSQLExceptions(e);
0761: } catch (Throwable e) {
0762: System.out.println("FAIL -- unexpected exception:"
0763: + e.toString());
0764: }
0765:
0766: System.out.println("Finishing streamTest8(conn, " + stream1_len
0767: + ", " + stream2_len + ")");
0768: }
0769:
0770: /**
0771: * long row test of insert/backout case, using setBinaryStream().
0772: * <p>
0773: * The heap tries to make rows all fit on one page if possible. So it
0774: * first asks raw store to try inserting without overflowing rows or
0775: * columns. If that doesn't work it then asks raw store for a mostly
0776: * empty page and tries to insert it there with overflow, If that doesn't
0777: * work then an empty page is picked.
0778: * <p>
0779: * If input parameters are conn,10,2500 - then the second row inserted
0780: * will have the 1st column fit, but the second not fit which caused
0781: * track #2240.
0782: *
0783: * @exception StandardException Standard exception policy.
0784: **/
0785: static void streamTest9(Connection conn, int stream1_len,
0786: int stream2_len) {
0787: System.out.println("Starting streamTest9(conn, " + stream1_len
0788: + ", " + stream2_len + ")");
0789:
0790: ResultSetMetaData met;
0791: ResultSet rs;
0792: Statement stmt;
0793:
0794: String createsql = new String(
0795: "create table t9(a int, b long varchar for bit data, c long varchar for bit data)");
0796:
0797: String insertsql = new String(
0798: "insert into t9 values (?, ?, ?) ");
0799:
0800: int numStrings = 10;
0801:
0802: byte[][] stream1_byte_array = new byte[numStrings][];
0803: byte[][] stream2_byte_array = new byte[numStrings][];
0804:
0805: // make string size match input sizes.
0806: for (int i = 0; i < numStrings; i++) {
0807: stream1_byte_array[i] = new byte[stream1_len];
0808:
0809: for (int j = 0; j < stream1_len; j++)
0810: stream1_byte_array[i][j] = (byte) ('a' + i);
0811:
0812: stream2_byte_array[i] = new byte[stream2_len];
0813: for (int j = 0; j < stream2_len; j++)
0814: stream2_byte_array[i][j] = (byte) ('A' + i);
0815: }
0816:
0817: try {
0818: conn.setAutoCommit(false);
0819: stmt = conn.createStatement();
0820: stmt.execute(createsql);
0821: conn.commit();
0822:
0823: PreparedStatement insert_ps = conn
0824: .prepareStatement(insertsql);
0825:
0826: for (int i = 0; i < numStrings; i++) {
0827: // create the stream and insert it
0828: insert_ps.setInt(1, i);
0829:
0830: // create the stream and insert it
0831: insert_ps.setBinaryStream(2, new ByteArrayInputStream(
0832: stream1_byte_array[i]), stream1_len);
0833:
0834: // create the stream and insert it
0835: insert_ps.setBinaryStream(3, new ByteArrayInputStream(
0836: stream2_byte_array[i]), stream2_len);
0837:
0838: insert_ps.executeUpdate();
0839:
0840: // just force a scan of the table, no insert is done.
0841: String checkSQL = "insert into t9 select * from t9 where a = -6363";
0842: stmt.execute(checkSQL);
0843: }
0844:
0845: insert_ps.close();
0846: conn.commit();
0847:
0848: rs = stmt.executeQuery("select a, b, c from t9");
0849:
0850: // should return one row.
0851: while (rs.next()) {
0852: // JDBC columns use 1-based counting
0853:
0854: // get the first column as an int
0855: int a = rs.getInt("a");
0856:
0857: // get the second column as a string
0858: byte[] resultString = rs.getBytes(2);
0859:
0860: // compare result with expected
0861: byte[] canon = stream1_byte_array[a];
0862:
0863: if (!byteArrayEquals(canon, 0, canon.length,
0864: resultString, 0, resultString.length)) {
0865: // System.out.println(
0866: // "FAIL -- bad result byte array 1:" +
0867: // "canon: " + ByteArray.hexDump(canon) +
0868: // "resultString: " + ByteArray.hexDump(resultString));
0869: System.out
0870: .println("FAIL -- bad result byte array 1:"
0871: + "canon: " + canon
0872: + "resultString: " + resultString);
0873: }
0874:
0875: // get the second column as a string
0876: resultString = rs.getBytes(3);
0877:
0878: // compare result with expected
0879: canon = stream2_byte_array[a];
0880:
0881: if (!byteArrayEquals(canon, 0, canon.length,
0882: resultString, 0, resultString.length)) {
0883: // System.out.println(
0884: // "FAIL -- bad result byte array 2:" +
0885: // "canon: " + ByteArray.hexDump(canon) +
0886: // "resultString: " + ByteArray.hexDump(resultString));
0887: System.out
0888: .println("FAIL -- bad result byte array 2:"
0889: + "canon: " + canon
0890: + "resultString: " + resultString);
0891: }
0892: }
0893:
0894: rs.close();
0895:
0896: stmt.execute("insert into t9 select * from t9");
0897:
0898: stmt.executeUpdate("drop table t9");
0899:
0900: stmt.close();
0901: conn.commit();
0902: } catch (SQLException e) {
0903: dumpSQLExceptions(e);
0904: } catch (Throwable e) {
0905: System.out.println("FAIL -- unexpected exception:"
0906: + e.toString());
0907: }
0908:
0909: System.out.println("Finishing streamTest9(conn, " + stream1_len
0910: + ", " + stream2_len + ")");
0911: }
0912:
0913: /**
0914: * table with multiple indexes, indexes share columns
0915: * table has more than 4 rows, insert stream into table
0916: * compress table and verify that each index is valid
0917: * @exception StandardException Standard exception policy.
0918: **/
0919: private static void streamTest10(Connection conn) {
0920:
0921: ResultSetMetaData met;
0922: ResultSet rs;
0923: Statement stmt;
0924: System.out.println("Testing 10 starts from here");
0925:
0926: try {
0927: stmt = conn.createStatement();
0928: //create the table
0929: stmt
0930: .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024')");
0931: stmt
0932: .execute("create table tab10 (a int, b int, c long varchar)");
0933: stmt
0934: .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)");
0935: //create the indexes which shares columns
0936: stmt
0937: .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096')");
0938: stmt.execute("create index i_a on tab10 (a)");
0939: stmt.execute("create index i_ab on tab10 (a, b)");
0940: stmt
0941: .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)");
0942:
0943: // insert a null long varchar
0944: stmt.execute("insert into tab10 values(1, 1, '')");
0945: // insert a long varchar with a short text string
0946: stmt
0947: .execute("insert into tab10 values(2, 2, 'test data: a string column inserted as an object')");
0948:
0949: //insert stream into table
0950: for (int i = 0; i < fileName.length; i++) {
0951: // prepare an InputStream from the file
0952: File file = new File(fileName[i]);
0953: fileLength[i] = file.length();
0954: InputStream fileIn = new FileInputStream(file);
0955:
0956: System.out.println("===> testing " + fileName[i]
0957: + " length = " + fileLength[i]);
0958:
0959: // insert a streaming column
0960: PreparedStatement ps = conn
0961: .prepareStatement("insert into tab10 values(?, ?, ?)");
0962: ps.setInt(1, 100 + i);
0963: ps.setInt(2, 100 + i);
0964: ps.setAsciiStream(3, fileIn, (int) fileLength[i]);
0965: try {//if trying to insert data > 32700, there will be an exception
0966: ps.executeUpdate();
0967: System.out
0968: .println("No truncation and hence no error");
0969: } catch (SQLException e) {
0970: if (fileLength[i] > Limits.DB2_LONGVARCHAR_MAXWIDTH
0971: && e.getSQLState().equals("22001")) //was getting data longer than maxValueAllowed
0972: System.out
0973: .println("expected exception for data > "
0974: + Limits.DB2_LONGVARCHAR_MAXWIDTH
0975: + " in length");
0976: else
0977: dumpSQLExceptions(e);
0978: }
0979: fileIn.close();
0980: }
0981:
0982: //execute the compress command
0983: CallableStatement cs = conn
0984: .prepareCall("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)");
0985: cs.setString(1, "APP");
0986: cs.setString(2, "TESTLONGVARCHAR");
0987: cs.setInt(3, 0);
0988: cs.execute();
0989:
0990: //do consistency checking
0991: stmt
0992: .execute("CREATE FUNCTION ConsistencyChecker() RETURNS VARCHAR(128) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_ConsistencyChecker.runConsistencyChecker' LANGUAGE JAVA PARAMETER STYLE JAVA");
0993: stmt.execute("VALUES ConsistencyChecker()");
0994:
0995: stmt.close();
0996:
0997: } catch (SQLException e) {
0998: dumpSQLExceptions(e);
0999: } catch (Throwable e) {
1000: System.out.println("FAIL -- unexpected exception:"
1001: + e.toString());
1002: }
1003: System.out.println("Testing 10 ends in here");
1004: }
1005:
1006: private static void streamTest11(Connection conn) {
1007:
1008: Statement stmt;
1009:
1010: System.out
1011: .println("Test 11 - Can't pass negative length as the stream length for various setXXXStream methods");
1012: try {
1013: stmt = conn.createStatement();
1014: stmt
1015: .execute("create table testLongVarCharInvalidStreamLength (a int, b long varchar, c long varchar for bit data)");
1016: // prepare an InputStream from the file
1017: File file = new File("extin/short.data");
1018: InputStream fileIn = new FileInputStream(file);
1019:
1020: PreparedStatement ps = conn
1021: .prepareStatement("insert into testLongVarCharInvalidStreamLength values(?, ?, ?)");
1022: ps.setInt(1, 100);
1023: try {
1024: System.out
1025: .println("===> testing using setAsciiStream with -2 as length");
1026: ps.setAsciiStream(2, fileIn, -2); //test specifically for bug 4250
1027: System.out
1028: .println("FAIL -- should have gotten exception for -2 param value to setAsciiStream");
1029: } catch (SQLException e) {
1030: if ("XJ025".equals(e.getSQLState()))
1031: System.out.println("PASS -- expected exception:"
1032: + e.toString());
1033: else
1034: dumpSQLExceptions(e);
1035: }
1036:
1037: Reader filer = new InputStreamReader(fileIn, "US-ASCII");
1038: try {
1039: System.out
1040: .println("===> testing using setCharacterStream with -1 as length");
1041: ps.setCharacterStream(2, filer, -1);
1042: System.out
1043: .println("FAIL -- should have gotten exception for -1 param value to setCharacterStream");
1044: } catch (SQLException e) {
1045: if ("XJ025".equals(e.getSQLState()))
1046: System.out.println("PASS -- expected exception:"
1047: + e.toString());
1048: else
1049: dumpSQLExceptions(e);
1050: }
1051:
1052: try {
1053: System.out
1054: .println("===> testing using setBinaryStream with -1 as length");
1055: ps.setBinaryStream(3, fileIn, -1);
1056: System.out
1057: .println("FAIL -- should have gotten exception for -1 param value to setBinaryStream");
1058: } catch (SQLException e) {
1059: if ("XJ025".equals(e.getSQLState()))
1060: System.out.println("PASS -- expected exception:"
1061: + e.toString());
1062: else
1063: dumpSQLExceptions(e);
1064: }
1065:
1066: fileIn.close();
1067: } catch (SQLException e) {
1068: dumpSQLExceptions(e);
1069: } catch (Throwable e) {
1070: System.out.println("FAIL -- unexpected exception:"
1071: + e.toString());
1072: }
1073: System.out
1074: .println("Test 11 - negative stream length tests end in here");
1075: }
1076:
1077: private static void streamTest12(Connection conn) {
1078:
1079: ResultSet rs;
1080: Statement stmt;
1081:
1082: //The following 2 files are for testing the truncation in varchar.
1083: //only non-blank character truncation will throw an exception for varchars.
1084: //max value allowed in varchars is 32672 characters long
1085: String fileName1 = "extin/char32675trailingblanks.data"; // set up a file 32675 characters long but with last 3 characters as blanks
1086: String fileName2 = "extin/char32675.data"; // set up a file 32675 characters long with 3 extra non-blank characters trailing in the end
1087:
1088: System.out
1089: .println("Test 12 - varchar truncation tests start from here");
1090: try {
1091: stmt = conn.createStatement();
1092: stmt
1093: .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096')");
1094: stmt
1095: .execute("create table testVarChar (a int, b varchar(32672))");
1096: //create a table with 4 varchars. This table will be used to try overflow through concatenation
1097: stmt
1098: .execute("create table testConcatenation (a varchar(16350), b varchar(16350), c varchar(16336), d varchar(16336))");
1099: stmt
1100: .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)");
1101: String largeStringA16350 = new String(Formatters
1102: .repeatChar("a", 16350));
1103: String largeStringA16336 = new String(Formatters
1104: .repeatChar("a", 16336));
1105: PreparedStatement ps = conn
1106: .prepareStatement("insert into testConcatenation values (?, ?, ?, ?)");
1107: ps.setString(1, largeStringA16350);
1108: ps.setString(2, largeStringA16350);
1109: ps.setString(3, largeStringA16336);
1110: ps.setString(4, largeStringA16336);
1111: ps.executeUpdate();
1112:
1113: ps = conn
1114: .prepareStatement("insert into testVarChar values(?, ?)");
1115:
1116: // prepare an InputStream from the file which has 3 trailing blanks in the end, so after blank truncation, there won't be any overflow
1117: // try this using setAsciiStream, setCharacterStream, setString and setObject
1118: insertDataUsingAsciiStream(ps, 1, fileName1,
1119: Limits.DB2_VARCHAR_MAXWIDTH);
1120: insertDataUsingCharacterStream(ps, 2, fileName1,
1121: Limits.DB2_VARCHAR_MAXWIDTH);
1122: insertDataUsingStringOrObject(ps, 3,
1123: Limits.DB2_VARCHAR_MAXWIDTH, true, true);
1124: insertDataUsingStringOrObject(ps, 4,
1125: Limits.DB2_VARCHAR_MAXWIDTH, true, false);
1126: System.out
1127: .println("===> testing trailing blanks using concatenation");
1128: insertDataUsingConcat(stmt, 5, Limits.DB2_VARCHAR_MAXWIDTH,
1129: true, VARCHAR);
1130:
1131: // prepare an InputStream from the file which has 3 trailing non-blanks in the end, and hence there would be overflow exception
1132: // try this using setAsciiStream, setCharacterStream, setString and setObject
1133: insertDataUsingAsciiStream(ps, 6, fileName2,
1134: Limits.DB2_VARCHAR_MAXWIDTH);
1135: insertDataUsingCharacterStream(ps, 7, fileName2,
1136: Limits.DB2_VARCHAR_MAXWIDTH);
1137: insertDataUsingStringOrObject(ps, 8,
1138: Limits.DB2_VARCHAR_MAXWIDTH, false, true);
1139: insertDataUsingStringOrObject(ps, 9,
1140: Limits.DB2_VARCHAR_MAXWIDTH, false, false);
1141: System.out
1142: .println("===> testing trailing non-blank characters using concatenation");
1143: insertDataUsingConcat(stmt, 10,
1144: Limits.DB2_VARCHAR_MAXWIDTH, false, VARCHAR);
1145:
1146: rs = stmt.executeQuery("select a, b from testVarChar");
1147: streamTestDataVerification(rs, Limits.DB2_VARCHAR_MAXWIDTH);
1148: } catch (SQLException e) {
1149: dumpSQLExceptions(e);
1150: } catch (Throwable e) {
1151: System.out.println("FAIL -- unexpected exception:"
1152: + e.toString());
1153: }
1154: System.out
1155: .println("Test 12 - varchar truncation tests end in here");
1156: }
1157:
1158: private static void streamTest13(Connection conn) {
1159:
1160: ResultSet rs;
1161: Statement stmt;
1162:
1163: //The following 2 files are for testing the truncation in long varchar.
1164: //any character truncation (including blanks characters) will throw an exception for long varchars.
1165: //max value allowed in long varchars is 32700 characters long
1166: String fileName1 = "extin/char32703trailingblanks.data"; // set up a file 32703 characters long but with last 3 characters as blanks
1167: String fileName2 = "extin/char32703.data"; // set up a file 32703 characters long with 3 extra non-blank characters trailing in the end
1168:
1169: System.out
1170: .println("Test 13 - long varchar truncation tests start from here");
1171: try {
1172: stmt = conn.createStatement();
1173: stmt
1174: .execute("create table testLongVarChars (a int, b long varchar)");
1175: PreparedStatement ps = conn
1176: .prepareStatement("insert into testLongVarChars values(?, ?)");
1177:
1178: // prepare an InputStream from the file which has 3 trailing blanks in the end. For long varchar, this would throw a truncation error
1179: // try this using setAsciiStream, setCharacterStream, setString and setObject
1180: insertDataUsingAsciiStream(ps, 1, fileName1,
1181: Limits.DB2_LONGVARCHAR_MAXWIDTH);
1182: insertDataUsingCharacterStream(ps, 2, fileName1,
1183: Limits.DB2_LONGVARCHAR_MAXWIDTH);
1184: insertDataUsingStringOrObject(ps, 3,
1185: Limits.DB2_LONGVARCHAR_MAXWIDTH, true, true);
1186: insertDataUsingStringOrObject(ps, 4,
1187: Limits.DB2_LONGVARCHAR_MAXWIDTH, true, false);
1188: //bug 5600- Can't test data overflow in longvarchar using concatenation because longvarchar concatenated string can't be longer than 32700
1189: //System.out.println("===> testing trailing blanks using concatenation");
1190: //insertDataUsingConcat(stmt, 5, Limits.DB2_LONGVARCHAR_MAXWIDTH, true, true);
1191:
1192: // prepare an InputStream from the file which has 3 trailing non-blanks in the end, and hence there would be overflow exception
1193: // try this using setAsciiStream, setCharacterStream, setString and setObject
1194: insertDataUsingAsciiStream(ps, 6, fileName2,
1195: Limits.DB2_LONGVARCHAR_MAXWIDTH);
1196: insertDataUsingCharacterStream(ps, 7, fileName2,
1197: Limits.DB2_LONGVARCHAR_MAXWIDTH);
1198: insertDataUsingStringOrObject(ps, 7,
1199: Limits.DB2_LONGVARCHAR_MAXWIDTH, false, true);
1200: insertDataUsingStringOrObject(ps, 9,
1201: Limits.DB2_LONGVARCHAR_MAXWIDTH, false, false);
1202: //bug 5600 - Can't test data overflow in longvarchar using concatenation because longvarchar concatenated string can't be longer than 32700
1203: //System.out.println("===> testing trailing non-blank characters using concatenation");
1204: //insertDataUsingConcat(stmt, 10, Limits.DB2_LONGVARCHAR_MAXWIDTH, false, true);
1205:
1206: rs = stmt.executeQuery("select a, b from testLongVarChars");
1207: streamTestDataVerification(rs,
1208: Limits.DB2_LONGVARCHAR_MAXWIDTH);
1209: } catch (SQLException e) {
1210: dumpSQLExceptions(e);
1211: } catch (Throwable e) {
1212: System.out.println("FAIL -- unexpected exception:"
1213: + e.toString());
1214: }
1215: System.out
1216: .println("Test 13 - long varchar truncation tests end in here");
1217: }
1218:
1219: /**
1220: * Test truncation behavior for clobs
1221: * Test is similar to streamTest12 except that this test tests for clob column
1222: * @param conn
1223: */
1224: private static void streamTest14(Connection conn) {
1225:
1226: ResultSet rs;
1227: Statement stmt;
1228:
1229: //The following 2 files are for testing the truncation in clob
1230: //only non-blank character truncation will throw an exception for clob.
1231: //max value allowed in clob is 2G-1
1232: String fileName1 = "extin/char32675trailingblanks.data"; // set up a file 32675 characters long but with last 3 characters as blanks
1233: String fileName2 = "extin/char32675.data"; // set up a file 32675 characters long with 3 extra non-blank characters trailing in the end
1234:
1235: System.out
1236: .println("Test 14 - clob truncation tests start from here");
1237: try {
1238: stmt = conn.createStatement();
1239: stmt
1240: .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096')");
1241: stmt.execute("drop table testConcatenation");
1242: stmt
1243: .execute("create table testClob (a int, b clob(32672))");
1244: //create a table with 4 varchars. This table will be used to try overflow through concatenation
1245:
1246: stmt
1247: .execute("create table testConcatenation (a clob(16350), b clob(16350), c clob(16336), d clob(16336))");
1248: stmt
1249: .executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)");
1250: String largeStringA16350 = new String(Formatters
1251: .repeatChar("a", 16350));
1252: String largeStringA16336 = new String(Formatters
1253: .repeatChar("a", 16336));
1254: PreparedStatement ps = conn
1255: .prepareStatement("insert into testConcatenation values (?, ?, ?, ?)");
1256: ps.setString(1, largeStringA16350);
1257: ps.setString(2, largeStringA16350);
1258: ps.setString(3, largeStringA16336);
1259: ps.setString(4, largeStringA16336);
1260: ps.executeUpdate();
1261:
1262: ps = conn
1263: .prepareStatement("insert into testClob values(?, ?)");
1264:
1265: // prepare an InputStream from the file which has 3 trailing blanks in the end, so after blank truncation, there won't be any overflow
1266: // try this using setAsciiStream, setCharacterStream, setString and setObject
1267: insertDataUsingAsciiStream(ps, 1, fileName1,
1268: Limits.DB2_VARCHAR_MAXWIDTH);
1269: insertDataUsingCharacterStream(ps, 2, fileName1,
1270: Limits.DB2_VARCHAR_MAXWIDTH);
1271: insertDataUsingStringOrObject(ps, 3,
1272: Limits.DB2_VARCHAR_MAXWIDTH, true, true);
1273: insertDataUsingStringOrObject(ps, 4,
1274: Limits.DB2_VARCHAR_MAXWIDTH, true, false);
1275: System.out
1276: .println("===> testing trailing blanks using concatenation");
1277: insertDataUsingConcat(stmt, 5, Limits.DB2_VARCHAR_MAXWIDTH,
1278: true, CLOB);
1279:
1280: // prepare an InputStream from the file which has 3 trailing non-blanks in the end, and hence there would be overflow exception
1281: // try this using setAsciiStream, setCharacterStream, setString and setObject
1282: insertDataUsingAsciiStream(ps, 6, fileName2,
1283: Limits.DB2_VARCHAR_MAXWIDTH);
1284: insertDataUsingCharacterStream(ps, 7, fileName2,
1285: Limits.DB2_VARCHAR_MAXWIDTH);
1286: insertDataUsingStringOrObject(ps, 8,
1287: Limits.DB2_VARCHAR_MAXWIDTH, false, true);
1288: insertDataUsingStringOrObject(ps, 9,
1289: Limits.DB2_VARCHAR_MAXWIDTH, false, false);
1290: System.out
1291: .println("===> testing trailing non-blank characters using concatenation");
1292: insertDataUsingConcat(stmt, 10,
1293: Limits.DB2_VARCHAR_MAXWIDTH, false, CLOB);
1294:
1295: rs = stmt.executeQuery("select a, b from testVarChar");
1296: streamTestDataVerification(rs, Limits.DB2_VARCHAR_MAXWIDTH);
1297: } catch (SQLException e) {
1298: dumpSQLExceptions(e);
1299: } catch (Throwable e) {
1300: System.out.println("FAIL -- unexpected exception:"
1301: + e.toString());
1302: }
1303: System.out
1304: .println("Test 14 - clob truncation tests end in here");
1305: }
1306:
1307: /**
1308: * Streams are not re-used. This test tests the fix for
1309: * DERBY-500. If an update statement has multiple rows that
1310: * is affected, and one of the parameter values is a stream,
1311: * the update will fail because streams are not re-used.
1312: * @param conn database connection
1313: */
1314: private static void derby500Test(Connection conn) {
1315:
1316: Statement stmt;
1317:
1318: System.out.println("======================================");
1319: System.out.println("START DERBY-500 TEST ");
1320:
1321: try {
1322: stmt = conn.createStatement();
1323: conn.setAutoCommit(false);
1324: stmt
1325: .execute("CREATE TABLE t1 ("
1326: + "id INTEGER NOT NULL,"
1327: + "mname VARCHAR( 254 ) NOT NULL,"
1328: + "mvalue INT NOT NULL,"
1329: + "bytedata BLOB NOT NULL,"
1330: + "chardata CLOB NOT NULL,"
1331: + "PRIMARY KEY ( id ))");
1332:
1333: PreparedStatement ps = conn
1334: .prepareStatement("insert into t1 values (?,?,?,?,?)");
1335:
1336: // insert 10 rows.
1337: int rowCount = 0;
1338: // use blob and clob values
1339: int len = 10000;
1340: byte buf[] = new byte[len];
1341: char cbuf[] = new char[len];
1342: char orig = 'c';
1343: for (int i = 0; i < len; i++) {
1344: buf[i] = (byte) orig;
1345: cbuf[i] = orig;
1346: }
1347: int randomOffset = 9998;
1348: buf[randomOffset] = (byte) 'e';
1349: cbuf[randomOffset] = 'e';
1350: System.out.println("Inserting rows ");
1351: for (int i = 0; i < 10; i++) {
1352: ps.setInt(1, i);
1353: ps.setString(2, "mname" + i);
1354: ps.setInt(3, 0);
1355: ps.setBinaryStream(4, new ByteArrayInputStream(buf),
1356: len);
1357: ps
1358: .setAsciiStream(5,
1359: new ByteArrayInputStream(buf), len);
1360: rowCount += ps.executeUpdate();
1361: }
1362: conn.commit();
1363: System.out.println("Rows inserted =" + rowCount);
1364:
1365: //conn.commit();
1366: PreparedStatement pss = conn
1367: .prepareStatement(" select chardata,bytedata from t1 where id = ?");
1368: verifyDerby500Test(pss, buf, cbuf, 0, 10, true);
1369:
1370: // do the update, update must qualify more than 1 row and update will fail
1371: // as currently we dont allow stream values to be re-used
1372: PreparedStatement psu = conn
1373: .prepareStatement("update t1 set bytedata = ? "
1374: + ", chardata = ? where mvalue = ? ");
1375:
1376: buf[randomOffset + 1] = (byte) 'u';
1377: cbuf[randomOffset + 1] = 'u';
1378: rowCount = 0;
1379: System.out.println("Update qualifies many rows + streams");
1380:
1381: try {
1382: psu.setBinaryStream(1, new ByteArrayInputStream(buf),
1383: len);
1384: psu.setCharacterStream(2, new CharArrayReader(cbuf),
1385: len);
1386: psu.setInt(3, 0);
1387: rowCount += psu.executeUpdate();
1388: System.out.println("DERBY500 #1 Rows updated ="
1389: + rowCount);
1390:
1391: } catch (SQLException sqle) {
1392: System.out
1393: .println("EXPECTED EXCEPTION - streams cannot be re-used");
1394: expectedException(sqle);
1395: conn.rollback();
1396: }
1397:
1398: //verify data
1399: //set back buffer value to what was inserted.
1400: buf[randomOffset + 1] = (byte) orig;
1401: cbuf[randomOffset + 1] = orig;
1402:
1403: verifyDerby500Test(pss, buf, cbuf, 0, 10, true);
1404:
1405: PreparedStatement psu2 = conn
1406: .prepareStatement("update t1 set bytedata = ? "
1407: + ", chardata = ? where id = ? ");
1408:
1409: buf[randomOffset + 1] = (byte) 'u';
1410: cbuf[randomOffset + 1] = 'u';
1411:
1412: rowCount = 0;
1413: try {
1414: psu2.setBinaryStream(1, new ByteArrayInputStream(buf),
1415: len);
1416: psu2.setAsciiStream(2, new ByteArrayInputStream(buf),
1417: len);
1418: psu2.setInt(3, 0);
1419: rowCount += psu2.executeUpdate();
1420: System.out.println("DERBY500 #2 Rows updated ="
1421: + rowCount);
1422:
1423: } catch (SQLException sqle) {
1424: System.out
1425: .println("UNEXPECTED EXCEPTION - update should have actually gone through");
1426: dumpSQLExceptions(sqle);
1427: }
1428: conn.commit();
1429: verifyDerby500Test(pss, buf, cbuf, 0, 1, true);
1430:
1431: // delete
1432: // as currently we dont allow stream values to be re-used
1433: PreparedStatement psd = conn
1434: .prepareStatement("delete from t1 where mvalue = ?");
1435:
1436: rowCount = 0;
1437: try {
1438: psd.setInt(1, 0);
1439: rowCount += psd.executeUpdate();
1440: rowCount += psd.executeUpdate();
1441: System.out.println("DERBY500 #3 Rows deleted ="
1442: + rowCount);
1443:
1444: } catch (SQLException sqle) {
1445: System.out
1446: .println("UNEXPECTED EXCEPTION - delete should have actually gone through");
1447: dumpSQLExceptions(sqle);
1448: }
1449:
1450: conn.commit();
1451: //verify data
1452:
1453: verifyDerby500Test(pss, buf, cbuf, 0, 10, true);
1454:
1455: PreparedStatement psd2 = conn
1456: .prepareStatement("delete from t1 where id = ?");
1457:
1458: rowCount = 0;
1459: try {
1460: psd2.setInt(1, 0);
1461: rowCount += psd2.executeUpdate();
1462: System.out.println("DERBY500 #4 Rows deleted ="
1463: + rowCount);
1464:
1465: } catch (SQLException sqle) {
1466: System.out
1467: .println("UNEXPECTED EXCEPTION - delete should have actually gone through");
1468: dumpSQLExceptions(sqle);
1469: }
1470: conn.commit();
1471: verifyDerby500Test(pss, buf, cbuf, 1, 2, true);
1472:
1473: try {
1474: ps.setInt(1, 11);
1475: rowCount += ps.executeUpdate();
1476: System.out.println("Rows inserted = " + rowCount);
1477: } catch (SQLException sqle) {
1478: System.out
1479: .println("EXPECTED EXCEPTION - streams cannot be re-used");
1480: expectedException(sqle);
1481: conn.rollback();
1482: }
1483:
1484: stmt.execute("drop table t1");
1485: conn.commit();
1486: stmt.close();
1487: pss.close();
1488: psu2.close();
1489: psu.close();
1490: psd.close();
1491: psd2.close();
1492: System.out.println("END DERBY-500 TEST ");
1493: System.out
1494: .println("======================================");
1495:
1496: } catch (SQLException sqle) {
1497: dumpSQLExceptions(sqle);
1498: } catch (Exception e) {
1499: System.out.println("DERBY-500 TEST FAILED!");
1500: e.printStackTrace();
1501: }
1502:
1503: }
1504:
1505: /**
1506: * Test that DERBY500 fix did not change the behavior for varchar,
1507: * char, long varchar types when stream api is used.
1508: * Currently, for char,varchar and long varchar - the stream is
1509: * read once and materialized, hence the materialized stream value
1510: * will/can be used for multiple executions of the prepared statement
1511: * @param conn database connection
1512: */
1513: private static void derby500_verifyVarcharStreams(Connection conn) {
1514:
1515: Statement stmt;
1516:
1517: System.out.println("======================================");
1518: System.out.println("START DERBY-500 TEST for varchar ");
1519:
1520: try {
1521: stmt = conn.createStatement();
1522: stmt.execute("CREATE TABLE t1 (" + "id INTEGER NOT NULL,"
1523: + "mname VARCHAR( 254 ) NOT NULL,"
1524: + "mvalue INT NOT NULL," + "vc varchar(32500),"
1525: + "lvc long varchar NOT NULL,"
1526: + "PRIMARY KEY ( id ))");
1527:
1528: PreparedStatement ps = conn
1529: .prepareStatement("insert into t1 values (?,?,?,?,?)");
1530:
1531: // insert 10 rows.
1532: int rowCount = 0;
1533: // use blob and clob values
1534: int len = 10000;
1535: byte buf[] = new byte[len];
1536: char cbuf[] = new char[len];
1537: char orig = 'c';
1538: for (int i = 0; i < len; i++) {
1539: buf[i] = (byte) orig;
1540: cbuf[i] = orig;
1541: }
1542: int randomOffset = 9998;
1543: buf[randomOffset] = (byte) 'e';
1544: cbuf[randomOffset] = 'e';
1545: for (int i = 0; i < 10; i++) {
1546: ps.setInt(1, i);
1547: ps.setString(2, "mname" + i);
1548: ps.setInt(3, 0);
1549: ps
1550: .setCharacterStream(4,
1551: new CharArrayReader(cbuf), len);
1552: ps
1553: .setAsciiStream(5,
1554: new ByteArrayInputStream(buf), len);
1555: rowCount += ps.executeUpdate();
1556: }
1557: conn.commit();
1558: System.out.println("Rows inserted =" + rowCount);
1559:
1560: try {
1561: ps.setInt(1, 11);
1562: rowCount += ps.executeUpdate();
1563: } catch (SQLException sqle) {
1564: System.out
1565: .println("UNEXPECTED EXCEPTION - streams cannot be "
1566: + "re-used but in case of varchar, stream is materialized the"
1567: + " first time around. So multiple executions using streams should "
1568: + " work fine. ");
1569: dumpSQLExceptions(sqle);
1570: }
1571:
1572: PreparedStatement pss = conn
1573: .prepareStatement(" select lvc,vc from t1 where id = ?");
1574: verifyDerby500Test(pss, buf, cbuf, 0, 10, false);
1575:
1576: // do the update, update must qualify more than 1 row and update will
1577: // pass for char,varchar,long varchar columns.
1578: PreparedStatement psu = conn
1579: .prepareStatement("update t1 set vc = ? "
1580: + ", lvc = ? where mvalue = ? ");
1581:
1582: buf[randomOffset + 1] = (byte) 'u';
1583: cbuf[randomOffset + 1] = 'u';
1584: rowCount = 0;
1585: try {
1586: psu.setAsciiStream(1, new ByteArrayInputStream(buf),
1587: len);
1588: psu.setCharacterStream(2, new CharArrayReader(cbuf),
1589: len);
1590: psu.setInt(3, 0);
1591: rowCount += psu.executeUpdate();
1592: } catch (SQLException sqle) {
1593: System.out
1594: .println("EXPECTED EXCEPTION - streams cannot be re-used");
1595: expectedException(sqle);
1596: }
1597: System.out
1598: .println("DERBY500 for varchar #1 Rows updated ="
1599: + rowCount);
1600:
1601: //verify data
1602: verifyDerby500Test(pss, buf, cbuf, 0, 10, false);
1603:
1604: PreparedStatement psu2 = conn
1605: .prepareStatement("update t1 set vc = ? "
1606: + ", lvc = ? where id = ? ");
1607:
1608: buf[randomOffset + 1] = (byte) 'h';
1609: cbuf[randomOffset + 1] = 'h';
1610:
1611: rowCount = 0;
1612: try {
1613: psu2.setAsciiStream(1, new ByteArrayInputStream(buf),
1614: len);
1615: psu2.setAsciiStream(2, new ByteArrayInputStream(buf),
1616: len);
1617: psu2.setInt(3, 0);
1618: rowCount += psu2.executeUpdate();
1619: } catch (SQLException sqle) {
1620: System.out
1621: .println("UNEXPECTED EXCEPTION - update should have actually gone through");
1622: dumpSQLExceptions(sqle);
1623: }
1624: conn.commit();
1625: System.out
1626: .println("DERBY500 for varchar #2 Rows updated ="
1627: + rowCount);
1628: verifyDerby500Test(pss, buf, cbuf, 0, 1, false);
1629:
1630: // delete
1631: // as currently we dont allow stream values to be re-used
1632: PreparedStatement psd = conn
1633: .prepareStatement("delete from t1 where mvalue = ?");
1634:
1635: rowCount = 0;
1636: try {
1637: psd.setInt(1, 0);
1638: rowCount += psd.executeUpdate();
1639: rowCount += psd.executeUpdate();
1640: } catch (SQLException sqle) {
1641: System.out
1642: .println("UNEXPECTED EXCEPTION - delete should have actually gone through");
1643: dumpSQLExceptions(sqle);
1644: }
1645: System.out.println("DERBY500 for varchar #3 Rows deleted ="
1646: + rowCount);
1647:
1648: //verify data
1649: verifyDerby500Test(pss, buf, cbuf, 0, 10, false);
1650:
1651: PreparedStatement psd2 = conn
1652: .prepareStatement("delete from t1 where id = ?");
1653:
1654: rowCount = 0;
1655: try {
1656: psd2.setInt(1, 0);
1657: rowCount += psd2.executeUpdate();
1658: } catch (SQLException sqle) {
1659: System.out
1660: .println("UNEXPECTED EXCEPTION - delete should have actually gone through");
1661: dumpSQLExceptions(sqle);
1662: }
1663: conn.commit();
1664: System.out
1665: .println("DERBY500 for varchar #4 Rows deleted ="
1666: + rowCount);
1667: verifyDerby500Test(pss, buf, cbuf, 1, 2, false);
1668:
1669: stmt.execute("drop table t1");
1670: conn.commit();
1671: stmt.close();
1672: pss.close();
1673: psu2.close();
1674: psu.close();
1675: psd.close();
1676: psd2.close();
1677: System.out.println("END DERBY-500 TEST for varchar");
1678: System.out
1679: .println("======================================");
1680:
1681: } catch (SQLException sqle) {
1682: dumpSQLExceptions(sqle);
1683: } catch (Exception e) {
1684: System.out.println("DERBY-500 TEST for varchar FAILED!");
1685: e.printStackTrace();
1686: }
1687:
1688: }
1689:
1690: /**
1691: * verify the data in the derby500Test
1692: * @param ps select preparedstatement
1693: * @param buf byte array to compare the blob data
1694: * @param cbuf char array to compare the clob data
1695: * @param startId start id of the row to check data for
1696: * @param endId end id of the row to check data for
1697: * @param binaryType flag to indicate if the second column in resultset
1698: * is a binary type or not. true for binary type
1699: * @throws Exception
1700: */
1701: private static void verifyDerby500Test(PreparedStatement ps,
1702: byte[] buf, char[] cbuf, int startId, int endId,
1703: boolean binaryType) throws Exception {
1704: byte[] retrieveData = null;
1705: int rowCount = 0;
1706: ResultSet rs = null;
1707: for (int i = startId; i < endId; i++) {
1708: ps.setInt(1, i);
1709: rs = ps.executeQuery();
1710: if (rs.next()) {
1711: compareCharArray(rs.getCharacterStream(1), cbuf,
1712: cbuf.length);
1713: if (binaryType)
1714: byteArrayEquals(rs.getBytes(2), 0, buf.length, buf,
1715: 0, buf.length);
1716: else
1717: compareCharArray(rs.getCharacterStream(2), cbuf,
1718: cbuf.length);
1719:
1720: rowCount++;
1721: }
1722: }
1723: System.out.println("Rows selected =" + rowCount);
1724: rs.close();
1725: }
1726:
1727: /**
1728: * compare char data
1729: * @param stream data from stream to compare
1730: * @param compare base data to compare against
1731: * @param length compare length number of chars.
1732: * @throws Exception
1733: */
1734: private static void compareCharArray(Reader stream, char[] compare,
1735: int length) throws Exception {
1736: int c1 = 0;
1737: int i = 0;
1738: do {
1739: c1 = stream.read();
1740: if (c1 != compare[i++]) {
1741: System.out
1742: .println("FAIL -- MISMATCH in data stored versus data retrieved at "
1743: + (i - 1));
1744: break;
1745: }
1746: length--;
1747: } while (c1 != -1 && length > 0);
1748:
1749: }
1750:
1751: private static void expectedException(SQLException sqle) {
1752:
1753: while (sqle != null) {
1754: String sqlState = sqle.getSQLState();
1755: if (sqlState == null) {
1756: sqlState = "<NULL>";
1757: }
1758: System.out.println("EXPECTED SQL Exception: (" + sqlState
1759: + ") " + sqle.getMessage());
1760:
1761: sqle = sqle.getNextException();
1762: }
1763: }
1764:
1765: private static void streamTestDataVerification(ResultSet rs,
1766: int maxValueAllowed) throws Exception {
1767: ResultSetMetaData met;
1768:
1769: met = rs.getMetaData();
1770: byte[] buff = new byte[128];
1771: // fetch all rows back, get the varchar and/ long varchar columns as streams.
1772: while (rs.next()) {
1773: // get the first column as an int
1774: int a = rs.getInt("a");
1775: // get the second column as a stream
1776: InputStream fin = rs.getAsciiStream(2);
1777: int columnSize = 0;
1778: for (;;) {
1779: int size = fin.read(buff);
1780: if (size == -1)
1781: break;
1782: columnSize += size;
1783: }
1784: if ((a >= 1 && a <= 5) && columnSize == maxValueAllowed)
1785: System.out.println("===> verified length "
1786: + maxValueAllowed);
1787: else
1788: System.out.println("test failed, columnSize should be "
1789: + maxValueAllowed + " but it is" + columnSize);
1790: }
1791: }
1792:
1793: //blankPadding
1794: // true means excess trailing blanks
1795: // false means excess trailing non-blank characters
1796: // @param tblType table type, depending on the table type, the corresponding
1797: // table is used. for varchar - testVarChar , for long varchar - testVarChars,
1798: // and for clob - testClob is used
1799: private static void insertDataUsingConcat(Statement stmt,
1800: int intValue, int maxValueAllowed, boolean blankPadding,
1801: int tblType) throws Exception {
1802: String sql;
1803:
1804: switch (tblType) {
1805: case LONGVARCHAR:
1806: sql = "insert into testLongVarChars select " + intValue
1807: + ", a||b||";
1808: break;
1809: case CLOB:
1810: sql = "insert into testClob select " + intValue
1811: + ", c||d||";
1812: break;
1813: default:
1814: sql = "insert into testVarChar select " + intValue
1815: + ", c||d||";
1816: }
1817:
1818: if (blankPadding) //try overflow with trailing blanks
1819: sql = sql.concat("' ' from testConcatenation");
1820: else
1821: //try overflow with trailing non-blank characters
1822: sql = sql.concat("'123' from testConcatenation");
1823:
1824: //for varchars, trailing blank truncation will not throw an exception. Only non-blank characters will cause truncation error
1825: //for long varchars, any character truncation will throw an exception.
1826: try {
1827: stmt.execute(sql);
1828: System.out.println("No truncation and hence no error.");
1829: } catch (SQLException e) {
1830: if (e.getSQLState().equals("22001")) //truncation error
1831: System.out.println("expected exception for data > "
1832: + maxValueAllowed + " in length");
1833: else
1834: dumpSQLExceptions(e);
1835: }
1836: }
1837:
1838: //blankPadding
1839: // true means excess trailing blanks
1840: // false means excess trailing non-blank characters
1841: //testUsingString
1842: // true means try setString method for overflow
1843: // false means try setObject method for overflow
1844: private static void insertDataUsingStringOrObject(
1845: PreparedStatement ps, int intValue, int maxValueAllowed,
1846: boolean blankPadding, boolean testUsingString)
1847: throws Exception {
1848: StringBuffer sb = new StringBuffer(maxValueAllowed);
1849: for (int i = 0; i < maxValueAllowed; i++)
1850: sb.append('q');
1851:
1852: String largeString = new String(sb);
1853: if (blankPadding) {
1854: largeString = largeString.concat(" ");
1855: System.out.print("===> testing trailing blanks(using ");
1856: } else {
1857: largeString = largeString.concat("123");
1858: System.out.print("===> testing trailing non-blanks(using ");
1859: }
1860:
1861: ps.setInt(1, intValue);
1862: if (testUsingString) {
1863: System.out.println("setString) length = "
1864: + largeString.length());
1865: ps.setString(2, largeString);
1866: } else {
1867: System.out.println("setObject) length = "
1868: + largeString.length());
1869: ps.setObject(2, largeString);
1870: }
1871:
1872: //for varchars, trailing blank truncation will not throw an exception. Only non-blank characters cause truncation error
1873: //for long varchars, any character truncation will throw an exception.
1874: try {
1875: ps.executeUpdate();
1876: System.out.println("No truncation and hence no error");
1877: } catch (SQLException e) {
1878: if (largeString.length() > maxValueAllowed
1879: && e.getSQLState().equals("22001")) //truncation error
1880: System.out.println("expected exception for data > "
1881: + maxValueAllowed + " in length");
1882: else
1883: dumpSQLExceptions(e);
1884: }
1885: }
1886:
1887: private static void insertDataUsingCharacterStream(
1888: PreparedStatement ps, int intValue, String fileName,
1889: int maxValueAllowed) throws Exception {
1890: File file = new File(fileName);
1891: InputStream fileIn = new FileInputStream(file);
1892: Reader filer = new InputStreamReader(fileIn, "US-ASCII");
1893: System.out.println("===> testing(using setCharacterStream) "
1894: + fileName + " length = " + file.length());
1895: ps.setInt(1, intValue);
1896: // insert a streaming column
1897: ps.setCharacterStream(2, filer, (int) file.length());
1898: //for varchars, trailing blank truncation will not throw an exception. Only non-blank characters cause truncation error
1899: //for long varchars, any character truncation will throw an exception.
1900: try {
1901: ps.executeUpdate();
1902: System.out.println("No truncation and hence no error");
1903: } catch (SQLException e) {
1904: if (file.length() > maxValueAllowed
1905: && e.getSQLState().equals("22001")) //truncation error
1906: System.out.println("expected exception for data > "
1907: + maxValueAllowed + " in length");
1908: else
1909: TestUtil.dumpSQLExceptions(e, true);
1910: }
1911: filer.close();
1912: }
1913:
1914: private static void insertDataUsingAsciiStream(
1915: PreparedStatement ps, int intValue, String fileName,
1916: int maxValueAllowed) throws Exception {
1917: File file = new File(fileName);
1918: InputStream fileIn = new FileInputStream(file);
1919: System.out.println("===> testing(using setAsciiStream) "
1920: + fileName + " length = " + file.length());
1921: // insert a streaming column
1922: ps.setInt(1, intValue);
1923: ps.setAsciiStream(2, fileIn, (int) file.length());
1924: //for varchars, trailing blank truncation will not throw an exception. Only non-blank characters cause truncation error
1925: //for long varchars, any character truncation will throw an exception.
1926: try {
1927: ps.executeUpdate();
1928: System.out.println("No truncation and hence no error");
1929: } catch (SQLException e) {
1930: if (file.length() > maxValueAllowed
1931: && e.getSQLState().equals("22001")) //truncation error
1932: System.out.println("expected exception for data > "
1933: + maxValueAllowed + " in length");
1934: else
1935: TestUtil.dumpSQLExceptions(e, true);
1936: }
1937: fileIn.close();
1938: }
1939:
1940: static void verifyLength(int a, int columnSize, long[] fileLength) {
1941: for (int i = 0; i < fileLength.length; i++) {
1942: if ((a == (100 + i)) || (a == (10000 + i))) {
1943: if (columnSize != fileLength[i])
1944: System.out
1945: .println("test failed, columnSize should be "
1946: + fileLength[i]
1947: + ", but it is "
1948: + columnSize + ", i = " + i);
1949: else
1950: System.out.println("===> verified length "
1951: + fileLength[i]);
1952: }
1953: }
1954: }
1955:
1956: static void verifyExistence(Connection conn, int key, String base,
1957: long length) throws Exception {
1958: if (!pad(base, length).equals(getLongString(conn, key)))
1959: throw new Exception("failed to find value " + base
1960: + "... at key " + key);
1961: }
1962:
1963: static String getLongString(Connection conn, int key)
1964: throws Exception {
1965: Statement s = conn.createStatement();
1966: ResultSet rs = s.executeQuery("select b from foo where a = "
1967: + key);
1968: if (!rs.next())
1969: throw new Exception("there weren't any rows for key = "
1970: + key);
1971: String answer = rs.getString(1);
1972: if (rs.next())
1973: throw new Exception("there were multiple rows for key = "
1974: + key);
1975: rs.close();
1976: s.close();
1977: return answer;
1978: }
1979:
1980: static String pad(String base, long length) {
1981: StringBuffer b = new StringBuffer(base);
1982: for (long i = 1; b.length() < length; i++)
1983: b.append(" " + i);
1984: return b.toString();
1985: }
1986:
1987: static int insertLongString(Connection conn, int key, String data,
1988: boolean binaryColumn) throws Exception {
1989: PreparedStatement ps = conn
1990: .prepareStatement("insert into foo values(" + key
1991: + ", ?)");
1992: return streamInStringCol(ps, data, binaryColumn);
1993: }
1994:
1995: static int updateLongString(Connection conn, int oldkey, int newkey)
1996: throws Exception {
1997: PreparedStatement ps = conn
1998: .prepareStatement("update foo set a = ?, b = ? where a = "
1999: + oldkey);
2000:
2001: String updateString = pad("", newkey);
2002: ByteArrayInputStream bais = new ByteArrayInputStream(
2003: updateString.getBytes("US-ASCII"));
2004: ps.setInt(1, newkey);
2005: ps.setAsciiStream(2, bais, updateString.length());
2006: int nRows = ps.executeUpdate();
2007: ps.close();
2008: return nRows;
2009: }
2010:
2011: static int streamInStringCol(PreparedStatement ps, String data,
2012: boolean binaryColumn) throws Exception {
2013: int nRows = 0;
2014:
2015: if (data == null) {
2016: ps.setAsciiStream(1, null, 0);
2017: nRows = ps.executeUpdate();
2018: } else {
2019: ByteArrayInputStream bais = new ByteArrayInputStream(data
2020: .getBytes("US-ASCII"));
2021: if (binaryColumn)
2022: ps.setBinaryStream(1, bais, data.length());
2023: else
2024: ps.setAsciiStream(1, bais, data.length());
2025: nRows = ps.executeUpdate();
2026: bais.close();
2027: }
2028: return nRows;
2029: }
2030:
2031: public static int streamInLongCol(PreparedStatement ps, Object data)
2032: throws Exception {
2033: String s = (String) data;
2034: ByteArrayInputStream bais = new ByteArrayInputStream(s
2035: .getBytes("US-ASCII"));
2036: ps.setAsciiStream(1, bais, s.length());
2037: int nRows = ps.executeUpdate();
2038: bais.close();
2039: return nRows;
2040: }
2041:
2042: /**
2043: Compare two byte arrays using value equality.
2044: Two byte arrays are equal if their length is
2045: identical and their contents are identical.
2046: */
2047: private static boolean byteArrayEquals(byte[] a, int aOffset,
2048: int aLength, byte[] b, int bOffset, int bLength) {
2049: if (aLength != bLength)
2050: return false;
2051:
2052: for (int i = 0; i < aLength; i++) {
2053: if (a[i + aOffset] != b[i + bOffset])
2054: return false;
2055: }
2056: return true;
2057: }
2058:
2059: static private void dumpSQLExceptions(SQLException se) {
2060: System.out.println("FAIL -- unexpected exception: "
2061: + se.toString());
2062: se.printStackTrace();
2063: while (se != null) {
2064: System.out.println("SQLSTATE(" + se.getSQLState() + "):"
2065: + se.getMessage());
2066: se = se.getNextException();
2067: }
2068: }
2069:
2070: }
|