Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 30739 invoked from network); 16 Feb 2005 18:13:12 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur-2.apache.org with SMTP; 16 Feb 2005 18:13:12 -0000 Received: (qmail 93007 invoked by uid 500); 16 Feb 2005 18:13:11 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 92967 invoked by uid 500); 16 Feb 2005 18:13:10 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: list-post: List-Id: Reply-To: "Derby Development" Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 92953 invoked by uid 99); 16 Feb 2005 18:13:10 -0000 X-ASF-Spam-Status: No, hits=2.0 required=10.0 tests=DNS_FROM_RFC_ABUSE,DNS_FROM_RFC_POST,UPPERCASE_25_50 X-Spam-Check-By: apache.org Received-SPF: neutral (hermes.apache.org: local policy) Received: from e33.co.us.ibm.com (HELO e33.co.us.ibm.com) (32.97.110.131) by apache.org (qpsmtpd/0.28) with ESMTP; Wed, 16 Feb 2005 10:13:04 -0800 Received: from d03relay04.boulder.ibm.com (d03relay04.boulder.ibm.com [9.17.195.106]) by e33.co.us.ibm.com (8.12.10/8.12.9) with ESMTP id j1GID10D326546 for ; Wed, 16 Feb 2005 13:13:01 -0500 Received: from d03av04.boulder.ibm.com (d03av04.boulder.ibm.com [9.17.195.170]) by d03relay04.boulder.ibm.com (8.12.10/NCO/VER6.6) with ESMTP id j1GICtdj317962 for ; Wed, 16 Feb 2005 11:12:55 -0700 Received: from d03av04.boulder.ibm.com (loopback [127.0.0.1]) by d03av04.boulder.ibm.com (8.12.11/8.12.11) with ESMTP id j1GICtlG013197 for ; Wed, 16 Feb 2005 11:12:55 -0700 Received: from [127.0.0.1] (Abrown.svl.ibm.com [9.30.40.204]) by d03av04.boulder.ibm.com (8.12.11/8.12.11) with ESMTP id j1GICq85012935 for ; Wed, 16 Feb 2005 11:12:54 -0700 Message-ID: <42138D36.4010405@sbcglobal.net> Date: Wed, 16 Feb 2005 10:13:10 -0800 From: Army User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.1) Gecko/20040707 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Derby Development Subject: [PATCH] Derby-107, Phase III Content-Type: multipart/mixed; boundary="------------030605000307010209040601" X-Virus-Checked: Checked X-Spam-Rating: minotaur-2.apache.org 1.6.2 0/1000/N This is a multi-part message in MIME format. --------------030605000307010209040601 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit For background on this patch and for the "Phase I" and "Phase II" patches that precede this one, please see the following emails: http://mail-archives.eu.apache.org/mod_mbox/db-derby-dev/200502.mbox/%3c4202699E.3000009@golux.com%3e http://mail-archives.eu.apache.org/mod_mbox/db-derby-dev/200502.mbox/%3c421240C1.6040806@sbcglobal.net%3e *** NOTE TO COMMITTERS: *** This patch is BASED ON THE PHASE I AND PHASE II PATCHES. Therefore, please do NOT commit this patch until after the Phase I and Phase II patches have been committed. I posted the Phase I patch to derby-dev on Feb 3rd and the (revised) Phase II patch on February 15th; they can be referenced at the links given above. I have run the derbyall test suite with these changes, using Windows with Sun JDK 1.4.2, and have included the relevant master updates as part of the patch. I have also reworked the "metadata.java" test and extended it with a new test, "odbc_metadata.java", for purposes of verifying the ODBC metadata behavior. ---- Attached is a patch for Phase III (the final phase) of my proposal for fixing Derby-107: "Phase III) Submit a patch that will automatically generate ODBC metadata statements at Derby build time, and that will add support for such metadata statements in the Derby engine (so that ODBC clients using Derby Network Server can use a Derby database)." -- Pasted from my first email referenced above. Following is a detailed description of what all this patch does, for those interested. I) New Files ------------ This patch adds the following new files to the Derby codeline: 1. java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java This is the primary class for generating ODBC-compliant metadata queries. It reads the existing, JDBC metadata queries from "java/org/apache/derby/impl/jdbc/metadata.properties" and writes all of those queries back out to a new file called "odbc_metadata.properties". Then, where appropriate, this class duplicates the JDBC queries and makes alterations to them in order to create ODBC-compliant versions. These ODBC-compliant queries are written to odbc_metadata.properties, as well, so that in the end, ALL metadata queries (JDBC and OBDC alike) have been written to that output file. At build time, an ant process executes this class and then clobbers the existing (JDBC only) version of metadata.properties (the copy in the CLASSES directory, not the copy in the source directory) with the contents of the new output file, "odbc_metadata.properties". The result is that metadata.properties as it exists in the _build_ directory will contain both JDBC and, where needed, ODBC metadata queries, and both sets of queries will be loaded as internal prepared statements for the Derby engine at database creation time. NOTE: This class is ONLY used at build time, and does NOT end up in the org.apache.derby.* package, so it will not be included in any Derby jar files. 2. java/build/org/apache/derbyBuild/odbcgen_fragments.properties This is a helper file for the ODBCMetadataGenerator class. This file contains SQL fragments that are specific to the ODBC metadata queries, and thus it does NOT (and probably shouldn't ever) contain any SQL that already exists in the source copy of "metadata.properties"--otherwise, we'd end up having to maintain two copies of the overlapping metadata SQL, which would defeat the whole purpose of automatic query generation. SQL fragments in this file are pulled into the ODBC queries as part of the ODBCMetadataGenerator's work. While the fragments in this file do eventually end up in the build copy of metadata.properties file, the file itself is ONLY used at build time, and so it does NOT end up in the org.apache.derby.* package. 3. java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata_test.java In order to avoid having redundant code, I created this abstract class to be used as the basis for JDBC and ODBC metadata tests. This class contains the primary "runTest()" method that used to be in the old metadata.java class, modified to call two new abstract methods. The old metadata.java class, then, extends this new class and implements the abstract methods in a way that is functionally equivalent to the old, JDBC-only metadata test. 4. java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/odbc_metadata.java This is a new test, added to the suite for purposes of verifying ODBC metadata behavior. It extends the new file metadata_test.java (mentioned above) and implements that class's abstract methods in a way that allows us to test the ODBC versions of the metadata queries. This class does everything that is done for the "metadata.java" test, plus it performs some other ODBC compliance checks, so that any future changes which break compliance will (hopefully) be easily detected. 5. java/testing/org/apache/derbyTesting/functionTests/master/odbc_metadata.out A new master file to correlate with the new odbc_metadata.java test described above. 6. java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/odbc_metadata.out A new master file to correlate with the new odbc_metadata.java test described above. II) Build Modifications ----------------------- I modified two build.xml files as well as the tools/ant/properties/dir.properties file. These modifications lead to the following additions to the build process: 1. When the java/build directory is built, ODBCMetadataGenerator is compiled into the classes/org/apache/derbyBuild directory, and the "odbcgen_fragments.properties" file is copied to that location, as well. 2. When the java/drda directory is built, all of the following things happen: -- The copy of metadata.properties in the OUTPUT (classes) directory is deleted (so that we can get a fresh copy). -- The SOURCE copy of metadata.properties is copied to the OUTPUT (classes) directory; this is our "fresh copy". -- The ODBCMetadataGenerator class executes, using the "fresh copy" of metadata.properties as input, and creating the odbc_metadata.properties file as output. -- The odbc_metadata.properties file is renamed to metadata.properties in the OUTPUT directory. NOTE: The above things only happen if at least one of the following files has been modified: metadata.properties, ODBCMetadataGenerator.java, or odbcgen_fragments.properties. III) Engine Changes for ODBC Metadata Support --------------------------------------------- The changes described in I and II above allow Derby to automatically generate ODBC-compliant versions of the metadata queries at compile-time, so that, when the build is done, there are both JDBC metadata queries and ODBC metadata queries available in the system. That done, the changes discussed in this section are what allow the Derby engine, at run-time, to determine which version of a query it needs to execute. The type of result set required (JDBC or ODBC) is determined by an "OPTIONS" parameter that is passed into a set of Derby metadata system procedures. If this OPTIONS parameter contains the exact string "DATATYPE='ODBC'", then the result set that is returned will comply with the ODBC specification (meaning that Derby will execute the ODBC version of the corresponding metadata query). If that string doesn't exist in the OPTIONS parameter, the result set will default to JDBC. Derby in embedded mode never passes "DATATYPE='ODBC'" as an OPTION to the system procedures. However, if a user does an explicit CALL of the system procedure when running embedded Derby, and s/he passes "DATATYPE='ODBC'" as a parameter, then the result will in fact be ODBC compliant. This is how the odbc_metadata.java test (mentioned above) works. In server mode, it is the client who sends in the OPTION parameter. For example, both JCC (for JDBC clients) and the DB2 Runtime Client (for ODBC clients) pass OPTIONS parameters when calling the Derby system procedures, and those values are used to determine runtime behavior. In this case, the client must pass in "DATATYPE='ODBC'" when it calls the Derby system procedure if it wants to retrieve metadata that complies with the ODBC specification. The Derby class that processes the OPTIONS parameter is java/engine/org/apache/derby/catalog/SystemProcedures.java. Thus, it is in this class that we parse the OPTIONS parameter and search for the ODBC datatype indicator. Based on whether or not the ODBC string was found, SystemProcedures either calls the normal, JDBC metadata methods as defined in java.sql.DatabaseMetadata, or else it calls new, Derby-only methods that I have added specifically for returning ODBC result sets. In order to accommodate these new ODBC methods, I made the following changes to the Derby EmbedDatabaseMetaData class for each method that needs to support different result sets for JDBC and ODBC. The running example here is "getColumns". 1. I moved the code in the existing method (ex. "getColumns()") into a new private method (ex. "doGetCols()") that takes as an additional parameter the name of the metadata query to execute. 2. I then changed the existing JDBC methods (ex. "getColumns()") to call the new private method and to pass in the regular, JDBC query name (in this case, "getColumns"). 3. Finally, I added a new method (ex. "getColumnsForODBC()") that also calls the new private method, but this method passes in the new ODBC query name (in this case, "odbc_getColumns"). It is this new method that will be called by SystemProcedures if the "DATATYPE='ODBC'" string is received as an OPTIONS parameter. I made the above changes to EmbedDatabaseMetaData.java in order to avoid duplicate code as much as possible. IV) Test Changes ---------------- As mentioned in section I above, I created a new test, "odbc_metadata.java", that will allow developers to verify that the changes they make haven't broken the ODBC query generation and/or execution process. I added this new test to run as part of the following three suites: "derbylang", "derbynetmats", and "j9derbynetmats". I hope that's enough detail for anyone interested ;) If you want to learn more, feel free to review the attached patch. And of course, if anyone has any issues/comments, please do let me know. ---- I have successfully applied this patch (AFTER applying the Phase I and Phase II patches) and have run the "derbyall" suite on Windows 2000 with Sun JDK 1.4.2--with no failures. Thanks, Army --------------030605000307010209040601 Content-Type: text/plain; name="derby-107.III.patch" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="derby-107.III.patch" Index: tools/ant/properties/dirs.properties =================================================================== --- tools/ant/properties/dirs.properties (revision 153312) +++ tools/ant/properties/dirs.properties (working copy) @@ -23,6 +23,7 @@ loc.dir=${out.dir}/org/apache/derby/loc drdaloc.dir=${out.dir}/org/apache/derby/loc/drda jarsdist.dir=${basedir}/jars +metadata.out.dir=${out.dir}/org/apache/derby/impl/jdbc # # OpenSource dirs @@ -37,6 +38,7 @@ derby.build.src.dir=${derbysrc.dir}/build derby.demo.src.dir=${derbysrc.dir}/demo derby.testing.src.dir=${derbysrc.dir}/testing +derby.metadata.src.dir=${derby.engine.src.dir}/org/apache/derby/impl/jdbc derby.engine.dir=${derby.engine.src.dir}/${derby.dir} derby.drda.dir=${derby.drda.src.dir}/${derby.dir} Index: java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java =================================================================== --- java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java (revision 0) +++ java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java (revision 0) @@ -0,0 +1,1207 @@ +/* + + Derby - Class org.apache.derby.catalog.ODBCProcedureColsVTI + + Copyright 2000, 2004 The Apache Software Foundation or its licensors, as applicable. + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + + */ + +package org.apache.derbyBuild; + +import java.io.IOException; +import java.io.InputStream; +import java.io.InputStreamReader; +import java.io.FileWriter; + +import java.util.Properties; +import java.util.HashMap; +import java.util.ArrayList; + +import org.apache.derby.iapi.services.sanity.SanityManager; + +/* **** + * This class is used at COMPILE TIME ONLY. It is responsible for generating + * ODBC metadata queries based on existing JDBC queries. In a word, + * this class reads from the org/apache/derby/impl/jdbc/metadata.properties + * file (which is where the JDBC queries are stored), and for each query, + * performs the changes/additions required to make it comply with ODBC + * standards. The generated ODBC queries are written to an output file + * that is then used, at build time, to create a full set of both JDBC and + * ODBC queries, all of which are then loaded into the database system + * tables at creation time. + * + * For more on the ODBC specification of the metadata methods in question, + * see: + * + * "http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/ + * htm/odbcsqlprocedures.asp" + * + * For more on how the generated queries are used at execution time, see + * EmbedDatabaseMetadata.java and SystemProcedures.java in the codeline. + * + */ +public class ODBCMetadataGenerator { + + // Types of changes that are possible. There are three + // types that we handle here: + // + // 1. Column rename: + // Rename a column to have an ODBC-specified name. + // For ex. change "SCALE" to "DECIMAL_DIGITS" + // 2. Type and/or value change: + // Cast a column to an OBDC-specified type. At time + // of writing, this was just for casting INTs to + // SMALLINTs; OR modify an existing JDBC value + // to match the ODBC specification. + // 3. Additional column(s): + // Add a new, ODBC-specified column to an existing + // result set. + private final byte COL_RENAME_CHANGE = 0x01; + private final byte TYPE_VALUE_CHANGE = 0x02; + private final byte ADD_COLUMN_CHANGE = 0x04; + + // Notice written before each generated ODBC statement. + private final String ODBC_QUERY_NOTICE = + "#\n# *** NOTE! *** The following query was generated\n" + + "# AUTOMATICALLY at build time based on the existing\n" + + "# JDBC version of the query. DO NOT MODIFY this\n" + + "# generated query by hand. Instead, modify either\n" + + "# 1) the JDBC version of the query in the codeline \n" + + "# file \"metadata.properties\" (which will then get\n" + + "# propagated at build time), 2) the relevant SQL\n" + + "# fragments in 'odbcgen_fragments.properties' in\n" + + "# the codleine, or 3) the ODBCMetadataGenerator\n" + + "# class in the org/apache/derbyBuild directory.\n"; + + // Prefix to append to all ODBC queries. NOTE: if you change + // this value, you'll have to modify EmbedDatabaseMetadata.java + // to reflect the change. + private final String ODBC_QUERY_PREFIX = "odbc_"; + + // Name to use when making JDBC queries into subqueries + // (loaded from odbcFragments). NOTE: if you change this value, + // you'll have to modify "odbcgen_fragments.properties" to + // reflect the change. + private final String SUBQUERY_NAME = "JDBC_SUBQUERY"; + + // Mock value used to accomplish insertion of new columns. + private final String NEW_COL_PLACEHOLDER = "COLUMN_POSITION_HOLDER"; + + // Used for trimming 'whitespace'. + private final short FOLLOWING = 1; + private final short PRECEDING = -1; + + // List of what types of changes are required for a given + // metadata procedure. + private HashMap changeMap; + + // SQL fragments and keywords that are used in composing + // ODBC metadata queries. These are loaded from a file + // once and then used throughout the generation process + // to build the ODBC queries piece-by-piece. + private Properties odbcFragments; + + // Output file; all processed statements are written to this + // file. At BUILD TIME, this file will clobber the copy of + // "metadata.properties" that is in the BUILD/CLASSES + // directory. NOTE: this will NOT clobber the metadata + // properties file that is in the SOURCE/CODELINE. + private FileWriter odbcMetaFile; + + /* **** + * Constructor. + * Initializes SQL fragments used for generation, and + * then opens the output file, + */ + public ODBCMetadataGenerator() throws IOException { + + // SQL fragments. + odbcFragments = new Properties(); + odbcFragments.load(this.getClass().getResourceAsStream( + "odbcgen_fragments.properties")); + + // Prep output file. + odbcMetaFile = new FileWriter("odbc_metadata.properties"); + + } + + /* **** + * main: + * Open the metadata.properties file (the copy that is in the + * build directory, NOT the one in the source directory), + * figure out what changes are needed for the various metadata + * queries, and then generate the ODBC-compliant versions + * where needed. + * @param args Ignored. + * @return ODBC-compliant metadata statements have been + * generated and written out to "odbc_metadata.properties" + * in the running directory. + */ + public static void main(String [] args) throws IOException { + + ODBCMetadataGenerator odbcGen = new ODBCMetadataGenerator(); + odbcGen.initChanges(); + odbcGen.generateODBCQueries(odbcGen.getClass().getResourceAsStream( + "/org/apache/derby/impl/jdbc/metadata.properties")); + + } + + /* **** + * initChanges + * Create a listing of the types of changes that need to be + * made for each metadata query to be ODBC-compliant. + * If a metadata query has no entry in this map, then + * it is left unchanged and no ODBC-version will be created. + * Having this mapping allows us to skip over String + * parsing (which can be slow) when it's not required. + * For details on the changes, see the appropriate methods + * below. + * @return Map holding the list of changes to be made for + * each metadata query has been initialized. + */ + private void initChanges() { + + changeMap = new HashMap(); + + changeMap.put("getProcedures", + new Byte(COL_RENAME_CHANGE)); + + changeMap.put("getProcedureColumns", + new Byte((byte)(COL_RENAME_CHANGE + | TYPE_VALUE_CHANGE + | ADD_COLUMN_CHANGE))); + + changeMap.put("getColumns", + new Byte(TYPE_VALUE_CHANGE)); + + changeMap.put("getVersionColumns", + new Byte(TYPE_VALUE_CHANGE)); + + changeMap.put("getBestRowIdentifierPrimaryKeyColumns", + new Byte(TYPE_VALUE_CHANGE)); + + changeMap.put("getBestRowIdentifierUniqueKeyColumns", + new Byte(TYPE_VALUE_CHANGE)); + + changeMap.put("getBestRowIdentifierUniqueIndexColumns", + new Byte(TYPE_VALUE_CHANGE)); + + changeMap.put("getBestRowIdentifierAllColumns", + new Byte(TYPE_VALUE_CHANGE)); + + changeMap.put("getPrimaryKeys", + new Byte(TYPE_VALUE_CHANGE)); + + changeMap.put("getTypeInfo", + new Byte((byte)(COL_RENAME_CHANGE + | TYPE_VALUE_CHANGE + | ADD_COLUMN_CHANGE))); + + changeMap.put("getIndexInfo", + new Byte(TYPE_VALUE_CHANGE)); + + return; + + } + + /* **** + * generateODBCQueries: + * Reads the existing (JDBC) metadata queries from + * metadata.properties and, for each one, makes a call + * to generate an ODBC-compliant version. + * @param is InputStream for reading metadata.properties. + */ + public void generateODBCQueries(InputStream is) + throws IOException + { + + // JDBC query that we read from metadata.properties. + StringBuffer query = new StringBuffer(); + + // We assume no single line/query is greater than 1K in + // length, and we'll fail if this isn't the case. The + // limit of 1K was just picked arbitrarily; this can be + // increased if needed at a later time. + char [] line = new char[1024]; + + for (int count = readLine(is, line); + count != -1; count = readLine(is, line)) + { + + if (count == 0) + // blank line; ignore + continue; + else if (line[0] == '#') { + // comment; write it to file. + odbcMetaFile.write(line, 0, count); + odbcMetaFile.write("\n"); + continue; + } + + // Verify that we haven't passed our limit. + if (count >= line.length) { + throw new IOException( + "Encountered line longer than expected when reading metadata " + + "file; either shorten the line, or increase the limit..."); + } + + // "+1" in next line because we added a "\n" at the end and + // we want to include that, for sake of easier reading. + query.append(line, 0, count+1); + + if (line[count-1] == '\\') + // then continue building the query. + continue; + + // Take the query and see if we need to generate an ODBC- + // compliant version. + generateODBCQuery(query); + + // Prep for another query. + query.delete(0, query.length()); + + } + + // Make sure we didn't end up with an incomplete query somewhere. + if (query.length() > 0) { + throw new IOException( + "Encountered non-terminated query while reading metadata file."); + } + + // Close out. + odbcMetaFile.flush(); + odbcMetaFile.close(); + + } + + /* **** + * generateODBCQuery + * Takes a specific JDBC query, writes it to the output file, + * and then creates an ODBC-compliant version of that + * query (if needed) and writes that to the output file, + * as well. + * @param queryText SQL text from a JDBC metadata query + * that was read from metadata.properties. + */ + private void generateODBCQuery(StringBuffer queryText) + throws IOException + { + + // Create a string for purposes of using "indexOf" + // calls, which aren't allowed on a StringBuffer + // for JDBC 2.0. + String queryAsString = queryText.toString().trim(); + + if (queryAsString.startsWith(ODBC_QUERY_PREFIX)) + // this query was automatically generated (presumably + // by this class), so ignore it now. + return; + + // Write the original (JDBC) query. + odbcMetaFile.write(queryAsString, 0, queryAsString.length()); + odbcMetaFile.write("\n\n"); + + // Parse out the name of this particular query. + int pos = queryAsString.indexOf("="); + if (pos == -1) { + throw new IOException( + "Failed to extract query name from a JDBC metadata query."); + } + String queryName = queryText.substring(0, pos); + + // Parse out the ORDER BY clause since they are not allowed + // in subqueries; we'll re-attach it later. + String orderBy = ""; + int orderByPos = queryAsString.lastIndexOf("ORDER BY"); + if (orderByPos != -1) + orderBy = queryAsString.substring(orderByPos, queryAsString.length()); + + // Isolate query text (remove ORDER BY clause and then query name, + // in that order). + if (orderByPos != -1) + queryText.delete(orderByPos, queryText.length()); + queryText.delete(0, pos+1); + + // Three types of modifications that we may need to do. + + // -- #1: Column renaming. + StringBuffer outerQueryText = new StringBuffer(); + boolean haveODBCChanges = renameColsForODBC(queryName, queryText); + + // Get a list of the column definitions in the subquery, for + // use by subsequent operations. + ArrayList colDefs = new ArrayList(); + pos = getSelectColDefinitions(queryText, colDefs); + + // In some cases, we need to add "helper" columns to the + // subquery so that we can use them in calculations for + // the outer query. + addHelperColsToSubquery(queryName, queryText, pos); + + // -- #2.A: Prep to add new ODBC columns. Note: we need + // to do this BEFORE we generate the outer SELECT statement. + markNewColPosition(queryName, colDefs); + + // If we're going to use a subquery, generate the outer + // SELECT statement. This is where we enforce column + // types (via CAST) if needed. + generateSELECTClause(queryName, colDefs, outerQueryText); + + // -- #3: Alter column values, where needed. + changeValuesForODBC(queryName, outerQueryText); + + // -- #2.B: Add new ODBC columns. + addNewColumnsForODBC(queryName, outerQueryText); + + haveODBCChanges = (haveODBCChanges || (outerQueryText.length() > 0)); + if (!haveODBCChanges) + // we didn't change anything, so nothing left to do. + return; + + // Write out the new, ODBC version of the query. + + odbcMetaFile.write(ODBC_QUERY_NOTICE); + odbcMetaFile.write(ODBC_QUERY_PREFIX); + odbcMetaFile.write(queryName); + odbcMetaFile.write("="); + + if (outerQueryText.length() == 0) { + // all we did was change column names, so just write out the + // original query with the new column names. + odbcMetaFile.write(queryText.toString()); + odbcMetaFile.write("\n\n"); + return; + } + + // Else, we need to make the original query a subquery so that we + // can change types/values and/or add columns. + queryAsString = queryText.toString().trim(); + odbcMetaFile.write(outerQueryText.toString()); + odbcMetaFile.write(queryAsString); + if (queryText.charAt(queryAsString.length()-1) == '\\') + odbcMetaFile.write("\n\\\n) "); + else + odbcMetaFile.write(" \\\n\\\n) "); + odbcMetaFile.write(SUBQUERY_NAME); + if (orderBy.length() == 0) + odbcMetaFile.write("\n"); + else { + // re-attach ORDER BY clause. + odbcMetaFile.write(" \\\n"); + odbcMetaFile.write(orderBy); + } + odbcMetaFile.write("\n\n"); + return; + + } + + /* **** + * renameColsForODBC + * Renames any columns in the received query so that they are + * ODBC-compliant. + * @param queryName Name of the query being processed. + * @param queryText Text of the query being processed. + * @return All columns requiring renaming have been renamed IN + * PLACE in the received StringBuffer. True is returned if + * at least one column was renamed; false otherwise. + */ + private boolean renameColsForODBC(String queryName, StringBuffer queryText) { + + // If we know the received query doesn't have any columns to + // be renamed, then there's nothing to do here. + if (!stmtNeedsChange(queryName, COL_RENAME_CHANGE)) + return false; + + // Which columns are renamed, and what the new names are, + // depends on which query we're processing. + + if (queryName.equals("getProcedures")) { + renameColForODBC(queryText, "RESERVED1", "NUM_INPUT_PARAMS"); + renameColForODBC(queryText, "RESERVED2", "NUM_OUTPUT_PARAMS"); + renameColForODBC(queryText, "RESERVED3", "NUM_RESULT_SETS"); + return true; + } + else if (queryName.equals("getProcedureColumns")) { + renameColForODBC(queryText, "PRECISION", "COLUMN_SIZE"); + renameColForODBC(queryText, "LENGTH", "BUFFER_LENGTH"); + renameColForODBC(queryText, "SCALE", "DECIMAL_DIGITS"); + renameColForODBC(queryText, "RADIX", "NUM_PREC_RADIX"); + return true; + } + else if (queryName.equals("getTypeInfo")) { + renameColForODBC(queryText, "PRECISION", "COLUMN_SIZE"); + renameColForODBC(queryText, "AUTO_INCREMENT", "AUTO_UNIQUE_VAL"); + return true; + } + + // No renaming was necessary. + return false; + + } + + /* **** + * renameColForODBC + * Searches for the old column name in the received String + * buffer and replaces it with the new column name. Note + * that we only replace the old column name where it is + * preceded by "AS", because this is the instance that + * determines the column name in the final result set. + * @param queryText The query text in which we're doing the + * rename operation. + * @param oldVal The old column name. + * @param newVal The new column name. + * @return Occurence of <"AS " + oldVal> in the query text + * has been changed IN PLACE to newVal. + */ + private void renameColForODBC(StringBuffer queryText, + String oldVal, String newVal) + { + + String queryString = queryText.toString(); + int pos = queryString.indexOf(oldVal); + while (pos != -1) { + + // Next line will set pos2 to be the index of the + // first (reading left-to-right) ignorable char + // preceding the old column name. That means + // that the letters immediately preceding this + // position should be "AS". If not, don't + // replace this instance. + int pos2 = trimIgnorable(PRECEDING, queryString, pos); + if (((pos2 - 2) > 0) && (queryString.charAt(pos2-2) == 'A') + && (queryString.charAt(pos2-1) == 'S')) + { // then this is the one we want to replace. + break; + } + else { + // look for next occurrence. + pos = queryString.indexOf(oldVal, pos+1); + } + + } + + if (pos == -1) { + // couldn't find the one to replace; leave unchanged. + return; + } + + // Do the renaming. + queryText.replace(pos, pos + oldVal.length(), newVal); + + } + + /* **** + * generateSELECTClause + * Generates an outer SELECT clause that is then wrapped around a + * JDBC query to change the types and/or values of the JDBC + * result set. The JDBC query thus becomes a subquery. + * + * Ex. if we have a JDBC query "SELECT A, B FROM T1" and ODBC + * requires that "A" be a smallint, this method will generate + * a select clause "SELECT CAST (T2.A AS SMALLINT), T2.B FROM" + * that is then used to wrap the JDBC query, as follows: + * + * SELECT CAST (T2.A AS SMALLINT), T2.B FROM + * (SELECT A, B FROM T1) T2 + * + * @param queryName Name of the query being processed. + * @param selectColDefs Array list of the SELECT columns that + * exist for the JDBC version of the query. For the above + * example, this would be an array list with two String + * elements, "A" and "B". + * @param newQueryText StringBuffer to which the generated + * outer SELECT will be appended. + * @return An outer SELECT clause has been generated and + * appended to the received buffer. The "FROM" keyword + * has been appended, but the subquery itself is NOT + * added here. + */ + private void generateSELECTClause(String queryName, + ArrayList selectColDefs, StringBuffer newQueryText) + { + + if (!stmtNeedsChange(queryName, TYPE_VALUE_CHANGE) && + !stmtNeedsChange(queryName, ADD_COLUMN_CHANGE)) + { // then we don't need to generate a SELECT, because we + // don't need to use a subquery (we're only renaming). + return; + } + + // Begin the SELECT clause. + newQueryText.append("SELECT \\\n\\\n"); + + // For each of the SELECT columns in JDBC, either + // just grab the column name and use it directly in + // the generated clause, or else cast the column + // to the required type, if appropriate. + String colName; + String castInfo; + for (int i = 0; i < selectColDefs.size(); i++) { + if (i > 0) + newQueryText.append(", \\\n"); + colName = extractColName((String)selectColDefs.get(i)); + castInfo = getCastInfoForCol(queryName, colName); + if (castInfo != null) + newQueryText.append("CAST ("); + newQueryText.append(SUBQUERY_NAME); + newQueryText.append("."); + newQueryText.append(colName); + if (castInfo != null) { + newQueryText.append(" AS "); + newQueryText.append(castInfo); + newQueryText.append(")"); + } + if (!colName.equals(NEW_COL_PLACEHOLDER)) { + // don't append the "AS" clause if this is just our + // place-holder for adding new columns. + newQueryText.append(" AS "); + newQueryText.append(colName); + } + } + + if (newQueryText.charAt(newQueryText.length() - 1) != '\\') + newQueryText.append(" \\"); + + // End the SELECT clause. + newQueryText.append("\nFROM ( "); + return; + + } + + /* **** + * changeValuesForODBC + * Searches for a JDBC column name in the received String + * buffer and replaces the first occurrence with an ODBC- + * compliant value. This method determines what specific + * columns need updated values for a given query, and then + * makes the appropriate call for each column. + * @param queryName Name of the query being processed. + * @param newQueryText The query text in which we're doing the + * change-value operation. + * @return All relevant columns have been updated IN PLACE + * to return the required ODBC-compliant values. + */ + private void changeValuesForODBC(String queryName, + StringBuffer newQueryText) + { + + if (!stmtNeedsChange(queryName, TYPE_VALUE_CHANGE)) + return; + + // Which column values are changed, and what the new + // values are, depends on which query we're processing. + + if (queryName.equals("getColumns")) { + changeColValueToODBC(queryName, "BUFFER_LENGTH", newQueryText); + changeColValueToODBC(queryName, "DECIMAL_DIGITS", newQueryText); + changeColValueToODBC(queryName, "NUM_PREC_RADIX", newQueryText); + changeColValueToODBC(queryName, "SQL_DATA_TYPE", newQueryText); + changeColValueToODBC(queryName, "SQL_DATETIME_SUB", newQueryText); + changeColValueToODBC(queryName, "CHAR_OCTET_LENGTH", newQueryText); + } + else if (queryName.startsWith("getBestRowIdentifier")) { + changeColValueToODBC(queryName, "BUFFER_LENGTH", newQueryText); + changeColValueToODBC(queryName, "DECIMAL_DIGITS", newQueryText); + } + else if (queryName.equals("getTypeInfo")) { + changeColValueToODBC(queryName, "NUM_PREC_RADIX", newQueryText); + changeColValueToODBC(queryName, "SQL_DATA_TYPE", newQueryText); + changeColValueToODBC(queryName, "SQL_DATETIME_SUB", newQueryText); + changeColValueToODBC(queryName, "UNSIGNED_ATTRIBUTE", newQueryText); + changeColValueToODBC(queryName, "AUTO_UNIQUE_VAL", newQueryText); + } + else if (queryName.equals("getProcedureColumns")) { + changeColValueToODBC(queryName, "NUM_PREC_RADIX", newQueryText); + changeColValueToODBC(queryName, "DECIMAL_DIGITS", newQueryText); + } + + } + + /* **** + * changeColValueToODBC + * Searches for the received column name in the received String + * buffer and replaces it with an ODBC-compliant value. + * @param queryName Name of the query being processed. + * @param colName Name of the specific column to update. + * @param newQueryText The query text in which we're doing + * the change-value operation. + * @return The received column has been updated IN PLACE + * to return the required ODBC-compliant value. + */ + private void changeColValueToODBC(String queryName, String colName, + StringBuffer newQueryText) + { + + colName = SUBQUERY_NAME + "." + colName; + int pos = newQueryText.toString().indexOf(colName); + if (pos == -1) + // column we're supposed to change isn't in the query. + return; + + if (colName.endsWith("CHAR_OCTET_LENGTH")) { + newQueryText.replace(pos, pos + colName.length(), + getFragment("CHAR_OCTET_FOR_ODBC")); + } + else if (colName.endsWith("BUFFER_LENGTH")) { + newQueryText.replace(pos, pos + colName.length(), + getFragment("BUFFER_LEN_FOR_ODBC")); + } + else if (colName.endsWith("SQL_DATA_TYPE")) { + newQueryText.replace(pos, pos + colName.length(), + getFragment("SQL_DATA_TYPE_FOR_ODBC")); + } + else if (colName.endsWith("SQL_DATETIME_SUB")) { + newQueryText.replace(pos, pos + colName.length(), + getFragment("DATETIME_SUB_FOR_ODBC")); + } + else if (colName.endsWith("UNSIGNED_ATTRIBUTE")) { + newQueryText.replace(pos, pos + colName.length(), + getFragment("UNSIGNED_ATTR_FOR_ODBC")); + } + else if (colName.endsWith("AUTO_UNIQUE_VAL")) { + newQueryText.replace(pos, pos + colName.length(), + getFragment("AUTO_UNIQUE_FOR_ODBC")); + } + else if (colName.endsWith("DECIMAL_DIGITS")) { + newQueryText.replace(pos, pos + colName.length(), + getFragment("DECIMAL_DIGITS_FOR_ODBC")); + } + else if (colName.endsWith("NUM_PREC_RADIX")) { + newQueryText.replace(pos, pos + colName.length(), + getFragment("RADIX_FOR_ODBC")); + } + else if (colName.endsWith(NEW_COL_PLACEHOLDER)) { + // This is a special case indication that we need to add new columns. + if (queryName.equals("getProcedureColumns")) { + newQueryText.replace(pos, pos + colName.length(), + getFragment("GET_PROC_COLS_NEW_COLS")); + } + else if (queryName.equals("getTypeInfo")) { + newQueryText.replace(pos, pos + colName.length(), + getFragment("GET_TYPE_INFO_NEW_COLS")); + } + } + + } + + /* **** + * getSelectColDefinitions + * Parses the SELECT clause of a JDBC metadata SQL query + * and returns a list of the columns being selected. For + * example, if the received statement was "SELECT A, + * B AS C, D * 2 FROM T1", this method will return an + * ArrayList with three string elements: 1) "A", 2) "B + * AS C", and 3) "D * 2". + * @param query The query from which we are extracting + * the SELECT columns. + * @param colDefList ArrayList in which we want to + * store the column definitions that we find. + * @return Received ArrayList has one string value for + * each of the columns found in the received query. + * Also, an integer is returned indicating the index + * in the received query of the start of the FROM + * clause, for later use by the calling method. + */ + private int getSelectColDefinitions(StringBuffer queryText, + ArrayList colDefList) + { + + // Create a string for purposes of using "indexOf" + // calls, which aren't allowed on a StringBuffer + // for JDBC 2.0. + String query = queryText.toString().trim(); + char [] queryChars = query.toCharArray(); + + // Move beyond the "SELECT" keyword, if there is one. + int start = query.indexOf("SELECT"); + if (start != -1) + // "+6" in the next line is length of "SELECT". + start += 6; + else + // just start at the first character. + start = 0; + + // Have to read character-by-character in order to + // figure out where each column description ends. + int fromClauseIndex = -1; + int parenDepth = 0; + for (int i = start; i < queryChars.length; i++) { + + if (queryChars[i] == '(') + parenDepth++; + else if (queryChars[i] == ')') + parenDepth--; + else if ((queryChars[i] == ',') && (parenDepth == 0)) { + // this is a naive way of determining the end of a + // column definition (it'll work so long as there are no + // string constants in the query that have commas in them, + // which was true at the time of writing. + colDefList.add(new String(queryChars, start, (i - start)).trim()); + // Skip over non-important whitespace to find start + // of next column definition. Next line will set i to + // just before the next non-whitespace character. + i = trimIgnorable(FOLLOWING, queryChars, i); + start = i + 1; + } + else if (((i+3) < queryChars.length) + && (parenDepth == 0) + && (queryChars[i] == 'F') + && (queryChars[i+1] == 'R') + && (queryChars[i+2] == 'O') + && (queryChars[i+3] == 'M')) + { // this is the end of final column definition; store it + // and then exit the loop, after trimming off non-important + // whitespace. Next line will set i to just after the + // last (reading left-to-right) non-whitespace character + // before the FROM. + i = trimIgnorable(PRECEDING, queryChars, i); + fromClauseIndex = i; + colDefList.add(new String(queryChars, start, (i - start)).trim()); + break; + + } + + } + + return fromClauseIndex; + + } + + /* **** + * addHelperColsToSubquery + * For some of the metadata queries, the ODBC version + * needs to access values that are only available in + * the JDBC subquery. In such cases, we want to add + * those values as additional "helper" columns to + * the subquery result set, so that they can be + * referenced from the new ODBC outer query (without + * requiring a join). For example, assume we have 2 + * tables T1(int i, int j) and T2 (int a), and a + * subquery "SELECT T1.i, T1.j + T2.a from T1, T2)". + * Then we have an outer query that, instead of + * returning "T1.j + T2.a", needs to return the + * value of "2 * T2.a": + * + * SELECT VT.i, 2 * T2.a FROM + * (SELECT T1.i, T1.j + T2.a FROM T1, T2) VT + * + * The above statement WON'T work, because the outer + * query can't see the value "T2.a". So in such a + * a case, this method will add "T2.a" to the list + * of columns returned by the subquery, so that the + * outer query can then access it: + * + * SELECT VT.i, 2 * VT.a FROM + * (SELECT T1.i, T1.j + T2.a, T2.a FROM T1, T2) VT + * + * Which specific columns are added to the subquery + * depends on the query in question. + * + * @param queryName Name of the query in question. + * @param subqueryText text of the subquery in question. + * @param insertPos Index into the received buffer + * marking the position where the helper columns + * should be inserted. + */ + private void addHelperColsToSubquery(String queryName, + StringBuffer subqueryText, int insertPos) + { + + if (queryName.equals("getColumns")) { + subqueryText.insert(insertPos, + getFragment("GET_COLS_HELPER_COLS")); + } + else if (queryName.startsWith("getBestRowIdentifier")) { + subqueryText.insert(insertPos, + getFragment("BEST_ROW_ID_HELPER_COLS")); + } + + } + + /* **** + * extractColName + * Takes a single column definition from a SELECT clause + * and returns only the unqualified name of the column. + * Assumption here is that any column definition we see + * here will either 1) end with an "AS " + * clause, or 2) consist of ONLY a column name, such + * as "A" or "A.B". At the time of writing, these + * assumptions were true for all relevant metadata + * queries. + * + * Ex. If colDef is "A", this method will return "A". + * If colDef is "A.B", this method will return "B". + * If colDef is " AS C", this method + * will return "C". + * + * @param colDef Column definition from which we're + * trying to extract the name. + * @return Name of the column that is referenced in + * the received column definition. + */ + private String extractColName(String colDef) { + + // Find out where the column name starts. + int pos = colDef.lastIndexOf("AS "); + if (pos == -1) { + // we assume that the col def is _just_ a column name, + // so start at the beginning. + pos = 0; + } + else { + // Move beyond the "AS". + pos += 2; + + // Skip any non-important whitespace or backslashes. + char c = colDef.charAt(pos); + while ((c == '\\') || Character.isWhitespace(c)) + c = colDef.charAt(++pos); + } + + // Check to see if it's a qualified name. + int pos2 = colDef.indexOf(".", pos); + if (pos2 == -1) + // it's not a qualified name, so just return it. + return colDef.substring(pos, colDef.length()); + + // Else, strip off the schema and just return the col name. + return colDef.substring(pos2+1, colDef.length()); + + } + + /* **** + * getCastInfoForCol + * Returns the target type for a result set column that + * needs to be cast into an ODBC type. This is usually + * for casting integers to "SMALLINT". + * @param queryName Name of query being processed. + * @param colName Name of the specific column for which + * we are trying to find the target type. + * @return The target type if one exists, or else null + * if the received column in the received query has + * no known target type. + */ + private String getCastInfoForCol(String queryName, + String colName) + { + + if (queryName.equals("getTypeInfo")) { + if (colName.equals("NULLABLE") || + colName.equals("CASE_SENSITIVE") || + colName.equals("SEARCHABLE") || + colName.equals("UNSIGNED_ATTRIBUTE") || + colName.equals("FIXED_PREC_SCALE") || + colName.equals("AUTO_UNIQUE_VAL") || + colName.equals("SQL_DATA_TYPE") || + colName.equals("SQL_DATETIME_SUB") || + colName.equals("MINIMUM_SCALE") || + colName.equals("MAXIMUM_SCALE")) + { + return "SMALLINT"; + } + } + else if (queryName.equals("getColumns")) { + if (colName.equals("DECIMAL_DIGITS") || + colName.equals("NULLABLE") || + colName.equals("NUM_PREC_RADIX") || + colName.equals("SQL_DATA_TYPE") || + colName.equals("SQL_DATETIME_SUB")) + { + return "SMALLINT"; + } + } + else if (queryName.equals("getVersionColumns")) { + if (colName.equals("SCOPE") || + colName.equals("DATA_TYPE") || + colName.equals("DECIMAL_DIGITS") || + colName.equals("PSEUDO_COLUMN")) + { + return "SMALLINT"; + } + } + else if (queryName.equals("getPrimaryKeys")) { + if (colName.equals("KEY_SEQ")) + return "SMALLINT"; + } + else if (queryName.equals("getIndexInfo")) { + if (colName.equals("NON_UNIQUE") || + colName.equals("TYPE") || + colName.equals("ORDINAL_POSITION")) + { + return "SMALLINT"; + } + } + + // No target type for the received column + // in the received query (leave it unchanged). + return null; + + } + + /* **** + * markNewColPosition + * In effect, "marks" the position at which additional + * columns are to be added for ODBC compliance. This + * is accomplished by adding a dummy column name to + * the list of SELECT columns. Later, in the method + * that actually adds the columns, we'll do a find- + * replace on this dummy value. + * @param queryName Name of the query. + * @param selectColDefs Array list of the SELECT + * columns that exist in the ODBC version of + * the query thus far. + * @return A dummy column name has been added to + * the received list of columns at the position + * at which new ODBC columns should be added. + * If a query doesn't require additional + * columns to be ODBC compliant, this method + * leaves the received column list unchanged. + */ + private void markNewColPosition(String queryName, + ArrayList selectColDefs) + { + + if (!stmtNeedsChange(queryName, ADD_COLUMN_CHANGE)) + return; + + if (queryName.equals("getProcedureColumns")) { + // Add the new columns in front of the Derby-specific ones. + // The "-2" in the next line is because there are 2 Derby- + // specific columns in the JDBC version of getProcedureCols + // (PARAMETER_ID and METHOD_ID). + selectColDefs.add(selectColDefs.size() - 2, NEW_COL_PLACEHOLDER); + } + else if (queryName.equals("getTypeInfo")) { + // just add the new column to the end. + selectColDefs.add(NEW_COL_PLACEHOLDER); + } + + } + + /* **** + * addNewColumnsForODBC + * Adds new columns to the ODBC version of a metadata + * query (the ODBC version is at this point being + * built up in newQueryText). Before this method + * was called, a dummy placeholder should have been + * placed in the newQueryText buffer (by a call to + * "markNewColPosition"). This method simply replaces + * that dummy placeholder with the SQL text for the + * new columns. + * @param queryName Name of query being processed. + * @newQueryText The buffer in which we want to + * add the new column. + * @return The dummy placeholder in the received + * buffer has been replaced with any ODBC columns + * that need to be added to the query in question + * for ODBC compliance. + */ + private void addNewColumnsForODBC(String queryName, + StringBuffer newQueryText) + { + + if (!stmtNeedsChange(queryName, ADD_COLUMN_CHANGE)) + return; + + changeColValueToODBC(queryName, NEW_COL_PLACEHOLDER, newQueryText); + + // It's possible that the new column fragments we added + // have placeholders in them for _other_ fragments. We + // need to do the substitution here. + if (queryName.equals("getProcedureColumns")) { + fragSubstitution("SQL_DATA_TYPE_FOR_ODBC", newQueryText); + fragSubstitution("DATETIME_SUB_FOR_ODBC", newQueryText); + } + + return; + + } + + /* **** + * fragSubstitution + * Replaces a single occurrence of the received + * fragment key with the text corresponding to + * that key. + * @param fragKey The fragment key for which we are + * going to do the substitution. + * @queryText The buffer in which we are going to do + * the substitution. + * @return fragKey has been substituted (IN PLACE) + * with the fragment corresponding to it in the + * received buffer. If the fragment key could not + * be found, the buffer remains unchanged. + */ + private void fragSubstitution(String fragKey, + StringBuffer queryText) + { + + int pos = queryText.toString().indexOf(fragKey); + if (pos != -1) { + // NOTE: the " + 1" and " - 1" in the next line + // are needed because the fragment key is + // enclosed within curly braces ("{}"). + queryText.replace(pos - 1, pos + fragKey.length() + 1, + getFragment(fragKey)); + } + + } + + /* **** + * readLine + * Reads a line from the received input stream and stores it + * into the received character array. In this method, we + * consider the end of the line to be either 1) "\n" char, or + * 2) a single backslash "\", which is used in metadata + * queries to indicate line continuation. After reading + * a line, we append an EOL to it for formatting purposes, + * but that last EOL is NOT included in the count of + * characters. + * @param is The input stream from which we're reading. + * @param line The char array into which we're reading. + * @return the number of characters read from the + * stream; -1 if we reached end of the stream. + */ + private int readLine(InputStream is, char [] line) + throws IOException + { + + int count = 0; + boolean atLeastOneNonWSChar = false; + + char ch; + int byteRead; + for (byteRead = is.read(); + (byteRead != -1) && (count < line.length); + byteRead = is.read()) + { + ch = (char)byteRead; + line[count++] = ch; + atLeastOneNonWSChar = true; + if ((ch == '\\') || (ch == '\n')) + break; + } + + if ((byteRead == -1) && (count == 0)) + // end of file. + return -1; + + // Take off trailing whitespace. + while ((count > 0) && Character.isWhitespace(line[count-1])) + count--; + + // Add an EOL for ease of reading, but don't include it in + // "count" total. + line[count] = '\n'; + return count; + + } + + /* **** + * trimIgnorable + * Removes all 'ignorable' chars that immediately precede or + * follow (depending on the direction) the character at + * the received index. "Ignorable" here means whitespace + * OR a single backslash ("\"), which is used in the + * metadata.properties file to indicate line continuation. + * @param direction +1 if we want to trim following, -1 + * if we want to trim preceding. + * @param chars The character array being processed. + * @param index The point before/after which to start + * trimming. + * @return The index into the received char array of the + * "last" ignorable character w.r.t the received index + * and direction. In other words, if we're trimming + * the chars FOLLOWING, the returned index will be of + * the last (reading left-to-right) ignorable char; if + * we're trimming the chars PRECEDING, the returned index + * will be of the first (reading left-to-right) ignorable + * character. + */ + private int trimIgnorable(short direction, char [] chars, int index) { + + index += direction; + while ((index >= 0) && (index < chars.length) && + ((chars[index] == '\\') || + Character.isWhitespace(chars[index]))) + { + index += direction; + } + + // index is now on the final non-ignorable character + // in the given direction. Move it back one so that + // it's on the "last" ignorable character (with + // respect to direction). + index -= direction; + + return index; + + } + + /* **** + * trimIgnorable + * Same as trimIgnorable above, except with String argument + * instead of char[]. + */ + private int trimIgnorable(short direction, String str, int index) { + + index += direction; + while ((index >= 0) && (index < str.length()) && + ((str.charAt(index) == '\\') || + Character.isWhitespace(str.charAt(index)))) + { + index += direction; + } + + // index is now on the final non-ignorable character + // in the given direction. Move it back one so that + // it's on the "first" ignorable character (with + // respect to direction). + index -= direction; + + return index; + + } + + /* **** + * stmtNeedsChange + * Returns whether or not a specific metadata statement + * requires the received type of change. This is determined + * based on the info stored in the "changeMaps" mapping. + * @param queryName Name of the query in question. + * @param changeType The type of change in question. + */ + private boolean stmtNeedsChange(String queryName, byte changeType) { + + Byte changeByte = (Byte)changeMap.get(queryName); + if (changeByte == null) + // No entry means change is not needed. + return false; + + return ((changeByte.byteValue() & changeType) == changeType); + + } + + /* **** + * getFragment + * Looks up an SQL fragment and returns the value as a String. + * @param String fragId id of the fragment to look up. + * @return The string fragment corresponding to the received + * fragment id. + */ + private String getFragment(String fragId) { + return (String)(odbcFragments.get(fragId)); + } + +} Property changes on: java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java ___________________________________________________________________ Name: svn:eol-style + native Index: java/build/org/apache/derbyBuild/odbcgen_fragments.properties =================================================================== --- java/build/org/apache/derbyBuild/odbcgen_fragments.properties (revision 0) +++ java/build/org/apache/derbyBuild/odbcgen_fragments.properties (revision 0) @@ -0,0 +1,269 @@ +# +# This file contains SQL fragments that are used as +# part of the ODBC metadata generation process. +# This file is NOT, and is NOT intended to become, +# an ODBC substitute for metadata.properties. +# Rather, it is a place to store fragments of ODBC +# metadata statements that do not apply to JDBC +# metadata, but which are used as part of the ODBC +# metadata generation process (these fragments are +# plugged into the ODBC versions of the queries for +# which they are required). When all is done, +# the fragments in this file will show up as part +# of the generated ODBC metadata queries, which +# will then be appended to Derby's internal list +# of metadata queries (in metadata.properties), +# which will, finally, serve as the basis for +# for both JDBC and ODBC metadata calls. +# +# This file, like the ODBCMetadataGenerator class +# that uses it, is ONLY USED AT BUILD TIME; and +# like the ODBCMetadataGenerator class, it is NOT +# included in the final org.apache.derby package +# (and thus will NOT be included in the Derby jar +# file). +# +# Note: In this file, words between curly brackets +# (ex "{SQL_DATA_TYPE_FOR_ODBC}") are placeholders +# for other fragments in this file. The actual +# substitutions for these placeholders occur as +# part of the ODBCMetadataGenerator class's work. +# +# Finally, note that starting a line in this file +# with the "\\\n" sequence allows formatting (esp. +# tabs and newlines) to be preserved, so that the +# generated ODBC queries are human-readable. + +# ---------- +# +# SQL_DATA_TYPE: +# Set SQL_DATA_TYPE, which is unused by JDBC (and +# thus returns NULL for JDBC), to the value as +# defined by the ODBC specification. Note that +# any metadata statement requiring this fragment +# must already have a column named "DATA_TYPE" +# as part of the JDBC subquery's result set. +# +# Date, time, and timestamp columns are supposed +# to return a generic "SQL_DATETIME" value here; +# that's defined as the value "9". All other +# types return their normal DATA_TYPE value. +# +SQL_DATA_TYPE_FOR_ODBC=\ +CASE WHEN (JDBC_SUBQUERY.DATA_TYPE IN (java.sql.Types::DATE, \ +\\\n java.sql.Types::TIME, java.sql.Types::TIMESTAMP)) \ +\\\n THEN 9 \ +\\\n ELSE JDBC_SUBQUERY.DATA_TYPE END + +# ---------- +# +# SQL_DATETIME_SUB: +# Set SQL_DATETIME_SUB, which is unused by JDBC (and +# thus returns NULL for JDBC), to the value as +# defined by the ODBC specification. Note that +# any metadata statement requiring this fragment +# must already have a column named "DATA_TYPE" +# as part of the JDBC subquery's result set. +# +# This value is null for all types except date, time, +# and timestamp. For those, the values are defined +# as follows: +# +# SQL_CODE_DATE 1 +# SQL_CODE_TIME 2 +# SQL_CODE_TIMESTAMP 3 +# +DATETIME_SUB_FOR_ODBC=\ +CASE WHEN (JDBC_SUBQUERY.DATA_TYPE = java.sql.Types::DATE) \ +\\\n THEN 1 \ +\\\n ELSE (CASE WHEN (JDBC_SUBQUERY.DATA_TYPE = java.sql.Types::TIME) \ +\\\n THEN 2 \ +\\\n ELSE (CASE WHEN (JDBC_SUBQUERY.DATA_TYPE = java.sql.Types::TIMESTAMP) \ +\\\n THEN 3 \ +\\\n ELSE CAST (NULL AS SMALLINT) END ) END ) END + +# ---------- +# +# UNSIGNED_ATTRIBUTE: +# Set UNSIGNED_ATTRIBUTE, which defaults to "true" +# for non-numeric types in JDBC, to be NULL for +# non-numeric types according the definition as +# given in the ODBC specification. Note that +# any metadata statement requiring this fragment +# must already have a column named "DATA_TYPE" +# and a column named "UNSIGNED_ATTRIBUTE" as +# part of the JDBC subquery's result set. +# +UNSIGNED_ATTR_FOR_ODBC=\ +CASE WHEN (JDBC_SUBQUERY.DATA_TYPE IN (java.sql.Types::DECIMAL, \ +\\\n java.sql.Types::NUMERIC, java.sql.Types::INTEGER, \ +\\\n java.sql.Types::SMALLINT, java.sql.Types::TINYINT, \ +\\\n java.sql.Types::BIGINT, java.sql.Types::DOUBLE, \ +\\\n java.sql.Types::FLOAT, java.sql.Types::REAL, \ +\\\n java.sql.Types::DATE, java.sql.Types::TIME, \ +\\\n java.sql.Types::TIMESTAMP)) \ +\\\n THEN JDBC_SUBQUERY.UNSIGNED_ATTRIBUTE \ +\\\n ELSE CAST (NULL AS SMALLINT) END + +# ---------- +# +# AUTO_UNIQUE_VAL: +# Set AUTO_UNIQUE_VAL, which defaults to "false" +# for non-numeric types in JDBC, to be NULL for +# non-numeric types according the definition as +# given in the ODBC specification. Note that +# any metadata statement requiring this fragment +# must already have a column named "DATA_TYPE" +# and a column named "AUTO_UNIQUE_VAL" as +# part of the JDBC subquery's result set. +# +AUTO_UNIQUE_FOR_ODBC=\ +CASE WHEN (JDBC_SUBQUERY.DATA_TYPE IN (java.sql.Types::DECIMAL, \ +\\\n java.sql.Types::NUMERIC, java.sql.Types::INTEGER, \ +\\\n java.sql.Types::SMALLINT, java.sql.Types::TINYINT, \ +\\\n java.sql.Types::BIGINT, java.sql.Types::DOUBLE, \ +\\\n java.sql.Types::FLOAT, java.sql.Types::REAL, \ +\\\n java.sql.Types::DATE, java.sql.Types::TIME, \ +\\\n java.sql.Types::TIMESTAMP)) \ +\\\n THEN JDBC_SUBQUERY.AUTO_UNIQUE_VAL \ +\\\n ELSE CAST (NULL AS SMALLINT) END + +# ---------- +# +# NUM_PREC_RADIX: +# Set NUM_PREC_RADIX, which is "10" for datetime +# values in JDBC, to be "2" for datetime values +# in ODBC, as given in the ODBC specification. +# Note that any metadata statement requiring this +# fragment must already have a column named +# "DATA_TYPE" and a column named "NUM_PREC_RADIX" +# as part of the JDBC subquery's result set. +# +RADIX_FOR_ODBC=\ +CASE WHEN (JDBC_SUBQUERY.DATA_TYPE IN (java.sql.Types::DATE, \ +\\\n java.sql.Types::TIME, java.sql.Types::TIMESTAMP)) \ +\\\n THEN CAST (2 AS SMALLINT) \ +\\\n ELSE JDBC_SUBQUERY.NUM_PREC_RADIX END + +# ---------- +# +# DECIMAL_DIGITS: +# Set DECIMAL_DIGITS to be NULL for DATE columns +# in ODBC, as given in the ODBC specification. +# Note that any metadata statement requiring this +# fragment must already have a column named +# "DATA_TYPE" and a column named "DECIMAL_DIGITS" +# as part of the JDBC subquery's result set. +# +DECIMAL_DIGITS_FOR_ODBC=\ +CASE WHEN (JDBC_SUBQUERY.DATA_TYPE IN (java.sql.Types::DATE)) \ +\\\n THEN CAST (NULL AS SMALLINT) \ +\\\n ELSE JDBC_SUBQUERY.DECIMAL_DIGITS END + +# ---------- +# +# Columns that need to be added to the getProcedureColumns result +# set for ODBC compliance. +# +GET_PROC_COLS_NEW_COLS=\ +CAST (NULL AS VARCHAR(254)) AS COLUMN_DEF, \ +\\\nCAST (({SQL_DATA_TYPE_FOR_ODBC}) AS SMALLINT) \ +\\\n AS SQL_DATA_TYPE, \ +\\\nCAST (({DATETIME_SUB_FOR_ODBC}) AS SMALLINT) \ +\\\n AS SQL_DATETIME_SUB, \ +\\\nCASE WHEN (JDBC_SUBQUERY.DATA_TYPE IN (java.sql.Types::CHAR, \ +\\\n java.sql.Types::VARCHAR, java.sql.Types::BINARY, \ +\\\n java.sql.Types::VARBINARY)) \ +\\\n THEN JDBC_SUBQUERY.BUFFER_LENGTH \ +\\\n ELSE CAST (NULL AS INT) END \ +\\\n AS CHAR_OCTET_LENGTH, \ +\\\nCAST ((JDBC_SUBQUERY.PARAMETER_ID + 1) AS INT) AS ORDINAL_POSITION, \ +\\\nCAST ((CASE WHEN (JDBC_SUBQUERY.NULLABLE IN \ +\\\n (java.sql.DatabaseMetaData::procedureNullable)) \ +\\\n THEN 'YES' \ +\\\n ELSE 'NO' END) \ +\\\n AS VARCHAR(128)) AS IS_NULLABLE + +# ---------- +# +# Columns that need to be added to the getTypeInfo result +# set for ODBC compliance. +# +GET_TYPE_INFO_NEW_COLS=\ +CAST (NULL AS SMALLINT) AS INTERVAL_PRECISION + +# ---------- +# +# In order to correctly determine the BUFFER_LENGTH +# and CHAR_OCTET_LENGTH values for the ODBC version +# of getColumns, we need to retrieve the max width +# value of the column in question. Since this +# specific value isn't returned as part of the JDBC +# metadata, we need to add it as a "helper" column +# to the JDBC subquery result set. See the +# addHelperColsToSubquery method in the ODBC meta- +# data generator class for more details. +# +GET_COLS_HELPER_COLS=, \ +\\\n C.COLUMNDATATYPE.getMaximumWidth() AS COL_MAX_WIDTH + +# ---------- +# +# In order to correctly determine the BUFFER_LENGTH +# value for the ODBC versions of the getBestRow* +# queries, we need to retrieve the max width +# value of the column in question. Since this +# specific value isn't returned as part of the JDBC +# metadata, we need to add it as a "helper" column +# to the JDBC subquery result set. See the +# addHelperColsToSubquery method in the ODBC meta- +# data generator class for more details. +# +BEST_ROW_ID_HELPER_COLS=, \ +\\\n COLS.COLUMNDATATYPE.getMaximumWidth() AS COL_MAX_WIDTH + +# ---------- +# +# BUFFER_LENGTH: +# Set BUFFER_LENGTH, which is unused by JDBC (and +# thus returns NULL for JDBC), to the value as +# defined by the ODBC specification. Note that +# any metadata statement requiring this fragment +# must already have a column named "DATA_TYPE" +# and a "helper" column named "COL_MAX_WIDTH" +# as part of the JDBC subquery's result set. +# +BUFFER_LEN_FOR_ODBC=\ +CASE WHEN (JDBC_SUBQUERY.DATA_TYPE IN (java.sql.Types::CHAR, \ +\\\n java.sql.Types::VARCHAR)) \ +\\\n THEN (CASE WHEN (JDBC_SUBQUERY.COL_MAX_WIDTH * 2.0 > 2147483647) \ +\\\n THEN 2147483647 \ +\\\n ELSE (JDBC_SUBQUERY.COL_MAX_WIDTH * 2) END) \ +\\\n ELSE (CASE WHEN (JDBC_SUBQUERY.COL_MAX_WIDTH > 2147483647) \ +\\\n THEN 2147483647 \ +\\\n ELSE JDBC_SUBQUERY.COL_MAX_WIDTH END) END + +# ---------- +# +# CHAR_OCTET_LENGTH: +# Make CHAR_OCTET_LENGTH, which only applies to +# char cols in JDBC, apply to both char and binary +# columns per the ODBC specification. Note that +# any metadata statement requiring this fragment +# must already have a column named "DATA_TYPE" +# and a "helper" column named "COL_MAX_WIDTH" +# as part of the JDBC subquery's result set. +# +CHAR_OCTET_FOR_ODBC=\ +CASE WHEN (JDBC_SUBQUERY.DATA_TYPE IN (java.sql.Types::CHAR, \ +\\\n java.sql.Types::VARCHAR)) \ +\\\n THEN (CASE WHEN (JDBC_SUBQUERY.COL_MAX_WIDTH * 2.0 > 2147483647) \ +\\\n THEN 2147483647 \ +\\\n ELSE (JDBC_SUBQUERY.COL_MAX_WIDTH * 2) END) \ +\\\n ELSE (CASE WHEN (JDBC_SUBQUERY.DATA_TYPE IN ( \ +\\\n java.sql.Types::BINARY, java.sql.Types::VARBINARY)) \ +\\\n THEN (CASE WHEN (JDBC_SUBQUERY.COL_MAX_WIDTH > 2147483647) \ +\\\n THEN 2147483647 \ +\\\n ELSE JDBC_SUBQUERY.COL_MAX_WIDTH END) \ +\\\n ELSE CAST(NULL AS INT) END) END + Property changes on: java/build/org/apache/derbyBuild/odbcgen_fragments.properties ___________________________________________________________________ Name: svn:eol-style + native Index: java/build/org/apache/derbyBuild/build.xml =================================================================== --- java/build/org/apache/derbyBuild/build.xml (revision 153312) +++ java/build/org/apache/derbyBuild/build.xml (working copy) @@ -20,6 +20,7 @@ + Index: java/drda/build.xml =================================================================== --- java/drda/build.xml (revision 153312) +++ java/drda/build.xml (working copy) @@ -38,9 +38,65 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Index: java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/odbc_metadata.java =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/odbc_metadata.java (revision 0) +++ java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/odbc_metadata.java (revision 0) @@ -0,0 +1,740 @@ +/* + + Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.odbc_metadata + + Copyright 1999, 2004 The Apache Software Foundation or its licensors, as applicable. + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + + */ + +package org.apache.derbyTesting.functionTests.tests.jdbcapi; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.DatabaseMetaData; +import java.sql.ResultSetMetaData; +import java.sql.Statement; +import java.sql.CallableStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Types; +import java.sql.Timestamp; +import java.sql.Time; +import java.sql.Date; +import java.math.BigDecimal; + +import java.util.Properties; + +import org.apache.derby.tools.ij; +import org.apache.derbyTesting.functionTests.util.TestUtil; + +/** + * Test of database metadata for ODBC clients. This test does + * everything that is done in "metadata.java" (which this class + * extends), except that it makes the metadata calls in such a way + * as to retrieve ODBC-compliant result sets. Unlike metadata.java, + * this test also does a (simple) check of the metadata result sets + * to see if they comply with the standards--in this case, with the + * ODBC 3.0 standard as defined at this URL: + * + * http://msdn.microsoft.com/library/default.asp?url=/library/ + * en-us/odbc/htm/odbcsqlprocedurecolumns.asp + * + * The ODBC standards verification involves checking the following + * for each column in each of the relevant metadata result sets: + * + * 1. Does the column name match what the spec says? + * 2. Does the column type match what the spec says? + * 3. Does the column nullability match what the spec says? + * + * If compliance failures occur in any of these ways, an ODBC non- + * compliance failure will be reported as part of the test output. + * + * Under no circumstances should a master file for this test + * contain an ODBC non-compliance message and still be considered + * as "passing". + */ + +public class odbc_metadata extends metadata_test { + + // The following 2-D array holds the target names, + // types, and nullability of metadata result sets + // as defined in the ODBC 3.0 specification. Each + // row in this array corresponds to a SYSIBM + // metadata procedure that is used (by both the + // engine and the Network Server) for retrieval + // of metadata. Each row in turn consists of + // 3 * n strings, where "n" is the number of columns + // expected as part of the result set for that row's + // corresponding SYSIBM procedure. For a given column + // "c" in each row, the expected name for that column + // is at [c], the expected type is at [c+1], + // and the expected nullability is at [c+2]. The + // expected values are hard-coded into this file, and + // are loaded via the loadODBCTargets method. + // + // "15" here is the number of procedure ids defined in + // metadata.java. "25" is a safety figure for the max + // number of columns a single metadata procedure returns + // in its result set. At time of writing, the most + // any procedure had was 19, so use 25 to give us + // some cushion. + + private static String [][] odbcComplianceTargets = + new String [15][25]; + + /** + * Constructor: + * Intializes the Connection and Statement fields + * to be used through the test, and then does the + * first-level check of ODBC compliance. + */ + public odbc_metadata(String[] args) { + + try { + + con = createConnection(args); + s = con.createStatement(); + + // Run the compliance checks for column name and + // column type. This method will load the target + // values that we want to match. + verifyODBC3Compliance(); + + } catch (SQLException e) { + dumpSQLExceptions(e); + } + catch (Throwable e) { + System.out.println("FAIL -- unexpected exception:"); + e.printStackTrace(System.out); + } + + } + + /** + * Makes a call to the "runTest" method in metadata_test.java, + * which will in turn call back here for implementations of + * the abstract methods. + */ + public static void main(String[] args) { + + new odbc_metadata(args).runTest(); + + } + + /** + * This method is responsible for executing a metadata query and returning + * a result set that complies with the ODBC 3.0 specification. + */ + protected ResultSet getMetaDataRS(DatabaseMetaData dmd, int procId, + String [] sArgs, String [] argArray, int [] iArgs, boolean [] bArgs) + throws SQLException + { + + switch (procId) { + + case GET_PROCEDURES: + + s.execute("CALL SYSIBM.SQLPROCEDURES (" + + addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) + + ", " + addQuotes(sArgs[2]) + ", 'DATATYPE=''ODBC''')"); + return s.getResultSet(); + + case GET_PROCEDURE_COLUMNS: + + s.execute("CALL SYSIBM.SQLPROCEDURECOLS (" + + addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) + + ", " + addQuotes(sArgs[2]) + ", " + addQuotes(sArgs[3]) + + ", 'DATATYPE=''ODBC''')"); + return s.getResultSet(); + + case GET_TABLES: + + int count = (argArray == null) ? 0 : argArray.length; + StringBuffer tableTypes = new StringBuffer(); + for (int i = 0; i < count; i++) { + if (i > 0) + tableTypes.append(","); + tableTypes.append(argArray[i]); + } + + s.execute("CALL SYSIBM.SQLTABLES (" + + addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) + + ", " + addQuotes(sArgs[2]) + ", " + + ((argArray == null) ? "null" : addQuotes(tableTypes.toString())) + + ", 'DATATYPE=''ODBC''')"); + + return s.getResultSet(); + + case GET_COLUMNS: + + s.execute("CALL SYSIBM.SQLCOLUMNS (" + + addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) + + ", " + addQuotes(sArgs[2]) + ", " + addQuotes(sArgs[3]) + + ", 'DATATYPE=''ODBC''')"); + return s.getResultSet(); + + case GET_COLUMN_PRIVILEGES: + + s.execute("CALL SYSIBM.SQLCOLPRIVILEGES (" + + addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) + + ", " + addQuotes(sArgs[2]) + ", " + addQuotes(sArgs[3]) + + ", 'DATATYPE=''ODBC''')"); + return s.getResultSet(); + + case GET_TABLE_PRIVILEGES: + + s.execute("CALL SYSIBM.SQLTABLEPRIVILEGES (" + + addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) + + ", " + addQuotes(sArgs[2]) + ", 'DATATYPE=''ODBC''')"); + return s.getResultSet(); + + case GET_BEST_ROW_IDENTIFIER: + + s.execute("CALL SYSIBM.SQLSPECIALCOLUMNS (1, " + + addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) + + ", " + addQuotes(sArgs[2]) + ", " + iArgs[0] + ", " + + (bArgs[0] ? "1, " : "0, ") + "'DATATYPE=''ODBC''')"); + return s.getResultSet(); + + case GET_VERSION_COLUMNS: + + s.execute("CALL SYSIBM.SQLSPECIALCOLUMNS (2, " + + addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) + + ", " + addQuotes(sArgs[2]) + ", 1, 1, 'DATATYPE=''ODBC''')"); + return s.getResultSet(); + + case GET_PRIMARY_KEYS: + + s.execute("CALL SYSIBM.SQLPRIMARYKEYS (" + + addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) + + ", " + addQuotes(sArgs[2]) + ", 'DATATYPE=''ODBC''')"); + return s.getResultSet(); + + case GET_IMPORTED_KEYS: + + s.execute("CALL SYSIBM.SQLFOREIGNKEYS (null, null, null, " + + addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) + + ", " + addQuotes(sArgs[2]) + ", 'IMPORTEDKEY=1;DATATYPE=''ODBC''')"); + return s.getResultSet(); + + case GET_EXPORTED_KEYS: + + s.execute("CALL SYSIBM.SQLFOREIGNKEYS (" + + addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) + + ", " + addQuotes(sArgs[2]) + ", null, null, null, " + + "'EXPORTEDKEY=1;DATATYPE=''ODBC''')"); + return s.getResultSet(); + + case GET_CROSS_REFERENCE: + + s.execute("CALL SYSIBM.SQLFOREIGNKEYS (" + + addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) + + ", " + addQuotes(sArgs[2]) + ", " + addQuotes(sArgs[3]) + + ", " + addQuotes(sArgs[4]) + ", " + addQuotes(sArgs[5]) + + ", 'DATATYPE=''ODBC''')"); + return s.getResultSet(); + + case GET_TYPE_INFO: + + s.execute("CALL SYSIBM.SQLGETTYPEINFO (0, 'DATATYPE=''ODBC''')"); + return s.getResultSet(); + + case GET_INDEX_INFO: + + s.execute("CALL SYSIBM.SQLSTATISTICS (" + + addQuotes(sArgs[0]) + ", " + addQuotes(sArgs[1]) + + ", " + addQuotes(sArgs[2]) + (bArgs[0] ? ", 0, " : ", 1, ") + + (bArgs[1] ? "1, " : "0, ") + "'DATATYPE=''ODBC''')"); + return s.getResultSet(); + + default: + // shouldn't get here. + + System.out.println("*** UNEXPECTED PROCEDURE ID ENCOUNTERED: " + procId + "."); + return null; + + } + + } + + /** + * Dumps a result to output and, if procId is not -1, checks + * to see if the nullability of the result set values conforms + * to the ODBC 3.0 specification. + */ + protected void dumpRS(int procId, ResultSet s) throws SQLException { + + ResultSetMetaData rsmd = s.getMetaData (); + + // Get the number of columns in the result set + int numCols = rsmd.getColumnCount (); + String[] headers = new String[numCols]; + if (numCols <= 0) { + System.out.println("(no columns!)"); + return; + } + + // Display column headings, and include column types + // as part of those headings. + for (int i=1; i<=numCols; i++) { + if (i > 1) System.out.print(","); + headers[i-1] = rsmd.getColumnLabel(i); + System.out.print(headers[i-1]); + System.out.print("[" + rsmd.getColumnTypeName(i) + "]"); + + } + System.out.println(); + + // Display data, fetching until end of the result set + StringBuffer errorColumns; + while (s.next()) { + // Loop through each column, getting the + // column data and displaying + errorColumns = new StringBuffer(); + String value; + for (int i=1; i<=numCols; i++) { + if (i > 1) System.out.print(","); + value = s.getString(i); + if (headers[i-1].equals("DATA_TYPE")) + { + if (((TestUtil.getJDBCMajorVersion(s.getStatement().getConnection()) >= 3) && + (Integer.valueOf(value).intValue() == 16)) || + (Integer.valueOf(value).intValue() == -7)) + System.out.print("**BOOLEAN_TYPE for VM**"); + else + System.out.print(value); + } + else + System.out.print(value); + + // Check ODBC nullability, if required. + if ((procId != IGNORE_PROC_ID) && + badNullability(procId, headers[i-1], i, s.wasNull())) + { + errorColumns.append(headers[i-1]); + } + + } + + if (errorColumns.length() > 0) { + System.out.println( + "\n--> ODBC COMPLIANCE FAILED: Column was NULL in " + + "the preceding row when it is specified as NOT NULL: " + + errorColumns.toString() + "."); + } + + System.out.println(); + } + s.close(); + } + + /** + * This method tests to see if the result sets returned for + * ODBC clients do in fact comply with the ODBC 3.0 spec. + * That specification can be found here: + * + * http://msdn.microsoft.com/library/default.asp?url=/library/ + * en-us/odbc/htm/odbcsqlprocedurecolumns.asp + * + * NOTE: This method only verifies the names and types of the + * columns. The nullability of the values is checked as part + * of the dumpRS() method. + * + */ + protected void verifyODBC3Compliance() + throws Exception + { + + System.out.println( + "\n=============== Begin ODBC 3.0 Compliance Tests =================\n"); + + // Load the "target" values, which are the values that + // specified by the ODBC specification. + loadODBCTargets(); + + System.out.println("SQLProcedures:"); + s.execute( + "call sysibm.sqlprocedures (null, '%', 'GETPCTEST%', 'DATATYPE=''ODBC''')"); + checkODBCNamesAndTypes(s.getResultSet(), GET_PROCEDURES); + + System.out.println("SQLProcedureColumns:"); + s.execute( + "call sysibm.sqlprocedurecols(null, '%', 'GETPCTEST%', '%', 'DATATYPE=''ODBC''')"); + checkODBCNamesAndTypes(s.getResultSet(), GET_PROCEDURE_COLUMNS); + + System.out.println("SQLTables:"); + s.execute( + "call sysibm.sqltables (null, null, null, 'SYSTEM TABLE', 'DATATYPE=''ODBC''')"); + checkODBCNamesAndTypes(s.getResultSet(), GET_TABLES); + + System.out.println("SQLColumns:"); + s.execute( + "call sysibm.sqlcolumns ('', null, '', '', 'DATATYPE=''ODBC''')"); + checkODBCNamesAndTypes(s.getResultSet(), GET_COLUMNS); + + System.out.println("SQLColumnPrivileges:"); + s.execute( + "call sysibm.sqlcolprivileges ('Huey', 'Dewey', 'Louie', 'Frooey', 'DATATYPE=''ODBC''')"); + checkODBCNamesAndTypes(s.getResultSet(), GET_COLUMN_PRIVILEGES); + + System.out.println("SQLTablePrivileges:"); + s.execute( + "call sysibm.sqltableprivileges ('Huey', 'Dewey', 'Louie', 'DATATYPE=''ODBC''')"); + checkODBCNamesAndTypes(s.getResultSet(), GET_TABLE_PRIVILEGES); + + System.out.println("SQLSpecialColumns: getBestRowIdentifier"); + s.execute( + "call sysibm.sqlspecialcolumns (1, '', null, 'LOUIE', 1, 1, 'DATATYPE=''ODBC''')"); + checkODBCNamesAndTypes(s.getResultSet(), GET_BEST_ROW_IDENTIFIER); + + System.out.println("SQLSpecialColumns: getVersionColumns"); + s.execute( + "call sysibm.sqlspecialcolumns (2, 'Huey', 'Dewey', 'Louie', 1, 1, 'DATATYPE=''ODBC''')"); + checkODBCNamesAndTypes(s.getResultSet(), GET_VERSION_COLUMNS); + + System.out.println("SQLPrimaryKeys:"); + s.execute( + "call sysibm.sqlprimarykeys ('', '%', 'LOUIE', 'DATATYPE=''ODBC''')"); + checkODBCNamesAndTypes(s.getResultSet(), GET_PRIMARY_KEYS); + + System.out.println("SQLForeignKeys: getImportedKeys"); + s.execute( + "call sysibm.sqlforeignkeys (null, null, null, null, null, null, " + + "'IMPORTEDKEY=1;DATATYPE=''ODBC''')"); + checkODBCNamesAndTypes(s.getResultSet(), GET_IMPORTED_KEYS); + + System.out.println("SQLForeignKeys: getExportedKeys"); + s.execute( + "call sysibm.sqlforeignkeys (null, null, null, null, null, null, " + + "'EXPORTEDKEY=1;DATATYPE=''ODBC''')"); + checkODBCNamesAndTypes(s.getResultSet(), GET_EXPORTED_KEYS); + + System.out.println("SQLForeignKeys: getCrossReference"); + s.execute( + "call sysibm.sqlforeignkeys ('', null, 'LOUIE', '', null, 'REFTAB', 'DATATYPE=''ODBC''')"); + checkODBCNamesAndTypes(s.getResultSet(), GET_CROSS_REFERENCE); + + System.out.println("SQLGetTypeInfo"); + s.execute( + "call sysibm.sqlgettypeinfo (0, 'DATATYPE=''ODBC''')"); + checkODBCNamesAndTypes(s.getResultSet(), GET_TYPE_INFO); + + System.out.println("SQLStatistics:"); + s.execute( + "call sysibm.sqlstatistics ('', 'SYS', 'SYSCOLUMNS', 1, 0, 'DATATYPE=''ODBC''')"); + checkODBCNamesAndTypes(s.getResultSet(), GET_INDEX_INFO); + + System.out.println( + "\n=============== End ODBC 3.0 Compliance Tests =================\n"); + + } + + /** + * This is where we load the metadata result set schema + * that is specified in the ODBC 3.0 spec. When we do + * validation of the ODBC result set, we will compare + * the actual results with the target values that we + * load here. + * + * Target lists consist of three strings for each column + * in the result set. The first string is the expected + * (ODBC 3.0) column name. The second string is the + * expected column type. The third string is the + * expected column nullability (null means that the + * column is nullable; any non-null String means that + * the column is NOT NULLABLE). + * + * The target values in this method come from the following + * URL: + * + * http://msdn.microsoft.com/library/default.asp?url=/library/ + * en-us/odbc/htm/odbcsqlprocedurecolumns.asp + * + */ + protected void loadODBCTargets() { + + odbcComplianceTargets[GET_PROCEDURES] = new String [] { + + "PROCEDURE_CAT", "VARCHAR", null, + "PROCEDURE_SCHEM", "VARCHAR", null, + "PROCEDURE_NAME", "VARCHAR", "NOT NULL", + "NUM_INPUT_PARAMS", "INTEGER", null, + "NUM_OUTPUT_PARAMS", "INTEGER", null, + "NUM_RESULT_SETS", "INTEGER", null, + "REMARKS", "VARCHAR", null, + "PROCEDURE_TYPE", "SMALLINT", null + + }; + + odbcComplianceTargets[GET_PROCEDURE_COLUMNS] = new String [] { + + "PROCEDURE_CAT", "VARCHAR", null, + "PROCEDURE_SCHEM", "VARCHAR", null, + "PROCEDURE_NAME", "VARCHAR", "NOT NULL", + "COLUMN_NAME", "VARCHAR", "NOT NULL", + "COLUMN_TYPE", "SMALLINT", "NOT NULL", + "DATA_TYPE", "SMALLINT", "NOT NULL", + "TYPE_NAME", "VARCHAR", "NOT NULL", + "COLUMN_SIZE", "INTEGER", null, + "BUFFER_LENGTH", "INTEGER", null, + "DECIMAL_DIGITS", "SMALLINT", null, + "NUM_PREC_RADIX", "SMALLINT", null, + "NULLABLE", "SMALLINT", "NOT NULL", + "REMARKS", "VARCHAR", null, + "COLUMN_DEF", "VARCHAR", null, + "SQL_DATA_TYPE", "SMALLINT", "NOT NULL", + "SQL_DATETIME_SUB", "SMALLINT", null, + "CHAR_OCTET_LENGTH", "INTEGER", null, + "ORDINAL_POSITION", "INTEGER", "NOT NULL", + "IS_NULLABLE", "VARCHAR", null + + }; + + odbcComplianceTargets[GET_TABLES] = new String [] { + + "TABLE_CAT", "VARCHAR", null, + "TABLE_SCHEM", "VARCHAR", null, + "TABLE_NAME", "VARCHAR", null, + "TABLE_TYPE", "VARCHAR", null, + "REMARKS", "VARCHAR", null + + }; + + odbcComplianceTargets[GET_COLUMNS] = new String [] { + + "TABLE_CAT", "VARCHAR", null, + "TABLE_SCHEM", "VARCHAR", null, + "TABLE_NAME", "VARCHAR", "NOT NULL", + "COLUMN_NAME", "VARCHAR", "NOT NULL", + "DATA_TYPE", "SMALLINT", "NOT NULL", + "TYPE_NAME", "VARCHAR", "NOT NULL", + "COLUMN_SIZE", "INTEGER", null, + "BUFFER_LENGTH", "INTEGER", null, + "DECIMAL_DIGITS", "SMALLINT", null, + "NUM_PREC_RADIX", "SMALLINT", null, + "NULLABLE", "SMALLINT", "NOT NULL", + "REMARKS", "VARCHAR", null, + "COLUMN_DEF", "VARCHAR", null, + "SQL_DATA_TYPE", "SMALLINT", "NOT NULL", + "SQL_DATETIME_SUB", "SMALLINT", null, + "CHAR_OCTET_LENGTH", "INTEGER", null, + "ORDINAL_POSITION", "INTEGER", "NOT NULL", + "IS_NULLABLE", "VARCHAR", null + + }; + + odbcComplianceTargets[GET_COLUMN_PRIVILEGES] = new String [] { + + "TABLE_CAT", "VARCHAR", null, + "TABLE_SCHEM", "VARCHAR", null, + "TABLE_NAME", "VARCHAR", "NOT NULL", + "COLUMN_NAME", "VARCHAR", "NOT NULL", + "GRANTOR", "VARCHAR", null, + "GRANTEE", "VARCHAR", "NOT NULL", + "PRIVILEGE", "VARCHAR", "NOT NULL", + "IS_GRANTABLE", "VARCHAR", null + + }; + + odbcComplianceTargets[GET_TABLE_PRIVILEGES] = new String [] { + + "TABLE_CAT", "VARCHAR", null, + "TABLE_SCHEM", "VARCHAR", null, + "TABLE_NAME", "VARCHAR", "NOT NULL", + "GRANTOR", "VARCHAR", null, + "GRANTEE", "VARCHAR", "NOT NULL", + "PRIVILEGE", "VARCHAR", "NOT NULL", + "IS_GRANTABLE", "VARCHAR", null + + }; + + // Next two corresond to ODBC's "SQLSpecialColumns". + + odbcComplianceTargets[GET_BEST_ROW_IDENTIFIER] = new String [] { + + "SCOPE", "SMALLINT", null, + "COLUMN_NAME", "VARCHAR", "NOT NULL", + "DATA_TYPE", "SMALLINT", "NOT NULL", + "TYPE_NAME", "VARCHAR", "NOT NULL", + "COLUMN_SIZE", "INTEGER", null, + "BUFFER_LENGTH", "INTEGER", null, + "DECIMAL_DIGITS", "SMALLINT", null, + "PSEUDO_COLUMN", "SMALLINT", null + + }; + + odbcComplianceTargets[GET_VERSION_COLUMNS] = + odbcComplianceTargets[GET_BEST_ROW_IDENTIFIER]; + + odbcComplianceTargets[GET_PRIMARY_KEYS] = new String [] { + + "TABLE_CAT", "VARCHAR", null, + "TABLE_SCHEM", "VARCHAR", null, + "TABLE_NAME", "VARCHAR", "NOT NULL", + "COLUMN_NAME", "VARCHAR", "NOT NULL", + "KEY_SEQ", "SMALLINT", "NOT NULL", + "PK_NAME", "VARCHAR", null + + }; + + // Next three correspond to ODBC's "SQLForeignKeys". + + odbcComplianceTargets[GET_IMPORTED_KEYS] = new String [] { + + "PKTABLE_CAT", "VARCHAR", null, + "PKTABLE_SCHEM", "VARCHAR", null, + "PKTABLE_NAME", "VARCHAR", "NOT NULL", + "PKCOLUMN_NAME", "VARCHAR", "NOT NULL", + "FKTABLE_CAT", "VARCHAR", null, + "FKTABLE_SCHEM", "VARCHAR", null, + "FKTABLE_NAME", "VARCHAR", "NOT NULL", + "FKCOLUMN_NAME", "VARCHAR", "NOT NULL", + "KEY_SEQ", "SMALLINT", "NOT NULL", + "UPDATE_RULE", "SMALLINT", null, + "DELETE_RULE", "SMALLINT", null, + "FK_NAME", "VARCHAR", null, + "PK_NAME", "VARCHAR", null, + "DEFERRABILITY", "SMALLINT", null + + }; + + odbcComplianceTargets[GET_EXPORTED_KEYS] = + odbcComplianceTargets[GET_IMPORTED_KEYS]; + + odbcComplianceTargets[GET_CROSS_REFERENCE] = + odbcComplianceTargets[GET_IMPORTED_KEYS]; + + odbcComplianceTargets[GET_TYPE_INFO] = new String [] { + + "TYPE_NAME", "VARCHAR", "NOT NULL", + "DATA_TYPE", "SMALLINT", "NOT NULL", + "COLUMN_SIZE", "INTEGER", null, + "LITERAL_PREFIX", "VARCHAR", null, + "LITERAL_SUFFIX", "VARCHAR", null, + "CREATE_PARAMS", "VARCHAR", null, + "NULLABLE", "SMALLINT", "NOT NULL", + "CASE_SENSITIVE", "SMALLINT", "NOT NULL", + "SEARCHABLE", "SMALLINT", "NOT NULL", + "UNSIGNED_ATTRIBUTE", "SMALLINT", null, + "FIXED_PREC_SCALE", "SMALLINT", "NOT NULL", + "AUTO_UNIQUE_VAL", "SMALLINT", null, + "LOCAL_TYPE_NAME", "VARCHAR", null, + "MINIMUM_SCALE", "SMALLINT", null, + "MAXIMUM_SCALE", "SMALLINT", null, + "SQL_DATA_TYPE", "SMALLINT", "NOT NULL", + "SQL_DATETIME_SUB", "SMALLINT", null, + "NUM_PREC_RADIX", "INTEGER", null, + "INTERVAL_PRECISION", "SMALLINT", null + + }; + + // Next one corresponds to ODBC's "SQLStatistics". + odbcComplianceTargets[GET_INDEX_INFO] = new String [] { + + "TABLE_CAT", "VARCHAR", null, + "TABLE_SCHEM", "VARCHAR", null, + "TABLE_NAME", "VARCHAR", "NOT NULL", + "NON_UNIQUE", "SMALLINT", null, + "INDEX_QUALIFIER", "VARCHAR", null, + "INDEX_NAME", "VARCHAR", null, + "TYPE", "SMALLINT", "NOT NULL", + "ORDINAL_POSITION", "SMALLINT", null, + "COLUMN_NAME", "VARCHAR", null, + "ASC_OR_DESC", "CHAR", null, + "CARDINALITY", "INTEGER", null, + "PAGES", "INTEGER", null, + "FILTER_CONDITION", "VARCHAR", null + + }; + + } + + /** + * Takes result set metadata and sees if the names + * and types of its columns match what ODBC 3.0 + * dictates. + */ + protected void checkODBCNamesAndTypes(ResultSet rs, int procId) + throws SQLException + { + + ResultSetMetaData rsmd = rs.getMetaData(); + + int numCols = rsmd.getColumnCount(); + int targetCols = odbcComplianceTargets[procId].length / 3; + if (numCols < targetCols) { + // result set is missing columns, so we already know + // something is wrong. + System.out.println( + " --> ODBC COMPLIANCE FAILED: Result set was missing columns."); + return; + } + + // Check type and name of each column in the result set. + for (int i = 1; i <= targetCols; i++) { + + int offset = 3 * (i - 1); + if (!rsmd.getColumnLabel(i).equals(odbcComplianceTargets[procId][offset])) { + System.out.println( + "--> ODBC COMPLIANCE FAILED: Column name '" + rsmd.getColumnLabel(i) + + "' does not match expected name '" + + odbcComplianceTargets[procId][offset] + "'."); + } + if (!rsmd.getColumnTypeName(i).equals(odbcComplianceTargets[procId][offset + 1])) { + System.out.println( + "--> ODBC COMPLIANCE FAILED: Column type '" + rsmd.getColumnTypeName(i) + + "' does not match expected type '" + + odbcComplianceTargets[procId][offset + 1] + "' for column '" + + rsmd.getColumnLabel(i) + "'."); + } + + } + + System.out.println("==> ODBC type/name checking done."); + + } + + /** + * Takes result set metadata and sees if the + * nullability of its columns match what ODBC 3.0 + * dictates. + */ + protected boolean badNullability(int odbcProc, String colName, + int colNum, boolean wasNull) + { + + return (wasNull && + odbcComplianceTargets[odbcProc][3 * (colNum-1) + 2] != null); + + } + + private static String addQuotes(String str) { + + if (str == null) + return "null"; + + if (str.length() == 0) + return "''"; + + if ((str.charAt(0) == '\'') && (str.charAt(str.length()-1) == '\'')) + // already have quotes. + return str; + + return "'" + str + "'"; + + } + +} + Property changes on: java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/odbc_metadata.java ___________________________________________________________________ Name: svn:eol-style + native Index: java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata_test.java =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata_test.java (revision 0) +++ java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata_test.java (revision 0) @@ -0,0 +1,973 @@ +/* + + Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata_test + + Copyright 1999, 2004 The Apache Software Foundation or its licensors, as applicable. + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + + */ + +package org.apache.derbyTesting.functionTests.tests.jdbcapi; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.DatabaseMetaData; +import java.sql.ResultSetMetaData; +import java.sql.Statement; +import java.sql.CallableStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Types; +import java.sql.Timestamp; +import java.sql.Time; +import java.sql.Date; +import java.math.BigDecimal; + +import java.util.Properties; + +import org.apache.derby.tools.ij; + +/** + * Test of database meta-data. This program simply calls each of the meta-data + * methods, one by one, and prints the results. The test passes if the printed + * results match a previously stored "master". Thus this test cannot actually + * discern whether it passes or not. + * + */ + +public abstract class metadata_test { + + // Ids for the Derby internal procedures that are used to fetch + // some of the metadata. + + protected static final int GET_PROCEDURES = 0; + protected static final int GET_PROCEDURE_COLUMNS = 1; + protected static final int GET_TABLES = 2; + protected static final int GET_COLUMNS = 3; + protected static final int GET_COLUMN_PRIVILEGES = 5; + protected static final int GET_TABLE_PRIVILEGES = 6; + protected static final int GET_BEST_ROW_IDENTIFIER = 7; + protected static final int GET_VERSION_COLUMNS = 8; + protected static final int GET_PRIMARY_KEYS = 9; + protected static final int GET_IMPORTED_KEYS = 10; + protected static final int GET_EXPORTED_KEYS = 11; + protected static final int GET_CROSS_REFERENCE = 12; + protected static final int GET_TYPE_INFO = 13; + protected static final int GET_INDEX_INFO = 14; + + protected static final int IGNORE_PROC_ID = -1; + + // We leave it up to the classes which extend this one to + // initialize the following fields at contruct time. + protected Connection con; + protected static Statement s; + + protected void runTest() { + + DatabaseMetaData met; + ResultSet rs; + ResultSetMetaData rsmet; + + System.out.println("Test metadata starting"); + + try + { + + // test decimal type and other numeric types precision, scale, + // and display width after operations, beetle 3875, 3906 + s.execute("create table t (i int, s smallint, r real, "+ + "d double precision, dt date, t time, ts timestamp, "+ + "c char(10), v varchar(40) not null, dc dec(10,2))"); + s.execute("insert into t values(1,2,3.3,4.4,date('1990-05-05'),"+ + "time('12:06:06'),timestamp('1990-07-07 07:07:07.07'),"+ + "'eight','nine', 11.1)"); + + // test decimal type and other numeric types precision, scale, + // and display width after operations, beetle 3875, 3906 + //rs = s.executeQuery("select dc from t where tn = 10 union select dc from t where i = 1"); + rs = s.executeQuery("select dc from t where dc = 11.1 union select dc from t where i = 1"); + rsmet = rs.getMetaData(); + System.out.println("Column display size of the union result is: " + rsmet.getColumnDisplaySize(1)); + rs.close(); + + rs = s.executeQuery("select dc, dc, r+dc, d-dc, dc-d from t"); + rsmet = rs.getMetaData(); + System.out.println("dec(10,2) -- precision: " + rsmet.getPrecision(1) + " scale: " + rsmet.getScale(1) + " display size: " + rsmet.getColumnDisplaySize(1) + " type name: " + rsmet.getColumnTypeName(1)); + System.out.println("dec(10,2) -- precision: " + rsmet.getPrecision(2) + " scale: " + rsmet.getScale(2) + " display size: " + rsmet.getColumnDisplaySize(2) + " type name: " + rsmet.getColumnTypeName(2)); + System.out.println("real + dec(10,2) -- precision: " + rsmet.getPrecision(3) + " scale: " + rsmet.getScale(3) + " display size: " + rsmet.getColumnDisplaySize(3) + " type name: " + rsmet.getColumnTypeName(3)); + System.out.println("double precision - dec(10,2) -- precision: " + rsmet.getPrecision(4) + " scale: " + rsmet.getScale(4) + " display size: " + rsmet.getColumnDisplaySize(4) + " type name: " + rsmet.getColumnTypeName(4)); + // result is double, precision/scale don't make sense + System.out.println("dec(10,2) - double precision -- precision: " + rsmet.getPrecision(5) + " scale: " + rsmet.getScale(5) + " display size: " + rsmet.getColumnDisplaySize(5) + " type name: " + rsmet.getColumnTypeName(5)); + while (rs.next()) + System.out.println("result row: " + rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4) + " " + rs.getString(5)); + rs.close(); + + s.execute("insert into t values(1,2,3.3,4.4,date('1990-05-05'),"+ + "time('12:06:06'),timestamp('1990-07-07 07:07:07.07'),"+ + "'eight','nine', 11.11)"); + + // test decimal/integer static column result scale consistent + // with result set metadata after division, beetle 3901 + rs = s.executeQuery("select dc / 2 from t"); + rsmet = rs.getMetaData(); + System.out.println("Column result scale after division is: " + rsmet.getScale(1)); + while (rs.next()) + System.out.println("dc / 2 = " + rs.getString(1)); + rs.close(); + + + s.execute("create table louie (i int not null default 10, s smallint not null, " + + "c30 char(30) not null, " + + "vc10 varchar(10) not null default 'asdf', " + + "constraint PRIMKEY primary key(vc10, i), " + + "constraint UNIQUEKEY unique(c30, s), " + + "ai bigint generated always as identity (start with -10, increment by 2001))"); + + // Create another unique index on louie + s.execute("create unique index u1 on louie(s, i)"); + // Create a non-unique index on louie + s.execute("create index u2 on louie(s)"); + // Create a view on louie + s.execute("create view screwie as select * from louie"); + + // Create a foreign key + s.execute("create table reftab (vc10 varchar(10), i int, " + + "s smallint, c30 char(30), " + + "s2 smallint, c302 char(30), " + + "dprim decimal(5,1) not null, dfor decimal(5,1) not null, "+ + "constraint PKEY_REFTAB primary key (dprim), " + + "constraint FKEYSELF foreign key (dfor) references reftab, "+ + "constraint FKEY1 foreign key(vc10, i) references louie, " + + "constraint FKEY2 foreign key(c30, s2) references louie (c30, s), "+ + "constraint FKEY3 foreign key(c30, s) references louie (c30, s))"); + + s.execute("create table reftab2 (t2_vc10 varchar(10), t2_i int, " + + "constraint T2_FKEY1 foreign key(t2_vc10, t2_i) references louie)"); + + // Create a table with all types + s.execute("create table alltypes ( "+ + //"bitcol16_______ bit(16), "+ + //"bitvaryingcol32 bit varying(32), "+ + //"tinyintcol tinyint, "+ + "smallintcol smallint, "+ + "intcol int default 20, "+ + "bigintcol bigint, "+ + "realcol real, "+ + "doublepreccol double precision default 10, "+ + "decimalcol10p4s decimal(10,4), "+ + "numericcol20p2s numeric(20,2), "+ + "char8col___ char(8), "+ + "varchar9col varchar(9), "+ + "longvarcharcol long varchar,"+ + //"longvarbinarycol long bit varying,"+ + //"nchar10col nchar(10)" + //+ ", nvarchar8col nvarchar(8)" + //+ ", longnvarchar long nvarchar" + //+ ", + "blobcol blob(3K)" + + ")" ); + // test for beetle 4620 + s.execute("CREATE TABLE INFLIGHT(FLT_NUM CHAR(20) NOT NULL," + + "FLT_ORIGIN CHAR(6), " + + "FLT_DEST CHAR(6), " + + "FLT_AIRCRAFT CHAR(20), " + + "FLT_FLYING_TIME VARCHAR(22), "+ + "FLT_DEPT_TIME CHAR(8), "+ + "FLT_ARR_TIME CHAR(8), "+ + "FLT_NOTES VARCHAR(510), "+ + "FLT_DAYS_OF_WK CHAR(14), "+ + "FLT_CRAFT_PIC VARCHAR(32672), "+ + "PRIMARY KEY(FLT_NUM))"); + + // Create procedures so we can test + // getProcedureColumns() + s.execute("create procedure GETPCTEST1 (" + + // for creating, the procedure's params do not need to exactly match the method's + "out outb VARCHAR(3), a VARCHAR(3), b NUMERIC, c SMALLINT, " + + "e SMALLINT, f INTEGER, g BIGINT, h FLOAT, i DOUBLE PRECISION, " + + "k DATE, l TIME, T TIMESTAMP )"+ + "language java external name " + + "'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc'" + + " parameter style java"); + s.execute("create procedure GETPCTEST2 (pa INTEGER, pb BIGINT)"+ + "language java external name " + + "'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc'" + + " parameter style java"); + s.execute("create procedure GETPCTEST3A (STRING1 VARCHAR(5), out STRING2 VARCHAR(5))"+ + "language java external name " + + "'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc'" + + " parameter style java"); + s.execute("create procedure GETPCTEST3B (in STRING3 VARCHAR(5), inout STRING4 VARCHAR(5))"+ + "language java external name " + + "'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc'" + + " parameter style java"); + s.execute("create procedure GETPCTEST4A() "+ + "language java external name " + + "'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4a'"+ + " parameter style java"); + s.execute("create procedure GETPCTEST4B() "+ + "language java external name " + + "'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b'" + + " parameter style java"); + s.execute("create procedure GETPCTEST4Bx(out retparam INTEGER) "+ + "language java external name " + + "'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b'" + + " parameter style java"); + + met = con.getMetaData(); + + System.out.println("JDBC Driver '" + met.getDriverName() + + "', version " + met.getDriverMajorVersion() + + "." + met.getDriverMinorVersion() + + " (" + met.getDriverVersion() + ")"); + + try { + System.out.println("The URL is: " + met.getURL()); + } catch (Throwable err) { + System.out.println("%%getURL() gave the exception: " + err); + } + + System.out.println("allTablesAreSelectable(): " + + met.allTablesAreSelectable()); + + System.out.println("maxColumnNameLength(): " + met.getMaxColumnNameLength()); + + System.out.println(); + System.out.println("getSchemas():"); + dumpRS(met.getSchemas()); + + System.out.println(); + System.out.println("getCatalogs():"); + dumpRS(met.getCatalogs()); + + System.out.println("getSearchStringEscape(): " + + met.getSearchStringEscape()); + + System.out.println("getSQLKeywords(): " + + met.getSQLKeywords()); + + System.out.println("getDefaultTransactionIsolation(): " + + met.getDefaultTransactionIsolation()); + + System.out.println("getProcedures():"); + dumpRS(GET_PROCEDURES, getMetaDataRS(met, GET_PROCEDURES, + new String [] {null, "%", "GETPCTEST%"}, + null, null, null)); + + + /* + * any methods that were not tested above using code written + * specifically for it will now be tested in a generic way. + */ + + + System.out.println("allProceduresAreCallable(): " + + met.allProceduresAreCallable()); + System.out.println("getUserName(): " + + met.getUserName()); + System.out.println("isReadOnly(): " + + met.isReadOnly()); + System.out.println("nullsAreSortedHigh(): " + + met.nullsAreSortedHigh()); + System.out.println("nullsAreSortedLow(): " + + met.nullsAreSortedLow()); + System.out.println("nullsAreSortedAtStart(): " + + met.nullsAreSortedAtStart()); + System.out.println("nullsAreSortedAtEnd(): " + + met.nullsAreSortedAtEnd()); + + + System.out.println("getDatabaseProductName(): " + met.getDatabaseProductName()); + + String v = met.getDatabaseProductVersion(); + int l = v.indexOf('('); + if (l<0) l = v.length(); + v = v.substring(0,l); + System.out.println("getDatabaseProductVersion(): " + v); + System.out.println("getDriverVersion(): " + + met.getDriverVersion()); + System.out.println("usesLocalFiles(): " + + met.usesLocalFiles()); + System.out.println("usesLocalFilePerTable(): " + + met.usesLocalFilePerTable()); + System.out.println("supportsMixedCaseIdentifiers(): " + + met.supportsMixedCaseIdentifiers()); + System.out.println("storesUpperCaseIdentifiers(): " + + met.storesUpperCaseIdentifiers()); + System.out.println("storesLowerCaseIdentifiers(): " + + met.storesLowerCaseIdentifiers()); + System.out.println("storesMixedCaseIdentifiers(): " + + met.storesMixedCaseIdentifiers()); + System.out.println("supportsMixedCaseQuotedIdentifiers(): " + + met.supportsMixedCaseQuotedIdentifiers()); + System.out.println("storesUpperCaseQuotedIdentifiers(): " + + met.storesUpperCaseQuotedIdentifiers()); + System.out.println("storesLowerCaseQuotedIdentifiers(): " + + met.storesLowerCaseQuotedIdentifiers()); + System.out.println("storesMixedCaseQuotedIdentifiers(): " + + met.storesMixedCaseQuotedIdentifiers()); + System.out.println("getIdentifierQuoteString(): " + + met.getIdentifierQuoteString()); + System.out.println("getNumericFunctions(): " + + met.getNumericFunctions()); + System.out.println("getStringFunctions(): " + + met.getStringFunctions()); + System.out.println("getSystemFunctions(): " + + met.getSystemFunctions()); + System.out.println("getTimeDateFunctions(): " + + met.getTimeDateFunctions()); + System.out.println("getExtraNameCharacters(): " + + met.getExtraNameCharacters()); + System.out.println("supportsAlterTableWithAddColumn(): " + + met.supportsAlterTableWithAddColumn()); + System.out.println("supportsAlterTableWithDropColumn(): " + + met.supportsAlterTableWithDropColumn()); + System.out.println("supportsColumnAliasing(): " + + met.supportsColumnAliasing()); + System.out.println("nullPlusNonNullIsNull(): " + + met.nullPlusNonNullIsNull()); + System.out.println("supportsConvert(): " + + met.supportsConvert()); + System.out.println("supportsConvert(Types.INTEGER, Types.SMALLINT): " + + met.supportsConvert(Types.INTEGER, Types.SMALLINT)); + System.out.println("supportsTableCorrelationNames(): " + + met.supportsTableCorrelationNames()); + System.out.println("supportsDifferentTableCorrelationNames(): " + + met.supportsDifferentTableCorrelationNames()); + System.out.println("supportsExpressionsInOrderBy(): " + + met.supportsExpressionsInOrderBy()); + System.out.println("supportsOrderByUnrelated(): " + + met.supportsOrderByUnrelated()); + System.out.println("supportsGroupBy(): " + + met.supportsGroupBy()); + System.out.println("supportsGroupByUnrelated(): " + + met.supportsGroupByUnrelated()); + System.out.println("supportsGroupByBeyondSelect(): " + + met.supportsGroupByBeyondSelect()); + System.out.println("supportsLikeEscapeClause(): " + + met.supportsLikeEscapeClause()); + System.out.println("supportsMultipleResultSets(): " + + met.supportsMultipleResultSets()); + System.out.println("supportsMultipleTransactions(): " + + met.supportsMultipleTransactions()); + System.out.println("supportsNonNullableColumns(): " + + met.supportsNonNullableColumns()); + System.out.println("supportsMinimumSQLGrammar(): " + + met.supportsMinimumSQLGrammar()); + System.out.println("supportsCoreSQLGrammar(): " + + met.supportsCoreSQLGrammar()); + System.out.println("supportsExtendedSQLGrammar(): " + + met.supportsExtendedSQLGrammar()); + System.out.println("supportsANSI92EntryLevelSQL(): " + + met.supportsANSI92EntryLevelSQL()); + System.out.println("supportsANSI92IntermediateSQL(): " + + met.supportsANSI92IntermediateSQL()); + System.out.println("supportsANSI92FullSQL(): " + + met.supportsANSI92FullSQL()); + System.out.println("supportsIntegrityEnhancementFacility(): " + + met.supportsIntegrityEnhancementFacility()); + System.out.println("supportsOuterJoins(): " + + met.supportsOuterJoins()); + System.out.println("supportsFullOuterJoins(): " + + met.supportsFullOuterJoins()); + System.out.println("supportsLimitedOuterJoins(): " + + met.supportsLimitedOuterJoins()); + System.out.println("getSchemaTerm(): " + + met.getSchemaTerm()); + System.out.println("getProcedureTerm(): " + + met.getProcedureTerm()); + System.out.println("getCatalogTerm(): " + + met.getCatalogTerm()); + System.out.println("isCatalogAtStart(): " + + met.isCatalogAtStart()); + System.out.println("getCatalogSeparator(): " + + met.getCatalogSeparator()); + System.out.println("supportsSchemasInDataManipulation(): " + + met.supportsSchemasInDataManipulation()); + System.out.println("supportsSchemasInProcedureCalls(): " + + met.supportsSchemasInProcedureCalls()); + System.out.println("supportsSchemasInTableDefinitions(): " + + met.supportsSchemasInTableDefinitions()); + System.out.println("supportsSchemasInIndexDefinitions(): " + + met.supportsSchemasInIndexDefinitions()); + System.out.println("supportsSchemasInPrivilegeDefinitions(): " + + met.supportsSchemasInPrivilegeDefinitions()); + System.out.println("supportsCatalogsInDataManipulation(): " + + met.supportsCatalogsInDataManipulation()); + System.out.println("supportsCatalogsInProcedureCalls(): " + + met.supportsCatalogsInProcedureCalls()); + System.out.println("supportsCatalogsInTableDefinitions(): " + + met.supportsCatalogsInTableDefinitions()); + System.out.println("supportsCatalogsInIndexDefinitions(): " + + met.supportsCatalogsInIndexDefinitions()); + System.out.println("supportsCatalogsInPrivilegeDefinitions(): " + + met.supportsCatalogsInPrivilegeDefinitions()); + System.out.println("supportsPositionedDelete(): " + + met.supportsPositionedDelete()); + System.out.println("supportsPositionedUpdate(): " + + met.supportsPositionedUpdate()); + System.out.println("supportsSelectForUpdate(): " + + met.supportsSelectForUpdate()); + System.out.println("supportsStoredProcedures(): " + + met.supportsStoredProcedures()); + System.out.println("supportsSubqueriesInComparisons(): " + + met.supportsSubqueriesInComparisons()); + System.out.println("supportsSubqueriesInExists(): " + + met.supportsSubqueriesInExists()); + System.out.println("supportsSubqueriesInIns(): " + + met.supportsSubqueriesInIns()); + System.out.println("supportsSubqueriesInQuantifieds(): " + + met.supportsSubqueriesInQuantifieds()); + System.out.println("supportsCorrelatedSubqueries(): " + + met.supportsCorrelatedSubqueries()); + System.out.println("supportsUnion(): " + + met.supportsUnion()); + System.out.println("supportsUnionAll(): " + + met.supportsUnionAll()); + System.out.println("supportsOpenCursorsAcrossCommit(): " + + met.supportsOpenCursorsAcrossCommit()); + System.out.println("supportsOpenCursorsAcrossRollback(): " + + met.supportsOpenCursorsAcrossRollback()); + System.out.println("supportsOpenStatementsAcrossCommit(): " + + met.supportsOpenStatementsAcrossCommit()); + System.out.println("supportsOpenStatementsAcrossRollback(): " + + met.supportsOpenStatementsAcrossRollback()); + System.out.println("getMaxBinaryLiteralLength(): " + + met.getMaxBinaryLiteralLength()); + System.out.println("getMaxCharLiteralLength(): " + + met.getMaxCharLiteralLength()); + System.out.println("getMaxColumnsInGroupBy(): " + + met.getMaxColumnsInGroupBy()); + System.out.println("getMaxColumnsInIndex(): " + + met.getMaxColumnsInIndex()); + System.out.println("getMaxColumnsInOrderBy(): " + + met.getMaxColumnsInOrderBy()); + System.out.println("getMaxColumnsInSelect(): " + + met.getMaxColumnsInSelect()); + System.out.println("getMaxColumnsInTable(): " + + met.getMaxColumnsInTable()); + System.out.println("getMaxConnections(): " + + met.getMaxConnections()); + System.out.println("getMaxCursorNameLength(): " + + met.getMaxCursorNameLength()); + System.out.println("getMaxIndexLength(): " + + met.getMaxIndexLength()); + System.out.println("getMaxSchemaNameLength(): " + + met.getMaxSchemaNameLength()); + System.out.println("getMaxProcedureNameLength(): " + + met.getMaxProcedureNameLength()); + System.out.println("getMaxCatalogNameLength(): " + + met.getMaxCatalogNameLength()); + System.out.println("getMaxRowSize(): " + + met.getMaxRowSize()); + System.out.println("doesMaxRowSizeIncludeBlobs(): " + + met.doesMaxRowSizeIncludeBlobs()); + System.out.println("getMaxStatementLength(): " + + met.getMaxStatementLength()); + System.out.println("getMaxStatements(): " + + met.getMaxStatements()); + System.out.println("getMaxTableNameLength(): " + + met.getMaxTableNameLength()); + System.out.println("getMaxTablesInSelect(): " + + met.getMaxTablesInSelect()); + System.out.println("getMaxUserNameLength(): " + + met.getMaxUserNameLength()); + System.out.println("supportsTransactions(): " + + met.supportsTransactions()); + System.out.println("supportsTransactionIsolationLevel(Connection.TRANSACTION_NONE): " + + met.supportsTransactionIsolationLevel(Connection.TRANSACTION_NONE)); + System.out.println("supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ): " + + met.supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ)); + System.out.println("supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE): " + + met.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)); + System.out.println("supportsDataDefinitionAndDataManipulationTransactions(): " + + met.supportsDataDefinitionAndDataManipulationTransactions()); + System.out.println("supportsDataManipulationTransactionsOnly(): " + + met.supportsDataManipulationTransactionsOnly()); + System.out.println("dataDefinitionCausesTransactionCommit(): " + + met.dataDefinitionCausesTransactionCommit()); + System.out.println("dataDefinitionIgnoredInTransactions(): " + + met.dataDefinitionIgnoredInTransactions()); + + System.out.println("getConnection(): "+ + ((met.getConnection()==con)?"same connection":"different connection") ); + System.out.println("getProcedureColumns():"); + dumpRS(GET_PROCEDURE_COLUMNS, getMetaDataRS(met, GET_PROCEDURE_COLUMNS, + new String [] {null, "%", "GETPCTEST%", "%"}, + null, null, null)); + + System.out.println("getTables() with TABLE_TYPE in ('SYSTEM TABLE') :"); + String[] tabTypes = new String[1]; + tabTypes[0] = "SYSTEM TABLE"; + dumpRS(GET_TABLES, getMetaDataRS(met, GET_TABLES, + new String [] {null, null, null}, + tabTypes, null, null)); + + System.out.println("getTables() with no types:"); + dumpRS(GET_TABLES, getMetaDataRS(met, GET_TABLES, + new String [] {"", null, "%"}, + null, null, null)); + + System.out.println("getTables() with TABLE_TYPE in ('VIEW','TABLE') :"); + tabTypes = new String[2]; + tabTypes[0] = "VIEW"; + tabTypes[1] = "TABLE"; + dumpRS(GET_TABLES, getMetaDataRS(met, GET_TABLES, + new String [] {null, null, null}, + tabTypes, null, null)); + + + System.out.println("getTableTypes():"); + dumpRS(met.getTableTypes()); + + System.out.println("getColumns():"); + dumpRS(GET_COLUMNS, getMetaDataRS(met, GET_COLUMNS, + new String [] {"", null, "", ""}, + null, null, null)); + + System.out.println("getColumns('SYSTABLES'):"); + dumpRS(GET_COLUMNS, getMetaDataRS(met, GET_COLUMNS, + new String [] {"", "SYS", "SYSTABLES", null}, + null, null, null)); + + System.out.println("getColumns('ALLTYPES'):"); + dumpRS(GET_COLUMNS, getMetaDataRS(met, GET_COLUMNS, + new String [] {"", "APP", "ALLTYPES", null}, + null, null, null)); + + System.out.println("getColumns('LOUIE'):"); + dumpRS(GET_COLUMNS, getMetaDataRS(met, GET_COLUMNS, + new String [] {"", "APP", "LOUIE", null}, + null, null, null)); + + // test for beetle 4620 + System.out.println("getColumns('INFLIGHT'):"); + dumpRS(GET_COLUMNS, getMetaDataRS(met, GET_COLUMNS, + new String [] {"", "APP", "INFLIGHT", null}, + null, null, null)); + + System.out.println("getColumnPrivileges():"); + dumpRS(GET_COLUMN_PRIVILEGES, getMetaDataRS(met, GET_COLUMN_PRIVILEGES, + new String [] {"Huey", "Dewey", "Louie", "Frooey"}, + null, null, null)); + + System.out.println("getTablePrivileges():"); + dumpRS(GET_TABLE_PRIVILEGES, getMetaDataRS(met, GET_TABLE_PRIVILEGES, + new String [] {"Huey", "Dewey", "Louie"}, + null, null, null)); + + System.out.println("getBestRowIdentifier(\"\",null,\"LOUIE\"):"); + dumpRS(GET_BEST_ROW_IDENTIFIER, getMetaDataRS(met, GET_BEST_ROW_IDENTIFIER, + new String [] {"", null, "LOUIE"}, null, + new int [] {DatabaseMetaData.bestRowTransaction}, + new boolean [] {true})); + + System.out.println("getBestRowIdentifier(\"\",\"SYS\",\"SYSTABLES\"):"); + dumpRS(GET_BEST_ROW_IDENTIFIER, getMetaDataRS(met, GET_BEST_ROW_IDENTIFIER, + new String [] {"", "SYS", "SYSTABLES"}, null, + new int [] {DatabaseMetaData.bestRowTransaction}, + new boolean [] {true})); + + System.out.println("getVersionColumns():"); + dumpRS(GET_VERSION_COLUMNS, getMetaDataRS(met, GET_VERSION_COLUMNS, + new String [] {"Huey", "Dewey", "Louie"}, + null, null, null)); + + System.out.println("getPrimaryKeys():"); + dumpRS(GET_PRIMARY_KEYS, getMetaDataRS(met, GET_PRIMARY_KEYS, + new String [] {"", "%", "LOUIE"}, + null, null, null)); + + //beetle 4571 + System.out.println("getPrimaryKeys(null, null, tablename):"); + dumpRS(GET_PRIMARY_KEYS, getMetaDataRS(met, GET_PRIMARY_KEYS, + new String [] {null, null, "LOUIE"}, + null, null, null)); + + System.out.println("getImportedKeys():"); + dumpRS(GET_IMPORTED_KEYS, getMetaDataRS(met, GET_IMPORTED_KEYS, + new String [] {null, null, "%"}, + null, null, null)); + + System.out.println("getExportedKeys():"); + dumpRS(GET_EXPORTED_KEYS, getMetaDataRS(met, GET_EXPORTED_KEYS, + new String [] {null, null, "%"}, + null, null, null)); + + System.out.println("---------------------------------------"); + System.out.println("getCrossReference('',null,'louie','',null,'reftab' ):"); + dumpRS(GET_CROSS_REFERENCE, getMetaDataRS(met, GET_CROSS_REFERENCE, + new String [] {"", null, "LOUIE", "", null, "REFTAB"}, + null, null, null)); + + System.out.println("\ngetCrossReference('','APP','reftab','',null,'reftab' ):"); + dumpRS(GET_CROSS_REFERENCE, getMetaDataRS(met, GET_CROSS_REFERENCE, + new String [] {"", "APP", "REFTAB", "", null, "REFTAB"}, + null, null, null)); + + System.out.println("\ngetCrossReference('',null,null,'','APP','reftab' ):"); + dumpRS(GET_CROSS_REFERENCE, getMetaDataRS(met, GET_CROSS_REFERENCE, + new String [] {"", null, "%", "", "APP", "REFTAB"}, + null, null, null)); + + System.out.println("\ngetImportedKeys('',null,null,'','APP','reftab' ):"); + dumpRS(GET_IMPORTED_KEYS, getMetaDataRS(met, GET_IMPORTED_KEYS, + new String [] {"", "APP", "REFTAB"}, + null, null, null)); + + System.out.println("\ngetCrossReference('',null,'louie','','APP',null):"); + dumpRS(GET_CROSS_REFERENCE, getMetaDataRS(met, GET_CROSS_REFERENCE, + new String [] {"", null, "LOUIE", "", "APP", "%"}, + null, null, null)); + + System.out.println("\ngetExportedKeys('',null,'louie,'','APP',null ):"); + dumpRS(GET_EXPORTED_KEYS, getMetaDataRS(met, GET_EXPORTED_KEYS, + new String [] {"", null, "LOUIE"}, + null, null, null)); + + System.out.println("\ngetCrossReference('','badschema','LOUIE','','APP','REFTAB' ):"); + dumpRS(GET_CROSS_REFERENCE, getMetaDataRS(met, GET_CROSS_REFERENCE, + new String [] {"", "BADSCHEMA", "LOUIE", "", "APP", "REFTAB"}, + null, null, null)); + + System.out.println("getTypeInfo():"); + dumpRS(GET_TYPE_INFO, getMetaDataRS(met, GET_TYPE_INFO, null, null, null, null)); + + /* NOTE - we call getIndexInfo() only on system tables here + * so that there will be no diffs due to generated names. + */ + // unique indexes on SYSCOLUMNS + System.out.println("getIndexInfo():"); + dumpRS(GET_INDEX_INFO, getMetaDataRS(met, GET_INDEX_INFO, + new String [] {"", "SYS", "SYSCOLUMNS"}, + null, null, new boolean [] {true, false})); + + // all indexes on SYSCOLUMNS + System.out.println("getIndexInfo():"); + dumpRS(GET_INDEX_INFO, getMetaDataRS(met, GET_INDEX_INFO, + new String [] {"", "SYS", "SYSCOLUMNS"}, + null, null, new boolean [] {false, false})); + + System.out.println("getIndexInfo():"); + dumpRS(GET_INDEX_INFO, getMetaDataRS(met, GET_INDEX_INFO, + new String [] {"", "SYS", "SYSTABLES"}, + null, null, new boolean [] {true, false})); + + rs = s.executeQuery("SELECT * FROM SYS.SYSTABLES"); + + System.out.println("getColumns('SYSTABLES'):"); + dumpRS(GET_COLUMNS, getMetaDataRS(met, GET_COLUMNS, + new String [] {"", "SYS", "SYSTABLES", null}, + null, null, null)); + + try { + if (!rs.next()) { + System.out.println("FAIL -- user result set closed by"+ + " intervening getColumns request"); + } + } catch (SQLException se) { + if (this instanceof metadata) { + System.out.println("FAIL -- user result set closed by"+ + " intervening getColumns request"); + } + else { + System.out.println("OK -- user result set closed by"+ + " intervening OBDC getColumns request; this was" + + " expected because of the way the test works."); + } + } + rs.close(); + // + // Test referential actions on delete + // + System.out.println("---------------------------------------"); + //create tables to test that we get the delete and update + // referential action correct + System.out.println("Referential action values"); + System.out.println("RESTRICT = "+ DatabaseMetaData.importedKeyRestrict); + System.out.println("NO ACTION = "+ DatabaseMetaData.importedKeyNoAction); + System.out.println("CASCADE = "+ DatabaseMetaData.importedKeyCascade); + System.out.println("SETNULL = "+ DatabaseMetaData.importedKeySetNull); + System.out.println("SETDEFAULT = "+ DatabaseMetaData.importedKeySetDefault); + s.execute("create table refaction1(a int not null primary key)"); + s.execute("create table refactnone(a int references refaction1(a))"); + s.execute("create table refactrestrict(a int references refaction1(a) on delete restrict)"); + s.execute("create table refactnoaction(a int references refaction1(a) on delete no action)"); + s.execute("create table refactcascade(a int references refaction1(a) on delete cascade)"); + s.execute("create table refactsetnull(a int references refaction1(a) on delete set null)"); + System.out.println("getCrossReference('','APP','REFACTION1','','APP','REFACTIONNONE' ):"); + s.execute("create table refactupdrestrict(a int references refaction1(a) on update restrict)"); + s.execute("create table refactupdnoaction(a int references refaction1(a) on update no action)"); + dumpRS(GET_CROSS_REFERENCE, getMetaDataRS(met, GET_CROSS_REFERENCE, + new String [] {"", "APP", "REFACTION1", "", "APP", "REFACTNONE"}, + null, null, null)); + System.out.println("\ngetCrossReference('','APP','REFACTION1','','APP','REFACTRESTRICT' ):"); + dumpRS(GET_CROSS_REFERENCE, getMetaDataRS(met, GET_CROSS_REFERENCE, + new String [] {"", "APP", "REFACTION1", "", "APP", "REFACTRESTRICT"}, + null, null, null)); + System.out.println("\ngetCrossReference('','APP','REFACTION1','','APP','REFACTNOACTION' ):"); + dumpRS(GET_CROSS_REFERENCE, getMetaDataRS(met, GET_CROSS_REFERENCE, + new String [] {"", "APP", "REFACTION1", "", "APP", "REFACTNOACTION"}, + null, null, null)); + System.out.println("\ngetCrossReference('','APP','REFACTION1','','APP','REFACTCASCADE' ):"); + dumpRS(GET_CROSS_REFERENCE, getMetaDataRS(met, GET_CROSS_REFERENCE, + new String [] {"", "APP", "REFACTION1", "", "APP", "REFACTCASCADE"}, + null, null, null)); + System.out.println("\ngetCrossReference('','APP','REFACTION1','','APP','REFACTSETNULL' ):"); + dumpRS(GET_CROSS_REFERENCE, getMetaDataRS(met, GET_CROSS_REFERENCE, + new String [] {"", "APP", "REFACTION1", "", "APP", "REFACTSETNULL"}, + null, null, null)); + System.out.println("\ngetCrossReference('','APP','REFACTION1','','APP','REFACTUPDRESTRICT' ):"); + dumpRS(GET_CROSS_REFERENCE, getMetaDataRS(met, GET_CROSS_REFERENCE, + new String [] {"", "APP", "REFACTION1", "", "APP", "REFACTUPDRESTRICT"}, + null, null, null)); + System.out.println("\ngetCrossReference('','APP','REFACTION1','','APP','REFACTUPDNOACTION' ):"); + dumpRS(GET_CROSS_REFERENCE, getMetaDataRS(met, GET_CROSS_REFERENCE, + new String [] {"", "APP", "REFACTION1", "", "APP", "REFACTUPDNOACTION"}, + null, null, null)); + + ResultSet refrs = getMetaDataRS(met, GET_IMPORTED_KEYS, + new String [] {"", "APP", "REFACTNONE"}, null, null, null); + + if (refrs.next()) + { + //check update rule + if (refrs.getShort(11) != DatabaseMetaData.importedKeyNoAction) + System.out.println("\ngetImportedKeys - none update Failed - action = " + refrs.getShort(11) + " required value = " + DatabaseMetaData.importedKeyNoAction); + else + System.out.println("\ngetImportedKeys - none update Passed"); + //check delete rule + if (refrs.getShort(11) != DatabaseMetaData.importedKeyNoAction) + System.out.println("\ngetImportedKeys - none delete Failed - action = " + refrs.getShort(11) + " required value = " + DatabaseMetaData.importedKeyNoAction); + else + System.out.println("\ngetImportedKeys - none delete Passed"); + } + else + System.out.println("\ngetImportedKeys - none Failed no rows"); + + refrs.close(); + refrs = getMetaDataRS(met, GET_IMPORTED_KEYS, + new String [] {"", "APP", "REFACTRESTRICT"}, null, null, null); + + if (refrs.next()) + { + if (refrs.getShort(11) != DatabaseMetaData.importedKeyRestrict) + System.out.println("\ngetImportedKeys - delete Restrict Failed - action = " + refrs.getShort(11) + " required value = " + DatabaseMetaData.importedKeyRestrict); + else + System.out.println("\ngetImportedKeys - delete Restrict Passed"); + } + else + System.out.println("\ngetImportedKeys - delete Restrict Failed no rows"); + + refrs.close(); + refrs = getMetaDataRS(met, GET_IMPORTED_KEYS, + new String [] {"", "APP", "REFACTNOACTION"}, null, null, null); + + if (refrs.next()) + { + if (refrs.getShort(11) != DatabaseMetaData.importedKeyNoAction) + System.out.println("\ngetImportedKeys - delete NO ACTION Failed - action = " + refrs.getShort(11) + " required value = " + DatabaseMetaData.importedKeyNoAction); + else + System.out.println("\ngetImportedKeys - delete NO ACTION Passed"); + } + else + System.out.println("\ngetImportedKeys - delete NO ACTION Failed no rows"); + + refrs.close(); + refrs = getMetaDataRS(met, GET_IMPORTED_KEYS, + new String [] {"", "APP", "REFACTCASCADE"}, null, null, null); + + if (refrs.next()) + { + if (refrs.getShort(11) != DatabaseMetaData.importedKeyCascade) + System.out.println("\ngetImportedKeys - delete CASCADE Failed - action = " + refrs.getShort(11) + " required value = " + DatabaseMetaData.importedKeyCascade); + else + System.out.println("\ngetImportedKeys - delete CASCADE Passed"); + } + else + System.out.println("\ngetImportedKeys - delete CASCADE Failed no rows"); + + refrs.close(); + refrs = getMetaDataRS(met, GET_IMPORTED_KEYS, + new String [] {"", "APP", "REFACTSETNULL"}, null, null, null); + + if (refrs.next()) + { + if (refrs.getShort(11) != DatabaseMetaData.importedKeySetNull) + System.out.println("\ngetImportedKeys - delete SET NULL Failed - action = " + refrs.getShort(11) + " required value = " + DatabaseMetaData.importedKeySetNull); + else + System.out.println("\ngetImportedKeys - delete SET NULL Passed"); + } + else + System.out.println("\ngetImportedKeys - SET NULL Failed no rows"); + + refrs.close(); + refrs = getMetaDataRS(met, GET_IMPORTED_KEYS, + new String [] {"", "APP", "REFACTRESTRICT"}, null, null, null); + + if (refrs.next()) + { + // test update rule + if (refrs.getShort(11) != DatabaseMetaData.importedKeyRestrict) + System.out.println("\ngetImportedKeys - update Restrict Failed - action = " + refrs.getShort(11) + " required value = " + DatabaseMetaData.importedKeyRestrict); + else + System.out.println("\ngetImportedKeys - update Restrict Passed"); + } + else + System.out.println("\ngetImportedKeys - update Restrict Failed no rows"); + + refrs.close(); + refrs = getMetaDataRS(met, GET_IMPORTED_KEYS, + new String [] {"", "APP", "REFACTNOACTION"}, null, null, null); + + if (refrs.next()) + { + if (refrs.getShort(11) != DatabaseMetaData.importedKeyNoAction) + System.out.println("\ngetImportedKeys - update NO ACTION Failed - action = " + refrs.getShort(11) + " required value = " + DatabaseMetaData.importedKeyNoAction); + else + System.out.println("\ngetImportedKeys - update NO ACTION Passed"); + } + else + System.out.println("\ngetImportedKeys - update NO ACTION Failed no rows"); + refrs.close(); + + System.out.println("\ngetExportedKeys('',null,null,'','APP','REFACTION1' ):"); + dumpRS(GET_EXPORTED_KEYS, getMetaDataRS(met, GET_EXPORTED_KEYS, + new String [] {"", "APP", "REFACTION1"}, + null, null, null)); + + System.out.println("---------------------------------------"); + + // drop referential action test tables + s.execute("drop table refactnone"); + s.execute("drop table refactupdrestrict"); + s.execute("drop table refactupdnoaction"); + s.execute("drop table refactrestrict"); + s.execute("drop table refactnoaction"); + s.execute("drop table refactcascade"); + s.execute("drop table refactsetnull"); + s.execute("drop table refaction1"); + + // test beetle 5195 + s.execute("create table t1 (c1 int not null, c2 int, c3 int default null, c4 char(10) not null, c5 char(10) default null, c6 char(10) default 'NULL', c7 int default 88)"); + + String schema = "APP"; + String tableName = "T1"; + DatabaseMetaData dmd = con.getMetaData(); + + System.out.println("getColumns for '" + tableName + "'"); + + rs = getMetaDataRS(dmd, GET_COLUMNS, + new String [] {null, schema, tableName, null}, + null, null, null); + + try + { + while (rs.next()) + { + String col = rs.getString(4); + String type = rs.getString(6); + String defval = rs.getString(13); + if (defval == null) + System.out.println(" Next line is real null."); + System.out.println("defval for col " + col + + " type " + type + " DEFAULT '" + defval + "' wasnull " + rs.wasNull()); + } + + } + finally + { + if (rs != null) + rs.close(); + } + s.execute("drop table t1"); + + // tiny test moved over from no longer used metadata2.sql + // This checks for a bug where you get incorrect behavior on a nested connection. + // if you do not get an error, the bug does not occur. + s.execute("create procedure isReadO() "+ + "language java external name " + + "'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.isro'" + + " parameter style java"); + s.execute("call isReadO()"); + + s.close(); + if (con.getAutoCommit() == false) + con.commit(); + + con.close(); + + } + catch (SQLException e) { + dumpSQLExceptions(e); + } + catch (Throwable e) { + System.out.println("FAIL -- unexpected exception:"); + e.printStackTrace(System.out); + } + + System.out.println("Test metadata finished"); + } + + static protected void dumpSQLExceptions (SQLException se) { + System.out.println("FAIL -- unexpected exception"); + while (se != null) { + System.out.print("SQLSTATE("+se.getSQLState()+"):"); + se.printStackTrace(System.out); + se = se.getNextException(); + } + } + + /** + * This method is responsible for executing a metadata query and returning + * the result set. We do it like this so that the metadata.java and + * odbc_metadata.java classes can implement this method in their + * own ways (which is needed because we have to extra work to + * get the ODBC versions of the metadata). + */ + abstract protected ResultSet getMetaDataRS(DatabaseMetaData dmd, int procId, + String [] sArgs, String [] argArray, int [] iArgs, boolean [] bArgs) + throws SQLException; + + /** + * Dump the values in the received result set to output. + */ + protected void dumpRS(ResultSet rs) throws SQLException { + dumpRS(IGNORE_PROC_ID, rs); + } + + /** + * Dump the values in the received result set to output. + */ + abstract protected void dumpRS(int procId, ResultSet s) throws SQLException; + + /** + * Create a connect based on the test arguments passed in. + */ + protected Connection createConnection(String[] args) throws Exception { + + Connection con; + + // use the ij utility to read the property file and + // make the initial connection. + ij.getPropertyArg(args); + con = ij.startJBMS(); + //con.setAutoCommit(true); // make sure it is true + con.setAutoCommit(false); + + return con; + + } +} + Property changes on: java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata_test.java ___________________________________________________________________ Name: svn:eol-style + native Index: java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata.java =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata.java (revision 153312) +++ java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata.java (working copy) @@ -47,778 +47,108 @@ * @author alan */ -public class metadata { +public class metadata extends metadata_test { - public static void main(String[] args) { + /** + * Constructor: + * Just intializes the Connection and Statement fields + * to be used through the test. + */ + public metadata(String [] args) { - DatabaseMetaData met; - Connection con; - Statement s; - ResultSet rs; - ResultSetMetaData rsmet; - - System.out.println("Test metadata starting"); - - try - { - // use the ij utility to read the property file and - // make the initial connection. - ij.getPropertyArg(args); - con = ij.startJBMS(); - //con.setAutoCommit(true); // make sure it is true - con.setAutoCommit(false); + try { - // Create a table with a primary key and some unique keys - // and a default + con = createConnection(args); s = con.createStatement(); - // test decimal type and other numeric types precision, scale, - // and display width after operations, beetle 3875, 3906 - s.execute("create table t (i int, s smallint, r real, "+ - "d double precision, dt date, t time, ts timestamp, "+ - "c char(10), v varchar(40) not null, dc dec(10,2))"); - s.execute("insert into t values(1,2,3.3,4.4,date('1990-05-05'),"+ - "time('12:06:06'),timestamp('1990-07-07 07:07:07.07'),"+ - "'eight','nine', 11.1)"); - - // test decimal type and other numeric types precision, scale, - // and display width after operations, beetle 3875, 3906 - //rs = s.executeQuery("select dc from t where tn = 10 union select dc from t where i = 1"); - rs = s.executeQuery("select dc from t where dc = 11.1 union select dc from t where i = 1"); - rsmet = rs.getMetaData(); - System.out.println("Column display size of the union result is: " + rsmet.getColumnDisplaySize(1)); - rs.close(); - - rs = s.executeQuery("select dc, dc, r+dc, d-dc, dc-d from t"); - rsmet = rs.getMetaData(); - System.out.println("dec(10,2) -- precision: " + rsmet.getPrecision(1) + " scale: " + rsmet.getScale(1) + " display size: " + rsmet.getColumnDisplaySize(1) + " type name: " + rsmet.getColumnTypeName(1)); - System.out.println("dec(10,2) -- precision: " + rsmet.getPrecision(2) + " scale: " + rsmet.getScale(2) + " display size: " + rsmet.getColumnDisplaySize(2) + " type name: " + rsmet.getColumnTypeName(2)); - System.out.println("real + dec(10,2) -- precision: " + rsmet.getPrecision(3) + " scale: " + rsmet.getScale(3) + " display size: " + rsmet.getColumnDisplaySize(3) + " type name: " + rsmet.getColumnTypeName(3)); - System.out.println("double precision - dec(10,2) -- precision: " + rsmet.getPrecision(4) + " scale: " + rsmet.getScale(4) + " display size: " + rsmet.getColumnDisplaySize(4) + " type name: " + rsmet.getColumnTypeName(4)); - // result is double, precision/scale don't make sense - System.out.println("dec(10,2) - double precision -- precision: " + rsmet.getPrecision(5) + " scale: " + rsmet.getScale(5) + " display size: " + rsmet.getColumnDisplaySize(5) + " type name: " + rsmet.getColumnTypeName(5)); - while (rs.next()) - System.out.println("result row: " + rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4) + " " + rs.getString(5)); - rs.close(); - - s.execute("insert into t values(1,2,3.3,4.4,date('1990-05-05'),"+ - "time('12:06:06'),timestamp('1990-07-07 07:07:07.07'),"+ - "'eight','nine', 11.11)"); - - // test decimal/integer static column result scale consistent - // with result set metadata after division, beetle 3901 - rs = s.executeQuery("select dc / 2 from t"); - rsmet = rs.getMetaData(); - System.out.println("Column result scale after division is: " + rsmet.getScale(1)); - while (rs.next()) - System.out.println("dc / 2 = " + rs.getString(1)); - rs.close(); - - - s.execute("create table louie (i int not null default 10, s smallint not null, " + - "c30 char(30) not null, " + - "vc10 varchar(10) not null default 'asdf', " + - "constraint PRIMKEY primary key(vc10, i), " + - "constraint UNIQUEKEY unique(c30, s), " + - "ai bigint generated always as identity (start with -10, increment by 2001))"); - - // Create another unique index on louie - s.execute("create unique index u1 on louie(s, i)"); - // Create a non-unique index on louie - s.execute("create index u2 on louie(s)"); - // Create a view on louie - s.execute("create view screwie as select * from louie"); - - // Create a foreign key - s.execute("create table reftab (vc10 varchar(10), i int, " + - "s smallint, c30 char(30), " + - "s2 smallint, c302 char(30), " + - "dprim decimal(5,1) not null, dfor decimal(5,1) not null, "+ - "constraint PKEY_REFTAB primary key (dprim), " + - "constraint FKEYSELF foreign key (dfor) references reftab, "+ - "constraint FKEY1 foreign key(vc10, i) references louie, " + - "constraint FKEY2 foreign key(c30, s2) references louie (c30, s), "+ - "constraint FKEY3 foreign key(c30, s) references louie (c30, s))"); - - s.execute("create table reftab2 (t2_vc10 varchar(10), t2_i int, " + - "constraint T2_FKEY1 foreign key(t2_vc10, t2_i) references louie)"); - - // Create a table with all types - s.execute("create table alltypes ( "+ - //"bitcol16_______ bit(16), "+ - //"bitvaryingcol32 bit varying(32), "+ - //"tinyintcol tinyint, "+ - "smallintcol smallint, "+ - "intcol int default 20, "+ - "bigintcol bigint, "+ - "realcol real, "+ - "doublepreccol double precision default 10, "+ - "decimalcol10p4s decimal(10,4), "+ - "numericcol20p2s numeric(20,2), "+ - "char8col___ char(8), "+ - "varchar9col varchar(9), "+ - "longvarcharcol long varchar,"+ - //"longvarbinarycol long bit varying,"+ - //"nchar10col nchar(10)" - //+ ", nvarchar8col nvarchar(8)" - //+ ", longnvarchar long nvarchar" - //+ ", - "blobcol blob(3K)" - + ")" ); - // test for beetle 4620 - s.execute("CREATE TABLE INFLIGHT(FLT_NUM CHAR(20) NOT NULL," + - "FLT_ORIGIN CHAR(6), " + - "FLT_DEST CHAR(6), " + - "FLT_AIRCRAFT CHAR(20), " + - "FLT_FLYING_TIME VARCHAR(22), "+ - "FLT_DEPT_TIME CHAR(8), "+ - "FLT_ARR_TIME CHAR(8), "+ - "FLT_NOTES VARCHAR(510), "+ - "FLT_DAYS_OF_WK CHAR(14), "+ - "FLT_CRAFT_PIC VARCHAR(32672), "+ - "PRIMARY KEY(FLT_NUM))"); - - // Create procedures so we can test - // getProcedureColumns() - s.execute("create procedure GETPCTEST1 (" + - // for creating, the procedure's params do not need to exactly match the method's - "out outb VARCHAR(3), a VARCHAR(3), b NUMERIC, c SMALLINT, " + - "e SMALLINT, f INTEGER, g BIGINT, h FLOAT, i DOUBLE PRECISION, " + - "k DATE, l TIME, T TIMESTAMP )"+ - "language java external name " + - "'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc'" + - " parameter style java"); - s.execute("create procedure GETPCTEST2 (pa INTEGER, pb BIGINT)"+ - "language java external name " + - "'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc'" + - " parameter style java"); - s.execute("create procedure GETPCTEST3A (STRING1 VARCHAR(5), out STRING2 VARCHAR(5))"+ - "language java external name " + - "'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc'" + - " parameter style java"); - s.execute("create procedure GETPCTEST3B (in STRING3 VARCHAR(5), inout STRING4 VARCHAR(5))"+ - "language java external name " + - "'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc'" + - " parameter style java"); - s.execute("create procedure GETPCTEST4A() "+ - "language java external name " + - "'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4a'"+ - " parameter style java"); - s.execute("create procedure GETPCTEST4B() "+ - "language java external name " + - "'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b'" + - " parameter style java"); - s.execute("create procedure GETPCTEST4Bx(out retparam INTEGER) "+ - "language java external name " + - "'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b'" + - " parameter style java"); - - met = con.getMetaData(); - - System.out.println("JDBC Driver '" + met.getDriverName() + - "', version " + met.getDriverMajorVersion() + - "." + met.getDriverMinorVersion() + - " (" + met.getDriverVersion() + ")"); - - try { - System.out.println("The URL is: " + met.getURL()); - } catch (Throwable err) { - System.out.println("%%getURL() gave the exception: " + err); - } + } catch (SQLException e) { + dumpSQLExceptions(e); + } + catch (Throwable e) { + System.out.println("FAIL -- unexpected exception:"); + e.printStackTrace(System.out); + } - System.out.println("allTablesAreSelectable(): " + - met.allTablesAreSelectable()); - - System.out.println("maxColumnNameLength(): " + met.getMaxColumnNameLength()); + } - System.out.println(); - System.out.println("getSchemas():"); - dumpRS(met.getSchemas()); + /** + * Makes a call to the "runTest" method in metadata_test.java, + * which will in turn call back here for implementations of + * the abstract methods. + */ + public static void main(String[] args) { - System.out.println(); - System.out.println("getCatalogs():"); - dumpRS(met.getCatalogs()); + new metadata(args).runTest(); - System.out.println("getSearchStringEscape(): " + - met.getSearchStringEscape()); + } - System.out.println("getSQLKeywords(): " + - met.getSQLKeywords()); + /** + * This method is responsible for executing a metadata query and returning + * a result set that complies with the JDBC specification. + */ + protected ResultSet getMetaDataRS(DatabaseMetaData dmd, int procId, + String [] sArgs, String [] argArray, int [] iArgs, boolean [] bArgs) + throws SQLException + { - System.out.println("getDefaultTransactionIsolation(): " + - met.getDefaultTransactionIsolation()); - - System.out.println("getProcedures():"); - dumpRS(met.getProcedures(null, "%", "GETPCTEST%")); - - - /* - * any methods that were not tested above using code written - * specifically for it will now be tested in a generic way. - */ - - - System.out.println("allProceduresAreCallable(): " + - met.allProceduresAreCallable()); - System.out.println("getUserName(): " + - met.getUserName()); - System.out.println("isReadOnly(): " + - met.isReadOnly()); - System.out.println("nullsAreSortedHigh(): " + - met.nullsAreSortedHigh()); - System.out.println("nullsAreSortedLow(): " + - met.nullsAreSortedLow()); - System.out.println("nullsAreSortedAtStart(): " + - met.nullsAreSortedAtStart()); - System.out.println("nullsAreSortedAtEnd(): " + - met.nullsAreSortedAtEnd()); - - - System.out.println("getDatabaseProductName(): " + met.getDatabaseProductName()); - - String v = met.getDatabaseProductVersion(); - int l = v.indexOf('('); - if (l<0) l = v.length(); - v = v.substring(0,l); - System.out.println("getDatabaseProductVersion(): " + v); - System.out.println("getDriverVersion(): " + - met.getDriverVersion()); - System.out.println("usesLocalFiles(): " + - met.usesLocalFiles()); - System.out.println("usesLocalFilePerTable(): " + - met.usesLocalFilePerTable()); - System.out.println("supportsMixedCaseIdentifiers(): " + - met.supportsMixedCaseIdentifiers()); - System.out.println("storesUpperCaseIdentifiers(): " + - met.storesUpperCaseIdentifiers()); - System.out.println("storesLowerCaseIdentifiers(): " + - met.storesLowerCaseIdentifiers()); - System.out.println("storesMixedCaseIdentifiers(): " + - met.storesMixedCaseIdentifiers()); - System.out.println("supportsMixedCaseQuotedIdentifiers(): " + - met.supportsMixedCaseQuotedIdentifiers()); - System.out.println("storesUpperCaseQuotedIdentifiers(): " + - met.storesUpperCaseQuotedIdentifiers()); - System.out.println("storesLowerCaseQuotedIdentifiers(): " + - met.storesLowerCaseQuotedIdentifiers()); - System.out.println("storesMixedCaseQuotedIdentifiers(): " + - met.storesMixedCaseQuotedIdentifiers()); - System.out.println("getIdentifierQuoteString(): " + - met.getIdentifierQuoteString()); - System.out.println("getNumericFunctions(): " + - met.getNumericFunctions()); - System.out.println("getStringFunctions(): " + - met.getStringFunctions()); - System.out.println("getSystemFunctions(): " + - met.getSystemFunctions()); - System.out.println("getTimeDateFunctions(): " + - met.getTimeDateFunctions()); - System.out.println("getExtraNameCharacters(): " + - met.getExtraNameCharacters()); - System.out.println("supportsAlterTableWithAddColumn(): " + - met.supportsAlterTableWithAddColumn()); - System.out.println("supportsAlterTableWithDropColumn(): " + - met.supportsAlterTableWithDropColumn()); - System.out.println("supportsColumnAliasing(): " + - met.supportsColumnAliasing()); - System.out.println("nullPlusNonNullIsNull(): " + - met.nullPlusNonNullIsNull()); - System.out.println("supportsConvert(): " + - met.supportsConvert()); - System.out.println("supportsConvert(Types.INTEGER, Types.SMALLINT): " + - met.supportsConvert(Types.INTEGER, Types.SMALLINT)); - System.out.println("supportsTableCorrelationNames(): " + - met.supportsTableCorrelationNames()); - System.out.println("supportsDifferentTableCorrelationNames(): " + - met.supportsDifferentTableCorrelationNames()); - System.out.println("supportsExpressionsInOrderBy(): " + - met.supportsExpressionsInOrderBy()); - System.out.println("supportsOrderByUnrelated(): " + - met.supportsOrderByUnrelated()); - System.out.println("supportsGroupBy(): " + - met.supportsGroupBy()); - System.out.println("supportsGroupByUnrelated(): " + - met.supportsGroupByUnrelated()); - System.out.println("supportsGroupByBeyondSelect(): " + - met.supportsGroupByBeyondSelect()); - System.out.println("supportsLikeEscapeClause(): " + - met.supportsLikeEscapeClause()); - System.out.println("supportsMultipleResultSets(): " + - met.supportsMultipleResultSets()); - System.out.println("supportsMultipleTransactions(): " + - met.supportsMultipleTransactions()); - System.out.println("supportsNonNullableColumns(): " + - met.supportsNonNullableColumns()); - System.out.println("supportsMinimumSQLGrammar(): " + - met.supportsMinimumSQLGrammar()); - System.out.println("supportsCoreSQLGrammar(): " + - met.supportsCoreSQLGrammar()); - System.out.println("supportsExtendedSQLGrammar(): " + - met.supportsExtendedSQLGrammar()); - System.out.println("supportsANSI92EntryLevelSQL(): " + - met.supportsANSI92EntryLevelSQL()); - System.out.println("supportsANSI92IntermediateSQL(): " + - met.supportsANSI92IntermediateSQL()); - System.out.println("supportsANSI92FullSQL(): " + - met.supportsANSI92FullSQL()); - System.out.println("supportsIntegrityEnhancementFacility(): " + - met.supportsIntegrityEnhancementFacility()); - System.out.println("supportsOuterJoins(): " + - met.supportsOuterJoins()); - System.out.println("supportsFullOuterJoins(): " + - met.supportsFullOuterJoins()); - System.out.println("supportsLimitedOuterJoins(): " + - met.supportsLimitedOuterJoins()); - System.out.println("getSchemaTerm(): " + - met.getSchemaTerm()); - System.out.println("getProcedureTerm(): " + - met.getProcedureTerm()); - System.out.println("getCatalogTerm(): " + - met.getCatalogTerm()); - System.out.println("isCatalogAtStart(): " + - met.isCatalogAtStart()); - System.out.println("getCatalogSeparator(): " + - met.getCatalogSeparator()); - System.out.println("supportsSchemasInDataManipulation(): " + - met.supportsSchemasInDataManipulation()); - System.out.println("supportsSchemasInProcedureCalls(): " + - met.supportsSchemasInProcedureCalls()); - System.out.println("supportsSchemasInTableDefinitions(): " + - met.supportsSchemasInTableDefinitions()); - System.out.println("supportsSchemasInIndexDefinitions(): " + - met.supportsSchemasInIndexDefinitions()); - System.out.println("supportsSchemasInPrivilegeDefinitions(): " + - met.supportsSchemasInPrivilegeDefinitions()); - System.out.println("supportsCatalogsInDataManipulation(): " + - met.supportsCatalogsInDataManipulation()); - System.out.println("supportsCatalogsInProcedureCalls(): " + - met.supportsCatalogsInProcedureCalls()); - System.out.println("supportsCatalogsInTableDefinitions(): " + - met.supportsCatalogsInTableDefinitions()); - System.out.println("supportsCatalogsInIndexDefinitions(): " + - met.supportsCatalogsInIndexDefinitions()); - System.out.println("supportsCatalogsInPrivilegeDefinitions(): " + - met.supportsCatalogsInPrivilegeDefinitions()); - System.out.println("supportsPositionedDelete(): " + - met.supportsPositionedDelete()); - System.out.println("supportsPositionedUpdate(): " + - met.supportsPositionedUpdate()); - System.out.println("supportsSelectForUpdate(): " + - met.supportsSelectForUpdate()); - System.out.println("supportsStoredProcedures(): " + - met.supportsStoredProcedures()); - System.out.println("supportsSubqueriesInComparisons(): " + - met.supportsSubqueriesInComparisons()); - System.out.println("supportsSubqueriesInExists(): " + - met.supportsSubqueriesInExists()); - System.out.println("supportsSubqueriesInIns(): " + - met.supportsSubqueriesInIns()); - System.out.println("supportsSubqueriesInQuantifieds(): " + - met.supportsSubqueriesInQuantifieds()); - System.out.println("supportsCorrelatedSubqueries(): " + - met.supportsCorrelatedSubqueries()); - System.out.println("supportsUnion(): " + - met.supportsUnion()); - System.out.println("supportsUnionAll(): " + - met.supportsUnionAll()); - System.out.println("supportsOpenCursorsAcrossCommit(): " + - met.supportsOpenCursorsAcrossCommit()); - System.out.println("supportsOpenCursorsAcrossRollback(): " + - met.supportsOpenCursorsAcrossRollback()); - System.out.println("supportsOpenStatementsAcrossCommit(): " + - met.supportsOpenStatementsAcrossCommit()); - System.out.println("supportsOpenStatementsAcrossRollback(): " + - met.supportsOpenStatementsAcrossRollback()); - System.out.println("getMaxBinaryLiteralLength(): " + - met.getMaxBinaryLiteralLength()); - System.out.println("getMaxCharLiteralLength(): " + - met.getMaxCharLiteralLength()); - System.out.println("getMaxColumnsInGroupBy(): " + - met.getMaxColumnsInGroupBy()); - System.out.println("getMaxColumnsInIndex(): " + - met.getMaxColumnsInIndex()); - System.out.println("getMaxColumnsInOrderBy(): " + - met.getMaxColumnsInOrderBy()); - System.out.println("getMaxColumnsInSelect(): " + - met.getMaxColumnsInSelect()); - System.out.println("getMaxColumnsInTable(): " + - met.getMaxColumnsInTable()); - System.out.println("getMaxConnections(): " + - met.getMaxConnections()); - System.out.println("getMaxCursorNameLength(): " + - met.getMaxCursorNameLength()); - System.out.println("getMaxIndexLength(): " + - met.getMaxIndexLength()); - System.out.println("getMaxSchemaNameLength(): " + - met.getMaxSchemaNameLength()); - System.out.println("getMaxProcedureNameLength(): " + - met.getMaxProcedureNameLength()); - System.out.println("getMaxCatalogNameLength(): " + - met.getMaxCatalogNameLength()); - System.out.println("getMaxRowSize(): " + - met.getMaxRowSize()); - System.out.println("doesMaxRowSizeIncludeBlobs(): " + - met.doesMaxRowSizeIncludeBlobs()); - System.out.println("getMaxStatementLength(): " + - met.getMaxStatementLength()); - System.out.println("getMaxStatements(): " + - met.getMaxStatements()); - System.out.println("getMaxTableNameLength(): " + - met.getMaxTableNameLength()); - System.out.println("getMaxTablesInSelect(): " + - met.getMaxTablesInSelect()); - System.out.println("getMaxUserNameLength(): " + - met.getMaxUserNameLength()); - System.out.println("supportsTransactions(): " + - met.supportsTransactions()); - System.out.println("supportsTransactionIsolationLevel(Connection.TRANSACTION_NONE): " + - met.supportsTransactionIsolationLevel(Connection.TRANSACTION_NONE)); - System.out.println("supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ): " + - met.supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ)); - System.out.println("supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE): " + - met.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)); - System.out.println("supportsDataDefinitionAndDataManipulationTransactions(): " + - met.supportsDataDefinitionAndDataManipulationTransactions()); - System.out.println("supportsDataManipulationTransactionsOnly(): " + - met.supportsDataManipulationTransactionsOnly()); - System.out.println("dataDefinitionCausesTransactionCommit(): " + - met.dataDefinitionCausesTransactionCommit()); - System.out.println("dataDefinitionIgnoredInTransactions(): " + - met.dataDefinitionIgnoredInTransactions()); - - System.out.println("getConnection(): "+ - ((met.getConnection()==con)?"same connection":"different connection") ); - System.out.println("getProcedureColumns():"); - dumpRS(met.getProcedureColumns(null, "%", "GETPCTEST%", "%")); - - System.out.println("getTables() with TABLE_TYPE in ('SYSTEM TABLE') :"); - String[] tabTypes = new String[1]; - tabTypes[0] = "SYSTEM TABLE"; - dumpRS(met.getTables(null, null, null, - tabTypes)); - System.out.println("getTables() with no types:"); - dumpRS(met.getTables("", null, "%", null)); - - System.out.println("getTables() with TABLE_TYPE in ('VIEW','TABLE') :"); - tabTypes = new String[2]; - tabTypes[0] = "VIEW"; - tabTypes[1] = "TABLE"; - dumpRS(met.getTables(null, null, null, tabTypes)); - - System.out.println("getTableTypes():"); - dumpRS(met.getTableTypes()); - - System.out.println("getColumns():"); - dumpRS(met.getColumns("", null, "", "")); - - System.out.println("getColumns('SYSTABLES'):"); - dumpRS(met.getColumns("", "SYS", "SYSTABLES", null)); - - System.out.println("getColumns('ALLTYPES'):"); - dumpRS(met.getColumns("", "APP", "ALLTYPES", null)); - - System.out.println("getColumns('LOUIE'):"); - dumpRS(met.getColumns("", "APP", "LOUIE", null)); - - // test for beetle 4620 - System.out.println("getColumns('INFLIGHT'):"); - dumpRS(met.getColumns("", "APP", "INFLIGHT", null)); - - System.out.println("getColumnPrivileges():"); - dumpRS(met.getColumnPrivileges("Huey", "Dewey", "Louie", "Frooey")); - - System.out.println("getTablePrivileges():"); - dumpRS(met.getTablePrivileges("Huey", "Dewey", "Louie")); - - System.out.println("getBestRowIdentifier(\"\",null,\"LOUIE\"):"); - dumpRS(met.getBestRowIdentifier("", null, "LOUIE", - DatabaseMetaData.bestRowTransaction, - true)); - - System.out.println("getBestRowIdentifier(\"\",\"SYS\",\"SYSTABLES\"):"); - dumpRS(met.getBestRowIdentifier("", "SYS", "SYSTABLES", - DatabaseMetaData.bestRowTransaction, - true)); - - System.out.println("getVersionColumns():"); - dumpRS(met.getVersionColumns("Huey", "Dewey", "Louie")); - - System.out.println("getPrimaryKeys():"); - dumpRS(met.getPrimaryKeys("", "%", "LOUIE")); - - //beetle 4571 - System.out.println("getPrimaryKeys(null, null, tablename):"); - dumpRS(met.getPrimaryKeys(null, null, "LOUIE")); - - System.out.println("getImportedKeys():"); - dumpRS(met.getImportedKeys(null, null, "%")); - - System.out.println("getExportedKeys():"); - dumpRS(met.getExportedKeys(null, null, "%")); - - System.out.println("---------------------------------------"); - System.out.println("getCrossReference('',null,'louie','',null,'reftab' ):"); - dumpRS(met.getCrossReference("", null, "LOUIE", - "", null, "REFTAB")); - - System.out.println("\ngetCrossReference('','APP','reftab','',null,'reftab' ):"); - dumpRS(met.getCrossReference("", "APP", "REFTAB", - "", null, "REFTAB")); - - System.out.println("\ngetCrossReference('',null,null,'','APP','reftab' ):"); - dumpRS(met.getCrossReference("", null, "%", - "", "APP", "REFTAB")); - System.out.println("\ngetImportedKeys('',null,null,'','APP','reftab' ):"); - dumpRS(met.getImportedKeys("", "APP", "REFTAB")); - - - System.out.println("\ngetCrossReference('',null,'louie','','APP',null):"); - dumpRS(met.getCrossReference("", null, "LOUIE", - "", "APP", "%")); - System.out.println("\ngetExportedKeys('',null,'louie,'','APP',null ):"); - dumpRS(met.getExportedKeys("", null, "LOUIE")); - - - System.out.println("\ngetCrossReference('','badschema','LOUIE','','APP','REFTAB' ):"); - dumpRS(met.getCrossReference("", "BADSCHEMA", "LOUIE", - "", "APP", "REFTAB")); - - - System.out.println("getTypeInfo():"); - dumpRS(met.getTypeInfo()); - - /* NOTE - we call getIndexInfo() only on system tables here - * so that there will be no diffs due to generated names. - */ - // unique indexes on SYSCOLUMNS - System.out.println("getIndexInfo():"); - dumpRS(met.getIndexInfo("", "SYS", "SYSCOLUMNS", true, false)); - - // all indexes on SYSCOLUMNS - System.out.println("getIndexInfo():"); - dumpRS(met.getIndexInfo("", "SYS", "SYSCOLUMNS", false, false)); - - System.out.println("getIndexInfo():"); - dumpRS(met.getIndexInfo("", "SYS", "SYSTABLES", true, false)); - - rs = s.executeQuery("SELECT * FROM SYS.SYSTABLES"); - - System.out.println("getColumns('SYSTABLES'):"); - dumpRS(met.getColumns("", "SYS", "SYSTABLES", null)); - - if (!rs.next()) { - System.out.println("FAIL -- user result set closed by"+ - " intervening getColumns request"); - } - rs.close(); - // - // Test referential actions on delete - // - System.out.println("---------------------------------------"); - //create tables to test that we get the delete and update - // referential action correct - System.out.println("Referential action values"); - System.out.println("RESTRICT = "+ DatabaseMetaData.importedKeyRestrict); - System.out.println("NO ACTION = "+ DatabaseMetaData.importedKeyNoAction); - System.out.println("CASCADE = "+ DatabaseMetaData.importedKeyCascade); - System.out.println("SETNULL = "+ DatabaseMetaData.importedKeySetNull); - System.out.println("SETDEFAULT = "+ DatabaseMetaData.importedKeySetDefault); - s.execute("create table refaction1(a int not null primary key)"); - s.execute("create table refactnone(a int references refaction1(a))"); - s.execute("create table refactrestrict(a int references refaction1(a) on delete restrict)"); - s.execute("create table refactnoaction(a int references refaction1(a) on delete no action)"); - s.execute("create table refactcascade(a int references refaction1(a) on delete cascade)"); - s.execute("create table refactsetnull(a int references refaction1(a) on delete set null)"); - System.out.println("getCrossReference('','APP','REFACTION1','','APP','REFACTIONNONE' ):"); - s.execute("create table refactupdrestrict(a int references refaction1(a) on update restrict)"); - s.execute("create table refactupdnoaction(a int references refaction1(a) on update no action)"); - dumpRS(met.getCrossReference("", "APP", "REFACTION1", "", "APP", "REFACTNONE")); - System.out.println("\ngetCrossReference('','APP','REFACTION1','','APP','REFACTRESTRICT' ):"); - dumpRS(met.getCrossReference("", "APP", "REFACTION1", "", "APP", "REFACTRESTRICT")); - System.out.println("\ngetCrossReference('','APP','REFACTION1','','APP','REFACTNOACTION' ):"); - dumpRS(met.getCrossReference("", "APP", "REFACTION1", "", "APP", "REFACTNOACTION")); - System.out.println("\ngetCrossReference('','APP','REFACTION1','','APP','REFACTCASCADE' ):"); - dumpRS(met.getCrossReference("", "APP", "REFACTION1", "", "APP", "REFACTCASCADE")); - System.out.println("\ngetCrossReference('','APP','REFACTION1','','APP','REFACTSETNULL' ):"); - dumpRS(met.getCrossReference("", "APP", "REFACTION1", "", "APP", "REFACTSETNULL")); - System.out.println("\ngetCrossReference('','APP','REFACTION1','','APP','REFACTUPDRESTRICT' ):"); - dumpRS(met.getCrossReference("", "APP", "REFACTION1", "", "APP", "REFACTUPDRESTRICT")); - System.out.println("\ngetCrossReference('','APP','REFACTION1','','APP','REFACTUPDNOACTION' ):"); - dumpRS(met.getCrossReference("", "APP", "REFACTION1", "", "APP", "REFACTUPDNOACTION")); - ResultSet refrs = met.getImportedKeys("", "APP", "REFACTNONE"); - if (refrs.next()) - { - //check update rule - if (refrs.getShort(11) != DatabaseMetaData.importedKeyNoAction) - System.out.println("\ngetImportedKeys - none update Failed - action = " + refrs.getShort(11) + " required value = " + DatabaseMetaData.importedKeyNoAction); - else - System.out.println("\ngetImportedKeys - none update Passed"); - //check delete rule - if (refrs.getShort(11) != DatabaseMetaData.importedKeyNoAction) - System.out.println("\ngetImportedKeys - none delete Failed - action = " + refrs.getShort(11) + " required value = " + DatabaseMetaData.importedKeyNoAction); - else - System.out.println("\ngetImportedKeys - none delete Passed"); - } - else - System.out.println("\ngetImportedKeys - none Failed no rows"); - - refrs.close(); - - refrs = met.getImportedKeys("", "APP", "REFACTRESTRICT"); - if (refrs.next()) - { - if (refrs.getShort(11) != DatabaseMetaData.importedKeyRestrict) - System.out.println("\ngetImportedKeys - delete Restrict Failed - action = " + refrs.getShort(11) + " required value = " + DatabaseMetaData.importedKeyRestrict); - else - System.out.println("\ngetImportedKeys - delete Restrict Passed"); - } - else - System.out.println("\ngetImportedKeys - delete Restrict Failed no rows"); - refrs.close(); - - refrs = met.getImportedKeys("", "APP", "REFACTNOACTION"); - if (refrs.next()) - { - if (refrs.getShort(11) != DatabaseMetaData.importedKeyNoAction) - System.out.println("\ngetImportedKeys - delete NO ACTION Failed - action = " + refrs.getShort(11) + " required value = " + DatabaseMetaData.importedKeyNoAction); - else - System.out.println("\ngetImportedKeys - delete NO ACTION Passed"); - } - else - System.out.println("\ngetImportedKeys - delete NO ACTION Failed no rows"); - refrs.close(); - - refrs = met.getImportedKeys("", "APP", "REFACTCASCADE"); - if (refrs.next()) - { - if (refrs.getShort(11) != DatabaseMetaData.importedKeyCascade) - System.out.println("\ngetImportedKeys - delete CASCADE Failed - action = " + refrs.getShort(11) + " required value = " + DatabaseMetaData.importedKeyCascade); - else - System.out.println("\ngetImportedKeys - delete CASCADE Passed"); - } - else - System.out.println("\ngetImportedKeys - delete CASCADE Failed no rows"); - refrs.close(); - - refrs = met.getImportedKeys("", "APP", "REFACTSETNULL"); - if (refrs.next()) - { - if (refrs.getShort(11) != DatabaseMetaData.importedKeySetNull) - System.out.println("\ngetImportedKeys - delete SET NULL Failed - action = " + refrs.getShort(11) + " required value = " + DatabaseMetaData.importedKeySetNull); - else - System.out.println("\ngetImportedKeys - delete SET NULL Passed"); - } - else - System.out.println("\ngetImportedKeys - SET NULL Failed no rows"); - refrs.close(); - refrs = met.getImportedKeys("", "APP", "REFACTRESTRICT"); - if (refrs.next()) - { - // test update rule - if (refrs.getShort(11) != DatabaseMetaData.importedKeyRestrict) - System.out.println("\ngetImportedKeys - update Restrict Failed - action = " + refrs.getShort(11) + " required value = " + DatabaseMetaData.importedKeyRestrict); - else - System.out.println("\ngetImportedKeys - update Restrict Passed"); - } - else - System.out.println("\ngetImportedKeys - update Restrict Failed no rows"); - refrs.close(); - - refrs = met.getImportedKeys("", "APP", "REFACTNOACTION"); - if (refrs.next()) - { - if (refrs.getShort(11) != DatabaseMetaData.importedKeyNoAction) - System.out.println("\ngetImportedKeys - update NO ACTION Failed - action = " + refrs.getShort(11) + " required value = " + DatabaseMetaData.importedKeyNoAction); - else - System.out.println("\ngetImportedKeys - update NO ACTION Passed"); - } - else - System.out.println("\ngetImportedKeys - update NO ACTION Failed no rows"); - refrs.close(); - - System.out.println("\ngetExportedKeys('',null,null,'','APP','REFACTION1' ):"); - dumpRS(met.getExportedKeys("", "APP", "REFACTION1")); - System.out.println("---------------------------------------"); - - // drop referential action test tables - s.execute("drop table refactnone"); - s.execute("drop table refactupdrestrict"); - s.execute("drop table refactupdnoaction"); - s.execute("drop table refactrestrict"); - s.execute("drop table refactnoaction"); - s.execute("drop table refactcascade"); - s.execute("drop table refactsetnull"); - s.execute("drop table refaction1"); - - // test beetle 5195 - s.execute("create table t1 (c1 int not null, c2 int, c3 int default null, c4 char(10) not null, c5 char(10) default null, c6 char(10) default 'NULL', c7 int default 88)"); - - String schema = "APP"; - String tableName = "T1"; - DatabaseMetaData dmd = con.getMetaData(); - - System.out.println("getColumns for '" + tableName + "'"); - - rs = dmd.getColumns(null, schema, tableName, null); - try - { - while (rs.next()) - { - String col = rs.getString(4); - String type = rs.getString(6); - String defval = rs.getString(13); - if (defval == null) - System.out.println(" Next line is real null."); - System.out.println("defval for col " + col + - " type " + type + " DEFAULT '" + defval + "' wasnull " + rs.wasNull()); - } - - } - finally - { - if (rs != null) - rs.close(); - } - s.execute("drop table t1"); + switch (procId) { - // tiny test moved over from no longer used metadata2.sql - // This checks for a bug where you get incorrect behavior on a nested connection. - // if you do not get an error, the bug does not occur. - s.execute("create procedure isReadO() "+ - "language java external name " + - "'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.isro'" + - " parameter style java"); - s.execute("call isReadO()"); - - - s.close(); - if (con.getAutoCommit() == false) - con.commit(); + case GET_PROCEDURES: + return dmd.getProcedures(sArgs[0], sArgs[1], sArgs[2]); - con.close(); + case GET_PROCEDURE_COLUMNS: + return dmd.getProcedureColumns(sArgs[0], sArgs[1], sArgs[2], sArgs[3]); - } - catch (SQLException e) { - dumpSQLExceptions(e); - } - catch (Throwable e) { - System.out.println("FAIL -- unexpected exception:"); - e.printStackTrace(System.out); - } + case GET_TABLES: + return dmd.getTables(sArgs[0], sArgs[1], sArgs[2], argArray); - System.out.println("Test metadata finished"); - } + case GET_COLUMNS: + return dmd.getColumns(sArgs[0], sArgs[1], sArgs[2], sArgs[3]); + + case GET_COLUMN_PRIVILEGES: + return dmd.getColumnPrivileges(sArgs[0], sArgs[1], sArgs[2], sArgs[3]); - static private void dumpSQLExceptions (SQLException se) { - System.out.println("FAIL -- unexpected exception"); - while (se != null) { - System.out.print("SQLSTATE("+se.getSQLState()+"):"); - se.printStackTrace(System.out); - se = se.getNextException(); + case GET_TABLE_PRIVILEGES: + return dmd.getTablePrivileges(sArgs[0], sArgs[1], sArgs[2]); + + case GET_BEST_ROW_IDENTIFIER: + return dmd.getBestRowIdentifier(sArgs[0], sArgs[1], sArgs[2], + iArgs[0], bArgs[0]); + + case GET_VERSION_COLUMNS: + return dmd.getVersionColumns(sArgs[0], sArgs[1], sArgs[2]); + + case GET_PRIMARY_KEYS: + return dmd.getPrimaryKeys(sArgs[0], sArgs[1], sArgs[2]); + + case GET_IMPORTED_KEYS: + return dmd.getImportedKeys(sArgs[0], sArgs[1], sArgs[2]); + + case GET_EXPORTED_KEYS: + return dmd.getExportedKeys(sArgs[0], sArgs[1], sArgs[2]); + + case GET_CROSS_REFERENCE: + return dmd.getCrossReference(sArgs[0], sArgs[1], sArgs[2], + sArgs[3], sArgs[4], sArgs[5]); + + case GET_TYPE_INFO: + return dmd.getTypeInfo(); + + case GET_INDEX_INFO: + return dmd.getIndexInfo(sArgs[0], sArgs[1], sArgs[2], + bArgs[0], bArgs[1]); + + default: + // shouldn't get here. + + System.out.println("*** UNEXPECTED PROCEDURE ID ENCOUNTERED: " + procId + "."); + return null; } + } - static void dumpRS(ResultSet s) throws SQLException { + protected void dumpRS(int procId, ResultSet s) throws SQLException { + ResultSetMetaData rsmd = s.getMetaData (); // Get the number of columns in the result set @@ -841,9 +171,11 @@ System.out.println(); // Display data, fetching until end of the result set + StringBuffer errorColumns; while (s.next()) { // Loop through each column, getting the // column data and displaying + errorColumns = new StringBuffer(); String value; for (int i=1; i<=numCols; i++) { if (i > 1) System.out.print(","); @@ -855,10 +187,11 @@ (Integer.valueOf(value).intValue() == -7)) System.out.print("**BOOLEAN_TYPE for VM**"); else - System.out.print(s.getString(i)); + System.out.print(value); } else - System.out.print(s.getString(i)); + System.out.print(value); + } System.out.println(); } @@ -906,5 +239,6 @@ public static void isro() throws SQLException { DriverManager.getConnection("jdbc:default:connection").getMetaData().isReadOnly(); } + } Index: java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/odbc_metadata.out =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/odbc_metadata.out (revision 0) +++ java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/odbc_metadata.out (revision 0) @@ -0,0 +1,502 @@ +=============== Begin ODBC 3.0 Compliance Tests ================= +SQLProcedures: +==> ODBC type/name checking done. +SQLProcedureColumns: +==> ODBC type/name checking done. +SQLTables: +==> ODBC type/name checking done. +SQLColumns: +==> ODBC type/name checking done. +SQLColumnPrivileges: +==> ODBC type/name checking done. +SQLTablePrivileges: +==> ODBC type/name checking done. +SQLSpecialColumns: getBestRowIdentifier +==> ODBC type/name checking done. +SQLSpecialColumns: getVersionColumns +==> ODBC type/name checking done. +SQLPrimaryKeys: +==> ODBC type/name checking done. +SQLForeignKeys: getImportedKeys +==> ODBC type/name checking done. +SQLForeignKeys: getExportedKeys +==> ODBC type/name checking done. +SQLForeignKeys: getCrossReference +==> ODBC type/name checking done. +SQLGetTypeInfo +==> ODBC type/name checking done. +SQLStatistics: +==> ODBC type/name checking done. +=============== End ODBC 3.0 Compliance Tests ================= +Test metadata starting +Column display size of the union result is: 12 +dec(10,2) -- precision: 10 scale: 2 display size: 12 type name: DECIMAL +dec(10,2) -- precision: 10 scale: 2 display size: 12 type name: DECIMAL +real + dec(10,2) -- precision: 7 scale: 0 display size: 13 type name: REAL +double precision - dec(10,2) -- precision: 15 scale: 0 display size: 22 type name: DOUBLE +dec(10,2) - double precision -- precision: 15 scale: 2 display size: 22 type name: DOUBLE +result row: 11.10 11.10 14.4 -6.699999999999999 6.699999999999999 +Column result scale after division is: 23 +dc / 2 = 5.55000000000000000000000 +dc / 2 = 5.55500000000000000000000 +JDBC Driver 'IBM DB2 JDBC Universal Driver Architecture', version 2.4 (2.4.17) +The URL is: jdbc:derby:net://localhost:1527/wombat;create=true +allTablesAreSelectable(): true +maxColumnNameLength(): 30 +getSchemas(): +TABLE_SCHEM[VARCHAR] +APP +NULLID +SQLJ +SYS +SYSCAT +SYSCS_DIAG +SYSCS_UTIL +SYSFUN +SYSIBM +SYSPROC +SYSSTAT +getCatalogs(): +TABLE_CAT[CHAR] +getSearchStringEscape(): +getSQLKeywords(): ALIAS,BIGINT,BOOLEAN,CALL,CLASS,COPY,DB2J_DEBUG,EXECUTE,EXPLAIN,FILE,FILTER,GETCURRENTCONNECTION,INDEX,INSTANCEOF,METHOD,NEW,OFF,PROPERTIES,PUBLICATION,RECOMPILE,REFRESH,RENAME,RUNTIMESTATISTICS,STATEMENT,STATISTICS,TIMING,WAIT +getDefaultTransactionIsolation(): 2 +getProcedures(): +PROCEDURE_CAT[VARCHAR],PROCEDURE_SCHEM[VARCHAR],PROCEDURE_NAME[VARCHAR],NUM_INPUT_PARAMS[INTEGER],NUM_OUTPUT_PARAMS[INTEGER],NUM_RESULT_SETS[INTEGER],REMARKS[VARCHAR],PROCEDURE_TYPE[SMALLINT] +,APP,GETPCTEST1,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc,1 +,APP,GETPCTEST2,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc,1 +,APP,GETPCTEST3A,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc,1 +,APP,GETPCTEST3B,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc,1 +,APP,GETPCTEST4A,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4a,1 +,APP,GETPCTEST4B,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b,1 +,APP,GETPCTEST4BX,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b,1 +allProceduresAreCallable(): true +getUserName(): APP +isReadOnly(): false +nullsAreSortedHigh(): true +nullsAreSortedLow(): false +nullsAreSortedAtStart(): false +nullsAreSortedAtEnd(): false +getDatabaseProductName(): Apache Derby +getDatabaseProductVersion(): 10.1.0.0 alpha +getDriverVersion(): 2.4.17 +usesLocalFiles(): true +usesLocalFilePerTable(): true +supportsMixedCaseIdentifiers(): false +storesUpperCaseIdentifiers(): true +storesLowerCaseIdentifiers(): false +storesMixedCaseIdentifiers(): false +supportsMixedCaseQuotedIdentifiers(): true +storesUpperCaseQuotedIdentifiers(): false +storesLowerCaseQuotedIdentifiers(): false +storesMixedCaseQuotedIdentifiers(): true +getIdentifierQuoteString(): " +getNumericFunctions(): ABS,SQRT +getStringFunctions(): LENGTH,LOWER,LTRIM,RTRIM,SUBSTR,SUBSTRING,UPPER +getSystemFunctions(): CURRENT_USER,getCurrentConnection,runTimeStatistics,SESSION_USER,USER,CURRENT SCHEMA +getTimeDateFunctions(): CURDATE,CURTIME,CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP,EXTRACT +getExtraNameCharacters(): +supportsAlterTableWithAddColumn(): true +supportsAlterTableWithDropColumn(): true +supportsColumnAliasing(): true +nullPlusNonNullIsNull(): true +supportsConvert(): true +supportsConvert(Types.INTEGER, Types.SMALLINT): false +supportsTableCorrelationNames(): true +supportsDifferentTableCorrelationNames(): true +supportsExpressionsInOrderBy(): false +supportsOrderByUnrelated(): false +supportsGroupBy(): true +supportsGroupByUnrelated(): true +supportsGroupByBeyondSelect(): true +supportsLikeEscapeClause(): true +supportsMultipleResultSets(): true +supportsMultipleTransactions(): true +supportsNonNullableColumns(): true +supportsMinimumSQLGrammar(): true +supportsCoreSQLGrammar(): false +supportsExtendedSQLGrammar(): false +supportsANSI92EntryLevelSQL(): true +supportsANSI92IntermediateSQL(): false +supportsANSI92FullSQL(): false +supportsIntegrityEnhancementFacility(): false +supportsOuterJoins(): true +supportsFullOuterJoins(): false +supportsLimitedOuterJoins(): true +getSchemaTerm(): SCHEMA +getProcedureTerm(): PROCEDURE +getCatalogTerm(): CATALOG +isCatalogAtStart(): false +getCatalogSeparator(): +supportsSchemasInDataManipulation(): true +supportsSchemasInProcedureCalls(): true +supportsSchemasInTableDefinitions(): true +supportsSchemasInIndexDefinitions(): true +supportsSchemasInPrivilegeDefinitions(): true +supportsCatalogsInDataManipulation(): false +supportsCatalogsInProcedureCalls(): false +supportsCatalogsInTableDefinitions(): false +supportsCatalogsInIndexDefinitions(): false +supportsCatalogsInPrivilegeDefinitions(): false +supportsPositionedDelete(): true +supportsPositionedUpdate(): true +supportsSelectForUpdate(): true +supportsStoredProcedures(): true +supportsSubqueriesInComparisons(): true +supportsSubqueriesInExists(): true +supportsSubqueriesInIns(): true +supportsSubqueriesInQuantifieds(): true +supportsCorrelatedSubqueries(): true +supportsUnion(): true +supportsUnionAll(): true +supportsOpenCursorsAcrossCommit(): false +supportsOpenCursorsAcrossRollback(): false +supportsOpenStatementsAcrossCommit(): true +supportsOpenStatementsAcrossRollback(): false +getMaxBinaryLiteralLength(): 0 +getMaxCharLiteralLength(): 0 +getMaxColumnsInGroupBy(): 0 +getMaxColumnsInIndex(): 0 +getMaxColumnsInOrderBy(): 0 +getMaxColumnsInSelect(): 0 +getMaxColumnsInTable(): 0 +getMaxConnections(): 0 +getMaxCursorNameLength(): 18 +getMaxIndexLength(): 0 +getMaxSchemaNameLength(): 30 +getMaxProcedureNameLength(): 128 +getMaxCatalogNameLength(): 0 +getMaxRowSize(): 0 +doesMaxRowSizeIncludeBlobs(): true +getMaxStatementLength(): 0 +getMaxStatements(): 0 +getMaxTableNameLength(): 128 +getMaxTablesInSelect(): 0 +getMaxUserNameLength(): 30 +supportsTransactions(): true +supportsTransactionIsolationLevel(Connection.TRANSACTION_NONE): false +supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ): true +supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE): true +supportsDataDefinitionAndDataManipulationTransactions(): true +supportsDataManipulationTransactionsOnly(): false +dataDefinitionCausesTransactionCommit(): false +dataDefinitionIgnoredInTransactions(): false +getConnection(): same connection +getProcedureColumns(): +PROCEDURE_CAT[VARCHAR],PROCEDURE_SCHEM[VARCHAR],PROCEDURE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],COLUMN_TYPE[SMALLINT],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],NUM_PREC_RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],COLUMN_DEF[VARCHAR],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],CHAR_OCTET_LENGTH[INTEGER],ORDINAL_POSITION[INTEGER],IS_NULLABLE[VARCHAR],METHOD_ID[SMALLINT],PARAMETER_ID[SMALLINT] +null,APP,GETPCTEST1,OUTB,4,12,VARCHAR,3,3,null,null,1,null,null,12,null,3,1,YES,12,0 +null,APP,GETPCTEST1,A,1,12,VARCHAR,3,3,null,null,1,null,null,12,null,3,2,YES,12,1 +null,APP,GETPCTEST1,B,1,2,NUMERIC,5,6,0,10,1,null,null,2,null,null,3,YES,12,2 +null,APP,GETPCTEST1,C,1,5,SMALLINT,5,2,0,10,1,null,null,5,null,null,4,YES,12,3 +null,APP,GETPCTEST1,E,1,5,SMALLINT,5,2,0,10,1,null,null,5,null,null,5,YES,12,4 +null,APP,GETPCTEST1,F,1,4,INTEGER,10,4,0,10,1,null,null,4,null,null,6,YES,12,5 +null,APP,GETPCTEST1,G,1,-5,BIGINT,19,8,0,10,1,null,null,-5,null,null,7,YES,12,6 +null,APP,GETPCTEST1,H,1,8,DOUBLE,52,8,null,2,1,null,null,8,null,null,8,YES,12,7 +null,APP,GETPCTEST1,I,1,8,DOUBLE,52,8,null,2,1,null,null,8,null,null,9,YES,12,8 +null,APP,GETPCTEST1,K,1,91,DATE,10,-1,null,2,1,null,null,9,1,null,10,YES,12,9 +null,APP,GETPCTEST1,L,1,92,TIME,8,-1,0,2,1,null,null,9,2,null,11,YES,12,10 +null,APP,GETPCTEST1,T,1,93,TIMESTAMP,26,-1,0,2,1,null,null,9,3,null,12,YES,12,11 +null,APP,GETPCTEST2,PA,1,4,INTEGER,10,4,0,10,1,null,null,4,null,null,1,YES,2,0 +null,APP,GETPCTEST2,PB,1,-5,BIGINT,19,8,0,10,1,null,null,-5,null,null,2,YES,2,1 +null,APP,GETPCTEST3A,STRING1,1,12,VARCHAR,5,5,null,null,1,null,null,12,null,5,1,YES,2,0 +null,APP,GETPCTEST3A,STRING2,4,12,VARCHAR,5,5,null,null,1,null,null,12,null,5,2,YES,2,1 +null,APP,GETPCTEST3B,STRING3,1,12,VARCHAR,5,5,null,null,1,null,null,12,null,5,1,YES,2,0 +null,APP,GETPCTEST3B,STRING4,2,12,VARCHAR,5,5,null,null,1,null,null,12,null,5,2,YES,2,1 +null,APP,GETPCTEST4BX,RETPARAM,4,4,INTEGER,10,4,0,10,1,null,null,4,null,null,1,YES,1,0 +getTables() with TABLE_TYPE in ('SYSTEM TABLE') : +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],TABLE_TYPE[VARCHAR],REMARKS[VARCHAR] +,SYS,SYSALIASES,SYSTEM TABLE, +,SYS,SYSCHECKS,SYSTEM TABLE, +,SYS,SYSCOLUMNS,SYSTEM TABLE, +,SYS,SYSCONGLOMERATES,SYSTEM TABLE, +,SYS,SYSCONSTRAINTS,SYSTEM TABLE, +,SYS,SYSDEPENDS,SYSTEM TABLE, +,SYS,SYSFILES,SYSTEM TABLE, +,SYS,SYSFOREIGNKEYS,SYSTEM TABLE, +,SYS,SYSKEYS,SYSTEM TABLE, +,SYS,SYSSCHEMAS,SYSTEM TABLE, +,SYS,SYSSTATEMENTS,SYSTEM TABLE, +,SYS,SYSSTATISTICS,SYSTEM TABLE, +,SYS,SYSTABLES,SYSTEM TABLE, +,SYS,SYSTRIGGERS,SYSTEM TABLE, +,SYS,SYSVIEWS,SYSTEM TABLE, +,SYSIBM,SYSDUMMY1,SYSTEM TABLE, +getTables() with no types: +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],TABLE_TYPE[VARCHAR],REMARKS[VARCHAR] +,SYS,SYSALIASES,SYSTEM TABLE, +,SYS,SYSCHECKS,SYSTEM TABLE, +,SYS,SYSCOLUMNS,SYSTEM TABLE, +,SYS,SYSCONGLOMERATES,SYSTEM TABLE, +,SYS,SYSCONSTRAINTS,SYSTEM TABLE, +,SYS,SYSDEPENDS,SYSTEM TABLE, +,SYS,SYSFILES,SYSTEM TABLE, +,SYS,SYSFOREIGNKEYS,SYSTEM TABLE, +,SYS,SYSKEYS,SYSTEM TABLE, +,SYS,SYSSCHEMAS,SYSTEM TABLE, +,SYS,SYSSTATEMENTS,SYSTEM TABLE, +,SYS,SYSSTATISTICS,SYSTEM TABLE, +,SYS,SYSTABLES,SYSTEM TABLE, +,SYS,SYSTRIGGERS,SYSTEM TABLE, +,SYS,SYSVIEWS,SYSTEM TABLE, +,SYSIBM,SYSDUMMY1,SYSTEM TABLE, +,APP,ALLTYPES,TABLE, +,APP,INFLIGHT,TABLE, +,APP,LOUIE,TABLE, +,APP,REFTAB,TABLE, +,APP,REFTAB2,TABLE, +,APP,T,TABLE, +,APP,SCREWIE,VIEW, +getTables() with TABLE_TYPE in ('VIEW','TABLE') : +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],TABLE_TYPE[VARCHAR],REMARKS[VARCHAR] +,APP,ALLTYPES,TABLE, +,APP,INFLIGHT,TABLE, +,APP,LOUIE,TABLE, +,APP,REFTAB,TABLE, +,APP,REFTAB2,TABLE, +,APP,T,TABLE, +,APP,SCREWIE,VIEW, +getTableTypes(): +TABLE_TYPE[VARCHAR] +SYSTEM TABLE +TABLE +VIEW +getColumns(): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],NUM_PREC_RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],COLUMN_DEF[VARCHAR],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],CHAR_OCTET_LENGTH[INTEGER],ORDINAL_POSITION[INTEGER],IS_NULLABLE[VARCHAR] +getColumns('SYSTABLES'): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],NUM_PREC_RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],COLUMN_DEF[VARCHAR],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],CHAR_OCTET_LENGTH[INTEGER],ORDINAL_POSITION[INTEGER],IS_NULLABLE[VARCHAR] +,SYS,SYSTABLES,TABLEID,1,CHAR,36,72,null,null,0,,null,1,null,72,1,NO +,SYS,SYSTABLES,TABLENAME,12,VARCHAR,128,256,null,null,0,,null,12,null,256,2,NO +,SYS,SYSTABLES,TABLETYPE,1,CHAR,1,2,null,null,0,,null,1,null,2,3,NO +,SYS,SYSTABLES,SCHEMAID,1,CHAR,36,72,null,null,0,,null,1,null,72,4,NO +,SYS,SYSTABLES,LOCKGRANULARITY,1,CHAR,1,2,null,null,0,,null,1,null,2,5,NO +getColumns('ALLTYPES'): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],NUM_PREC_RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],COLUMN_DEF[VARCHAR],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],CHAR_OCTET_LENGTH[INTEGER],ORDINAL_POSITION[INTEGER],IS_NULLABLE[VARCHAR] +,APP,ALLTYPES,SMALLINTCOL,5,SMALLINT,5,2,0,10,1,,null,5,null,null,1,YES +,APP,ALLTYPES,INTCOL,4,INTEGER,10,4,0,10,1,,20,4,null,null,2,YES +,APP,ALLTYPES,BIGINTCOL,-5,BIGINT,19,8,0,10,1,,null,-5,null,null,3,YES +,APP,ALLTYPES,REALCOL,7,REAL,23,4,null,2,1,,null,7,null,null,4,YES +,APP,ALLTYPES,DOUBLEPRECCOL,8,DOUBLE,52,8,null,2,1,,10,8,null,null,5,YES +,APP,ALLTYPES,DECIMALCOL10P4S,3,DECIMAL,10,13,4,10,1,,null,3,null,null,6,YES +,APP,ALLTYPES,NUMERICCOL20P2S,2,NUMERIC,20,23,2,10,1,,null,2,null,null,7,YES +,APP,ALLTYPES,CHAR8COL___,1,CHAR,8,16,null,null,1,,null,1,null,16,8,YES +,APP,ALLTYPES,VARCHAR9COL,12,VARCHAR,9,18,null,null,1,,null,12,null,18,9,YES +,APP,ALLTYPES,LONGVARCHARCOL,-1,LONG VARCHAR,32700,32700,null,null,1,,null,-1,null,null,10,YES +,APP,ALLTYPES,BLOBCOL,2004,BLOB,3072,3072,null,null,1,,null,2004,null,null,11,YES +getColumns('LOUIE'): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],NUM_PREC_RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],COLUMN_DEF[VARCHAR],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],CHAR_OCTET_LENGTH[INTEGER],ORDINAL_POSITION[INTEGER],IS_NULLABLE[VARCHAR] +,APP,LOUIE,I,4,INTEGER,10,4,0,10,0,,10,4,null,null,1,NO +,APP,LOUIE,S,5,SMALLINT,5,2,0,10,0,,null,5,null,null,2,NO +,APP,LOUIE,C30,1,CHAR,30,60,null,null,0,,null,1,null,60,3,NO +,APP,LOUIE,VC10,12,VARCHAR,10,20,null,null,0,,'asdf',12,null,20,4,NO +,APP,LOUIE,AI,-5,BIGINT,19,8,0,10,0,,AUTOINCREMENT: start -10 increment 2001,-5,null,null,5,NO +getColumns('INFLIGHT'): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],NUM_PREC_RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],COLUMN_DEF[VARCHAR],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],CHAR_OCTET_LENGTH[INTEGER],ORDINAL_POSITION[INTEGER],IS_NULLABLE[VARCHAR] +,APP,INFLIGHT,FLT_NUM,1,CHAR,20,40,null,null,0,,null,1,null,40,1,NO +,APP,INFLIGHT,FLT_ORIGIN,1,CHAR,6,12,null,null,1,,null,1,null,12,2,YES +,APP,INFLIGHT,FLT_DEST,1,CHAR,6,12,null,null,1,,null,1,null,12,3,YES +,APP,INFLIGHT,FLT_AIRCRAFT,1,CHAR,20,40,null,null,1,,null,1,null,40,4,YES +,APP,INFLIGHT,FLT_FLYING_TIME,12,VARCHAR,22,44,null,null,1,,null,12,null,44,5,YES +,APP,INFLIGHT,FLT_DEPT_TIME,1,CHAR,8,16,null,null,1,,null,1,null,16,6,YES +,APP,INFLIGHT,FLT_ARR_TIME,1,CHAR,8,16,null,null,1,,null,1,null,16,7,YES +,APP,INFLIGHT,FLT_NOTES,12,VARCHAR,510,1020,null,null,1,,null,12,null,1020,8,YES +,APP,INFLIGHT,FLT_DAYS_OF_WK,1,CHAR,14,28,null,null,1,,null,1,null,28,9,YES +,APP,INFLIGHT,FLT_CRAFT_PIC,12,VARCHAR,32672,65344,null,null,1,,null,12,null,65344,10,YES +getColumnPrivileges(): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],GRANTOR[VARCHAR],GRANTEE[VARCHAR],PRIVILEGE[VARCHAR],IS_GRANTABLE[VARCHAR] +getTablePrivileges(): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],GRANTOR[VARCHAR],GRANTEE[VARCHAR],PRIVILEGE[VARCHAR],IS_GRANTABLE[VARCHAR] +getBestRowIdentifier("",null,"LOUIE"): +SCOPE[SMALLINT],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],PSEUDO_COLUMN[SMALLINT] +2,I,4,INTEGER,4,4,10,1 +2,VC10,12,VARCHAR,10,20,null,1 +getBestRowIdentifier("","SYS","SYSTABLES"): +SCOPE[SMALLINT],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],PSEUDO_COLUMN[SMALLINT] +2,TABLEID,1,CHAR,36,72,null,1 +getVersionColumns(): +SCOPE[SMALLINT],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],PSEUDO_COLUMN[SMALLINT] +getPrimaryKeys(): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],PK_NAME[VARCHAR] +,APP,LOUIE,I,2,PRIMKEY +,APP,LOUIE,VC10,1,PRIMKEY +getPrimaryKeys(null, null, tablename): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],PK_NAME[VARCHAR] +,APP,LOUIE,I,2,PRIMKEY +,APP,LOUIE,VC10,1,PRIMKEY +getImportedKeys(): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,LOUIE,VC10,,APP,REFTAB2,T2_VC10,1,3,3,T2_FKEY1,PRIMKEY,7 +,APP,LOUIE,VC10,,APP,REFTAB,VC10,1,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB2,T2_I,2,3,3,T2_FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB,I,2,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S,2,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S2,2,3,3,FKEY2,UNIQUEKEY,7 +,APP,REFTAB,DPRIM,,APP,REFTAB,DFOR,1,3,3,FKEYSELF,PKEY_REFTAB,7 +getExportedKeys(): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,LOUIE,VC10,,APP,REFTAB,VC10,1,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB,I,2,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S2,2,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S,2,3,3,FKEY3,UNIQUEKEY,7 +,APP,REFTAB,DPRIM,,APP,REFTAB,DFOR,1,3,3,FKEYSELF,PKEY_REFTAB,7 +,APP,LOUIE,VC10,,APP,REFTAB2,T2_VC10,1,3,3,T2_FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB2,T2_I,2,3,3,T2_FKEY1,PRIMKEY,7 +----- +getCrossReference('',null,'louie','',null,'reftab' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,LOUIE,VC10,,APP,REFTAB,VC10,1,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB,I,2,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S2,2,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S,2,3,3,FKEY3,UNIQUEKEY,7 +getCrossReference('','APP','reftab','',null,'reftab' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFTAB,DPRIM,,APP,REFTAB,DFOR,1,3,3,FKEYSELF,PKEY_REFTAB,7 +getCrossReference('',null,null,'','APP','reftab' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,LOUIE,VC10,,APP,REFTAB,VC10,1,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB,I,2,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S2,2,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S,2,3,3,FKEY3,UNIQUEKEY,7 +,APP,REFTAB,DPRIM,,APP,REFTAB,DFOR,1,3,3,FKEYSELF,PKEY_REFTAB,7 +getImportedKeys('',null,null,'','APP','reftab' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,LOUIE,VC10,,APP,REFTAB,VC10,1,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB,I,2,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S,2,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S2,2,3,3,FKEY2,UNIQUEKEY,7 +,APP,REFTAB,DPRIM,,APP,REFTAB,DFOR,1,3,3,FKEYSELF,PKEY_REFTAB,7 +getCrossReference('',null,'louie','','APP',null): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,LOUIE,VC10,,APP,REFTAB,VC10,1,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB,I,2,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S2,2,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S,2,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,VC10,,APP,REFTAB2,T2_VC10,1,3,3,T2_FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB2,T2_I,2,3,3,T2_FKEY1,PRIMKEY,7 +getExportedKeys('',null,'louie,'','APP',null ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,LOUIE,VC10,,APP,REFTAB,VC10,1,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB,I,2,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S2,2,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S,2,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,VC10,,APP,REFTAB2,T2_VC10,1,3,3,T2_FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB2,T2_I,2,3,3,T2_FKEY1,PRIMKEY,7 +getCrossReference('','badschema','LOUIE','','APP','REFTAB' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +getTypeInfo(): +TYPE_NAME[VARCHAR],DATA_TYPE[SMALLINT],COLUMN_SIZE[INTEGER],LITERAL_PREFIX[VARCHAR],LITERAL_SUFFIX[VARCHAR],CREATE_PARAMS[VARCHAR],NULLABLE[SMALLINT],CASE_SENSITIVE[SMALLINT],SEARCHABLE[SMALLINT],UNSIGNED_ATTRIBUTE[SMALLINT],FIXED_PREC_SCALE[SMALLINT],AUTO_UNIQUE_VAL[SMALLINT],LOCAL_TYPE_NAME[VARCHAR],MINIMUM_SCALE[SMALLINT],MAXIMUM_SCALE[SMALLINT],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],NUM_PREC_RADIX[INTEGER],INTERVAL_PRECISION[SMALLINT] +BIGINT,-5,19,null,null,null,1,0,2,0,0,1,BIGINT,0,0,-5,null,10,null +LONG VARCHAR FOR BIT DATA,-4,32700,X',',null,1,0,2,null,0,null,LONG VARCHAR FOR BIT DATA,null,null,-4,null,null,null +VARCHAR () FOR BIT DATA,-3,32762,X',',length,1,0,2,null,0,null,VARCHAR () FOR BIT DATA,null,null,-3,null,null,null +CHAR () FOR BIT DATA,-2,254,X',',length,1,0,2,null,0,null,CHAR () FOR BIT DATA,null,null,-2,null,null,null +LONG VARCHAR,-1,32700,',',null,1,1,3,null,0,null,LONG VARCHAR,null,null,-1,null,null,null +LONG NVARCHAR,-1,32700,',',null,1,1,3,null,0,null,LONG NVARCHAR,null,null,-1,null,null,null +CHAR,1,254,',',length,1,1,3,null,0,null,CHAR,null,null,1,null,null,null +NATIONAL CHAR,1,2147483647,',',length,1,1,3,null,0,null,NATIONAL CHAR,null,null,1,null,null,null +NUMERIC,2,31,null,null,precision,scale,1,0,2,0,1,0,NUMERIC,0,32767,2,null,10,null +DECIMAL,3,31,null,null,precision,scale,1,0,2,0,1,0,DECIMAL,0,32767,3,null,10,null +INTEGER,4,10,null,null,null,1,0,2,0,0,1,INTEGER,0,0,4,null,10,null +SMALLINT,5,5,null,null,null,1,0,2,0,0,1,SMALLINT,0,0,5,null,10,null +FLOAT,6,52,null,null,precision,1,0,2,0,0,0,FLOAT,null,null,6,null,2,null +REAL,7,23,null,null,null,1,0,2,0,0,0,REAL,null,null,7,null,2,null +DOUBLE,8,52,null,null,null,1,0,2,0,0,0,DOUBLE,null,null,8,null,2,null +VARCHAR,12,32672,',',length,1,1,3,null,0,null,VARCHAR,null,null,12,null,null,null +NATIONAL CHAR VARYING,12,2147483647,',',length,1,1,3,null,0,null,NATIONAL CHAR VARYING,null,null,12,null,null,null +DATE,91,10,DATE',',null,1,0,2,1,0,0,DATE,0,0,9,1,2,null +TIME,92,0,TIME',',null,1,0,2,1,0,0,TIME,0,0,9,2,2,null +TIMESTAMP,93,0,TIMESTAMP',',null,1,0,2,1,0,0,TIMESTAMP,0,0,9,3,2,null +BLOB,2004,2147483647,null,null,length,1,0,1,null,0,null,BLOB,null,null,2004,null,null,null +CLOB,2005,2147483647,',',length,1,1,1,null,0,null,CLOB,null,null,2005,null,null,null +getIndexInfo(): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],NON_UNIQUE[SMALLINT],INDEX_QUALIFIER[VARCHAR],INDEX_NAME[VARCHAR],TYPE[SMALLINT],ORDINAL_POSITION[SMALLINT],COLUMN_NAME[VARCHAR],ASC_OR_DESC[CHAR],CARDINALITY[INTEGER],PAGES[INTEGER],FILTER_CONDITION[VARCHAR] +,SYS,SYSCOLUMNS,0,,SYSCOLUMNS_INDEX1,3,1,REFERENCEID,A,null,null,null +,SYS,SYSCOLUMNS,0,,SYSCOLUMNS_INDEX1,3,2,COLUMNNAME,A,null,null,null +getIndexInfo(): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],NON_UNIQUE[SMALLINT],INDEX_QUALIFIER[VARCHAR],INDEX_NAME[VARCHAR],TYPE[SMALLINT],ORDINAL_POSITION[SMALLINT],COLUMN_NAME[VARCHAR],ASC_OR_DESC[CHAR],CARDINALITY[INTEGER],PAGES[INTEGER],FILTER_CONDITION[VARCHAR] +,SYS,SYSCOLUMNS,0,,SYSCOLUMNS_INDEX1,3,1,REFERENCEID,A,null,null,null +,SYS,SYSCOLUMNS,0,,SYSCOLUMNS_INDEX1,3,2,COLUMNNAME,A,null,null,null +,SYS,SYSCOLUMNS,1,,SYSCOLUMNS_INDEX2,3,1,COLUMNDEFAULTID,A,null,null,null +getIndexInfo(): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],NON_UNIQUE[SMALLINT],INDEX_QUALIFIER[VARCHAR],INDEX_NAME[VARCHAR],TYPE[SMALLINT],ORDINAL_POSITION[SMALLINT],COLUMN_NAME[VARCHAR],ASC_OR_DESC[CHAR],CARDINALITY[INTEGER],PAGES[INTEGER],FILTER_CONDITION[VARCHAR] +,SYS,SYSTABLES,0,,SYSTABLES_INDEX1,3,1,TABLENAME,A,null,null,null +,SYS,SYSTABLES,0,,SYSTABLES_INDEX1,3,2,SCHEMAID,A,null,null,null +,SYS,SYSTABLES,0,,SYSTABLES_INDEX2,3,1,TABLEID,A,null,null,null +getColumns('SYSTABLES'): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],NUM_PREC_RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],COLUMN_DEF[VARCHAR],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],CHAR_OCTET_LENGTH[INTEGER],ORDINAL_POSITION[INTEGER],IS_NULLABLE[VARCHAR] +,SYS,SYSTABLES,TABLEID,1,CHAR,36,72,null,null,0,,null,1,null,72,1,NO +,SYS,SYSTABLES,TABLENAME,12,VARCHAR,128,256,null,null,0,,null,12,null,256,2,NO +,SYS,SYSTABLES,TABLETYPE,1,CHAR,1,2,null,null,0,,null,1,null,2,3,NO +,SYS,SYSTABLES,SCHEMAID,1,CHAR,36,72,null,null,0,,null,1,null,72,4,NO +,SYS,SYSTABLES,LOCKGRANULARITY,1,CHAR,1,2,null,null,0,,null,1,null,2,5,NO +OK -- user result set closed by intervening OBDC getColumns request; this was expected because of the way the test works. +----- +Referential action values +RESTRICT = 1 +NO ACTION = 3 +CASCADE = 0 +SETNULL = 2 +SETDEFAULT = 4 +getCrossReference('','APP','REFACTION1','','APP','REFACTIONNONE' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFACTION1,A,,APP,REFACTNONE,A,1,3,3,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +getCrossReference('','APP','REFACTION1','','APP','REFACTRESTRICT' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFACTION1,A,,APP,REFACTRESTRICT,A,1,3,1,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +getCrossReference('','APP','REFACTION1','','APP','REFACTNOACTION' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFACTION1,A,,APP,REFACTNOACTION,A,1,3,3,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +getCrossReference('','APP','REFACTION1','','APP','REFACTCASCADE' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFACTION1,A,,APP,REFACTCASCADE,A,1,3,0,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +getCrossReference('','APP','REFACTION1','','APP','REFACTSETNULL' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFACTION1,A,,APP,REFACTSETNULL,A,1,3,2,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +getCrossReference('','APP','REFACTION1','','APP','REFACTUPDRESTRICT' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFACTION1,A,,APP,REFACTUPDRESTRICT,A,1,1,3,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +getCrossReference('','APP','REFACTION1','','APP','REFACTUPDNOACTION' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFACTION1,A,,APP,REFACTUPDNOACTION,A,1,3,3,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +getImportedKeys - none update Passed +getImportedKeys - none delete Passed +getImportedKeys - delete Restrict Passed +getImportedKeys - delete NO ACTION Passed +getImportedKeys - delete CASCADE Passed +getImportedKeys - delete SET NULL Passed +getImportedKeys - update Restrict Passed +getImportedKeys - update NO ACTION Passed +getExportedKeys('',null,null,'','APP','REFACTION1' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFACTION1,A,,APP,REFACTCASCADE,A,1,3,0,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +,APP,REFACTION1,A,,APP,REFACTNOACTION,A,1,3,3,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +,APP,REFACTION1,A,,APP,REFACTNONE,A,1,3,3,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +,APP,REFACTION1,A,,APP,REFACTRESTRICT,A,1,3,1,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +,APP,REFACTION1,A,,APP,REFACTSETNULL,A,1,3,2,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +,APP,REFACTION1,A,,APP,REFACTUPDNOACTION,A,1,3,3,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +,APP,REFACTION1,A,,APP,REFACTUPDRESTRICT,A,1,1,3,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +----- +getColumns for 'T1' + Next line is real null. +defval for col C1 type INTEGER DEFAULT 'null' wasnull true + Next line is real null. +defval for col C2 type INTEGER DEFAULT 'null' wasnull true +defval for col C3 type INTEGER DEFAULT 'NULL' wasnull false + Next line is real null. +defval for col C4 type CHAR DEFAULT 'null' wasnull true +defval for col C5 type CHAR DEFAULT 'NULL' wasnull false +defval for col C6 type CHAR DEFAULT ''NULL'' wasnull false +defval for col C7 type INTEGER DEFAULT '88' wasnull false +Test metadata finished Property changes on: java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/odbc_metadata.out ___________________________________________________________________ Name: svn:eol-style + native Index: java/testing/org/apache/derbyTesting/functionTests/master/odbc_metadata.out =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/master/odbc_metadata.out (revision 0) +++ java/testing/org/apache/derbyTesting/functionTests/master/odbc_metadata.out (revision 0) @@ -0,0 +1,502 @@ +=============== Begin ODBC 3.0 Compliance Tests ================= +SQLProcedures: +==> ODBC type/name checking done. +SQLProcedureColumns: +==> ODBC type/name checking done. +SQLTables: +==> ODBC type/name checking done. +SQLColumns: +==> ODBC type/name checking done. +SQLColumnPrivileges: +==> ODBC type/name checking done. +SQLTablePrivileges: +==> ODBC type/name checking done. +SQLSpecialColumns: getBestRowIdentifier +==> ODBC type/name checking done. +SQLSpecialColumns: getVersionColumns +==> ODBC type/name checking done. +SQLPrimaryKeys: +==> ODBC type/name checking done. +SQLForeignKeys: getImportedKeys +==> ODBC type/name checking done. +SQLForeignKeys: getExportedKeys +==> ODBC type/name checking done. +SQLForeignKeys: getCrossReference +==> ODBC type/name checking done. +SQLGetTypeInfo +==> ODBC type/name checking done. +SQLStatistics: +==> ODBC type/name checking done. +=============== End ODBC 3.0 Compliance Tests ================= +Test metadata starting +Column display size of the union result is: 13 +dec(10,2) -- precision: 10 scale: 2 display size: 13 type name: DECIMAL +dec(10,2) -- precision: 10 scale: 2 display size: 13 type name: DECIMAL +real + dec(10,2) -- precision: 7 scale: 0 display size: 13 type name: REAL +double precision - dec(10,2) -- precision: 15 scale: 0 display size: 22 type name: DOUBLE +dec(10,2) - double precision -- precision: 15 scale: 2 display size: 22 type name: DOUBLE +result row: 11.10 11.10 14.4 -6.699999999999999 6.699999999999999 +Column result scale after division is: 23 +dc / 2 = 5.55000000000000000000000 +dc / 2 = 5.55500000000000000000000 +JDBC Driver 'Apache Derby Embedded JDBC Driver', version 10.1 (10.1.0.0 alpha) +The URL is: jdbc:derby:wombat +allTablesAreSelectable(): true +maxColumnNameLength(): 30 +getSchemas(): +TABLE_SCHEM[VARCHAR] +APP +NULLID +SQLJ +SYS +SYSCAT +SYSCS_DIAG +SYSCS_UTIL +SYSFUN +SYSIBM +SYSPROC +SYSSTAT +getCatalogs(): +TABLE_CAT[CHAR] +getSearchStringEscape(): +getSQLKeywords(): ALIAS,BIGINT,BOOLEAN,CALL,CLASS,COPY,DB2J_DEBUG,EXECUTE,EXPLAIN,FILE,FILTER,GETCURRENTCONNECTION,INDEX,INSTANCEOF,METHOD,NEW,OFF,PROPERTIES,PUBLICATION,RECOMPILE,REFRESH,RENAME,RUNTIMESTATISTICS,STATEMENT,STATISTICS,TIMING,WAIT +getDefaultTransactionIsolation(): 2 +getProcedures(): +PROCEDURE_CAT[VARCHAR],PROCEDURE_SCHEM[VARCHAR],PROCEDURE_NAME[VARCHAR],NUM_INPUT_PARAMS[INTEGER],NUM_OUTPUT_PARAMS[INTEGER],NUM_RESULT_SETS[INTEGER],REMARKS[VARCHAR],PROCEDURE_TYPE[SMALLINT] +,APP,GETPCTEST1,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc,1 +,APP,GETPCTEST2,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc,1 +,APP,GETPCTEST3A,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc,1 +,APP,GETPCTEST3B,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc,1 +,APP,GETPCTEST4A,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4a,1 +,APP,GETPCTEST4B,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b,1 +,APP,GETPCTEST4BX,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b,1 +allProceduresAreCallable(): true +getUserName(): APP +isReadOnly(): false +nullsAreSortedHigh(): true +nullsAreSortedLow(): false +nullsAreSortedAtStart(): false +nullsAreSortedAtEnd(): false +getDatabaseProductName(): Apache Derby +getDatabaseProductVersion(): 10.1.0.0 alpha +getDriverVersion(): 10.1.0.0 alpha +usesLocalFiles(): true +usesLocalFilePerTable(): true +supportsMixedCaseIdentifiers(): false +storesUpperCaseIdentifiers(): true +storesLowerCaseIdentifiers(): false +storesMixedCaseIdentifiers(): false +supportsMixedCaseQuotedIdentifiers(): true +storesUpperCaseQuotedIdentifiers(): false +storesLowerCaseQuotedIdentifiers(): false +storesMixedCaseQuotedIdentifiers(): true +getIdentifierQuoteString(): " +getNumericFunctions(): ABS,SQRT +getStringFunctions(): LENGTH,LOWER,LTRIM,RTRIM,SUBSTR,SUBSTRING,UPPER +getSystemFunctions(): CURRENT_USER,getCurrentConnection,runTimeStatistics,SESSION_USER,USER,CURRENT SCHEMA +getTimeDateFunctions(): CURDATE,CURTIME,CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP,EXTRACT +getExtraNameCharacters(): +supportsAlterTableWithAddColumn(): true +supportsAlterTableWithDropColumn(): true +supportsColumnAliasing(): true +nullPlusNonNullIsNull(): true +supportsConvert(): true +supportsConvert(Types.INTEGER, Types.SMALLINT): false +supportsTableCorrelationNames(): true +supportsDifferentTableCorrelationNames(): true +supportsExpressionsInOrderBy(): false +supportsOrderByUnrelated(): false +supportsGroupBy(): true +supportsGroupByUnrelated(): true +supportsGroupByBeyondSelect(): true +supportsLikeEscapeClause(): true +supportsMultipleResultSets(): true +supportsMultipleTransactions(): true +supportsNonNullableColumns(): true +supportsMinimumSQLGrammar(): true +supportsCoreSQLGrammar(): false +supportsExtendedSQLGrammar(): false +supportsANSI92EntryLevelSQL(): false +supportsANSI92IntermediateSQL(): false +supportsANSI92FullSQL(): false +supportsIntegrityEnhancementFacility(): false +supportsOuterJoins(): true +supportsFullOuterJoins(): false +supportsLimitedOuterJoins(): true +getSchemaTerm(): SCHEMA +getProcedureTerm(): PROCEDURE +getCatalogTerm(): CATALOG +isCatalogAtStart(): false +getCatalogSeparator(): +supportsSchemasInDataManipulation(): true +supportsSchemasInProcedureCalls(): true +supportsSchemasInTableDefinitions(): true +supportsSchemasInIndexDefinitions(): true +supportsSchemasInPrivilegeDefinitions(): true +supportsCatalogsInDataManipulation(): false +supportsCatalogsInProcedureCalls(): false +supportsCatalogsInTableDefinitions(): false +supportsCatalogsInIndexDefinitions(): false +supportsCatalogsInPrivilegeDefinitions(): false +supportsPositionedDelete(): true +supportsPositionedUpdate(): true +supportsSelectForUpdate(): true +supportsStoredProcedures(): true +supportsSubqueriesInComparisons(): true +supportsSubqueriesInExists(): true +supportsSubqueriesInIns(): true +supportsSubqueriesInQuantifieds(): true +supportsCorrelatedSubqueries(): true +supportsUnion(): true +supportsUnionAll(): true +supportsOpenCursorsAcrossCommit(): false +supportsOpenCursorsAcrossRollback(): false +supportsOpenStatementsAcrossCommit(): true +supportsOpenStatementsAcrossRollback(): false +getMaxBinaryLiteralLength(): 0 +getMaxCharLiteralLength(): 0 +getMaxColumnsInGroupBy(): 0 +getMaxColumnsInIndex(): 0 +getMaxColumnsInOrderBy(): 0 +getMaxColumnsInSelect(): 0 +getMaxColumnsInTable(): 0 +getMaxConnections(): 0 +getMaxCursorNameLength(): 18 +getMaxIndexLength(): 0 +getMaxSchemaNameLength(): 30 +getMaxProcedureNameLength(): 128 +getMaxCatalogNameLength(): 0 +getMaxRowSize(): 0 +doesMaxRowSizeIncludeBlobs(): true +getMaxStatementLength(): 0 +getMaxStatements(): 0 +getMaxTableNameLength(): 128 +getMaxTablesInSelect(): 0 +getMaxUserNameLength(): 30 +supportsTransactions(): true +supportsTransactionIsolationLevel(Connection.TRANSACTION_NONE): false +supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ): true +supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE): true +supportsDataDefinitionAndDataManipulationTransactions(): true +supportsDataManipulationTransactionsOnly(): false +dataDefinitionCausesTransactionCommit(): false +dataDefinitionIgnoredInTransactions(): false +getConnection(): same connection +getProcedureColumns(): +PROCEDURE_CAT[VARCHAR],PROCEDURE_SCHEM[VARCHAR],PROCEDURE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],COLUMN_TYPE[SMALLINT],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],NUM_PREC_RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],COLUMN_DEF[VARCHAR],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],CHAR_OCTET_LENGTH[INTEGER],ORDINAL_POSITION[INTEGER],IS_NULLABLE[VARCHAR],METHOD_ID[SMALLINT],PARAMETER_ID[SMALLINT] +null,APP,GETPCTEST1,OUTB,4,12,VARCHAR,3,3,null,null,1,null,null,12,null,3,1,YES,12,0 +null,APP,GETPCTEST1,A,1,12,VARCHAR,3,3,null,null,1,null,null,12,null,3,2,YES,12,1 +null,APP,GETPCTEST1,B,1,2,NUMERIC,5,6,0,10,1,null,null,2,null,null,3,YES,12,2 +null,APP,GETPCTEST1,C,1,5,SMALLINT,5,2,0,10,1,null,null,5,null,null,4,YES,12,3 +null,APP,GETPCTEST1,E,1,5,SMALLINT,5,2,0,10,1,null,null,5,null,null,5,YES,12,4 +null,APP,GETPCTEST1,F,1,4,INTEGER,10,4,0,10,1,null,null,4,null,null,6,YES,12,5 +null,APP,GETPCTEST1,G,1,-5,BIGINT,19,8,0,10,1,null,null,-5,null,null,7,YES,12,6 +null,APP,GETPCTEST1,H,1,8,DOUBLE,52,8,null,2,1,null,null,8,null,null,8,YES,12,7 +null,APP,GETPCTEST1,I,1,8,DOUBLE,52,8,null,2,1,null,null,8,null,null,9,YES,12,8 +null,APP,GETPCTEST1,K,1,91,DATE,10,-1,null,2,1,null,null,9,1,null,10,YES,12,9 +null,APP,GETPCTEST1,L,1,92,TIME,8,-1,0,2,1,null,null,9,2,null,11,YES,12,10 +null,APP,GETPCTEST1,T,1,93,TIMESTAMP,26,-1,0,2,1,null,null,9,3,null,12,YES,12,11 +null,APP,GETPCTEST2,PA,1,4,INTEGER,10,4,0,10,1,null,null,4,null,null,1,YES,2,0 +null,APP,GETPCTEST2,PB,1,-5,BIGINT,19,8,0,10,1,null,null,-5,null,null,2,YES,2,1 +null,APP,GETPCTEST3A,STRING1,1,12,VARCHAR,5,5,null,null,1,null,null,12,null,5,1,YES,2,0 +null,APP,GETPCTEST3A,STRING2,4,12,VARCHAR,5,5,null,null,1,null,null,12,null,5,2,YES,2,1 +null,APP,GETPCTEST3B,STRING3,1,12,VARCHAR,5,5,null,null,1,null,null,12,null,5,1,YES,2,0 +null,APP,GETPCTEST3B,STRING4,2,12,VARCHAR,5,5,null,null,1,null,null,12,null,5,2,YES,2,1 +null,APP,GETPCTEST4BX,RETPARAM,4,4,INTEGER,10,4,0,10,1,null,null,4,null,null,1,YES,1,0 +getTables() with TABLE_TYPE in ('SYSTEM TABLE') : +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],TABLE_TYPE[VARCHAR],REMARKS[VARCHAR] +,SYS,SYSALIASES,SYSTEM TABLE, +,SYS,SYSCHECKS,SYSTEM TABLE, +,SYS,SYSCOLUMNS,SYSTEM TABLE, +,SYS,SYSCONGLOMERATES,SYSTEM TABLE, +,SYS,SYSCONSTRAINTS,SYSTEM TABLE, +,SYS,SYSDEPENDS,SYSTEM TABLE, +,SYS,SYSFILES,SYSTEM TABLE, +,SYS,SYSFOREIGNKEYS,SYSTEM TABLE, +,SYS,SYSKEYS,SYSTEM TABLE, +,SYS,SYSSCHEMAS,SYSTEM TABLE, +,SYS,SYSSTATEMENTS,SYSTEM TABLE, +,SYS,SYSSTATISTICS,SYSTEM TABLE, +,SYS,SYSTABLES,SYSTEM TABLE, +,SYS,SYSTRIGGERS,SYSTEM TABLE, +,SYS,SYSVIEWS,SYSTEM TABLE, +,SYSIBM,SYSDUMMY1,SYSTEM TABLE, +getTables() with no types: +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],TABLE_TYPE[VARCHAR],REMARKS[VARCHAR] +,SYS,SYSALIASES,SYSTEM TABLE, +,SYS,SYSCHECKS,SYSTEM TABLE, +,SYS,SYSCOLUMNS,SYSTEM TABLE, +,SYS,SYSCONGLOMERATES,SYSTEM TABLE, +,SYS,SYSCONSTRAINTS,SYSTEM TABLE, +,SYS,SYSDEPENDS,SYSTEM TABLE, +,SYS,SYSFILES,SYSTEM TABLE, +,SYS,SYSFOREIGNKEYS,SYSTEM TABLE, +,SYS,SYSKEYS,SYSTEM TABLE, +,SYS,SYSSCHEMAS,SYSTEM TABLE, +,SYS,SYSSTATEMENTS,SYSTEM TABLE, +,SYS,SYSSTATISTICS,SYSTEM TABLE, +,SYS,SYSTABLES,SYSTEM TABLE, +,SYS,SYSTRIGGERS,SYSTEM TABLE, +,SYS,SYSVIEWS,SYSTEM TABLE, +,SYSIBM,SYSDUMMY1,SYSTEM TABLE, +,APP,ALLTYPES,TABLE, +,APP,INFLIGHT,TABLE, +,APP,LOUIE,TABLE, +,APP,REFTAB,TABLE, +,APP,REFTAB2,TABLE, +,APP,T,TABLE, +,APP,SCREWIE,VIEW, +getTables() with TABLE_TYPE in ('VIEW','TABLE') : +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],TABLE_TYPE[VARCHAR],REMARKS[VARCHAR] +,APP,ALLTYPES,TABLE, +,APP,INFLIGHT,TABLE, +,APP,LOUIE,TABLE, +,APP,REFTAB,TABLE, +,APP,REFTAB2,TABLE, +,APP,T,TABLE, +,APP,SCREWIE,VIEW, +getTableTypes(): +TABLE_TYPE[VARCHAR] +SYSTEM TABLE +TABLE +VIEW +getColumns(): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],NUM_PREC_RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],COLUMN_DEF[VARCHAR],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],CHAR_OCTET_LENGTH[INTEGER],ORDINAL_POSITION[INTEGER],IS_NULLABLE[VARCHAR] +getColumns('SYSTABLES'): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],NUM_PREC_RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],COLUMN_DEF[VARCHAR],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],CHAR_OCTET_LENGTH[INTEGER],ORDINAL_POSITION[INTEGER],IS_NULLABLE[VARCHAR] +,SYS,SYSTABLES,TABLEID,1,CHAR,36,72,null,null,0,,null,1,null,72,1,NO +,SYS,SYSTABLES,TABLENAME,12,VARCHAR,128,256,null,null,0,,null,12,null,256,2,NO +,SYS,SYSTABLES,TABLETYPE,1,CHAR,1,2,null,null,0,,null,1,null,2,3,NO +,SYS,SYSTABLES,SCHEMAID,1,CHAR,36,72,null,null,0,,null,1,null,72,4,NO +,SYS,SYSTABLES,LOCKGRANULARITY,1,CHAR,1,2,null,null,0,,null,1,null,2,5,NO +getColumns('ALLTYPES'): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],NUM_PREC_RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],COLUMN_DEF[VARCHAR],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],CHAR_OCTET_LENGTH[INTEGER],ORDINAL_POSITION[INTEGER],IS_NULLABLE[VARCHAR] +,APP,ALLTYPES,SMALLINTCOL,5,SMALLINT,5,2,0,10,1,,null,5,null,null,1,YES +,APP,ALLTYPES,INTCOL,4,INTEGER,10,4,0,10,1,,20,4,null,null,2,YES +,APP,ALLTYPES,BIGINTCOL,-5,BIGINT,19,8,0,10,1,,null,-5,null,null,3,YES +,APP,ALLTYPES,REALCOL,7,REAL,23,4,null,2,1,,null,7,null,null,4,YES +,APP,ALLTYPES,DOUBLEPRECCOL,8,DOUBLE,52,8,null,2,1,,10,8,null,null,5,YES +,APP,ALLTYPES,DECIMALCOL10P4S,3,DECIMAL,10,13,4,10,1,,null,3,null,null,6,YES +,APP,ALLTYPES,NUMERICCOL20P2S,2,NUMERIC,20,23,2,10,1,,null,2,null,null,7,YES +,APP,ALLTYPES,CHAR8COL___,1,CHAR,8,16,null,null,1,,null,1,null,16,8,YES +,APP,ALLTYPES,VARCHAR9COL,12,VARCHAR,9,18,null,null,1,,null,12,null,18,9,YES +,APP,ALLTYPES,LONGVARCHARCOL,-1,LONG VARCHAR,32700,32700,null,null,1,,null,-1,null,null,10,YES +,APP,ALLTYPES,BLOBCOL,2004,BLOB,3072,3072,null,null,1,,null,2004,null,null,11,YES +getColumns('LOUIE'): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],NUM_PREC_RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],COLUMN_DEF[VARCHAR],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],CHAR_OCTET_LENGTH[INTEGER],ORDINAL_POSITION[INTEGER],IS_NULLABLE[VARCHAR] +,APP,LOUIE,I,4,INTEGER,10,4,0,10,0,,10,4,null,null,1,NO +,APP,LOUIE,S,5,SMALLINT,5,2,0,10,0,,null,5,null,null,2,NO +,APP,LOUIE,C30,1,CHAR,30,60,null,null,0,,null,1,null,60,3,NO +,APP,LOUIE,VC10,12,VARCHAR,10,20,null,null,0,,'asdf',12,null,20,4,NO +,APP,LOUIE,AI,-5,BIGINT,19,8,0,10,0,,AUTOINCREMENT: start -10 increment 2001,-5,null,null,5,NO +getColumns('INFLIGHT'): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],NUM_PREC_RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],COLUMN_DEF[VARCHAR],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],CHAR_OCTET_LENGTH[INTEGER],ORDINAL_POSITION[INTEGER],IS_NULLABLE[VARCHAR] +,APP,INFLIGHT,FLT_NUM,1,CHAR,20,40,null,null,0,,null,1,null,40,1,NO +,APP,INFLIGHT,FLT_ORIGIN,1,CHAR,6,12,null,null,1,,null,1,null,12,2,YES +,APP,INFLIGHT,FLT_DEST,1,CHAR,6,12,null,null,1,,null,1,null,12,3,YES +,APP,INFLIGHT,FLT_AIRCRAFT,1,CHAR,20,40,null,null,1,,null,1,null,40,4,YES +,APP,INFLIGHT,FLT_FLYING_TIME,12,VARCHAR,22,44,null,null,1,,null,12,null,44,5,YES +,APP,INFLIGHT,FLT_DEPT_TIME,1,CHAR,8,16,null,null,1,,null,1,null,16,6,YES +,APP,INFLIGHT,FLT_ARR_TIME,1,CHAR,8,16,null,null,1,,null,1,null,16,7,YES +,APP,INFLIGHT,FLT_NOTES,12,VARCHAR,510,1020,null,null,1,,null,12,null,1020,8,YES +,APP,INFLIGHT,FLT_DAYS_OF_WK,1,CHAR,14,28,null,null,1,,null,1,null,28,9,YES +,APP,INFLIGHT,FLT_CRAFT_PIC,12,VARCHAR,32672,65344,null,null,1,,null,12,null,65344,10,YES +getColumnPrivileges(): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],GRANTOR[VARCHAR],GRANTEE[VARCHAR],PRIVILEGE[VARCHAR],IS_GRANTABLE[VARCHAR] +getTablePrivileges(): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],GRANTOR[VARCHAR],GRANTEE[VARCHAR],PRIVILEGE[VARCHAR],IS_GRANTABLE[VARCHAR] +getBestRowIdentifier("",null,"LOUIE"): +SCOPE[SMALLINT],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],PSEUDO_COLUMN[SMALLINT] +2,I,4,INTEGER,4,4,10,1 +2,VC10,12,VARCHAR,10,20,null,1 +getBestRowIdentifier("","SYS","SYSTABLES"): +SCOPE[SMALLINT],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],PSEUDO_COLUMN[SMALLINT] +2,TABLEID,1,CHAR,36,72,null,1 +getVersionColumns(): +SCOPE[SMALLINT],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],PSEUDO_COLUMN[SMALLINT] +getPrimaryKeys(): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],PK_NAME[VARCHAR] +,APP,LOUIE,I,2,PRIMKEY +,APP,LOUIE,VC10,1,PRIMKEY +getPrimaryKeys(null, null, tablename): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],PK_NAME[VARCHAR] +,APP,LOUIE,I,2,PRIMKEY +,APP,LOUIE,VC10,1,PRIMKEY +getImportedKeys(): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,LOUIE,VC10,,APP,REFTAB2,T2_VC10,1,3,3,T2_FKEY1,PRIMKEY,7 +,APP,LOUIE,VC10,,APP,REFTAB,VC10,1,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB2,T2_I,2,3,3,T2_FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB,I,2,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S,2,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S2,2,3,3,FKEY2,UNIQUEKEY,7 +,APP,REFTAB,DPRIM,,APP,REFTAB,DFOR,1,3,3,FKEYSELF,PKEY_REFTAB,7 +getExportedKeys(): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,LOUIE,VC10,,APP,REFTAB,VC10,1,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB,I,2,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S2,2,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S,2,3,3,FKEY3,UNIQUEKEY,7 +,APP,REFTAB,DPRIM,,APP,REFTAB,DFOR,1,3,3,FKEYSELF,PKEY_REFTAB,7 +,APP,LOUIE,VC10,,APP,REFTAB2,T2_VC10,1,3,3,T2_FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB2,T2_I,2,3,3,T2_FKEY1,PRIMKEY,7 +--------------------------------------- +getCrossReference('',null,'louie','',null,'reftab' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,LOUIE,VC10,,APP,REFTAB,VC10,1,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB,I,2,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S2,2,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S,2,3,3,FKEY3,UNIQUEKEY,7 +getCrossReference('','APP','reftab','',null,'reftab' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFTAB,DPRIM,,APP,REFTAB,DFOR,1,3,3,FKEYSELF,PKEY_REFTAB,7 +getCrossReference('',null,null,'','APP','reftab' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,LOUIE,VC10,,APP,REFTAB,VC10,1,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB,I,2,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S2,2,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S,2,3,3,FKEY3,UNIQUEKEY,7 +,APP,REFTAB,DPRIM,,APP,REFTAB,DFOR,1,3,3,FKEYSELF,PKEY_REFTAB,7 +getImportedKeys('',null,null,'','APP','reftab' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,LOUIE,VC10,,APP,REFTAB,VC10,1,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB,I,2,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S,2,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S2,2,3,3,FKEY2,UNIQUEKEY,7 +,APP,REFTAB,DPRIM,,APP,REFTAB,DFOR,1,3,3,FKEYSELF,PKEY_REFTAB,7 +getCrossReference('',null,'louie','','APP',null): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,LOUIE,VC10,,APP,REFTAB,VC10,1,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB,I,2,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S2,2,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S,2,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,VC10,,APP,REFTAB2,T2_VC10,1,3,3,T2_FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB2,T2_I,2,3,3,T2_FKEY1,PRIMKEY,7 +getExportedKeys('',null,'louie,'','APP',null ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,LOUIE,VC10,,APP,REFTAB,VC10,1,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB,I,2,3,3,FKEY1,PRIMKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S2,2,3,3,FKEY2,UNIQUEKEY,7 +,APP,LOUIE,C30,,APP,REFTAB,C30,1,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,S,,APP,REFTAB,S,2,3,3,FKEY3,UNIQUEKEY,7 +,APP,LOUIE,VC10,,APP,REFTAB2,T2_VC10,1,3,3,T2_FKEY1,PRIMKEY,7 +,APP,LOUIE,I,,APP,REFTAB2,T2_I,2,3,3,T2_FKEY1,PRIMKEY,7 +getCrossReference('','badschema','LOUIE','','APP','REFTAB' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +getTypeInfo(): +TYPE_NAME[VARCHAR],DATA_TYPE[SMALLINT],COLUMN_SIZE[INTEGER],LITERAL_PREFIX[VARCHAR],LITERAL_SUFFIX[VARCHAR],CREATE_PARAMS[VARCHAR],NULLABLE[SMALLINT],CASE_SENSITIVE[SMALLINT],SEARCHABLE[SMALLINT],UNSIGNED_ATTRIBUTE[SMALLINT],FIXED_PREC_SCALE[SMALLINT],AUTO_UNIQUE_VAL[SMALLINT],LOCAL_TYPE_NAME[VARCHAR],MINIMUM_SCALE[SMALLINT],MAXIMUM_SCALE[SMALLINT],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],NUM_PREC_RADIX[INTEGER],INTERVAL_PRECISION[SMALLINT] +BIGINT,-5,19,null,null,null,1,0,2,0,0,1,BIGINT,0,0,-5,null,10,null +LONG VARCHAR FOR BIT DATA,-4,32700,X',',null,1,0,2,null,0,null,LONG VARCHAR FOR BIT DATA,null,null,-4,null,null,null +VARCHAR () FOR BIT DATA,-3,32762,X',',length,1,0,2,null,0,null,VARCHAR () FOR BIT DATA,null,null,-3,null,null,null +CHAR () FOR BIT DATA,-2,254,X',',length,1,0,2,null,0,null,CHAR () FOR BIT DATA,null,null,-2,null,null,null +LONG VARCHAR,-1,32700,',',null,1,1,3,null,0,null,LONG VARCHAR,null,null,-1,null,null,null +LONG NVARCHAR,-1,32700,',',null,1,1,3,null,0,null,LONG NVARCHAR,null,null,-1,null,null,null +CHAR,1,254,',',length,1,1,3,null,0,null,CHAR,null,null,1,null,null,null +NATIONAL CHAR,1,2147483647,',',length,1,1,3,null,0,null,NATIONAL CHAR,null,null,1,null,null,null +NUMERIC,2,31,null,null,precision,scale,1,0,2,0,1,0,NUMERIC,0,32767,2,null,10,null +DECIMAL,3,31,null,null,precision,scale,1,0,2,0,1,0,DECIMAL,0,32767,3,null,10,null +INTEGER,4,10,null,null,null,1,0,2,0,0,1,INTEGER,0,0,4,null,10,null +SMALLINT,5,5,null,null,null,1,0,2,0,0,1,SMALLINT,0,0,5,null,10,null +FLOAT,6,52,null,null,precision,1,0,2,0,0,0,FLOAT,null,null,6,null,2,null +REAL,7,23,null,null,null,1,0,2,0,0,0,REAL,null,null,7,null,2,null +DOUBLE,8,52,null,null,null,1,0,2,0,0,0,DOUBLE,null,null,8,null,2,null +VARCHAR,12,32672,',',length,1,1,3,null,0,null,VARCHAR,null,null,12,null,null,null +NATIONAL CHAR VARYING,12,2147483647,',',length,1,1,3,null,0,null,NATIONAL CHAR VARYING,null,null,12,null,null,null +DATE,91,10,DATE',',null,1,0,2,1,0,0,DATE,0,0,9,1,2,null +TIME,92,0,TIME',',null,1,0,2,1,0,0,TIME,0,0,9,2,2,null +TIMESTAMP,93,0,TIMESTAMP',',null,1,0,2,1,0,0,TIMESTAMP,0,0,9,3,2,null +BLOB,2004,2147483647,null,null,length,1,0,1,null,0,null,BLOB,null,null,2004,null,null,null +CLOB,2005,2147483647,',',length,1,1,1,null,0,null,CLOB,null,null,2005,null,null,null +getIndexInfo(): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],NON_UNIQUE[SMALLINT],INDEX_QUALIFIER[VARCHAR],INDEX_NAME[VARCHAR],TYPE[SMALLINT],ORDINAL_POSITION[SMALLINT],COLUMN_NAME[VARCHAR],ASC_OR_DESC[CHAR],CARDINALITY[INTEGER],PAGES[INTEGER],FILTER_CONDITION[VARCHAR] +,SYS,SYSCOLUMNS,0,,SYSCOLUMNS_INDEX1,3,1,REFERENCEID,A,null,null,null +,SYS,SYSCOLUMNS,0,,SYSCOLUMNS_INDEX1,3,2,COLUMNNAME,A,null,null,null +getIndexInfo(): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],NON_UNIQUE[SMALLINT],INDEX_QUALIFIER[VARCHAR],INDEX_NAME[VARCHAR],TYPE[SMALLINT],ORDINAL_POSITION[SMALLINT],COLUMN_NAME[VARCHAR],ASC_OR_DESC[CHAR],CARDINALITY[INTEGER],PAGES[INTEGER],FILTER_CONDITION[VARCHAR] +,SYS,SYSCOLUMNS,0,,SYSCOLUMNS_INDEX1,3,1,REFERENCEID,A,null,null,null +,SYS,SYSCOLUMNS,0,,SYSCOLUMNS_INDEX1,3,2,COLUMNNAME,A,null,null,null +,SYS,SYSCOLUMNS,1,,SYSCOLUMNS_INDEX2,3,1,COLUMNDEFAULTID,A,null,null,null +getIndexInfo(): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],NON_UNIQUE[SMALLINT],INDEX_QUALIFIER[VARCHAR],INDEX_NAME[VARCHAR],TYPE[SMALLINT],ORDINAL_POSITION[SMALLINT],COLUMN_NAME[VARCHAR],ASC_OR_DESC[CHAR],CARDINALITY[INTEGER],PAGES[INTEGER],FILTER_CONDITION[VARCHAR] +,SYS,SYSTABLES,0,,SYSTABLES_INDEX1,3,1,TABLENAME,A,null,null,null +,SYS,SYSTABLES,0,,SYSTABLES_INDEX1,3,2,SCHEMAID,A,null,null,null +,SYS,SYSTABLES,0,,SYSTABLES_INDEX2,3,1,TABLEID,A,null,null,null +getColumns('SYSTABLES'): +TABLE_CAT[VARCHAR],TABLE_SCHEM[VARCHAR],TABLE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],NUM_PREC_RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],COLUMN_DEF[VARCHAR],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],CHAR_OCTET_LENGTH[INTEGER],ORDINAL_POSITION[INTEGER],IS_NULLABLE[VARCHAR] +,SYS,SYSTABLES,TABLEID,1,CHAR,36,72,null,null,0,,null,1,null,72,1,NO +,SYS,SYSTABLES,TABLENAME,12,VARCHAR,128,256,null,null,0,,null,12,null,256,2,NO +,SYS,SYSTABLES,TABLETYPE,1,CHAR,1,2,null,null,0,,null,1,null,2,3,NO +,SYS,SYSTABLES,SCHEMAID,1,CHAR,36,72,null,null,0,,null,1,null,72,4,NO +,SYS,SYSTABLES,LOCKGRANULARITY,1,CHAR,1,2,null,null,0,,null,1,null,2,5,NO +OK -- user result set closed by intervening OBDC getColumns request; this was expected because of the way the test works. +--------------------------------------- +Referential action values +RESTRICT = 1 +NO ACTION = 3 +CASCADE = 0 +SETNULL = 2 +SETDEFAULT = 4 +getCrossReference('','APP','REFACTION1','','APP','REFACTIONNONE' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFACTION1,A,,APP,REFACTNONE,A,1,3,3,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +getCrossReference('','APP','REFACTION1','','APP','REFACTRESTRICT' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFACTION1,A,,APP,REFACTRESTRICT,A,1,3,1,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +getCrossReference('','APP','REFACTION1','','APP','REFACTNOACTION' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFACTION1,A,,APP,REFACTNOACTION,A,1,3,3,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +getCrossReference('','APP','REFACTION1','','APP','REFACTCASCADE' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFACTION1,A,,APP,REFACTCASCADE,A,1,3,0,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +getCrossReference('','APP','REFACTION1','','APP','REFACTSETNULL' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFACTION1,A,,APP,REFACTSETNULL,A,1,3,2,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +getCrossReference('','APP','REFACTION1','','APP','REFACTUPDRESTRICT' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFACTION1,A,,APP,REFACTUPDRESTRICT,A,1,1,3,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +getCrossReference('','APP','REFACTION1','','APP','REFACTUPDNOACTION' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFACTION1,A,,APP,REFACTUPDNOACTION,A,1,3,3,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +getImportedKeys - none update Passed +getImportedKeys - none delete Passed +getImportedKeys - delete Restrict Passed +getImportedKeys - delete NO ACTION Passed +getImportedKeys - delete CASCADE Passed +getImportedKeys - delete SET NULL Passed +getImportedKeys - update Restrict Passed +getImportedKeys - update NO ACTION Passed +getExportedKeys('',null,null,'','APP','REFACTION1' ): +PKTABLE_CAT[VARCHAR],PKTABLE_SCHEM[VARCHAR],PKTABLE_NAME[VARCHAR],PKCOLUMN_NAME[VARCHAR],FKTABLE_CAT[VARCHAR],FKTABLE_SCHEM[VARCHAR],FKTABLE_NAME[VARCHAR],FKCOLUMN_NAME[VARCHAR],KEY_SEQ[SMALLINT],UPDATE_RULE[SMALLINT],DELETE_RULE[SMALLINT],FK_NAME[VARCHAR],PK_NAME[VARCHAR],DEFERRABILITY[SMALLINT] +,APP,REFACTION1,A,,APP,REFACTCASCADE,A,1,3,0,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +,APP,REFACTION1,A,,APP,REFACTNOACTION,A,1,3,3,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +,APP,REFACTION1,A,,APP,REFACTNONE,A,1,3,3,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +,APP,REFACTION1,A,,APP,REFACTRESTRICT,A,1,3,1,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +,APP,REFACTION1,A,,APP,REFACTSETNULL,A,1,3,2,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +,APP,REFACTION1,A,,APP,REFACTUPDNOACTION,A,1,3,3,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +,APP,REFACTION1,A,,APP,REFACTUPDRESTRICT,A,1,1,3,xxxxGENERATED-IDxxxx,xxxxGENERATED-IDxxxx,7 +--------------------------------------- +getColumns for 'T1' + Next line is real null. +defval for col C1 type INTEGER DEFAULT 'null' wasnull true + Next line is real null. +defval for col C2 type INTEGER DEFAULT 'null' wasnull true +defval for col C3 type INTEGER DEFAULT 'NULL' wasnull false + Next line is real null. +defval for col C4 type CHAR DEFAULT 'null' wasnull true +defval for col C5 type CHAR DEFAULT 'NULL' wasnull false +defval for col C6 type CHAR DEFAULT ''NULL'' wasnull false +defval for col C7 type INTEGER DEFAULT '88' wasnull false +Test metadata finished Property changes on: java/testing/org/apache/derbyTesting/functionTests/master/odbc_metadata.out ___________________________________________________________________ Name: svn:eol-style + native Index: java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall (revision 153312) +++ java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall (working copy) @@ -1,5 +1,6 @@ jdbcapi/metadata.java jdbcapi/metadataMultiConn.java +jdbcapi/odbc_metadata.java lang/AIjdbc.java lang/CharUTF8.java lang/DB2IsolationLevels.sql Index: java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall (revision 153312) +++ java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall (working copy) @@ -19,6 +19,7 @@ jdbcapi/dbMetaDataJdbc30.java jdbcapi/metadata.java jdbcapi/metadataMultiConn.java +jdbcapi/odbc_metadata.java jdbcapi/parameterMetaDataJdbc30.java jdbcapi/savepointJdbc30.java lang/big.sql Index: java/testing/org/apache/derbyTesting/functionTests/suites/j9derbynetmats.runall =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/suites/j9derbynetmats.runall (revision 153312) +++ java/testing/org/apache/derbyTesting/functionTests/suites/j9derbynetmats.runall (working copy) @@ -17,6 +17,7 @@ jdbcapi/dbMetaDataJdbc30.java jdbcapi/metadata.java jdbcapi/metadataMultiConn.java +jdbcapi/odbc_metadata.java jdbcapi/parameterMetaDataJdbc30.java jdbcapi/savepointJdbc30.java lang/big.sql --------------030605000307010209040601--