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