0001: /*
0002:
0003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.concateTests
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.lang;
0023:
0024: import java.io.*;
0025: import java.sql.*;
0026: import java.util.Arrays;
0027:
0028: import org.apache.derby.tools.ij;
0029: import org.apache.derbyTesting.functionTests.util.Formatters;
0030:
0031: import java.io.ByteArrayInputStream;
0032:
0033: /**
0034: Concatenation tests for various datatypes
0035: */
0036: public class concateTests {
0037:
0038: private static String[] concatenatedSQLTypesNames = {
0039: /*0*/"CHAR",
0040: /*1*/"VARCHAR",
0041: /*2*/"LONG VARCHAR",
0042: /*3*/"CLOB",
0043: /*4*/"CHAR () FOR BIT DATA",
0044: /*5*/"VARCHAR () FOR BIT DATA",
0045: /*6*/"LONG VARCHAR FOR BIT DATA",
0046: /*7*/"BLOB",
0047:
0048: };
0049:
0050: public static void main(String[] argv) throws Throwable {
0051: ij.getPropertyArg(argv);
0052: Connection conn = ij.startJBMS();
0053:
0054: testCharConcatenation(conn);
0055: testCharForBitDataConcatenation(conn);
0056: }
0057:
0058: public static void testCharConcatenation(Connection conn)
0059: throws Throwable {
0060: try {
0061: System.out
0062: .println("Test1 - CHAR, VARCHAR, LONGVARCHAR and CLOB concatenation tests");
0063:
0064: String columnC1value;
0065: String columnC2value = Formatters.repeatChar("a", 40);
0066: String columnVC1value;
0067: String columnVC2value;
0068: String columnVC3value = "z";
0069: String columnLVC1value;
0070: String columnLVC2value = Formatters.repeatChar("b", 32698);
0071: StringBuffer tempStringBuffer = new StringBuffer();
0072:
0073: Statement s = conn.createStatement();
0074: try { //this is if we ever run the test against DB2, we want to make sure table doesn't already exist in DB2
0075: s.executeUpdate("drop table t1");
0076: } catch (Exception ex) {
0077: }
0078: s
0079: .executeUpdate("create table t1 (c1 char(254), c2 char(40), vc1 varchar(264), vc2 varchar(4000), vc3 varchar(1), lvc1 long varchar, lvc2 long varchar)");
0080: PreparedStatement ps = conn
0081: .prepareStatement("insert into t1(c2, vc3) values(?, ?)");
0082: ps.setString(1, columnC2value);
0083: ps.setString(2, columnVC3value);
0084: ps.executeUpdate();
0085: ps = conn.prepareStatement("update t1 set lvc2 = ?");
0086: ps.setString(1, columnLVC2value);
0087: ps.executeUpdate();
0088:
0089: System.out
0090: .println("Test1a - CHAR concatenations will give result type of CHAR when concatenated string < 255");
0091: //operands CHAR(A) CHAR(B) and A+B<255 then result is CHAR(A+B)
0092: dumpSomeMetaDataInfo(
0093: s
0094: .executeQuery("values(select c2 || c2 || c2 || c2 || c2 || c2 || '12345678901234' from t1)"),
0095: concatenatedSQLTypesNames[0]);
0096: tempStringBuffer = new StringBuffer(columnC2value);
0097: tempStringBuffer.append(columnC2value)
0098: .append(columnC2value).append(columnC2value)
0099: .append(columnC2value).append(columnC2value);
0100: tempStringBuffer.append("12345678901234");
0101: columnC1value = tempStringBuffer.toString();
0102: verifyStringData(
0103: s
0104: .executeQuery("values(select c2 || c2 || c2 || c2 || c2 || c2 || '12345678901234' from t1)"),
0105: columnC1value);
0106: s
0107: .executeUpdate("update t1 set c1 = c2 || c2 || c2 || c2 || c2 || c2 || '12345678901234'");
0108: verifyStringData(s.executeQuery("select c1 from t1"),
0109: columnC1value);
0110:
0111: System.out
0112: .println("Test1b boundary test - CHAR concatenations will give result type of VARCHAR when concatenated string = 255");
0113: //operands CHAR(A) CHAR(B) and A+B>254 then result is VARCHAR(A+B)
0114: columnVC1value = columnC1value + "1";
0115: dumpSomeMetaDataInfo(s
0116: .executeQuery("values(select c1 || '1' from t1)"),
0117: concatenatedSQLTypesNames[1]);
0118: verifyStringData(s
0119: .executeQuery("values(select c1 || '1' from t1)"),
0120: columnVC1value);
0121: s.executeUpdate("update t1 set vc1 = c1 || '1'");
0122: verifyStringData(s.executeQuery("select vc1 from t1"),
0123: columnVC1value);
0124:
0125: System.out
0126: .println("Test1b - CHAR concatenations will give result type of VARCHAR when concatenated string > 254");
0127: //operands CHAR(A) CHAR(B) and A+B>254 then result is VARCHAR(A+B)
0128: columnVC1value = columnC1value + "1234567890";
0129: dumpSomeMetaDataInfo(
0130: s
0131: .executeQuery("values(select c1 || '1234567890' from t1)"),
0132: concatenatedSQLTypesNames[1]);
0133: verifyStringData(
0134: s
0135: .executeQuery("values(select c1 || '1234567890' from t1)"),
0136: columnVC1value);
0137: s.executeUpdate("update t1 set vc1 = c1 || '1234567890'");
0138: verifyStringData(s.executeQuery("select vc1 from t1"),
0139: columnVC1value);
0140:
0141: System.out
0142: .println("Test1c - CHAR and VARCHAR concatenations will give result type of VARCHAR when concatenated string < 4001");
0143: //operands CHAR(A) VARCHAR(B) and A+B<4001 then result is VARCHAR(A+B)
0144: //concatenated string 4000 characters long in following updates
0145: tempStringBuffer = new StringBuffer(columnC2value);
0146: tempStringBuffer.append(columnVC1value).append(
0147: columnVC1value).append(columnVC1value).append(
0148: columnVC1value).append(columnVC1value);
0149: tempStringBuffer.append(columnVC1value).append(
0150: columnVC1value).append(columnVC1value).append(
0151: columnVC1value).append(columnVC1value);
0152: tempStringBuffer.append(columnVC1value).append(
0153: columnVC1value).append(columnVC1value).append(
0154: columnVC1value).append(columnVC1value);
0155: columnVC2value = tempStringBuffer.toString();
0156: columnLVC1value = tempStringBuffer.toString();
0157: dumpSomeMetaDataInfo(
0158: s
0159: .executeQuery("values(select c2||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1 from t1)"),
0160: concatenatedSQLTypesNames[1]);
0161: verifyStringData(
0162: s
0163: .executeQuery("values(select c2||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1 from t1)"),
0164: columnVC2value);
0165: s
0166: .executeUpdate("update t1 set vc2 = c2||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1");
0167: verifyStringData(s.executeQuery("select vc2 from t1"),
0168: columnVC2value);
0169: s
0170: .executeUpdate("update t1 set lvc1 = c2||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1");
0171: verifyStringData(s.executeQuery("select lvc1 from t1"),
0172: columnLVC1value);
0173:
0174: System.out
0175: .println("Test1d - VARCHAR and CHAR concatenations will give result type of VARCHAR when concatenated string < 4001");
0176: //operands VARCHAR(A) CHAR(B) and A+B<4001 then result is VARCHAR(A+B)
0177: //concatenated string 4000 characters long in following updates
0178: tempStringBuffer = new StringBuffer();
0179: tempStringBuffer.append(columnVC1value).append(
0180: columnVC1value).append(columnVC1value).append(
0181: columnVC1value).append(columnVC1value);
0182: tempStringBuffer.append(columnVC1value).append(
0183: columnVC1value).append(columnVC1value).append(
0184: columnVC1value).append(columnVC1value);
0185: tempStringBuffer.append(columnVC1value).append(
0186: columnVC1value).append(columnVC1value).append(
0187: columnVC1value).append(columnVC1value);
0188: tempStringBuffer.append(columnC2value);
0189: columnVC2value = tempStringBuffer.toString();
0190: columnLVC1value = tempStringBuffer.toString();
0191: dumpSomeMetaDataInfo(
0192: s
0193: .executeQuery("values(select vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||c2 from t1)"),
0194: concatenatedSQLTypesNames[1]);
0195: verifyStringData(
0196: s
0197: .executeQuery("values(select vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||c2 from t1)"),
0198: columnVC2value);
0199: s
0200: .executeUpdate("update t1 set vc2 = vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||c2");
0201: verifyStringData(s.executeQuery("select vc2 from t1"),
0202: columnVC2value);
0203: s
0204: .executeUpdate("update t1 set lvc1 = vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||c2");
0205: verifyStringData(s.executeQuery("select lvc1 from t1"),
0206: columnLVC1value);
0207:
0208: System.out
0209: .println("Test1e boundary test - CHAR and VARCHAR concatenations will give result type of LONG VARCHAR when concatenated string = 4001");
0210: //operands CHAR(A) VARCHAR(B) and A+B>4000 then result is LONG VARCHAR
0211: //concatenated string is 4001 characters long in following 2 updates
0212: columnLVC1value = "a" + columnVC2value;
0213: dumpSomeMetaDataInfo(s
0214: .executeQuery("values(select 'a'||vc2 from t1)"),
0215: concatenatedSQLTypesNames[2]);
0216: verifyStringData(s
0217: .executeQuery("values(select 'a'||vc2 from t1)"),
0218: columnLVC1value);
0219: s.executeUpdate("update t1 set lvc1 = 'a'||vc2");
0220: verifyStringData(s.executeQuery("select lvc1 from t1"),
0221: columnLVC1value);
0222:
0223: System.out
0224: .println("Test1f boundary test - VARCHAR and CHAR concatenations will give result type of LONG VARCHAR when concatenated string = 4001");
0225: //operands VARCHAR(A) CHAR(B) and A+B>4000 then result is LONG VARCHAR
0226: columnLVC1value = columnVC2value + "a";
0227: dumpSomeMetaDataInfo(s
0228: .executeQuery("values(select vc2 || 'a' from t1)"),
0229: concatenatedSQLTypesNames[2]);
0230: verifyStringData(s
0231: .executeQuery("values(select vc2 || 'a' from t1)"),
0232: columnLVC1value);
0233: s.executeUpdate("update t1 set lvc1 = vc2 || 'a'");
0234: verifyStringData(s.executeQuery("select lvc1 from t1"),
0235: columnLVC1value);
0236:
0237: System.out
0238: .println("Test1g - CHAR and LONG VARCHAR concatenations will give result type of LONG VARCHAR");
0239: //operands CHAR(A) LONG VARCHAR then result is LONG VARCHAR
0240: columnLVC1value = "a" + columnLVC1value;
0241: dumpSomeMetaDataInfo(
0242: s
0243: .executeQuery("values(select 'a' || lvc1 from t1)"),
0244: concatenatedSQLTypesNames[2]);
0245: verifyStringData(
0246: s
0247: .executeQuery("values(select 'a' || lvc1 from t1)"),
0248: columnLVC1value);
0249: s.executeUpdate("update t1 set lvc1 = 'a' || lvc1");
0250: verifyStringData(s.executeQuery("select lvc1 from t1"),
0251: columnLVC1value);
0252:
0253: System.out
0254: .println("Test1h - VARCHAR and LONG VARCHAR concatenations will give result type of LONG VARCHAR");
0255: //operands VARCHAR(A) LONG VARCHAR then result is LONG VARCHAR
0256: columnLVC1value = columnVC1value + columnLVC1value;
0257: dumpSomeMetaDataInfo(
0258: s
0259: .executeQuery("values(select vc1 || lvc1 from t1)"),
0260: concatenatedSQLTypesNames[2]);
0261: verifyStringData(
0262: s
0263: .executeQuery("values(select vc1 || lvc1 from t1)"),
0264: columnLVC1value);
0265: s.executeUpdate("update t1 set lvc1 = vc1 || lvc1");
0266: verifyStringData(s.executeQuery("select lvc1 from t1"),
0267: columnLVC1value);
0268:
0269: System.out
0270: .println("Test1i - VARCHAR and VARCHAR concatenations will give result type of VARCHAR when concatenated string < 4001");
0271: //operands VARCHAR(A) VARCHAR(B) and A+B<4001 then result is VARCHAR(A+B)
0272: columnLVC1value = columnVC1value + columnVC1value;
0273: dumpSomeMetaDataInfo(s
0274: .executeQuery("values(select vc1 || vc1 from t1)"),
0275: concatenatedSQLTypesNames[1]);
0276: verifyStringData(s
0277: .executeQuery("values(select vc1 || vc1 from t1)"),
0278: columnLVC1value);
0279: s.executeUpdate("update t1 set lvc1 = vc1 || vc1");
0280: verifyStringData(s.executeQuery("select lvc1 from t1"),
0281: columnLVC1value);
0282:
0283: System.out
0284: .println("Test1j boundary test - VARCHAR and VARCHAR concatenations will give result type of LONG VARCHAR when concatenated string = 4001");
0285: //operands VARCHAR(A) VARCHAR(B) and A+B>4000 then result is LONG VARCHAR
0286: columnLVC1value = columnVC2value + columnVC3value;
0287: dumpSomeMetaDataInfo(s
0288: .executeQuery("values(select vc2 || vc3 from t1)"),
0289: concatenatedSQLTypesNames[2]);
0290: verifyStringData(s
0291: .executeQuery("values(select vc2 || vc3 from t1)"),
0292: columnLVC1value);
0293: s.executeUpdate("update t1 set lvc1 = vc2 || vc3");
0294: verifyStringData(s.executeQuery("select lvc1 from t1"),
0295: columnLVC1value);
0296:
0297: System.out
0298: .println("Test1j - VARCHAR and VARCHAR concatenations will give result type of LONG VARCHAR when concatenated string > 4000");
0299: //operands VARCHAR(A) VARCHAR(B) and A+B>4000 then result is LONG VARCHAR
0300: columnLVC1value = columnVC1value + columnVC2value;
0301: dumpSomeMetaDataInfo(s
0302: .executeQuery("values(select vc1 || vc2 from t1)"),
0303: concatenatedSQLTypesNames[2]);
0304: verifyStringData(s
0305: .executeQuery("values(select vc1 || vc2 from t1)"),
0306: columnLVC1value);
0307: s.executeUpdate("update t1 set lvc1 = vc1 || vc2");
0308: verifyStringData(s.executeQuery("select lvc1 from t1"),
0309: columnLVC1value);
0310:
0311: System.out
0312: .println("Test1k - LONG VARCHAR and LONG VARCHAR concatenations will give result type of LONG VARCHAR");
0313: //operands LONG VARCHAR, LONG VARCHAR then result is LONG VARCHAR
0314: columnLVC1value = columnLVC1value + columnLVC1value;
0315: dumpSomeMetaDataInfo(
0316: s
0317: .executeQuery("values(select lvc1 || lvc1 from t1)"),
0318: concatenatedSQLTypesNames[2]);
0319: verifyStringData(
0320: s
0321: .executeQuery("values(select lvc1 || lvc1 from t1)"),
0322: columnLVC1value);
0323: s.executeUpdate("update t1 set lvc1 = lvc1 || lvc1");
0324: verifyStringData(s.executeQuery("select lvc1 from t1"),
0325: columnLVC1value);
0326:
0327: //operands CHAR(A)/VARCHAR(A)/LONGVARCHAR, LONGVARCHAR and "concatenated string length">32700 does not cause automatic escalation
0328: //to LOB for compatibility with previous releases. Any such cases would result in an error at runtime
0329: System.out
0330: .println("Test1l - CHAR and LONGVARCHAR concatenation resulting in concatenated string > 32700 will give error");
0331: try {
0332: dumpSomeMetaDataInfo(
0333: s
0334: .executeQuery("values(select c2 || lvc2 from t1)"),
0335: concatenatedSQLTypesNames[2]);
0336: System.out
0337: .println("FAIL - should have gotten overflow error for values");
0338: } catch (SQLException e) {
0339: if (e.getSQLState().equals("54006"))
0340: System.out.println("expected exception "
0341: + e.getMessage());
0342: else
0343: dumpSQLExceptions(e);
0344: }
0345: try {
0346: s.executeUpdate("update t1 set lvc2 = c2 || lvc2");
0347: System.out
0348: .println("FAIL - should have gotten overflow error for insert");
0349: } catch (SQLException e) {
0350: if (e.getSQLState().equals("54006"))
0351: System.out.println("expected exception "
0352: + e.getMessage());
0353: else
0354: dumpSQLExceptions(e);
0355: }
0356:
0357: System.out
0358: .println("Test1m - VARCHAR and LONGVARCHAR concatenation resulting in concatenated string > 32700 will give error");
0359: try {
0360: dumpSomeMetaDataInfo(
0361: s
0362: .executeQuery("values(select vc1 || lvc2 from t1)"),
0363: concatenatedSQLTypesNames[2]);
0364: System.out
0365: .println("FAIL - should have gotten overflow error for values");
0366: } catch (SQLException e) {
0367: if (e.getSQLState().equals("54006"))
0368: System.out.println("expected exception "
0369: + e.getMessage());
0370: else
0371: dumpSQLExceptions(e);
0372: }
0373: try {
0374: s.executeUpdate("update t1 set lvc2 = vc1 || lvc2");
0375: System.out
0376: .println("FAIL - should have gotten overflow error for insert");
0377: } catch (SQLException e) {
0378: if (e.getSQLState().equals("54006"))
0379: System.out.println("expected exception "
0380: + e.getMessage());
0381: else
0382: dumpSQLExceptions(e);
0383: }
0384:
0385: System.out
0386: .println("Test1n - LONGVARCHAR and LONGVARCHAR concatenation resulting in concatenated string > 32700 will give error");
0387: try {
0388: dumpSomeMetaDataInfo(
0389: s
0390: .executeQuery("values(select lvc1 || lvc2 from t1)"),
0391: concatenatedSQLTypesNames[2]);
0392: System.out
0393: .println("FAIL - should have gotten overflow error for values");
0394: } catch (SQLException e) {
0395: if (e.getSQLState().equals("54006"))
0396: System.out.println("expected exception "
0397: + e.getMessage());
0398: else
0399: dumpSQLExceptions(e);
0400: }
0401: try {
0402: s.executeUpdate("update t1 set lvc2 = lvc1 || lvc2");
0403: System.out
0404: .println("FAIL - should have gotten overflow error for insert");
0405: } catch (SQLException e) {
0406: if (e.getSQLState().equals("54006"))
0407: System.out.println("expected exception "
0408: + e.getMessage());
0409: else
0410: dumpSQLExceptions(e);
0411: }
0412:
0413: try { //this is if we ever run the test against DB2, we want to make sure table doesn't already exist in DB2
0414: s.executeUpdate("drop table testCLOB_MAIN");
0415: } catch (Exception ex) {
0416: }
0417: s
0418: .executeUpdate("create table testCLOB_MAIN (c1 char(10), vc1 varchar(100), lvc1 long varchar, clob1 CLOB(2G), clob2 CLOB(256), clob3 CLOB(1M))");
0419: ps = conn
0420: .prepareStatement("insert into testCLOB_MAIN values(?,?,?,?,?,?)");
0421: columnC1value = "1234567890";
0422: ps.setString(1, columnC1value);
0423: columnVC1value = "this is varchar";
0424: ps.setString(2, columnVC1value);
0425: columnLVC1value = "this is long varchar";
0426: ps.setString(3, columnLVC1value);
0427: String columnCLOB1value = "this is 2G clob";
0428: ps.setString(4, columnCLOB1value);
0429: String columnCLOB2value = "this is 256 characters clob";
0430: ps.setString(5, columnCLOB2value);
0431: String columnCLOB3value = "this is 1M clob";
0432: ps.setString(6, columnCLOB3value);
0433: ps.executeUpdate();
0434:
0435: System.out
0436: .println("Test1o - CHAR(A) and CLOB(B) concatenations will give result type of CLOB(A+B) when A+B<2G");
0437: columnCLOB2value = columnC1value
0438: + "this is 256 characters clob";
0439: dumpSomeMetaDataInfo(
0440: s
0441: .executeQuery("values(select c1 || clob2 from testCLOB_MAIN)"),
0442: concatenatedSQLTypesNames[3]);
0443: verifyStringData(
0444: s
0445: .executeQuery("values(select c1 || clob2 from testCLOB_MAIN)"),
0446: columnCLOB2value);
0447: s
0448: .executeUpdate("update testCLOB_MAIN set clob2 = c1 || clob2");
0449: verifyStringData(s
0450: .executeQuery("select clob2 from testCLOB_MAIN"),
0451: columnCLOB2value);
0452:
0453: System.out
0454: .println("Test1p - CLOB(A) and CHAR(B) concatenations will give result type of CLOB(A+B) when A+B<2G");
0455: columnCLOB2value = columnCLOB2value + columnC1value;
0456: dumpSomeMetaDataInfo(
0457: s
0458: .executeQuery("values(select clob2 || c1 from testCLOB_MAIN)"),
0459: concatenatedSQLTypesNames[3]);
0460: verifyStringData(
0461: s
0462: .executeQuery("values(select clob2 || c1 from testCLOB_MAIN)"),
0463: columnCLOB2value);
0464: s
0465: .executeUpdate("update testCLOB_MAIN set clob2 = clob2 || c1");
0466: verifyStringData(s
0467: .executeQuery("select clob2 from testCLOB_MAIN"),
0468: columnCLOB2value);
0469:
0470: System.out
0471: .println("Test1q - CHAR(A) and CLOB(B) concatenations will give result type of CLOB(2G) when A+B>2G");
0472: columnCLOB1value = columnC1value + columnCLOB1value;
0473: dumpSomeMetaDataInfo(
0474: s
0475: .executeQuery("values(select c1 || clob1 from testCLOB_MAIN)"),
0476: concatenatedSQLTypesNames[3]);
0477: verifyStringData(
0478: s
0479: .executeQuery("values(select c1 || clob1 from testCLOB_MAIN)"),
0480: columnCLOB1value);
0481: s
0482: .executeUpdate("update testCLOB_MAIN set clob1 = c1 || clob1");
0483: verifyStringData(s
0484: .executeQuery("select clob1 from testCLOB_MAIN"),
0485: columnCLOB1value);
0486:
0487: System.out
0488: .println("Test1r - CLOB(A) and CHAR(B) concatenations will give result type of CLOB(2G) when A+B>2G");
0489: columnCLOB1value = columnCLOB1value + columnC1value;
0490: dumpSomeMetaDataInfo(
0491: s
0492: .executeQuery("values(select clob1 || c1 from testCLOB_MAIN)"),
0493: concatenatedSQLTypesNames[3]);
0494: verifyStringData(
0495: s
0496: .executeQuery("values(select clob1 || c1 from testCLOB_MAIN)"),
0497: columnCLOB1value);
0498: s
0499: .executeUpdate("update testCLOB_MAIN set clob1 = clob1 || c1");
0500: verifyStringData(s
0501: .executeQuery("select clob1 from testCLOB_MAIN"),
0502: columnCLOB1value);
0503:
0504: System.out
0505: .println("Test1s - VARCHAR(A) and CLOB(B) concatenations will give result type of CLOB(A+B) when A+B<2G");
0506: columnCLOB2value = columnVC1value + columnCLOB2value;
0507: dumpSomeMetaDataInfo(
0508: s
0509: .executeQuery("values(select vc1 || clob2 from testCLOB_MAIN)"),
0510: concatenatedSQLTypesNames[3]);
0511: verifyStringData(
0512: s
0513: .executeQuery("values(select vc1 || clob2 from testCLOB_MAIN)"),
0514: columnCLOB2value);
0515: s
0516: .executeUpdate("update testCLOB_MAIN set clob2 = vc1 || clob2");
0517: verifyStringData(s
0518: .executeQuery("select clob2 from testCLOB_MAIN"),
0519: columnCLOB2value);
0520:
0521: System.out
0522: .println("Test1t - CLOB(A) and VARCHAR(B) concatenations will give result type of CLOB(A+B) when A+B<2G");
0523: columnCLOB2value = columnCLOB2value + columnVC1value;
0524: dumpSomeMetaDataInfo(
0525: s
0526: .executeQuery("values(select clob2 || vc1 from testCLOB_MAIN)"),
0527: concatenatedSQLTypesNames[3]);
0528: verifyStringData(
0529: s
0530: .executeQuery("values(select clob2 || vc1 from testCLOB_MAIN)"),
0531: columnCLOB2value);
0532: s
0533: .executeUpdate("update testCLOB_MAIN set clob2 = clob2 || vc1");
0534: verifyStringData(s
0535: .executeQuery("select clob2 from testCLOB_MAIN"),
0536: columnCLOB2value);
0537:
0538: System.out
0539: .println("Test1u - VARCHAR(A) and CLOB(B) concatenations will give result type of CLOB(2G) when A+B>2G");
0540: columnCLOB1value = columnVC1value + columnCLOB1value;
0541: dumpSomeMetaDataInfo(
0542: s
0543: .executeQuery("values(select vc1 || clob1 from testCLOB_MAIN)"),
0544: concatenatedSQLTypesNames[3]);
0545: verifyStringData(
0546: s
0547: .executeQuery("values(select vc1 || clob1 from testCLOB_MAIN)"),
0548: columnCLOB1value);
0549: s
0550: .executeUpdate("update testCLOB_MAIN set clob1 = vc1 || clob1");
0551: verifyStringData(s
0552: .executeQuery("select clob1 from testCLOB_MAIN"),
0553: columnCLOB1value);
0554:
0555: System.out
0556: .println("Test1v - CLOB(A) and VARCHAR(B) concatenations will give result type of CLOB(2G) when A+B>2G");
0557: columnCLOB1value = columnCLOB1value + columnVC1value;
0558: dumpSomeMetaDataInfo(
0559: s
0560: .executeQuery("values(select clob1 || vc1 from testCLOB_MAIN)"),
0561: concatenatedSQLTypesNames[3]);
0562: verifyStringData(
0563: s
0564: .executeQuery("values(select clob1 || vc1 from testCLOB_MAIN)"),
0565: columnCLOB1value);
0566: s
0567: .executeUpdate("update testCLOB_MAIN set clob1 = clob1 || vc1");
0568: verifyStringData(s
0569: .executeQuery("select clob1 from testCLOB_MAIN"),
0570: columnCLOB1value);
0571:
0572: System.out
0573: .println("Test1w - LONG VARCHAR and CLOB(A) concatenations will give result type of CLOB(A+32K) when A+32K<2G");
0574: columnCLOB2value = columnLVC1value + columnCLOB2value;
0575: dumpSomeMetaDataInfo(
0576: s
0577: .executeQuery("values(select lvc1 || clob2 from testCLOB_MAIN)"),
0578: concatenatedSQLTypesNames[3]);
0579: verifyStringData(
0580: s
0581: .executeQuery("values(select lvc1 || clob2 from testCLOB_MAIN)"),
0582: columnCLOB2value);
0583: s
0584: .executeUpdate("update testCLOB_MAIN set clob2 = lvc1 || clob2");
0585: verifyStringData(s
0586: .executeQuery("select clob2 from testCLOB_MAIN"),
0587: columnCLOB2value);
0588:
0589: System.out
0590: .println("Test1x - CLOB(A) and LONG VARCHAR concatenations will give result type of CLOB(A+32K) when A+32K<2G");
0591: columnCLOB2value = columnCLOB2value + columnLVC1value;
0592: dumpSomeMetaDataInfo(
0593: s
0594: .executeQuery("values(select clob2 || lvc1 from testCLOB_MAIN)"),
0595: concatenatedSQLTypesNames[3]);
0596: verifyStringData(
0597: s
0598: .executeQuery("values(select clob2 || lvc1 from testCLOB_MAIN)"),
0599: columnCLOB2value);
0600: s
0601: .executeUpdate("update testCLOB_MAIN set clob2 = clob2 || lvc1");
0602: verifyStringData(s
0603: .executeQuery("select clob2 from testCLOB_MAIN"),
0604: columnCLOB2value);
0605:
0606: System.out
0607: .println("Test1y - LONG VARCHAR and CLOB(B) concatenations will give result type of CLOB(2G) when A+32K>2G");
0608: columnCLOB1value = columnLVC1value + columnCLOB1value;
0609: dumpSomeMetaDataInfo(
0610: s
0611: .executeQuery("values(select lvc1 || clob1 from testCLOB_MAIN)"),
0612: concatenatedSQLTypesNames[3]);
0613: verifyStringData(
0614: s
0615: .executeQuery("values(select lvc1 || clob1 from testCLOB_MAIN)"),
0616: columnCLOB1value);
0617: s
0618: .executeUpdate("update testCLOB_MAIN set clob1 = lvc1 || clob1");
0619: verifyStringData(s
0620: .executeQuery("select clob1 from testCLOB_MAIN"),
0621: columnCLOB1value);
0622:
0623: System.out
0624: .println("Test1z - CLOB(A) and LONG VARCHAR concatenations will give result type of CLOB(2G) when A+32K>2G");
0625: columnCLOB1value = columnCLOB1value + columnLVC1value;
0626: dumpSomeMetaDataInfo(
0627: s
0628: .executeQuery("values(select clob1 || lvc1 from testCLOB_MAIN)"),
0629: concatenatedSQLTypesNames[3]);
0630: verifyStringData(
0631: s
0632: .executeQuery("values(select clob1 || lvc1 from testCLOB_MAIN)"),
0633: columnCLOB1value);
0634: s
0635: .executeUpdate("update testCLOB_MAIN set clob1 = clob1 || lvc1");
0636: verifyStringData(s
0637: .executeQuery("select clob1 from testCLOB_MAIN"),
0638: columnCLOB1value);
0639:
0640: System.out
0641: .println("Test11a - CLOB(A) and CLOB(B) concatenations will give result type of CLOB(A+B) when A+B<2G");
0642: columnCLOB2value = columnCLOB2value + columnCLOB3value;
0643: dumpSomeMetaDataInfo(
0644: s
0645: .executeQuery("values(select clob2 || clob3 from testCLOB_MAIN)"),
0646: concatenatedSQLTypesNames[3]);
0647: verifyStringData(
0648: s
0649: .executeQuery("values(select clob2 || clob3 from testCLOB_MAIN)"),
0650: columnCLOB2value);
0651: s
0652: .executeUpdate("update testCLOB_MAIN set clob2 = clob2 || clob3");
0653: verifyStringData(s
0654: .executeQuery("select clob2 from testCLOB_MAIN"),
0655: columnCLOB2value);
0656:
0657: System.out
0658: .println("Test11b - CLOB(A) and CLOB(B) concatenations will give result type of CLOB(2G) when A+B>2G");
0659: columnCLOB1value = columnCLOB2value + columnCLOB1value;
0660: dumpSomeMetaDataInfo(
0661: s
0662: .executeQuery("values(select clob2 || clob1 from testCLOB_MAIN)"),
0663: concatenatedSQLTypesNames[3]);
0664: verifyStringData(
0665: s
0666: .executeQuery("values(select clob2 || clob1 from testCLOB_MAIN)"),
0667: columnCLOB1value);
0668: s
0669: .executeUpdate("update testCLOB_MAIN set clob1 = clob2 || clob1");
0670: verifyStringData(s
0671: .executeQuery("select clob1 from testCLOB_MAIN"),
0672: columnCLOB1value);
0673:
0674: System.out
0675: .println("Test12 - try 2 empty string concatenation and verify that length comes back as 0 for the result");
0676: dumpSomeMetaDataInfo(s.executeQuery("values('' || '')"),
0677: concatenatedSQLTypesNames[0]);
0678: verifyStringData(s.executeQuery("values('' || '')"), "");
0679:
0680: System.out
0681: .println("Test13 - Prepared statement with CLOB(A) and ? concatenations will give result type of CLOB(A+length of ?)");
0682: System.out
0683: .println(" Prior to Derby-124 fix, ? parameter was getting bound to VARCHAR of length 32672 rather than CLOB.");
0684: try {
0685: s.executeUpdate("drop table ct");
0686: } catch (Exception ex) {
0687: }
0688: s.executeUpdate("create table ct (c CLOB(100K))");
0689: String cData = Formatters.repeatChar("c", 32700);
0690: String cData1 = "aa";
0691: String cConcatenatedData = cData1 + cData;
0692: //Prior to fix for Derby-124, the ? was getting bound to VARCHAR with max length of 32670
0693: //As a fix for this, if one of the operands of concatenation is CLOB, then the ? parameter would be bound to CLOB as well
0694: System.out.println(" preapre statement with clob||?");
0695: ps = conn
0696: .prepareStatement("insert into ct values (cast ('aa' as CLOB) || ?)");
0697: ps.setString(1, cData);
0698: ps.execute();
0699: verifyStringData(s.executeQuery("select c from ct"),
0700: cConcatenatedData);
0701: s.executeUpdate("delete from ct");
0702: System.out
0703: .println(" Test - preapre statement with clob||cast(? to cLOB)");
0704: ps = conn
0705: .prepareStatement("insert into ct values (cast ('aa' as CLOB) || cast(? as CLOB))");
0706: ps.setString(1, cData);
0707: ps.execute();
0708: verifyStringData(s.executeQuery("select c from ct"),
0709: cConcatenatedData);
0710: s.executeUpdate("delete from ct");
0711:
0712: s.executeUpdate("drop table testCLOB_MAIN");
0713: s.executeUpdate("drop table t1");
0714: s.executeUpdate("drop table ct");
0715: System.out
0716: .println("Test1 finished - CHAR, VARCHAR, LONGVARCHAR and CLOB concatenation tests");
0717: } catch (SQLException sqle) {
0718: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
0719: System.out, sqle);
0720: sqle.printStackTrace(System.out);
0721: }
0722: }
0723:
0724: public static void testCharForBitDataConcatenation(Connection conn)
0725: throws Throwable {
0726: try {
0727: System.out
0728: .println("Test2 - CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONGVARCHAR FOR BIT DATA and BLOB concatenation tests");
0729: Statement s = conn.createStatement();
0730: byte[] columnCBD0value = { (byte) 'a' };
0731: byte[] columnCBD1value;
0732: byte[] columnCBD2value = new byte[40];
0733: java.util.Arrays.fill(columnCBD2value, (byte) 'a');
0734: byte[] columnCBD3value = new byte[14];
0735: java.util.Arrays.fill(columnCBD3value, (byte) 'a');
0736: byte[] columnVCBD1value;
0737: byte[] columnVCBD2value;
0738: byte[] columnVCBD3value = { (byte) 'a' };
0739: byte[] columnLVCBD1value;
0740: byte[] columnLVCBD2value = new byte[32698];
0741: java.util.Arrays.fill(columnLVCBD2value, (byte) 'a');
0742: byte[] tempStringBuffer;
0743:
0744: try { //this is if we ever run the test against DB2, we want to make sure table doesn't already exist in DB2
0745: s.executeUpdate("drop table t2");
0746: } catch (Exception ex) {
0747: }
0748: s
0749: .executeUpdate("create table t2 (cbd0 CHAR(1) FOR BIT DATA, cbd1 CHAR(254) FOR BIT DATA, cbd2 CHAR(40) FOR BIT DATA, cbd3 CHAR(14) FOR BIT DATA, vcbd1 VARCHAR(264) FOR BIT DATA, vcbd2 VARCHAR(4000) FOR BIT DATA, vcbd3 VARCHAR(1) FOR BIT DATA, lvcbd1 LONG VARCHAR FOR BIT DATA, lvcbd2 LONG VARCHAR FOR BIT DATA)");
0750: PreparedStatement ps = conn
0751: .prepareStatement("insert into t2(cbd0, cbd2, cbd3, vcbd3) values (?, ?,?,?)");
0752: ps.setBytes(1, columnCBD0value);
0753: ps.setBytes(2, columnCBD2value);
0754: ps.setBytes(3, columnCBD3value);
0755: ps.setBytes(4, columnVCBD3value);
0756: ps.executeUpdate();
0757: ps = conn.prepareStatement("update t2 set lvcbd2 = ?");
0758: ps.setBytes(1, columnLVCBD2value);
0759: ps.executeUpdate();
0760:
0761: System.out
0762: .println("Test2a - CHAR FOR BIT DATA concatenations will give result type of CHAR FOR BIT DATA when concatenated string < 255");
0763: //operands CHAR(A) FOR BIT DATA, CHAR(B) FOR BIT DATA and A+B<255 then result is CHAR(A+B) FOR BIT DATA
0764: columnCBD1value = new byte[254];
0765: System.arraycopy(columnCBD2value, 0, columnCBD1value, 0,
0766: columnCBD2value.length);
0767: System.arraycopy(columnCBD2value, 0, columnCBD1value, 40,
0768: columnCBD2value.length);
0769: System.arraycopy(columnCBD2value, 0, columnCBD1value, 80,
0770: columnCBD2value.length);
0771: System.arraycopy(columnCBD2value, 0, columnCBD1value, 120,
0772: columnCBD2value.length);
0773: System.arraycopy(columnCBD2value, 0, columnCBD1value, 160,
0774: columnCBD2value.length);
0775: System.arraycopy(columnCBD2value, 0, columnCBD1value, 200,
0776: columnCBD2value.length);
0777: System.arraycopy(columnCBD3value, 0, columnCBD1value, 240,
0778: columnCBD3value.length);
0779: dumpSomeMetaDataInfo(
0780: s
0781: .executeQuery("values(select cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd3 from t2)"),
0782: concatenatedSQLTypesNames[4]);
0783: verifyByteData(
0784: s
0785: .executeQuery("values(select cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd3 from t2)"),
0786: columnCBD1value);
0787: s
0788: .executeUpdate("update t2 set cbd1 = cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd3");
0789: verifyByteData(s.executeQuery("select cbd1 from t2"),
0790: columnCBD1value);
0791:
0792: System.out
0793: .println("Test2b boundary test - CHAR FOR BIT DATA concatenations will give result type of VARCHAR FOR BIT DATA when concatenated string = 255");
0794: //operands CHAR(A) FOR BIT DATA, CHAR(B) FOR BIT DATA and A+B>254 then result is VARCHAR(A+B) FOR BIT DATA
0795: columnVCBD1value = new byte[255];
0796: System.arraycopy(columnCBD1value, 0, columnVCBD1value, 0,
0797: columnCBD1value.length);
0798: System.arraycopy(columnCBD0value, 0, columnVCBD1value, 254,
0799: columnCBD0value.length);
0800: dumpSomeMetaDataInfo(
0801: s
0802: .executeQuery("values(select cbd1 || cbd0 from t2)"),
0803: concatenatedSQLTypesNames[5]);
0804: verifyByteData(
0805: s
0806: .executeQuery("values(select cbd1 || cbd0 from t2)"),
0807: columnVCBD1value);
0808: s.executeUpdate("update t2 set vcbd1 = cbd1 || cbd0");
0809: verifyByteData(s.executeQuery("select vcbd1 from t2"),
0810: columnVCBD1value);
0811:
0812: System.out
0813: .println("Test2b - CHAR FOR BIT DATA concatenations will give result type of VARCHAR FOR BIT DATA when concatenated string > 254");
0814: //operands CHAR(A) FOR BIT DATA, CHAR(B) FOR BIT DATA and A+B>254 then result is VARCHAR(A+B) FOR BIT DATA
0815: columnVCBD1value = new byte[264];
0816: System.arraycopy(columnCBD1value, 0, columnVCBD1value, 0,
0817: columnCBD1value.length);
0818: System.arraycopy(columnCBD0value, 0, columnVCBD1value, 254,
0819: columnCBD0value.length);
0820: System.arraycopy(columnCBD0value, 0, columnVCBD1value, 255,
0821: columnCBD0value.length);
0822: System.arraycopy(columnCBD0value, 0, columnVCBD1value, 256,
0823: columnCBD0value.length);
0824: System.arraycopy(columnCBD0value, 0, columnVCBD1value, 257,
0825: columnCBD0value.length);
0826: System.arraycopy(columnCBD0value, 0, columnVCBD1value, 258,
0827: columnCBD0value.length);
0828: System.arraycopy(columnCBD0value, 0, columnVCBD1value, 259,
0829: columnCBD0value.length);
0830: System.arraycopy(columnCBD0value, 0, columnVCBD1value, 260,
0831: columnCBD0value.length);
0832: System.arraycopy(columnCBD0value, 0, columnVCBD1value, 261,
0833: columnCBD0value.length);
0834: System.arraycopy(columnCBD0value, 0, columnVCBD1value, 262,
0835: columnCBD0value.length);
0836: System.arraycopy(columnCBD0value, 0, columnVCBD1value, 263,
0837: columnCBD0value.length);
0838: dumpSomeMetaDataInfo(
0839: s
0840: .executeQuery("values(select cbd1 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 from t2)"),
0841: concatenatedSQLTypesNames[5]);
0842: verifyByteData(
0843: s
0844: .executeQuery("values(select cbd1 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 from t2)"),
0845: columnVCBD1value);
0846: s
0847: .executeUpdate("update t2 set vcbd1 = cbd1 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0");
0848: verifyByteData(s.executeQuery("select vcbd1 from t2"),
0849: columnVCBD1value);
0850:
0851: System.out
0852: .println("Test2c - CHAR FOR BIT DATA and VARCHAR FOR BIT DATA concatenations will give result type of VARCHAR FOR BIT DATA when concatenated string < 4001");
0853: //operands CHAR(A) FOR BIT DATA, VARCHAR(B) FOR BIT DATA and A+B<4001 then result is VARCHAR(A+B) FOR BIT DATA
0854: //concatenated string 4000 characters long in following updates
0855: columnLVCBD1value = new byte[4000];
0856: columnVCBD2value = new byte[4000];
0857: System.arraycopy(columnCBD2value, 0, columnLVCBD1value, 0,
0858: columnCBD2value.length);
0859: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0860: 40, columnVCBD1value.length);
0861: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0862: 304, columnVCBD1value.length);
0863: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0864: 568, columnVCBD1value.length);
0865: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0866: 832, columnVCBD1value.length);
0867: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0868: 1096, columnVCBD1value.length);
0869: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0870: 1360, columnVCBD1value.length);
0871: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0872: 1624, columnVCBD1value.length);
0873: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0874: 1888, columnVCBD1value.length);
0875: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0876: 2152, columnVCBD1value.length);
0877: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0878: 2416, columnVCBD1value.length);
0879: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0880: 2680, columnVCBD1value.length);
0881: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0882: 2944, columnVCBD1value.length);
0883: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0884: 3208, columnVCBD1value.length);
0885: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0886: 3472, columnVCBD1value.length);
0887: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0888: 3736, columnVCBD1value.length);
0889: System.arraycopy(columnLVCBD1value, 0, columnVCBD2value, 0,
0890: columnLVCBD1value.length);
0891: dumpSomeMetaDataInfo(
0892: s
0893: .executeQuery("values(select cbd2||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1 from t2)"),
0894: concatenatedSQLTypesNames[5]);
0895: verifyByteData(
0896: s
0897: .executeQuery("values(select cbd2||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1 from t2)"),
0898: columnVCBD2value);
0899: s
0900: .executeUpdate("update t2 set vcbd2 = cbd2||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1");
0901: verifyByteData(s.executeQuery("select vcbd2 from t2"),
0902: columnVCBD2value);
0903: s
0904: .executeUpdate("update t2 set lvcbd1 = cbd2||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1");
0905: verifyByteData(s.executeQuery("select lvcbd1 from t2"),
0906: columnLVCBD1value);
0907:
0908: System.out
0909: .println("Test2d - VARCHAR FOR BIT DATA and CHAR FOR BIT DATA concatenations will give result type of VARCHAR FOR BIT DATA when concatenated string < 4001");
0910: //operands VARCHAR(A) FOR BIT DATA, CHAR(B) FOR BIT DATA and A+B<4001 then result is VARCHAR(A+B) FOR BIT DATA
0911: //concatenated string 4000 characters long in following updates
0912: columnLVCBD1value = new byte[4000];
0913: columnVCBD2value = new byte[4000];
0914: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 0,
0915: columnVCBD1value.length);
0916: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0917: 264, columnVCBD1value.length);
0918: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0919: 528, columnVCBD1value.length);
0920: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0921: 792, columnVCBD1value.length);
0922: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0923: 1056, columnVCBD1value.length);
0924: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0925: 1320, columnVCBD1value.length);
0926: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0927: 1584, columnVCBD1value.length);
0928: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0929: 1848, columnVCBD1value.length);
0930: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0931: 2112, columnVCBD1value.length);
0932: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0933: 2376, columnVCBD1value.length);
0934: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0935: 2640, columnVCBD1value.length);
0936: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0937: 2904, columnVCBD1value.length);
0938: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0939: 3168, columnVCBD1value.length);
0940: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0941: 3432, columnVCBD1value.length);
0942: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
0943: 3696, columnVCBD1value.length);
0944: System.arraycopy(columnCBD2value, 0, columnLVCBD1value,
0945: 3960, columnCBD2value.length);
0946: System.arraycopy(columnLVCBD1value, 0, columnVCBD2value, 0,
0947: columnLVCBD1value.length);
0948: dumpSomeMetaDataInfo(
0949: s
0950: .executeQuery("values(select vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||cbd2 from t2)"),
0951: concatenatedSQLTypesNames[5]);
0952: verifyByteData(
0953: s
0954: .executeQuery("values(select vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||cbd2 from t2)"),
0955: columnLVCBD1value);
0956: s
0957: .executeUpdate("update t2 set vcbd2 = vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||cbd2");
0958: verifyByteData(s.executeQuery("select vcbd2 from t2"),
0959: columnVCBD2value);
0960: s
0961: .executeUpdate("update t2 set lvcbd1 = vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||cbd2");
0962: verifyByteData(s.executeQuery("select lvcbd1 from t2"),
0963: columnLVCBD1value);
0964:
0965: System.out
0966: .println("Test2e boundary test - CHAR FOR BIT DATA and VARCHAR FOR BIT DATA concatenations will give result type of LONG VARCHAR FOR BIT DATA when concatenated string > 4000");
0967: //operands CHAR(A) FOR BIT DATA, VARCHAR(B) FOR BIT DATA and A+B>4000 then result is LONG VARCHAR FOR BIT DATA
0968: //concatenated string is > 4000 characters long in following 2 updates
0969: columnLVCBD1value = new byte[4001];
0970: System.arraycopy(columnCBD0value, 0, columnLVCBD1value, 0,
0971: columnCBD0value.length);
0972: System.arraycopy(columnVCBD2value, 0, columnLVCBD1value, 1,
0973: columnVCBD2value.length);
0974: dumpSomeMetaDataInfo(
0975: s
0976: .executeQuery("values(select cbd0||vcbd2 from t2)"),
0977: concatenatedSQLTypesNames[6]);
0978: verifyByteData(
0979: s
0980: .executeQuery("values(select cbd0||vcbd2 from t2)"),
0981: columnLVCBD1value);
0982: s.executeUpdate("update t2 set lvcbd1 = cbd0||vcbd2 ");
0983: verifyByteData(s.executeQuery("select lvcbd1 from t2"),
0984: columnLVCBD1value);
0985:
0986: System.out
0987: .println("Test2f boundary test - VARCHAR FOR BIT DATA and CHAR FOR BIT DATA concatenations will give result type of LONG VARCHAR FOR BIT DATA when concatenated string > 4000");
0988: //operands VARCHAR(A) FOR BIT DATA, CHAR(B) FOR BIT DATA and A+B>4000 then result is LONG VARCHAR FOR BIT DATA
0989: columnLVCBD1value = new byte[4001];
0990: System.arraycopy(columnVCBD2value, 0, columnLVCBD1value, 0,
0991: columnVCBD2value.length);
0992: System.arraycopy(columnCBD0value, 0, columnLVCBD1value,
0993: 4000, columnCBD0value.length);
0994: dumpSomeMetaDataInfo(
0995: s
0996: .executeQuery("values(select vcbd2 || cbd0 from t2)"),
0997: concatenatedSQLTypesNames[6]);
0998: verifyByteData(
0999: s
1000: .executeQuery("values(select vcbd2 || cbd0 from t2)"),
1001: columnLVCBD1value);
1002: s.executeUpdate("update t2 set lvcbd1 = vcbd2 || cbd0");
1003: verifyByteData(s.executeQuery("select lvcbd1 from t2"),
1004: columnLVCBD1value);
1005:
1006: System.out
1007: .println("Test2g - CHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA concatenations will give result type of LONG VARCHAR FOR BIT DATA");
1008: //operands CHAR(A) FOR BIT DATA, LONG VARCHAR FOR BIT DATA then result is LONG VARCHAR FOR BIT DATA
1009: byte[] tmpColumnLVCBD1value = new byte[4001];
1010: System.arraycopy(columnLVCBD1value, 0,
1011: tmpColumnLVCBD1value, 0, columnLVCBD1value.length);
1012: columnLVCBD1value = new byte[4002];
1013: System.arraycopy(columnCBD0value, 0, columnLVCBD1value, 0,
1014: columnCBD0value.length);
1015: System.arraycopy(tmpColumnLVCBD1value, 0,
1016: columnLVCBD1value, 1, tmpColumnLVCBD1value.length);
1017: dumpSomeMetaDataInfo(
1018: s
1019: .executeQuery("values(select cbd0 || lvcbd1 from t2)"),
1020: concatenatedSQLTypesNames[6]);
1021: verifyByteData(
1022: s
1023: .executeQuery("values(select cbd0 || lvcbd1 from t2)"),
1024: columnLVCBD1value);
1025: s.executeUpdate("update t2 set lvcbd1 = cbd0 || lvcbd1");
1026: verifyByteData(s.executeQuery("select lvcbd1 from t2"),
1027: columnLVCBD1value);
1028:
1029: System.out
1030: .println("Test2h - VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA concatenations will give result type of LONG VARCHAR FOR BIT DATA");
1031: //operands VARCHAR(A) FOR BIT DATA, LONG VARCHAR FOR BIT DATA then result is LONG VARCHAR FOR BIT DATA
1032: tmpColumnLVCBD1value = new byte[4002];
1033: System.arraycopy(columnLVCBD1value, 0,
1034: tmpColumnLVCBD1value, 0, columnLVCBD1value.length);
1035: columnLVCBD1value = new byte[4266];
1036: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 0,
1037: columnVCBD1value.length);
1038: System
1039: .arraycopy(tmpColumnLVCBD1value, 0,
1040: columnLVCBD1value, 264,
1041: tmpColumnLVCBD1value.length);
1042: dumpSomeMetaDataInfo(
1043: s
1044: .executeQuery("values(select vcbd1 || lvcbd1 from t2)"),
1045: concatenatedSQLTypesNames[6]);
1046: verifyByteData(
1047: s
1048: .executeQuery("values(select vcbd1 || lvcbd1 from t2)"),
1049: columnLVCBD1value);
1050: s.executeUpdate("update t2 set lvcbd1 = vcbd1 || lvcbd1");
1051: verifyByteData(s.executeQuery("select lvcbd1 from t2"),
1052: columnLVCBD1value);
1053:
1054: System.out
1055: .println("Test2i - VARCHAR FOR BIT DATA and VARCHAR FOR BIT DATA concatenations will give result type of VARCHAR FOR BIT DATA when concatenated string < 4001");
1056: //operands VARCHAR(A) FOR BIT DATA, VARCHAR(B) FOR BIT DATA and A+B<4001 then result is VARCHAR(A+B) FOR BIT DATA
1057: columnLVCBD1value = new byte[528];
1058: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 0,
1059: columnVCBD1value.length);
1060: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value,
1061: 264, columnVCBD1value.length);
1062: dumpSomeMetaDataInfo(
1063: s
1064: .executeQuery("values(select vcbd1 || vcbd1 from t2)"),
1065: concatenatedSQLTypesNames[5]);
1066: verifyByteData(
1067: s
1068: .executeQuery("values(select vcbd1 || vcbd1 from t2)"),
1069: columnLVCBD1value);
1070: s.executeUpdate("update t2 set lvcbd1 = vcbd1 || vcbd1");
1071: verifyByteData(s.executeQuery("select lvcbd1 from t2"),
1072: columnLVCBD1value);
1073:
1074: System.out
1075: .println("Test2j boundary test - VARCHAR FOR BIT DATA and VARCHAR FOR BIT DATA concatenations will give result type of LONG VARCHAR FOR BIT DATA when concatenated string = 4001");
1076: //operands VARCHAR(A) FOR BIT DATA, VARCHAR(B) FOR BIT DATA and A+B>4000 then result is LONG VARCHAR FOR BIT DATA
1077: columnLVCBD1value = new byte[4001];
1078: System.arraycopy(columnVCBD2value, 0, columnLVCBD1value, 0,
1079: columnVCBD2value.length);
1080: System.arraycopy(columnVCBD3value, 0, columnLVCBD1value,
1081: 4000, columnVCBD3value.length);
1082: dumpSomeMetaDataInfo(
1083: s
1084: .executeQuery("values(select vcbd2 || vcbd3 from t2)"),
1085: concatenatedSQLTypesNames[6]);
1086: verifyByteData(
1087: s
1088: .executeQuery("values(select vcbd2 || vcbd3 from t2)"),
1089: columnLVCBD1value);
1090: s.executeUpdate("update t2 set lvcbd1 = vcbd2 || vcbd3");
1091: verifyByteData(s.executeQuery("select lvcbd1 from t2"),
1092: columnLVCBD1value);
1093:
1094: System.out
1095: .println("Test2j - VARCHAR FOR BIT DATA and VARCHAR FOR BIT DATA concatenations will give result type of LONG VARCHAR FOR BIT DATA when concatenated string > 4000");
1096: //operands VARCHAR(A) FOR BIT DATA, VARCHAR(B) FOR BIT DATA and A+B>4000 then result is LONG VARCHAR FOR BIT DATA
1097: columnLVCBD1value = new byte[columnVCBD1value.length
1098: + columnVCBD2value.length];
1099: System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 0,
1100: columnVCBD1value.length);
1101: System.arraycopy(columnVCBD2value, 0, columnLVCBD1value,
1102: columnVCBD1value.length, columnVCBD2value.length);
1103: dumpSomeMetaDataInfo(
1104: s
1105: .executeQuery("values(select vcbd1 || vcbd2 from t2)"),
1106: concatenatedSQLTypesNames[6]);
1107: verifyByteData(
1108: s
1109: .executeQuery("values(select vcbd1 || vcbd2 from t2)"),
1110: columnLVCBD1value);
1111: s.executeUpdate("update t2 set lvcbd1 = vcbd1 || vcbd2");
1112: verifyByteData(s.executeQuery("select lvcbd1 from t2"),
1113: columnLVCBD1value);
1114:
1115: System.out
1116: .println("Test2k - LONG VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA concatenations will give result type of LONG VARCHAR FOR BIT DATA");
1117: //operands LONG VARCHAR FOR BIT DATA, LONG VARCHAR FOR BIT DATA then result is LONG VARCHAR FOR BIT DATA
1118: tmpColumnLVCBD1value = new byte[columnLVCBD1value.length];
1119: System.arraycopy(columnLVCBD1value, 0,
1120: tmpColumnLVCBD1value, 0, columnLVCBD1value.length);
1121: columnLVCBD1value = new byte[tmpColumnLVCBD1value.length
1122: + tmpColumnLVCBD1value.length];
1123: System.arraycopy(tmpColumnLVCBD1value, 0,
1124: columnLVCBD1value, 0, tmpColumnLVCBD1value.length);
1125: System.arraycopy(tmpColumnLVCBD1value, 0,
1126: columnLVCBD1value, tmpColumnLVCBD1value.length,
1127: tmpColumnLVCBD1value.length);
1128: dumpSomeMetaDataInfo(
1129: s
1130: .executeQuery("values(select lvcbd1 || lvcbd1 from t2)"),
1131: concatenatedSQLTypesNames[6]);
1132: verifyByteData(
1133: s
1134: .executeQuery("values(select lvcbd1 || lvcbd1 from t2)"),
1135: columnLVCBD1value);
1136: s.executeUpdate("update t2 set lvcbd1 = lvcbd1 || lvcbd1");
1137: verifyByteData(s.executeQuery("select lvcbd1 from t2"),
1138: columnLVCBD1value);
1139:
1140: System.out
1141: .println("Test2l - CHAR FOR BIT DATA and LONGVARCHAR FOR BIT DATA concatenation resulting in concatenated string > 32700");
1142: byte[] tmpColumnLVCBD2value = new byte[columnLVCBD2value.length];
1143: System.arraycopy(columnLVCBD2value, 0,
1144: tmpColumnLVCBD2value, 0, columnLVCBD2value.length);
1145: columnLVCBD2value = new byte[columnCBD2value.length
1146: + tmpColumnLVCBD2value.length];
1147: System.arraycopy(columnCBD2value, 0, columnLVCBD2value, 0,
1148: columnCBD2value.length);
1149: System.arraycopy(tmpColumnLVCBD2value, 0,
1150: columnLVCBD2value, columnCBD2value.length,
1151: tmpColumnLVCBD2value.length);
1152: dumpSomeMetaDataInfo(
1153: s
1154: .executeQuery("values(select cbd2 || lvcbd2 from t2)"),
1155: concatenatedSQLTypesNames[6]);
1156: verifyByteData(
1157: s
1158: .executeQuery("values(select cbd2 || lvcbd2 from t2)"),
1159: columnLVCBD2value);
1160: s.executeUpdate("update t2 set lvcbd2 = cbd2 || lvcbd2");
1161: verifyByteData(s.executeQuery("select lvcbd2 from t2"),
1162: columnLVCBD2value);
1163:
1164: System.out
1165: .println("Test2m - VARCHAR FOR BIT DATA and LONGVARCHAR FOR BIT DATA concatenation resulting in concatenated string > 32700");
1166: tmpColumnLVCBD2value = new byte[columnLVCBD2value.length];
1167: System.arraycopy(columnLVCBD2value, 0,
1168: tmpColumnLVCBD2value, 0, columnLVCBD2value.length);
1169: columnLVCBD2value = new byte[columnVCBD1value.length
1170: + tmpColumnLVCBD2value.length];
1171: System.arraycopy(columnVCBD1value, 0, columnLVCBD2value, 0,
1172: columnVCBD1value.length);
1173: System.arraycopy(tmpColumnLVCBD2value, 0,
1174: columnLVCBD2value, columnVCBD1value.length,
1175: tmpColumnLVCBD2value.length);
1176: dumpSomeMetaDataInfo(
1177: s
1178: .executeQuery("values(select vcbd1 || lvcbd2 from t2)"),
1179: concatenatedSQLTypesNames[6]);
1180: verifyByteData(
1181: s
1182: .executeQuery("values(select vcbd1 || lvcbd2 from t2)"),
1183: columnLVCBD2value);
1184: s.executeUpdate("update t2 set lvcbd2 = vcbd1 || lvcbd2");
1185: verifyByteData(s.executeQuery("select lvcbd2 from t2"),
1186: columnLVCBD2value);
1187:
1188: System.out
1189: .println("Test2n - LONGVARCHAR FOR BIT DATA and LONGVARCHAR FOR BIT DATA concatenation resulting in concatenated string > 32700");
1190: tmpColumnLVCBD2value = new byte[columnLVCBD2value.length];
1191: System.arraycopy(columnLVCBD2value, 0,
1192: tmpColumnLVCBD2value, 0, columnLVCBD2value.length);
1193: columnLVCBD2value = new byte[columnLVCBD1value.length
1194: + tmpColumnLVCBD2value.length];
1195: System.arraycopy(columnLVCBD1value, 0, columnLVCBD2value,
1196: 0, columnLVCBD1value.length);
1197: System.arraycopy(tmpColumnLVCBD2value, 0,
1198: columnLVCBD2value, columnLVCBD1value.length,
1199: tmpColumnLVCBD2value.length);
1200: dumpSomeMetaDataInfo(
1201: s
1202: .executeQuery("values(select lvcbd1 || lvcbd2 from t2)"),
1203: concatenatedSQLTypesNames[6]);
1204: verifyByteData(
1205: s
1206: .executeQuery("values(select lvcbd1 || lvcbd2 from t2)"),
1207: columnLVCBD2value);
1208: s.executeUpdate("update t2 set lvcbd2 = lvcbd1 || lvcbd2");
1209: verifyByteData(s.executeQuery("select lvcbd2 from t2"),
1210: columnLVCBD2value);
1211:
1212: try { //this is if we ever run the test against DB2, we want to make sure table doesn't already exist in DB2
1213: s.executeUpdate("drop table testBLOB_MAIN");
1214: } catch (Exception ex) {
1215: }
1216: s
1217: .executeUpdate("create table testBLOB_MAIN (cbd1 CHAR(10) FOR BIT DATA, vcbd1 VARCHAR(100) FOR BIT DATA, lvcbd1 LONG VARCHAR FOR BIT DATA, blob1 BLOB(2G), blob2 BLOB(256), blob3 BLOB(1M))");
1218: ps = conn
1219: .prepareStatement("insert into testBLOB_MAIN values(?,?,?,?,?,?)");
1220: columnCBD1value = new byte[10];
1221: java.util.Arrays.fill(columnCBD1value, (byte) 'a');
1222: ps.setBytes(1, columnCBD1value);
1223: columnVCBD1value = new byte[10];
1224: java.util.Arrays.fill(columnVCBD1value, (byte) 'b');
1225: ps.setBytes(2, columnVCBD1value);
1226: columnLVCBD1value = new byte[10];
1227: java.util.Arrays.fill(columnLVCBD1value, (byte) 'c');
1228: ps.setBytes(3, columnLVCBD1value);
1229: byte[] columnBLOB1value = new byte[10];
1230: java.util.Arrays.fill(columnBLOB1value, (byte) 'd');
1231: ps.setBytes(4, columnBLOB1value);
1232: byte[] columnBLOB2value = new byte[10];
1233: java.util.Arrays.fill(columnBLOB2value, (byte) 'e');
1234: ps.setBytes(5, columnBLOB2value);
1235: byte[] columnBLOB3value = new byte[10];
1236: java.util.Arrays.fill(columnBLOB3value, (byte) 'f');
1237: ps.setBytes(6, columnBLOB3value);
1238: ps.executeUpdate();
1239:
1240: System.out
1241: .println("Test2o - CHAR(A) FOR BIT DATA and BLOB(B) concatenations will give result type of BLOB(A+B) when A+B<2G");
1242: byte[] tmpValue = new byte[columnBLOB2value.length];
1243: System.arraycopy(columnBLOB2value, 0, tmpValue, 0,
1244: columnBLOB2value.length);
1245: columnBLOB2value = new byte[columnCBD1value.length
1246: + tmpValue.length];
1247: System.arraycopy(columnCBD1value, 0, columnBLOB2value, 0,
1248: columnCBD1value.length);
1249: System.arraycopy(tmpValue, 0, columnBLOB2value,
1250: columnCBD1value.length, tmpValue.length);
1251: dumpSomeMetaDataInfo(
1252: s
1253: .executeQuery("values(select cbd1 || blob2 from testBLOB_MAIN)"),
1254: concatenatedSQLTypesNames[7]);
1255: verifyByteData(
1256: s
1257: .executeQuery("values(select cbd1 || blob2 from testBLOB_MAIN)"),
1258: columnBLOB2value);
1259: s
1260: .executeUpdate("update testBLOB_MAIN set blob2 = cast((cbd1 || blob2) as blob(256))");
1261: verifyByteData(s
1262: .executeQuery("select blob2 from testBLOB_MAIN"),
1263: columnBLOB2value);
1264:
1265: System.out
1266: .println("Test2p - BLOB(A) and CHAR(B) FOR BIT DATA concatenations will give result type of BLOB(A+B) when A+B<2G");
1267: tmpValue = new byte[columnBLOB2value.length];
1268: System.arraycopy(columnBLOB2value, 0, tmpValue, 0,
1269: columnBLOB2value.length);
1270: columnBLOB2value = new byte[tmpValue.length
1271: + columnCBD1value.length];
1272: System.arraycopy(tmpValue, 0, columnBLOB2value, 0,
1273: tmpValue.length);
1274: System.arraycopy(columnCBD1value, 0, columnBLOB2value,
1275: tmpValue.length, columnCBD1value.length);
1276: dumpSomeMetaDataInfo(
1277: s
1278: .executeQuery("values(select blob2 || cbd1 from testBLOB_MAIN)"),
1279: concatenatedSQLTypesNames[7]);
1280: verifyByteData(
1281: s
1282: .executeQuery("values(select blob2 || cbd1 from testBLOB_MAIN)"),
1283: columnBLOB2value);
1284: s
1285: .executeUpdate("update testBLOB_MAIN set blob2 = cast((blob2 || cbd1) as blob(256))");
1286: verifyByteData(s
1287: .executeQuery("select blob2 from testBLOB_MAIN"),
1288: columnBLOB2value);
1289:
1290: System.out
1291: .println("Test2q - CHAR(A) FOR BIT DATA and BLOB(B) concatenations will give result type of BLOB(2G) when A+B>2G");
1292: tmpValue = new byte[columnBLOB1value.length];
1293: System.arraycopy(columnBLOB1value, 0, tmpValue, 0,
1294: columnBLOB1value.length);
1295: columnBLOB1value = new byte[columnCBD1value.length
1296: + tmpValue.length];
1297: System.arraycopy(columnCBD1value, 0, columnBLOB1value, 0,
1298: columnCBD1value.length);
1299: System.arraycopy(tmpValue, 0, columnBLOB1value,
1300: columnCBD1value.length, tmpValue.length);
1301: dumpSomeMetaDataInfo(
1302: s
1303: .executeQuery("values(select cbd1 || blob1 from testBLOB_MAIN)"),
1304: concatenatedSQLTypesNames[7]);
1305: verifyByteData(
1306: s
1307: .executeQuery("values(select cbd1 || blob1 from testBLOB_MAIN)"),
1308: columnBLOB1value);
1309: s
1310: .executeUpdate("update testBLOB_MAIN set blob1 = cast((cbd1 || blob1) as blob(2G))");
1311: verifyByteData(s
1312: .executeQuery("select blob1 from testBLOB_MAIN"),
1313: columnBLOB1value);
1314:
1315: System.out
1316: .println("Test2r - BLOB(A) and CHAR(B) FOR BIT DATA concatenations will give result type of BLOB(2G) when A+B>2G");
1317: tmpValue = new byte[columnBLOB1value.length];
1318: System.arraycopy(columnBLOB1value, 0, tmpValue, 0,
1319: columnBLOB1value.length);
1320: columnBLOB1value = new byte[tmpValue.length
1321: + columnCBD1value.length];
1322: System.arraycopy(tmpValue, 0, columnBLOB1value, 0,
1323: tmpValue.length);
1324: System.arraycopy(columnCBD1value, 0, columnBLOB1value,
1325: tmpValue.length, columnCBD1value.length);
1326: dumpSomeMetaDataInfo(
1327: s
1328: .executeQuery("values(select blob1 || cbd1 from testBLOB_MAIN)"),
1329: concatenatedSQLTypesNames[7]);
1330: verifyByteData(
1331: s
1332: .executeQuery("values(select blob1 || cbd1 from testBLOB_MAIN)"),
1333: columnBLOB1value);
1334: s
1335: .executeUpdate("update testBLOB_MAIN set blob1 = cast((blob1 || cbd1) as blob(2G))");
1336: verifyByteData(s
1337: .executeQuery("select blob1 from testBLOB_MAIN"),
1338: columnBLOB1value);
1339:
1340: System.out
1341: .println("Test2s - VARCHAR(A) FOR BIT DATA and BLOB(B) concatenations will give result type of BLOB(A+B) when A+B<2G");
1342: tmpValue = new byte[columnBLOB2value.length];
1343: System.arraycopy(columnBLOB2value, 0, tmpValue, 0,
1344: columnBLOB2value.length);
1345: columnBLOB2value = new byte[columnVCBD1value.length
1346: + tmpValue.length];
1347: System.arraycopy(columnVCBD1value, 0, columnBLOB2value, 0,
1348: columnVCBD1value.length);
1349: System.arraycopy(tmpValue, 0, columnBLOB2value,
1350: columnVCBD1value.length, tmpValue.length);
1351: dumpSomeMetaDataInfo(
1352: s
1353: .executeQuery("values(select vcbd1 || blob2 from testBLOB_MAIN)"),
1354: concatenatedSQLTypesNames[7]);
1355: verifyByteData(
1356: s
1357: .executeQuery("values(select vcbd1 || blob2 from testBLOB_MAIN)"),
1358: columnBLOB2value);
1359: s
1360: .executeUpdate("update testBLOB_MAIN set blob2 = cast((vcbd1 || blob2) as blob(256))");
1361: verifyByteData(s
1362: .executeQuery("select blob2 from testBLOB_MAIN"),
1363: columnBLOB2value);
1364:
1365: System.out
1366: .println("Test2t - BLOB(A) and VARCHAR(B) FOR BIT DATA concatenations will give result type of BLOB(A+B) when A+B<2G");
1367: tmpValue = new byte[columnBLOB2value.length];
1368: System.arraycopy(columnBLOB2value, 0, tmpValue, 0,
1369: columnBLOB2value.length);
1370: columnBLOB2value = new byte[tmpValue.length
1371: + columnVCBD1value.length];
1372: System.arraycopy(tmpValue, 0, columnBLOB2value, 0,
1373: tmpValue.length);
1374: System.arraycopy(columnVCBD1value, 0, columnBLOB2value,
1375: tmpValue.length, columnVCBD1value.length);
1376: dumpSomeMetaDataInfo(
1377: s
1378: .executeQuery("values(select blob2 || vcbd1 from testBLOB_MAIN)"),
1379: concatenatedSQLTypesNames[7]);
1380: verifyByteData(
1381: s
1382: .executeQuery("values(select blob2 || vcbd1 from testBLOB_MAIN)"),
1383: columnBLOB2value);
1384: s
1385: .executeUpdate("update testBLOB_MAIN set blob2 = cast((blob2 || vcbd1) as blob(256))");
1386: verifyByteData(s
1387: .executeQuery("select blob2 from testBLOB_MAIN"),
1388: columnBLOB2value);
1389:
1390: System.out
1391: .println("Test2u - VARCHAR(A) FOR BIT DATA and BLOB(B) concatenations will give result type of BLOB(2G) when A+B>2G");
1392: tmpValue = new byte[columnBLOB1value.length];
1393: System.arraycopy(columnBLOB1value, 0, tmpValue, 0,
1394: columnBLOB1value.length);
1395: columnBLOB1value = new byte[columnVCBD1value.length
1396: + tmpValue.length];
1397: System.arraycopy(columnVCBD1value, 0, columnBLOB1value, 0,
1398: columnVCBD1value.length);
1399: System.arraycopy(tmpValue, 0, columnBLOB1value,
1400: columnVCBD1value.length, tmpValue.length);
1401: dumpSomeMetaDataInfo(
1402: s
1403: .executeQuery("values(select vcbd1 || blob1 from testBLOB_MAIN)"),
1404: concatenatedSQLTypesNames[7]);
1405: verifyByteData(
1406: s
1407: .executeQuery("values(select vcbd1 || blob1 from testBLOB_MAIN)"),
1408: columnBLOB1value);
1409: s
1410: .executeUpdate("update testBLOB_MAIN set blob1 = cast((vcbd1 || blob1) as blob(2G))");
1411: verifyByteData(s
1412: .executeQuery("select blob1 from testBLOB_MAIN"),
1413: columnBLOB1value);
1414:
1415: System.out
1416: .println("Test2v - BLOB(A) and VARCHAR(B) FOR BIT DATA concatenations will give result type of BLOB(2G) when A+B>2G");
1417: tmpValue = new byte[columnBLOB1value.length];
1418: System.arraycopy(columnBLOB1value, 0, tmpValue, 0,
1419: columnBLOB1value.length);
1420: columnBLOB1value = new byte[tmpValue.length
1421: + columnVCBD1value.length];
1422: System.arraycopy(tmpValue, 0, columnBLOB1value, 0,
1423: tmpValue.length);
1424: System.arraycopy(columnVCBD1value, 0, columnBLOB1value,
1425: tmpValue.length, columnVCBD1value.length);
1426: dumpSomeMetaDataInfo(
1427: s
1428: .executeQuery("values(select blob1 || vcbd1 from testBLOB_MAIN)"),
1429: concatenatedSQLTypesNames[7]);
1430: verifyByteData(
1431: s
1432: .executeQuery("values(select blob1 || vcbd1 from testBLOB_MAIN)"),
1433: columnBLOB1value);
1434: s
1435: .executeUpdate("update testBLOB_MAIN set blob1 = cast((blob1 || vcbd1) as blob(2G))");
1436: verifyByteData(s
1437: .executeQuery("select blob1 from testBLOB_MAIN"),
1438: columnBLOB1value);
1439:
1440: System.out
1441: .println("Test2w - LONG VARCHAR FOR BIT DATA and BLOB(A) concatenations will give result type of BLOB(A+32K) when A+32K<2G");
1442: tmpValue = new byte[columnBLOB2value.length];
1443: System.arraycopy(columnBLOB2value, 0, tmpValue, 0,
1444: columnBLOB2value.length);
1445: columnBLOB2value = new byte[columnLVCBD1value.length
1446: + tmpValue.length];
1447: System.arraycopy(columnLVCBD1value, 0, columnBLOB2value, 0,
1448: columnLVCBD1value.length);
1449: System.arraycopy(tmpValue, 0, columnBLOB2value,
1450: columnLVCBD1value.length, tmpValue.length);
1451: dumpSomeMetaDataInfo(
1452: s
1453: .executeQuery("values(select lvcbd1 || blob2 from testBLOB_MAIN)"),
1454: concatenatedSQLTypesNames[7]);
1455: verifyByteData(
1456: s
1457: .executeQuery("values(select lvcbd1 || blob2 from testBLOB_MAIN)"),
1458: columnBLOB2value);
1459: s
1460: .executeUpdate("update testBLOB_MAIN set blob2 = cast((lvcbd1 || blob2) as blob(256))");
1461: verifyByteData(s
1462: .executeQuery("select blob2 from testBLOB_MAIN"),
1463: columnBLOB2value);
1464:
1465: System.out
1466: .println("Test2x - BLOB(A) and LONG VARCHAR FOR BIT DATA concatenations will give result type of BLOB(A+32K) when A+32K<2G");
1467: tmpValue = new byte[columnBLOB2value.length];
1468: System.arraycopy(columnBLOB2value, 0, tmpValue, 0,
1469: columnBLOB2value.length);
1470: columnBLOB2value = new byte[tmpValue.length
1471: + columnLVCBD1value.length];
1472: System.arraycopy(tmpValue, 0, columnBLOB2value, 0,
1473: tmpValue.length);
1474: System.arraycopy(columnLVCBD1value, 0, columnBLOB2value,
1475: tmpValue.length, columnLVCBD1value.length);
1476: dumpSomeMetaDataInfo(
1477: s
1478: .executeQuery("values(select blob2 || lvcbd1 from testBLOB_MAIN)"),
1479: concatenatedSQLTypesNames[7]);
1480: verifyByteData(
1481: s
1482: .executeQuery("values(select blob2 || lvcbd1 from testBLOB_MAIN)"),
1483: columnBLOB2value);
1484: s
1485: .executeUpdate("update testBLOB_MAIN set blob2 = cast((blob2 || lvcbd1) as blob(256))");
1486: verifyByteData(s
1487: .executeQuery("select blob2 from testBLOB_MAIN"),
1488: columnBLOB2value);
1489:
1490: System.out
1491: .println("Test2y - LONG VARCHAR FOR BIT DATA and BLOB(B) concatenations will give result type of BLOB(2G) when A+32K>2G");
1492: tmpValue = new byte[columnBLOB1value.length];
1493: System.arraycopy(columnBLOB1value, 0, tmpValue, 0,
1494: columnBLOB1value.length);
1495: columnBLOB1value = new byte[columnLVCBD1value.length
1496: + tmpValue.length];
1497: System.arraycopy(columnLVCBD1value, 0, columnBLOB1value, 0,
1498: columnLVCBD1value.length);
1499: System.arraycopy(tmpValue, 0, columnBLOB1value,
1500: columnLVCBD1value.length, tmpValue.length);
1501: dumpSomeMetaDataInfo(
1502: s
1503: .executeQuery("values(select lvcbd1 || blob1 from testBLOB_MAIN)"),
1504: concatenatedSQLTypesNames[7]);
1505: verifyByteData(
1506: s
1507: .executeQuery("values(select lvcbd1 || blob1 from testBLOB_MAIN)"),
1508: columnBLOB1value);
1509: s
1510: .executeUpdate("update testBLOB_MAIN set blob1 = cast((lvcbd1 || blob1) as blob(2G))");
1511: verifyByteData(s
1512: .executeQuery("select blob1 from testBLOB_MAIN"),
1513: columnBLOB1value);
1514:
1515: System.out
1516: .println("Test2z - BLOB(A) and LONG VARCHAR FOR BIT DATA concatenations will give result type of BLOB(2G) when A+32K>2G");
1517: tmpValue = new byte[columnBLOB1value.length];
1518: System.arraycopy(columnBLOB1value, 0, tmpValue, 0,
1519: columnBLOB1value.length);
1520: columnBLOB1value = new byte[tmpValue.length
1521: + columnLVCBD1value.length];
1522: System.arraycopy(tmpValue, 0, columnBLOB1value, 0,
1523: tmpValue.length);
1524: System.arraycopy(columnLVCBD1value, 0, columnBLOB1value,
1525: tmpValue.length, columnLVCBD1value.length);
1526: dumpSomeMetaDataInfo(
1527: s
1528: .executeQuery("values(select blob1 || lvcbd1 from testBLOB_MAIN)"),
1529: concatenatedSQLTypesNames[7]);
1530: verifyByteData(
1531: s
1532: .executeQuery("values(select blob1 || lvcbd1 from testBLOB_MAIN)"),
1533: columnBLOB1value);
1534: s
1535: .executeUpdate("update testBLOB_MAIN set blob1 = cast((blob1 || lvcbd1) as blob(2G))");
1536: verifyByteData(s
1537: .executeQuery("select blob1 from testBLOB_MAIN"),
1538: columnBLOB1value);
1539:
1540: System.out
1541: .println("Test21a - BLOB(A) and BLOB(B) concatenations will give result type of BLOB(A+B) when A+B<2G");
1542: tmpValue = new byte[columnBLOB2value.length];
1543: System.arraycopy(columnBLOB2value, 0, tmpValue, 0,
1544: columnBLOB2value.length);
1545: columnBLOB2value = new byte[tmpValue.length
1546: + columnBLOB3value.length];
1547: System.arraycopy(tmpValue, 0, columnBLOB2value, 0,
1548: tmpValue.length);
1549: System.arraycopy(columnBLOB3value, 0, columnBLOB2value,
1550: tmpValue.length, columnBLOB3value.length);
1551: dumpSomeMetaDataInfo(
1552: s
1553: .executeQuery("values(select blob2 || blob3 from testBLOB_MAIN)"),
1554: concatenatedSQLTypesNames[7]);
1555: verifyByteData(
1556: s
1557: .executeQuery("values(select blob2 || blob3 from testBLOB_MAIN)"),
1558: columnBLOB2value);
1559: s
1560: .executeUpdate("update testBLOB_MAIN set blob2 = blob2 || blob3");
1561: verifyByteData(s
1562: .executeQuery("select blob2 from testBLOB_MAIN"),
1563: columnBLOB2value);
1564:
1565: System.out
1566: .println("Test21b - BLOB(A) and BLOB(B) concatenations will give result type of BLOB(2G) when A+B>2G");
1567: tmpValue = new byte[columnBLOB1value.length];
1568: System.arraycopy(columnBLOB1value, 0, tmpValue, 0,
1569: columnBLOB1value.length);
1570: columnBLOB1value = new byte[columnBLOB2value.length
1571: + tmpValue.length];
1572: System.arraycopy(columnBLOB2value, 0, columnBLOB1value, 0,
1573: columnBLOB2value.length);
1574: System.arraycopy(tmpValue, 0, columnBLOB1value,
1575: columnBLOB2value.length, tmpValue.length);
1576: dumpSomeMetaDataInfo(
1577: s
1578: .executeQuery("values(select blob2 || blob1 from testBLOB_MAIN)"),
1579: concatenatedSQLTypesNames[7]);
1580: verifyByteData(
1581: s
1582: .executeQuery("values(select blob2 || blob1 from testBLOB_MAIN)"),
1583: columnBLOB1value);
1584: s
1585: .executeUpdate("update testBLOB_MAIN set blob1 = blob2 || blob1");
1586: verifyByteData(s
1587: .executeQuery("select blob1 from testBLOB_MAIN"),
1588: columnBLOB1value);
1589:
1590: System.out
1591: .println("Test22 - try 2 empty char for bit data concatenation and verify that length comes back as 0 for the result");
1592: dumpSomeMetaDataInfo(s.executeQuery("values(X'' || X'')"),
1593: concatenatedSQLTypesNames[4]);
1594:
1595: System.out
1596: .println("Test23 - Derby-124 try concatenation in a prepared statement with one operand casted to BLOB and other as ? parameter");
1597: System.out
1598: .println(" Prior to Derby-124 fix, ? parameter was getting bound to VARCHAR TO BIT DATA of length 32672 rather than BLOB.");
1599: System.out
1600: .println(" That caused truncation exception when ? parameter was set to > 32672 bytes");
1601: try {
1602: s.executeUpdate("drop table bt");
1603: } catch (Exception ex) {
1604: }
1605: s.executeUpdate("create table bt (b BLOB(100K))");
1606: byte[] bData = new byte[32700];
1607: for (int i = 0; i < bData.length; i++)
1608: bData[i] = (byte) (i % 10);
1609: byte[] bData1 = new byte[2];
1610: bData1[0] = (byte) 0x10;
1611: bData1[1] = (byte) 0x10;
1612: byte[] bConcatenatedData = new byte[32702];
1613: System.arraycopy(bData1, 0, bConcatenatedData, 0,
1614: bData1.length);
1615: System.arraycopy(bData, 0, bConcatenatedData,
1616: bData1.length, bData.length);
1617: //Prior to fix for Derby-124, the ? was getting bound to VARCHAR FOR BIT DATA with max length of 32670
1618: //And when ps.setBytes() set 32700 bytes into the ? parameter, at ps.execute() time, you got following exception
1619: //ERROR 22001: A truncation error was encountered trying to shrink VARCHAR () FOR BIT DATA 'XX-RESOLVE-XX' to length 32672.
1620: //As a fix for this, if one of the operands of concatenation is BLOB, then the ? parameter would be bound to BLOB as well
1621: System.out.println(" preapre statement with blob||?");
1622: ps = conn
1623: .prepareStatement("insert into bt values (cast (x'1010' as BLOB) || ?)");
1624: ps.setBytes(1, bData);
1625: ps.execute();
1626: verifyByteData(s.executeQuery("select b from bt"),
1627: bConcatenatedData);
1628: s.executeUpdate("delete from bt");
1629: System.out
1630: .println(" Test - preapre statement with blob||cast(? to BLOB)");
1631: ps = conn
1632: .prepareStatement("insert into bt values (cast (x'1010' as BLOB) || cast(? as BLOB))");
1633: ps.setBytes(1, bData);
1634: ps.execute();
1635: verifyByteData(s.executeQuery("select b from bt"),
1636: bConcatenatedData);
1637:
1638: s.executeUpdate("drop table testBLOB_MAIN");
1639: s.executeUpdate("drop table t2");
1640: s.executeUpdate("drop table bt");
1641: System.out
1642: .println("Test2 finished - CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONGVARCHAR FOR BIT DATA and BLOB concatenation tests");
1643: } catch (SQLException sqle) {
1644: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
1645: System.out, sqle);
1646: sqle.printStackTrace(System.out);
1647: }
1648: }
1649:
1650: private static void verifyStringData(ResultSet rs,
1651: String expectedValue) throws SQLException {
1652: if (rs == null) {
1653: System.out.println("<NULL>");
1654: return;
1655: }
1656:
1657: rs.next();
1658: if (!(rs.getString(1).equals(expectedValue))) {
1659: System.out
1660: .println("ERROR: expected value does not match actual value");
1661: System.out.println("expected value is " + expectedValue);
1662: System.out
1663: .println("what we got here is " + rs.getString(1));
1664: } else {
1665: System.out.println("Successful "
1666: + rs.getMetaData().getColumnTypeName(1)
1667: + " read of " + expectedValue.length()
1668: + " characters");
1669: }
1670: }
1671:
1672: private static void verifyByteData(ResultSet rs,
1673: byte[] expectedValue) throws SQLException {
1674: if (rs == null) {
1675: System.out.println("<NULL>");
1676: return;
1677: }
1678:
1679: rs.next();
1680: if (!(java.util.Arrays.equals(rs.getBytes(1), expectedValue))) {
1681: System.out
1682: .println("ERROR: expected value does not match actual value");
1683: System.out.println("expected value is " + expectedValue);
1684: System.out.println("what we got here is " + rs.getBytes(1));
1685: } else {
1686: System.out.println("Successful "
1687: + rs.getMetaData().getColumnTypeName(1)
1688: + " read of " + expectedValue.length + " bytes");
1689: }
1690: }
1691:
1692: private static void dumpSomeMetaDataInfo(ResultSet s,
1693: String expectedTypeName) throws SQLException {
1694: if (s == null) {
1695: System.out.println("<NULL>");
1696: return;
1697: }
1698:
1699: ResultSetMetaData rsmd = s.getMetaData();
1700:
1701: // Get the number of columns in the result set
1702: int numCols = rsmd.getColumnCount();
1703:
1704: StringBuffer heading = new StringBuffer("\t ");
1705:
1706: // Display column headings
1707: for (int i = 1; i <= numCols; i++) {
1708: System.out.println("datatype of concatenated string is : "
1709: + rsmd.getColumnTypeName(i));
1710: if (!(rsmd.getColumnTypeName(i).equals(expectedTypeName)))
1711: System.out
1712: .println("FAIL : expected datatype of concatenated string is : "
1713: + expectedTypeName);
1714: System.out.println("precision of concatenated string is : "
1715: + rsmd.getPrecision(i));
1716: }
1717: }
1718:
1719: static private void dumpSQLExceptions(SQLException se) {
1720: System.out.println("FAIL -- unexpected exception: "
1721: + se.toString());
1722: while (se != null) {
1723: System.out.print("SQLSTATE(" + se.getSQLState() + "):");
1724: se = se.getNextException();
1725: }
1726: }
1727:
1728: }
|