Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > jdbcapi > odbc_metadata


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.odbc_metadata
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.jdbcapi;
23
24 import java.sql.Connection ;
25 import java.sql.DriverManager ;
26 import java.sql.DatabaseMetaData ;
27 import java.sql.ResultSetMetaData ;
28 import java.sql.Statement ;
29 import java.sql.CallableStatement ;
30 import java.sql.ResultSet ;
31 import java.sql.SQLException ;
32 import java.sql.Types ;
33 import java.sql.Timestamp ;
34 import java.sql.Time ;
35 import java.sql.Date ;
36 import java.math.BigDecimal ;
37
38 import java.util.Properties ;
39
40 import org.apache.derby.tools.ij;
41 import org.apache.derbyTesting.functionTests.util.TestUtil;
42
43 /**
44  * Test of database metadata for ODBC clients. This test does
45  * everything that is done in "metadata.java" (which this class
46  * extends), except that it makes the metadata calls in such a way
47  * as to retrieve ODBC-compliant result sets. Unlike metadata.java,
48  * this test also does a (simple) check of the metadata result sets
49  * to see if they comply with the standards--in this case, with the
50  * ODBC 3.0 standard as defined at this URL:
51  *
52  * http://msdn.microsoft.com/library/default.asp?url=/library/
53  * en-us/odbc/htm/odbcsqlprocedurecolumns.asp
54  *
55  * The ODBC standards verification involves checking the following
56  * for each column in each of the relevant metadata result sets:
57  *
58  * 1. Does the column name match what the spec says?
59  * 2. Does the column type match what the spec says?
60  * 3. Does the column nullability match what the spec says?
61  *
62  * If compliance failures occur in any of these ways, an ODBC non-
63  * compliance failure will be reported as part of the test output.
64  *
65  * Under no circumstances should a master file for this test
66  * contain an ODBC non-compliance message and still be considered
67  * as "passing".
68  */
69
70 public class odbc_metadata extends metadata_test {
71
72     // The following 2-D array holds the target names,
73 // types, and nullability of metadata result sets
74 // as defined in the ODBC 3.0 specification. Each
75 // row in this array corresponds to a SYSIBM
76 // metadata procedure that is used (by both the
77 // engine and the Network Server) for retrieval
78 // of metadata. Each row in turn consists of
79 // 3 * n strings, where "n" is the number of columns
80 // expected as part of the result set for that row's
81 // corresponding SYSIBM procedure. For a given column
82 // "c" in each row, the expected name for that column
83 // is at <row>[c], the expected type is at <row>[c+1],
84 // and the expected nullability is at <row>[c+2]. The
85 // expected values are hard-coded into this file, and
86 // are loaded via the loadODBCTargets method.
87 //
88 // "15" here is the number of procedure ids defined in
89 // metadata.java. "25" is a safety figure for the max
90 // number of columns a single metadata procedure returns
91 // in its result set. At time of writing, the most
92 // any procedure had was 19, so use 25 to give us
93 // some cushion.
94
95     private static String [][] odbcComplianceTargets =
96         new String [15][25];
97
98     /**
99      * Constructor:
100      * Intializes the Connection and Statement fields
101      * to be used through the test, and then does the
102      * first-level check of ODBC compliance.
103      */
104     public odbc_metadata(String [] args) {
105
106         try {
107
108             ij.getPropertyArg(args);
109             con = ij.startJBMS();
110             s = con.createStatement();
111
112             // Run the compliance checks for column name and
113 // column type. This method will load the target
114 // values that we want to match.
115 verifyODBC3Compliance();
116
117         } catch (SQLException e) {
118             dumpSQLExceptions(e);
119         }
120         catch (Throwable e) {
121             System.out.println("FAIL -- unexpected exception:");
122             e.printStackTrace(System.out);
123         }
124
125     }
126
127     /**
128      * Makes a call to the "runTest" method in metadata_test.java,
129      * which will in turn call back here for implementations of
130      * the abstract methods.
131      */
132     public static void main(String [] args) {
133
134         new odbc_metadata(args).runTest();
135
136     }
137
138     /**
139      * This method is responsible for executing a metadata query and returning
140      * a result set that complies with the ODBC 3.0 specification.
141      */
142     protected ResultSet getMetaDataRS(DatabaseMetaData dmd, int procId,
143         String [] sArgs, String [] argArray, int [] iArgs, boolean [] bArgs)
144         throws SQLException
145     {
146
147         switch (procId) {
148
149             case GET_PROCEDURES:
150
151                 s.execute("CALL SYSIBM.SQLPROCEDURES (" +
152                     addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) +
153                     ", " + addQuotes(sArgs[2]) + ", 'DATATYPE=''ODBC''')");
154                 return s.getResultSet();
155
156             case GET_PROCEDURE_COLUMNS:
157
158                 s.execute("CALL SYSIBM.SQLPROCEDURECOLS (" +
159                     addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) +
160                     ", " + addQuotes(sArgs[2]) + ", " + addQuotes(sArgs[3]) +
161                     ", 'DATATYPE=''ODBC''')");
162                 return s.getResultSet();
163
164             case GET_TABLES:
165
166                 int count = (argArray == null) ? 0 : argArray.length;
167                 StringBuffer tableTypes = new StringBuffer ();
168                 for (int i = 0; i < count; i++) {
169                     if (i > 0)
170                         tableTypes.append(",");
171                     tableTypes.append(argArray[i]);
172                 }
173
174                 s.execute("CALL SYSIBM.SQLTABLES (" +
175                     addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) +
176                     ", " + addQuotes(sArgs[2]) + ", " +
177                     ((argArray == null) ? "null" : addQuotes(tableTypes.toString())) +
178                     ", 'DATATYPE=''ODBC''')");
179
180                 return s.getResultSet();
181
182             case GET_COLUMNS:
183
184                 s.execute("CALL SYSIBM.SQLCOLUMNS (" +
185                     addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) +
186                     ", " + addQuotes(sArgs[2]) + ", " + addQuotes(sArgs[3]) +
187                     ", 'DATATYPE=''ODBC''')");
188                 return s.getResultSet();
189
190             case GET_COLUMN_PRIVILEGES:
191
192                 s.execute("CALL SYSIBM.SQLCOLPRIVILEGES (" +
193                     addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) +
194                     ", " + addQuotes(sArgs[2]) + ", " + addQuotes(sArgs[3]) +
195                     ", 'DATATYPE=''ODBC''')");
196                 return s.getResultSet();
197
198             case GET_TABLE_PRIVILEGES:
199
200                 s.execute("CALL SYSIBM.SQLTABLEPRIVILEGES (" +
201                     addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) +
202                     ", " + addQuotes(sArgs[2]) + ", 'DATATYPE=''ODBC''')");
203                 return s.getResultSet();
204
205             case GET_BEST_ROW_IDENTIFIER:
206
207                 s.execute("CALL SYSIBM.SQLSPECIALCOLUMNS (1, " +
208                     addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) +
209                     ", " + addQuotes(sArgs[2]) + ", " + iArgs[0] + ", " +
210                     (bArgs[0] ? "1, " : "0, ") + "'DATATYPE=''ODBC''')");
211                 return s.getResultSet();
212
213             case GET_VERSION_COLUMNS:
214
215                 s.execute("CALL SYSIBM.SQLSPECIALCOLUMNS (2, " +
216                     addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) +
217                     ", " + addQuotes(sArgs[2]) + ", 1, 1, 'DATATYPE=''ODBC''')");
218                 return s.getResultSet();
219
220             case GET_PRIMARY_KEYS:
221
222                 s.execute("CALL SYSIBM.SQLPRIMARYKEYS (" +
223                     addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) +
224                     ", " + addQuotes(sArgs[2]) + ", 'DATATYPE=''ODBC''')");
225                 return s.getResultSet();
226
227             case GET_IMPORTED_KEYS:
228
229                 s.execute("CALL SYSIBM.SQLFOREIGNKEYS (null, null, null, " +
230                     addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) +
231                     ", " + addQuotes(sArgs[2]) + ", 'IMPORTEDKEY=1;DATATYPE=''ODBC''')");
232                 return s.getResultSet();
233
234             case GET_EXPORTED_KEYS:
235
236                 s.execute("CALL SYSIBM.SQLFOREIGNKEYS (" +
237                     addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) +
238                     ", " + addQuotes(sArgs[2]) + ", null, null, null, " +
239                     "'EXPORTEDKEY=1;DATATYPE=''ODBC''')");
240                 return s.getResultSet();
241
242             case GET_CROSS_REFERENCE:
243
244                 s.execute("CALL SYSIBM.SQLFOREIGNKEYS (" +
245                     addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) +
246                     ", " + addQuotes(sArgs[2]) + ", " + addQuotes(sArgs[3]) +
247                     ", " + addQuotes(sArgs[4]) + ", " + addQuotes(sArgs[5]) +
248                     ", 'DATATYPE=''ODBC''')");
249                 return s.getResultSet();
250
251             case GET_TYPE_INFO:
252
253                 s.execute("CALL SYSIBM.SQLGETTYPEINFO (0, 'DATATYPE=''ODBC''')");
254                 return s.getResultSet();
255
256             case GET_INDEX_INFO:
257
258                 s.execute("CALL SYSIBM.SQLSTATISTICS (" +
259                     addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) +
260                     ", " + addQuotes(sArgs[2]) + (bArgs[0] ? ", 0, " : ", 1, ") +
261                     (bArgs[1] ? "1, " : "0, ") + "'DATATYPE=''ODBC''')");
262                 return s.getResultSet();
263
264             default:
265             // shouldn't get here.
266
267                 System.out.println("*** UNEXPECTED PROCEDURE ID ENCOUNTERED: " + procId + ".");
268                 return null;
269
270         }
271
272     }
273
274     /**
275      * Dumps a result to output and, if procId is not -1, checks
276      * to see if the nullability of the result set values conforms
277      * to the ODBC 3.0 specification.
278      */
279     protected void dumpRS(int procId, ResultSet s) throws SQLException {
280
281         ResultSetMetaData rsmd = s.getMetaData ();
282
283         // Get the number of columns in the result set
284 int numCols = rsmd.getColumnCount ();
285         String [] headers = new String [numCols];
286         if (numCols <= 0) {
287             System.out.println("(no columns!)");
288             return;
289         }
290         
291         // Display column headings, and include column types
292 // as part of those headings.
293 for (int i=1; i<=numCols; i++) {
294             if (i > 1) System.out.print(",");
295             headers[i-1] = rsmd.getColumnLabel(i);
296             System.out.print(headers[i-1]);
297             System.out.print("[" + rsmd.getColumnTypeName(i) + "]");
298
299         }
300         System.out.println();
301     
302         // Display data, fetching until end of the result set
303 StringBuffer errorColumns;
304         while (s.next()) {
305             // Loop through each column, getting the
306 // column data and displaying
307 errorColumns = new StringBuffer ();
308             String value;
309             for (int i=1; i<=numCols; i++) {
310                 if (i > 1) System.out.print(",");
311                 value = s.getString(i);
312                 if (headers[i-1].equals("DATA_TYPE"))
313                 {
314                     if (((TestUtil.getJDBCMajorVersion(s.getStatement().getConnection()) >= 3) &&
315                          (Integer.valueOf(value).intValue() == 16)) ||
316                         (Integer.valueOf(value).intValue() == -7))
317                         System.out.print("**BOOLEAN_TYPE for VM**");
318                     else
319                         System.out.print(value);
320                 }
321                 else
322                     System.out.print(value);
323
324                 // Check ODBC nullability, if required.
325 if ((procId != IGNORE_PROC_ID) &&
326                     badNullability(procId, headers[i-1], i, s.wasNull()))
327                 {
328                     errorColumns.append(headers[i-1]);
329                 }
330
331             }
332
333             if (errorColumns.length() > 0) {
334                 System.out.println(
335                     "\n--> ODBC COMPLIANCE FAILED: Column was NULL in " +
336                     "the preceding row when it is specified as NOT NULL: " +
337                     errorColumns.toString() + ".");
338             }
339
340             System.out.println();
341         }
342         s.close();
343     }
344
345     /**
346      * This method tests to see if the result sets returned for
347      * ODBC clients do in fact comply with the ODBC 3.0 spec.
348      * That specification can be found here:
349      *
350      * http://msdn.microsoft.com/library/default.asp?url=/library/
351      * en-us/odbc/htm/odbcsqlprocedurecolumns.asp
352      *
353      * NOTE: This method only verifies the names and types of the
354      * columns. The nullability of the values is checked as part
355      * of the dumpRS() method.
356      *
357      */
358     protected void verifyODBC3Compliance()
359         throws Exception
360     {
361
362         System.out.println(
363             "\n=============== Begin ODBC 3.0 Compliance Tests =================\n");
364
365         // Load the "target" values, which are the values that
366 // specified by the ODBC specification.
367 loadODBCTargets();
368
369         System.out.println("SQLProcedures:");
370         s.execute(
371             "call sysibm.sqlprocedures (null, '%', 'GETPCTEST%', 'DATATYPE=''ODBC''')");
372         checkODBCNamesAndTypes(s.getResultSet(), GET_PROCEDURES);
373
374         System.out.println("SQLProcedureColumns:");
375         s.execute(
376                 "call sysibm.sqlprocedurecols(null, '%', 'GETPCTEST%', '%', 'DATATYPE=''ODBC''')");
377         checkODBCNamesAndTypes(s.getResultSet(), GET_PROCEDURE_COLUMNS);
378
379         System.out.println("SQLTables:");
380         s.execute(
381             "call sysibm.sqltables (null, null, null, 'SYSTEM TABLE', 'DATATYPE=''ODBC''')");
382         checkODBCNamesAndTypes(s.getResultSet(), GET_TABLES);
383
384         System.out.println("SQLColumns:");
385         s.execute(
386             "call sysibm.sqlcolumns ('', null, '', '', 'DATATYPE=''ODBC''')");
387         checkODBCNamesAndTypes(s.getResultSet(), GET_COLUMNS);
388
389         System.out.println("SQLColumnPrivileges:");
390         s.execute(
391             "call sysibm.sqlcolprivileges ('Huey', 'Dewey', 'Louie', 'Frooey', 'DATATYPE=''ODBC''')");
392         checkODBCNamesAndTypes(s.getResultSet(), GET_COLUMN_PRIVILEGES);
393
394         System.out.println("SQLTablePrivileges:");
395         s.execute(
396             "call sysibm.sqltableprivileges ('Huey', 'Dewey', 'Louie', 'DATATYPE=''ODBC''')");
397         checkODBCNamesAndTypes(s.getResultSet(), GET_TABLE_PRIVILEGES);
398
399         System.out.println("SQLSpecialColumns: getBestRowIdentifier");
400         s.execute(
401             "call sysibm.sqlspecialcolumns (1, '', null, 'LOUIE', 1, 1, 'DATATYPE=''ODBC''')");
402         checkODBCNamesAndTypes(s.getResultSet(), GET_BEST_ROW_IDENTIFIER);
403
404         System.out.println("SQLSpecialColumns: getVersionColumns");
405         s.execute(
406             "call sysibm.sqlspecialcolumns (2, 'Huey', 'Dewey', 'Louie', 1, 1, 'DATATYPE=''ODBC''')");
407         checkODBCNamesAndTypes(s.getResultSet(), GET_VERSION_COLUMNS);
408
409         System.out.println("SQLPrimaryKeys:");
410         s.execute(
411             "call sysibm.sqlprimarykeys ('', '%', 'LOUIE', 'DATATYPE=''ODBC''')");
412         checkODBCNamesAndTypes(s.getResultSet(), GET_PRIMARY_KEYS);
413
414         System.out.println("SQLForeignKeys: getImportedKeys");
415         s.execute(
416             "call sysibm.sqlforeignkeys (null, null, null, null, null, null, " +
417                     "'IMPORTEDKEY=1;DATATYPE=''ODBC''')");
418         checkODBCNamesAndTypes(s.getResultSet(), GET_IMPORTED_KEYS);
419
420         System.out.println("SQLForeignKeys: getExportedKeys");
421         s.execute(
422             "call sysibm.sqlforeignkeys (null, null, null, null, null, null, " +
423                 "'EXPORTEDKEY=1;DATATYPE=''ODBC''')");
424         checkODBCNamesAndTypes(s.getResultSet(), GET_EXPORTED_KEYS);
425
426         System.out.println("SQLForeignKeys: getCrossReference");
427         s.execute(
428             "call sysibm.sqlforeignkeys ('', null, 'LOUIE', '', null, 'REFTAB', 'DATATYPE=''ODBC''')");
429         checkODBCNamesAndTypes(s.getResultSet(), GET_CROSS_REFERENCE);
430
431         System.out.println("SQLGetTypeInfo");
432         s.execute(
433             "call sysibm.sqlgettypeinfo (0, 'DATATYPE=''ODBC''')");
434         checkODBCNamesAndTypes(s.getResultSet(), GET_TYPE_INFO);
435
436         System.out.println("SQLStatistics:");
437         s.execute(
438             "call sysibm.sqlstatistics ('', 'SYS', 'SYSCOLUMNS', 1, 0, 'DATATYPE=''ODBC''')");
439         checkODBCNamesAndTypes(s.getResultSet(), GET_INDEX_INFO);
440
441         System.out.println(
442             "\n=============== End ODBC 3.0 Compliance Tests =================\n");
443
444     }
445
446     /**
447      * This is where we load the metadata result set schema
448      * that is specified in the ODBC 3.0 spec. When we do
449      * validation of the ODBC result set, we will compare
450      * the actual results with the target values that we
451      * load here.
452      *
453      * Target lists consist of three strings for each column
454      * in the result set. The first string is the expected
455      * (ODBC 3.0) column name. The second string is the
456      * expected column type. The third string is the
457      * expected column nullability (null means that the
458      * column is nullable; any non-null String means that
459      * the column is NOT NULLABLE).
460      *
461      * The target values in this method come from the following
462      * URL:
463      *
464      * http://msdn.microsoft.com/library/default.asp?url=/library/
465      * en-us/odbc/htm/odbcsqlprocedurecolumns.asp
466      *
467      */
468     protected void loadODBCTargets() {
469
470         odbcComplianceTargets[GET_PROCEDURES] = new String [] {
471
472             "PROCEDURE_CAT", "VARCHAR", null,
473             "PROCEDURE_SCHEM", "VARCHAR", null,
474             "PROCEDURE_NAME", "VARCHAR", "NOT NULL",
475             "NUM_INPUT_PARAMS", "INTEGER", null,
476             "NUM_OUTPUT_PARAMS", "INTEGER", null,
477             "NUM_RESULT_SETS", "INTEGER", null,
478             "REMARKS", "VARCHAR", null,
479             "PROCEDURE_TYPE", "SMALLINT", null
480
481         };
482
483         odbcComplianceTargets[GET_PROCEDURE_COLUMNS] = new String [] {
484
485             "PROCEDURE_CAT", "VARCHAR", null,
486             "PROCEDURE_SCHEM", "VARCHAR", null,
487             "PROCEDURE_NAME", "VARCHAR", "NOT NULL",
488             "COLUMN_NAME", "VARCHAR", "NOT NULL",
489             "COLUMN_TYPE", "SMALLINT", "NOT NULL",
490             "DATA_TYPE", "SMALLINT", "NOT NULL",
491             "TYPE_NAME", "VARCHAR", "NOT NULL",
492             "COLUMN_SIZE", "INTEGER", null,
493             "BUFFER_LENGTH", "INTEGER", null,
494             "DECIMAL_DIGITS", "SMALLINT", null,
495             "NUM_PREC_RADIX", "SMALLINT", null,
496             "NULLABLE", "SMALLINT", "NOT NULL",
497             "REMARKS", "VARCHAR", null,
498             "COLUMN_DEF", "VARCHAR", null,
499             "SQL_DATA_TYPE", "SMALLINT", "NOT NULL",
500             "SQL_DATETIME_SUB", "SMALLINT", null,
501             "CHAR_OCTET_LENGTH", "INTEGER", null,
502             "ORDINAL_POSITION", "INTEGER", "NOT NULL",
503             "IS_NULLABLE", "VARCHAR", null
504
505         };
506
507         odbcComplianceTargets[GET_TABLES] = new String [] {
508
509             "TABLE_CAT", "VARCHAR", null,
510             "TABLE_SCHEM", "VARCHAR", null,
511             "TABLE_NAME", "VARCHAR", null,
512             "TABLE_TYPE", "VARCHAR", null,
513             "REMARKS", "VARCHAR", null,
514             // the next columns are not defined in ODBC
515 "TYPE_CAT", "VARCHAR", null,
516             "TYPE_SCHEM", "VARCHAR", null,
517             "TYPE_NAME", "VARCHAR", null,
518             "SELF_REFERENCING_COL_NAME", "VARCHAR", null,
519             "REF_GENERATION", "VARCHAR", null,
520
521         };
522
523         odbcComplianceTargets[GET_COLUMNS] = new String [] {
524
525             "TABLE_CAT", "VARCHAR", null,
526             "TABLE_SCHEM", "VARCHAR", null,
527             "TABLE_NAME", "VARCHAR", "NOT NULL",
528             "COLUMN_NAME", "VARCHAR", "NOT NULL",
529             "DATA_TYPE", "SMALLINT", "NOT NULL",
530             "TYPE_NAME", "VARCHAR", "NOT NULL",
531             "COLUMN_SIZE", "INTEGER", null,
532             "BUFFER_LENGTH", "INTEGER", null,
533             "DECIMAL_DIGITS", "SMALLINT", null,
534             "NUM_PREC_RADIX", "SMALLINT", null,
535             "NULLABLE", "SMALLINT", "NOT NULL",
536             "REMARKS", "VARCHAR", null,
537             "COLUMN_DEF", "VARCHAR", null,
538             "SQL_DATA_TYPE", "SMALLINT", "NOT NULL",
539             "SQL_DATETIME_SUB", "SMALLINT", null,
540             "CHAR_OCTET_LENGTH", "INTEGER", null,
541             "ORDINAL_POSITION", "INTEGER", "NOT NULL",
542             "IS_NULLABLE", "VARCHAR", null,
543             // the next columns are not defined in ODBC
544 "SCOPE_CATLOG", "VARCHAR", null,
545             "SCOPE_SCHEMA", "VARCHAR", null,
546             "SCOPE_TABLE", "VARCHAR", null,
547             "SOURCE_DATA_TYPE", "SMALLINT", null,
548             "IS_AUTOINCREMENT", "VARCHAR", "NOT NULL",
549
550         };
551
552         odbcComplianceTargets[GET_COLUMN_PRIVILEGES] = new String [] {
553
554             "TABLE_CAT", "VARCHAR", null,
555             "TABLE_SCHEM", "VARCHAR", null,
556             "TABLE_NAME", "VARCHAR", "NOT NULL",
557             "COLUMN_NAME", "VARCHAR", "NOT NULL",
558             "GRANTOR", "VARCHAR", null,
559             "GRANTEE", "VARCHAR", "NOT NULL",
560             "PRIVILEGE", "VARCHAR", "NOT NULL",
561             "IS_GRANTABLE", "VARCHAR", null
562
563         };
564
565         odbcComplianceTargets[GET_TABLE_PRIVILEGES] = new String [] {
566
567             "TABLE_CAT", "VARCHAR", null,
568             "TABLE_SCHEM", "VARCHAR", null,
569             "TABLE_NAME", "VARCHAR", "NOT NULL",
570             "GRANTOR", "VARCHAR", null,
571             "GRANTEE", "VARCHAR", "NOT NULL",
572             "PRIVILEGE", "VARCHAR", "NOT NULL",
573             "IS_GRANTABLE", "VARCHAR", null
574
575         };
576
577         // Next two corresond to ODBC's "SQLSpecialColumns".
578
579         odbcComplianceTargets[GET_BEST_ROW_IDENTIFIER] = new String [] {
580
581             "SCOPE", "SMALLINT", null,
582             "COLUMN_NAME", "VARCHAR", "NOT NULL",
583             "DATA_TYPE", "SMALLINT", "NOT NULL",
584             "TYPE_NAME", "VARCHAR", "NOT NULL",
585             "COLUMN_SIZE", "INTEGER", null,
586             "BUFFER_LENGTH", "INTEGER", null,
587             "DECIMAL_DIGITS", "SMALLINT", null,
588             "PSEUDO_COLUMN", "SMALLINT", null
589
590         };
591
592         odbcComplianceTargets[GET_VERSION_COLUMNS] =
593             odbcComplianceTargets[GET_BEST_ROW_IDENTIFIER];
594
595         odbcComplianceTargets[GET_PRIMARY_KEYS] = new String [] {
596
597             "TABLE_CAT", "VARCHAR", null,
598             "TABLE_SCHEM", "VARCHAR", null,
599             "TABLE_NAME", "VARCHAR", "NOT NULL",
600             "COLUMN_NAME", "VARCHAR", "NOT NULL",
601             "KEY_SEQ", "SMALLINT", "NOT NULL",
602             "PK_NAME", "VARCHAR", null
603
604         };
605
606         // Next three correspond to ODBC's "SQLForeignKeys".
607
608         odbcComplianceTargets[GET_IMPORTED_KEYS] = new String [] {
609
610             "PKTABLE_CAT", "VARCHAR", null,
611             "PKTABLE_SCHEM", "VARCHAR", null,
612             "PKTABLE_NAME", "VARCHAR", "NOT NULL",
613             "PKCOLUMN_NAME", "VARCHAR", "NOT NULL",
614             "FKTABLE_CAT", "VARCHAR", null,
615             "FKTABLE_SCHEM", "VARCHAR", null,
616             "FKTABLE_NAME", "VARCHAR", "NOT NULL",
617             "FKCOLUMN_NAME", "VARCHAR", "NOT NULL",
618             "KEY_SEQ", "SMALLINT", "NOT NULL",
619             "UPDATE_RULE", "SMALLINT", null,
620             "DELETE_RULE", "SMALLINT", null,
621             "FK_NAME", "VARCHAR", null,
622             "PK_NAME", "VARCHAR", null,
623             "DEFERRABILITY", "SMALLINT", null
624
625         };
626
627         odbcComplianceTargets[GET_EXPORTED_KEYS] =
628             odbcComplianceTargets[GET_IMPORTED_KEYS];
629
630         odbcComplianceTargets[GET_CROSS_REFERENCE] =
631             odbcComplianceTargets[GET_IMPORTED_KEYS];
632
633         odbcComplianceTargets[GET_TYPE_INFO] = new String [] {
634
635             "TYPE_NAME", "VARCHAR", "NOT NULL",
636             "DATA_TYPE", "SMALLINT", "NOT NULL",
637             "COLUMN_SIZE", "INTEGER", null,
638             "LITERAL_PREFIX", "VARCHAR", null,
639             "LITERAL_SUFFIX", "VARCHAR", null,
640             "CREATE_PARAMS", "VARCHAR", null,
641             "NULLABLE", "SMALLINT", "NOT NULL",
642             "CASE_SENSITIVE", "SMALLINT", "NOT NULL",
643             "SEARCHABLE", "SMALLINT", "NOT NULL",
644             "UNSIGNED_ATTRIBUTE", "SMALLINT", null,
645             "FIXED_PREC_SCALE", "SMALLINT", "NOT NULL",
646             "AUTO_UNIQUE_VAL", "SMALLINT", null,
647             "LOCAL_TYPE_NAME", "VARCHAR", null,
648             "MINIMUM_SCALE", "SMALLINT", null,
649             "MAXIMUM_SCALE", "SMALLINT", null,
650             "SQL_DATA_TYPE", "SMALLINT", "NOT NULL",
651             "SQL_DATETIME_SUB", "SMALLINT", null,
652             "NUM_PREC_RADIX", "INTEGER", null,
653             "INTERVAL_PRECISION", "SMALLINT", null
654
655         };
656
657         // Next one corresponds to ODBC's "SQLStatistics".
658 odbcComplianceTargets[GET_INDEX_INFO] = new String [] {
659
660             "TABLE_CAT", "VARCHAR", null,
661             "TABLE_SCHEM", "VARCHAR", null,
662             "TABLE_NAME", "VARCHAR", "NOT NULL",
663             "NON_UNIQUE", "SMALLINT", null,
664             "INDEX_QUALIFIER", "VARCHAR", null,
665             "INDEX_NAME", "VARCHAR", null,
666             "TYPE", "SMALLINT", "NOT NULL",
667             "ORDINAL_POSITION", "SMALLINT", null,
668             "COLUMN_NAME", "VARCHAR", null,
669             "ASC_OR_DESC", "CHAR", null,
670             "CARDINALITY", "INTEGER", null,
671             "PAGES", "INTEGER", null,
672             "FILTER_CONDITION", "VARCHAR", null
673
674         };
675
676     }
677
678     /**
679      * Takes result set metadata and sees if the names
680      * and types of its columns match what ODBC 3.0
681      * dictates.
682      */
683     protected void checkODBCNamesAndTypes(ResultSet rs, int procId)
684         throws SQLException
685     {
686
687         ResultSetMetaData rsmd = rs.getMetaData();
688
689         int numCols = rsmd.getColumnCount();
690         int targetCols = odbcComplianceTargets[procId].length / 3;
691         if (numCols < targetCols) {
692         // result set is missing columns, so we already know
693 // something is wrong.
694 System.out.println(
695                 " --> ODBC COMPLIANCE FAILED: Result set was missing columns.");
696             return;
697         }
698
699         // Check type and name of each column in the result set.
700 for (int i = 1; i <= targetCols; i++) {
701
702             int offset = 3 * (i - 1);
703             if (!rsmd.getColumnLabel(i).equals(odbcComplianceTargets[procId][offset])) {
704                 System.out.println(
705                     "--> ODBC COMPLIANCE FAILED: Column name '" + rsmd.getColumnLabel(i) +
706                     "' does not match expected name '" +
707                     odbcComplianceTargets[procId][offset] + "'.");
708             }
709             if (!rsmd.getColumnTypeName(i).equals(odbcComplianceTargets[procId][offset + 1])) {
710                 System.out.println(
711                     "--> ODBC COMPLIANCE FAILED: Column type '" + rsmd.getColumnTypeName(i) +
712                     "' does not match expected type '" +
713                     odbcComplianceTargets[procId][offset + 1] + "' for column '" +
714                     rsmd.getColumnLabel(i) + "'.");
715             }
716
717         }
718
719         System.out.println("==> ODBC type/name checking done.");
720
721     }
722
723     /**
724      * Takes result set metadata and sees if the
725      * nullability of its columns match what ODBC 3.0
726      * dictates.
727      */
728     protected boolean badNullability(int odbcProc, String colName,
729         int colNum, boolean wasNull)
730     {
731
732         return (wasNull &&
733             odbcComplianceTargets[odbcProc][3 * (colNum-1) + 2] != null);
734
735     }
736
737     private static String addQuotes(String str) {
738
739         if (str == null)
740             return "null";
741
742         if (str.length() == 0)
743             return "''";
744
745         if ((str.charAt(0) == '\'') && (str.charAt(str.length()-1) == '\''))
746         // already have quotes.
747 return str;
748
749         return "'" + str + "'";
750
751     }
752
753 }
754
755

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags