Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > lang > concateTests


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.tests.lang.concateTests
4
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to You under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11
12       http://www.apache.org/licenses/LICENSE-2.0
13
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19
20  */
21
22 package org.apache.derbyTesting.functionTests.tests.lang;
23
24 import java.io.*;
25 import java.sql.*;
26 import java.util.Arrays ;
27
28 import org.apache.derby.tools.ij;
29 import org.apache.derbyTesting.functionTests.util.Formatters;
30
31 import java.io.ByteArrayInputStream ;
32
33 /**
34   Concatenation tests for various datatypes
35  */
36 public class concateTests
37 {
38
39     private static String [] concatenatedSQLTypesNames =
40     {
41         /*0*/ "CHAR",
42         /*1*/ "VARCHAR",
43         /*2*/ "LONG VARCHAR",
44         /*3*/ "CLOB",
45         /*4*/ "CHAR () FOR BIT DATA",
46         /*5*/ "VARCHAR () FOR BIT DATA",
47         /*6*/ "LONG VARCHAR FOR BIT DATA",
48         /*7*/ "BLOB",
49
50     };
51
52     public static void main (String [] argv) throws Throwable
53     {
54         ij.getPropertyArg(argv);
55         Connection conn = ij.startJBMS();
56
57         testCharConcatenation(conn);
58         testCharForBitDataConcatenation(conn);
59     }
60
61     public static void testCharConcatenation( Connection conn) throws Throwable
62     {
63     try {
64             System.out.println("Test1 - CHAR, VARCHAR, LONGVARCHAR and CLOB concatenation tests");
65
66             String columnC1value;
67             String columnC2value = Formatters.repeatChar("a",40);
68             String columnVC1value;
69             String columnVC2value;
70             String columnVC3value = "z";
71             String columnLVC1value;
72             String columnLVC2value = Formatters.repeatChar("b",32698);
73             StringBuffer tempStringBuffer = new StringBuffer ();
74
75             Statement s = conn.createStatement();
76             try { //this is if we ever run the test against DB2, we want to make sure table doesn't already exist in DB2
77 s.executeUpdate("drop table t1");
78             } catch(Exception ex) {}
79             s.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)");
80             PreparedStatement ps = conn.prepareStatement("insert into t1(c2, vc3) values(?, ?)");
81       ps.setString(1,columnC2value);
82       ps.setString(2,columnVC3value);
83             ps.executeUpdate();
84             ps = conn.prepareStatement("update t1 set lvc2 = ?");
85       ps.setString(1,columnLVC2value);
86             ps.executeUpdate();
87
88             System.out.println("Test1a - CHAR concatenations will give result type of CHAR when concatenated string < 255");
89             //operands CHAR(A) CHAR(B) and A+B<255 then result is CHAR(A+B)
90 dumpSomeMetaDataInfo(s.executeQuery("values(select c2 || c2 || c2 || c2 || c2 || c2 || '12345678901234' from t1)"), concatenatedSQLTypesNames[0]);
91             tempStringBuffer = new StringBuffer (columnC2value);
92       tempStringBuffer.append(columnC2value).append(columnC2value).append(columnC2value).append(columnC2value).append(columnC2value);
93       tempStringBuffer.append("12345678901234");
94       columnC1value = tempStringBuffer.toString();
95             verifyStringData(s.executeQuery("values(select c2 || c2 || c2 || c2 || c2 || c2 || '12345678901234' from t1)"), columnC1value);
96             s.executeUpdate("update t1 set c1 = c2 || c2 || c2 || c2 || c2 || c2 || '12345678901234'");
97             verifyStringData(s.executeQuery("select c1 from t1"), columnC1value);
98
99             System.out.println("Test1b boundary test - CHAR concatenations will give result type of VARCHAR when concatenated string = 255");
100             //operands CHAR(A) CHAR(B) and A+B>254 then result is VARCHAR(A+B)
101 columnVC1value = columnC1value + "1";
102             dumpSomeMetaDataInfo(s.executeQuery("values(select c1 || '1' from t1)"), concatenatedSQLTypesNames[1]);
103             verifyStringData(s.executeQuery("values(select c1 || '1' from t1)"), columnVC1value);
104             s.executeUpdate("update t1 set vc1 = c1 || '1'");
105             verifyStringData(s.executeQuery("select vc1 from t1"), columnVC1value);
106
107             System.out.println("Test1b - CHAR concatenations will give result type of VARCHAR when concatenated string > 254");
108             //operands CHAR(A) CHAR(B) and A+B>254 then result is VARCHAR(A+B)
109 columnVC1value = columnC1value + "1234567890";
110             dumpSomeMetaDataInfo(s.executeQuery("values(select c1 || '1234567890' from t1)"), concatenatedSQLTypesNames[1]);
111             verifyStringData(s.executeQuery("values(select c1 || '1234567890' from t1)"), columnVC1value);
112             s.executeUpdate("update t1 set vc1 = c1 || '1234567890'");
113             verifyStringData(s.executeQuery("select vc1 from t1"), columnVC1value);
114
115             System.out.println("Test1c - CHAR and VARCHAR concatenations will give result type of VARCHAR when concatenated string < 4001");
116             //operands CHAR(A) VARCHAR(B) and A+B<4001 then result is VARCHAR(A+B)
117 //concatenated string 4000 characters long in following updates
118 tempStringBuffer = new StringBuffer (columnC2value);
119       tempStringBuffer.append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value);
120       tempStringBuffer.append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value);
121       tempStringBuffer.append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value);
122       columnVC2value = tempStringBuffer.toString();
123       columnLVC1value = tempStringBuffer.toString();
124             dumpSomeMetaDataInfo(s.executeQuery("values(select c2||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1 from t1)"), concatenatedSQLTypesNames[1]);
125             verifyStringData(s.executeQuery("values(select c2||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1 from t1)"), columnVC2value);
126             s.executeUpdate("update t1 set vc2 = c2||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1");
127             verifyStringData(s.executeQuery("select vc2 from t1"), columnVC2value);
128             s.executeUpdate("update t1 set lvc1 = c2||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1");
129             verifyStringData(s.executeQuery("select lvc1 from t1"), columnLVC1value);
130
131             System.out.println("Test1d - VARCHAR and CHAR concatenations will give result type of VARCHAR when concatenated string < 4001");
132             //operands VARCHAR(A) CHAR(B) and A+B<4001 then result is VARCHAR(A+B)
133 //concatenated string 4000 characters long in following updates
134 tempStringBuffer = new StringBuffer ();
135       tempStringBuffer.append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value);
136       tempStringBuffer.append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value);
137       tempStringBuffer.append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value);
138             tempStringBuffer.append(columnC2value);
139       columnVC2value = tempStringBuffer.toString();
140       columnLVC1value = tempStringBuffer.toString();
141             dumpSomeMetaDataInfo(s.executeQuery("values(select vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||c2 from t1)"), concatenatedSQLTypesNames[1]);
142             verifyStringData(s.executeQuery("values(select vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||c2 from t1)"), columnVC2value);
143             s.executeUpdate("update t1 set vc2 = vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||c2");
144             verifyStringData(s.executeQuery("select vc2 from t1"), columnVC2value);
145             s.executeUpdate("update t1 set lvc1 = vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||c2");
146             verifyStringData(s.executeQuery("select lvc1 from t1"), columnLVC1value);
147
148             System.out.println("Test1e boundary test - CHAR and VARCHAR concatenations will give result type of LONG VARCHAR when concatenated string = 4001");
149             //operands CHAR(A) VARCHAR(B) and A+B>4000 then result is LONG VARCHAR
150 //concatenated string is 4001 characters long in following 2 updates
151 columnLVC1value = "a"+columnVC2value;
152             dumpSomeMetaDataInfo(s.executeQuery("values(select 'a'||vc2 from t1)"), concatenatedSQLTypesNames[2]);
153             verifyStringData(s.executeQuery("values(select 'a'||vc2 from t1)"), columnLVC1value);
154             s.executeUpdate("update t1 set lvc1 = 'a'||vc2");
155             verifyStringData(s.executeQuery("select lvc1 from t1"), columnLVC1value);
156
157             System.out.println("Test1f boundary test - VARCHAR and CHAR concatenations will give result type of LONG VARCHAR when concatenated string = 4001");
158             //operands VARCHAR(A) CHAR(B) and A+B>4000 then result is LONG VARCHAR
159 columnLVC1value = columnVC2value+"a";
160             dumpSomeMetaDataInfo(s.executeQuery("values(select vc2 || 'a' from t1)"), concatenatedSQLTypesNames[2]);
161             verifyStringData(s.executeQuery("values(select vc2 || 'a' from t1)"), columnLVC1value);
162             s.executeUpdate("update t1 set lvc1 = vc2 || 'a'");
163             verifyStringData(s.executeQuery("select lvc1 from t1"), columnLVC1value);
164
165             System.out.println("Test1g - CHAR and LONG VARCHAR concatenations will give result type of LONG VARCHAR");
166             //operands CHAR(A) LONG VARCHAR then result is LONG VARCHAR
167 columnLVC1value = "a"+columnLVC1value;
168             dumpSomeMetaDataInfo(s.executeQuery("values(select 'a' || lvc1 from t1)"), concatenatedSQLTypesNames[2]);
169             verifyStringData(s.executeQuery("values(select 'a' || lvc1 from t1)"), columnLVC1value);
170             s.executeUpdate("update t1 set lvc1 = 'a' || lvc1");
171             verifyStringData(s.executeQuery("select lvc1 from t1"), columnLVC1value);
172
173             System.out.println("Test1h - VARCHAR and LONG VARCHAR concatenations will give result type of LONG VARCHAR");
174             //operands VARCHAR(A) LONG VARCHAR then result is LONG VARCHAR
175 columnLVC1value = columnVC1value+columnLVC1value;
176             dumpSomeMetaDataInfo(s.executeQuery("values(select vc1 || lvc1 from t1)"), concatenatedSQLTypesNames[2]);
177             verifyStringData(s.executeQuery("values(select vc1 || lvc1 from t1)"), columnLVC1value);
178             s.executeUpdate("update t1 set lvc1 = vc1 || lvc1");
179             verifyStringData(s.executeQuery("select lvc1 from t1"), columnLVC1value);
180
181             System.out.println("Test1i - VARCHAR and VARCHAR concatenations will give result type of VARCHAR when concatenated string < 4001");
182             //operands VARCHAR(A) VARCHAR(B) and A+B<4001 then result is VARCHAR(A+B)
183 columnLVC1value = columnVC1value+columnVC1value;
184             dumpSomeMetaDataInfo(s.executeQuery("values(select vc1 || vc1 from t1)"), concatenatedSQLTypesNames[1]);
185             verifyStringData(s.executeQuery("values(select vc1 || vc1 from t1)"), columnLVC1value);
186             s.executeUpdate("update t1 set lvc1 = vc1 || vc1");
187             verifyStringData(s.executeQuery("select lvc1 from t1"), columnLVC1value);
188
189             System.out.println("Test1j boundary test - VARCHAR and VARCHAR concatenations will give result type of LONG VARCHAR when concatenated string = 4001");
190             //operands VARCHAR(A) VARCHAR(B) and A+B>4000 then result is LONG VARCHAR
191 columnLVC1value = columnVC2value+columnVC3value;
192             dumpSomeMetaDataInfo(s.executeQuery("values(select vc2 || vc3 from t1)"), concatenatedSQLTypesNames[2]);
193             verifyStringData(s.executeQuery("values(select vc2 || vc3 from t1)"), columnLVC1value);
194             s.executeUpdate("update t1 set lvc1 = vc2 || vc3");
195             verifyStringData(s.executeQuery("select lvc1 from t1"), columnLVC1value);
196
197             System.out.println("Test1j - VARCHAR and VARCHAR concatenations will give result type of LONG VARCHAR when concatenated string > 4000");
198             //operands VARCHAR(A) VARCHAR(B) and A+B>4000 then result is LONG VARCHAR
199 columnLVC1value = columnVC1value+columnVC2value;
200             dumpSomeMetaDataInfo(s.executeQuery("values(select vc1 || vc2 from t1)"), concatenatedSQLTypesNames[2]);
201             verifyStringData(s.executeQuery("values(select vc1 || vc2 from t1)"), columnLVC1value);
202             s.executeUpdate("update t1 set lvc1 = vc1 || vc2");
203             verifyStringData(s.executeQuery("select lvc1 from t1"), columnLVC1value);
204
205             System.out.println("Test1k - LONG VARCHAR and LONG VARCHAR concatenations will give result type of LONG VARCHAR");
206             //operands LONG VARCHAR, LONG VARCHAR then result is LONG VARCHAR
207 columnLVC1value = columnLVC1value+columnLVC1value;
208             dumpSomeMetaDataInfo(s.executeQuery("values(select lvc1 || lvc1 from t1)"), concatenatedSQLTypesNames[2]);
209             verifyStringData(s.executeQuery("values(select lvc1 || lvc1 from t1)"), columnLVC1value);
210             s.executeUpdate("update t1 set lvc1 = lvc1 || lvc1");
211             verifyStringData(s.executeQuery("select lvc1 from t1"), columnLVC1value);
212
213             //operands CHAR(A)/VARCHAR(A)/LONGVARCHAR, LONGVARCHAR and "concatenated string length">32700 does not cause automatic escalation
214 //to LOB for compatibility with previous releases. Any such cases would result in an error at runtime
215 System.out.println("Test1l - CHAR and LONGVARCHAR concatenation resulting in concatenated string > 32700 will give error");
216             try {
217                 dumpSomeMetaDataInfo(s.executeQuery("values(select c2 || lvc2 from t1)"), concatenatedSQLTypesNames[2]);
218                 System.out.println("FAIL - should have gotten overflow error for values");
219             }
220             catch (SQLException e) {
221                 if (e.getSQLState().equals("54006"))
222                     System.out.println("expected exception " + e.getMessage());
223                 else
224                     dumpSQLExceptions(e);
225             }
226             try {
227                 s.executeUpdate("update t1 set lvc2 = c2 || lvc2");
228                 System.out.println("FAIL - should have gotten overflow error for insert");
229             }
230             catch (SQLException e) {
231                 if (e.getSQLState().equals("54006"))
232                     System.out.println("expected exception " + e.getMessage());
233                 else
234                     dumpSQLExceptions(e);
235             }
236
237             System.out.println("Test1m - VARCHAR and LONGVARCHAR concatenation resulting in concatenated string > 32700 will give error");
238             try {
239                 dumpSomeMetaDataInfo(s.executeQuery("values(select vc1 || lvc2 from t1)"), concatenatedSQLTypesNames[2]);
240                 System.out.println("FAIL - should have gotten overflow error for values");
241             }
242             catch (SQLException e) {
243                 if (e.getSQLState().equals("54006"))
244                     System.out.println("expected exception " + e.getMessage());
245                 else
246                     dumpSQLExceptions(e);
247             }
248             try {
249                 s.executeUpdate("update t1 set lvc2 = vc1 || lvc2");
250                 System.out.println("FAIL - should have gotten overflow error for insert");
251             }
252             catch (SQLException e) {
253                 if (e.getSQLState().equals("54006"))
254                     System.out.println("expected exception " + e.getMessage());
255                 else
256                     dumpSQLExceptions(e);
257             }
258
259             System.out.println("Test1n - LONGVARCHAR and LONGVARCHAR concatenation resulting in concatenated string > 32700 will give error");
260             try {
261                 dumpSomeMetaDataInfo(s.executeQuery("values(select lvc1 || lvc2 from t1)"), concatenatedSQLTypesNames[2]);
262                 System.out.println("FAIL - should have gotten overflow error for values");
263             }
264             catch (SQLException e) {
265                 if (e.getSQLState().equals("54006"))
266                     System.out.println("expected exception " + e.getMessage());
267                 else
268                     dumpSQLExceptions(e);
269             }
270             try {
271                 s.executeUpdate("update t1 set lvc2 = lvc1 || lvc2");
272                 System.out.println("FAIL - should have gotten overflow error for insert");
273             }
274             catch (SQLException e) {
275                 if (e.getSQLState().equals("54006"))
276                     System.out.println("expected exception " + e.getMessage());
277                 else
278                     dumpSQLExceptions(e);
279             }
280
281             try { //this is if we ever run the test against DB2, we want to make sure table doesn't already exist in DB2
282 s.executeUpdate("drop table testCLOB_MAIN");
283             } catch(Exception ex) {}
284             s.executeUpdate("create table testCLOB_MAIN (c1 char(10), vc1 varchar(100), lvc1 long varchar, clob1 CLOB(2G), clob2 CLOB(256), clob3 CLOB(1M))");
285             ps = conn.prepareStatement("insert into testCLOB_MAIN values(?,?,?,?,?,?)");
286             columnC1value = "1234567890";
287             ps.setString(1, columnC1value);
288             columnVC1value = "this is varchar";
289             ps.setString(2, columnVC1value);
290             columnLVC1value = "this is long varchar";
291             ps.setString(3, columnLVC1value);
292             String columnCLOB1value = "this is 2G clob";
293             ps.setString(4, columnCLOB1value);
294             String columnCLOB2value = "this is 256 characters clob";
295             ps.setString(5, columnCLOB2value);
296             String columnCLOB3value = "this is 1M clob";
297             ps.setString(6, columnCLOB3value);
298             ps.executeUpdate();
299
300             System.out.println("Test1o - CHAR(A) and CLOB(B) concatenations will give result type of CLOB(A+B) when A+B<2G");
301       columnCLOB2value = columnC1value+"this is 256 characters clob";
302             dumpSomeMetaDataInfo(s.executeQuery("values(select c1 || clob2 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
303             verifyStringData(s.executeQuery("values(select c1 || clob2 from testCLOB_MAIN)"), columnCLOB2value);
304             s.executeUpdate("update testCLOB_MAIN set clob2 = c1 || clob2");
305             verifyStringData(s.executeQuery("select clob2 from testCLOB_MAIN"), columnCLOB2value);
306
307             System.out.println("Test1p - CLOB(A) and CHAR(B) concatenations will give result type of CLOB(A+B) when A+B<2G");
308       columnCLOB2value = columnCLOB2value+columnC1value;
309             dumpSomeMetaDataInfo(s.executeQuery("values(select clob2 || c1 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
310             verifyStringData(s.executeQuery("values(select clob2 || c1 from testCLOB_MAIN)"), columnCLOB2value);
311             s.executeUpdate("update testCLOB_MAIN set clob2 = clob2 || c1");
312             verifyStringData(s.executeQuery("select clob2 from testCLOB_MAIN"), columnCLOB2value);
313
314             System.out.println("Test1q - CHAR(A) and CLOB(B) concatenations will give result type of CLOB(2G) when A+B>2G");
315       columnCLOB1value = columnC1value+columnCLOB1value;
316             dumpSomeMetaDataInfo(s.executeQuery("values(select c1 || clob1 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
317             verifyStringData(s.executeQuery("values(select c1 || clob1 from testCLOB_MAIN)"), columnCLOB1value);
318             s.executeUpdate("update testCLOB_MAIN set clob1 = c1 || clob1");
319             verifyStringData(s.executeQuery("select clob1 from testCLOB_MAIN"), columnCLOB1value);
320
321             System.out.println("Test1r - CLOB(A) and CHAR(B) concatenations will give result type of CLOB(2G) when A+B>2G");
322       columnCLOB1value = columnCLOB1value+columnC1value;
323             dumpSomeMetaDataInfo(s.executeQuery("values(select clob1 || c1 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
324             verifyStringData(s.executeQuery("values(select clob1 || c1 from testCLOB_MAIN)"), columnCLOB1value);
325             s.executeUpdate("update testCLOB_MAIN set clob1 = clob1 || c1");
326             verifyStringData(s.executeQuery("select clob1 from testCLOB_MAIN"), columnCLOB1value);
327
328             System.out.println("Test1s - VARCHAR(A) and CLOB(B) concatenations will give result type of CLOB(A+B) when A+B<2G");
329       columnCLOB2value = columnVC1value+columnCLOB2value;
330             dumpSomeMetaDataInfo(s.executeQuery("values(select vc1 || clob2 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
331             verifyStringData(s.executeQuery("values(select vc1 || clob2 from testCLOB_MAIN)"), columnCLOB2value);
332             s.executeUpdate("update testCLOB_MAIN set clob2 = vc1 || clob2");
333             verifyStringData(s.executeQuery("select clob2 from testCLOB_MAIN"), columnCLOB2value);
334
335             System.out.println("Test1t - CLOB(A) and VARCHAR(B) concatenations will give result type of CLOB(A+B) when A+B<2G");
336       columnCLOB2value = columnCLOB2value+columnVC1value;
337             dumpSomeMetaDataInfo(s.executeQuery("values(select clob2 || vc1 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
338             verifyStringData(s.executeQuery("values(select clob2 || vc1 from testCLOB_MAIN)"), columnCLOB2value);
339             s.executeUpdate("update testCLOB_MAIN set clob2 = clob2 || vc1");
340             verifyStringData(s.executeQuery("select clob2 from testCLOB_MAIN"), columnCLOB2value);
341
342             System.out.println("Test1u - VARCHAR(A) and CLOB(B) concatenations will give result type of CLOB(2G) when A+B>2G");
343       columnCLOB1value = columnVC1value+columnCLOB1value;
344             dumpSomeMetaDataInfo(s.executeQuery("values(select vc1 || clob1 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
345             verifyStringData(s.executeQuery("values(select vc1 || clob1 from testCLOB_MAIN)"), columnCLOB1value);
346             s.executeUpdate("update testCLOB_MAIN set clob1 = vc1 || clob1");
347             verifyStringData(s.executeQuery("select clob1 from testCLOB_MAIN"), columnCLOB1value);
348
349             System.out.println("Test1v - CLOB(A) and VARCHAR(B) concatenations will give result type of CLOB(2G) when A+B>2G");
350       columnCLOB1value = columnCLOB1value+columnVC1value;
351             dumpSomeMetaDataInfo(s.executeQuery("values(select clob1 || vc1 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
352             verifyStringData(s.executeQuery("values(select clob1 || vc1 from testCLOB_MAIN)"), columnCLOB1value);
353             s.executeUpdate("update testCLOB_MAIN set clob1 = clob1 || vc1");
354             verifyStringData(s.executeQuery("select clob1 from testCLOB_MAIN"), columnCLOB1value);
355
356             System.out.println("Test1w - LONG VARCHAR and CLOB(A) concatenations will give result type of CLOB(A+32K) when A+32K<2G");
357       columnCLOB2value = columnLVC1value+columnCLOB2value;
358             dumpSomeMetaDataInfo(s.executeQuery("values(select lvc1 || clob2 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
359             verifyStringData(s.executeQuery("values(select lvc1 || clob2 from testCLOB_MAIN)"), columnCLOB2value);
360             s.executeUpdate("update testCLOB_MAIN set clob2 = lvc1 || clob2");
361             verifyStringData(s.executeQuery("select clob2 from testCLOB_MAIN"), columnCLOB2value);
362
363             System.out.println("Test1x - CLOB(A) and LONG VARCHAR concatenations will give result type of CLOB(A+32K) when A+32K<2G");
364       columnCLOB2value = columnCLOB2value+columnLVC1value;
365             dumpSomeMetaDataInfo(s.executeQuery("values(select clob2 || lvc1 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
366             verifyStringData(s.executeQuery("values(select clob2 || lvc1 from testCLOB_MAIN)"), columnCLOB2value);
367             s.executeUpdate("update testCLOB_MAIN set clob2 = clob2 || lvc1");
368             verifyStringData(s.executeQuery("select clob2 from testCLOB_MAIN"), columnCLOB2value);
369
370             System.out.println("Test1y - LONG VARCHAR and CLOB(B) concatenations will give result type of CLOB(2G) when A+32K>2G");
371       columnCLOB1value = columnLVC1value+columnCLOB1value;
372             dumpSomeMetaDataInfo(s.executeQuery("values(select lvc1 || clob1 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
373             verifyStringData(s.executeQuery("values(select lvc1 || clob1 from testCLOB_MAIN)"), columnCLOB1value);
374             s.executeUpdate("update testCLOB_MAIN set clob1 = lvc1 || clob1");
375             verifyStringData(s.executeQuery("select clob1 from testCLOB_MAIN"), columnCLOB1value);
376
377             System.out.println("Test1z - CLOB(A) and LONG VARCHAR concatenations will give result type of CLOB(2G) when A+32K>2G");
378       columnCLOB1value = columnCLOB1value+columnLVC1value;
379             dumpSomeMetaDataInfo(s.executeQuery("values(select clob1 || lvc1 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
380             verifyStringData(s.executeQuery("values(select clob1 || lvc1 from testCLOB_MAIN)"), columnCLOB1value);
381             s.executeUpdate("update testCLOB_MAIN set clob1 = clob1 || lvc1");
382             verifyStringData(s.executeQuery("select clob1 from testCLOB_MAIN"), columnCLOB1value);
383
384             System.out.println("Test11a - CLOB(A) and CLOB(B) concatenations will give result type of CLOB(A+B) when A+B<2G");
385       columnCLOB2value = columnCLOB2value+columnCLOB3value;
386             dumpSomeMetaDataInfo(s.executeQuery("values(select clob2 || clob3 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
387             verifyStringData(s.executeQuery("values(select clob2 || clob3 from testCLOB_MAIN)"), columnCLOB2value);
388             s.executeUpdate("update testCLOB_MAIN set clob2 = clob2 || clob3");
389             verifyStringData(s.executeQuery("select clob2 from testCLOB_MAIN"), columnCLOB2value);
390
391             System.out.println("Test11b - CLOB(A) and CLOB(B) concatenations will give result type of CLOB(2G) when A+B>2G");
392       columnCLOB1value = columnCLOB2value+columnCLOB1value;
393             dumpSomeMetaDataInfo(s.executeQuery("values(select clob2 || clob1 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
394             verifyStringData(s.executeQuery("values(select clob2 || clob1 from testCLOB_MAIN)"), columnCLOB1value);
395             s.executeUpdate("update testCLOB_MAIN set clob1 = clob2 || clob1");
396             verifyStringData(s.executeQuery("select clob1 from testCLOB_MAIN"), columnCLOB1value);
397
398             System.out.println("Test12 - try 2 empty string concatenation and verify that length comes back as 0 for the result");
399             dumpSomeMetaDataInfo(s.executeQuery("values('' || '')"), concatenatedSQLTypesNames[0]);
400             verifyStringData(s.executeQuery("values('' || '')"), "");
401
402             System.out.println("Test13 - Prepared statement with CLOB(A) and ? concatenations will give result type of CLOB(A+length of ?)");
403             System.out.println(" Prior to Derby-124 fix, ? parameter was getting bound to VARCHAR of length 32672 rather than CLOB.");
404             try {
405             s.executeUpdate("drop table ct");
406             } catch(Exception ex) {}
407             s.executeUpdate("create table ct (c CLOB(100K))");
408             String cData = Formatters.repeatChar("c",32700);
409             String cData1 = "aa";
410             String cConcatenatedData = cData1 + cData;
411             //Prior to fix for Derby-124, the ? was getting bound to VARCHAR with max length of 32670
412 //As a fix for this, if one of the operands of concatenation is CLOB, then the ? parameter would be bound to CLOB as well
413 System.out.println(" preapre statement with clob||?");
414             ps = conn.prepareStatement("insert into ct values (cast ('aa' as CLOB) || ?)");
415             ps.setString(1, cData);
416             ps.execute();
417             verifyStringData(s.executeQuery("select c from ct"), cConcatenatedData);
418             s.executeUpdate("delete from ct");
419             System.out.println(" Test - preapre statement with clob||cast(? to cLOB)");
420             ps = conn.prepareStatement("insert into ct values (cast ('aa' as CLOB) || cast(? as CLOB))");
421             ps.setString(1, cData);
422             ps.execute();
423             verifyStringData(s.executeQuery("select c from ct"), cConcatenatedData);
424             s.executeUpdate("delete from ct");
425
426             s.executeUpdate("drop table testCLOB_MAIN");
427             s.executeUpdate("drop table t1");
428             s.executeUpdate("drop table ct");
429             System.out.println("Test1 finished - CHAR, VARCHAR, LONGVARCHAR and CLOB concatenation tests");
430         } catch (SQLException sqle) {
431             org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
432             sqle.printStackTrace(System.out);
433         }
434     }
435
436     public static void testCharForBitDataConcatenation( Connection conn) throws Throwable
437     {
438     try {
439             System.out.println("Test2 - CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONGVARCHAR FOR BIT DATA and BLOB concatenation tests");
440             Statement s = conn.createStatement();
441             byte[] columnCBD0value = {(byte)'a'};
442             byte[] columnCBD1value;
443             byte[] columnCBD2value = new byte[40];
444             java.util.Arrays.fill(columnCBD2value, (byte)'a');
445             byte[] columnCBD3value = new byte[14];
446             java.util.Arrays.fill(columnCBD3value, (byte)'a');
447             byte[] columnVCBD1value;
448             byte[] columnVCBD2value;
449             byte[] columnVCBD3value = {(byte)'a'};
450             byte[] columnLVCBD1value;
451             byte[] columnLVCBD2value = new byte[32698];
452             java.util.Arrays.fill(columnLVCBD2value, (byte)'a');
453             byte[] tempStringBuffer;
454
455             try { //this is if we ever run the test against DB2, we want to make sure table doesn't already exist in DB2
456 s.executeUpdate("drop table t2");
457             } catch(Exception ex) {}
458             s.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)");
459             PreparedStatement ps = conn.prepareStatement("insert into t2(cbd0, cbd2, cbd3, vcbd3) values (?, ?,?,?)");
460             ps.setBytes(1, columnCBD0value);
461             ps.setBytes(2, columnCBD2value);
462             ps.setBytes(3, columnCBD3value);
463             ps.setBytes(4, columnVCBD3value);
464             ps.executeUpdate();
465             ps = conn.prepareStatement("update t2 set lvcbd2 = ?");
466             ps.setBytes(1, columnLVCBD2value);
467             ps.executeUpdate();
468
469             System.out.println("Test2a - CHAR FOR BIT DATA concatenations will give result type of CHAR FOR BIT DATA when concatenated string < 255");
470             //operands CHAR(A) FOR BIT DATA, CHAR(B) FOR BIT DATA and A+B<255 then result is CHAR(A+B) FOR BIT DATA
471 columnCBD1value = new byte[254];
472             System.arraycopy(columnCBD2value, 0, columnCBD1value, 0, columnCBD2value.length);
473             System.arraycopy(columnCBD2value, 0, columnCBD1value, 40, columnCBD2value.length);
474             System.arraycopy(columnCBD2value, 0, columnCBD1value, 80, columnCBD2value.length);
475             System.arraycopy(columnCBD2value, 0, columnCBD1value, 120, columnCBD2value.length);
476             System.arraycopy(columnCBD2value, 0, columnCBD1value, 160, columnCBD2value.length);
477             System.arraycopy(columnCBD2value, 0, columnCBD1value, 200, columnCBD2value.length);
478             System.arraycopy(columnCBD3value, 0, columnCBD1value, 240, columnCBD3value.length);
479             dumpSomeMetaDataInfo(s.executeQuery("values(select cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd3 from t2)"), concatenatedSQLTypesNames[4]);
480             verifyByteData(s.executeQuery("values(select cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd3 from t2)"), columnCBD1value);
481             s.executeUpdate("update t2 set cbd1 = cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd3");
482             verifyByteData(s.executeQuery("select cbd1 from t2"), columnCBD1value);
483
484             System.out.println("Test2b boundary test - CHAR FOR BIT DATA concatenations will give result type of VARCHAR FOR BIT DATA when concatenated string = 255");
485             //operands CHAR(A) FOR BIT DATA, CHAR(B) FOR BIT DATA and A+B>254 then result is VARCHAR(A+B) FOR BIT DATA
486 columnVCBD1value = new byte[255];
487             System.arraycopy(columnCBD1value, 0, columnVCBD1value, 0, columnCBD1value.length);
488             System.arraycopy(columnCBD0value, 0, columnVCBD1value, 254, columnCBD0value.length);
489             dumpSomeMetaDataInfo(s.executeQuery("values(select cbd1 || cbd0 from t2)"), concatenatedSQLTypesNames[5]);
490             verifyByteData(s.executeQuery("values(select cbd1 || cbd0 from t2)"), columnVCBD1value);
491             s.executeUpdate("update t2 set vcbd1 = cbd1 || cbd0");
492             verifyByteData(s.executeQuery("select vcbd1 from t2"), columnVCBD1value);
493
494             System.out.println("Test2b - CHAR FOR BIT DATA concatenations will give result type of VARCHAR FOR BIT DATA when concatenated string > 254");
495             //operands CHAR(A) FOR BIT DATA, CHAR(B) FOR BIT DATA and A+B>254 then result is VARCHAR(A+B) FOR BIT DATA
496 columnVCBD1value = new byte[264];
497             System.arraycopy(columnCBD1value, 0, columnVCBD1value, 0, columnCBD1value.length);
498             System.arraycopy(columnCBD0value, 0, columnVCBD1value, 254, columnCBD0value.length);
499             System.arraycopy(columnCBD0value, 0, columnVCBD1value, 255, columnCBD0value.length);
500             System.arraycopy(columnCBD0value, 0, columnVCBD1value, 256, columnCBD0value.length);
501             System.arraycopy(columnCBD0value, 0, columnVCBD1value, 257, columnCBD0value.length);
502             System.arraycopy(columnCBD0value, 0, columnVCBD1value, 258, columnCBD0value.length);
503             System.arraycopy(columnCBD0value, 0, columnVCBD1value, 259, columnCBD0value.length);
504             System.arraycopy(columnCBD0value, 0, columnVCBD1value, 260, columnCBD0value.length);
505             System.arraycopy(columnCBD0value, 0, columnVCBD1value, 261, columnCBD0value.length);
506             System.arraycopy(columnCBD0value, 0, columnVCBD1value, 262, columnCBD0value.length);
507             System.arraycopy(columnCBD0value, 0, columnVCBD1value, 263, columnCBD0value.length);
508             dumpSomeMetaDataInfo(s.executeQuery("values(select cbd1 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 from t2)"), concatenatedSQLTypesNames[5]);
509             verifyByteData(s.executeQuery("values(select cbd1 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 from t2)"), columnVCBD1value);
510             s.executeUpdate("update t2 set vcbd1 = cbd1 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0");
511             verifyByteData(s.executeQuery("select vcbd1 from t2"), columnVCBD1value);
512
513             System.out.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");
514             //operands CHAR(A) FOR BIT DATA, VARCHAR(B) FOR BIT DATA and A+B<4001 then result is VARCHAR(A+B) FOR BIT DATA
515 //concatenated string 4000 characters long in following updates
516 columnLVCBD1value = new byte[4000];
517             columnVCBD2value = new byte[4000];
518             System.arraycopy(columnCBD2value, 0, columnLVCBD1value, 0, columnCBD2value.length);
519             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 40, columnVCBD1value.length);
520             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 304, columnVCBD1value.length);
521             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 568, columnVCBD1value.length);
522             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 832, columnVCBD1value.length);
523             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 1096, columnVCBD1value.length);
524             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 1360, columnVCBD1value.length);
525             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 1624, columnVCBD1value.length);
526             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 1888, columnVCBD1value.length);
527             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 2152, columnVCBD1value.length);
528             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 2416, columnVCBD1value.length);
529             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 2680, columnVCBD1value.length);
530             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 2944, columnVCBD1value.length);
531             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 3208, columnVCBD1value.length);
532             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 3472, columnVCBD1value.length);
533             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 3736, columnVCBD1value.length);
534             System.arraycopy(columnLVCBD1value, 0, columnVCBD2value, 0, columnLVCBD1value.length);
535             dumpSomeMetaDataInfo(s.executeQuery("values(select cbd2||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1 from t2)"), concatenatedSQLTypesNames[5]);
536             verifyByteData(s.executeQuery("values(select cbd2||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1 from t2)"), columnVCBD2value);
537             s.executeUpdate("update t2 set vcbd2 = cbd2||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1");
538             verifyByteData(s.executeQuery("select vcbd2 from t2"), columnVCBD2value);
539             s.executeUpdate("update t2 set lvcbd1 = cbd2||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1");
540             verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);
541
542             System.out.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");
543             //operands VARCHAR(A) FOR BIT DATA, CHAR(B) FOR BIT DATA and A+B<4001 then result is VARCHAR(A+B) FOR BIT DATA
544 //concatenated string 4000 characters long in following updates
545 columnLVCBD1value = new byte[4000];
546             columnVCBD2value = new byte[4000];
547             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 0, columnVCBD1value.length);
548             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 264, columnVCBD1value.length);
549             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 528, columnVCBD1value.length);
550             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 792, columnVCBD1value.length);
551             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 1056, columnVCBD1value.length);
552             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 1320, columnVCBD1value.length);
553             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 1584, columnVCBD1value.length);
554             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 1848, columnVCBD1value.length);
555             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 2112, columnVCBD1value.length);
556             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 2376, columnVCBD1value.length);
557             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 2640, columnVCBD1value.length);
558             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 2904, columnVCBD1value.length);
559             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 3168, columnVCBD1value.length);
560             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 3432, columnVCBD1value.length);
561             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 3696, columnVCBD1value.length);
562             System.arraycopy(columnCBD2value, 0, columnLVCBD1value, 3960, columnCBD2value.length);
563             System.arraycopy(columnLVCBD1value, 0, columnVCBD2value, 0, columnLVCBD1value.length);
564             dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||cbd2 from t2)"), concatenatedSQLTypesNames[5]);
565             verifyByteData(s.executeQuery("values(select vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||cbd2 from t2)"), columnLVCBD1value);
566             s.executeUpdate("update t2 set vcbd2 = vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||cbd2");
567             verifyByteData(s.executeQuery("select vcbd2 from t2"), columnVCBD2value);
568             s.executeUpdate("update t2 set lvcbd1 = vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||cbd2");
569             verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);
570
571             System.out.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");
572             //operands CHAR(A) FOR BIT DATA, VARCHAR(B) FOR BIT DATA and A+B>4000 then result is LONG VARCHAR FOR BIT DATA
573 //concatenated string is > 4000 characters long in following 2 updates
574 columnLVCBD1value = new byte[4001];
575             System.arraycopy(columnCBD0value, 0, columnLVCBD1value, 0, columnCBD0value.length);
576             System.arraycopy(columnVCBD2value, 0, columnLVCBD1value, 1, columnVCBD2value.length);
577             dumpSomeMetaDataInfo(s.executeQuery("values(select cbd0||vcbd2 from t2)"), concatenatedSQLTypesNames[6]);
578             verifyByteData(s.executeQuery("values(select cbd0||vcbd2 from t2)"), columnLVCBD1value);
579             s.executeUpdate("update t2 set lvcbd1 = cbd0||vcbd2 ");
580             verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);
581
582             System.out.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");
583             //operands VARCHAR(A) FOR BIT DATA, CHAR(B) FOR BIT DATA and A+B>4000 then result is LONG VARCHAR FOR BIT DATA
584 columnLVCBD1value = new byte[4001];
585             System.arraycopy(columnVCBD2value, 0, columnLVCBD1value, 0, columnVCBD2value.length);
586             System.arraycopy(columnCBD0value, 0, columnLVCBD1value, 4000, columnCBD0value.length);
587             dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd2 || cbd0 from t2)"), concatenatedSQLTypesNames[6]);
588             verifyByteData(s.executeQuery("values(select vcbd2 || cbd0 from t2)"), columnLVCBD1value);
589             s.executeUpdate("update t2 set lvcbd1 = vcbd2 || cbd0");
590             verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);
591
592             System.out.println("Test2g - CHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA concatenations will give result type of LONG VARCHAR FOR BIT DATA");
593             //operands CHAR(A) FOR BIT DATA, LONG VARCHAR FOR BIT DATA then result is LONG VARCHAR FOR BIT DATA
594 byte[] tmpColumnLVCBD1value = new byte[4001];
595             System.arraycopy(columnLVCBD1value, 0, tmpColumnLVCBD1value, 0, columnLVCBD1value.length);
596             columnLVCBD1value = new byte[4002];
597             System.arraycopy(columnCBD0value, 0, columnLVCBD1value, 0, columnCBD0value.length);
598             System.arraycopy(tmpColumnLVCBD1value, 0, columnLVCBD1value, 1, tmpColumnLVCBD1value.length);
599             dumpSomeMetaDataInfo(s.executeQuery("values(select cbd0 || lvcbd1 from t2)"), concatenatedSQLTypesNames[6]);
600             verifyByteData(s.executeQuery("values(select cbd0 || lvcbd1 from t2)"), columnLVCBD1value);
601             s.executeUpdate("update t2 set lvcbd1 = cbd0 || lvcbd1");
602             verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);
603
604             System.out.println("Test2h - VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA concatenations will give result type of LONG VARCHAR FOR BIT DATA");
605             //operands VARCHAR(A) FOR BIT DATA, LONG VARCHAR FOR BIT DATA then result is LONG VARCHAR FOR BIT DATA
606 tmpColumnLVCBD1value = new byte[4002];
607             System.arraycopy(columnLVCBD1value, 0, tmpColumnLVCBD1value, 0, columnLVCBD1value.length);
608             columnLVCBD1value = new byte[4266];
609             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 0, columnVCBD1value.length);
610             System.arraycopy(tmpColumnLVCBD1value, 0, columnLVCBD1value, 264, tmpColumnLVCBD1value.length);
611             dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd1 || lvcbd1 from t2)"), concatenatedSQLTypesNames[6]);
612             verifyByteData(s.executeQuery("values(select vcbd1 || lvcbd1 from t2)"), columnLVCBD1value);
613             s.executeUpdate("update t2 set lvcbd1 = vcbd1 || lvcbd1");
614             verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);
615
616             System.out.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");
617             //operands VARCHAR(A) FOR BIT DATA, VARCHAR(B) FOR BIT DATA and A+B<4001 then result is VARCHAR(A+B) FOR BIT DATA
618 columnLVCBD1value = new byte[528];
619             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 0, columnVCBD1value.length);
620             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 264, columnVCBD1value.length);
621             dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd1 || vcbd1 from t2)"), concatenatedSQLTypesNames[5]);
622             verifyByteData(s.executeQuery("values(select vcbd1 || vcbd1 from t2)"), columnLVCBD1value);
623             s.executeUpdate("update t2 set lvcbd1 = vcbd1 || vcbd1");
624             verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);
625
626             System.out.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");
627             //operands VARCHAR(A) FOR BIT DATA, VARCHAR(B) FOR BIT DATA and A+B>4000 then result is LONG VARCHAR FOR BIT DATA
628 columnLVCBD1value = new byte[4001];
629             System.arraycopy(columnVCBD2value, 0, columnLVCBD1value, 0, columnVCBD2value.length);
630             System.arraycopy(columnVCBD3value, 0, columnLVCBD1value, 4000, columnVCBD3value.length);
631             dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd2 || vcbd3 from t2)"), concatenatedSQLTypesNames[6]);
632             verifyByteData(s.executeQuery("values(select vcbd2 || vcbd3 from t2)"), columnLVCBD1value);
633             s.executeUpdate("update t2 set lvcbd1 = vcbd2 || vcbd3");
634             verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);
635
636             System.out.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");
637             //operands VARCHAR(A) FOR BIT DATA, VARCHAR(B) FOR BIT DATA and A+B>4000 then result is LONG VARCHAR FOR BIT DATA
638 columnLVCBD1value = new byte[columnVCBD1value.length + columnVCBD2value.length];
639             System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 0, columnVCBD1value.length);
640             System.arraycopy(columnVCBD2value, 0, columnLVCBD1value, columnVCBD1value.length, columnVCBD2value.length);
641             dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd1 || vcbd2 from t2)"), concatenatedSQLTypesNames[6]);
642             verifyByteData(s.executeQuery("values(select vcbd1 || vcbd2 from t2)"), columnLVCBD1value);
643             s.executeUpdate("update t2 set lvcbd1 = vcbd1 || vcbd2");
644             verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);
645
646             System.out.println("Test2k - LONG VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA concatenations will give result type of LONG VARCHAR FOR BIT DATA");
647             //operands LONG VARCHAR FOR BIT DATA, LONG VARCHAR FOR BIT DATA then result is LONG VARCHAR FOR BIT DATA
648 tmpColumnLVCBD1value = new byte[columnLVCBD1value.length];
649             System.arraycopy(columnLVCBD1value, 0, tmpColumnLVCBD1value, 0, columnLVCBD1value.length);
650             columnLVCBD1value = new byte[tmpColumnLVCBD1value.length + tmpColumnLVCBD1value.length];
651             System.arraycopy(tmpColumnLVCBD1value, 0, columnLVCBD1value, 0, tmpColumnLVCBD1value.length);
652             System.arraycopy(tmpColumnLVCBD1value, 0, columnLVCBD1value, tmpColumnLVCBD1value.length, tmpColumnLVCBD1value.length);
653             dumpSomeMetaDataInfo(s.executeQuery("values(select lvcbd1 || lvcbd1 from t2)"), concatenatedSQLTypesNames[6]);
654             verifyByteData(s.executeQuery("values(select lvcbd1 || lvcbd1 from t2)"), columnLVCBD1value);
655             s.executeUpdate("update t2 set lvcbd1 = lvcbd1 || lvcbd1");
656             verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);
657
658             System.out.println("Test2l - CHAR FOR BIT DATA and LONGVARCHAR FOR BIT DATA concatenation resulting in concatenated string > 32700");
659             byte[] tmpColumnLVCBD2value = new byte[columnLVCBD2value.length];
660             System.arraycopy(columnLVCBD2value, 0, tmpColumnLVCBD2value, 0, columnLVCBD2value.length);
661             columnLVCBD2value = new byte[columnCBD2value.length + tmpColumnLVCBD2value.length];
662             System.arraycopy(columnCBD2value, 0, columnLVCBD2value, 0, columnCBD2value.length);
663             System.arraycopy(tmpColumnLVCBD2value, 0, columnLVCBD2value, columnCBD2value.length, tmpColumnLVCBD2value.length);
664             dumpSomeMetaDataInfo(s.executeQuery("values(select cbd2 || lvcbd2 from t2)"), concatenatedSQLTypesNames[6]);
665             verifyByteData(s.executeQuery("values(select cbd2 || lvcbd2 from t2)"), columnLVCBD2value);
666             s.executeUpdate("update t2 set lvcbd2 = cbd2 || lvcbd2");
667             verifyByteData(s.executeQuery("select lvcbd2 from t2"), columnLVCBD2value);
668
669             System.out.println("Test2m - VARCHAR FOR BIT DATA and LONGVARCHAR FOR BIT DATA concatenation resulting in concatenated string > 32700");
670             tmpColumnLVCBD2value = new byte[columnLVCBD2value.length];
671             System.arraycopy(columnLVCBD2value, 0, tmpColumnLVCBD2value, 0, columnLVCBD2value.length);
672             columnLVCBD2value = new byte[columnVCBD1value.length + tmpColumnLVCBD2value.length];
673             System.arraycopy(columnVCBD1value, 0, columnLVCBD2value, 0, columnVCBD1value.length);
674             System.arraycopy(tmpColumnLVCBD2value, 0, columnLVCBD2value, columnVCBD1value.length, tmpColumnLVCBD2value.length);
675             dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd1 || lvcbd2 from t2)"), concatenatedSQLTypesNames[6]);
676             verifyByteData(s.executeQuery("values(select vcbd1 || lvcbd2 from t2)"), columnLVCBD2value);
677             s.executeUpdate("update t2 set lvcbd2 = vcbd1 || lvcbd2");
678             verifyByteData(s.executeQuery("select lvcbd2 from t2"), columnLVCBD2value);
679
680             System.out.println("Test2n - LONGVARCHAR FOR BIT DATA and LONGVARCHAR FOR BIT DATA concatenation resulting in concatenated string > 32700");
681             tmpColumnLVCBD2value = new byte[columnLVCBD2value.length];
682             System.arraycopy(columnLVCBD2value, 0, tmpColumnLVCBD2value, 0, columnLVCBD2value.length);
683             columnLVCBD2value = new byte[columnLVCBD1value.length + tmpColumnLVCBD2value.length];
684             System.arraycopy(columnLVCBD1value, 0, columnLVCBD2value, 0, columnLVCBD1value.length);
685             System.arraycopy(tmpColumnLVCBD2value, 0, columnLVCBD2value, columnLVCBD1value.length, tmpColumnLVCBD2value.length);
686             dumpSomeMetaDataInfo(s.executeQuery("values(select lvcbd1 || lvcbd2 from t2)"), concatenatedSQLTypesNames[6]);
687             verifyByteData(s.executeQuery("values(select lvcbd1 || lvcbd2 from t2)"), columnLVCBD2value);
688             s.executeUpdate("update t2 set lvcbd2 = lvcbd1 || lvcbd2");
689             verifyByteData(s.executeQuery("select lvcbd2 from t2"), columnLVCBD2value);
690
691             try { //this is if we ever run the test against DB2, we want to make sure table doesn't already exist in DB2
692 s.executeUpdate("drop table testBLOB_MAIN");
693             } catch(Exception ex) {}
694             s.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))");
695             ps = conn.prepareStatement("insert into testBLOB_MAIN values(?,?,?,?,?,?)");
696             columnCBD1value = new byte[10];
697             java.util.Arrays.fill(columnCBD1value, (byte)'a');
698             ps.setBytes(1, columnCBD1value);
699             columnVCBD1value = new byte[10];
700             java.util.Arrays.fill(columnVCBD1value, (byte)'b');
701             ps.setBytes(2, columnVCBD1value);
702             columnLVCBD1value = new byte[10];
703             java.util.Arrays.fill(columnLVCBD1value, (byte)'c');
704             ps.setBytes(3, columnLVCBD1value);
705             byte[] columnBLOB1value = new byte[10];
706             java.util.Arrays.fill(columnBLOB1value, (byte)'d');
707             ps.setBytes(4, columnBLOB1value);
708             byte[] columnBLOB2value = new byte[10];
709             java.util.Arrays.fill(columnBLOB2value, (byte)'e');
710             ps.setBytes(5, columnBLOB2value);
711             byte[] columnBLOB3value = new byte[10];
712             java.util.Arrays.fill(columnBLOB3value, (byte)'f');
713             ps.setBytes(6, columnBLOB3value);
714             ps.executeUpdate();
715
716             System.out.println("Test2o - CHAR(A) FOR BIT DATA and BLOB(B) concatenations will give result type of BLOB(A+B) when A+B<2G");
717             byte[] tmpValue = new byte[columnBLOB2value.length];
718             System.arraycopy(columnBLOB2value, 0, tmpValue, 0, columnBLOB2value.length);
719             columnBLOB2value = new byte[columnCBD1value.length + tmpValue.length];
720             System.arraycopy(columnCBD1value, 0, columnBLOB2value, 0, columnCBD1value.length);
721             System.arraycopy(tmpValue, 0, columnBLOB2value, columnCBD1value.length, tmpValue.length);
722             dumpSomeMetaDataInfo(s.executeQuery("values(select cbd1 || blob2 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
723             verifyByteData(s.executeQuery("values(select cbd1 || blob2 from testBLOB_MAIN)"), columnBLOB2value);
724             s.executeUpdate("update testBLOB_MAIN set blob2 = cast((cbd1 || blob2) as blob(256))");
725             verifyByteData(s.executeQuery("select blob2 from testBLOB_MAIN"), columnBLOB2value);
726
727             System.out.println("Test2p - BLOB(A) and CHAR(B) FOR BIT DATA concatenations will give result type of BLOB(A+B) when A+B<2G");
728             tmpValue = new byte[columnBLOB2value.length];
729             System.arraycopy(columnBLOB2value, 0, tmpValue, 0, columnBLOB2value.length);
730             columnBLOB2value = new byte[tmpValue.length + columnCBD1value.length];
731             System.arraycopy(tmpValue, 0, columnBLOB2value, 0, tmpValue.length);
732             System.arraycopy(columnCBD1value, 0, columnBLOB2value, tmpValue.length, columnCBD1value.length);
733             dumpSomeMetaDataInfo(s.executeQuery("values(select blob2 || cbd1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
734             verifyByteData(s.executeQuery("values(select blob2 || cbd1 from testBLOB_MAIN)"), columnBLOB2value);
735             s.executeUpdate("update testBLOB_MAIN set blob2 = cast((blob2 || cbd1) as blob(256))");
736             verifyByteData(s.executeQuery("select blob2 from testBLOB_MAIN"), columnBLOB2value);
737
738             System.out.println("Test2q - CHAR(A) FOR BIT DATA and BLOB(B) concatenations will give result type of BLOB(2G) when A+B>2G");
739             tmpValue = new byte[columnBLOB1value.length];
740             System.arraycopy(columnBLOB1value, 0, tmpValue, 0, columnBLOB1value.length);
741             columnBLOB1value = new byte[columnCBD1value.length + tmpValue.length];
742             System.arraycopy(columnCBD1value, 0, columnBLOB1value, 0, columnCBD1value.length);
743             System.arraycopy(tmpValue, 0, columnBLOB1value, columnCBD1value.length, tmpValue.length);
744             dumpSomeMetaDataInfo(s.executeQuery("values(select cbd1 || blob1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
745             verifyByteData(s.executeQuery("values(select cbd1 || blob1 from testBLOB_MAIN)"), columnBLOB1value);
746             s.executeUpdate("update testBLOB_MAIN set blob1 = cast((cbd1 || blob1) as blob(2G))");
747             verifyByteData(s.executeQuery("select blob1 from testBLOB_MAIN"), columnBLOB1value);
748
749             System.out.println("Test2r - BLOB(A) and CHAR(B) FOR BIT DATA concatenations will give result type of BLOB(2G) when A+B>2G");
750             tmpValue = new byte[columnBLOB1value.length];
751             System.arraycopy(columnBLOB1value, 0, tmpValue, 0, columnBLOB1value.length);
752             columnBLOB1value = new byte[tmpValue.length + columnCBD1value.length];
753             System.arraycopy(tmpValue, 0, columnBLOB1value, 0, tmpValue.length);
754             System.arraycopy(columnCBD1value, 0, columnBLOB1value, tmpValue.length, columnCBD1value.length);
755             dumpSomeMetaDataInfo(s.executeQuery("values(select blob1 || cbd1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
756             verifyByteData(s.executeQuery("values(select blob1 || cbd1 from testBLOB_MAIN)"), columnBLOB1value);
757             s.executeUpdate("update testBLOB_MAIN set blob1 = cast((blob1 || cbd1) as blob(2G))");
758             verifyByteData(s.executeQuery("select blob1 from testBLOB_MAIN"), columnBLOB1value);
759
760             System.out.println("Test2s - VARCHAR(A) FOR BIT DATA and BLOB(B) concatenations will give result type of BLOB(A+B) when A+B<2G");
761             tmpValue = new byte[columnBLOB2value.length];
762             System.arraycopy(columnBLOB2value, 0, tmpValue, 0, columnBLOB2value.length);
763             columnBLOB2value = new byte[columnVCBD1value.length + tmpValue.length];
764             System.arraycopy(columnVCBD1value, 0, columnBLOB2value, 0, columnVCBD1value.length);
765             System.arraycopy(tmpValue, 0, columnBLOB2value, columnVCBD1value.length, tmpValue.length);
766             dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd1 || blob2 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
767             verifyByteData(s.executeQuery("values(select vcbd1 || blob2 from testBLOB_MAIN)"), columnBLOB2value);
768             s.executeUpdate("update testBLOB_MAIN set blob2 = cast((vcbd1 || blob2) as blob(256))");
769             verifyByteData(s.executeQuery("select blob2 from testBLOB_MAIN"), columnBLOB2value);
770
771             System.out.println("Test2t - BLOB(A) and VARCHAR(B) FOR BIT DATA concatenations will give result type of BLOB(A+B) when A+B<2G");
772             tmpValue = new byte[columnBLOB2value.length];
773             System.arraycopy(columnBLOB2value, 0, tmpValue, 0, columnBLOB2value.length);
774             columnBLOB2value = new byte[tmpValue.length + columnVCBD1value.length];
775             System.arraycopy(tmpValue, 0, columnBLOB2value, 0, tmpValue.length);
776             System.arraycopy(columnVCBD1value, 0, columnBLOB2value, tmpValue.length, columnVCBD1value.length);
777             dumpSomeMetaDataInfo(s.executeQuery("values(select blob2 || vcbd1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
778             verifyByteData(s.executeQuery("values(select blob2 || vcbd1 from testBLOB_MAIN)"), columnBLOB2value);
779             s.executeUpdate("update testBLOB_MAIN set blob2 = cast((blob2 || vcbd1) as blob(256))");
780             verifyByteData(s.executeQuery("select blob2 from testBLOB_MAIN"), columnBLOB2value);
781
782             System.out.println("Test2u - VARCHAR(A) FOR BIT DATA and BLOB(B) concatenations will give result type of BLOB(2G) when A+B>2G");
783             tmpValue = new byte[columnBLOB1value.length];
784             System.arraycopy(columnBLOB1value, 0, tmpValue, 0, columnBLOB1value.length);
785             columnBLOB1value = new byte[columnVCBD1value.length + tmpValue.length];
786             System.arraycopy(columnVCBD1value, 0, columnBLOB1value, 0, columnVCBD1value.length);
787             System.arraycopy(tmpValue, 0, columnBLOB1value, columnVCBD1value.length, tmpValue.length);
788             dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd1 || blob1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
789             verifyByteData(s.executeQuery("values(select vcbd1 || blob1 from testBLOB_MAIN)"), columnBLOB1value);
790             s.executeUpdate("update testBLOB_MAIN set blob1 = cast((vcbd1 || blob1) as blob(2G))");
791             verifyByteData(s.executeQuery("select blob1 from testBLOB_MAIN"), columnBLOB1value);
792
793             System.out.println("Test2v - BLOB(A) and VARCHAR(B) FOR BIT DATA concatenations will give result type of BLOB(2G) when A+B>2G");
794             tmpValue = new byte[columnBLOB1value.length];
795             System.arraycopy(columnBLOB1value, 0, tmpValue, 0, columnBLOB1value.length);
796             columnBLOB1value = new byte[tmpValue.length + columnVCBD1value.length];
797             System.arraycopy(tmpValue, 0, columnBLOB1value, 0, tmpValue.length);
798             System.arraycopy(columnVCBD1value, 0, columnBLOB1value, tmpValue.length, columnVCBD1value.length);
799             dumpSomeMetaDataInfo(s.executeQuery("values(select blob1 || vcbd1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
800             verifyByteData(s.executeQuery("values(select blob1 || vcbd1 from testBLOB_MAIN)"), columnBLOB1value);
801             s.executeUpdate("update testBLOB_MAIN set blob1 = cast((blob1 || vcbd1) as blob(2G))");
802             verifyByteData(s.executeQuery("select blob1 from testBLOB_MAIN"), columnBLOB1value);
803
804             System.out.println("Test2w - LONG VARCHAR FOR BIT DATA and BLOB(A) concatenations will give result type of BLOB(A+32K) when A+32K<2G");
805             tmpValue = new byte[columnBLOB2value.length];
806             System.arraycopy(columnBLOB2value, 0, tmpValue, 0, columnBLOB2value.length);
807             columnBLOB2value = new byte[columnLVCBD1value.length + tmpValue.length];
808             System.arraycopy(columnLVCBD1value, 0, columnBLOB2value, 0, columnLVCBD1value.length);
809             System.arraycopy(tmpValue, 0, columnBLOB2value, columnLVCBD1value.length, tmpValue.length);
810             dumpSomeMetaDataInfo(s.executeQuery("values(select lvcbd1 || blob2 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
811             verifyByteData(s.executeQuery("values(select lvcbd1 || blob2 from testBLOB_MAIN)"), columnBLOB2value);
812             s.executeUpdate("update testBLOB_MAIN set blob2 = cast((lvcbd1 || blob2) as blob(256))");
813             verifyByteData(s.executeQuery("select blob2 from testBLOB_MAIN"), columnBLOB2value);
814
815             System.out.println("Test2x - BLOB(A) and LONG VARCHAR FOR BIT DATA concatenations will give result type of BLOB(A+32K) when A+32K<2G");
816             tmpValue = new byte[columnBLOB2value.length];
817             System.arraycopy(columnBLOB2value, 0, tmpValue, 0, columnBLOB2value.length);
818             columnBLOB2value = new byte[tmpValue.length + columnLVCBD1value.length];
819             System.arraycopy(tmpValue, 0, columnBLOB2value, 0, tmpValue.length);
820             System.arraycopy(columnLVCBD1value, 0, columnBLOB2value, tmpValue.length, columnLVCBD1value.length);
821             dumpSomeMetaDataInfo(s.executeQuery("values(select blob2 || lvcbd1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
822             verifyByteData(s.executeQuery("values(select blob2 || lvcbd1 from testBLOB_MAIN)"), columnBLOB2value);
823             s.executeUpdate("update testBLOB_MAIN set blob2 = cast((blob2 || lvcbd1) as blob(256))");
824             verifyByteData(s.executeQuery("select blob2 from testBLOB_MAIN"), columnBLOB2value);
825
826             System.out.println("Test2y - LONG VARCHAR FOR BIT DATA and BLOB(B) concatenations will give result type of BLOB(2G) when A+32K>2G");
827             tmpValue = new byte[columnBLOB1value.length];
828             System.arraycopy(columnBLOB1value, 0, tmpValue, 0, columnBLOB1value.length);
829             columnBLOB1value = new byte[columnLVCBD1value.length + tmpValue.length];
830             System.arraycopy(columnLVCBD1value, 0, columnBLOB1value, 0, columnLVCBD1value.length);
831             System.arraycopy(tmpValue, 0, columnBLOB1value, columnLVCBD1value.length, tmpValue.length);
832             dumpSomeMetaDataInfo(s.executeQuery("values(select lvcbd1 || blob1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
833             verifyByteData(s.executeQuery("values(select lvcbd1 || blob1 from testBLOB_MAIN)"), columnBLOB1value);
834             s.executeUpdate("update testBLOB_MAIN set blob1 = cast((lvcbd1 || blob1) as blob(2G))");
835             verifyByteData(s.executeQuery("select blob1 from testBLOB_MAIN"), columnBLOB1value);
836
837             System.out.println("Test2z - BLOB(A) and LONG VARCHAR FOR BIT DATA concatenations will give result type of BLOB(2G) when A+32K>2G");
838             tmpValue = new byte[columnBLOB1value.length];
839             System.arraycopy(columnBLOB1value, 0, tmpValue, 0, columnBLOB1value.length);
840             columnBLOB1value = new byte[tmpValue.length + columnLVCBD1value.length];
841             System.arraycopy(tmpValue, 0, columnBLOB1value, 0, tmpValue.length);
842             System.arraycopy(columnLVCBD1value, 0, columnBLOB1value, tmpValue.length, columnLVCBD1value.length);
843             dumpSomeMetaDataInfo(s.executeQuery("values(select blob1 || lvcbd1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
844             verifyByteData(s.executeQuery("values(select blob1 || lvcbd1 from testBLOB_MAIN)"), columnBLOB1value);
845             s.executeUpdate("update testBLOB_MAIN set blob1 = cast((blob1 || lvcbd1) as blob(2G))");
846             verifyByteData(s.executeQuery("select blob1 from testBLOB_MAIN"), columnBLOB1value);
847
848             System.out.println("Test21a - BLOB(A) and BLOB(B) concatenations will give result type of BLOB(A+B) when A+B<2G");
849             tmpValue = new byte[columnBLOB2value.length];
850             System.arraycopy(columnBLOB2value, 0, tmpValue, 0, columnBLOB2value.length);
851             columnBLOB2value = new byte[tmpValue.length + columnBLOB3value.length];
852             System.arraycopy(tmpValue, 0, columnBLOB2value, 0, tmpValue.length);
853             System.arraycopy(columnBLOB3value, 0, columnBLOB2value, tmpValue.length, columnBLOB3value.length);
854             dumpSomeMetaDataInfo(s.executeQuery("values(select blob2 || blob3 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
855             verifyByteData(s.executeQuery("values(select blob2 || blob3 from testBLOB_MAIN)"), columnBLOB2value);
856             s.executeUpdate("update testBLOB_MAIN set blob2 = blob2 || blob3");
857             verifyByteData(s.executeQuery("select blob2 from testBLOB_MAIN"), columnBLOB2value);
858
859             System.out.println("Test21b - BLOB(A) and BLOB(B) concatenations will give result type of BLOB(2G) when A+B>2G");
860             tmpValue = new byte[columnBLOB1value.length];
861             System.arraycopy(columnBLOB1value, 0, tmpValue, 0, columnBLOB1value.length);
862             columnBLOB1value = new byte[columnBLOB2value.length + tmpValue.length];
863             System.arraycopy(columnBLOB2value, 0, columnBLOB1value, 0, columnBLOB2value.length);
864             System.arraycopy(tmpValue, 0, columnBLOB1value, columnBLOB2value.length, tmpValue.length);
865             dumpSomeMetaDataInfo(s.executeQuery("values(select blob2 || blob1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
866             verifyByteData(s.executeQuery("values(select blob2 || blob1 from testBLOB_MAIN)"), columnBLOB1value);
867             s.executeUpdate("update testBLOB_MAIN set blob1 = blob2 || blob1");
868             verifyByteData(s.executeQuery("select blob1 from testBLOB_MAIN"), columnBLOB1value);
869
870             System.out.println("Test22 - try 2 empty char for bit data concatenation and verify that length comes back as 0 for the result");
871             dumpSomeMetaDataInfo(s.executeQuery("values(X'' || X'')"), concatenatedSQLTypesNames[4]);
872
873             System.out.println("Test23 - Derby-124 try concatenation in a prepared statement with one operand casted to BLOB and other as ? parameter");
874             System.out.println(" Prior to Derby-124 fix, ? parameter was getting bound to VARCHAR TO BIT DATA of length 32672 rather than BLOB.");
875             System.out.println(" That caused truncation exception when ? parameter was set to > 32672 bytes");
876             try {
877             s.executeUpdate("drop table bt");
878             } catch(Exception ex) {}
879             s.executeUpdate("create table bt (b BLOB(100K))");
880             byte [] bData = new byte[32700];
881             for (int i = 0; i < bData.length; i++)
882         bData[i] = (byte)(i % 10);
883             byte [] bData1 = new byte[2];
884             bData1[0] = (byte) 0x10;
885             bData1[1] = (byte) 0x10;
886             byte [] bConcatenatedData = new byte[32702];
887             System.arraycopy(bData1, 0, bConcatenatedData, 0, bData1.length);
888             System.arraycopy(bData, 0, bConcatenatedData, bData1.length, bData.length);
889             //Prior to fix for Derby-124, the ? was getting bound to VARCHAR FOR BIT DATA with max length of 32670
890 //And when ps.setBytes() set 32700 bytes into the ? parameter, at ps.execute() time, you got following exception
891 //ERROR 22001: A truncation error was encountered trying to shrink VARCHAR () FOR BIT DATA 'XX-RESOLVE-XX' to length 32672.
892 //As a fix for this, if one of the operands of concatenation is BLOB, then the ? parameter would be bound to BLOB as well
893 System.out.println(" preapre statement with blob||?");
894             ps = conn.prepareStatement("insert into bt values (cast (x'1010' as BLOB) || ?)");
895             ps.setBytes(1, bData);
896             ps.execute();
897             verifyByteData(s.executeQuery("select b from bt"), bConcatenatedData);
898             s.executeUpdate("delete from bt");
899             System.out.println(" Test - preapre statement with blob||cast(? to BLOB)");
900             ps = conn.prepareStatement("insert into bt values (cast (x'1010' as BLOB) || cast(? as BLOB))");
901             ps.setBytes(1, bData);
902             ps.execute();
903             verifyByteData(s.executeQuery("select b from bt"), bConcatenatedData);
904
905             s.executeUpdate("drop table testBLOB_MAIN");
906             s.executeUpdate("drop table t2");
907             s.executeUpdate("drop table bt");
908             System.out.println("Test2 finished - CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONGVARCHAR FOR BIT DATA and BLOB concatenation tests");
909         } catch (SQLException sqle) {
910             org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
911             sqle.printStackTrace(System.out);
912         }
913     }
914
915     private static void verifyStringData(ResultSet rs, String expectedValue) throws SQLException
916     {
917         if (rs == null)
918         {
919             System.out.println("<NULL>");
920             return;
921         }
922
923         rs.next();
924         if (!(rs.getString(1).equals(expectedValue))) {
925             System.out.println("ERROR: expected value does not match actual value");
926             System.out.println("expected value is " + expectedValue);
927             System.out.println("what we got here is " + rs.getString(1));
928         }
929         else
930         {
931             System.out.println("Successful " + rs.getMetaData().getColumnTypeName(1) + " read of " + expectedValue.length() + " characters");
932         }
933     }
934
935     private static void verifyByteData(ResultSet rs, byte[] expectedValue) throws SQLException
936     {
937         if (rs == null)
938         {
939             System.out.println("<NULL>");
940             return;
941         }
942
943         rs.next();
944         if (!(java.util.Arrays.equals(rs.getBytes(1),expectedValue))) {
945             System.out.println("ERROR: expected value does not match actual value");
946             System.out.println("expected value is " + expectedValue);
947             System.out.println("what we got here is " + rs.getBytes(1));
948         }
949         else
950         {
951             System.out.println("Successful " + rs.getMetaData().getColumnTypeName(1) + " read of " + expectedValue.length + " bytes");
952         }
953     }
954
955     private static void dumpSomeMetaDataInfo(ResultSet s, String expectedTypeName) throws SQLException
956     {
957         if (s == null)
958         {
959             System.out.println("<NULL>");
960             return;
961         }
962
963         ResultSetMetaData rsmd = s.getMetaData();
964
965         // Get the number of columns in the result set
966 int numCols = rsmd.getColumnCount();
967
968         StringBuffer heading = new StringBuffer ("\t ");
969
970         // Display column headings
971 for (int i=1; i<=numCols; i++)
972         {
973             System.out.println("datatype of concatenated string is : "+rsmd.getColumnTypeName(i));
974             if (!(rsmd.getColumnTypeName(i).equals(expectedTypeName)))
975                 System.out.println("FAIL : expected datatype of concatenated string is : "+expectedTypeName);
976             System.out.println("precision of concatenated string is : "+rsmd.getPrecision(i));
977         }
978     }
979
980     static private void dumpSQLExceptions (SQLException se) {
981         System.out.println("FAIL -- unexpected exception: " + se.toString());
982         while (se != null) {
983             System.out.print("SQLSTATE("+se.getSQLState()+"):");
984             se = se.getNextException();
985         }
986     }
987
988 }
989

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags