KickJava   Java API By Example, From Geeks To Geeks.

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


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.tests.lang.casting
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 org.apache.derby.tools.ij;
25 import org.apache.derbyTesting.functionTests.util.TestUtil;
26 import org.apache.derby.tools.JDBCDisplayUtil;
27  
28 import java.sql.*;
29 import java.math.*;
30 import java.io.*;
31
32
33 public class casting {
34
35     public static String JavaDoc VALID_DATE_STRING = "'2000-01-01'";
36     public static String JavaDoc VALID_TIME_STRING = "'15:30:20'";
37     public static String JavaDoc VALID_TIMESTAMP_STRING = "'2000-01-01 15:30:20'";
38     public static String JavaDoc NULL_VALUE="NULL";
39
40     public static String JavaDoc ILLEGAL_CAST_EXCEPTION_SQLSTATE = "42846";
41     public static String JavaDoc LANG_NOT_STORABLE_SQLSTATE = "42821";
42     public static String JavaDoc LANG_NOT_COMPARABLE_SQLSTATE = "42818";
43     public static String JavaDoc METHOD_NOT_FOUND_SQLSTATE = "42884";
44     public static String JavaDoc LANG_FORMAT_EXCEPTION_SQLSTATE = "22018";
45
46     public static int SQLTYPE_ARRAY_SIZE = 17 ;
47     public static int SMALLINT_OFFSET = 0;
48     public static int INTEGER_OFFSET = 1;
49     public static int BIGINT_OFFSET = 2;
50     public static int DECIMAL_OFFSET = 3;
51     public static int REAL_OFFSET = 4;
52     public static int DOUBLE_OFFSET = 5;
53     public static int CHAR_OFFSET = 6;
54     public static int VARCHAR_OFFSET = 7;
55     public static int LONGVARCHAR_OFFSET = 8;
56     public static int CHAR_FOR_BIT_OFFSET = 9;
57     public static int VARCHAR_FOR_BIT_OFFSET = 10;
58     public static int LONGVARCHAR_FOR_BIT_OFFSET = 11;
59     public static int CLOB_OFFSET = 12;
60     public static int DATE_OFFSET = 13;
61     public static int TIME_OFFSET = 14;
62     public static int TIMESTAMP_OFFSET = 15;
63     public static int BLOB_OFFSET = 16;
64
65
66     public static String JavaDoc[] SQLTypes =
67     {
68         "SMALLINT",
69         "INTEGER",
70         "BIGINT",
71         "DECIMAL(10,5)",
72         "REAL",
73         "DOUBLE",
74         "CHAR(60)",
75         "VARCHAR(60)",
76         "LONG VARCHAR",
77         "CHAR(60) FOR BIT DATA",
78         "VARCHAR(60) FOR BIT DATA",
79         "LONG VARCHAR FOR BIT DATA",
80         "CLOB(1k)",
81         "DATE",
82         "TIME",
83         "TIMESTAMP",
84         "BLOB(1k)",
85     };
86
87
88     public static int NULL_DATA_OFFSET = 0; // offset of NULL value
89
public static int VALID_DATA_OFFSET = 1; // offset of NULL value
90

91     // rows are data types.
92
// data is NULL_VALUE, VALID_VALUE
93
// Should add Minimum, Maximum and out of range.
94
public static String JavaDoc[][]SQLData =
95     {
96         {NULL_VALUE, "0"}, // SMALLINT
97
{NULL_VALUE,"11"}, // INTEGER
98
{NULL_VALUE,"22"}, // BIGINT
99
{NULL_VALUE,"3.3"}, // DECIMAL(10,5)
100
{NULL_VALUE,"4.4"}, // REAL,
101
{NULL_VALUE,"5.5"}, // DOUBLE
102
{NULL_VALUE,"'7'"}, // CHAR(60)
103
{NULL_VALUE,"'8'"}, //VARCHAR(60)",
104
{NULL_VALUE,"'9'"}, // LONG VARCHAR
105
{NULL_VALUE,"X'10aa'"}, // CHAR(60) FOR BIT DATA
106
{NULL_VALUE,"X'10bb'"}, // VARCHAR(60) FOR BIT DATA
107
{NULL_VALUE,"X'10cc'"}, //LONG VARCHAR FOR BIT DATA
108
{NULL_VALUE,"'13'"}, //CLOB(1k)
109
{NULL_VALUE,VALID_DATE_STRING}, // DATE
110
{NULL_VALUE,VALID_TIME_STRING}, // TIME
111
{NULL_VALUE,VALID_TIMESTAMP_STRING}, // TIMESTAMP
112
{NULL_VALUE,"X'01dd'"} // BLOB
113
};
114  
115
116
117
118     public static final boolean _ = false;
119     public static final boolean X = true;
120
121     /**
122        DB2 Table 146 - Supported explicit casts between Built-in DataTypes
123
124        This table has THE FOR BIT DATA TYPES broken out into separate columns
125        for clarity and testing
126     **/

127
128
129     public static final boolean[][] T_146 = {
130         
131     // Types. S I B D R D C V L C V L C D T T B
132
// M N I E E O H A O H A O L A I I L
133
// A T G C A U A R N A R N O T M M O
134
// L E I I L B R C G R C G B E E E B
135
// L G N M L H V . H V S
136
// I E T A E A A B . A T
137
// N R L R R I B R A
138
// T C T I . M
139
// H T B P
140
// A I
141
// R T
142
/* 0 SMALLINT */ { X, X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _ },
143 /* 1 INTEGER */ { X, X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _ },
144 /* 2 BIGINT */ { X, X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _ },
145 /* 3 DECIMAL */ { X, X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _ },
146 /* 4 REAL */ { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
147 /* 5 DOUBLE */ { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
148 /* 6 CHAR */ { X, X, X, X, _, _, X, X, X, _, _, _, X, X, X, X, _ },
149 /* 7 VARCHAR */ { X, X, X, X, _, _, X, X, X, _, _, _, X, X, X, X, _ },
150 /* 8 LONGVARCHAR */ { _, _, _, _, _, _, X, X, X, _, _, _, X, _, _, _, _ },
151 /* 9 CHAR FOR BIT */ { _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, X },
152 /* 10 VARCH. BIT */ { _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, X },
153 /* 11 LONGVAR. BIT */ { _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, X },
154 /* 12 CLOB */ { _, _, _, _, _, _, X, X, X, _, _, _, X, _, _, _, _ },
155 /* 13 DATE */ { _, _, _, _, _, _, X, X, _, _, _, _, _, X, _, _, _ },
156 /* 14 TIME */ { _, _, _, _, _, _, X, X, _, _, _, _, _, _, X, _, _ },
157 /* 15 TIMESTAMP */ { _, _, _, _, _, _, X, X, _, _, _, _, _, X, X, X, _ },
158 /* 16 BLOB */ { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, X },
159
160     };
161
162     /**
163      * DB2 uses Table 147 to describe Data Type Compatibility for Assignments
164      *
165      * The table 147a covers the assignments as they do differ somewhat
166      * from comparisons which can be found in 147b
167        
168      * This table has DATA TYPES for operands rather than lumping types
169      * together. Here is the mapping from DB2 Table 147 to this table.
170      * Binary Integer = SMALLINT, INTEGER, BIGINT
171      * Decimal Number = DECIMAL/(NUMERIC)
172      * Floating Point = REAL, DOUBLE/(FLOAT)
173      * Character String = CHAR, VARCHAR, LONGVARCHAR
174      * Binary String = CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONG VARCHAR FOR
175      * BIT DATA
176      * Graphic String = not suppported
177      * Date = DATE
178      * Time = TIME
179      * TimeStamp = TIMESTAMP
180      * Binary String = literal hexadecimal, CHAR FOR BIT DATA, VARCHAR FOR BIT
181      * DATA, LONG VARCHAR FOR BIT
182      * CLOB and BLOB are not covered in Table 147 but are included here
183      * for clarity and testing
184     **/

185
186     public static final boolean[][] T_147a = {
187         
188     // Types. S I B D R D C V L C V L C D T T B
189
// M N I E E O H A O H A O L A I I L
190
// A T G C A U A R N A R N O T M M O
191
// L E I I L B R C G R C G B E E E B
192
// L G N M L H V . H V S
193
// I E T A E A A B . A T
194
// N R L R R I B R A
195
// T C T I . M
196
// H T B P
197
// A I
198
// R T
199
/* 0 SMALLINT */ { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
200 /* 1 INTEGER */ { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
201 /* 2 BIGINT */ { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
202 /* 3 DECIMAL */ { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
203 /* 4 REAL */ { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
204 /* 5 DOUBLE */ { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
205 /* 6 CHAR */ { _, _, _, _, _, _, X, X, X, _, _, _, X, X, X, X, _ },
206 /* 7 VARCHAR */ { _, _, _, _, _, _, X, X, X, _, _, _, X, X, X, X, _ },
207 /* 8 LONGVARCHAR */ { _, _, _, _, _, _, X, X, X, _, _, _, X, _, _, _, _ },
208 /* 9 CHAR FOR BIT */ { _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, _ },
209 /* 10 VARCH. BIT */ { _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, _ },
210 /* 11 LONGVAR. BIT */ { _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, _ },
211 /* 12 CLOB */ { _, _, _, _, _, _, X, X, X, _, _, _, X, _, _, _, _ },
212 /* 13 DATE */ { _, _, _, _, _, _, X, X, _, _, _, _, _, X, _, _, _ },
213 /* 14 TIME */ { _, _, _, _, _, _, X, X, _, _, _, _, _, _, X, _, _ },
214 /* 15 TIMESTAMP */ { _, _, _, _, _, _, X, X, _, _, _, _, _, _, _, X, _ },
215 /* 16 BLOB */ { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, X },
216
217     };
218
219
220     // Comparisons table
221
// Comparison's are different than assignments because
222
// Long types cannot be compared.
223
public static final boolean[][] T_147b = {
224         
225     // Types. S I B D R D C V L C V L C D T T B
226
// M N I E E O H A O H A O L A I I L
227
// A T G C A U A R N A R N O T M M O
228
// L E I I L B R C G R C G B E E E B
229
// L G N M L H V . H V S
230
// I E T A E A A B . A T
231
// N R L R R I B R A
232
// T C T I . M
233
// H T B P
234
// A I
235
// R T
236
/* 0 SMALLINT */ { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
237 /* 1 INTEGER */ { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
238 /* 2 BIGINT */ { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
239 /* 3 DECIMAL */ { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
240 /* 4 REAL */ { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
241 /* 5 DOUBLE */ { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
242 /* 6 CHAR */ { _, _, _, _, _, _, X, X, _, _, _, _, _, X, X, X, _ },
243 /* 7 VARCHAR */ { _, _, _, _, _, _, X, X, _, _, _, _, _, X, X, X, _ },
244 /* 8 LONGVARCHAR */ { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _ },
245 /* 9 CHAR FOR BIT */ { _, _, _, _, _, _, _, _, _, X, X, _, _, _, _, _, _ },
246 /* 10 VARCH. BIT */ { _, _, _, _, _, _, _, _, _, X, X, _, _, _, _, _, _ },
247 /* 11 LONGVAR. BIT */ { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _ },
248 /* 12 CLOB */ { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _ },
249 /* 13 DATE */ { _, _, _, _, _, _, X, X, _, _, _, _, _, X, _, _, _ },
250 /* 14 TIME */ { _, _, _, _, _, _, X, X, _, _, _, _, _, _, X, _, _ },
251 /* 15 TIMESTAMP */ { _, _, _, _, _, _, X, X, _, _, _, _, _, _, _, X, _ },
252 /* 16 BLOB */ { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _ },
253
254     };
255
256     
257
258     public static boolean isDerbyNet;
259     public static boolean isDB2;
260     //testNum just print and increments with each query display
261
public static int testNum = 1;
262  
263     public static void main(String JavaDoc[] args) throws Exception JavaDoc {
264         String JavaDoc framework = System.getProperty("framework");
265         if (framework != null && framework.toUpperCase().equals("DB2JCC"))
266             isDB2 = true;
267
268         isDerbyNet = TestUtil.isNetFramework();
269         try {
270             // use the ij utility to read the property file and
271
// make the initial connection.
272
ij.getPropertyArg(args);
273              Connection conn = ij.startJBMS();
274              conn.setAutoCommit(false);
275              createTables(conn);
276              testAssignments(conn);
277              testExplicitCasts(conn);
278              testComparisons(conn);
279              conn.close();
280         }
281         catch (SQLException sqle) {
282             unexpectedException(sqle);
283         }
284         catch (Throwable JavaDoc t) {
285             t.printStackTrace(System.out);
286         }
287     }
288
289     public static void testExplicitCasts(Connection conn)
290         throws SQLException, Throwable JavaDoc
291     {
292         
293         System.out.println("**testExplicitCasts starting");
294         
295         ResultSet rs = null;
296         
297         // Try Casts from each type to the
298
for (int sourceType = 0; sourceType < SQLTypes.length; sourceType++) {
299             
300             String JavaDoc sourceTypeName = SQLTypes[sourceType];
301             for (int dataOffset = 0; dataOffset < SQLData[0].length; dataOffset++)
302                 for (int targetType =0; targetType < SQLTypes.length; targetType++)
303                 {
304                 try {
305                     String JavaDoc convertString = null;
306                     String JavaDoc targetTypeName = SQLTypes[targetType];
307                                 // For casts from Character types use strings that can
308
// be converted to the targetType.
309

310                     convertString = getCompatibleString(sourceType,
311                                                         targetType,dataOffset);
312
313
314                     String JavaDoc query = "VALUES CAST (CAST (" + convertString +
315                         " AS " + SQLTypes[sourceType] + ") AS " +
316                         SQLTypes[targetType] + " )";
317                     executeQueryAndDisplay(conn,query);
318                     checkSupportedCast(sourceType,targetType);
319                 } catch (SQLException se)
320                 {
321                     String JavaDoc sqlState = se.getSQLState();
322                     if (!isSupportedCast(sourceType,targetType))
323                     {
324                         if(isCastException(se))
325                             System.out.println("EXPECTED CASTING EXCEPTION: " +
326                                                se.getMessage());
327                         else
328                             gotWrongException(se);
329                     }
330                     else
331                         unexpectedException(se);
332                 }
333             }
334         }
335
336         conn.commit();
337             
338     }
339
340     public static void createTables(Connection conn)
341         throws SQLException, Throwable JavaDoc
342     
343     {
344         System.out.println("**createTables starting");
345
346         Statement scb = conn.createStatement();
347
348         for (int type = 0; type < SQLTypes.length; type++)
349         {
350             String JavaDoc typeName = SQLTypes[type];
351             String JavaDoc tableName = getTableName(type);
352
353             try {
354                 scb.executeUpdate("DROP TABLE " + tableName);
355             }
356             catch(SQLException se)
357             {// ignore drop error
358
}
359             String JavaDoc createSQL = "create table "+
360                 tableName + " (c " +
361                 typeName+ " )";
362             System.out.println(createSQL);
363             scb.executeUpdate(createSQL);
364         }
365
366         scb.close();
367         conn.commit();
368     }
369
370
371
372     public static void testAssignments(Connection conn)
373         throws SQLException, Throwable JavaDoc
374     {
375         
376         System.out.println("**testAssignments starting");
377         Statement scb = conn.createStatement();
378         ResultSet rs = null;
379         
380
381         // Insert's using literals
382
System.out.println("* testing literal inserts");
383         
384         for (int dataOffset = 0; dataOffset < SQLData[0].length; dataOffset++)
385             for (int type = 0; type < SQLTypes.length; type++) {
386             try {
387                 String JavaDoc tableName = getTableName(type);
388                 
389                 String JavaDoc insertSQL = "insert into " + tableName + " values( " +
390                     SQLData[type][dataOffset] + ")";
391                 System.out.println(insertSQL);
392                 scb.executeUpdate(insertSQL);
393             }
394             catch (SQLException se)
395             {
396                 // literal inserts are ok for everything but BLOB
397
if (type == BLOB_OFFSET)
398                 System.out.println("EXPECTED EXCEPTION inserting literal into BLOB . " + se.getMessage());
399             else
400                 gotWrongException(se);
401             }
402         }
403         
404         // Try to insert each sourceType into the targetType table
405
for (int dataOffset = 0; dataOffset < SQLData[0].length; dataOffset++)
406             for (int sourceType = 0; sourceType < SQLTypes.length; sourceType++) {
407                 String JavaDoc sourceTypeName = SQLTypes[sourceType];
408                 for (int targetType =0; targetType < SQLTypes.length; targetType++)
409                 {
410                 try {
411                     String JavaDoc convertString = null;
412                     String JavaDoc targetTableName = getTableName(targetType);
413
414                     // For assignments Character types use strings that can
415
// be converted to the targetType.
416
convertString = getCompatibleString(sourceType, targetType,dataOffset);
417                     
418                     String JavaDoc insertValuesString = " VALUES CAST(" +
419                     convertString + " AS " + sourceTypeName + ")";
420                     
421                     
422                     String JavaDoc insertSQL = "INSERT INTO " + targetTableName +
423                         insertValuesString;
424                     System.out.println(insertSQL);
425                     scb.executeUpdate(insertSQL);
426                     checkSupportedAssignment(sourceType, targetType);
427
428                 } catch (SQLException se)
429                 {
430                     String JavaDoc sqlState = se.getSQLState();
431                     if (!isSupportedAssignment(sourceType,targetType) &&
432                         isNotStorableException(se) ||
433                         isCastException(se))
434                         System.out.println("EXPECTED EXCEPTION: " +
435                                            sqlState + ":" + se.getMessage());
436                     else
437                         gotWrongException(se);
438                 }
439                 }
440             }
441         scb.close();
442         conn.commit();
443             
444     }
445
446
447
448     public static void testComparisons(Connection conn)
449         throws SQLException, Throwable JavaDoc
450     {
451         
452         System.out.println("**testComparisons starting");
453         Statement scb = conn.createStatement();
454         ResultSet rs = null;
455         
456
457         // Comparison's using literals
458
System.out.println("* testing literal comparisons");
459
460
461         for (int type = 0; type < SQLTypes.length; type++) {
462             try {
463                 int dataOffset = 1; // don't use null values
464
String JavaDoc tableName = getTableName(type);
465                 
466                 String JavaDoc compareSQL = "SELECT distinct c FROM " + tableName +
467                     " WHERE c = " + SQLData[type][dataOffset];
468                 System.out.println(compareSQL);
469                 rs = scb.executeQuery(compareSQL);
470                 JDBCDisplayUtil.DisplayResults(System.out,rs,conn);
471             }
472             catch (SQLException se)
473             {
474                 // literal comparisons are ok for everything but BLOB
475
if (isLongType(type))
476                     System.out.println("EXPECTED EXCEPTION comparing long type. " + se.getMessage());
477                 else
478                     gotWrongException(se);
479             }
480         }
481         
482         
483         // Try to compare each sourceType with the targetType
484
for (int dataOffset = 0; dataOffset < SQLData[0].length; dataOffset++)
485             for (int sourceType = 0; sourceType < SQLTypes.length; sourceType++) {
486                 String JavaDoc sourceTypeName = SQLTypes[sourceType];
487                 for (int targetType =0; targetType < SQLTypes.length; targetType++)
488                 {
489                 try {
490                     String JavaDoc convertString = null;
491                     String JavaDoc targetTableName = getTableName(targetType);
492                     
493                     
494
495                     // For assignments Character types use strings that can
496
// be converted to the targetType.
497
convertString = getCompatibleString(sourceType,
498                                                         targetType,dataOffset);
499                     
500                     // Make sure table has just compatible data
501
scb.executeUpdate("DELETE FROM " + targetTableName);
502                     String JavaDoc insertValuesString = " VALUES CAST(" +
503                         convertString + " AS " + sourceTypeName + ")";
504                     
505                     String JavaDoc insertSQL = "INSERT INTO " + targetTableName +
506                         insertValuesString;
507                     
508                     String JavaDoc compareSQL = "select c from " +
509                         targetTableName + " WHERE c = CAST(" + convertString
510                         + " AS " + sourceTypeName + ")";
511
512                     System.out.println(compareSQL);
513                     rs = scb.executeQuery(compareSQL);
514                     JDBCDisplayUtil.DisplayResults(System.out,rs,conn);
515                     checkSupportedComparison(sourceType, targetType);
516
517                 } catch (SQLException se)
518                 {
519                     String JavaDoc sqlState = se.getSQLState();
520                     if (!isSupportedComparison(sourceType,targetType) &&
521                         isNotComparableException(se) ||
522                         isCastException(se))
523                         System.out.println("EXPECTED EXCEPTION: " +
524                                            sqlState + ":" + se.getMessage());
525                     else
526                         gotWrongException(se);
527                 }
528                 }
529             }
530         scb.close();
531         conn.commit();
532             
533     }
534
535
536     public static boolean isSupportedCast(int sourceType, int targetType)
537     {
538         return T_146[sourceType][targetType];
539     }
540
541     public static boolean isSupportedAssignment(int sourceType, int targetType)
542     {
543         return T_147a[sourceType][targetType];
544     }
545
546     public static boolean isSupportedComparison(int sourceType, int targetType)
547     {
548         return T_147b[sourceType][targetType];
549     }
550
551
552     public static boolean isCastException (SQLException se)
553     {
554         return sqlStateMatches(se,ILLEGAL_CAST_EXCEPTION_SQLSTATE);
555     }
556
557     public static boolean isMethodNotFoundException (SQLException se)
558     {
559         return sqlStateMatches(se, METHOD_NOT_FOUND_SQLSTATE);
560     }
561
562     public static boolean sqlStateMatches(SQLException se,
563                                            String JavaDoc expectedValue)
564     {
565         String JavaDoc sqlState = se.getSQLState();
566         if ((sqlState != null) &&
567             (sqlState.equals(expectedValue)))
568             return true;
569         return false;
570     }
571
572     public static boolean isNotStorableException(SQLException se)
573     {
574         String JavaDoc 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     {
584         String JavaDoc sqlState = se.getSQLState();
585         if ((sqlState != null) &&
586             (sqlState.equals(LANG_NOT_COMPARABLE_SQLSTATE)))
587             return true;
588         return false;
589     }
590
591     public static void unexpectedException(SQLException sqle) {
592
593         String JavaDoc sqlState = sqle.getSQLState();
594         
595         if (isDB2 && (sqlState != null) &&
596             sqlState.equals("22003"))
597         {
598             System.out.print("WARNING: DB2 overflow exception -");
599         }
600         else
601             System.out.print("FAIL unexpected exception - ");
602         
603         showException(sqle);
604         sqle.printStackTrace(System.out);
605     }
606
607
608     /**
609      * We got an exception when one was expected, but it was the
610      * wrong one. For DB2 we will just print a warning.
611      * @param sqle
612      */

613     public static void gotWrongException(SQLException sqle) {
614         if (isDB2)
615         {
616             System.out.print("WARNING: DB2 exception different from Derby-" );
617             showException(sqle);
618         }
619         else unexpectedException(sqle);
620     }
621
622     /**
623      * Show an expected exception
624      * @param sqle SQL Exception
625      */

626     public static void expectedException(SQLException sqle) {
627             System.out.print("EXPECTED EXCEPTION:" );
628             showException(sqle);
629             System.out.println("\n");
630     }
631
632
633     public static void showException(SQLException sqle) {
634         do {
635             String JavaDoc state = sqle.getSQLState();
636             if (state == null)
637                 state = "?????";
638
639             String JavaDoc msg = sqle.getMessage();
640             if (msg == null)
641                 msg = "?? no message ??";
642
643             System.out.print(" (" + state + "):" + msg);
644             sqle = sqle.getNextException();
645         } while (sqle != null);
646     }
647
648
649     /**
650      * Display Query , execute and display results.
651      * @param conn Connection to use
652      * @param query to execute
653      */

654
655     public static void executeQueryAndDisplay(Connection conn,
656                                                     String JavaDoc query)
657                                                     throws SQLException
658     {
659         Statement stmt = conn.createStatement();
660         ResultSet rs;
661
662         System.out.println("Test #" + testNum++);
663         System.out.println(query );
664         rs = stmt.executeQuery(query);
665         JDBCDisplayUtil.DisplayResults(System.out,rs,conn);
666
667         stmt.close();
668     }
669
670
671     public static boolean isLongType( int typeOffset)
672     {
673         return ((typeOffset == LONGVARCHAR_OFFSET) ||
674                 (typeOffset == LONGVARCHAR_FOR_BIT_OFFSET) ||
675                 (typeOffset == CLOB_OFFSET) ||
676                 (typeOffset == BLOB_OFFSET));
677     }
678
679     public static boolean isCharacterType(int typeOffset)
680     {
681         return ((typeOffset == CHAR_OFFSET) ||
682                 (typeOffset == VARCHAR_OFFSET) ||
683                 (typeOffset == LONGVARCHAR_OFFSET) ||
684                 (typeOffset == CLOB_OFFSET));
685     }
686
687     public static boolean isBinaryType(int typeOffset)
688     {
689         return ((typeOffset == CHAR_FOR_BIT_OFFSET) ||
690                 (typeOffset == VARCHAR_FOR_BIT_OFFSET) ||
691                 (typeOffset == LONGVARCHAR_FOR_BIT_OFFSET) ||
692                 (typeOffset == BLOB_OFFSET));
693     }
694
695     public static boolean isDateTimeTimestamp(int typeOffset)
696     {
697         return ( (typeOffset == DATE_OFFSET) ||
698                  (typeOffset == TIME_OFFSET) ||
699                  (typeOffset == TIMESTAMP_OFFSET));
700
701     }
702
703     public static boolean isClob(int typeOffset)
704     {
705         return (typeOffset == CLOB_OFFSET);
706     }
707         
708     public static boolean isLob(int typeOffset)
709     {
710         return ((typeOffset == CLOB_OFFSET) ||
711                 (typeOffset == BLOB_OFFSET));
712
713     }
714
715     public static String JavaDoc getCompatibleString(int sourceType, int targetType,
716                                               int dataOffset)
717     {
718         String JavaDoc convertString = null;
719         if ((isCharacterType(sourceType) || isBinaryType(sourceType)) &&
720             !isLob(sourceType))
721             convertString = formatString(SQLData[targetType][dataOffset]);
722         else
723             convertString = SQLData[sourceType][dataOffset];
724
725         return convertString;
726     }
727     
728     // Data is already a string (starts with X, or a character string,
729
// just return, otherwise bracket with ''s
730
public static String JavaDoc formatString(String JavaDoc str)
731     {
732         if ((str != null) && (
733                               str.startsWith("X") ||
734                               str.startsWith("'") ||
735                               (str == NULL_VALUE)))
736             return str;
737         else
738             return "'" + str + "'";
739
740     }
741
742     public static boolean setValidValue(PreparedStatement ps, int param, int jdbcType)
743         throws SQLException {
744
745         switch (jdbcType) {
746         case Types.SMALLINT:
747             ps.setShort(param, (short) 32);
748             return true;
749         case Types.INTEGER:
750             ps.setInt(param, 32);
751             return true;
752         case Types.BIGINT:
753             ps.setLong(param, 32L);
754             return true;
755         case Types.REAL:
756             ps.setFloat(param, 32.0f);
757             return true;
758         case Types.FLOAT:
759         case Types.DOUBLE:
760             ps.setDouble(param, 32.0);
761             return true;
762         case Types.DECIMAL:
763             ps.setBigDecimal(param, new BigDecimal(32.0));
764             return true;
765         case Types.CHAR:
766         case Types.VARCHAR:
767         case Types.LONGVARCHAR:
768             ps.setString(param, "32");
769             return true;
770         case Types.BINARY:
771         case Types.VARBINARY:
772             {
773                 byte[] data = {(byte) 0x04, (byte) 0x03, (byte) 0xfd, (byte) 0xc3, (byte) 0x73};
774                 ps.setBytes(param, data);
775                 return true;
776             }
777         //Types.LONGVARBINARY:
778
case Types.DATE:
779             ps.setDate(param, java.sql.Date.valueOf("2004-02-14"));
780             return true;
781         case Types.TIME:
782             ps.setTime(param, java.sql.Time.valueOf("13:26:42"));
783             return true;
784         case Types.TIMESTAMP:
785             ps.setTimestamp(param, java.sql.Timestamp.valueOf("2004-02-23 17:14:24.097625551"));
786             return true;
787         case Types.CLOB:
788             // JDBC 3.0 spec section 16.3.2 explictly states setCharacterStream is OK for setting a CLOB
789
ps.setCharacterStream(param, new java.io.StringReader JavaDoc("67"), 2);
790             return true;
791         case Types.BLOB:
792             // JDBC 3.0 spec section 16.3.2 explictly states setBinaryStream is OK for setting a BLOB
793
{
794                 byte[] data = new byte[6];
795                 data[0] = (byte) 0x82;
796                 data[1] = (byte) 0x43;
797                 data[2] = (byte) 0xca;
798                 data[3] = (byte) 0xfe;
799                 data[4] = (byte) 0x00;
800                 data[5] = (byte) 0x32;
801
802             ps.setBinaryStream(param, new java.io.ByteArrayInputStream JavaDoc(data), 6);
803             return true;
804             }
805         default:
806             return false;
807         }
808     }
809
810     /**
811      * Truncates (*) from typename
812      * @param type - Type offset
813      *
814      * @returns short name of type (e.g DECIMAL instead of DECIMAL(10,5)
815      */

816
817     public static String JavaDoc getShortTypeName(int type)
818     {
819         String JavaDoc typeName = SQLTypes[type];
820         String JavaDoc shortName = typeName;
821         int parenIndex = typeName.indexOf('(');
822         if (parenIndex >= 0)
823         {
824             shortName = typeName.substring(0,parenIndex);
825             int endParenIndex = typeName.indexOf(')');
826             shortName = shortName + typeName.substring(endParenIndex+1,typeName.length());
827         }
828         return shortName;
829
830     }
831
832
833     /**
834      * Build a unique table name from the type
835      * @param - table offset
836      * @returns Table name in format <TYPE>_TAB. Replaces ' ' _;
837      */

838     public static String JavaDoc getTableName(int type)
839     {
840         return getShortTypeName(type).replace(' ', '_') + "_TAB";
841             
842     }
843
844     public static void checkSupportedCast(int sourceType, int targetType)
845     {
846         String JavaDoc description = " Cast from " +
847             SQLTypes[sourceType] +
848             " to " + SQLTypes[targetType];
849         
850         if(!isSupportedCast(sourceType,targetType))
851             printShouldNotSucceedMessage(description);
852     }
853
854     public static void printShouldNotSucceedMessage(String JavaDoc description)
855     {
856         if (isDB2)
857         {
858             System.out.println("WARNING:" + description + " which is not supported in Derby works in DB2");
859         }
860         else
861             System.out.println("FAIL:" + description +
862                                " should not be supported");
863         
864     }
865
866     public static void checkSupportedAssignment(int sourceType, int targetType)
867     {
868         String JavaDoc description = " Assignment from " +
869             SQLTypes[sourceType] +
870             " to " + SQLTypes[targetType];
871         
872         if (!isSupportedAssignment(sourceType,targetType))
873             printShouldNotSucceedMessage(description);
874
875
876     }
877
878     public static void checkSupportedComparison(int sourceType,int targetType)
879     {
880         String JavaDoc description = " Comparison of " +
881             SQLTypes[sourceType] +
882             " to " + SQLTypes[targetType];
883         
884         if (!isSupportedComparison(sourceType,targetType))
885             printShouldNotSucceedMessage(description);
886     }
887
888   
889     // -- HTML Table generation
890

891     public static void printHTMLTables()
892     {
893         // For headers. First four letters of each word
894
String JavaDoc [] shortTypes = new String JavaDoc[SQLTypes.length];
895
896         for (int i = 0; i < SQLTypes.length; i++)
897             shortTypes[i] = getShortTypeName(i);
898
899         TestUtil.startHTMLPage("Datatype Casting, Assignment, and Comparison",
900                       "person@a.company.com");
901         
902         TestUtil.printBoolArrayHTMLTable("Source Types","Target Types",
903                                          shortTypes,
904                                          shortTypes,
905                                          T_146,
906                                          "Table 146 - Explicit Casts Allowed by Derby");
907         
908         TestUtil.printBoolArrayHTMLTable("Source Types","Target Types",
909                                          shortTypes,
910                                          shortTypes,
911                                          T_147a,
912                                          "Table 147a - Assignments Allowed by Derby");
913
914         TestUtil.printBoolArrayHTMLTable("Source Types","Target Types",
915                                          shortTypes,
916                                          shortTypes,
917                                          T_147b,
918                                          "Table 147b - Comparisons Allowed by Derby");
919
920        
921         TestUtil.endHTMLPage();
922     }
923
924
925 }
926
927
Popular Tags