001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.casting
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 org.apache.derby.tools.ij;
025: import org.apache.derbyTesting.functionTests.util.TestUtil;
026: import org.apache.derby.tools.JDBCDisplayUtil;
027:
028: import java.sql.*;
029: import java.math.*;
030: import java.io.*;
031:
032: public class casting {
033:
034: public static String VALID_DATE_STRING = "'2000-01-01'";
035: public static String VALID_TIME_STRING = "'15:30:20'";
036: public static String VALID_TIMESTAMP_STRING = "'2000-01-01 15:30:20'";
037: public static String NULL_VALUE = "NULL";
038:
039: public static String ILLEGAL_CAST_EXCEPTION_SQLSTATE = "42846";
040: public static String LANG_NOT_STORABLE_SQLSTATE = "42821";
041: public static String LANG_NOT_COMPARABLE_SQLSTATE = "42818";
042: public static String METHOD_NOT_FOUND_SQLSTATE = "42884";
043: public static String LANG_FORMAT_EXCEPTION_SQLSTATE = "22018";
044:
045: public static int SQLTYPE_ARRAY_SIZE = 17;
046: public static int SMALLINT_OFFSET = 0;
047: public static int INTEGER_OFFSET = 1;
048: public static int BIGINT_OFFSET = 2;
049: public static int DECIMAL_OFFSET = 3;
050: public static int REAL_OFFSET = 4;
051: public static int DOUBLE_OFFSET = 5;
052: public static int CHAR_OFFSET = 6;
053: public static int VARCHAR_OFFSET = 7;
054: public static int LONGVARCHAR_OFFSET = 8;
055: public static int CHAR_FOR_BIT_OFFSET = 9;
056: public static int VARCHAR_FOR_BIT_OFFSET = 10;
057: public static int LONGVARCHAR_FOR_BIT_OFFSET = 11;
058: public static int CLOB_OFFSET = 12;
059: public static int DATE_OFFSET = 13;
060: public static int TIME_OFFSET = 14;
061: public static int TIMESTAMP_OFFSET = 15;
062: public static int BLOB_OFFSET = 16;
063:
064: public static String[] SQLTypes = { "SMALLINT", "INTEGER",
065: "BIGINT", "DECIMAL(10,5)", "REAL", "DOUBLE", "CHAR(60)",
066: "VARCHAR(60)", "LONG VARCHAR", "CHAR(60) FOR BIT DATA",
067: "VARCHAR(60) FOR BIT DATA", "LONG VARCHAR FOR BIT DATA",
068: "CLOB(1k)", "DATE", "TIME", "TIMESTAMP", "BLOB(1k)", };
069:
070: public static int NULL_DATA_OFFSET = 0; // offset of NULL value
071: public static int VALID_DATA_OFFSET = 1; // offset of NULL value
072:
073: // rows are data types.
074: // data is NULL_VALUE, VALID_VALUE
075: // Should add Minimum, Maximum and out of range.
076: public static String[][] SQLData = { { NULL_VALUE, "0" }, // SMALLINT
077: { NULL_VALUE, "11" }, // INTEGER
078: { NULL_VALUE, "22" }, // BIGINT
079: { NULL_VALUE, "3.3" }, // DECIMAL(10,5)
080: { NULL_VALUE, "4.4" }, // REAL,
081: { NULL_VALUE, "5.5" }, // DOUBLE
082: { NULL_VALUE, "'7'" }, // CHAR(60)
083: { NULL_VALUE, "'8'" }, //VARCHAR(60)",
084: { NULL_VALUE, "'9'" }, // LONG VARCHAR
085: { NULL_VALUE, "X'10aa'" }, // CHAR(60) FOR BIT DATA
086: { NULL_VALUE, "X'10bb'" }, // VARCHAR(60) FOR BIT DATA
087: { NULL_VALUE, "X'10cc'" }, //LONG VARCHAR FOR BIT DATA
088: { NULL_VALUE, "'13'" }, //CLOB(1k)
089: { NULL_VALUE, VALID_DATE_STRING }, // DATE
090: { NULL_VALUE, VALID_TIME_STRING }, // TIME
091: { NULL_VALUE, VALID_TIMESTAMP_STRING }, // TIMESTAMP
092: { NULL_VALUE, "X'01dd'" } // BLOB
093: };
094:
095: public static final boolean _ = false;
096: public static final boolean X = true;
097:
098: /**
099: DB2 Table 146 - Supported explicit casts between Built-in DataTypes
100:
101: This table has THE FOR BIT DATA TYPES broken out into separate columns
102: for clarity and testing
103: **/
104:
105: public static final boolean[][] T_146 = {
106:
107: // Types. S I B D R D C V L C V L C D T T B
108: // M N I E E O H A O H A O L A I I L
109: // A T G C A U A R N A R N O T M M O
110: // L E I I L B R C G R C G B E E E B
111: // L G N M L H V . H V S
112: // I E T A E A A B . A T
113: // N R L R R I B R A
114: // T C T I . M
115: // H T B P
116: // A I
117: // R T
118: /* 0 SMALLINT */{ X, X, X, X, X, X, X, _, _, _, _, _, _,
119: _, _, _, _ },
120: /* 1 INTEGER */{ X, X, X, X, X, X, X, _, _, _, _, _, _,
121: _, _, _, _ },
122: /* 2 BIGINT */{ X, X, X, X, X, X, X, _, _, _, _, _, _,
123: _, _, _, _ },
124: /* 3 DECIMAL */{ X, X, X, X, X, X, X, _, _, _, _, _, _,
125: _, _, _, _ },
126: /* 4 REAL */{ X, X, X, X, X, X, _, _, _, _, _, _, _,
127: _, _, _, _ },
128: /* 5 DOUBLE */{ X, X, X, X, X, X, _, _, _, _, _, _, _,
129: _, _, _, _ },
130: /* 6 CHAR */{ X, X, X, X, _, _, X, X, X, _, _, _, X,
131: X, X, X, _ },
132: /* 7 VARCHAR */{ X, X, X, X, _, _, X, X, X, _, _, _, X,
133: X, X, X, _ },
134: /* 8 LONGVARCHAR */{ _, _, _, _, _, _, X, X, X, _, _, _,
135: X, _, _, _, _ },
136: /* 9 CHAR FOR BIT */{ _, _, _, _, _, _, _, _, _, X, X, X,
137: _, _, _, _, X },
138: /* 10 VARCH. BIT */{ _, _, _, _, _, _, _, _, _, X, X, X,
139: _, _, _, _, X },
140: /* 11 LONGVAR. BIT */{ _, _, _, _, _, _, _, _, _, X, X, X,
141: _, _, _, _, X },
142: /* 12 CLOB */{ _, _, _, _, _, _, X, X, X, _, _, _,
143: X, _, _, _, _ },
144: /* 13 DATE */{ _, _, _, _, _, _, X, X, _, _, _, _,
145: _, X, _, _, _ },
146: /* 14 TIME */{ _, _, _, _, _, _, X, X, _, _, _, _,
147: _, _, X, _, _ },
148: /* 15 TIMESTAMP */{ _, _, _, _, _, _, X, X, _, _, _, _,
149: _, X, X, X, _ },
150: /* 16 BLOB */{ _, _, _, _, _, _, _, _, _, _, _, _,
151: _, _, _, _, X },
152:
153: };
154:
155: /**
156: * DB2 uses Table 147 to describe Data Type Compatibility for Assignments
157: *
158: * The table 147a covers the assignments as they do differ somewhat
159: * from comparisons which can be found in 147b
160:
161: * This table has DATA TYPES for operands rather than lumping types
162: * together. Here is the mapping from DB2 Table 147 to this table.
163: * Binary Integer = SMALLINT, INTEGER, BIGINT
164: * Decimal Number = DECIMAL/(NUMERIC)
165: * Floating Point = REAL, DOUBLE/(FLOAT)
166: * Character String = CHAR, VARCHAR, LONGVARCHAR
167: * Binary String = CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONG VARCHAR FOR
168: * BIT DATA
169: * Graphic String = not suppported
170: * Date = DATE
171: * Time = TIME
172: * TimeStamp = TIMESTAMP
173: * Binary String = literal hexadecimal, CHAR FOR BIT DATA, VARCHAR FOR BIT
174: * DATA, LONG VARCHAR FOR BIT
175: * CLOB and BLOB are not covered in Table 147 but are included here
176: * for clarity and testing
177: **/
178:
179: public static final boolean[][] T_147a = {
180:
181: // Types. S I B D R D C V L C V L C D T T B
182: // M N I E E O H A O H A O L A I I L
183: // A T G C A U A R N A R N O T M M O
184: // L E I I L B R C G R C G B E E E B
185: // L G N M L H V . H V S
186: // I E T A E A A B . A T
187: // N R L R R I B R A
188: // T C T I . M
189: // H T B P
190: // A I
191: // R T
192: /* 0 SMALLINT */{ X, X, X, X, X, X, _, _, _, _, _, _, _,
193: _, _, _, _ },
194: /* 1 INTEGER */{ X, X, X, X, X, X, _, _, _, _, _, _, _,
195: _, _, _, _ },
196: /* 2 BIGINT */{ X, X, X, X, X, X, _, _, _, _, _, _, _,
197: _, _, _, _ },
198: /* 3 DECIMAL */{ X, X, X, X, X, X, _, _, _, _, _, _, _,
199: _, _, _, _ },
200: /* 4 REAL */{ X, X, X, X, X, X, _, _, _, _, _, _, _,
201: _, _, _, _ },
202: /* 5 DOUBLE */{ X, X, X, X, X, X, _, _, _, _, _, _, _,
203: _, _, _, _ },
204: /* 6 CHAR */{ _, _, _, _, _, _, X, X, X, _, _, _, X,
205: X, X, X, _ },
206: /* 7 VARCHAR */{ _, _, _, _, _, _, X, X, X, _, _, _, X,
207: X, X, X, _ },
208: /* 8 LONGVARCHAR */{ _, _, _, _, _, _, X, X, X, _, _, _,
209: X, _, _, _, _ },
210: /* 9 CHAR FOR BIT */{ _, _, _, _, _, _, _, _, _, X, X, X,
211: _, _, _, _, _ },
212: /* 10 VARCH. BIT */{ _, _, _, _, _, _, _, _, _, X, X, X,
213: _, _, _, _, _ },
214: /* 11 LONGVAR. BIT */{ _, _, _, _, _, _, _, _, _, X, X, X,
215: _, _, _, _, _ },
216: /* 12 CLOB */{ _, _, _, _, _, _, X, X, X, _, _, _,
217: X, _, _, _, _ },
218: /* 13 DATE */{ _, _, _, _, _, _, X, X, _, _, _, _,
219: _, X, _, _, _ },
220: /* 14 TIME */{ _, _, _, _, _, _, X, X, _, _, _, _,
221: _, _, X, _, _ },
222: /* 15 TIMESTAMP */{ _, _, _, _, _, _, X, X, _, _, _, _,
223: _, _, _, X, _ },
224: /* 16 BLOB */{ _, _, _, _, _, _, _, _, _, _, _, _,
225: _, _, _, _, X },
226:
227: };
228:
229: // Comparisons table
230: // Comparison's are different than assignments because
231: // Long types cannot be compared.
232: public static final boolean[][] T_147b = {
233:
234: // Types. S I B D R D C V L C V L C D T T B
235: // M N I E E O H A O H A O L A I I L
236: // A T G C A U A R N A R N O T M M O
237: // L E I I L B R C G R C G B E E E B
238: // L G N M L H V . H V S
239: // I E T A E A A B . A T
240: // N R L R R I B R A
241: // T C T I . M
242: // H T B P
243: // A I
244: // R T
245: /* 0 SMALLINT */{ X, X, X, X, X, X, _, _, _, _, _, _, _,
246: _, _, _, _ },
247: /* 1 INTEGER */{ X, X, X, X, X, X, _, _, _, _, _, _, _,
248: _, _, _, _ },
249: /* 2 BIGINT */{ X, X, X, X, X, X, _, _, _, _, _, _, _,
250: _, _, _, _ },
251: /* 3 DECIMAL */{ X, X, X, X, X, X, _, _, _, _, _, _, _,
252: _, _, _, _ },
253: /* 4 REAL */{ X, X, X, X, X, X, _, _, _, _, _, _, _,
254: _, _, _, _ },
255: /* 5 DOUBLE */{ X, X, X, X, X, X, _, _, _, _, _, _, _,
256: _, _, _, _ },
257: /* 6 CHAR */{ _, _, _, _, _, _, X, X, _, _, _, _, _,
258: X, X, X, _ },
259: /* 7 VARCHAR */{ _, _, _, _, _, _, X, X, _, _, _, _, _,
260: X, X, X, _ },
261: /* 8 LONGVARCHAR */{ _, _, _, _, _, _, _, _, _, _, _, _,
262: _, _, _, _, _ },
263: /* 9 CHAR FOR BIT */{ _, _, _, _, _, _, _, _, _, X, X, _,
264: _, _, _, _, _ },
265: /* 10 VARCH. BIT */{ _, _, _, _, _, _, _, _, _, X, X, _,
266: _, _, _, _, _ },
267: /* 11 LONGVAR. BIT */{ _, _, _, _, _, _, _, _, _, _, _, _,
268: _, _, _, _, _ },
269: /* 12 CLOB */{ _, _, _, _, _, _, _, _, _, _, _, _,
270: _, _, _, _, _ },
271: /* 13 DATE */{ _, _, _, _, _, _, X, X, _, _, _, _,
272: _, X, _, _, _ },
273: /* 14 TIME */{ _, _, _, _, _, _, X, X, _, _, _, _,
274: _, _, X, _, _ },
275: /* 15 TIMESTAMP */{ _, _, _, _, _, _, X, X, _, _, _, _,
276: _, _, _, X, _ },
277: /* 16 BLOB */{ _, _, _, _, _, _, _, _, _, _, _, _,
278: _, _, _, _, _ },
279:
280: };
281:
282: public static boolean isDerbyNet;
283: public static boolean isDB2;
284: //testNum just print and increments with each query display
285: public static int testNum = 1;
286:
287: public static void main(String[] args) throws Exception {
288: String framework = System.getProperty("framework");
289: if (framework != null
290: && framework.toUpperCase().equals("DB2JCC"))
291: isDB2 = true;
292:
293: isDerbyNet = TestUtil.isNetFramework();
294: try {
295: // use the ij utility to read the property file and
296: // make the initial connection.
297: ij.getPropertyArg(args);
298: Connection conn = ij.startJBMS();
299: conn.setAutoCommit(false);
300: createTables(conn);
301: testAssignments(conn);
302: testExplicitCasts(conn);
303: testComparisons(conn);
304: conn.close();
305: } catch (SQLException sqle) {
306: unexpectedException(sqle);
307: } catch (Throwable t) {
308: t.printStackTrace(System.out);
309: }
310: }
311:
312: public static void testExplicitCasts(Connection conn)
313: throws SQLException, Throwable {
314:
315: System.out.println("**testExplicitCasts starting");
316:
317: ResultSet rs = null;
318:
319: // Try Casts from each type to the
320: for (int sourceType = 0; sourceType < SQLTypes.length; sourceType++) {
321:
322: String sourceTypeName = SQLTypes[sourceType];
323: for (int dataOffset = 0; dataOffset < SQLData[0].length; dataOffset++)
324: for (int targetType = 0; targetType < SQLTypes.length; targetType++) {
325: try {
326: String convertString = null;
327: String targetTypeName = SQLTypes[targetType];
328: // For casts from Character types use strings that can
329: // be converted to the targetType.
330:
331: convertString = getCompatibleString(sourceType,
332: targetType, dataOffset);
333:
334: String query = "VALUES CAST (CAST ("
335: + convertString + " AS "
336: + SQLTypes[sourceType] + ") AS "
337: + SQLTypes[targetType] + " )";
338: executeQueryAndDisplay(conn, query);
339: checkSupportedCast(sourceType, targetType);
340: } catch (SQLException se) {
341: String sqlState = se.getSQLState();
342: if (!isSupportedCast(sourceType, targetType)) {
343: if (isCastException(se))
344: System.out
345: .println("EXPECTED CASTING EXCEPTION: "
346: + se.getMessage());
347: else
348: gotWrongException(se);
349: } else
350: unexpectedException(se);
351: }
352: }
353: }
354:
355: conn.commit();
356:
357: }
358:
359: public static void createTables(Connection conn)
360: throws SQLException, Throwable
361:
362: {
363: System.out.println("**createTables starting");
364:
365: Statement scb = conn.createStatement();
366:
367: for (int type = 0; type < SQLTypes.length; type++) {
368: String typeName = SQLTypes[type];
369: String tableName = getTableName(type);
370:
371: try {
372: scb.executeUpdate("DROP TABLE " + tableName);
373: } catch (SQLException se) {// ignore drop error
374: }
375: String createSQL = "create table " + tableName + " (c "
376: + typeName + " )";
377: System.out.println(createSQL);
378: scb.executeUpdate(createSQL);
379: }
380:
381: scb.close();
382: conn.commit();
383: }
384:
385: public static void testAssignments(Connection conn)
386: throws SQLException, Throwable {
387:
388: System.out.println("**testAssignments starting");
389: Statement scb = conn.createStatement();
390: ResultSet rs = null;
391:
392: // Insert's using literals
393: System.out.println("* testing literal inserts");
394:
395: for (int dataOffset = 0; dataOffset < SQLData[0].length; dataOffset++)
396: for (int type = 0; type < SQLTypes.length; type++) {
397: try {
398: String tableName = getTableName(type);
399:
400: String insertSQL = "insert into " + tableName
401: + " values( " + SQLData[type][dataOffset]
402: + ")";
403: System.out.println(insertSQL);
404: scb.executeUpdate(insertSQL);
405: } catch (SQLException se) {
406: // literal inserts are ok for everything but BLOB
407: if (type == BLOB_OFFSET)
408: System.out
409: .println("EXPECTED EXCEPTION inserting literal into BLOB . "
410: + se.getMessage());
411: else
412: gotWrongException(se);
413: }
414: }
415:
416: // Try to insert each sourceType into the targetType table
417: for (int dataOffset = 0; dataOffset < SQLData[0].length; dataOffset++)
418: for (int sourceType = 0; sourceType < SQLTypes.length; sourceType++) {
419: String sourceTypeName = SQLTypes[sourceType];
420: for (int targetType = 0; targetType < SQLTypes.length; targetType++) {
421: try {
422: String convertString = null;
423: String targetTableName = getTableName(targetType);
424:
425: // For assignments Character types use strings that can
426: // be converted to the targetType.
427: convertString = getCompatibleString(sourceType,
428: targetType, dataOffset);
429:
430: String insertValuesString = " VALUES CAST("
431: + convertString + " AS "
432: + sourceTypeName + ")";
433:
434: String insertSQL = "INSERT INTO "
435: + targetTableName + insertValuesString;
436: System.out.println(insertSQL);
437: scb.executeUpdate(insertSQL);
438: checkSupportedAssignment(sourceType, targetType);
439:
440: } catch (SQLException se) {
441: String sqlState = se.getSQLState();
442: if (!isSupportedAssignment(sourceType,
443: targetType)
444: && isNotStorableException(se)
445: || isCastException(se))
446: System.out.println("EXPECTED EXCEPTION: "
447: + sqlState + ":" + se.getMessage());
448: else
449: gotWrongException(se);
450: }
451: }
452: }
453: scb.close();
454: conn.commit();
455:
456: }
457:
458: public static void testComparisons(Connection conn)
459: throws SQLException, Throwable {
460:
461: System.out.println("**testComparisons starting");
462: Statement scb = conn.createStatement();
463: ResultSet rs = null;
464:
465: // Comparison's using literals
466: System.out.println("* testing literal comparisons");
467:
468: for (int type = 0; type < SQLTypes.length; type++) {
469: try {
470: int dataOffset = 1; // don't use null values
471: String tableName = getTableName(type);
472:
473: String compareSQL = "SELECT distinct c FROM "
474: + tableName + " WHERE c = "
475: + SQLData[type][dataOffset];
476: System.out.println(compareSQL);
477: rs = scb.executeQuery(compareSQL);
478: JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
479: } catch (SQLException se) {
480: // literal comparisons are ok for everything but BLOB
481: if (isLongType(type))
482: System.out
483: .println("EXPECTED EXCEPTION comparing long type. "
484: + se.getMessage());
485: else
486: gotWrongException(se);
487: }
488: }
489:
490: // Try to compare each sourceType with the targetType
491: for (int dataOffset = 0; dataOffset < SQLData[0].length; dataOffset++)
492: for (int sourceType = 0; sourceType < SQLTypes.length; sourceType++) {
493: String sourceTypeName = SQLTypes[sourceType];
494: for (int targetType = 0; targetType < SQLTypes.length; targetType++) {
495: try {
496: String convertString = null;
497: String targetTableName = getTableName(targetType);
498:
499: // For assignments Character types use strings that can
500: // be converted to the targetType.
501: convertString = getCompatibleString(sourceType,
502: targetType, dataOffset);
503:
504: // Make sure table has just compatible data
505: scb.executeUpdate("DELETE FROM "
506: + targetTableName);
507: String insertValuesString = " VALUES CAST("
508: + convertString + " AS "
509: + sourceTypeName + ")";
510:
511: String insertSQL = "INSERT INTO "
512: + targetTableName + insertValuesString;
513:
514: String compareSQL = "select c from "
515: + targetTableName + " WHERE c = CAST("
516: + convertString + " AS "
517: + sourceTypeName + ")";
518:
519: System.out.println(compareSQL);
520: rs = scb.executeQuery(compareSQL);
521: JDBCDisplayUtil.DisplayResults(System.out, rs,
522: conn);
523: checkSupportedComparison(sourceType, targetType);
524:
525: } catch (SQLException se) {
526: String sqlState = se.getSQLState();
527: if (!isSupportedComparison(sourceType,
528: targetType)
529: && isNotComparableException(se)
530: || isCastException(se))
531: System.out.println("EXPECTED EXCEPTION: "
532: + sqlState + ":" + se.getMessage());
533: else
534: gotWrongException(se);
535: }
536: }
537: }
538: scb.close();
539: conn.commit();
540:
541: }
542:
543: public static boolean isSupportedCast(int sourceType, int targetType) {
544: return T_146[sourceType][targetType];
545: }
546:
547: public static boolean isSupportedAssignment(int sourceType,
548: int targetType) {
549: return T_147a[sourceType][targetType];
550: }
551:
552: public static boolean isSupportedComparison(int sourceType,
553: int targetType) {
554: return T_147b[sourceType][targetType];
555: }
556:
557: public static boolean isCastException(SQLException se) {
558: return sqlStateMatches(se, ILLEGAL_CAST_EXCEPTION_SQLSTATE);
559: }
560:
561: public static boolean isMethodNotFoundException(SQLException se) {
562: return sqlStateMatches(se, METHOD_NOT_FOUND_SQLSTATE);
563: }
564:
565: public static boolean sqlStateMatches(SQLException se,
566: String expectedValue) {
567: String sqlState = se.getSQLState();
568: if ((sqlState != null) && (sqlState.equals(expectedValue)))
569: return true;
570: return false;
571: }
572:
573: public static boolean isNotStorableException(SQLException se) {
574: String sqlState = se.getSQLState();
575: if ((sqlState != null)
576: && (sqlState.equals(LANG_NOT_STORABLE_SQLSTATE)))
577: return true;
578: return false;
579:
580: }
581:
582: public static boolean isNotComparableException(SQLException se) {
583: String sqlState = se.getSQLState();
584: if ((sqlState != null)
585: && (sqlState.equals(LANG_NOT_COMPARABLE_SQLSTATE)))
586: return true;
587: return false;
588: }
589:
590: public static void unexpectedException(SQLException sqle) {
591:
592: String sqlState = sqle.getSQLState();
593:
594: if (isDB2 && (sqlState != null) && sqlState.equals("22003")) {
595: System.out.print("WARNING: DB2 overflow exception -");
596: } else
597: System.out.print("FAIL unexpected exception - ");
598:
599: showException(sqle);
600: sqle.printStackTrace(System.out);
601: }
602:
603: /**
604: * We got an exception when one was expected, but it was the
605: * wrong one. For DB2 we will just print a warning.
606: * @param sqle
607: */
608: public static void gotWrongException(SQLException sqle) {
609: if (isDB2) {
610: System.out
611: .print("WARNING: DB2 exception different from Derby-");
612: showException(sqle);
613: } else
614: unexpectedException(sqle);
615: }
616:
617: /**
618: * Show an expected exception
619: * @param sqle SQL Exception
620: */
621: public static void expectedException(SQLException sqle) {
622: System.out.print("EXPECTED EXCEPTION:");
623: showException(sqle);
624: System.out.println("\n");
625: }
626:
627: public static void showException(SQLException sqle) {
628: do {
629: String state = sqle.getSQLState();
630: if (state == null)
631: state = "?????";
632:
633: String msg = sqle.getMessage();
634: if (msg == null)
635: msg = "?? no message ??";
636:
637: System.out.print(" (" + state + "):" + msg);
638: sqle = sqle.getNextException();
639: } while (sqle != null);
640: }
641:
642: /**
643: * Display Query , execute and display results.
644: * @param conn Connection to use
645: * @param query to execute
646: */
647:
648: public static void executeQueryAndDisplay(Connection conn,
649: String query) throws SQLException {
650: Statement stmt = conn.createStatement();
651: ResultSet rs;
652:
653: System.out.println("Test #" + testNum++);
654: System.out.println(query);
655: rs = stmt.executeQuery(query);
656: JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
657:
658: stmt.close();
659: }
660:
661: public static boolean isLongType(int typeOffset) {
662: return ((typeOffset == LONGVARCHAR_OFFSET)
663: || (typeOffset == LONGVARCHAR_FOR_BIT_OFFSET)
664: || (typeOffset == CLOB_OFFSET) || (typeOffset == BLOB_OFFSET));
665: }
666:
667: public static boolean isCharacterType(int typeOffset) {
668: return ((typeOffset == CHAR_OFFSET)
669: || (typeOffset == VARCHAR_OFFSET)
670: || (typeOffset == LONGVARCHAR_OFFSET) || (typeOffset == CLOB_OFFSET));
671: }
672:
673: public static boolean isBinaryType(int typeOffset) {
674: return ((typeOffset == CHAR_FOR_BIT_OFFSET)
675: || (typeOffset == VARCHAR_FOR_BIT_OFFSET)
676: || (typeOffset == LONGVARCHAR_FOR_BIT_OFFSET) || (typeOffset == BLOB_OFFSET));
677: }
678:
679: public static boolean isDateTimeTimestamp(int typeOffset) {
680: return ((typeOffset == DATE_OFFSET)
681: || (typeOffset == TIME_OFFSET) || (typeOffset == TIMESTAMP_OFFSET));
682:
683: }
684:
685: public static boolean isClob(int typeOffset) {
686: return (typeOffset == CLOB_OFFSET);
687: }
688:
689: public static boolean isLob(int typeOffset) {
690: return ((typeOffset == CLOB_OFFSET) || (typeOffset == BLOB_OFFSET));
691:
692: }
693:
694: public static String getCompatibleString(int sourceType,
695: int targetType, int dataOffset) {
696: String convertString = null;
697: if ((isCharacterType(sourceType) || isBinaryType(sourceType))
698: && !isLob(sourceType))
699: convertString = formatString(SQLData[targetType][dataOffset]);
700: else
701: convertString = SQLData[sourceType][dataOffset];
702:
703: return convertString;
704: }
705:
706: // Data is already a string (starts with X, or a character string,
707: // just return, otherwise bracket with ''s
708: public static String formatString(String str) {
709: if ((str != null)
710: && (str.startsWith("X") || str.startsWith("'") || (str == NULL_VALUE)))
711: return str;
712: else
713: return "'" + str + "'";
714:
715: }
716:
717: public static boolean setValidValue(PreparedStatement ps,
718: int param, int jdbcType) throws SQLException {
719:
720: switch (jdbcType) {
721: case Types.SMALLINT:
722: ps.setShort(param, (short) 32);
723: return true;
724: case Types.INTEGER:
725: ps.setInt(param, 32);
726: return true;
727: case Types.BIGINT:
728: ps.setLong(param, 32L);
729: return true;
730: case Types.REAL:
731: ps.setFloat(param, 32.0f);
732: return true;
733: case Types.FLOAT:
734: case Types.DOUBLE:
735: ps.setDouble(param, 32.0);
736: return true;
737: case Types.DECIMAL:
738: ps.setBigDecimal(param, new BigDecimal(32.0));
739: return true;
740: case Types.CHAR:
741: case Types.VARCHAR:
742: case Types.LONGVARCHAR:
743: ps.setString(param, "32");
744: return true;
745: case Types.BINARY:
746: case Types.VARBINARY: {
747: byte[] data = { (byte) 0x04, (byte) 0x03, (byte) 0xfd,
748: (byte) 0xc3, (byte) 0x73 };
749: ps.setBytes(param, data);
750: return true;
751: }
752: //Types.LONGVARBINARY:
753: case Types.DATE:
754: ps.setDate(param, java.sql.Date.valueOf("2004-02-14"));
755: return true;
756: case Types.TIME:
757: ps.setTime(param, java.sql.Time.valueOf("13:26:42"));
758: return true;
759: case Types.TIMESTAMP:
760: ps.setTimestamp(param, java.sql.Timestamp
761: .valueOf("2004-02-23 17:14:24.097625551"));
762: return true;
763: case Types.CLOB:
764: // JDBC 3.0 spec section 16.3.2 explictly states setCharacterStream is OK for setting a CLOB
765: ps.setCharacterStream(param,
766: new java.io.StringReader("67"), 2);
767: return true;
768: case Types.BLOB:
769: // JDBC 3.0 spec section 16.3.2 explictly states setBinaryStream is OK for setting a BLOB
770: {
771: byte[] data = new byte[6];
772: data[0] = (byte) 0x82;
773: data[1] = (byte) 0x43;
774: data[2] = (byte) 0xca;
775: data[3] = (byte) 0xfe;
776: data[4] = (byte) 0x00;
777: data[5] = (byte) 0x32;
778:
779: ps.setBinaryStream(param, new java.io.ByteArrayInputStream(
780: data), 6);
781: return true;
782: }
783: default:
784: return false;
785: }
786: }
787:
788: /**
789: * Truncates (*) from typename
790: * @param type - Type offset
791: *
792: * @returns short name of type (e.g DECIMAL instead of DECIMAL(10,5)
793: */
794:
795: public static String getShortTypeName(int type) {
796: String typeName = SQLTypes[type];
797: String shortName = typeName;
798: int parenIndex = typeName.indexOf('(');
799: if (parenIndex >= 0) {
800: shortName = typeName.substring(0, parenIndex);
801: int endParenIndex = typeName.indexOf(')');
802: shortName = shortName
803: + typeName.substring(endParenIndex + 1, typeName
804: .length());
805: }
806: return shortName;
807:
808: }
809:
810: /**
811: * Build a unique table name from the type
812: * @param - table offset
813: * @returns Table name in format <TYPE>_TAB. Replaces ' ' _;
814: */
815: public static String getTableName(int type) {
816: return getShortTypeName(type).replace(' ', '_') + "_TAB";
817:
818: }
819:
820: public static void checkSupportedCast(int sourceType, int targetType) {
821: String description = " Cast from " + SQLTypes[sourceType]
822: + " to " + SQLTypes[targetType];
823:
824: if (!isSupportedCast(sourceType, targetType))
825: printShouldNotSucceedMessage(description);
826: }
827:
828: public static void printShouldNotSucceedMessage(String description) {
829: if (isDB2) {
830: System.out.println("WARNING:" + description
831: + " which is not supported in Derby works in DB2");
832: } else
833: System.out.println("FAIL:" + description
834: + " should not be supported");
835:
836: }
837:
838: public static void checkSupportedAssignment(int sourceType,
839: int targetType) {
840: String description = " Assignment from " + SQLTypes[sourceType]
841: + " to " + SQLTypes[targetType];
842:
843: if (!isSupportedAssignment(sourceType, targetType))
844: printShouldNotSucceedMessage(description);
845:
846: }
847:
848: public static void checkSupportedComparison(int sourceType,
849: int targetType) {
850: String description = " Comparison of " + SQLTypes[sourceType]
851: + " to " + SQLTypes[targetType];
852:
853: if (!isSupportedComparison(sourceType, targetType))
854: printShouldNotSucceedMessage(description);
855: }
856:
857: // -- HTML Table generation
858:
859: public static void printHTMLTables() {
860: // For headers. First four letters of each word
861: String[] shortTypes = new String[SQLTypes.length];
862:
863: for (int i = 0; i < SQLTypes.length; i++)
864: shortTypes[i] = getShortTypeName(i);
865:
866: TestUtil.startHTMLPage(
867: "Datatype Casting, Assignment, and Comparison",
868: "person@a.company.com");
869:
870: TestUtil.printBoolArrayHTMLTable("Source Types",
871: "Target Types", shortTypes, shortTypes, T_146,
872: "Table 146 - Explicit Casts Allowed by Derby");
873:
874: TestUtil.printBoolArrayHTMLTable("Source Types",
875: "Target Types", shortTypes, shortTypes, T_147a,
876: "Table 147a - Assignments Allowed by Derby");
877:
878: TestUtil.printBoolArrayHTMLTable("Source Types",
879: "Target Types", shortTypes, shortTypes, T_147b,
880: "Table 147b - Comparisons Allowed by Derby");
881:
882: TestUtil.endHTMLPage();
883: }
884:
885: }
|