001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.forbitdata
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.lang;
023:
024: import java.sql.*;
025:
026: import org.apache.derby.tools.ij;
027: import java.io.*;
028: import java.math.BigInteger;
029: import java.math.BigDecimal;
030:
031: public class forbitdata {
032:
033: static private boolean isDB2jNet;
034:
035: public static void main(String[] argv) throws Throwable {
036: try {
037:
038: ij.getPropertyArg(argv);
039: Connection conn = ij.startJBMS();
040: // waiting for meta data
041: String framework = System.getProperty("framework");
042: if (framework != null
043: && framework.toUpperCase().equals("DB2JNET"))
044: isDB2jNet = true;
045:
046: runTests(conn);
047: } catch (Throwable t) {
048: System.out.println("FAIL " + t);
049: t.printStackTrace(System.out);
050: }
051: }
052:
053: public static void runTests(Connection conn) throws Throwable {
054: try {
055: testNegative(conn);
056: testTypes(conn);
057: testValues(conn);
058: testCompare(conn);
059: testEncodedLengths(conn);
060:
061: } catch (SQLException sqle) {
062: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
063: System.out, sqle);
064: sqle.printStackTrace(System.out);
065: }
066:
067: }
068:
069: /**
070: Negative for bit data tests.
071: FBD001,FBD007 negative syntax
072: FBD005 maximum char length
073: FBD009 maximum varchar length
074: */
075: public static void testNegative(Connection conn)
076: throws SQLException {
077:
078: System.out.println("START testNegative");
079:
080: Statement s = conn.createStatement();
081:
082: //
083: statementExceptionExpected(s,
084: "CREATE TABLE FBDFAIL.T001 (C001 CHAR(255) FOR BIT DATA)");
085: statementExceptionExpected(s,
086: "CREATE TABLE FBDFAIL.T002 (C002 VARCHAR(32673) FOR BIT DATA)");
087: statementExceptionExpected(s,
088: "CREATE TABLE FBDFAIL.T003 (C003 VARCHAR FOR BIT DATA)");
089: statementExceptionExpected(s,
090: "CREATE TABLE FBDFAIL.T004 (C004 LONG VARCHAR(100) FOR BIT DATA)");
091:
092: s.close();
093: System.out.println("END testNegative");
094: }
095:
096: /**
097: FBD001,FBD007 - positive syntax
098: FBD004 - CHAR length defaults to one
099: FBD037 - create table
100: FBD006, FBD011, FBD014 - correct JDBC type
101:
102: */
103: public static void testTypes(Connection conn) throws SQLException {
104:
105: System.out.println("START testTypes");
106:
107: Statement s = conn.createStatement();
108:
109: for (int i = 1; i <= 8; i++)
110: executeDrop(s, "DROP TABLE FBDOK.T00" + i);
111:
112: // FBD037
113:
114: executeOK(s, "CREATE TABLE FBDOK.T001 (C001 CHAR FOR BIT DATA)");
115: executeOK(s,
116: "CREATE TABLE FBDOK.T002 (C002 CHAR(1) FOR BIT DATA)");
117: executeOK(s,
118: "CREATE TABLE FBDOK.T003 (C003 CHAR(10) FOR BIT DATA)");
119: executeOK(s,
120: "CREATE TABLE FBDOK.T004 (C004 CHAR(254) FOR BIT DATA)");
121: executeOK(s,
122: "CREATE TABLE FBDOK.T005 (C005 VARCHAR(1) FOR BIT DATA)");
123: executeOK(s,
124: "CREATE TABLE FBDOK.T006 (C006 VARCHAR(100) FOR BIT DATA)");
125: executeOK(s,
126: "CREATE TABLE FBDOK.T007 (C007 VARCHAR(32672) FOR BIT DATA)");
127: executeOK(s,
128: "CREATE TABLE FBDOK.T008 (C008 LONG VARCHAR FOR BIT DATA)");
129:
130: ResultSet rs = conn.getMetaData().getColumns(null, "FBDOK",
131: null, null);
132: while (rs.next()) {
133: // skip 1 catalog
134: System.out.print(rs.getString(2) + ",");
135: System.out.print(rs.getString(3) + ",");
136: System.out.print(rs.getString(4) + ",");
137: System.out.print(rs.getString(5) + ",");
138: System.out.print(rs.getString(6) + ",");
139: System.out.print(rs.getString(7) + ",");
140: // skip 8 - unused
141: System.out.print(rs.getString(9) + ",");
142: System.out.print(rs.getString(10) + ",");
143: System.out.print(rs.getString(11) + ",");
144: // skip 12 remarks
145: System.out.print(rs.getString(13) + ",");
146: // skip 14,15 unused
147: System.out.print(rs.getString(16) + ",");
148: System.out.print(rs.getString(17) + ",");
149: System.out.println(rs.getString(18));
150: }
151: rs.close();
152:
153: for (int i = 1; i <= 8; i++) {
154: try {
155: PreparedStatement ps = conn
156: .prepareStatement("SELECT * FROM FBDOK.T00" + i);
157: ResultSetMetaData rsmd = ps.getMetaData();
158: System.out.println("TABLE FBDOK.T00" + i);
159: System.out.println(" " + rsmd.getColumnName(1) + " "
160: + rsmd.getColumnTypeName(1) + " precision "
161: + rsmd.getPrecision(1));
162: ps.close();
163: } catch (SQLException sqle) {
164: showSQLE(sqle);
165: }
166:
167: }
168:
169: for (int i = 1; i <= 8; i++)
170: executeDrop(s, "DROP TABLE FBDOK.T00" + i);
171:
172: s.execute("DROP SCHEMA FBDOK RESTRICT");
173:
174: System.out.println("DATABASE META DATA.getTypeInfo()");
175: DatabaseMetaData dmd = conn.getMetaData();
176:
177: rs = dmd.getTypeInfo();
178: while (rs.next()) {
179: String name = rs.getString(1);
180: int jdbcType = rs.getInt(2);
181: switch (jdbcType) {
182: case Types.BINARY:
183: case Types.VARBINARY:
184: case Types.LONGVARBINARY:
185: break;
186: default:
187: continue;
188: }
189:
190: System.out.print(name + "(" + jdbcType + ") ");
191: System.out.print("precision " + rs.getInt(3));
192: System.out.println("");
193: }
194:
195: rs.close();
196:
197: {
198: String sql = "VALUES X'2345d45a2e44'";
199: PreparedStatement psv = conn.prepareStatement(sql);
200: ResultSetMetaData rsmd = psv.getMetaData();
201: System.out.println(sql);
202: System.out.println(" " + rsmd.getColumnName(1) + " "
203: + rsmd.getColumnTypeName(1) + " precision "
204: + rsmd.getPrecision(1));
205: }
206:
207: {
208: String sql = "VALUES X''";
209: PreparedStatement psv = conn.prepareStatement(sql);
210: ResultSetMetaData rsmd = psv.getMetaData();
211: System.out.println(sql);
212: System.out.println(" " + rsmd.getColumnName(1) + " "
213: + rsmd.getColumnTypeName(1) + " precision "
214: + rsmd.getPrecision(1));
215: }
216:
217: s.close();
218: System.out.println("END testTypes");
219: }
220:
221: public static void testCast(Connection conn) throws SQLException {
222: }
223:
224: public static void testValues(Connection conn) throws SQLException {
225:
226: System.out.println("START testValues");
227:
228: Statement s = conn.createStatement();
229: executeDrop(s, "DROP TABLE FBDVAL.T001");
230: executeDrop(s, "DROP TABLE FBDVAL.X001");
231: s
232: .execute("CREATE TABLE FBDVAL.T001(ID INT NOT NULL PRIMARY KEY, C1 CHAR(10) FOR BIT DATA, C2 VARCHAR(10) FOR BIT DATA, C3 LONG VARCHAR FOR BIT DATA, C4 BLOB(10))");
233: PreparedStatement psI = conn
234: .prepareStatement("INSERT INTO FBDVAL.T001 VALUES(?, ?, ?, ?, ?)");
235: PreparedStatement psS = conn
236: .prepareStatement("SELECT C1, C2, C3, C4, ID FROM FBDVAL.T001 WHERE ID >= ? AND ID < ? ORDER BY ID");
237:
238: System.out.println("**** NULL");
239: insertData(psI, 0, null, 10, true);
240: showData(psS, 0, null);
241:
242: System.out.println("**** 7 bytes (EMPTY)");
243: byte[] empty = new byte[7];
244: insertData(psI, 10, empty, 10, true);
245: showData(psS, 10, empty);
246:
247: // DB2
248: // CHAR -- FAIL TOO BIG
249: // VARCHAR -- FAIL TOO BIG
250: // LONG VARCHAR -- OK
251: // BLOB -- FAIL TOO BIG
252: System.out.println("**** 15 bytes (EMPTY)");
253: byte[] empty2 = new byte[15];
254: insertData(psI, 20, empty2, 10, true);
255: showData(psS, 20, empty2);
256:
257: // DB2 - ALL OK
258: System.out.println("**** 4 bytes");
259: byte[] four = new byte[4];
260: four[0] = (byte) 0x04;
261: four[1] = (byte) 0x23;
262: four[2] = (byte) 0xA2;
263: four[3] = (byte) 0xFD;
264:
265: insertData(psI, 30, four, 10, true);
266: showData(psS, 30, four);
267:
268: // DB2 - ALL OK
269: System.out.println("**** 10 bytes");
270: byte[] ten = new byte[10];
271: ten[0] = (byte) 0x0B;
272: ten[1] = (byte) 0x27;
273: ten[2] = (byte) 0xA2;
274: ten[3] = (byte) 0xFD;
275: ten[4] = (byte) 0x01;
276: ten[5] = (byte) 0x6D;
277: ten[6] = (byte) 0xE2;
278: ten[7] = (byte) 0x35;
279: ten[8] = (byte) 0x66;
280: ten[9] = (byte) 0x90;
281:
282: insertData(psI, 40, ten, 10, true);
283: showData(psS, 40, ten);
284:
285: // DB2
286: // CHAR -- FAIL TOO BIG
287: // VARCHAR -- FAIL TOO BIG
288: // LONG VARCHAR -- OK
289: // BLOB -- FAIL TOO BIG
290: System.out.println("**** 15 bytes");
291: byte[] l15 = new byte[15];
292: l15[0] = (byte) 0xEB;
293: l15[1] = (byte) 0xCA;
294: l15[2] = (byte) 0xFE;
295: l15[3] = (byte) 0xBA;
296: l15[4] = (byte) 0xBE;
297: l15[5] = (byte) 0xFE;
298: l15[6] = (byte) 0xED;
299: l15[7] = (byte) 0xFA;
300: l15[8] = (byte) 0xCE;
301: l15[9] = (byte) 0x24;
302: l15[10] = (byte) 0x78;
303: l15[11] = (byte) 0x43;
304: l15[12] = (byte) 0x92;
305: l15[13] = (byte) 0x31;
306: l15[14] = (byte) 0x6D;
307:
308: insertData(psI, 50, l15, 10, true);
309: showData(psS, 50, l15);
310:
311: // DB2 UDB LUW no truncation of spaces for VARCHAR FBD, LONG VARCHAR FBD
312: System.out.println("**** 4 spaces ");
313: byte[] space4 = new byte[4];
314: space4[0] = (byte) 0x20;
315: space4[1] = (byte) 0x20;
316: space4[2] = (byte) 0x20;
317: space4[3] = (byte) 0x20;
318: insertData(psI, 60, space4, 10, true);
319: showData(psS, 60, space4);
320:
321: // DB2 UDB LUW no truncation of spaces for VARCHAR FBD, LONG VARCHAR FBD
322: System.out.println("**** 6 data with trailing space ");
323: byte[] space6 = new byte[6];
324: space6[0] = (byte) 0xca;
325: space6[1] = (byte) 0xfe;
326: space6[2] = (byte) 0x20;
327: space6[3] = (byte) 0x20;
328: space6[4] = (byte) 0x20;
329: space6[5] = (byte) 0x20;
330: insertData(psI, 70, space6, 10, true);
331: showData(psS, 70, space6);
332:
333: // DB2
334: // CHAR -- FAIL TOO BIG
335: // VARCHAR -- FAIL TOO BIG
336: // LONG VARCHAR -- OK
337: // BLOB -- FAIL TOO BIG
338: System.out.println("**** 12 data with trailing space ");
339: byte[] space12 = new byte[12];
340: space12[0] = (byte) 0xca;
341: space12[1] = (byte) 0xfe;
342: space12[2] = (byte) 0x20;
343: space12[3] = (byte) 0x20;
344: space12[4] = (byte) 0x20;
345: space12[5] = (byte) 0x20;
346: space12[6] = (byte) 0xca;
347: space12[7] = (byte) 0xfe;
348: space12[8] = (byte) 0x20;
349: space12[9] = (byte) 0x20;
350: space12[10] = (byte) 0x20;
351: space12[11] = (byte) 0x20;
352: insertData(psI, 210, space12, 10, true);
353: showData(psS, 210, space12);
354:
355: String sql = "INSERT INTO FBDVAL.T001 VALUES(80, X'2020202020', X'2020202020', X'2020202020', null)";
356: System.out.println("**** " + sql);
357: s.executeUpdate(sql);
358: showData(psS, 80, space4);
359:
360: // With a literal the value is truncated into CHAR FBD
361: sql = "INSERT INTO FBDVAL.T001 VALUES(90, X'CAFE20202020CAFE20202020', null, null, null)";
362: System.out.println("**** " + sql);
363: s.executeUpdate(sql);
364: showData(psS, 90, space12);
365:
366: sql = "INSERT INTO FBDVAL.T001 VALUES(100, null, X'CAFE20202020CAFE20202020', null, null)";
367: System.out.println("**** " + sql);
368: s.executeUpdate(sql);
369: showData(psS, 100, space12);
370:
371: sql = "INSERT INTO FBDVAL.T001 VALUES(110, null, null, X'CAFE20202020CAFE20202020', null)";
372: System.out.println("**** " + sql);
373: s.executeUpdate(sql);
374: showData(psS, 110, space12);
375: /*
376: sql = "INSERT INTO FBDVAL.T001 VALUES(150, null, null, null, X'CAFE20202020CAFE20202020')";
377: System.out.println("**** " + sql);
378: s.executeUpdate(sql);
379: showData(psS, 150, space12);
380: */
381: // insert with non-trailing blank from literal
382: // DB2 22001 error.
383: sql = "INSERT INTO FBDVAL.T001 VALUES(120, X'CAFE20202020CAFE20202020DD', null, null, null)";
384: System.out.println("**** " + sql);
385: try {
386: s.executeUpdate(sql);
387: System.out.println("FAIL - literal too long on CHAR FBD");
388: } catch (SQLException sqle) {
389: if ("22001".equals(sqle.getSQLState()))
390: System.out.println("22001 truncation error");
391: else
392: showSQLE(sqle);
393: }
394:
395: sql = "INSERT INTO FBDVAL.T001 VALUES(130, null, X'CAFE20202020CAFE20202020DD', null, null)";
396: System.out.println("**** " + sql);
397: try {
398: s.executeUpdate(sql);
399: System.out
400: .println("FAIL - literal too long on VARCHAR FBD");
401: } catch (SQLException sqle) {
402: if ("22001".equals(sqle.getSQLState()))
403: System.out.println("22001 truncation error");
404: else
405: showSQLE(sqle);
406: }
407:
408: sql = "INSERT INTO FBDVAL.T001 VALUES(140, null, null, X'CAFE20202020CAFE20202020DD', null)";
409: System.out.println("**** " + sql);
410: s.executeUpdate(sql);
411: showData(psS, 140, space12);
412:
413: s
414: .execute("CREATE TABLE FBDVAL.X001(XID INT NOT NULL PRIMARY KEY, X1 CHAR(12) FOR BIT DATA, C2 VARCHAR(12) FOR BIT DATA, C3 LONG VARCHAR FOR BIT DATA, C4 BLOB(12))");
415:
416: sql = "INSERT INTO FBDVAL.X001 VALUES(200, X'CAFE20202020CAFE20202020', null, null, null)";
417: System.out.println("**** " + sql);
418: s.executeUpdate(sql);
419:
420: sql = "INSERT INTO FBDVAL.T001 SELECT * FROM FBDVAL.X001";
421: System.out.println("**** " + sql);
422: s.executeUpdate(sql);
423: showData(psS, 200, space12);
424:
425: System.out.println("END testValues");
426: }
427:
428: private static void insertData(PreparedStatement psI, int id,
429: byte[] original, int maxLen, boolean streamAsWell)
430: throws SQLException {
431:
432: int ol = original == null ? 0 : original.length;
433:
434: if (original == null || original.length <= maxLen) {
435: // simple case.
436: psI.setInt(1, id);
437: psI.setBytes(2, original);
438: psI.setBytes(3, original);
439: psI.setBytes(4, original);
440: psI.setBytes(5, original);
441: psI.executeUpdate();
442:
443: if (streamAsWell) {
444: psI.setInt(1, id + 1);
445: psI.setBinaryStream(2, original == null ? null
446: : new ByteArrayInputStream(original), ol);
447: psI.setBinaryStream(3, original == null ? null
448: : new ByteArrayInputStream(original), ol);
449: psI.setBinaryStream(4, original == null ? null
450: : new ByteArrayInputStream(original), ol);
451: psI.setBinaryStream(5, original == null ? null
452: : new ByteArrayInputStream(original), ol);
453: psI.executeUpdate();
454: }
455: return;
456: }
457:
458: boolean okI1;
459: boolean okI2;
460:
461: // Insert potentially out of range value one at a time into the table
462: System.out.println(" >> CHAR FOR BIT DATA");
463: try {
464: psI.setInt(1, id);
465: psI.setBytes(2, original);
466: psI.setBytes(3, null);
467: psI.setBytes(4, null);
468: psI.setBytes(5, null);
469: psI.executeUpdate();
470: okI1 = true;
471: } catch (SQLException sqle) {
472: okI1 = false;
473: if ("22001".equals(sqle.getSQLState())) {
474: System.out.println("22001 truncation error");
475: } else
476: showSQLE(sqle);
477: }
478: if (streamAsWell) {
479: try {
480: psI.setInt(1, id + 1);
481: psI.setBinaryStream(2, original == null ? null
482: : new ByteArrayInputStream(original), ol);
483: psI.executeUpdate();
484: okI2 = true;
485: } catch (SQLException sqle) {
486: okI2 = false;
487: if ("22001".equals(sqle.getSQLState())) {
488: System.out.println("22001 truncation error");
489: } else
490: showSQLE(sqle);
491: }
492:
493: if (okI1 != okI2)
494: System.out.println("FAIL - mismatched failures");
495: }
496:
497: System.out.println(" >> VARCHAR FOR BIT DATA");
498: try {
499: psI.setInt(1, id + 2);
500: psI.setBytes(2, null);
501: psI.setBytes(3, original);
502: psI.setBytes(4, null);
503: psI.setBytes(5, null);
504: psI.executeUpdate();
505: okI1 = true;
506:
507: } catch (SQLException sqle) {
508: okI1 = false;
509: if ("22001".equals(sqle.getSQLState()))
510: System.out.println("22001 truncation error");
511: else
512: showSQLE(sqle);
513: }
514: if (streamAsWell) {
515: try {
516: psI.setInt(1, id + 3);
517: psI.setBinaryStream(3, original == null ? null
518: : new ByteArrayInputStream(original), ol);
519: psI.executeUpdate();
520: okI2 = true;
521:
522: } catch (SQLException sqle) {
523: okI2 = false;
524: if ("22001".equals(sqle.getSQLState()))
525: System.out.println("22001 truncation error");
526: else
527: showSQLE(sqle);
528: }
529: if (okI1 != okI2)
530: System.out.println("FAIL - mismatched failures");
531: }
532:
533: System.out.println(" >> LONG VARCHAR FOR BIT DATA");
534: try {
535: psI.setInt(1, id + 4);
536: psI.setBytes(2, null);
537: psI.setBytes(3, null);
538: psI.setBytes(4, original);
539: psI.setBytes(5, null);
540: psI.executeUpdate();
541: okI1 = true;
542: } catch (SQLException sqle) {
543: okI1 = false;
544: if ("22001".equals(sqle.getSQLState()))
545: System.out.println("22001 truncation error");
546: else
547: showSQLE(sqle);
548: }
549:
550: if (streamAsWell) {
551: try {
552: psI.setInt(1, id + 5);
553: psI.setBinaryStream(4, original == null ? null
554: : new ByteArrayInputStream(original), ol);
555: psI.executeUpdate();
556: okI2 = true;
557: } catch (SQLException sqle) {
558: okI2 = false;
559: if ("22001".equals(sqle.getSQLState()))
560: System.out.println("22001 truncation error");
561: else
562: showSQLE(sqle);
563: }
564: if (okI1 != okI2)
565: System.out.println("FAIL - mismatched failures");
566: }
567:
568: System.out.println(" >> BLOB");
569: try {
570: psI.setInt(1, id + 6);
571: psI.setBytes(2, null);
572: psI.setBytes(3, null);
573: psI.setBytes(4, null);
574: psI.setBytes(5, original);
575: okI1 = true;
576: psI.executeUpdate();
577: } catch (SQLException sqle) {
578: okI1 = false;
579: if ("22001".equals(sqle.getSQLState()))
580: System.out.println("22001 truncation error");
581: else
582: showSQLE(sqle);
583: }
584: if (streamAsWell) {
585: try {
586: psI.setInt(1, id + 7);
587: psI.setBinaryStream(5, original == null ? null
588: : new ByteArrayInputStream(original), ol);
589: psI.executeUpdate();
590: okI2 = true;
591: } catch (SQLException sqle) {
592: okI2 = false;
593: if ("22001".equals(sqle.getSQLState()))
594: System.out.println("22001 truncation error");
595: else
596: showSQLE(sqle);
597: }
598: if (okI1 != okI2)
599: System.out.println("FAIL - mismatched failures");
600: }
601: }
602:
603: public static void testCompare(Connection conn) throws SQLException {
604:
605: System.out.println("START testCompare");
606:
607: Statement s = conn.createStatement();
608: executeDrop(s, "DROP TABLE FBDVAL.T001");
609: executeDrop(s, "DROP TABLE FBDVAL.T002");
610: s
611: .execute("CREATE TABLE FBDVAL.T001(ID INT NOT NULL PRIMARY KEY, C1 CHAR(10) FOR BIT DATA, C2 VARCHAR(10) FOR BIT DATA, C3 LONG VARCHAR FOR BIT DATA, C4 BLOB(10))");
612: s
613: .execute("CREATE TABLE FBDVAL.T002(ID INT NOT NULL PRIMARY KEY, C1 CHAR(10) FOR BIT DATA, C2 VARCHAR(10) FOR BIT DATA, C3 LONG VARCHAR FOR BIT DATA, C4 BLOB(10))");
614: PreparedStatement psI = conn
615: .prepareStatement("INSERT INTO FBDVAL.T001 VALUES(?, ?, ?, ?, ?)");
616: PreparedStatement psI2 = conn
617: .prepareStatement("INSERT INTO FBDVAL.T002 VALUES(?, ?, ?, ?, ?)");
618:
619: insertData(psI, 0, null, 10, false);
620: insertData(psI2, 0, null, 10, false);
621:
622: byte[] four = new byte[4];
623: four[0] = (byte) 0x04;
624: four[1] = (byte) 0x23;
625: four[2] = (byte) 0xA2;
626: four[3] = (byte) 0xFD;
627:
628: insertData(psI, 30, four, 10, false);
629: insertData(psI2, 30, four, 10, false);
630: four[2] = (byte) 0xA1;
631: insertData(psI, 40, four, 10, false);
632: insertData(psI2, 40, four, 10, false);
633: four[2] = (byte) 0xA2;
634: four[3] = (byte) 0xFF;
635: insertData(psI, 50, four, 10, false);
636: insertData(psI2, 50, four, 10, false);
637:
638: byte[] four_plus_space = new byte[5];
639: four_plus_space[0] = (byte) 0x04;
640: four_plus_space[1] = (byte) 0x23;
641: four_plus_space[2] = (byte) 0xA2;
642: four_plus_space[3] = (byte) 0xFD;
643: four_plus_space[4] = (byte) 0x20;
644: insertData(psI, 60, four_plus_space, 10, false);
645: insertData(psI2, 60, four_plus_space, 10, false);
646:
647: byte[] ten = new byte[10];
648: ten[0] = (byte) 0x0B;
649: ten[1] = (byte) 0x27;
650: ten[2] = (byte) 0xA2;
651: ten[3] = (byte) 0xFD;
652: ten[4] = (byte) 0x01;
653: ten[5] = (byte) 0x6D;
654: ten[6] = (byte) 0xE2;
655: ten[7] = (byte) 0x35;
656: ten[8] = (byte) 0x66;
657: ten[9] = (byte) 0x90;
658:
659: insertData(psI, 70, ten, 10, false);
660: insertData(psI2, 70, ten, 10, false);
661:
662: String[] COLS = { "C1", "C2", "C3", "C4" };
663: String[] OPS = { "=", "<>", "<", "<=", ">", ">=" };
664:
665: for (int t = 0; t < COLS.length; t++) {
666: for (int o = 0; o < COLS.length; o++) {
667: for (int a = 0; a < OPS.length; a++) {
668:
669: String sql = "SELECT T.ID, T."
670: + COLS[t]
671: + ", O.ID, O."
672: + COLS[o]
673: + " FROM FBDVAL.T001 O, FBDVAL.T002 T WHERE T."
674: + COLS[t] + " " + OPS[a] + " O." + COLS[o]
675: + " ORDER BY 1,3";
676:
677: System.out.println(sql);
678: try {
679: PreparedStatement psS = conn
680: .prepareStatement(sql);
681: showCompareData(psS);
682: } catch (SQLException sqle) {
683: if ("42818".equals(sqle.getSQLState()))
684: System.out
685: .println("42818 types not comparable "
686: + COLS[t]
687: + " ... "
688: + COLS[o]);
689: else
690: showSQLE(sqle);
691: }
692: conn.commit();
693: }
694: }
695: }
696: System.out.println("END testCompare");
697: }
698:
699: /**
700: The length of a binary type is encoded when stored, this
701: test makes sure all the code paths are tested.
702: The encoded length is hidden from the JDBC client.
703: */
704: public static void testEncodedLengths(Connection conn)
705: throws SQLException, IOException {
706:
707: System.out.println("START testEncodedLengths");
708:
709: Statement s = conn.createStatement();
710: executeDrop(s, "DROP TABLE FBDVAL.TEL");
711: s
712: .execute("CREATE TABLE FBDVAL.TEL(C2 VARCHAR(32672) FOR BIT DATA, C3 LONG VARCHAR FOR BIT DATA, C4 BLOB(128k))");
713:
714: PreparedStatement psi = conn
715: .prepareStatement("INSERT INTO FBDVAL.TEL VALUES(?, ?, ?)");
716: PreparedStatement pss = conn
717: .prepareStatement("SELECT * FROM FBDVAL.TEL");
718: PreparedStatement psd = conn
719: .prepareStatement("DELETE FROM FBDVAL.TEL");
720:
721: //insertEL(psi, pss, psd, 0);
722: insertEL(psi, pss, psd, 10);
723: insertEL(psi, pss, psd, 30);
724: insertEL(psi, pss, psd, 31);
725: insertEL(psi, pss, psd, 32); // switch to 2 byte length
726: insertEL(psi, pss, psd, 1345);
727: insertEL(psi, pss, psd, 23456);
728: insertEL(psi, pss, psd, 32672);
729: insertEL(psi, pss, psd, 32700);
730: insertEL(psi, pss, psd, (32 * 1024) - 1);
731: insertEL(psi, pss, psd, (32 * 1024));
732: insertEL(psi, pss, psd, (32 * 1024) + 1);
733: insertEL(psi, pss, psd, (64 * 1024) - 1);
734: insertEL(psi, pss, psd, (64 * 1024)); // switch to 4 byte length
735: insertEL(psi, pss, psd, (64 * 1024) + 1);
736: insertEL(psi, pss, psd, (110 * 1024) + 3242);
737:
738: psi.close();
739: pss.close();
740: psd.close();
741: executeDrop(s, "DROP TABLE FBDVAL.TEL");
742: s.close();
743: System.out.println("END testEncodedLengths");
744:
745: }
746:
747: private static void insertEL(PreparedStatement psi,
748: PreparedStatement pss, PreparedStatement psd, int length)
749: throws SQLException, IOException {
750:
751: Connection conn = psi.getConnection();
752: byte[] data = new byte[length];
753:
754: // random simple value check
755: int off = (int) (System.currentTimeMillis() % ((long) length));
756: data[off] = 0x23;
757:
758: psi.setBytes(1, (length <= 32672) ? data : null);
759: psi.setBytes(2, (length <= 32700) ? data : null);
760: psi.setBinaryStream(3, new java.io.ByteArrayInputStream(data),
761: length); // BLOB column
762: psi.executeUpdate();
763: conn.commit();
764:
765: selectData(pss, data, off, length);
766:
767: conn.commit();
768:
769: psd.executeUpdate();
770: conn.commit();
771:
772: // Set values using stream and then verify that select is successful
773: psi.setBinaryStream(1,
774: (length <= 32672) ? new java.io.ByteArrayInputStream(
775: data) : null, length);
776: psi.setBinaryStream(2,
777: (length <= 32700) ? new java.io.ByteArrayInputStream(
778: data) : null, length);
779: psi.setBinaryStream(3, new java.io.ByteArrayInputStream(data),
780: length); // BLOB column
781: psi.executeUpdate();
782: conn.commit();
783:
784: selectData(pss, data, off, length);
785:
786: conn.commit();
787:
788: psd.executeUpdate();
789: conn.commit();
790:
791: }
792:
793: private static void selectData(PreparedStatement pss, byte[] data,
794: int off, int length) throws SQLException, IOException {
795:
796: ResultSet rs = pss.executeQuery();
797: while (rs.next()) {
798: System.out.print(" EL byte[] " + length);
799: byte[] v = rs.getBytes(1);
800: if (v != null) {
801: System.out.print(" C1 "
802: + ((v.length == length) ? "OK" : ("FAIL <"
803: + v.length + ">")));
804: System.out.print(" DATA "
805: + ((v[off] == 0x23) ? "OK" : ("FAIL " + off)));
806: } else
807: System.out.print(" C1 NULL");
808:
809: v = rs.getBytes(2);
810: if (v != null) {
811: System.out.print(" C2 "
812: + ((v.length == length) ? "OK" : ("FAIL <"
813: + v.length + ">")));
814: System.out.print(" DATA "
815: + ((v[off] == 0x23) ? "OK" : ("FAIL " + off)));
816: } else
817: System.out.print(" C2 NULL");
818: InputStream c3 = rs.getBinaryStream(3);
819: checkEncodedLengthValue("C3", c3, length, off);
820:
821: System.out.println("");
822: }
823: rs.close();
824:
825: rs = pss.executeQuery();
826: while (rs.next()) {
827: System.out.print(" EL stream " + length);
828:
829: checkEncodedLengthValue("C1", rs.getBinaryStream(1),
830: length, off);
831: checkEncodedLengthValue("C2", rs.getBinaryStream(2),
832: length, off);
833: checkEncodedLengthValue("C3", rs.getBinaryStream(3),
834: length, off);
835:
836: System.out.println("");
837: }
838: rs.close();
839:
840: }
841:
842: private static void checkEncodedLengthValue(String col,
843: InputStream is, int length, int off) throws IOException {
844:
845: if (is == null) {
846: System.out.print(" " + col + " NULL");
847: return;
848: }
849: byte[] buf = new byte[3213];
850: boolean dataOK = false;
851: int sl = 0;
852: for (;;) {
853: int r = is.read(buf);
854: if (r < 0)
855: break;
856:
857: if ((off >= sl) && (off < (sl + r))) {
858: if (buf[off - sl] == 0x23)
859: dataOK = true;
860: }
861: sl += r;
862: }
863: System.out.print(" " + col + " "
864: + ((sl == length) ? "OK" : ("FAIL <" + sl + ">")));
865: System.out.print(" DATA " + (dataOK ? "OK" : ("FAIL " + off)));
866: }
867:
868: private static void showData(PreparedStatement psS, int id,
869: byte[] original) throws SQLException {
870: psS.setInt(1, id);
871: psS.setInt(2, id + 10);
872: ResultSet rs = psS.executeQuery();
873: while (rs.next()) {
874:
875: System.out.print(" ORG ");
876: System.out.print(showData(original));
877: System.out.print("CHR ");
878: System.out.print(showData(rs.getBytes(1)));
879: System.out.print("VAR ");
880: System.out.print(showData(rs.getBytes(2)));
881: System.out.print("LVC ");
882: System.out.print(showData(rs.getBytes(3)));
883: System.out.print("BLOB ");
884: System.out.print(showData(rs.getBytes(4)));
885:
886: System.out.println("");
887: }
888: rs.close();
889:
890: }
891:
892: private static void showCompareData(PreparedStatement psS)
893: throws SQLException {
894: ResultSet rs = psS.executeQuery();
895: while (rs.next()) {
896: System.out.print(" " + rs.getInt(1) + " ");
897: System.out.print(showData(rs.getBytes(2)));
898: System.out.print(" " + rs.getInt(3) + " ");
899: System.out.println(showData(rs.getBytes(4)));
900: }
901: rs.close();
902: psS.close();
903: }
904:
905: private static String showData(byte[] data) {
906: if (data == null)
907: return "<NULL> ";
908:
909: StringBuffer sb = new StringBuffer();
910: for (int i = 0; i < data.length; i++) {
911: String s = Integer.toHexString(data[i] & 0xff);
912: if (s.length() == 1)
913: sb.append('0');
914: sb.append(s);
915: }
916:
917: sb.append(' ');
918: sb.append('(');
919: sb.append(data.length);
920: sb.append(')');
921: sb.append(' ');
922:
923: return sb.toString();
924: }
925:
926: private static void showSQLE(SQLException sqle) {
927: do {
928: System.out.println(sqle.getSQLState() + ": "
929: + sqle.getMessage());
930: //sqle.printStackTrace(System.out);
931: sqle = sqle.getNextException();
932: } while (sqle != null);
933: }
934:
935: private static void executeDrop(Statement s, String sql) {
936: try {
937: s.execute(sql);
938: } catch (SQLException sqle) {
939: }
940: }
941:
942: private static void executeOK(Statement s, String sql) {
943: System.out.println(sql);
944: try {
945: s.execute(sql);
946: } catch (SQLException sqle) {
947: System.out.println("FAIL ");
948: showSQLE(sqle);
949: }
950: }
951:
952: private static void statementExceptionExpected(Statement s,
953: String sql) {
954: System.out.println(sql);
955: try {
956: s.execute(sql);
957: System.out
958: .println("FAIL - SQL expected to throw exception");
959: } catch (SQLException sqle) {
960: expectedException(sqle);
961: }
962: }
963:
964: private static void expectedException(SQLException sqle) {
965: String sqlState = sqle.getSQLState();
966: if (sqlState == null) {
967: sqlState = "<NULL>";
968: }
969: System.out.println("EXPECTED SQL Exception: (" + sqlState
970: + ") " + sqle.getMessage());
971: }
972:
973: }
|