File: DatabaseMetaDataTest.java

package info (click to toggle)
derby 10.14.2.0-2
links: PTS, VCS
area: main
in suites: bookworm, bullseye
size: 78,896 kB
sloc: java: 691,930; sql: 42,686; xml: 20,511; sh: 3,373; sed: 96; makefile: 60
file content (5624 lines) | stat: -rw-r--r-- 214,646 bytes
parent folder | download | duplicates (3)
/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest

   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You 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 go1481
   verning permissions and
   limitations under the License.

 */
package org.apache.derbyTesting.functionTests.tests.jdbcapi;

import java.io.IOException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashSet;
import java.util.List;
import java.util.Locale;
import java.util.Properties;
import java.util.Random;
import java.util.StringTokenizer;
import junit.framework.Test;
import org.apache.derbyTesting.functionTests.tests.upgradeTests.Version;
import org.apache.derbyTesting.functionTests.util.Barrier;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;

/**
 * Test the DatabaseMetaData api.
 * <P>
 * For a number of methods that return a ResultSet to determine the
 * attributes of SQL objects (e.g. getTables) two methods
 * are provided. A non-modify and a modify one.
 * 
 * <BR>
 * The non-modify method tests that the getXXX method call works.
 * This can be used by other tests where issues have been seen
 * with database meta data, such as upgrade and read-only databases.
 * The non-modify means that the test method does not change the database
 * in order to test the return of the getXXX method is correct.
 * <BR>
 *  <P>
 *  This test is also called from the upgrade tests to test that
 *  metadata continues to work at various points in the upgrade.
 */
public class DatabaseMetaDataTest extends BaseJDBCTestCase {
  
    /*
    ** Escaped function testing
    */
    private static final String[][] NUMERIC_FUNCTIONS =
    {
        // Section C.1 JDBC 3.0 spec.
        { "ABS", "-25.67" },
        { "ACOS", "0.0707" },
        { "ASIN", "0.997" },
        { "ATAN", "14.10" },
        { "ATAN2", "0.56", "1.2" },
        { "CEILING", "3.45" },
        { "COS", "1.2" },
        { "COT", "3.4" },
        { "DEGREES", "2.1" },
        { "EXP", "2.3" },
        { "FLOOR", "3.22" },
        { "LOG", "34.1" },
        { "LOG10", "18.7" },
        { "MOD", "124", "7" },
        { "PI" },
        { "POWER", "2", "3" },
        { "RADIANS", "54" },
        { "RAND", "17" }, 
        { "ROUND", "345.345", "1" }, 
        { "SIGN", "-34" },
        { "SIN", "0.32" },
        { "SQRT", "6.22" },
        { "TAN", "0.57", },
        { "TRUNCATE", "345.395", "1" }
    };
    
    private static final String[][] TIMEDATE_FUNCTIONS =
    {   
        // Section C.3 JDBC 3.0 spec.
        { "CURDATE" },
        { "CURTIME" },
        { "DAYNAME", "{d '1995-12-19'h}" },
        { "DAYOFMONTH", "{d '1995-12-19'}" },
        { "DAYOFWEEK", "{d '1995-12-19'}" },
        { "DAYOFYEAR", "{d '1995-12-19'}" },
        { "HOUR", "{t '16:13:03'}" },
        { "MINUTE", "{t '16:13:03'}" },
        { "MONTH", "{d '1995-12-19'}" },
        { "MONTHNAME", "{d '1995-12-19'}" },
        { "NOW" },
        { "QUARTER", "{d '1995-12-19'}" },
        { "SECOND", "{t '16:13:03'}" },
        { "TIMESTAMPADD", "SQL_TSI_DAY", "7", "{ts '1995-12-19 12:15:54'}" },
        { "TIMESTAMPDIFF", "SQL_TSI_DAY", "{ts '1995-12-19 12:15:54'}", "{ts '1997-11-02 00:15:23'}" },
        { "WEEK", "{d '1995-12-19'}" },
        { "YEAR", "{d '1995-12-19'}" },
        
    };

    private static final String[][] SYSTEM_FUNCTIONS =
    {   
        // Section C.4 JDBC 3.0 spec.
        { "DATABASE" },
        { "IFNULL", "'this'", "'that'" },
        { "USER"},
        };  
    
    private static final String[][] STRING_FUNCTIONS =
    {   
        // Section C.2 JDBC 3.0 spec.
        { "ASCII" , "'Yellow'" },
        { "CHAR", "65" },
        { "CONCAT", "'hello'", "'there'" },
        { "DIFFERENCE", "'Pires'", "'Piers'" },
        { "INSERT", "'Bill Clinton'", "4", "'William'" },
        { "LCASE", "'Fernando Alonso'" },
        { "LEFT", "'Bonjour'", "3" },
        { "LENGTH", "'four    '" } ,
        { "LOCATE", "'jour'", "'Bonjour'" },
        { "LTRIM", "'   left trim   '"},
        { "REPEAT", "'echo'", "3" },
        { "REPLACE", "'to be or not to be'", "'be'", "'England'" },
        { "RTRIM", "'  right trim   '"},
        { "SOUNDEX", "'Derby'" },
        { "SPACE", "12"},
        { "SUBSTRING", "'Ruby the Rubicon Jeep'", "10", "7", },
        { "UCASE", "'Fernando Alonso'" }
        };
    
    /**
     * Did the test modifiy the database.
     */
    private boolean modifiedDatabase;
    /**
     * The schema used by the test.
     * <p>
     * Added to avoid other tests interfering with our metadata queries.
     * Configured by running the test with a specific user, i.e. use
     * {@linkplain TestConfiguration#changeUserDecorator}.
     */
    private String schema;

    public DatabaseMetaDataTest(String name) {
        super(name);
    }
    
    /**
     * Set the schema name to be used by the test.
     * @throws java.lang.Exception
     */
    @Override
    protected void setUp()
            throws Exception {
        // Currently there are no tests that depend on data created outside
        // of the test ficture itself. This means we can relax the retrictions 
        // on the user name, and thus schema; we don't need to keep it the same
        // over all test fixtures.
        schema = TestConfiguration.getCurrent().getUserName();
        // Only a prerequisite for one test, but enforce it here (fail-fast).
        assertTrue("schema name must be at least three characters long",
                schema.length() > 2);
    }

    @Override
    protected void tearDown() throws Exception
    {
        if (modifiedDatabase)
        {
            Connection conn = getConnection();
            conn.setAutoCommit(false);

            DatabaseMetaData dmd = getDMD();
            for (String IDS1 : IDS) {
                JDBC.dropSchema(dmd, getStoredIdentifier(IDS1));
            }
  
            commit();
        }
        super.tearDown();
    }
    
    /**
     * Default suite for running this test.
     * @return the suite
     */
    public static Test suite() {
        BaseTestSuite suite = new BaseTestSuite("DatabaseMetaDataTest");
        suite.addTest(
            TestConfiguration.defaultSuite(DatabaseMetaDataTest.class));

        // Add some tests to be run with connection pooling enabled.
        suite.addTest(connectionPoolingSuite("embedded"));
        suite.addTest(TestConfiguration.clientServerDecorator(
                    connectionPoolingSuite("client")));

        // Test for DERBY-2584 needs a fresh database to ensure that the
        // meta-data queries haven't already been compiled. No need to run the
        // test in client/server mode since it only tests the compilation of
        // meta-data queries.
        suite.addTest(
            TestConfiguration.singleUseDatabaseDecorator(
                new DatabaseMetaDataTest("initialCompilationTest")));

        // The test for DERBY-4160 needs a fresh database to ensure that the
        // meta-data queries haven't already been compiled.
        suite.addTest(TestConfiguration.singleUseDatabaseDecorator(
                new DatabaseMetaDataTest("concurrentCompilationTest")));

        // Test for DERBY-3693 needs a fresh database to ensure that the size
        // of SYSTABLES is so small that creating a relatively small number of
        // tables will cause the query plan for getTables() to be invalidated.
        // Also, set a high lock timeout explicitly so that we can check that
        // an internal timeout followed by a retry didn't happen, and set
        // derby.language.stalePlanCheckInterval to a low value so that the
        // invalidation happens earlier.
        Properties props = new Properties();
        props.setProperty("derby.locks.waitTimeout", "90");
        props.setProperty("derby.language.stalePlanCheckInterval", "5");
        suite.addTest(
            TestConfiguration.singleUseDatabaseDecorator(
                new DatabasePropertyTestSetup(
                    new DatabaseMetaDataTest("recompileTimeoutTest"),
                    props, true)));

        return suite;
    }
    
    /**
     * Returns a suite of tests to be run with connection pooling enabled.
     *
     * @param jdbcClient name of the client being used (for verbosity only)
     * @return A suite of tests.
     */
    private static Test connectionPoolingSuite(String jdbcClient) {
        // Return an empty suite if running in JavaME environment.
        if (JDBC.vmSupportsJSR169()) {
            return new BaseTestSuite("Base connection pooling suite:DISABLED");
        }

        BaseTestSuite baseCpSuite =
            new BaseTestSuite("Base connection pooling suite");
        // Add the tests here.
        baseCpSuite.addTest(new DatabaseMetaDataTest("testConnectionSpecific"));

        // Setup the two configurations; CPDS and XADS.
        BaseTestSuite fullCpSuite = new BaseTestSuite(
                "DatabaseMetaData with connection pooling:" + jdbcClient);
        BaseTestSuite cpSuite = new BaseTestSuite("ConnectionPoolDataSource");
        BaseTestSuite xaSuite = new BaseTestSuite("XADataSource");
        cpSuite.addTest(TestConfiguration.connectionCPDecorator(baseCpSuite));
        xaSuite.addTest(TestConfiguration.connectionXADecorator(baseCpSuite));
        fullCpSuite.addTest(cpSuite);
        fullCpSuite.addTest(xaSuite);

        return fullCpSuite;
    }

    /**
     * Return the identifiers used to create schemas,
     * tables etc. in the order the database stores them.
     * @return identifiers
     */
    private String[] getSortedIdentifiers()
    {
        String[] dbIDS = new String[IDS.length];
        // Remove any quotes from user schemas and upper case
        // those without quotes.
        for (int i = 0; i < IDS.length; i++)
        {          
            dbIDS[i] = getStoredIdentifier(IDS[i]);
        }
        Arrays.sort(dbIDS); 
        
        return dbIDS;
    }
    
    private DatabaseMetaData getDMD() throws SQLException
    {
        return getConnection().getMetaData();
    }

    /**
     * Tests that a meta-data query is compiled and stored correctly even when
     * there's a lock on the system tables (DERBY-2584). This test must run on
     * a fresh database (that is, <code>getIndexInfo</code> must not have been
     * prepared and stored in <code>SYS.SYSSTATEMENTS</code>).
     *
     * @throws java.sql.SQLException
     */
    public void initialCompilationTest() throws SQLException {
        Connection c = getConnection();
        c.setAutoCommit(false);
        c.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
        Statement s = createStatement();
        // First get shared row locks on the SYSSTATEMENTS table.
        JDBC.assertDrainResults(
            s.executeQuery("SELECT * FROM SYS.SYSSTATEMENTS"));
        s.close();
        // Execute getIndexInfo() for the first time. Because of the shared
        // locks on SYSSTATEMENTS, the query is compiled in the main
        // transaction.
        getDMD().getIndexInfo(null, null, "T", false, false).close();
        // Re-use the previously compiled query from disk. Fails with
        // ArrayIndexOutOfBoundsException before DERBY-2584.
        getDMD().getIndexInfo(null, null, "T", false, false).close();
    }

    /**
     * Test that a meta-data query is compiled and stored correctly even when
     * there's a lock conflict that causes the first attempt to store it to
     * stop midway (DERBY-4160). This test needs a fresh database so that the
     * meta-data calls are not already compiled.
     * @throws java.lang.Exception
     */
    public void concurrentCompilationTest() throws Exception {
        // Create a barrier that can be used to synchronize the two threads
        // so they perform the meta-data compilation at the same time.
        final Barrier barrier = new Barrier(2);

        // Create a thread thread that attempts to compile meta-data queries.
        final DatabaseMetaData dmd = getDMD();
        final Exception[] exception = new Exception[1];
        Thread th = new Thread() {
            @Override
            public void run() {
                try {
                    concurrentCompilationTestHelper(barrier, dmd);
                } catch (Exception e) {
                    exception[0] = e;
                }
            }
        };
        th.start();

        // At the same time, in the main thread, attempt to compile the same
        // meta-data queries.
        Connection c2 = openDefaultConnection();
        concurrentCompilationTestHelper(barrier, c2.getMetaData());
        c2.close();

        // Wait until both threads are done.
        th.join();

        // Check if the helper thread got any exceptions.
        if (exception[0] != null) {
            fail("Exception in other thread", exception[0]);
        }

        // Finally, verify that the two meta-data methods used in the test
        // are working.
        testGetBestRowIdentifier();
        testGetIndexInfo();
    }

    private void concurrentCompilationTestHelper(
            Barrier barrier, DatabaseMetaData dmd) throws Exception {
        // Wait until the other thread is ready to start, so that the
        // compilation happens at the same time in both threads.
        barrier.await();

        // Often, but not always, the getIndexInfo() call would fail
        // in one of the threads with the following error message:
        // ERROR X0Y68: Column 'PARAM1' already exists.
        ResultSet rs1 = dmd.getBestRowIdentifier(null, null, "", 0, true);
        ResultSet rs2 = dmd.getIndexInfo(null, null, "", true, true);
        JDBC.assertDrainResults(rs1);
        JDBC.assertDrainResults(rs2);
    }

    /**
     * Tests that we don't get an internal timeout when a meta-data statement
     * is recompiled because the size of the tables it queries has changed
     * (DERBY-3693). The test must be run on a fresh database, to ensure that
     * SYSTABLES initially has a relatively small number of records. The lock
     * timeout must be high (more than 60 seconds) to enable us to see the
     * difference between an internal lock timeout and slow execution.
     * derby.language.stalePlanCheckInterval should be set to 5 (the lowest
     * possible value) so that we don't have to wait long for the query plan
     * to be invalidated.
     * @throws java.sql.SQLException
     */
    public void recompileTimeoutTest() throws SQLException {
        DatabaseMetaData dmd = getDMD();

        // Make sure getTables() is initially compiled while SYSTABLES is small
        JDBC.assertDrainResults(dmd.getTables(null, "%", "%", null));

        // Grow SYSTABLES
        Statement s = createStatement();
        for (int i = 0; i < 20; i++) {
            s.executeUpdate("create table t" + i + "(x int)");
        }

        // Execute getTables() derby.language.stalePlanCheckInterval times so
        // that its plan is invalidated. Before DERBY-3693 was fixed, the
        // recompilation after the invalidation would get an internal timeout
        // and take very long time to complete.
        for (int i = 0; i < 5; i++) {
            long time = System.currentTimeMillis();
            JDBC.assertDrainResults(dmd.getTables(null, "%", "T0", null));
            time = System.currentTimeMillis() - time;
            if (time > 60000) {
                fail("getTables() took a very long time, possibly because " +
                     "of an internal timeout. i=" + i + ", time=" + time);
            }
        }
    }

    /**
     * Test the methods that indicate if a feature
     * is supported or not. Methods start with
     * 'support'. See secton 7.3 in JDBC 3.0 specification.
     * 
     * @throws SQLException 
     *
     */
    public void testDetermineFeatureSupport() throws SQLException
    {
        DatabaseMetaData dmd = getDMD();
        
        assertTrue(dmd.supportsAlterTableWithAddColumn());
        assertTrue(dmd.supportsAlterTableWithDropColumn());
        
	/* DERBY-2243 Derby does support ANSI 92 standards
	* and this behaviour is now consistant across drivers
	*/
	assertTrue(dmd.supportsANSI92EntryLevelSQL());
              
        assertFalse(dmd.supportsANSI92FullSQL());
        assertFalse(dmd.supportsANSI92IntermediateSQL());
        
        assertTrue(dmd.supportsBatchUpdates());
        
        assertFalse(dmd.supportsCatalogsInDataManipulation());
        assertFalse(dmd.supportsCatalogsInIndexDefinitions());
        assertFalse(dmd.supportsCatalogsInPrivilegeDefinitions());
        assertFalse(dmd.supportsCatalogsInProcedureCalls());
        assertFalse(dmd.supportsCatalogsInTableDefinitions());
        
        assertTrue(dmd.supportsColumnAliasing());
        
        assertFalse(dmd.supportsConvert());
        // Simple check since convert is not supported.
        // A comprehensive test should be added when convert
        // is supported, though most likely in a test class
        // specific to convert.
        assertFalse(dmd.supportsConvert(Types.INTEGER, Types.SMALLINT));
        
        assertFalse(dmd.supportsCoreSQLGrammar());
        assertTrue(dmd.supportsCorrelatedSubqueries());
        
        assertTrue(dmd.supportsDataDefinitionAndDataManipulationTransactions());
        assertFalse(dmd.supportsDataManipulationTransactionsOnly());
        assertTrue(dmd.supportsDifferentTableCorrelationNames());
        
	/* DERBY-2244 Derby does support Order By clause
	* thus the changing the assert condition to TRUE
	*/
	assertTrue(dmd.supportsExpressionsInOrderBy());
        
        assertFalse(dmd.supportsExtendedSQLGrammar());
        assertFalse(dmd.supportsFullOuterJoins());
        assertFalse(dmd.supportsGetGeneratedKeys());
        
        assertTrue(dmd.supportsGroupBy());
        assertTrue(dmd.supportsGroupByBeyondSelect());
        assertTrue(dmd.supportsGroupByUnrelated());
        
        assertFalse(dmd.supportsIntegrityEnhancementFacility());
        assertTrue(dmd.supportsLikeEscapeClause());
        assertTrue(dmd.supportsLimitedOuterJoins());
        assertTrue(dmd.supportsMinimumSQLGrammar());
        
        assertFalse(dmd.supportsMixedCaseIdentifiers());
        assertTrue(dmd.supportsMixedCaseQuotedIdentifiers());
        
        assertTrue(dmd.supportsMultipleOpenResults());
        assertTrue(dmd.supportsMultipleResultSets());
        assertTrue(dmd.supportsMultipleTransactions());
        
        assertFalse(dmd.supportsNamedParameters());
        assertTrue(dmd.supportsNonNullableColumns());
        
        // Open cursors are not supported across global
        // (XA) transactions so the driver returns false.
        assertFalse(dmd.supportsOpenCursorsAcrossCommit());
        
        assertFalse(dmd.supportsOpenCursorsAcrossRollback());
        assertTrue(dmd.supportsOpenStatementsAcrossCommit());
        
        assertFalse(dmd.supportsOpenStatementsAcrossRollback());
        
        assertFalse(dmd.supportsOrderByUnrelated());
        
        assertTrue(dmd.supportsOuterJoins());
        
        assertTrue(dmd.supportsPositionedDelete());
        assertTrue(dmd.supportsPositionedUpdate());
        
        assertTrue(dmd.supportsResultSetConcurrency(
            ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY));
        assertTrue(dmd.supportsResultSetConcurrency(
            ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE));

        assertTrue(dmd.supportsResultSetConcurrency(
            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY));
        assertTrue(dmd.supportsResultSetConcurrency(
            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE));

        assertFalse(dmd.supportsResultSetConcurrency(
             ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY));
        assertFalse(dmd.supportsResultSetConcurrency(
             ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE));

        assertTrue(dmd.supportsResultSetHoldability(
                ResultSet.CLOSE_CURSORS_AT_COMMIT));
        assertTrue(dmd.supportsResultSetHoldability(
                ResultSet.HOLD_CURSORS_OVER_COMMIT));
        
        assertTrue(dmd.supportsResultSetType(
                ResultSet.TYPE_FORWARD_ONLY));
        assertTrue(dmd.supportsResultSetType(
                ResultSet.TYPE_SCROLL_INSENSITIVE));
        assertFalse(dmd.supportsResultSetType(
                ResultSet.TYPE_SCROLL_SENSITIVE));
        
        assertTrue(dmd.supportsSavepoints());
        assertTrue(dmd.supportsSchemasInDataManipulation());
        assertTrue(dmd.supportsSchemasInIndexDefinitions());
        assertTrue(dmd.supportsSchemasInPrivilegeDefinitions());
        assertTrue(dmd.supportsSchemasInProcedureCalls());
        assertTrue(dmd.supportsSchemasInTableDefinitions());
        assertTrue(dmd.supportsSelectForUpdate());
        
        assertFalse(dmd.supportsStatementPooling());
        assertTrue(dmd.supportsStoredProcedures());
        assertTrue(dmd.supportsSubqueriesInComparisons());
        assertTrue(dmd.supportsSubqueriesInExists());
        assertTrue(dmd.supportsSubqueriesInIns());
        assertTrue(dmd.supportsSubqueriesInQuantifieds());
        assertTrue(dmd.supportsTableCorrelationNames());
        
        assertTrue(dmd.supportsTransactionIsolationLevel(
                Connection.TRANSACTION_READ_COMMITTED));
        assertTrue(dmd.supportsTransactionIsolationLevel(
                Connection.TRANSACTION_READ_UNCOMMITTED));
        assertTrue(dmd.supportsTransactionIsolationLevel(
                Connection.TRANSACTION_REPEATABLE_READ));
        assertTrue(dmd.supportsTransactionIsolationLevel(
                Connection.TRANSACTION_SERIALIZABLE));
        
        assertTrue(dmd.supportsTransactions());
        assertTrue(dmd.supportsUnion());
        assertTrue(dmd.supportsUnionAll());         
    }
    
    /**
     * Test group of methods provides the limits imposed by a given data source
     *  Methods start with
     * 'getMax'. See section 7.4 in JDBC 3.0 specification.
     * 
     * Note a return of zero from one of these functions means
     * no limit or limit not known.
     * 
     * @throws SQLException 
     *
     */
    public void testDataSourceLimits() throws SQLException
    {
        DatabaseMetaData dmd = getDMD();
        
        assertEquals(0, dmd.getMaxBinaryLiteralLength());
        
        // Catalogs not supported.
        assertEquals(0, dmd.getMaxCatalogNameLength());
        
        assertEquals(0, dmd.getMaxCharLiteralLength());
        
        assertEquals(128, dmd.getMaxColumnNameLength());
        assertEquals(0, dmd.getMaxColumnsInGroupBy());
        assertEquals(0, dmd.getMaxColumnsInIndex());
        assertEquals(0, dmd.getMaxColumnsInOrderBy());
        assertEquals(0, dmd.getMaxColumnsInSelect());
        assertEquals(0, dmd.getMaxColumnsInTable());
        assertEquals(0, dmd.getMaxConnections());
        assertEquals(128, dmd.getMaxCursorNameLength());
        assertEquals(0, dmd.getMaxIndexLength());
        assertEquals(128, dmd.getMaxProcedureNameLength());
        assertEquals(0, dmd.getMaxRowSize());
        assertEquals(128, dmd.getMaxSchemaNameLength());
        assertEquals(0, dmd.getMaxStatementLength());
        assertEquals(0, dmd.getMaxStatements());
        assertEquals(128, dmd.getMaxTableNameLength());
        assertEquals(0, dmd.getMaxTablesInSelect());
        assertEquals(128, dmd.getMaxUserNameLength());
    }
    
    public void testMiscellaneous() throws SQLException
    {
        DatabaseMetaData dmd = getDMD();
        
        assertTrue(dmd.allProceduresAreCallable());
        assertTrue(dmd.allTablesAreSelectable());
        assertFalse(dmd.dataDefinitionCausesTransactionCommit());
        assertFalse(dmd.dataDefinitionIgnoredInTransactions());
        
        // Derby does not yet implement scroll sensitive resultsets, so can't
        //   see changes for those; all *AreDetected and *AreVisible methods
        //   return false.
        // For Forward Only ResultSets, also see lang.UpdatableResultSetTest
        
        // *AreDetected; expect true for updates and deletes of 
        //   TYPE_SCROLL_INSENSITIVE, all others should be false
        assertFalse(dmd.deletesAreDetected(ResultSet.TYPE_FORWARD_ONLY));
        assertTrue(dmd.deletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE));
        assertFalse(dmd.deletesAreDetected(ResultSet.TYPE_SCROLL_SENSITIVE));
        assertFalse(dmd.insertsAreDetected(ResultSet.TYPE_FORWARD_ONLY));
        assertFalse(dmd.insertsAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE));
        assertFalse(dmd.insertsAreDetected(ResultSet.TYPE_SCROLL_SENSITIVE));
        assertFalse(dmd.updatesAreDetected(ResultSet.TYPE_FORWARD_ONLY));
        assertTrue(dmd.updatesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE));
        assertFalse(dmd.updatesAreDetected(ResultSet.TYPE_SCROLL_SENSITIVE));
        
        // others*AreVisible
        // Since Derby materializes a forward only ResultSet incrementally, 
        //   it is possible to see changes for FORWARD_ONLY
        // Scroll insensitive ResultSet by their definition do not see changes
        //   made by others
        assertTrue(dmd.othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
        assertFalse(dmd.othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
        assertFalse(dmd.othersDeletesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE));
        assertTrue(dmd.othersInsertsAreVisible(ResultSet.TYPE_FORWARD_ONLY));
        assertFalse(dmd.othersInsertsAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
        assertFalse(dmd.othersInsertsAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE));
        assertTrue(dmd.othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
        assertFalse(dmd.othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
        assertFalse(dmd.othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE));
        
        // Scroll insensitive ResultSets see updates, deletes, but not inserts
        assertFalse(dmd.ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
        assertTrue(dmd.ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
        assertFalse(dmd.ownDeletesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE));
        assertFalse(dmd.ownInsertsAreVisible(ResultSet.TYPE_FORWARD_ONLY));
        assertFalse(dmd.ownInsertsAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
        assertFalse(dmd.ownInsertsAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE));
        assertFalse(dmd.ownUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
        assertTrue(dmd.ownUpdatesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
        assertFalse(dmd.ownUpdatesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE));
                
        assertTrue(dmd.doesMaxRowSizeIncludeBlobs());
        
        // Catalogs not supported, so empty string returned for separator.
        assertEquals("", dmd.getCatalogSeparator());
        assertEquals("CATALOG", dmd.getCatalogTerm());
        
        assertEquals(Connection.TRANSACTION_READ_COMMITTED,
                dmd.getDefaultTransactionIsolation());
        
        assertEquals("", dmd.getExtraNameCharacters());
        assertEquals("\"", dmd.getIdentifierQuoteString());
        
        assertEquals("PROCEDURE", dmd.getProcedureTerm());
        
        assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT,
                dmd.getResultSetHoldability());
        
        assertEquals("SCHEMA", dmd.getSchemaTerm());
        
        assertEquals("", dmd.getSearchStringEscape());
        
        assertEquals(DatabaseMetaData.sqlStateSQL99,
                dmd.getSQLStateType());
        
        assertFalse(dmd.isCatalogAtStart()); 
        
        assertTrue(dmd.locatorsUpdateCopy());
        
        assertTrue(dmd.usesLocalFilePerTable());
        assertTrue(dmd.usesLocalFiles());
    }
    
    /**
     * Methods that describe the version of the
     * driver and database.
     *
     * @throws java.sql.SQLException
     */
    public void testVersionInfo() throws SQLException
    {
        DatabaseMetaData dmd = getDMD();
        int databaseMajor = dmd.getDatabaseMajorVersion();
        int databaseMinor = dmd.getDatabaseMinorVersion();
        
        int driverMajor = dmd.getDriverMajorVersion();
        int driverMinor = dmd.getDriverMinorVersion();
        
        String databaseVersion = dmd.getDatabaseProductVersion();
        String driverVersion = dmd.getDriverVersion();
        
        if (usingEmbedded())
        {
            // Database *is* the driver.
            
            assertEquals("Embedded Major version ",
                    databaseMajor, driverMajor);
            assertEquals("Embedded Minor version ",
                    databaseMinor, driverMinor);
            
            assertEquals("Embedded version",
                    databaseVersion, driverVersion);
        }
        
        assertEquals("Apache Derby", dmd.getDatabaseProductName());
        
        String driverName = dmd.getDriverName();
        if (usingEmbedded())
        {
            assertEquals("Apache Derby Embedded JDBC Driver",
                    driverName);
        }
        else if (usingDerbyNetClient())
        {
            assertEquals("Apache Derby Network Client JDBC Driver",
                    driverName);
        }

        int jdbcMajor = dmd.getJDBCMajorVersion();
        int jdbcMinor = dmd.getJDBCMinorVersion();
        
        int expectedJDBCMajor = -1;
        int expectedJDBCMinor = -1;
        // java 8 - jdbc 4.2
        if (JDBC.vmSupportsJDBC42())
        {
            expectedJDBCMajor = 4;
            expectedJDBCMinor = 2;
        }
        // java 7 - jdbc 4.1
        else if (JDBC.vmSupportsJDBC41())
        {
            expectedJDBCMajor = 4;
            expectedJDBCMinor = 1;
        }
        // java 6 - jdbc 4.0
        else if (JDBC.vmSupportsJDBC4())
        {
            expectedJDBCMajor = 4;
            expectedJDBCMinor = 0;
        }
         
        if (expectedJDBCMajor != -1)
        {
            assertEquals("JDBC Major version",
                    expectedJDBCMajor, jdbcMajor);
            assertEquals("JDBC Minor version", expectedJDBCMinor, jdbcMinor);
        }
    }
    
    /**
     * getURL() method. Note that this method
     * is the only JDBC 3 DatabaseMetaData method
     * that is dropped in JSR169.
     *
     * @throws java.sql.SQLException
     */
    public void testGetURL() throws SQLException
    {
        DatabaseMetaData dmd = getDMD();
        
        String url;
        try {
            url = dmd.getURL();
        } catch (NoSuchMethodError e) {
            // JSR 169 - method must not be there!
            assertTrue("getURL not supported", JDBC.vmSupportsJSR169());
            assertFalse("getURL not supported", JDBC.vmSupportsJDBC3());
            return;
        }
        
        assertFalse("getURL is supported!", JDBC.vmSupportsJSR169());
        assertTrue("getURL is supported!", JDBC.vmSupportsJDBC3());

        TestConfiguration config = getTestConfiguration();
        String expectedURL = config.getJDBCUrl();

        // DERBY-4886: Embedded returns the URL without connection attributes,
        // client returns the URL with connection attributes.
        if (usingDerbyNetClient()) {
            List<String> urlComponents = Arrays.asList(url.split(";"));

            // Only compare whatever comes before the first semi-colon with
            // the expected URL. Check connection attributes separately.
            url = urlComponents.get(0);

            // Put each actual connection attribute in a HashSet for easy
            // comparison.
            HashSet<String> attrs = new HashSet<String>(
                    urlComponents.subList(1, urlComponents.size()));

            // Put each expected connection attribute in a HashSet.
            HashSet<String> expectedAttrs = new HashSet<String>();
            Properties ca = config.getConnectionAttributes();
            for (String key : ca.stringPropertyNames()) {
                expectedAttrs.add(key + '=' + ca.getProperty(key));
            }

            // Verify that the actual connection attributes match the
            // expected attributes. Order is irrelevant.
            assertEquals("Connection attributes don't match",
                         expectedAttrs, attrs);
        }

        assertEquals("getURL match", expectedURL, url);
    }
    
    /**
     * Derby stores unquoted identifiers as upper
     * case and quoted ones as mixed case.
     * They are always compared case sensitive.
     *
     * @throws java.sql.SQLException
     */
    public void testIdentifierStorage() throws SQLException
    {
        DatabaseMetaData dmd = getDMD();
        
        assertFalse(dmd.storesLowerCaseIdentifiers());
        assertFalse(dmd.storesLowerCaseQuotedIdentifiers());
        assertFalse(dmd.storesMixedCaseIdentifiers());
        
        assertTrue(dmd.storesMixedCaseQuotedIdentifiers());
        assertTrue(dmd.storesUpperCaseIdentifiers());
        assertFalse(dmd.storesUpperCaseQuotedIdentifiers());
    }
    
    /**
     * methods that return information about handling NULL.
     *
     * @throws java.sql.SQLException
     */
    public void testNullInfo() throws SQLException
    {
        DatabaseMetaData dmd = getDMD();
        
        assertTrue(dmd.nullPlusNonNullIsNull());
        assertFalse(dmd.nullsAreSortedAtEnd());
        assertFalse(dmd.nullsAreSortedAtStart());
        assertTrue(dmd.nullsAreSortedHigh());
        assertFalse(dmd.nullsAreSortedLow());
    }
    
    /**
     * Method getSQLKeywords, returns list of SQL keywords
     * that are not defined by SQL92.
     *
     * @throws java.sql.SQLException
     */
    public void testSQLKeywords() throws SQLException
    {
        String keywords = getDMD().getSQLKeywords();
        
        assertNotNull(keywords);
        
        //TODO: more testing but not sure what!     
    }
    
    /**
     * Methods that return information specific to
     * the current connection.
     *
     * @throws java.sql.SQLException
     */
    public void testConnectionSpecific() throws SQLException
    {
        DatabaseMetaData dmd = getDMD();
        
        assertSame(getConnection(), dmd.getConnection());
        assertEquals(getTestConfiguration().getUserName(),
                dmd.getUserName());
        assertEquals(getConnection().isReadOnly(), dmd.isReadOnly());
    }
    
    /**
     * This is not a test of Derby but JDBC constants for meta data
     * that this test depends on.
     * The constants for nullability are the same but let's check to make sure.
     *
     */
    public void testConstants()
    {
      assertEquals(DatabaseMetaData.columnNoNulls, ResultSetMetaData.columnNoNulls);
      assertEquals(DatabaseMetaData.columnNullable, ResultSetMetaData.columnNullable);
      assertEquals(DatabaseMetaData.columnNullableUnknown, ResultSetMetaData.columnNullableUnknown);
    }
    
    /*
    ** DatabaseMetaData calls that return ResultSets.
    */
    
    /**
     * Test UDT-related metadata methods.
     * 
     * @throws java.lang.Exception
     */
    public void testUDTs() throws Exception
    {
        //
        // We only run this test if the database version is at least 10.6.
        // Otherwise we can't create a UDT.
        //
        Version dataVersion = getDataVersion( getConnection() );
        if ( dataVersion.compareTo( new Version( 10, 6, 0, 0 ) ) < 0 ) { return; }
        
        DatabaseMetaData dmd = getDMD();

        createObjectsForUDTTests();

        ResultSet rs = dmd.getUDTs(null,null,null,null);
        String[] columnNames = new String[] {
                "TYPE_CAT", "TYPE_SCHEM", "TYPE_NAME", "CLASS_NAME",
                "DATA_TYPE", "REMARKS", "BASE_TYPE"};
        int[] columnTypes = new int[] {
            Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.LONGVARCHAR,
            Types.INTEGER, Types.VARCHAR, Types.SMALLINT
        };
        boolean[] nullability = new boolean[] {
            true, true, false, false,
            false, true, true
        };
        assertMetaDataResultSet(rs, columnNames, columnTypes, nullability);

        String[][] expectedRows = new String[][]
        {
            {  null, schema, "PRICE", "org.apache.derbyTesting.functionTests.tests.lang.Price", "2000", null, null },
        };
        JDBC.assertFullResultSet( rs, expectedRows );

        // now try the test, specifying a specific type of UDT
        rs = dmd.getUDTs( null, null, null, new int[] { Types.JAVA_OBJECT } );
        JDBC.assertFullResultSet( rs, expectedRows );

        rs = dmd.getUDTs( null, null, null, new int[] { Types.DISTINCT, Types.JAVA_OBJECT } );
        JDBC.assertFullResultSet( rs, expectedRows );

        // no UDTs of these types
        rs = dmd.getUDTs( null, null, null, new int[] { Types.DISTINCT, Types.STRUCT } );
        JDBC.assertEmpty(rs);

        // try explicit schema and type name
        rs = dmd.getUDTs( null, schema, "PRICE",
                new int[] { Types.DISTINCT, Types.JAVA_OBJECT } );
        JDBC.assertFullResultSet( rs, expectedRows );

        rs = dmd.getUDTs( null, schema.substring(0, 2) + "%", "PRI%",
                new int[] { Types.DISTINCT, Types.JAVA_OBJECT } );
        JDBC.assertFullResultSet( rs, expectedRows );
        
        rs = dmd.getUDTs( null, "FOO", "PRICE", new int[] { Types.DISTINCT, Types.JAVA_OBJECT } );
        JDBC.assertEmpty(rs);

        // now make sure that getColumns() returns the right data
        rs = dmd.getColumns( null, schema, "ORDERS", null );
        expectedRows = new String[][]
        {
            {
                "", schema, "ORDERS", "TOTALPRICE",
                "2000", "\"" + schema + "\".\"PRICE\"", "-1", null,
                null, null, "1", "",
                null, null, null, null,
                "1", "YES", null, null,
                null, null, "NO", "NO", null
            },
        };
        JDBC.assertFullResultSet( rs, expectedRows );
        rs = dmd.getColumns( null, schema, "ORDERS", null );
        crossCheckGetColumnsAndResultSetMetaData( rs, false, 0 );

        dropObjectsForUDTTests();
    }

    /**
     * Create objects needed to test the UDT-related metadata calls
     *
     * @throws java.sql.SQLException
     */
    private void createObjectsForUDTTests() throws SQLException
    {
        getConnection().setAutoCommit(false);
        Statement s = createStatement();

        s.execute( "create type price external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java" );
        s.execute( "create table orders( totalPrice price )" );

        commit();
        getConnection().setAutoCommit(true);
    }

    /**
     * Drop the objects needed for testing the UDT-related metadata methods
     *
     * @throws java.sql.SQLException
     */
    private void dropObjectsForUDTTests() throws SQLException
    {
        getConnection().setAutoCommit(false);
        Statement s = createStatement();

        s.execute( "drop table orders" );
        s.execute( "drop type price restrict" );

        commit();
        getConnection().setAutoCommit(true);
    }
    
    /**
     * Test methods that describe attributes of SQL Objects
     * that are not supported by derby. In each case the
     * metadata should return an empty ResultSet of the
     * correct shape, and with correct names, datatypes and 
     * nullability for the columns in the ResultSet. 
     * 
     * @throws java.sql.SQLException
     */
    public void testUnimplementedSQLObjectAttributes() throws SQLException
    {
        DatabaseMetaData dmd = getDMD();
        
        ResultSet rs;
        
        rs = dmd.getAttributes(null,null,null,null);
        
        String [] columnNames = {
                "TYPE_CAT", "TYPE_SCHEM", "TYPE_NAME", "ATTR_NAME", "DATA_TYPE",
                "ATTR_TYPE_NAME", "ATTR_SIZE", "DECIMAL_DIGITS", "NUM_PREC_RADIX",
                "NULLABLE", "REMARKS", "ATTR_DEF", "SQL_DATA_TYPE", 
                "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", 
                "IS_NULLABLE", "SCOPE_CATALOG", "SCOPE_SCHEMA", "SCOPE_TABLE",
                "SOURCE_DATA_TYPE"
        };
        int [] columnTypes = { 
                Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, 
                Types.INTEGER, Types.VARCHAR, Types.INTEGER, Types.INTEGER, Types.INTEGER,
                Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.INTEGER,
                Types.INTEGER, Types.INTEGER, Types.INTEGER,
                Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.SMALLINT            
        };
        // DERBY-3171; we get a different value back for nullability for
        // a number of the columns with networkserver/client vs. embedded 
        boolean nullval = true;
        if (usingDerbyNetClient())
            nullval = false;
        boolean [] nullability = {
                true, true, false, nullval, nullval, nullval, nullval,
                nullval, nullval, nullval, true, true, nullval, nullval,
                nullval, nullval, nullval, true, true, true, true
        };
            
        assertMetaDataResultSet(rs, columnNames, columnTypes, nullability);
        JDBC.assertEmpty(rs);
        
        rs = dmd.getCatalogs();
        checkCatalogsShape(rs);
        JDBC.assertEmpty(rs);
        
        rs = dmd.getSuperTables(null,null,null);
        columnNames = new String[] {
                "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "SUPERTABLE_NAME"};
        columnTypes = new int[] {
                Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR};
        nullability = new boolean[] {
                true, true, false, false};
        assertMetaDataResultSet(rs, columnNames, columnTypes, nullability);
        JDBC.assertEmpty(rs);

        rs = dmd.getSuperTypes(null,null,null);
        columnNames = new String[] {
                "TYPE_CAT", "TYPE_SCHEM", "TYPE_NAME", "SUPERTYPE_CAT",
                "SUPERTYPE_SCHEM", "SUPERTYPE_NAME"};
        columnTypes = new int[] {
                Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
                Types.VARCHAR, Types.VARCHAR};
        nullability = new boolean[] {
                true, true, false, true, true, false};
        assertMetaDataResultSet(rs, columnNames, columnTypes, nullability);
        JDBC.assertEmpty(rs);

        ResultSet rss[] = getVersionColumns(null,null, "No_such_table");
        checkVersionColumnsShape(rss);
        JDBC.assertEmpty(rss[0]);
        JDBC.assertEmpty(rss[1]);
        
        rs.close();
        rss[0].close();
        rss[1].close();
    }
    
    /**
     * Implement ODBC equivalent for getVersionColumns - SYSIBM.SQLCOLUMNS
     *
     * @param catalog
     * @param schema
     * @param table
     *
     * @return version
     * @throws java.sql.SQLException
     */
    public ResultSet getVersionColumnsODBC(
            String catalog, String schema, String table)
        throws SQLException 
    {
        CallableStatement cs = prepareCall("CALL SYSIBM.SQLSPECIALCOLUMNS " +
            "(2, ?, ?, ?, 1, 1, 'DATATYPE=''ODBC''')");

        cs.setString(1, catalog);
        cs.setString(2, schema);
        cs.setString(3, table);
        cs.execute();
        return cs.getResultSet();
    }
    
    /**
     * Helper method for testing getVersionColumns - calls 
     * dmd.getVersionColumns for the JDBC call, and getVersionColumnsODBC for
     * the ODBC procedure
     * @param catalog
     * @param schema
     * @param table
     * @return version
     * @throws SQLException 
     */
    private ResultSet[] getVersionColumns(
            String catalog, String schema, String table)
        throws SQLException 
    {
        ResultSet[] rss = new ResultSet[2]; 
        DatabaseMetaData dmd = getDMD();
        rss[0]= dmd.getVersionColumns(catalog, schema, table);
        rss[1]= getVersionColumnsODBC(catalog, schema, table);
        return rss;        
    }

    /**
     * Six combinations of valid identifiers with mixed
     * case, to see how the various pattern matching
     * and returned values handle them.
     * This test only creates objects in these schemas.
     */
    private static final String[] IDS =
    {
            "one_dmd_test",
            "TWO_dmd_test",
            "ThReE_dmd_test",
            "\"four_dmd_test\"",
            "\"FIVE_dmd_test\"",
            "\"sIx_dmd_test\"" 
    };
    
    /**
     * All the builtin schemas.
     */
    private static final String[] BUILTIN_SCHEMAS = {
            "APP", "NULLID", "SQLJ", "SYS", "SYSCAT", "SYSCS_DIAG",
            "SYSCS_UTIL", "SYSFUN", "SYSIBM", "SYSPROC", "SYSSTAT"};
    
    public static String getStoredIdentifier(String sqlIdentifier)
    {
        if (sqlIdentifier.charAt(0) == '"')
            return sqlIdentifier.substring(1, sqlIdentifier.length() - 1);
        else
            return sqlIdentifier.toUpperCase(Locale.ENGLISH);       
    }

    /**
     * Test getSchemas() without modifying the database.
     * 
     * @throws SQLException
     */
    public void testGetSchemasReadOnly() throws SQLException {
        
        DatabaseMetaData dmd = getDMD();
         
        ResultSet rs = dmd.getSchemas();
        checkSchemas(rs, new String[0]);
    }
    
    /**
     * Test getSchemas().
     * 
     * @throws SQLException
     */
    public void testGetSchemasModify() throws SQLException {
        createSchemasForTests();
        DatabaseMetaData dmd = getDMD();
        ResultSet rs = dmd.getSchemas();
        checkSchemas(rs, IDS);
    }
    
    private void createSchemasForTests() throws SQLException
    {
        // Set to cleanup on teardown.
        modifiedDatabase = true;

        Statement s = createStatement();
        for (String IDS1 : IDS) {
            s.executeUpdate("CREATE SCHEMA " + IDS1);
        }
        s.close();
        
        commit();
    }
    
    
    /**
     * Check the returned information from a getSchemas().
     * The passed in String[] expected is a list of the
     * schemas expected to be present in the returned
     * set. The returned set may contain additional
     * schemas which will be ignored, thus this test
     * can be used regardless of the database state.
     * The builtin schemas are automatically checked
     * and must not be part of the passed in list.
     * @param rs
     * @param userExpected
     *
     * @throws java.sql.SQLException
     */
    public static void checkSchemas(ResultSet rs,
            String[] userExpected) throws SQLException
    {
        checkSchemasShape(rs);
        
        // Add in the system schemas
        String[] expected =
            new String[BUILTIN_SCHEMAS.length + userExpected.length];
        
        System.arraycopy(BUILTIN_SCHEMAS, 0,
                expected, 0, BUILTIN_SCHEMAS.length);
        System.arraycopy(userExpected, 0,
                expected, BUILTIN_SCHEMAS.length, userExpected.length);
                
        // Remove any quotes from user schemas and upper case
        // those without quotes.
        for (int i = BUILTIN_SCHEMAS.length; i < expected.length; i++)
        {          
            expected[i] = getStoredIdentifier(expected[i]);
        }
              
        //output is ordered by TABLE_SCHEM
        Arrays.sort(expected);
                   
        int nextMatch = 0;
 
        while (rs.next()) {
            String schema = rs.getString("TABLE_SCHEM");
            assertNotNull(schema);
            
            // Catalogs not supported
            assertNull(rs.getString("TABLE_CATALOG"));
                        
            if (nextMatch < expected.length)
            {
                if (expected[nextMatch].equals(schema))
                    nextMatch++;
            }
        }
        rs.close();
        assertEquals("Schemas missing ", expected.length, nextMatch);
    }

    /**
     * Check the shape of the ResultSet from any
     * getSchemas call.
     *
     * @param rs result set
     * @throws java.sql.SQLException
     */
    private static void checkSchemasShape(ResultSet rs) throws SQLException
    {
        assertMetaDataResultSet(rs,
          new String[] {
          "TABLE_SCHEM", "TABLE_CATALOG"
         },
         new int[] {
          Types.VARCHAR, Types.VARCHAR
         }
        , new boolean[] {false, true}
        );        
    }
    
    /**
     * Execute dmd.getTables() but perform additional checking
     * of the ODBC variant.
     * @param dmd
     * @param catalog
     * @param schema
     * @param table
     * @param tableTypes
     * @return result set from query
     * @throws java.sql.SQLException
     * @throws IOException 
     */
    private ResultSet getDMDTables(DatabaseMetaData dmd,
            String catalog, String schema, String table, String[] tableTypes)
        throws SQLException, IOException
    {
        checkGetTablesODBC(catalog, schema, table, tableTypes);
        return dmd.getTables(catalog, schema, table, tableTypes);
    }
    
    /**
     * Test getTables() without modifying the database.
     * 
     * @throws SQLException
     * @throws IOException 
     */
    public void testGetTablesReadOnly() throws SQLException, IOException {
        
        DatabaseMetaData dmd = getDMD();
        
        ResultSet rs;
        
        rs = getDMDTables(dmd, null, null, null, null);
        checkTablesShape(rs);
        int allTableCount = JDBC.assertDrainResults(rs);
        assertTrue("getTables() on all was empty!", allTableCount > 0);
        
        rs = getDMDTables(dmd, "%", "%", "%", null);
        checkTablesShape(rs);
        assertEquals("Different counts from getTables",
                allTableCount, JDBC.assertDrainResults(rs));
        
        rs = getDMDTables(dmd, null, "NO_such_schema", null, null);
        checkTablesShape(rs);
        JDBC.assertEmpty(rs);
        
        rs = getDMDTables(dmd, null, "SQLJ", null, null);
        checkTablesShape(rs);
        JDBC.assertEmpty(rs);
        
        rs = getDMDTables(dmd, null, "SQLJ", "%", null);
        checkTablesShape(rs);
        JDBC.assertEmpty(rs);
        
        rs = getDMDTables(dmd, null, "SYS", "No_such_table", null);
        checkTablesShape(rs);
        JDBC.assertEmpty(rs);
        
        String[] userTableOnly = new String[] {"TABLE"};

        // no user tables in SYS
        rs = getDMDTables(dmd, null, "SYS", null, userTableOnly);
        checkTablesShape(rs);
        JDBC.assertEmpty(rs);
        
        rs = getDMDTables(dmd, null, "SYS", "%", userTableOnly);
        checkTablesShape(rs);
        JDBC.assertEmpty(rs);
        
        String[] systemTableOnly = new String[] {"SYSTEM_TABLE"};
        
        rs = getDMDTables(dmd, null, "SYS", null, systemTableOnly);
        checkTablesShape(rs);
        int systemTableCount = JDBC.assertDrainResults(rs);
        assertTrue("getTables() on system tables was empty!", systemTableCount > 0);
        
        rs = getDMDTables(dmd, null, "SYS", "%", systemTableOnly);
        checkTablesShape(rs);
        assertEquals(systemTableCount, JDBC.assertDrainResults(rs));

        String[] viewOnly = new String[] {"VIEW"};
        rs = getDMDTables(dmd, null, "SYS", null, viewOnly);
        JDBC.assertEmpty(rs);
        
        rs = getDMDTables(dmd, null, "SYS", "%", viewOnly);
        JDBC.assertEmpty(rs);
        
        String[] allTables = {"SYNONYM","SYSTEM TABLE","TABLE","VIEW"};
        rs = getDMDTables(dmd, null, null, null, allTables);
        checkTablesShape(rs);
        assertEquals("Different counts from getTables",
                allTableCount, JDBC.assertDrainResults(rs));
        rs = getDMDTables(dmd, "%", "%", "%", allTables);
        checkTablesShape(rs);
        assertEquals("Different counts from getTables",
                allTableCount, JDBC.assertDrainResults(rs));
        
    }
    /**
     * Test getTables() with  modifying the database.
     * 
     * @throws SQLException
     * @throws IOException 
     */
    public void testGetTablesModify() throws Exception {
                
        int totalTables = createTablesForTest(false);
        
        DatabaseMetaData dmd = getDMD();
        ResultSet rs;
        
        String[] userTableOnly = new String[] {"TABLE"};
        
        // Get the list of idenifiers from IDS as the database
        // would store them in the order required.      
        String[] dbIDS = getSortedIdentifiers();    
               
        // Check the contents, ordered by TABLE_CAT, TABLE_SCHEMA, TABLE_NAME
        rs = getDMDTables(dmd, null, null, null, userTableOnly);
        checkTablesShape(rs);
        int rowPosition = 0;
        while (rs.next())
        {
            //boolean ourTable;
            assertEquals("TABLE_CAT", "", rs.getString("TABLE_CAT"));
            
            String schema_ = rs.getString("TABLE_SCHEM");
            
            // See if the table is in one of the schemas we created.
            // If not we perform what checking we can.
            boolean ourSchema = Arrays.binarySearch(dbIDS, schema_) >= 0;
            
            if (ourSchema) {        
                assertEquals("TABLE_SCHEM",
                    dbIDS[rowPosition/dbIDS.length], schema_);
                assertEquals("TABLE_NAME",
                    dbIDS[rowPosition%dbIDS.length], rs.getString("TABLE_NAME"));
            }
            
            assertEquals("TABLE_TYPE", "TABLE", rs.getString("TABLE_TYPE"));
            
            assertEquals("REMARKS", "", rs.getString("REMARKS"));

            assertNull("TYPE_CAT", rs.getString("TYPE_CAT"));
            assertNull("TYPE_SCHEM", rs.getString("TYPE_SCHEM"));
            assertNull("TYPE_NAME", rs.getString("TYPE_NAME"));
            assertNull("SELF_REFERENCING_COL_NAME", rs.getString("SELF_REFERENCING_COL_NAME"));
            assertNull("REF_GENERATION", rs.getString("REF_GENERATION"));
            
            if (ourSchema)
                rowPosition++;
         }
         rs.close();
         assertEquals("getTables count for all user tables",
               totalTables, rowPosition);
       
         Random rand = new Random();
        
        for (String schema_ : dbIDS) {
            int pc = rand.nextInt(6);
            String schemaPattern = schema_.substring(0, pc + 2) + "%";
            
            rs = getDMDTables(dmd, null, schemaPattern, null, userTableOnly);
            checkTablesShape(rs);
            rowPosition = 0;
            while (rs.next())
            {
                assertEquals("TABLE_SCHEM",
                        schema_, rs.getString("TABLE_SCHEM"));
                assertEquals("TABLE_NAME",
                        dbIDS[rowPosition%dbIDS.length], rs.getString("TABLE_NAME"));
                assertEquals("TABLE_TYPE", "TABLE", rs.getString("TABLE_TYPE"));
                rowPosition++;
            }
            rs.close();
            assertEquals("getTables count schema pattern",
                    dbIDS.length, rowPosition);
        }
         
        for (String table : dbIDS) {
            int pc = rand.nextInt(6);
            String tablePattern = table.substring(0, pc + 2) + "%";
            
            rs = getDMDTables(dmd, null, null, tablePattern, userTableOnly);
            checkTablesShape(rs);
            rowPosition = 0;
            while (rs.next())
            {
                assertEquals("TABLE_SCHEM",
                        dbIDS[rowPosition%dbIDS.length], rs.getString("TABLE_SCHEM"));
                assertEquals("TABLE_TYPE", "TABLE", rs.getString("TABLE_TYPE"));
                assertEquals("TABLE_NAME",
                        table, rs.getString("TABLE_NAME"));
                rowPosition++;
            }
            rs.close();
            assertEquals("getTables count schema pattern",
                    dbIDS.length, rowPosition);
        }
    }
      
  
    /**
     * Execute and check the ODBC variant of getTables which
     * uses a procedure to provide the same information to ODBC clients.
     * @param catalog
     * @param schema
     * @param table
     * @param tableTypes
     * @throws java.sql.SQLException
     * @throws IOException 
     */
    private void checkGetTablesODBC(String catalog, String schema,
            String table, String[] tableTypes) throws SQLException, IOException
    {
        String tableTypesAsString = null;
        if (tableTypes != null) {
            int count = tableTypes.length;
            StringBuilder sb = new StringBuilder();

            for (int i = 0; i < count; i++) {
                if (i > 0)
                    sb.append(",");
                sb.append(tableTypes[i]);
            }
            tableTypesAsString = sb.toString();
        }

        CallableStatement cs = prepareCall(
            "CALL SYSIBM.SQLTABLES(?, ?, ?, ?, 'DATATYPE=''ODBC''')");
        cs.setString(1, catalog);
        cs.setString(2, schema);
        cs.setString(3, table);
        cs.setString(4, tableTypesAsString);
        
        cs.execute();
        ResultSet odbcrs = cs.getResultSet();
        assertNotNull(odbcrs);
        
        // Returned ResultSet will have the same shape as
        // DatabaseMetaData.getTables() even though ODBC
        // only defines the first five columns.
        checkTablesShape(odbcrs);
        
        // Expect the contents of JDBC and ODBC metadata to be the same.
        ResultSet dmdrs = getDMD().getTables(catalog, schema, table, tableTypes);
        JDBC.assertSameContents(odbcrs, dmdrs);
        
        cs.close();
    }   

    /**
     * Create a set of tables using the identifiers in IDS.
     * For each identifier in IDS a schema is created.
     * For each identifier in IDS create a table in every schema just created.
     * Each table has five columns with names using the identifiers from IDS
     * suffixed with _N where N is the column number in the table. The base
     * name for each column is round-robined from the set of IDS.
     * The type of each column is round-robined from the set of supported
     * types returned by getSQLTypes.
     * 
     * <BR>
     * skipXML can be set to true to create tables without any XML
     * columns. This is useful for getColumns() testing where
     * the fixture compares the output of DatabaseMetaData to
     * ResultSetMetaData by a SELCT * from the table. However
     * for XML columns they cannot be returned through JDBC yet.
     * 
     * @param skipXML true if tables with the XML column should not
     * be created.
     * @return total number of tables created
     * @throws SQLException
     */
    private int createTablesForTest(boolean skipXML) throws Exception
    {
        getConnection().setAutoCommit(false);
        List<String> types = getSQLTypes(getConnection());
        if (skipXML)
            types.remove("XML");
            
        //
        // The BOOLEAN datatype is only allowed in databases
        // at level 10.7 or higher.
        //
        Version dataVersion = getDataVersion( getConnection() );
        if ( dataVersion.compareTo( new Version( 10, 7, 0, 0 ) ) < 0 )
        {
            types.remove("BOOLEAN");
        }
        
        int typeCount = types.size();
               
        createSchemasForTests();
        
        Statement s = createStatement();
        
        int columnCounter = 0;
        
        for (String IDS1 : IDS) {
            for (String IDS2 : IDS) {
                StringBuilder sb = new StringBuilder();
                sb.append("CREATE TABLE ");
                sb.append(IDS1);
                sb.append('.');
                sb.append(IDS2);
                sb.append(" (");
                
                // Five columns per table
                for (int c = 1; c <= 5; c++) {
                    String colName = IDS[columnCounter % IDS.length];
                    boolean delimited = colName.charAt(colName.length() - 1) == '"';
                    if (delimited)
                        colName = colName.substring(0, colName.length() - 1);
                    sb.append(colName);  
                    sb.append('_');
                    sb.append(c); // append the column number
                    if (delimited)
                        sb.append('"');
                    sb.append(' ');
                    sb.append(types.get(columnCounter++ % typeCount));
                    if (c < 5)
                        sb.append(", ");
                }
                sb.append(")");
                s.execute(sb.toString());
            }
        }
        
        s.close();
        
        commit();

        return IDS.length * IDS.length;
    }
    
    /**
     * Test getTableColumns().
     * Contents are compared to the ResultSetMetaData
     * for a SELECT * from the table. All columns in
     * all tables are checked.
     *
     * @throws java.lang.Exception
     */
    public void testGetColumnsReadOnly() throws Exception
    {
        ResultSet[] rs = getColumns(null, null, null, null);
        for ( int j =0 ; j<2 ; j++) {
            checkColumnsShape(rs[j], j);
            crossCheckGetColumnsAndResultSetMetaData(rs[j], false, j);
        }
    }
    
    /**
     * Test getColumns() with  modifying the database.
     * 
     * @throws SQLException
     */
    public void testGetColumnsModify() throws Exception {
           
        // skip XML datatype as our cross check with
        // ResultSetMetaData will fail
        int totalTables = createTablesForTest(true);
        
        // First cross check all the columns in the database
        // with the ResultSetMetaData.
        testGetColumnsReadOnly();
        
        Random rand = new Random();
        String[] dbIDS = getSortedIdentifiers();
            
        for (int i = 1; i < 20; i++) {
            int seenColumnCount = 0;
            // These are the pattern matching parameters
            String schemaPattern = getPattern(rand, dbIDS);
            String tableNamePattern = getPattern(rand, dbIDS);
            String columnNamePattern = getPattern(rand, dbIDS);
        
            ResultSet[] rs = getColumns(null,
                schemaPattern, tableNamePattern, columnNamePattern);
            
            for (int j=0  ; j<2 ; j++) {
                checkColumnsShape(rs[j], j);

                while (rs[j].next())
                {
                    String schema_ = rs[j].getString("TABLE_SCHEM");
                    String table = rs[j].getString("TABLE_NAME");
                    String column = rs[j].getString("COLUMN_NAME");

                    assertMatchesPattern(schemaPattern, schema_);
                    assertMatchesPattern(tableNamePattern, table);
                    assertMatchesPattern(columnNamePattern, column);

                    seenColumnCount++;
                }
                rs[j].close();
            }
            
            // Re-run to check the correct data is returned
            // when filtering is enabled
            rs = getColumns(null,
                    schemaPattern, tableNamePattern, columnNamePattern);
            for (int j=0  ; j<2 ; j++) {
                crossCheckGetColumnsAndResultSetMetaData(rs[j], true, j);
            }
            
            // Now re-execute fetching all schemas, columns etc.
            // and see we can the same result when we "filter"
            // in the application
            rs = getColumns(null,null, null, null);
            
            int appColumnCount = 0;
            for (int j=0  ; j<2 ; j++) {
                while (rs[j].next())
                {
                    String schema_ = rs[j].getString("TABLE_SCHEM");
                    String table = rs[j].getString("TABLE_NAME");
                    String column = rs[j].getString("COLUMN_NAME");

                    if (!doesMatch(schemaPattern, 0, schema_, 0))
                        continue;               
                    if (!doesMatch(tableNamePattern, 0, table, 0))
                        continue;
                    if (!doesMatch(columnNamePattern, 0, column, 0))
                        continue;

                    appColumnCount++;
                }
                rs[j].close();
            }
            assertEquals("Mismatched column count on getColumns() filtering",
                    seenColumnCount, appColumnCount);    
        }       
    }
    
    
    private void assertMatchesPattern(String pattern, String result)
    {       
        if (!doesMatch(pattern, 0, result, 0))
        {
            fail("Bad pattern matching:" + pattern + 
                            " result:" + result);
        }

    }
     
    /**
     * See if a string matches the pattern as defined by
     * DatabaseMetaData. By passing in non-zero values
     * can check sub-sets of the pattern against the
     * sub strings of the result.
     * <BR>
     * _ matches a single character
     * <BR>
     * % matches zero or more characters
     * <BR>
     * Other characters match themselves.
     * @param pattern Pattern
     * @param pp Position in pattern to start the actual pattern from
     * @param result result string
     * @param rp position in result to starting checking
     * @return true if a match is found
     */
    private boolean doesMatch(String pattern, int pp,
            String result, int rp)
    {
        // Find a match
        for (;;)
        {
            if (pp == pattern.length() && rp == result.length())
                return true;
            
            // more characters to match in the result but
            // no more pattern.
            if (pp == pattern.length())
                return false;
            
            char pc = pattern.charAt(pp);
            if (pc == '_')
            {
                // need to match a single character but
                // exhausted result, so no match.
                if (rp == result.length())
                    return false;
                
                pp++;
                rp++;
            }
            else if (pc == '%')
            {
                // % at end, complete match regardless of
                // position of result since % matches zero or more.
                if (pp == pattern.length() - 1)
                {
                    return true;
                }
                
                // Brut force, we have a pattern like %X
                // and we are say in the third character of
                // abCdefgX
                // then start a 'CdefgX' and look for a match,
                // then 'defgX' etc.
                for (int sp = rp; sp < result.length(); sp++)
                {
                    if (doesMatch(pattern, pp+1, result, sp))
                    {
                        // Have a match for the pattern after the %
                        // which means we have a match for the pattern
                        // with the % since we can match 0 or mor characters
                        // with %.
                        return true;
                    }
                }
                
                // Could not match the pattern after the %
                return false;
          }
            else
            {
                // need to match a single character but
                // exhausted result, so no match.
                if (rp == result.length())
                    return false;
                
                // Single character, must match exactly.
                if (pc != result.charAt(rp))
                {
                    //Computer says no.
                    return false;
                }
                pp++;
                rp++;
            }
            
        }
        
    }
    
    private String getPattern(Random rand, String[] dbIDS)
    {
        int y = rand.nextInt(100);
        if (y < 10)
            return "%"; // All
        if (y < 30)
            return dbIDS[rand.nextInt(dbIDS.length)]; // exact match
        
        String base;
        if (y < 40)
        {
            // Base for some pattern that can never match
            base = "XxZZzXXZZZxxXxZz";
        }
        else
        {
            base = dbIDS[rand.nextInt(dbIDS.length)];
        }
        
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < base.length();)
        {
            int x = rand.nextInt(10);
            if (x < 5)
                x = 0; // bias towards keeping the characters.
            
            boolean inWild;
            if (sb.length() == 0)
                inWild = false;
            else
            {
                char last = sb.charAt(sb.length() - 1);
                inWild = last == '_' || last == '%';
            }

            if (x == 0)
            {
                // character from base
                sb.append(base.charAt(i++));
            }
            else if (x == 5)
            {
                i++;
               // single character match
                if (!inWild)
                    sb.append('_');

            }
            else
            {
                i += (x - 5);
                
                // replace a number of characters with %
                if (!inWild)
                    sb.append('%');
               
            }
        }
        
        // Some pattern involving 
        return sb.toString();
    }
    
    /**
     * Compare a ResultSet from getColumns() with ResultSetMetaData
     * returned from a SELECT * against the table. This method
     * handles situations where a full set of the columns are in
     * the ResultSet.
     * The first action is to call rs.next().
     * The ResultSet will be closed by this method.
     * @param rs resultset to crossCheck
     * @param partial used to indicate if ordinal position should get checked
     * @param odbc - flag to indicate if this was a resultset obtained
     *    from a JDBC (0) or ODBC (1) call.
     * @throws SQLException
     */
    private void crossCheckGetColumnsAndResultSetMetaData(ResultSet rs,
            boolean partial, int odbc)
    throws Exception
    {
        Statement s = createStatement();
        while (rs.next())
        {
            String schema_ = rs.getString("TABLE_SCHEM");
            String table = rs.getString("TABLE_NAME");
            
            ResultSet rst = s.executeQuery(
                "SELECT * FROM " + JDBC.escape(schema_, table));
            ResultSetMetaData rsmdt = rst.getMetaData();

                     
            for (int col = 1; col <= rsmdt.getColumnCount() ; col++)
            {
                if (!partial) {
                    if (col != 1)
                        assertTrue(rs.next());
                
                    assertEquals("ORDINAL_POSITION",
                            col, rs.getInt("ORDINAL_POSITION"));
                }
                
                assertEquals("TABLE_CAT",
                        "", rs.getString("TABLE_CAT"));
                assertEquals("TABLE_SCHEM",
                        schema_, rs.getString("TABLE_SCHEM"));
                assertEquals("TABLE_NAME",
                        table, rs.getString("TABLE_NAME"));
                
                crossCheckGetColumnRowAndResultSetMetaData(rs, rsmdt, odbc);
                if (partial)
                    break;
                
            }
            rst.close();
            
            
        }
        rs.close();
        s.close();
    }
    
    /**
     * Cross check a single row from getColumns() with ResultSetMetaData
     * for a SELECT * from the same table.
     * @param rs ResultSet from getColumns already positioned on the row.
     * @param rsmdt ResultSetMetaData for the SELECT *
     * @param odbc 0 for JDBC call, 1 for ODBC. Needed to allow for difference
     *    in using BUFFER_LENGTH (ODBC) or no(JDBC).
     * @throws SQLException
     */
    public void crossCheckGetColumnRowAndResultSetMetaData(
            ResultSet rs, ResultSetMetaData rsmdt, int odbc)
        throws Exception
    {
        int col = rs.getInt("ORDINAL_POSITION");
        Version dataVersion = getDataVersion( getConnection() );

        String catalogName = rs.getString("TABLE_CAT");
        String schemaName = rs.getString("TABLE_SCHEM");
        String tableName = rs.getString("TABLE_NAME");

        // Check that the catalog/schema/table names reported by the
        // ResultSetMetaData are correct. Note that for views, RSMD will
        // return data for the underlying table, not for the view itself.
        // Therefore, skip the check for views.
        ResultSet views =
            rs.getStatement().getConnection().getMetaData().getTables(
                    catalogName, schemaName, tableName, JDBC.GET_TABLES_VIEW);
        boolean isView = JDBC.assertDrainResults(views) > 0;

        if (!isView) {
            assertEquals("RSMD.getCatalogName",
                         catalogName, rsmdt.getCatalogName(col));
            assertEquals("RSMD.getSchemaName",
                         schemaName, rsmdt.getSchemaName(col));
            assertEquals("RSMD.getTableName",
                         tableName, rsmdt.getTableName(col));
        }
        
        assertEquals("COLUMN_NAME",
                rsmdt.getColumnName(col), rs.getString("COLUMN_NAME"));
        
        // DERBY-2285 BOOLEAN columns appear different on
        // network client.
        // DMD returns BOOLEAN
        // RSMD returns SMALLINT
        int dmdColumnType = rs.getInt("DATA_TYPE");
        if (dmdColumnType == Types.JAVA_OBJECT && usingDerbyNetClient()
                 &&  ( dataVersion.compareTo( new Version( 10, 6, 0, 0 ) ) < 0 ) )
        {
            // DMD returns JAVA_OBJECT
            // RSMD returns LONGVARBINARY!                    
            assertEquals("DATA_TYPE",
                    Types.LONGVARBINARY, rsmdt.getColumnType(col));                   
        }
        else if (dmdColumnType == Types.VARBINARY && usingDerbyNetClient())
        {
            // DMD returns different type name to RSMD
            assertEquals("DATA_TYPE",
                    Types.VARBINARY, rsmdt.getColumnType(col));  
        }
        else if (dmdColumnType == Types.BINARY && usingDerbyNetClient())
        {
            // DMD returns different type name to RSMD
            assertEquals("DATA_TYPE",
                    Types.BINARY, rsmdt.getColumnType(col));                               
        }
        else if (dmdColumnType == Types.NUMERIC && usingDerbyNetClient())
        {
            // DERBY-584 inconsistency in numeric & decimal
            assertEquals("DATA_TYPE",
                    Types.DECIMAL, rsmdt.getColumnType(col));
            
            assertEquals("TYPE_NAME",
                    "DECIMAL", rsmdt.getColumnTypeName(col));
                       
            assertEquals("TYPE_NAME",
                    "NUMERIC", rs.getString("TYPE_NAME"));
        }
        else
        {
            assertEquals("DATA_TYPE",
                rsmdt.getColumnType(col), rs.getInt("DATA_TYPE"));
            assertEquals("TYPE_NAME",
                rsmdt.getColumnTypeName(col), rs.getString("TYPE_NAME"));
        }
        
        /*
        if (dmdColumnType != Types.JAVA_OBJECT) {
        System.out.println("TYPE " + rs.getInt("DATA_TYPE"));
        System.out.println(JDBC.escape(schema, table) + " " + rs.getString("COLUMN_NAME"));
        assertEquals("COLUMN_SIZE",
                rsmdt.getPrecision(col), rs.getInt("COLUMN_SIZE"));
        }
        */
        
        // not used by JDBC spec, but by ODBC
        if (odbc == 0)
        {
            assertEquals("BUFFER_LENGTH", 0, rs.getInt("BUFFER_LENGTH"));
            assertTrue("BUFFER_LENGTH", rs.wasNull());
        }
        else
        {
            if (col == 0)
                assertEquals("BUFFER_LENGTH", 0, rs.getInt("BUFFER_LENGTH"));
            else
                assertTrue(rs.getInt("BUFFER_LENGTH") != 0);
        }
        
        /*
        assertEquals("DECIMAL_DIGITS",
                rsmdt.getScale(col), rs.getInt("DECIMAL_DIGITS"));
        */
        
        // This assumes the constants defined by DMD and ResultSet
        // for nullability are equal. They are by inspection
        // and since they are static final and part of a defined
        // api by definition they cannot change. We also
        // check statically this is true in the testConstants fixture.
        assertEquals("NULLABLE",
                rsmdt.isNullable(col), rs.getInt("NULLABLE"));
        
        // REMARKS set to empty string by Derby
        assertEquals("REMARKS", "", rs.getString("REMARKS"));
        
        // COLUMN_DEF ??
       
        if (odbc == 0)
        {
            // both unused by JDBC spec
            assertEquals("SQL_DATA_TYPE", 0, rs.getInt("SQL_DATA_TYPE"));
            assertTrue(rs.wasNull());
            assertEquals("SQL_DATETIME_SUB", 0, rs.getInt("SQL_DATETIME_SUB"));
            assertTrue(rs.wasNull());
        }
        else {
            // ODBC uses formula:
            // DATA_TYPE = 10 * SQL_DATA_TYPE + SQL_DATETIME_SUB,
            // e.g SQL_TIME_STAMP = 10 * SQL_DATETIME + SQL_CODE_TIMESTAMP
            //                 93 = 10 * 9            + 3
            if (dmdColumnType == 91)
            {
                assertTrue(rs.getInt("SQL_DATA_TYPE")== 9);
                assertTrue(rs.getInt("SQL_DATETIME_SUB")== 1);
            }
            else if (dmdColumnType == 92)
            {
                assertTrue(rs.getInt("SQL_DATA_TYPE")== 9);
                assertTrue(rs.getInt("SQL_DATETIME_SUB")== 2);
            }
            else if (dmdColumnType == 93)
            {
                assertTrue(rs.getInt("SQL_DATA_TYPE")== 9);
                assertTrue(rs.getInt("SQL_DATETIME_SUB")== 3);
            }
            else
                assertTrue(rs.getInt("SQL_DATA_TYPE") == dmdColumnType);
        }
        
        // IS_NULLABLE
        switch (rsmdt.isNullable(col))
        {
        case ResultSetMetaData.columnNoNulls:
            assertEquals("IS_NULLABLE", "NO", rs.getString("IS_NULLABLE"));
            break;
        case ResultSetMetaData.columnNullable:
            assertEquals("IS_NULLABLE", "YES", rs.getString("IS_NULLABLE"));
            break;
        case ResultSetMetaData.columnNullableUnknown:
            assertEquals("IS_NULLABLE", "", rs.getString("IS_NULLABLE"));
            break;
        default:
            fail("invalid return from rsmdt.isNullable(col)");
        }
        
        // SCOPE not supported
        assertNull("SCOPE_CATALOG", rs.getString("SCOPE_CATALOG"));
        assertNull("SCOPE_CATLOG", rs.getString("SCOPE_CATLOG"));
        assertNull("SCOPE_SCHEMA", rs.getString("SCOPE_SCHEMA"));
        assertNull("SCOPE_TABLE", rs.getString("SCOPE_TABLE"));
        
        // DISTINCT not supported
        assertEquals("SOURCE_DATA_TYPE", 0, rs.getShort("SOURCE_DATA_TYPE"));
        assertTrue(rs.wasNull());
        
        // IS_AUTOINCREMENT added in JDBC 4.0
       assertEquals("IS_AUTOINCREMENT",
               rsmdt.isAutoIncrement(col) ? "YES" : "NO",
               rs.getString("IS_AUTOINCREMENT"));
       assertFalse(rs.wasNull());        
    }
    
    /**
     * Implement ODBC equivalent for getColumns - SYSIBM.SQLCOLUMNS
     *
     * @param catalog
     * @param schemaPattern
     * @param tableNamePattern
     * @param columnNamePattern
     *
     * @return result set of query
     * @throws java.sql.SQLException
     */
    private ResultSet getColumnsODBC(
            String catalog, String schemaPattern, String tableNamePattern,
            String columnNamePattern)
        throws SQLException 
    {
        CallableStatement cs = prepareCall("CALL SYSIBM.SQLCOLUMNS(" +
                "?, ?, ?, ?, 'DATATYPE=''ODBC''')");

        cs.setString(1, catalog);
        cs.setString(2, schemaPattern);
        cs.setString(3, tableNamePattern);
        cs.setString(4, columnNamePattern);
        cs.execute();
        return cs.getResultSet();
    }
    
    /**
     * Helper method for testing getColumns - calls dmd.getColumns for
     * the JDBC call, and getColumnsODBC for the ODBC procedure
     * @param catalog
     * @param schemaPattern
     * @param tableNamePattern
     * @param columnNamePattern
     * @return Array of the two result sets of columns
     * @throws SQLException 
     */
    private ResultSet[] getColumns(
            String catalog, String schemaPattern, String tableNamePattern,
            String columnNamePattern)
        throws SQLException 
    {
        ResultSet[] rss = new ResultSet[2]; 
        DatabaseMetaData dmd = getDMD();
        rss[0]= dmd.getColumns(catalog, schemaPattern, tableNamePattern,
            columnNamePattern);
        rss[1]= getColumnsODBC(catalog, schemaPattern, tableNamePattern,
                columnNamePattern);
        return rss;        
    }
    
    /**
     * Test getTableTypes()
     *
     * @throws java.sql.SQLException
     */
    public void testTableTypes() throws SQLException
    {
        DatabaseMetaData dmd = getDMD();
        
        ResultSet rs = dmd.getTableTypes();
        assertMetaDataResultSet(rs,
                new String[] {
                "TABLE_TYPE"
               },
               new int[] {
                Types.VARCHAR
               }
        , null
              );
        
        JDBC.assertFullResultSet(rs, new String[][]
          {
            {"SYNONYM"},{"SYSTEM TABLE"},{"TABLE"},{"VIEW"},               
          }, true);
        rs.close();
    }
    
    /**
     * Test getTypeInfo
     *
     * @throws java.lang.Exception
     */
    public void testGetTypeInfo() throws Exception
    {
        // Client returns BOOLEAN type from the engine as SMALLINT
        int BOOLEAN = Types.BOOLEAN;      
        
        String[] JDBC_COLUMN_NAMES = new String[] {
                "TYPE_NAME", "DATA_TYPE", "PRECISION", "LITERAL_PREFIX",
                "LITERAL_SUFFIX", "CREATE_PARAMS", "NULLABLE", "CASE_SENSITIVE",
                
                "SEARCHABLE", "UNSIGNED_ATTRIBUTE", "FIXED_PREC_SCALE",
                "AUTO_INCREMENT", "LOCAL_TYPE_NAME",
                
                "MINIMUM_SCALE", "MAXIMUM_SCALE",
                "SQL_DATA_TYPE", "SQL_DATETIME_SUB",
                
                "NUM_PREC_RADIX"          
              };
        
        int[] JDBC_COLUMN_TYPES = new int[] {
                Types.VARCHAR, Types.INTEGER, Types.INTEGER, Types.VARCHAR,
                Types.VARCHAR, Types.VARCHAR, Types.SMALLINT, BOOLEAN,
                
                Types.SMALLINT, BOOLEAN, BOOLEAN,
                BOOLEAN, Types.VARCHAR,
                
                Types.SMALLINT, Types.SMALLINT,
                Types.INTEGER, Types.INTEGER,
                
                Types.INTEGER
              };
        
        boolean[] JDBC_COLUMN_NULLABILITY = {
                false, false, true, true,
                true, true, false, false,
                false, true, false,
                true, true,
                true, true,
                true, true,
                true 
        };
        
        // DERBY-2307 Nullablity is wrong for column 1 (1-based)
        // Modify JDBC_COLUMN_NULLABILITY to reflect current reality
        JDBC_COLUMN_NULLABILITY[1 - 1] = true;
      
        ResultSet rs = getDMD().getTypeInfo();
        assertMetaDataResultSet(rs, JDBC_COLUMN_NAMES, JDBC_COLUMN_TYPES
        , JDBC_COLUMN_NULLABILITY
        );

	/*
	 Derby-2258 Removed 3 data types which are not supported by Derby
	 and added XML data type which is supported by Derby
	*/
        List<Integer> supportedTypes = new ArrayList<Integer>(Arrays.asList(
          Types.BIGINT, Types.BINARY, Types.BLOB, Types.BOOLEAN,
          Types.CHAR, Types.CLOB, Types.DATE,
          Types.DECIMAL, Types.DOUBLE, Types.FLOAT,
          Types.INTEGER, Types.LONGVARBINARY, Types.LONGVARCHAR,
          Types.NUMERIC, Types.REAL, Types.SMALLINT,
          Types.TIME, Types.TIMESTAMP,  Types.VARBINARY,
          Types.VARCHAR, JDBC.SQLXML, Types.JAVA_OBJECT
        ));

        Version dataVersion = getDataVersion(getConnection());
        boolean booleanSupported =
                dataVersion.compareTo(new Version(10, 7, 0, 0)) >= 0;
        
        // DERBY-4946: Boolean isn't supported if DB is soft-upgraded from
        // pre-10.7 version
        if (!booleanSupported) {
            supportedTypes.remove(Integer.valueOf(Types.BOOLEAN));
        }

        // Rows are returned from getTypeInfo in order of
        // "DATA_TYPE" (which is a constant from java.sql.Types)
        Collections.sort(supportedTypes);
        
        int offset = 0;
        while (rs.next()) {
            // TYPE_NAME (column 1)
            String typeName = rs.getString("TYPE_NAME");
            assertNotNull(typeName);
            
            // DATA_TYPE (column 2)
            int type = rs.getInt("DATA_TYPE");
            assertFalse(rs.wasNull());
            if (!supportedTypes.get(offset).equals(type))
            {
                fail("Unexpected type " + typeName);
            }
            else
            {
                offset++;
            }
            
            // PRECISION (column 3)
            int precision = -1;
            switch (type)
            {
            case Types.BOOLEAN:
                precision = 1;
                break;
            case Types.BINARY:
            case Types.CHAR:
                precision = 254;
                break;
            case Types.BLOB:
            case Types.CLOB:
                precision = Integer.MAX_VALUE;
                break;
            
            case Types.DATE:
                precision = 10;
                break;
            case Types.TIME:
                precision = 8;
                break;
            case Types.TIMESTAMP:
                precision = 29;
                break;
                                
            case Types.DECIMAL:
            case Types.NUMERIC:
                precision = 31;
                break;
            case Types.DOUBLE:
            case Types.FLOAT:
                precision = 52;
                break;
            case Types.REAL:
                precision = 23;
                break;
                
            case Types.BIGINT:
                precision = 19;
                break;              
            case Types.INTEGER:
                precision = 10;
                break;
            case Types.SMALLINT:
                precision = 5;
                break;
                
            case Types.LONGVARBINARY:
            case Types.LONGVARCHAR:
                precision = 32700;
                break;
                        
	    /*
	     Derby-2260 Correcting the precision value for VARCHAR FOR BIT DATA
	     Thus this test also now expects the correct value i.e. 32672
	     Also adding precision check for SQLXML data type
	    */
            case Types.VARBINARY:
                precision = 32672;
                break;

            case Types.VARCHAR:
                precision = 32672;
                break;
	    case Types.JAVA_OBJECT:
	    case JDBC.SQLXML:
		precision = 0;
		break;
            }
            assertEquals("PRECISION " + typeName,
                    precision, rs.getInt("PRECISION"));

            /*
              Precision value is null for XML and OBJECT data type
            */
            if ( (typeName.equals("XML" )) || (typeName.equals("OBJECT" )) )
            { assertTrue(rs.wasNull()); }
            else
            { assertFalse(rs.wasNull()); }

            
            // LITERAL_PREFIX (column 4)
            // LITERAL_SUFFIX (column 5)
            
            // CREATE_PARAMS (column 6)
            String createParams;
            switch (type)
            {
            case Types.CHAR:
            case Types.VARCHAR:
            case Types.BLOB:
            case Types.CLOB:
            case Types.BINARY:
            case Types.VARBINARY:
                createParams = "length";
                break;
                
            case Types.DECIMAL:
            case Types.NUMERIC:
                createParams = "precision,scale";
                break;
                
            case Types.FLOAT:
                createParams = "precision";
                break;
                
            default:
                createParams = null;
                break;
            }
            assertEquals("CREATE_PARAMS " + typeName,
                    createParams, rs.getString("CREATE_PARAMS"));
                     
            
            // NULLABLE (column 7) - all types are nullable in Derby
            assertEquals("NULLABLE " + typeName,
                    DatabaseMetaData.typeNullable, rs.getInt("NULLABLE"));
            assertFalse(rs.wasNull());
            
            // CASE_SENSITIVE (column 8)
            
            // SEARCHABLE (column 9) - most types searchable
            {
            int searchable;
            switch (type)
            {
	    /*
	     Derby-2259 Correcting the searchable value for 
	     LONGVARBINARY, LONGVARCHAR & BLOB data type
	     also adding SQLXML data type in the test.
	    */
            case Types.LONGVARBINARY:
                searchable = DatabaseMetaData.typePredNone;
                break;
            case Types.LONGVARCHAR:
                searchable = DatabaseMetaData.typePredChar;
                break;
                
            case Types.BLOB:
		searchable = DatabaseMetaData.typePredNone;
		break;
            case Types.CLOB:
		searchable = DatabaseMetaData.typePredChar;
                break;
            case Types.CHAR:
            case Types.VARCHAR:
                searchable = DatabaseMetaData.typeSearchable;
                break;
	    case JDBC.SQLXML:
		searchable = DatabaseMetaData.typePredNone;
		break;
            default:
                searchable = DatabaseMetaData.typePredBasic;
                break;  
            }
            assertEquals("SEARCHABLE " + typeName,
                    searchable, rs.getInt("SEARCHABLE"));
            }
            
            // UNSIGNED_ATTRIBUTE (column 10)
            //assertFalse("UNSIGNED_ATTRIBUTE " + typeName,
            //        rs.getBoolean("UNSIGNED_ATTRIBUTE"));
            
            
            // FIXED_PREC_SCALE (column 11)
            boolean fixedScale = type == Types.DECIMAL || type == Types.NUMERIC;
            assertEquals("FIXED_PREC_SCALE " + typeName,
                    fixedScale, rs.getBoolean("FIXED_PREC_SCALE"));
            assertFalse(rs.wasNull());
            
            // AUTO_INCREMENT (column 12)
            boolean autoIncrement;
            switch (type)
            {
            case Types.BIGINT:
            case Types.INTEGER:
            case Types.SMALLINT:
                autoIncrement = true;
                break;
            default:
                autoIncrement = false;
                break;
            }
            assertEquals("AUTO_INCREMENT " + typeName,
                    autoIncrement, rs.getBoolean("AUTO_INCREMENT"));
            
            // LOCAL_TYPE_NAME (column 13) always the same as TYPE_NAME
            assertEquals("LOCAL_TYPE_NAME " + typeName,
                    typeName, rs.getString("LOCAL_TYPE_NAME"));
            
            
            int maxScale;
            boolean hasScale = true;
            switch (type)
            {
        case Types.DECIMAL:
        case Types.NUMERIC:
            maxScale = 31; // Max Scale for Decimal & Numeric is 31: Derby-2262
            break;
            case Types.TIMESTAMP:
                maxScale = 9;
                break;
            case Types.SMALLINT:
            case Types.INTEGER:
            case Types.BIGINT:
            case Types.DATE:
            case Types.TIME:
                maxScale = 0;
                break;
            default:
                maxScale = 0;
                hasScale = false;
                break;
            }
            
            // MINIMUM_SCALE (column 14)
            assertEquals("MINIMUM_SCALE " + typeName,
                    0, rs.getInt("MINIMUM_SCALE"));
            assertEquals("MINIMUM_SCALE (wasNull) " + typeName,
                    !hasScale, rs.wasNull());
            
            // MAXIMUM_SCALE (column 15)
            assertEquals("MAXIMUM_SCALE " + typeName,
                    maxScale, rs.getInt("MAXIMUM_SCALE"));            
            assertEquals("MAXIMUM_SCALE (wasNull)" + typeName,
                    !hasScale, rs.wasNull());

            
            // SQL_DATA_TYPE (column 16) - Unused
            assertEquals("SQL_DATA_TYPE " + typeName,
                    0, rs.getInt("SQL_DATA_TYPE"));
            assertTrue(rs.wasNull());
            
            // SQL_DATETIME_SUB (column 17) - Unused
            assertEquals("SQL_DATETIME_SUB " + typeName,
                    0, rs.getInt("SQL_DATETIME_SUB"));
            assertTrue(rs.wasNull());

            // NUM_PREC_RADIX (column 18)
            
        }
        
        rs.close();
        
        // Now check the ODBC version:
        
        // ODBC column names & types differ from JDBC slightly.
        // ODBC has one more column.
        String[] ODBC_COLUMN_NAMES = new String[19];
        System.arraycopy(JDBC_COLUMN_NAMES, 0, ODBC_COLUMN_NAMES, 0,
                JDBC_COLUMN_NAMES.length);
        ODBC_COLUMN_NAMES[2] = "COLUMN_SIZE";
        ODBC_COLUMN_NAMES[11] = "AUTO_UNIQUE_VAL";
        ODBC_COLUMN_NAMES[18] = "INTERVAL_PRECISION";
        
        int[] ODBC_COLUMN_TYPES = new int[ODBC_COLUMN_NAMES.length];
        System.arraycopy(JDBC_COLUMN_TYPES, 0, ODBC_COLUMN_TYPES, 0,
                JDBC_COLUMN_TYPES.length);
        
        ODBC_COLUMN_TYPES[1] = Types.SMALLINT; // DATA_TYPE
        ODBC_COLUMN_TYPES[7] = Types.SMALLINT; // CASE_SENSITIVE
        ODBC_COLUMN_TYPES[9] = Types.SMALLINT; // UNSIGNED_ATTRIBUTE
        ODBC_COLUMN_TYPES[10] = Types.SMALLINT; // FIXED_PREC_SCALE
        ODBC_COLUMN_TYPES[11] = Types.SMALLINT; // AUTO_UNIQUE_VAL
        ODBC_COLUMN_TYPES[15] = Types.SMALLINT; // SQL_DATA_TYPE
        ODBC_COLUMN_TYPES[16] = Types.SMALLINT; // SQL_DATETIME_SUB
        ODBC_COLUMN_TYPES[18] = Types.SMALLINT; // INTERVAL_PRECISION
        
        // ODBC_COLUMN_NULLABILTY is the same as JDBC except for:
        // column 16 - SQL_DATA_TYPE is NULL in JDBC but a valid non-null value in ODBC
        // column 19 -  INTERVAL_PRECISION (extra column comapred to JDBC)
        boolean[] ODBC_COLUMN_NULLABILITY = {
                true, false, true, true,
                true, true, false, false,
                false, true, false,
                true, true,
                true, true,
                false, true,
                true,
                true 
        };

        CallableStatement cs = prepareCall(
                "CALL SYSIBM.SQLGETTYPEINFO (0, 'DATATYPE=''ODBC''')");
        
        cs.execute();
        ResultSet odbcrs = cs.getResultSet();
        assertNotNull(odbcrs);
        
        assertMetaDataResultSet(odbcrs, ODBC_COLUMN_NAMES, ODBC_COLUMN_TYPES,
        		ODBC_COLUMN_NULLABILITY);
        
        odbcrs.close();
        cs.close();

    }
    
    /*
     * Check the shape of the ResultSet from any getColumns call.
     */
    private void checkColumnsShape(ResultSet rs, int odbc) throws SQLException
    {
        int[] columnTypes = new int[] {
                Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
                Types.INTEGER, Types.VARCHAR, Types.INTEGER, Types.INTEGER,
                Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.VARCHAR,
                Types.VARCHAR, Types.INTEGER, Types.INTEGER, Types.INTEGER,
                Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
                Types.VARCHAR, Types.SMALLINT, Types.VARCHAR, Types.VARCHAR,
                Types.VARCHAR
                };
        boolean[]   nullability =
        {
            false,   false,  false,  false,
            true,  true,   true,  true,
            true,   true,   true,  false,
            true,   true,   true,   true,
            false,  false,  true,   true,
            true,   true,   false,  false,
            true
        };
        if (odbc == 1)
        {
            columnTypes[4] = Types.SMALLINT;
            columnTypes[8] = Types.SMALLINT;
            columnTypes[9] = Types.SMALLINT;
            columnTypes[10] = Types.SMALLINT;
            columnTypes[13] = Types.SMALLINT;
            columnTypes[14] = Types.SMALLINT;
        }
        assertMetaDataResultSet
            (
             rs,
             new String[]
             {
                 "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME",
                 "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH",
                 "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS",
                 "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH",
                 "ORDINAL_POSITION", "IS_NULLABLE", "SCOPE_CATALOG", "SCOPE_SCHEMA",
                 "SCOPE_TABLE", "SOURCE_DATA_TYPE", "IS_AUTOINCREMENT", "IS_GENERATEDCOLUMN",
                 "SCOPE_CATLOG"
             },
             columnTypes,
             nullability
             );          
    }

    /**
     * Check the shape of the ResultSet from any getTables call.
     * Note nullability of TABLE_CAT is not nullable for Derby
     * even though it doesn't support catalogs because the
     * SQL query returns a constant (empty string) for
     * a table's catalog.
     * @param rs
     * @throws java.sql.SQLException
     */
    private void checkTablesShape(ResultSet rs) throws SQLException
    {
        assertMetaDataResultSet(rs,
          new String[] {
          "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "TABLE_TYPE",
          "REMARKS", "TYPE_CAT", "TYPE_SCHEM", "TYPE_NAME",
          "SELF_REFERENCING_COL_NAME", "REF_GENERATION"
         },
         new int[] {
          Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
          Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
          Types.VARCHAR, Types.VARCHAR
         }
        , new boolean[] {
          false, false, false, true, // TABLE_SCHEM cannot be NULL in Derby
          false, true, true, true,
          true, true
        }
        );        
    }
    
    /**
     * Check the shape of the ResultSet from any getCatlogs call.
     * @param rs
     * @throws java.sql.SQLException
     */
    private void checkCatalogsShape(ResultSet rs) throws SQLException
    {
        assertMetaDataResultSet(rs,
          new String[] {
          "TABLE_CAT"
         },
         new int[] {
          Types.CHAR
         }
        , new boolean[] {false}
        );        
    }
    
    /**
     * Check the shape of the ResultSet from any
     * getVersionColumns call.
     * @param rs
     * @throws java.sql.SQLException
     */
    private static void checkVersionColumnsShape(ResultSet[] rs) throws SQLException
    {
        String [] columnNames = new String[] {
                "SCOPE", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME",
                "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "PSEUDO_COLUMN"
               };
        int[] columnTypes = new int[] {
                Types.SMALLINT, Types.VARCHAR, Types.INTEGER, Types.VARCHAR,
                Types.INTEGER, Types.INTEGER, Types.SMALLINT, Types.SMALLINT};
        assertMetaDataResultSet(rs[0], columnNames, columnTypes, null);
        columnTypes[2] = Types.SMALLINT;
        assertMetaDataResultSet(rs[1], columnNames, columnTypes, null);        
    }
    
    public static void assertMetaDataResultSet(ResultSet rs,
            String[] columnNames, int[] columnTypes,
            boolean[] nullability) throws SQLException
    {
        assertEquals(ResultSet.TYPE_FORWARD_ONLY, rs.getType());
        assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
        //assertNull(rs.getStatement());
        
        if (columnNames != null)
            JDBC.assertDatabaseMetaDataColumns(rs, 
                    nullability, columnTypes, columnNames);
    }
    
    /*
    ** Set of escaped functions.
    */
    
    /**
     * JDBC escaped numeric functions - JDBC 3.0 C.1
     * @throws SQLException
     */
    public void testNumericFunctions() throws SQLException
    {
        escapedFunctions(NUMERIC_FUNCTIONS,
                getDMD().getNumericFunctions());
    }
    /**
     * JDBC escaped string functions - JDBC 3.0 C.2
     * @throws SQLException
     */
    public void testStringFunctions() throws SQLException
    {
        escapedFunctions(STRING_FUNCTIONS,
                getDMD().getStringFunctions());
    }    
    /**
     * JDBC escaped date time functions - JDBC 3.0 C.3
     * @throws SQLException
     */
    public void testTimeDataFunctions() throws SQLException
    {
        escapedFunctions(TIMEDATE_FUNCTIONS,
                getDMD().getTimeDateFunctions());
    }    
    /**
     * JDBC escaped system functions - JDBC 3.0 C.4
     * @throws SQLException
     */
    public void testSystemFunctions() throws SQLException
    {
        escapedFunctions(SYSTEM_FUNCTIONS,
                getDMD().getSystemFunctions());
    }           
    
    /**
     * Check that the list of escaped functions provided by
     * the driver is a strict subet of the specified set,
     * the list does not contain duplicates, all the functions
     * listed can be executed and that if a function is not
     * in the list but is specified it cannot be executed.
     *
     * @param specList
     * @param metaDataList
     *
     * @throws java.sql.SQLException
     */
    private void escapedFunctions(String[][] specList, String metaDataList)
    throws SQLException
    {
        boolean[] seenFunction = new boolean[specList.length];
        
        StringTokenizer st = new StringTokenizer(metaDataList, ",");
        while (st.hasMoreTokens())
        {
            String function = st.nextToken();
            
            // find this function in the list
            boolean isSpecFunction = false;
            for (int f = 0; f < specList.length; f++)
            {
                String[] specDetails = specList[f];
                if (function.equals(specDetails[0]))
                {
                    // Matched spec.
                    if (seenFunction[f])
                        fail("Function in list twice: " + function);
                    seenFunction[f] = true;
                    isSpecFunction = true;
                    executeEscaped(specDetails);
                    break;
                }
            }
            
            if (!isSpecFunction)
            {
               fail("Non-JDBC spec function in list: " + function);
            }
        }
        
        // Now see if any speced functions are not in the metadata list
        for (int f = 0; f < specList.length; f++)
        {
            if (seenFunction[f])
                continue;
            String[] specDetails = specList[f];
            
            // bug DERBY-723 CHAR maps to wrong function
            if ("CHAR".equals(specDetails[0]))
                continue;
            try {
                executeEscaped(specDetails);
                fail("function works but not declared in list: " + specDetails[0]);
            } catch (SQLException e) {
                assertSQLState("42X01", e);
            }
        }
    }
    
    /**
     * Test we can execute a function listed as a supported
     * JDBC escaped function. We don't care about the actual
     * return value, that should be tested elsewhere in
     * the specific test of a function.
     *
     * @param specDetails
     *
     * @throws java.sql.SQLException
     */
    private void executeEscaped(String[] specDetails)
        throws SQLException
    {
        
        String sql = "VALUES { fn " + specDetails[0] + "(";
        
        for (int p = 0; p < specDetails.length - 1; p++)
        {
            if (p != 0)
                sql = sql + ", ";
            
            sql = sql + specDetails[p + 1];
        }
        
        sql = sql + ") }";       
        
        PreparedStatement ps = prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        JDBC.assertDrainResults(rs);
        rs.close();
        ps.close();
    }
    
    /**
     * Return a list of all valid supported datatypes as Strings
     * suitable for use in any SQL statement where a SQL type is
     * expected. For variable sized types the string will
     * have random valid length information. E.g. CHAR(37).
     * @param conn current connection
     * @return list of types
     * @throws java.sql.SQLException
     */
    public static List<String> getSQLTypes(Connection conn) throws SQLException
    {
        List<String> list = new ArrayList<String>();
        
        Random rand = new Random();
        
        ResultSet rs = conn.getMetaData().getTypeInfo();
        while (rs.next())
        {
            String typeName = rs.getString("TYPE_NAME");

            // skip the OBJECT type because this does not correspond to an
            // actual data type but merely flags the fact that the database
            // supports the creation of user defined types
            if ( "OBJECT".equals( typeName ) ) { continue; }
            
            String createParams = rs.getString("CREATE_PARAMS");
            
            if (createParams == null) {
                // Type name stands by itself.
                list.add(typeName);
                continue;
            }
            
            if (createParams.contains("length"))
            {
                int maxLength = rs.getInt("PRECISION");
                
                // nextInt returns a value between 0 and maxLength-1
                int length = rand.nextInt(maxLength) + 1;
                
                int paren = typeName.indexOf('(');
                if (paren == -1) {
                    list.add(typeName + "(" + length + ")");
                    
                } else {
                    StringBuilder sb = new StringBuilder();
                    sb.append(typeName.substring(0, paren+1));
                    sb.append(length);
                    sb.append(typeName.substring(paren+1));
                    list.add(sb.toString());
                }
                
                continue;
            }
            
            if (createParams.contains("scale"))
            {
                int maxPrecision = rs.getInt("PRECISION");
                StringBuilder sb = new StringBuilder();
                int precision = rand.nextInt(maxPrecision) + 1;
                sb.append(typeName);
                sb.append("(");
                sb.append(precision);
                // Most DECIMAL usage does have a scale
                // but randomly pick some that do not.
                if (rand.nextInt(100) < 95) {
                    sb.append(",");
                    sb.append(rand.nextInt(precision+1));
                }
                sb.append(")");
                list.add(sb.toString());
                continue;
            }
            
            if (createParams.contains("precision"))
            {
                list.add(typeName);
                continue;
            }
            
            fail("unknown how to generate valid type for " + typeName
                    + " CREATE_PARAMS=" + createParams);
        }
        
        return list;
    }
    
    /**
     * Given a valid SQL type return the corresponding
     * JDBC type identifier from java.sql.Types.
     * Will assert if the type is not known
     * (in future, currently just return Types.NULL).
     *
     * @param type
     * @return JDBC type identifier (java.sql.Types.*)
     */
    public static int getJDBCType(String type)
    {
        if ("SMALLINT".equals(type))
            return Types.SMALLINT;
        if ("INTEGER".equals(type) || "INT".equals(type))
            return Types.INTEGER;
        if ("BIGINT".equals(type))
            return Types.BIGINT;
        
        if (type.equals("FLOAT") || type.startsWith("FLOAT("))
            return Types.FLOAT;
        if (type.equals("REAL"))
            return Types.REAL;

        if ("DOUBLE".equals(type) || "DOUBLE PRECISION".equals(type))
            return Types.DOUBLE;
        
        if ("DATE".equals(type))
            return Types.DATE;
        if ("TIME".equals(type))
            return Types.TIME;
        if ("TIMESTAMP".equals(type))
            return Types.TIMESTAMP;
        
        if (type.equals("DECIMAL") || type.startsWith("DECIMAL("))
            return Types.DECIMAL;
        if (type.equals("NUMERIC") || type.startsWith("NUMERIC("))
            return Types.NUMERIC;
        
        if (type.endsWith("FOR BIT DATA")) {
           if (type.startsWith("CHAR"))
               return Types.BINARY;
           if (type.startsWith("CHARACTER"))
            return Types.BINARY; 
           
           if (type.startsWith("VARCHAR"))
               return Types.VARBINARY;
           if (type.startsWith("CHARACTER VARYING"))
               return Types.VARBINARY;
           if (type.startsWith("CHAR VARYING"))
               return Types.VARBINARY;           
        }
        
        if ("LONG VARCHAR".equals(type))
            return Types.LONGVARCHAR;
        if ("LONG VARCHAR FOR BIT DATA".equals(type))
            return Types.LONGVARBINARY;
        
        if (type.equals("CHAR") || type.startsWith("CHAR("))
            return Types.CHAR;
        if (type.equals("CHARACTER") || 
                type.startsWith("CHARACTER("))
            return Types.CHAR;
        
        if (type.equals("VARCHAR") || type.startsWith("VARCHAR("))
            return Types.VARCHAR;
        if (type.equals("CHARACTER VARYING") || 
                type.startsWith("CHARACTER VARYING("))
            return Types.VARCHAR;
        if (type.equals("CHAR VARYING") || 
                type.startsWith("CHAR VARYING("))
            return Types.VARCHAR;

        if (type.equals("BLOB") || type.startsWith("BLOB("))
            return Types.BLOB;
        if (type.equals("BINARY LARGE OBJECT") || 
                type.startsWith("BINARY LARGE OBJECT("))
            return Types.BLOB;
        
        if (type.equals("CLOB") || type.startsWith("CLOB("))
            return Types.CLOB;
        if (type.equals("CHARACTER LARGE OBJECT") || 
                type.startsWith("CHARACTER LARGE OBJECT("))
            return Types.CLOB;

        if ("XML".equals(type))
            return JDBC.SQLXML;
        
        if (type.equals("BOOLEAN"))
            return Types.BOOLEAN;
        
        fail("Unexpected SQL type: " + type);
        return Types.NULL;
    }
    
    /**
     * Given a valid SQL type return the corresponding
     * precision/length for this specific value
     * if the type is variable, e.g. CHAR(5) will
     * return 5, but LONG VARCHAR will return 0.
     *
     * @param jdbcType
     * @param type
     *
     * @return precision or length
     */
    public static int getPrecision(int jdbcType, String type)
    {
        switch (jdbcType)
        {
        case Types.CHAR:
        case Types.VARCHAR:
        case Types.CLOB:
        case Types.BINARY:
        case Types.VARBINARY:
        case Types.BLOB:
            int lp = type.indexOf('(');
            int rp = type.indexOf(')');
            int precision =
                Integer.parseInt(type.substring(lp+1, rp));
            return precision;

        default:
            return 0;
        }
    }
 
    /**
     * Execute and check the ODBC variant of getImported/Exported keys, which
     * uses the SQLFOREIGNKEYS system procedure to provide the same information
     * to ODBC clients.  Note that for "correctness" we just compare the results
     * to those of the equivalent JDBC calls; this fixture assumes that the
     * the JDBC calls return correct results (testing of the JDBC results occurs
     * elsewhere, see fixtures testGetXXportedKeys()
     *
     * @throws java.sql.SQLException
     * @throws java.io.IOException
     */
    public void testGetXXportedKeysODBC() throws SQLException, IOException
    {
        Statement st = createStatement();

        // Create some simple tables with primary/foreign keys.

        st.execute("create table pkt1 (i int not null, c char(1) not null)");
        st.execute("create table pkt2 (i int not null, c char(1) not null)");
        st.execute("create table pkt3 (i int not null, c char(1) not null)");

        st.execute("alter table pkt1 add constraint pk1 primary key (i)");
        st.execute("alter table pkt2 add constraint pk2 primary key (c)");
        st.execute("alter table pkt3 add constraint pk3 primary key (i, c)");

        st.execute("create table fkt1 (fi int, fc char(1), vc varchar(80))");
        st.execute("create table fkt2 (fi int, fc char(1), vc varchar(80))");

        st.execute("alter table fkt1 add constraint fk1 foreign key (fi) " +
            "references pkt1(i)");

        st.execute("alter table fkt1 add constraint fk2 foreign key (fc) " +
            "references pkt2(c)");

        st.execute("alter table fkt2 add constraint fk3 foreign key (fi, fc) " +
            "references pkt3(i, c)");

        /* Check for all arguments NULL; SQLFOREIGNKEYS allows this, though
         * there is no equivalent in JDBC.
         */
        checkODBCKeys(null, schema, null, null, null, null);

        /* Run equivalent of getImportedKeys(), getExportedKeys(),
         * and getCrossReference for each of the primary/foreign
         * key pairs.
         */

        checkODBCKeys(null, schema, null, null, null, "FKT1");
        checkODBCKeys(null, schema, "PKT1", null, null, null);
        checkODBCKeys(null, schema, "PKT1", null, null, "FKT1");

        checkODBCKeys(null, schema, null, null, null, "FKT2");
        checkODBCKeys(null, schema, "PKT2", null, null, null);
        checkODBCKeys(null, schema, "PKT2", null, null, "FKT2");

        checkODBCKeys(null, schema, null, null, null, "FKT3");
        checkODBCKeys(null, schema, "PKT3", null, null, null);
        checkODBCKeys(null, schema, "PKT3", null, null, "FKT3");

        // Reverse primary and foreign tables.

        checkODBCKeys(null, schema, "FKT1", null, null, null);
        checkODBCKeys(null, schema, null, null, null, "PKT3");
        checkODBCKeys(null, schema, "FKT1", null, null, "PKT1");
        checkODBCKeys(null, schema, "FKT2", null, null, "PKT2");
        checkODBCKeys(null, schema, "FKT3", null, null, "PKT3");

        // Mix-and-match primary key tables and foreign key tables.

        checkODBCKeys(null, schema, "PKT1", null, null, "FKT2");
        checkODBCKeys(null, schema, "PKT1", null, null, "FKT3");
        checkODBCKeys(null, schema, "PKT2", null, null, "FKT3");

        checkODBCKeys(null, schema, "FKT1", null, null, "PKT2");
        checkODBCKeys(null, schema, "FKT1", null, null, "PKT3");
        checkODBCKeys(null, schema, "FKT2", null, null, "PKT3");

        // Cleanup.

        st.execute("drop table fkt1");
        st.execute("drop table fkt2");
        st.execute("drop table pkt1");
        st.execute("drop table pkt2");
        st.execute("drop table pkt3");
        st.close();
    }

    /**
     * Execute a call to the ODBC system procedure "SQLFOREIGNKEYS"
     * and verify the results by comparing them with the results of
     * an equivalent JDBC call (if one exists).
     *
     * @param pCatalog
     * @param pSchema
     * @param pTable
     * @param fCatalog
     * @param fSchema
     * @param fTable
     *
     * @throws java.sql.SQLException
     * @throws java.io.IOException
     */

    private void checkODBCKeys(String pCatalog, String pSchema,
        String pTable, String fCatalog, String fSchema, String fTable)
        throws SQLException, IOException
    {
        /* To mimic the behavior of the issue which prompted this test
         * (DERBY-2758) we only send the "ODBC" option; we do *not*
         * explicitly send the "IMPORTEDKEY=1" nor "EXPORTEDKEY=1"
         * options, as DB2 Runtime Client does not send those, either.
         * This effectively means that the SQLFOREIGNKEYS function
         * will always be mapped to getCrossReference() internally.
         * Since that worked fine prior to 10.3, we need to preserve
         * that behavior if we want to maintain backward compatibility.
         */
        CallableStatement cs = prepareCall(
            "CALL SYSIBM.SQLFOREIGNKEYS(?, ?, ?, ?, ?, ?, " +
            "'DATATYPE=''ODBC''')");

        cs.setString(1, pCatalog);
        cs.setString(2, pSchema);
        cs.setString(3, pTable);
        cs.setString(4, fCatalog);
        cs.setString(5, fSchema);
        cs.setString(6, fTable);
        
        cs.execute();
        ResultSet odbcrs = cs.getResultSet();
        assertNotNull(odbcrs);
        
        /* Returned ResultSet will have the same shape as
         * DatabaseMetaData.getImportedKeys()
         */
        checkODBCKeysShape(odbcrs);
        
        /* Expect the contents of JDBC and ODBC metadata to be the same,
         * except if both pTable and cTable are null.  In that case
         * ODBC treats everything as a wildcard (and so effectively
         * returns all foreign key columns), while JDBC throws
         * an error.
         */

        ResultSet dmdrs = null;
        if ((pTable != null) && (fTable == null))
            dmdrs = getDMD().getExportedKeys(pCatalog, pSchema, pTable);
        else if ((pTable == null) && (fTable != null))
            dmdrs = getDMD().getImportedKeys(fCatalog, fSchema, fTable);
        else if (pTable != null)
        {
            dmdrs = getDMD().getCrossReference(
                pCatalog, pSchema, pTable, fCatalog, fSchema, fTable);
        }
        else
        {
            /* Must be the case of pTable and fTable both null.  Check
             * results for ODBC (one row for each foreign key column)
             * and assert error for JDBC.
             */

            JDBC.assertFullResultSet(odbcrs,
                new String [][] {
                    {"",schema,"PKT1","I","",schema,"FKT1","FI",
                        "1","3","3","FK1","PK1","7"},
                    {"",schema,"PKT2","C","",schema,"FKT1","FC",
                        "1","3","3","FK2","PK2","7"},
                    {"",schema,"PKT3","I","",schema,"FKT2","FI",
                        "1","3","3","FK3","PK3","7"},
                    {"",schema,"PKT3","C","",schema,"FKT2","FC",
                        "2","3","3","FK3","PK3","7"}
                });

            try {

                getDMD().getCrossReference(
                    pCatalog, pSchema, pTable, fCatalog, fSchema, fTable);

                fail("Expected error from call to DMD.getCrossReference() " +
                    "with NULL primary and foreign key tables.");

            } catch (SQLException se) {

                /* Looks like embedded and client have different (but similar)
                 * errors for this...
                 */
                assertSQLState(usingEmbedded() ? "XJ103" : "XJ110", se);

            }
            
        }
                
        /* If both pTable and fTable are null then dmdrs will be null, as
         * well.  So nothing to compare in that case.
         */
        if (dmdrs != null)
        {
            // Next call closes both results sets as a side effect.
            JDBC.assertSameContents(odbcrs, dmdrs);
        }
        
        cs.close();
    }

    /**
     * Check the shape of the ResultSet from a call to the ODBC function
     * SQLForeignKeys.
     *
     * @param rs
     * @throws java.sql.SQLException
     */
    private void checkODBCKeysShape(ResultSet rs) throws SQLException
    {
        assertMetaDataResultSet(rs,

            // ODBC and JDBC agree on column names and types.

            new String[] {
                "PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME", "PKCOLUMN_NAME",
                "FKTABLE_CAT", "FKTABLE_SCHEM", "FKTABLE_NAME", "FKCOLUMN_NAME",
                "KEY_SEQ", "UPDATE_RULE", "DELETE_RULE", "FK_NAME",
                "PK_NAME", "DEFERRABILITY"
            },

            new int[] {
                Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
                Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
                Types.SMALLINT, Types.SMALLINT, Types.SMALLINT, Types.VARCHAR,
                Types.VARCHAR, Types.SMALLINT
            },

            // Nullability comes from ODBC spec, not JDBC.

            /* DERBY-2797: Nullability of columns in ODBC's SQLForeignKey
             * result set is incorrect.  Un-comment the correct boolean array
             * when DERBY-2797 has been fixed.
             */

            // incorrect
            new boolean[] {
                false, false, false, false,
                false, false, false, false,
                true, true, true, false,
                false, true
            }

            // correct
            /* new boolean[] {
                true, true, false, false,
                true, true, false, false,
                false, true, true, true,
                true, true
            } */

        );        
    }
    
    /**
     * Test getBestRowIdentifier
     * @throws SQLException 
     */
    public void testGetBestRowIdentifier() throws SQLException
    {
        Statement st = createStatement();

        // First, create the test tables and indexes/keys
        // Create 5 tables which have only one best row identifier
        st.execute("create table brit1 (i int not null primary key, j int)");
        st.execute("create table brit2 (i int not null unique, j int)");
        // adding not null unique to j - otherwise brit2 & brit3 would be same.
        st.execute("create table brit3 (i int not null unique, " +
                "j int not null unique)");
        st.execute("create table brit4 (i int, j int)");
        st.execute("create unique index brit4i on brit4(i)");
        st.execute("create table brit5 (i int, j int)");
        // following have more than one best row identifier
        st.execute("create table brit6 (i int not null unique, " +
                "j int not null primary key)");
        // PK preferred to unique index
        st.execute("create table brit7 (i int not null, " +
                "j int not null primary key)");
        st.execute("create unique index brit7i on brit7(i)");
        // unique con preferred to unique index
        st.execute("create table brit8 (i int not null, " +
                "j int not null unique)");
        st.execute("create unique index brit8i on brit8(i)");
        // non-unique index just ignored
        st.execute("create table brit9 (i int, j int)");
        st.execute("create index brit9i on brit9(i)");
        // fewer cols unique con still ignored over primary key
        st.execute("create table brit10 " +
                "(i int unique not null , j int not null, primary key (i,j))");
        // fewer cols unique index still ignored over primary key
        st.execute("create table brit11 (i int not null, j int not null, "
                + "primary key (i,j))");
        st.execute("create unique index brit11i on brit11(i)");
        // fewer cols unique index still ignored over unique con
        st.execute("create table brit12 (i int not null, j int not null, "
                + "unique (i,j))");
        st.execute("create unique index brit12i on brit12(i)");
        st.execute("create table brit13 (i int not null, j int)");
        // fewest cols unique con is the one picked of several
        st.execute("create table brit14 (i int not null unique, j int not "
                + "null, k int, unique (i,j))");
        // fewest cols unique index is the one picked of several
        st.execute("create table brit15 (i int not null, j int not null, k int)");
        st.execute("create unique index brit15ij on brit15(i,j)");
        st.execute("create unique index brit15i on brit15(i)");
        st.execute("create table brit16 (i int not null primary key, j int)");
        // from old metadata test
        // DERBY-3180; if this table gets created here, running the entire test
        // twice with defaultSuite runs into into trouble.
        // Moving into separate fixture does not have this problem.
        st.execute("create table brit17 (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 brit17
        st.execute("create unique index brit17i on brit17(s, i)");
        // Create a non-unique index on brit17
        st.execute("create index brit17ij on brit17(s)");

        getConnection().setAutoCommit(false);
        
        // except for the last table, the expected results are
        // column i, column j, or columns i and j.
        String [][] expRSI = {
                {"2", "I", "4", "INTEGER", "4", null, "10", "1"}};
        String [][] expRSJ = {
                {"2", "J", "4", "INTEGER", "4", null, "10", "1"}};
        String [][] expRSIJ = {
                {"2", "I", "4", "INTEGER", "4", null, "10", "1"},
                {"2", "J", "4", "INTEGER", "4", null, "10", "1"}};
        
        // result: column i
        ResultSet[] rs = getBestRowIdentifier(null,schema,"BRIT1",
        		DatabaseMetaData.bestRowTemporary, true);
        verifyBRIResults(rs, expRSI);

        // result: column i
        rs = getBestRowIdentifier(null,schema,"BRIT2",
        		DatabaseMetaData.bestRowTemporary, true);
        verifyBRIResults(rs, expRSI);

        // result: column i (j is equally good but its index descriptor
        // sorts after i: DERBY-6623)
        rs = getBestRowIdentifier(null,schema,"BRIT3",
        		DatabaseMetaData.bestRowTemporary,true);
        verifyBRIResults(rs, expRSI);
        // result: column i
        rs = getBestRowIdentifier(null,schema,"BRIT4",
        		DatabaseMetaData.bestRowTemporary,true);
        verifyBRIResults(rs, expRSI);
        
        // result: columns i and j
        rs = getBestRowIdentifier(null,schema,"BRIT5",
        		DatabaseMetaData.bestRowTemporary,true);
        verifyBRIResults(rs, expRSIJ);

        // result: column j
        rs = getBestRowIdentifier(null,schema,"BRIT6",
        		DatabaseMetaData.bestRowTemporary,true);
        verifyBRIResults(rs, expRSJ);

        // result: column j
        rs = getBestRowIdentifier(null,schema,"BRIT7",
        		DatabaseMetaData.bestRowTemporary,true);
        verifyBRIResults(rs, expRSJ);
        
        // result: column j
        rs = getBestRowIdentifier(null,schema,"BRIT8",
        		DatabaseMetaData.bestRowTemporary,true);
        verifyBRIResults(rs, expRSJ);
        
        // result: columns i,j
        rs = getBestRowIdentifier(null,schema,"BRIT9",
        		DatabaseMetaData.bestRowTemporary,true);
        verifyBRIResults(rs, expRSIJ);
        
        // result: columns i,j
        rs = getBestRowIdentifier(null,schema,"BRIT10",
        		DatabaseMetaData.bestRowTemporary,true);
        verifyBRIResults(rs, expRSIJ);
        
        // result: columns i,j
        rs = getBestRowIdentifier(null,schema,"BRIT11",
        		DatabaseMetaData.bestRowTemporary,true);
        verifyBRIResults(rs, expRSIJ);
        
        // result: columns i,j
        rs = getBestRowIdentifier(null,schema,"BRIT12",
        		DatabaseMetaData.bestRowTemporary,true);
        verifyBRIResults(rs, expRSIJ);
        
        // Verify nullOK flags makes a difference. See also DERBY-3182
        // result: column i, should've ignored null column
        rs = getBestRowIdentifier(null,schema,"BRIT13",
        		DatabaseMetaData.bestRowTemporary,false);
        verifyBRIResults(rs, expRSI);
        // result: columns i, j
        rs = getBestRowIdentifier(null,schema,"BRIT13",
        		DatabaseMetaData.bestRowTemporary,true);
        verifyBRIResults(rs, expRSIJ);
        
        // result: columns i
        rs = getBestRowIdentifier(null,schema,"BRIT14",
        		DatabaseMetaData.bestRowTemporary,true);
        verifyBRIResults(rs, expRSI);
        
        // result: columns i
        rs = getBestRowIdentifier(null,schema,"BRIT15",
        		DatabaseMetaData.bestRowTemporary,true);
        verifyBRIResults(rs, expRSI);
        
        // we don't do anything with SCOPE except detect bad values
        // result: columns i
        rs = getBestRowIdentifier(null,schema,"BRIT16",
        		DatabaseMetaData.bestRowTransaction,true);
        verifyBRIResults(rs, expRSI);
        // result: columns i
        rs = getBestRowIdentifier(null,schema,"BRIT16",
        		DatabaseMetaData.bestRowSession,true);
        verifyBRIResults(rs, expRSI);
        // result: exception (invalid scope -1)
        try {
            rs = getBestRowIdentifier(null,schema,"BRIT16",-1,true);
        } catch (SQLException sqle) {
            assertSQLState( "42XAT", sqle);
        }
        
        // result: exception (invalid scope 3)
        try {
            rs = getBestRowIdentifier(null,schema,"BRIT16",3,true);
        } catch (SQLException sqle) {
            assertSQLState( "42XAT", sqle);
        }
        
        rs = getBestRowIdentifier(null, schema,"BRIT17",
        		DatabaseMetaData.bestRowTemporary,true);
        String [][] expRS = new String [][] {
                {"2", "I", "4", "INTEGER", "4", null, "10", "1"},
                {"2", "VC10", "12", "VARCHAR", "10", null, null, "1"}
        };
        JDBC.assertUnorderedResultSet(rs[0], expRS, true);
        // set buffer_length expected for ODBC; for most of the simple 
        // tables/rows in our test it's "4" so set in verifyBRIResults
        expRS[0][5] = "4";
        expRS[1][5] = "20";
        JDBC.assertUnorderedResultSet(rs[1], expRS, true);
        
        // test DERBY-2610 for fun; can't pass in null table name      
        try {
            getBestRowIdentifier(null,schema,null,
            		DatabaseMetaData.bestRowTemporary,true);
        } catch (SQLException sqle) {
            assertSQLState( "XJ103", sqle);
        }
        
        // check on systables
        rs = getBestRowIdentifier(null,"SYS","SYSTABLES",
        		DatabaseMetaData.bestRowTemporary,true);
        expRS = new String [][] {
                {"2", "TABLEID", "1", "CHAR", "36", null, null, "1"}
        };
        JDBC.assertUnorderedResultSet(rs[0], expRS, true);
        // set buffer_length expected for ODBC
        expRS[0][5] = "72";
        JDBC.assertUnorderedResultSet(rs[1], expRS, true);
        
        getConnection().setAutoCommit(true);
        
        st.execute("drop table brit1");
        st.execute("drop table brit2");
        st.execute("drop table brit3");
        st.execute("drop index brit4i");
        st.execute("drop table brit4");
        st.execute("drop table brit5");
        st.execute("drop table brit6");
        st.execute("drop index brit7i");
        st.execute("drop table brit7");
        st.execute("drop index brit8i");
        st.execute("drop table brit8");
        st.execute("drop index brit9i");
        st.execute("drop table brit9");
        st.execute("drop table brit10");
        st.execute("drop index brit11i");
        st.execute("drop table brit11");
        st.execute("drop index brit12i");
        st.execute("drop table brit12");
        st.execute("drop table brit13");
        st.execute("drop table brit14");
        st.execute("drop index brit15i");
        st.execute("drop index brit15ij");
        st.execute("drop table brit15");
        st.execute("drop table brit16");
        st.execute("drop index brit17i");
        st.execute("drop index brit17ij");
        st.execute("drop table brit17");

        st.close();
    }
    
    /**
     * Helper method for testing getBestRowIdentifier - calls the ODBC procedure
     *
     * @param catalog
     * @param schema
     * @param table
     * @param scope
     * @param nullable
     * @return the result set of the query
     *
     * @throws SQLException 
     */
    private ResultSet getBestRowIdentifierODBC(String catalog, String schema, 
        String table, int scope, boolean nullable) throws SQLException 
    {
        CallableStatement cs = prepareCall(
            "CALL SYSIBM.SQLSPECIALCOLUMNS(1, ?, ?, ?, ?, ?, " +
        "'DATATYPE=''ODBC''')");
        cs.setString(1, catalog);
        cs.setString(2, schema);
        cs.setString(3, table);
        cs.setInt(4, scope);
        cs.setBoolean(5, nullable);

        cs.execute();
        return cs.getResultSet();
    }
    
    /**
     * Helper method for testing getBestRowIdentifier - calls 
     * dmd.getBestRowIdentifier for the JDBC call, and getBestRowIdentifierODBC
     *
     * for the ODBC procedure
     * @param catalog
     * @param schema
     * @param table
     * @param scope
     * @param nullable
     * @return array of two result sets corresponding to JDBC and ODBC
     *
     * @throws SQLException 
     */
    private ResultSet[] getBestRowIdentifier(String catalog, String schema, String table,
        int scope, boolean nullable) throws SQLException 
    {
        ResultSet[] rss = new ResultSet[2]; 
        DatabaseMetaData dmd = getDMD();
        rss[0]= dmd.getBestRowIdentifier(catalog, schema, table, scope, nullable);
        rss[1]= getBestRowIdentifierODBC(catalog, schema, table, scope, nullable);
        
        String[] columnNames = {
                "SCOPE", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", 
                "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", 
                "PSEUDO_COLUMN"};
        int[] columnTypes = {
                Types.SMALLINT, Types.VARCHAR, Types.INTEGER, Types.VARCHAR,
                Types.INTEGER, Types.INTEGER, Types.SMALLINT, Types.SMALLINT};
        int[] odbcColumnTypes = {
                Types.SMALLINT, Types.VARCHAR, Types.SMALLINT, Types.VARCHAR,
                Types.INTEGER, Types.INTEGER, Types.SMALLINT, Types.SMALLINT};
        boolean [] nullability = {
                true, false, true, true, true, true, true, true};
        
        // column nullability is opposite to with scope=1 or 2...DERBY-3181
        // When running the ODBC version, the datatype returned for
        // column 3 is SMALLINT, vs. INTEGER when scope=1 or 2...
        // So, in this case, the columnTypes are the same for ODBC and JDBC,
        // but with calls with a valid scope, they are different.

        if (scope != DatabaseMetaData.bestRowTemporary &&
        		scope != DatabaseMetaData.bestRowTransaction &&
        		scope != DatabaseMetaData.bestRowSession)
        {
        	nullability = new boolean [] {
                    false, false, false, false, false, false, false, false};
        	
        	odbcColumnTypes = columnTypes;
        }
       
        assertMetaDataResultSet(rss[0], columnNames, columnTypes, nullability);
        assertMetaDataResultSet(rss[1], columnNames, odbcColumnTypes, nullability);
              
        return rss;        
    }

    /**
     * helper method for test testGetBestRowIdentifier
     * @param rss - ResultSet array from getBestRowIdentifier;
     *     rss[0] will have the JDBC result, rss[1] the ODBC result
     * @param expRS - bi-dimensional String array with expected result row(s)
     * @throws SQLException 
     */
    public void verifyBRIResults(ResultSet[] rss, String[][] expRS) throws SQLException {      
        JDBC.assertFullResultSet(rss[0], expRS, true);
        for (String[] expRS1 : expRS) {
            expRS1[5] = "4";
        }
        JDBC.assertFullResultSet(rss[1], expRS, true);
        for (String[] expRS1 : expRS) {
            expRS1[5] = null;
        }
    }
    
    /**
     * Test getGetColumnPrivileges; does not modify database
     * For further testing see test lang.grantRevokeTest
     * @throws SQLException 
     */
    public void testGetColumnPrivileges() throws SQLException
    {       
        // unlike for instance getTables() and getUDTs trying to call
        // getColumnPrivileges with all nulls gets stopped because 
        // the spec indicates it takes a table name, not just a pattern
        try {
            getColumnPrivileges(null,null,null,null);
            fail ("expected error XJ103");
        } catch (SQLException sqle) {
            assertSQLState("XJ103", sqle);
        }
        
        ResultSet[] rs = getColumnPrivileges(null,null,"",null);
        JDBC.assertEmpty(rs[0]);
        JDBC.assertEmpty(rs[1]);
        
        rs = getColumnPrivileges("","","","");
        JDBC.assertEmpty(rs[0]);
        JDBC.assertEmpty(rs[1]);
        
        rs = getColumnPrivileges("%","%","%","%");
        JDBC.assertEmpty(rs[0]);
        JDBC.assertEmpty(rs[1]);

        // we didn't (can't) grant any privileges to the systabels, so no row
        rs = getColumnPrivileges(null,"SYS","SYSTABLES","TABLEID");
        JDBC.assertEmpty(rs[0]);
        JDBC.assertEmpty(rs[1]);

    }

    /**
     * Helper method for testing getColumnPrivileges - calls the ODBC procedure
     * @param catalog
     * @param schema
     * @param table
     * @param columnNamePattern
     * @return the result set of the query
     * @throws SQLException 
     */
    private ResultSet getColumnPrivilegesODBC(String catalog, String schema, 
        String table, String columnNamePattern) throws SQLException 
    {
        CallableStatement cs = prepareCall(
            "CALL SYSIBM.SQLCOLPRIVILEGES(?, ?, ?, ?, 'DATATYPE=''ODBC''')");
        
        cs.setString(1, catalog);
        cs.setString(2, schema);
        cs.setString(3, table);
        cs.setString(4, columnNamePattern);

        cs.execute();
        return cs.getResultSet();
    }
    
    /**
     * Helper method for testing getColumnPrivileges - calls dmd.getIndexInfo for the 
     * JDBC call, and getColumnPrivilegesODBC for the ODBC procedure
     *
     * @param catalog
     * @param schema
     * @param table
     * @param columnNamePattern
     * @return array of two result sets corresponding to JDBC and ODBC
     *
     * @throws SQLException 
     */
    private ResultSet[] getColumnPrivileges(String catalog, String schema, String table,
        String columnNamePattern) throws SQLException 
    {
        ResultSet[] rss = new ResultSet[2]; 
        DatabaseMetaData dmd = getDMD();
        rss[0]= dmd.getColumnPrivileges(catalog, schema, table, columnNamePattern);
        rss[1]= getColumnPrivilegesODBC(catalog, schema, table, columnNamePattern);
        
        String [] columnNames = {"TABLE_CAT","TABLE_SCHEM","TABLE_NAME",
                "COLUMN_NAME","GRANTOR","GRANTEE","PRIVILEGE","IS_GRANTABLE"};
        int [] columnTypes = {
                Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,
                Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR};
        boolean [] nullability = {false,false,false,false,false,false,false,false};

        assertMetaDataResultSet(rss[0], columnNames, columnTypes, nullability);
        assertMetaDataResultSet(rss[1], columnNames, columnTypes, nullability);
                       
        return rss;        
    }
    
    /**
     * Test getGetTablePrivileges; does not modify database
     * For further testing see test lang.grantRevokeTest
     * @throws SQLException 
     */
    public void testGetTablePrivileges() throws SQLException
    {
        ResultSet rs[] = getTablePrivileges(null,null,null);
        JDBC.assertEmpty(rs[0]);
        JDBC.assertEmpty(rs[1]);
        
        rs = getTablePrivileges("","","");
       JDBC.assertEmpty(rs[0]);
        JDBC.assertEmpty(rs[1]);
        
        rs = getTablePrivileges("%","%","%");
         JDBC.assertEmpty(rs[0]);
        JDBC.assertEmpty(rs[1]);

        // we didn't (can't) grant any privileges to the systabels, so no row
        rs = getTablePrivileges(null,"SYS","SYSTABLES");
        JDBC.assertEmpty(rs[0]);
        JDBC.assertEmpty(rs[1]);
    }
    
    /**
     * Helper method for testing getTablePrivileges - calls the ODBC procedure
     *
     * @param catalog
     * @param schema
     * @param tableNamePattern
     * @return the result set of the query
     *
     * @throws SQLException 
     */
    private ResultSet getTablePrivilegesODBC(String catalog, String schema, 
        String tableNamePattern) throws SQLException 
    {
        CallableStatement cs = prepareCall(
            "CALL SYSIBM.SQLTABLEPRIVILEGES(?, ?, ?, 'DATATYPE=''ODBC''')");
        
        cs.setString(1, catalog);
        cs.setString(2, schema);
        cs.setString(3, tableNamePattern);
        cs.execute();
        return cs.getResultSet();
    }
    
    /**
     * Helper method for testing getTablePrivileges - calls dmd.getIndexInfo for the 
     * JDBC call, and getTablePrivilegesODBC for the ODBC procedure
     *
     * @param catalog
     * @param schema
     * @param tableNamePattern
     * @return array of two result sets corresponding to JDBC and ODBC
     *
     * @throws SQLException 
     */
    private ResultSet[] getTablePrivileges(
        String catalog, String schema, String tableNamePattern) 
    throws SQLException 
    {
        ResultSet[] rss = new ResultSet[2]; 
        DatabaseMetaData dmd = getDMD();
        rss[0]= dmd.getTablePrivileges(catalog, schema, tableNamePattern);
        rss[1]= getTablePrivilegesODBC(catalog, schema, tableNamePattern);
        
        String [] columnNames = {"TABLE_CAT","TABLE_SCHEM","TABLE_NAME",
                "GRANTOR","GRANTEE","PRIVILEGE","IS_GRANTABLE"};
        int [] columnTypes = {
                Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,
                Types.VARCHAR,Types.VARCHAR,Types.VARCHAR};
        boolean [] nullability = {false,false,false,false,false,false,false};
        
        assertMetaDataResultSet(rss[0], columnNames, columnTypes, nullability);
        assertMetaDataResultSet(rss[1], columnNames, columnTypes, nullability);
            
        return rss;        
    }

    /**
     * Test getIndexInfo; does not modify database
     * @throws SQLException 
     */
    public void testGetIndexInfo() throws SQLException
    {
        
        // unlike for instance getTables() and getUDTs trying to call
        // getIndexInfo with all nulls gets stopped because 
        // the spec indicates it takes a table name, not just a pattern
        try {
            getIndexInfo(null,null,null,true,true);
            fail ("expected error XJ103");
        } catch (SQLException sqle) {
            assertSQLState("XJ103", sqle);
        }
        
        // do a call which selects unique indexes only
        ResultSet rss[] = getIndexInfo("","SYS","SYSCOLUMNS",true,false);
        String[][] expRS = {
            {"","SYS","SYSCOLUMNS","false","","SYSCOLUMNS_INDEX1","3","1",
                "REFERENCEID","A",null,null,null},
            {"","SYS","SYSCOLUMNS","false","","SYSCOLUMNS_INDEX1","3","2",
                "COLUMNNAME","A",null,null,null}};
        assertFullResultSet(rss, expRS, true);
        
        // same table, but select all indexes (unique=false)
        // note, that true for approximate does nothing in Derby
        rss = getIndexInfo("","SYS","SYSCOLUMNS",false,false);

        expRS = new String[][] {
            {"","SYS","SYSCOLUMNS","false","","SYSCOLUMNS_INDEX1","3","1",
                "REFERENCEID","A",null,null,null},
            {"","SYS","SYSCOLUMNS","false","","SYSCOLUMNS_INDEX1","3","2",
                "COLUMNNAME","A",null,null,null},
            {"","SYS","SYSCOLUMNS","true","","SYSCOLUMNS_INDEX2","3","1",
                 "COLUMNDEFAULTID","A",null,null,null}};
        assertFullResultSet(rss, expRS, true);
        
        rss = getIndexInfo("","SYS","SYSTABLES",true,false);

        expRS = new String[][] {
            {"","SYS","SYSTABLES","false","","SYSTABLES_INDEX1","3","1",
                "TABLENAME","A",null,null,null},
            {"","SYS","SYSTABLES","false","","SYSTABLES_INDEX1","3","2",
                "SCHEMAID","A",null,null,null},
            {"","SYS","SYSTABLES","false","","SYSTABLES_INDEX2","3","1",
                "TABLEID","A",null,null,null}};
        assertFullResultSet(rss, expRS, true);
        
        // should return no rows
        rss = getIndexInfo("","SYS","SYSSTABLES",true,false);

        JDBC.assertEmpty(rss[0]);
        JDBC.assertEmpty(rss[1]);

    }
    
    /**
     * Test getIndexInfo further; does modify database
     * @throws SQLException 
     */
    public void testMoreGetIndexInfo() throws SQLException
    {
        // test to see that we are correctly returning D for ASC_OR_DESC.
        // As Derby only supports tableIndexHashed Type, and 
        // CARDINALITY, PAGES, nor FILTER_CONDITION get set, no further
        // tests seem necessary.
        Statement st = createStatement();

        // First, create the test table and indexes/keys
        st.execute("create table iit (i int not null, j int)");
        st.execute("create unique index iii on iit(i asc, j desc)");
        DatabaseMetaData dmd = getDMD();
        ResultSet rs = dmd.getIndexInfo("",schema,"IIT",false,false);
        boolean more = rs.next();

        if (more) {
            assertEquals("A",rs.getString(10));
        }

        more = rs.next();

        if (more) {
            assertEquals("D",rs.getString(10));
        }

        rs = getIndexInfoODBC("",schema,"IIT",false,false);
        more = rs.next();

        if (more) {
            assertEquals("A",rs.getString(10));
        }

        more = rs.next();

        if (more) {
            assertEquals("D",rs.getString(10));
        }

        st.execute("drop index iii");
        st.execute("drop table iit");

        st.close();
    }

    /**
     * Helper method for testing getIndexInfo - calls the ODBC procedure
     *
     * @param catalog
     * @param schema
     * @param table
     * @param approximate
     * @param unique
     * @return the result set of the query
     *
     * @throws SQLException 
     */
    private ResultSet getIndexInfoODBC(String catalog, String schema, 
        String table, boolean unique, boolean approximate) throws SQLException 
    {
        CallableStatement cs = prepareCall(
                "CALL SYSIBM.SQLSTATISTICS(?, ?, ?, ?, ?, " +
                "'DATATYPE=''ODBC''')");

        cs.setString(1, catalog);
        cs.setString(2, schema);
        cs.setString(3, table);
        // the unique parameter needs to be flopped...See the call
        // in SystemProcedures.
        cs.setBoolean(4, unique? false : true);
        cs.setBoolean(5, approximate);

        cs.execute();
        return cs.getResultSet();
    }
    
    /**
     * Helper method for testing getIndexInfo - calls dmd.getIndexInfo for the 
     * JDBC call, and getIndexInfoODBC for the ODBC procedure
     * @param catalog
     * @param schema
     * @param table
     * @param unique
     * @param approximate
     * @return an array of two result sets corresponding to JDBC and ODBC
     *         respectively
     * @throws SQLException 
     */
    private ResultSet[] getIndexInfo(String catalog, String schema, String table,
        boolean unique, boolean approximate) throws SQLException 
    {
        ResultSet[] rss = new ResultSet[2]; 
        DatabaseMetaData dmd = getDMD();
        rss[0]= dmd.getIndexInfo(catalog, schema, table, unique, approximate);
        rss[1]= getIndexInfoODBC(catalog, schema, table, unique, approximate);
        
        String [] columnNames = {"TABLE_CAT","TABLE_SCHEM","TABLE_NAME",
                "NON_UNIQUE","INDEX_QUALIFIER","INDEX_NAME","TYPE",
                "ORDINAL_POSITION","COLUMN_NAME","ASC_OR_DESC","CARDINALITY",
                "PAGES","FILTER_CONDITION"};
        int [] columnTypes = {
                Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,
                Types.BOOLEAN,Types.VARCHAR,Types.VARCHAR,Types.SMALLINT,
                // ASC_OR_DESC is Types.CHAR rather than VARCHAR...
                Types.SMALLINT,Types.VARCHAR,Types.CHAR,Types.BIGINT,
                Types.BIGINT,Types.VARCHAR};
        
        boolean [] nullability = {false,false,false,
            false,false,true,true,true,false,false,true,true,true};
        
        // JDBC result set
        assertMetaDataResultSet(rss[0], columnNames, columnTypes, nullability);
        
        // Change shape for ODBC.
        columnTypes[4 - 1] = Types.SMALLINT; // types.boolean is not supported with ODBC

        // ODBC result set
        assertMetaDataResultSet(rss[1], columnNames, columnTypes, nullability);
       
        return rss;        
    }
    
    /**
     * Helper method - unravels a ResultSet array created e.g.
     * with this.getIndexInfo, i.e. Resultset[0] has the JDBC resultset
     * and ResultSet[1] the ODBC resultset
     *
     * @param rs
     * @param expRS
     * @param trim
     *
     * @throws SQLException 
     */
    private void assertFullResultSet(
        ResultSet rs[], String[][] expRS, boolean trim) throws SQLException
    {
        JDBC.assertFullResultSet(rs[0], expRS, trim);
        JDBC.assertFullResultSet(rs[1], expRS, trim);
    }

    /**
     * Helper method - unravels a ResultSet array created e.g.
     * with this.getIndexInfo, i.e. Resultset[0] has the JDBC resultset
     * and ResultSet[1] the ODBC resultset
     *
     * @param rs
     * @param expRS
     * @param trim
     *
     * @throws SQLException
     */
    private void assertFullUnorderedResultSet(
        ResultSet rs[], String[][] expRS, boolean trim) throws SQLException
    {
        JDBC.assertUnorderedResultSet(rs[0], expRS, trim);
        JDBC.assertUnorderedResultSet(rs[1], expRS, trim);
    }

    /**
     * Create the tables for get*Keys tests
     * @throws SQLException 
     */
    private void createObjectsForKeysTests() throws SQLException
    {
        getConnection().setAutoCommit(false);
        Statement s = createStatement();
        s.execute("create table kt1 (" +
                "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 kt1
        s.execute("create unique index u1 on kt1(s, i)");
        // Create a non-unique index on kt1
        s.execute("create index u2 on kt1(s)");
        // Create a view on key table 1
        s.execute("create view kv as select * from kt1");

        // 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 kt1, " + 
                  "constraint FKEY2 " +
                      "foreign key(c30, s2) references kt1 (c30, s), "+
                  "constraint FKEY3 " +
                      "foreign key(c30, s) references kt1 (c30, s))");

        s.execute("create table reftab2 (t2_vc10 varchar(10), t2_i int, " +
                  "constraint T2_FKEY1 " +
                      "foreign key(t2_vc10, t2_i) references kt1)");
        commit();
        getConnection().setAutoCommit(true);
    }
    
    /**
     * Drop the database objects for get*Keys tests
     * @throws SQLException 
     */
    private void dropObjectsForKeysTests() throws SQLException
    {
        getConnection().setAutoCommit(false);
        Statement s = createStatement();
        s.execute("drop table reftab2");
        s.execute("drop table reftab");
        commit();
        s.execute("drop view kv");
        s.execute("drop index u2");
        s.execute("drop index u1");
        s.execute("drop table kt1");
        commit();
        getConnection().setAutoCommit(true);        
    }

    /**
     * Test getPrimaryKeys; does modify database
     * @throws SQLException 
     */
    public void testGetPrimaryKeys() throws SQLException
    {
        String[][] expRS = new String[][] {
                {"",schema,"KT1","I","2","PRIMKEY"},
                {"",schema,"KT1","VC10","1","PRIMKEY"}};
                       
        createObjectsForKeysTests();
        
        // try with valid search criteria
        // although, % may not actually be appropriate?
        ResultSet rs[] = getPrimaryKeys("", "%", "KT1");
        assertFullResultSet(rs, expRS, true);
        
        rs = getPrimaryKeys(null, schema, "KT1");
        assertFullResultSet(rs, expRS, true);

        rs = getPrimaryKeys(null, null, "KT1");
        assertFullResultSet(rs, expRS, true);

        rs = getPrimaryKeys(null, "", "KT1");
        JDBC.assertEmpty(rs[0]);
        JDBC.assertEmpty(rs[1]);

        // tablename may not be null
        DatabaseMetaData dmd = getDMD();
        try {
            rs[0] = dmd.getPrimaryKeys(null, null, null);
            fail ("table name may not be null, should've given error");
        } catch (SQLException sqle) {
            assertSQLState("XJ103", sqle);
        }
        try {
            rs[1] = getPrimaryKeysODBC(null, null, null);
            fail ("table name may not be null, should've given error");
        } catch (SQLException sqle) {
            assertSQLState("XJ103", sqle);
        }
        
        // DERBY-2610, tablename must be given as stored - % means no rows
        rs = getPrimaryKeys(null, null, "%");
        JDBC.assertEmpty(rs[0]);
        JDBC.assertEmpty(rs[1]);
        
        dropObjectsForKeysTests();
    }

    /**
     * Helper method for testing getPrimaryKeys - calls the ODBC procedure
     *
     * @param catalog
     * @param schema
     * @param table
     * @return result set of the query
     * @throws SQLException 
     */
    private ResultSet getPrimaryKeysODBC(
            String catalog, String schema, String table) throws SQLException 
    {
        CallableStatement cs = prepareCall(
                "CALL SYSIBM.SQLPRIMARYKEYS(?, ?, ?, 'DATATYPE=''ODBC''')");
        cs.setString(1, catalog);
        cs.setString(2, schema);
        cs.setString(3, table);
        cs.execute();
        return cs.getResultSet();
    }
    
    /**
     * Helper method for testing getPrimaryKeys - calls dmd.getPrimaryKeys for
     * the JDBC call, and getPrimaryKeysODBC for the ODBC procedure
     * @param catalog
     * @param schema
     * @param table
     * @return an array of two result sets corresponding to JDBC and ODBC
     *         respectively
     * @throws SQLException 
     */
    private ResultSet[] getPrimaryKeys(
            String catalog, String schema, String table) throws SQLException 
    {
        ResultSet[] rss = new ResultSet[2]; 
        DatabaseMetaData dmd = getDMD();
        rss[0]= dmd.getPrimaryKeys(catalog, schema, table);
        rss[1]= getPrimaryKeysODBC(catalog, schema, table);
        
        String [] columnNames = {"TABLE_CAT","TABLE_SCHEM","TABLE_NAME",
                "COLUMN_NAME","KEY_SEQ","PK_NAME"};
        int [] columnTypes = {
                Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,
                Types.VARCHAR,Types.SMALLINT,Types.VARCHAR};
        boolean [] nullability = {false,false,false,false,true,false};
        assertMetaDataResultSet(rss[0], columnNames, columnTypes, nullability);
        assertMetaDataResultSet(rss[1], columnNames, columnTypes, nullability);
           
        return rss;        
    }

    /**
     * Test getImportedKeys, getExportedKeys, getCrossReference; modifies db
     * @throws SQLException 
     */
    public void testGetXXportedKeys() throws SQLException
    {
        // getExportedKeys


        String[][] expRS1 = new String[][] {
            {"",schema,"KT1","VC10","",schema,"REFTAB","VC10","1","3","3","FKEY1","PRIMKEY","7"},
            {"",schema,"KT1","I","",schema,"REFTAB","I","2","3","3","FKEY1","PRIMKEY","7"},
            {"",schema,"KT1","C30","",schema,"REFTAB","C30","1","3","3","FKEY3","UNIQUEKEY","7"},
            {"",schema,"KT1","C30","",schema,"REFTAB","C30","1","3","3","FKEY2","UNIQUEKEY","7"},
            {"",schema,"KT1","S","",schema,"REFTAB","S","2","3","3","FKEY3","UNIQUEKEY","7"},
            {"",schema,"KT1","S","",schema,"REFTAB","S2","2","3","3","FKEY2","UNIQUEKEY","7"},
            {"",schema,"REFTAB","DPRIM","",schema,"REFTAB","DFOR","1","3","3","FKEYSELF","PKEY_REFTAB","7"}};
        String[][] expRS2 = new String[][] {
            {"",schema,"KT1","VC10","",schema,"REFTAB2","T2_VC10","1","3","3","T2_FKEY1","PRIMKEY","7"},
            {"",schema,"KT1","I","",schema,"REFTAB2","T2_I","2","3","3","T2_FKEY1","PRIMKEY","7"}};

        createObjectsForKeysTests();
        
        // try with valid search criteria
        // although, % may not actually be appropriate?
        ResultSet rs[] = getImportedKeys("", "%", "REFTAB");
        assertFullUnorderedResultSet(rs, expRS1, true);
        rs = getImportedKeys("", "%", "REFTAB2");
        assertFullResultSet(rs, expRS2, true);
        
        rs = getImportedKeys(null, schema, "REFTAB");
        assertFullUnorderedResultSet(rs, expRS1, true);
        rs = getImportedKeys(null, schema, "REFTAB2");
        assertFullResultSet(rs, expRS2, true);

        rs = getImportedKeys(null, null, "REFTAB");
        assertFullUnorderedResultSet(rs, expRS1, true);

        rs = getImportedKeys(null, "", "REFTAB");
        JDBC.assertEmpty(rs[0]);
        JDBC.assertEmpty(rs[1]);

        // tablename may not be null
        DatabaseMetaData dmd = getDMD();
        try {
            rs[0] = dmd.getImportedKeys(null, null, null);
            fail ("table name may not be null, should've given error");
        } catch (SQLException sqle) {
            assertSQLState("XJ103", sqle);
        }
        try {
            rs[1] = getImportedKeysODBC(null, null, null);
            fail ("table name may not be null, should've given error");
        } catch (SQLException sqle) {
            assertSQLState("XJ103", sqle);
        }
        
        // DERBY-2610, tablename must be given as stored - % means no rows
        rs = getImportedKeys(null, null, "%");
        JDBC.assertEmpty(rs[0]);
        JDBC.assertEmpty(rs[1]);

        // getExportedKeys
        expRS1 = new String[][] {
                {"",schema,"KT1","VC10","",schema,"REFTAB","VC10","1","3","3","FKEY1","PRIMKEY","7"},
                {"",schema,"KT1","I","",schema,"REFTAB","I","2","3","3","FKEY1","PRIMKEY","7"},
                {"",schema,"KT1","C30","",schema,"REFTAB","C30","1","3","3","FKEY2","UNIQUEKEY","7"},
                {"",schema,"KT1","S","",schema,"REFTAB","S2","2","3","3","FKEY2","UNIQUEKEY","7"},
                {"",schema,"KT1","C30","",schema,"REFTAB","C30","1","3","3","FKEY3","UNIQUEKEY","7"},
                {"",schema,"KT1","S","",schema,"REFTAB","S","2","3","3","FKEY3","UNIQUEKEY","7"},
                {"",schema,"KT1","VC10","",schema,"REFTAB2","T2_VC10","1","3","3","T2_FKEY1","PRIMKEY","7"},
                {"",schema,"KT1","I","",schema,"REFTAB2","T2_I","2","3","3","T2_FKEY1","PRIMKEY","7"}};
        expRS2 = new String[][] {
                {"",schema,"REFTAB","DPRIM","",schema,"REFTAB","DFOR","1","3","3","FKEYSELF","PKEY_REFTAB","7"}};

        rs = getExportedKeys("", "%", "KT1");
        assertFullResultSet(rs, expRS1, true);
        rs = getExportedKeys("", "%", "REFTAB");
        assertFullResultSet(rs, expRS2, true);
        
        rs = getExportedKeys(null, schema, "KT1");
        assertFullResultSet(rs, expRS1, true);
        rs = getExportedKeys(null, schema, "REFTAB");
       assertFullResultSet(rs, expRS2, true);

        rs = getExportedKeys(null, null, "KT1");
        assertFullResultSet(rs, expRS1, true);

        rs = getExportedKeys(null, "", "KT1");
        JDBC.assertEmpty(rs[0]);
        JDBC.assertEmpty(rs[1]);

        // tablename may not be null
        try {
            rs[0] = dmd.getExportedKeys(null, schema, null);
            fail ("table name may not be null, should've given error");
        } catch (SQLException sqle) {
            assertSQLState("XJ103", sqle);
        }
        try {
            rs[1] = getExportedKeysODBC(null, schema, null);
            fail ("table name may not be null, should've given error");
        } catch (SQLException sqle) {
            assertSQLState("XJ103", sqle);
        }
        
        // DERBY-2610, tablename must be given as stored - % means no rows
        rs = getExportedKeys(null, null, "%");
        JDBC.assertEmpty(rs[0]);
        JDBC.assertEmpty(rs[1]);
        
        // getCrossReference
        expRS1 = new String[][] {
                {"",schema,"KT1","VC10","",schema,"REFTAB","VC10","1","3","3","FKEY1","PRIMKEY","7"},
                {"",schema,"KT1","I","",schema,"REFTAB","I","2","3","3","FKEY1","PRIMKEY","7"},
                {"",schema,"KT1","C30","",schema,"REFTAB","C30","1","3","3","FKEY2","UNIQUEKEY","7"},
                {"",schema,"KT1","S","",schema,"REFTAB","S2","2","3","3","FKEY2","UNIQUEKEY","7"},
                {"",schema,"KT1","C30","",schema,"REFTAB","C30","1","3","3","FKEY3","UNIQUEKEY","7"},
                {"",schema,"KT1","S","",schema,"REFTAB","S","2","3","3","FKEY3","UNIQUEKEY","7"}};
        expRS2 = new String[][] {
                {"",schema,"REFTAB","DPRIM","",schema,"REFTAB","DFOR","1","3","3","FKEYSELF","PKEY_REFTAB","7"}};

        // try with valid search criteria
        rs = getCrossReference("", schema, "KT1", "", schema, "REFTAB");
        assertFullResultSet(rs, expRS1, true);
        
        rs = getCrossReference("", schema, "REFTAB", "", schema, "REFTAB");
        assertFullResultSet(rs, expRS2, true);

        rs = getCrossReference("", schema, "KT1", "", schema, "REFTAB");
        assertFullResultSet(rs, expRS1, true);

        rs = getCrossReference("", schema, "REFTAB", "", schema, "REFTAB");
        assertFullResultSet(rs, expRS2, true);

        rs = getCrossReference(null, schema, "KT1", null, schema, "REFTAB");
        assertFullResultSet(rs, expRS1, true);

        rs = getCrossReference(null, schema, "REFTAB", null, schema, "REFTAB");
        assertFullResultSet(rs, expRS2, true);

        // DERBY-2758; query should return a different value for odbc vs. jdbc
        // only experiment jdbc here, odbc is handled elsewhere.
        rs = getCrossReference(null, schema, "%", null, schema, "%");
        JDBC.assertEmpty(rs[0]);
        String[][] expRS = new String[][] {
                {"",schema,"KT1","VC10","",schema,"REFTAB","VC10","1","3","3","FKEY1","PRIMKEY","7"},
                {"",schema,"KT1","I","",schema,"REFTAB","I","2","3","3","FKEY1","PRIMKEY","7"},
                {"",schema,"KT1","C30","",schema,"REFTAB","C30","1","3","3","FKEY2","UNIQUEKEY","7"},
                {"",schema,"KT1","S","",schema,"REFTAB","S2","2","3","3","FKEY2","UNIQUEKEY","7"},
                {"",schema,"KT1","C30","",schema,"REFTAB","C30","1","3","3","FKEY3","UNIQUEKEY","7"},
                {"",schema,"KT1","S","",schema,"REFTAB","S","2","3","3","FKEY3","UNIQUEKEY","7"},
                {"",schema,"REFTAB","DPRIM","",schema,"REFTAB","DFOR","1","3","3","FKEYSELF","PKEY_REFTAB","7"},
                {"",schema,"KT1","VC10","",schema,"REFTAB2","T2_VC10","1","3","3","T2_FKEY1","PRIMKEY","7"},
                {"",schema,"KT1","I","",schema,"REFTAB2","T2_I","2","3","3","T2_FKEY1","PRIMKEY","7"}};
        JDBC.assertFullResultSet(rs[1], expRS, true);
        // Test also with null for schema.
        rs = getCrossReference(null, null, "%", null, null, "%");
        JDBC.assertEmpty(rs[0]);
        JDBC.assertResultSetContains(rs[1], expRS);
        
        // tablename may not be null
        try {
            rs[0] = dmd.getCrossReference(null, null, null, null, null, null);
            fail ("table name may not be null, should've given error");
        } catch (SQLException sqle) {
            if (usingDerbyNetClient())
                assertSQLState("XJ110", sqle);
            else
                assertSQLState("XJ103", sqle);
        }
        // Note: With ODBC, this does *not* give an error. 
        // If that changes, uncomment the fail.
        try {
            rs[1] = getCrossReferenceODBC(null, schema, null, null, schema, null);
        //    fail ("table name may not be null, should've given error");
        } catch (SQLException sqle) {
            if (usingDerbyNetClient())
                assertSQLState("XJ110", sqle);
            else
                assertSQLState("XJ103", sqle);
        }
        // tablename may not be null
        try {
            rs[0] = dmd.getCrossReference(null, null, "", null, null, null);
            fail ("table name may not be null, should've given error");
        } catch (SQLException sqle) {
            if (usingDerbyNetClient())
                assertSQLState("XJ111", sqle);
            else
                assertSQLState("XJ103", sqle);
        }
        try {
            rs[1] = getCrossReferenceODBC(null, schema, "", null, schema, null);
            //fail ("table name may not be null, should've given error");
        } catch (SQLException sqle) {
            if (usingDerbyNetClient())
                assertSQLState("XJ111", sqle);
            else
                assertSQLState("XJ103", sqle);
        }        
        
        // DERBY-2610, tablename must be given as stored - % means no rows
        rs = getCrossReference(null, schema, "%", null, schema, "%");
        JDBC.assertEmpty(rs[0]);
        // But it *is* allowed with ODBC, see DERBY-2758
        JDBC.assertFullResultSet(rs[1], expRS, true);
        
        rs[0].close();
        rs[1].close();
        
        dropObjectsForKeysTests();
    }
    
    /**
     * Helper method for testing getImportedKeys - calls the ODBC procedure
     *
     * @param catalog
     * @param schema
     * @param table
     * @return result set of the query
     *
     * @throws SQLException 
     */
    private ResultSet getImportedKeysODBC(
            String catalog, String schema, String table) throws SQLException 
    {
        CallableStatement cs = prepareCall("CALL SYSIBM.SQLFOREIGNKEYS(" +
            "null, null, null, ?, ?, ?, 'IMPORTEDKEY=1;DATATYPE=''ODBC''')");
        cs.setString(1, catalog);
        cs.setString(2, schema);
        cs.setString(3, table);
        cs.execute();
        return cs.getResultSet();
    }
    
    /**
     * Helper method for testing getImportedKeys - calls dmd.getImportedKeys for
     * the JDBC call, and getImportedKeysODBC for the ODBC procedure
     * @param catalog
     * @param schema
     * @param table
     * @return an array of two result sets corresponding to JDBC and ODBC
     *         respectively
     * @throws SQLException 
     */
    private ResultSet[] getImportedKeys(
            String catalog, String schema, String table) throws SQLException 
    {
        ResultSet[] rss = new ResultSet[2]; 
        DatabaseMetaData dmd = getDMD();
        rss[0]= dmd.getImportedKeys(catalog, schema, table);
        rss[1]= getImportedKeysODBC(catalog, schema, table);
        
        assertGetImportedAndExportedKeysShape(rss);
        return rss;        
    }
    
    /**
     * Assert the shape of the ResultSets for getImportedKeys,
     * getExportedKeys and getCrossReference.
     * @param rss ResultSets from JDBC and ODBC calls.
     * @throws SQLException
     */
    private void assertGetImportedAndExportedKeysShape(ResultSet[] rss)
        throws SQLException
    {
        String [] columnNames = {
                "PKTABLE_CAT","PKTABLE_SCHEM","PKTABLE_NAME","PKCOLUMN_NAME",
                "FKTABLE_CAT","FKTABLE_SCHEM","FKTABLE_NAME","FKCOLUMN_NAME",
                "KEY_SEQ","UPDATE_RULE","DELETE_RULE",
                "FK_NAME","PK_NAME","DEFERRABILITY"};
            int [] columnTypes = {
                Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,
                Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,
                Types.SMALLINT,Types.SMALLINT,Types.SMALLINT,
                Types.VARCHAR,Types.VARCHAR,Types.SMALLINT};
            boolean [] nullability = {false,false,false,false,
                false,false,false,false,true,true,true,false,false,true};
            
         assertMetaDataResultSet(rss[0], columnNames, columnTypes, nullability);
         assertMetaDataResultSet(rss[1], columnNames, columnTypes, nullability);   
    }

    /**
     * Helper method for testing getExportedKeys - calls the ODBC procedure
     *
     * @param catalog
     * @param schema
     * @param table
     * @return the result set of the query
     *
     * @throws SQLException 
     */
    private ResultSet getExportedKeysODBC(
            String catalog, String schema, String table) throws SQLException 
    {
        CallableStatement cs = prepareCall("CALL SYSIBM.SQLFOREIGNKEYS(" +
            "?, ?, ?, null, null, null, 'EXPORTEDKEY=1;DATATYPE=''ODBC''')");
        cs.setString(1, catalog);
        cs.setString(2, schema);
        cs.setString(3, table);
        cs.execute();
        return cs.getResultSet();
    }
    
    /**
     * Helper method for testing getExportedKeys - calls dmd.getExportedKeys for
     * the JDBC call, and getExportedKeysODBC for the ODBC procedure
     * @param catalog
     * @param schema
     * @param table
     * @return an array of two result sets corresponding to JDBC and ODBC
     *         respectively
     * @throws SQLException 
     */
    private ResultSet[] getExportedKeys(
            String catalog, String schema, String table) throws SQLException 
    {
        ResultSet[] rss = new ResultSet[2]; 
        DatabaseMetaData dmd = getDMD();
        rss[0]= dmd.getExportedKeys(catalog, schema, table);
        rss[1]= getExportedKeysODBC(catalog, schema, table);
        
        assertGetImportedAndExportedKeysShape(rss);
        
        return rss;        
    }

    /**
     * Helper method for testing getCrossReference - calls the ODBC procedure
     * @param parentcatalog
     * @param parentschema
     * @param parenttable
     * @param foreigncatalog
     * @param foreignschema
     * @param foreigntable
     * @return the result set of the query
     * @throws SQLException 
     */
    private ResultSet getCrossReferenceODBC(
            String parentcatalog, String parentschema, String parenttable,
            String foreigncatalog, String foreignschema, String foreigntable)
        throws SQLException 
    {
        CallableStatement cs = prepareCall("CALL SYSIBM.SQLFOREIGNKEYS(" +
                "?, ?, ?, ?, ?, ?, 'DATATYPE=''ODBC''')");

        cs.setString(1, parentcatalog);
        cs.setString(2, parentschema);
        cs.setString(3, parenttable);
        cs.setString(4, foreigncatalog);
        cs.setString(5, foreignschema);
        cs.setString(6, foreigntable);
        cs.execute();
        return cs.getResultSet();
    }
    
    /**
     * Helper method for testing getCrossReference - calls dmd.getCrossReference for
     * the JDBC call, and getCrossReferenceODBC for the ODBC procedure
     *
     * @param parentcatalog
     * @param parentschema
     * @param parenttable
     * @param foreigncatalog
     * @param foreignschema
     * @param foreigntable
     * @return an array of two result sets corresponding to JDBC and ODBC
     *         respectively
     *
     * @throws SQLException 
     */
    private ResultSet[] getCrossReference(
            String parentcatalog, String parentschema, String parenttable,
            String foreigncatalog, String foreignschema, String foreigntable)
        throws SQLException 
    {
        ResultSet[] rss = new ResultSet[2]; 
        DatabaseMetaData dmd = getDMD();
        rss[0]= dmd.getCrossReference(parentcatalog, parentschema, parenttable,
            foreigncatalog, foreignschema, foreigntable);
        rss[1]= getCrossReferenceODBC(parentcatalog, parentschema, parenttable,
            foreigncatalog, foreignschema, foreigntable);
        
        assertGetImportedAndExportedKeysShape(rss);
        return rss;        
    }
    
    /**
     * Test referential action values; modifies database
     * @throws SQLException 
     */
    public void testReferentialAction() throws SQLException
    {
        Statement s = createStatement();

        getConnection().setAutoCommit(false);        
        // First, create the test table and indexes/keys
        // note: apparently we have no test for setdefault.
        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)");
        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)");
        
        short restrict = DatabaseMetaData.importedKeyRestrict;
        short no_action = DatabaseMetaData.importedKeyNoAction;
        short cascade = DatabaseMetaData.importedKeyCascade;
        short setnull = DatabaseMetaData.importedKeySetNull;
        short setdefault = DatabaseMetaData.importedKeySetDefault;
        
        ResultSet rs[] = getCrossReference("",schema,"REFACTION1","",schema,"REFACTNONE");
        verifyReferentialAction(rs, new short[] {no_action, no_action});
        rs = getCrossReference("",schema,"REFACTION1","",schema,"REFACTRESTRICT");
        verifyReferentialAction(rs, new short[] {no_action, restrict});
        rs = getCrossReference("",schema,"REFACTION1","",schema,"REFACTNOACTION");
        verifyReferentialAction(rs, new short[] {no_action, no_action});
        rs = getCrossReference("",schema,"REFACTION1","",schema,"REFACTCASCADE");
        verifyReferentialAction(rs, new short[] {no_action, cascade});
        rs = getCrossReference("",schema,"REFACTION1","",schema,"REFACTSETNULL");
        verifyReferentialAction(rs, new short[] {no_action, setnull});
        rs = getCrossReference("",schema,"REFACTION1","",schema,"REFACTUPDRESTRICT");
        verifyReferentialAction(rs, new short[] {restrict, no_action});
        rs = getCrossReference("",schema,"REFACTION1","",schema,"REFACTUPDNOACTION");
        verifyReferentialAction(rs, new short[] {no_action, no_action});

        rs = getImportedKeys(null, schema, "REFACTNONE");
        verifyReferentialAction(rs, new short[] {no_action, no_action});
        rs = getImportedKeys(null, schema, "REFACTRESTRICT");
        verifyReferentialAction(rs, new short[] {restrict, restrict}); 
        rs = getImportedKeys(null, schema, "REFACTNOACTION");
        verifyReferentialAction(rs, new short[] {no_action, no_action});
        rs = getImportedKeys(null, schema, "REFACTCASCADE");
        verifyReferentialAction(rs, new short[] {no_action, cascade});
        rs = getImportedKeys(null, schema, "REFACTSETNULL");
        verifyReferentialAction(rs, new short[] {no_action, setnull});
        rs = getImportedKeys(null, schema, "REFACTUPDRESTRICT");
        verifyReferentialAction(rs, new short[] {no_action, no_action});
        rs = getImportedKeys(null, schema, "REFACTUPDNOACTION");
        verifyReferentialAction(rs, new short[] {no_action, no_action});

        rs = getExportedKeys(null, schema, "REFACTION1");
        short [][] expkeyresults = {
                {no_action, cascade},
                {no_action, no_action},
                {no_action, no_action},
                {no_action, restrict},
                {no_action, setnull},
                {no_action, no_action},
                {restrict, no_action}};
        for (int i = 0 ; i < 6 ; i++)
        {
            rs[0].next();
            assertEquals(expkeyresults[i][0], rs[0].getShort(10));
            assertEquals(expkeyresults[i][1], rs[0].getShort(11));
        }
        for (int i = 0 ; i < 6 ; i++)
        {
            rs[1].next();
            assertEquals(expkeyresults[i][0], rs[1].getShort(10));
            assertEquals(expkeyresults[i][1], rs[1].getShort(11));
        }

        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");
        commit();
        
        rs[0].close();
        rs[1].close();
        s.close();
        
        getConnection().setAutoCommit(true);        
    }    
    
    // helper method for Referential Action test; verifies result
    // of various calls.
    // rs[0] is for JDBC calls, rs[1] for ODBC
    public void verifyReferentialAction(ResultSet[] rs, short[] expRes) 
        throws SQLException {
        rs[0].next();
        assertEquals(expRes[0], rs[0].getShort(10));
        assertEquals(expRes[1], rs[0].getShort(11));
        rs[1].next();
        assertEquals(expRes[0], rs[1].getShort(10));
        assertEquals(expRes[1], rs[1].getShort(11));
    }
    
    
    /**
     * Test DatabaseMetaData.getProcedures and .getProcedureColumns,
     * Both for JDBC and ODBC.
     * Further testing of these methods is done in lang/LangProcedureTest
     *
     * 
     * @throws Exception
     */
    // Possible TODO: 
    //   rewrite data portion of this test to compare results from 
    //   metadata with sys.sys* query results (leave shape check in place)
    public void testGetProceduresGetProcColumns() throws Exception {

        boolean supportsBoolean = true;
        Version dataVersion = getDataVersion( getConnection() );
        if ( dataVersion.compareTo( new Version( 10, 7, 0, 0 ) ) < 0 ) { supportsBoolean = false; }

        Statement s = createStatement();
        getConnection().setAutoCommit(false);
        
        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.DatabaseMetaDataTest.getpc'" +
        " parameter style java"); 
        s.execute("create procedure GETPCTEST2 (pa INTEGER, pb BIGINT)"+
                "language java external name " +
                "'org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.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.DatabaseMetaDataTest.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.DatabaseMetaDataTest.getpc'" +
        " parameter style java"); 
        s.execute("create procedure GETPCTEST4A()  "+
                "language java external name " +
                "'org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.getpc4a'"+
        " parameter style java"); 
        s.execute("create procedure GETPCTEST4B() "+
                "language java external name " +
                "'org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.getpc4b'" +
        " parameter style java"); 
        s.execute("create procedure GETPCTEST4Bx(out retparam INTEGER) "+
                "language java external name " +
                "'org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.getpc4b'" +
        " parameter style java");

        if ( supportsBoolean )
        {
            s.execute("create procedure GETPCTEST5(in inarg boolean, out outarg boolean, inout inoutarg boolean) "+
                      "language java external name " +
                      "'org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.foo'" +
                      " parameter style java");
        }
        
        JDBC.GeneratedId genid = new JDBC.GeneratedId();

        String classname = getClass().getName();
        String getpc = classname + ".getpc";
        String getpc4a = classname + ".getpc4a";
        String getpc4b = classname + ".getpc4b";
        String foo = classname + ".foo";

        ResultSet rs[] = getProcedures(null, "%", "GETPCTEST%");
        Object[][] expRS = new Object[][] {
                {"",schema,"GETPCTEST1",null,null,null,getpc,i(1),genid},
                {"",schema,"GETPCTEST2",null,null,null,getpc,i(1),genid},
                {"",schema,"GETPCTEST3A",null,null,null,getpc,i(1),genid},
                {"",schema,"GETPCTEST3B",null,null,null,getpc,i(1),genid},
                {"",schema,"GETPCTEST4A",null,null,null,getpc4a,i(1),genid},
                {"",schema,"GETPCTEST4B",null,null,null,getpc4b,i(1),genid},
                {"",schema,"GETPCTEST4BX",null,null,null,getpc4b,i(1),genid},
        };

        if ( supportsBoolean )
        {
            expRS = appendArray
                (
                 expRS,
                 new Object[][]
                 {
                     {"",schema,"GETPCTEST5",null,null,null,foo,i(1),genid},
                 }
                 );
        }

        // Check the JDBC variant of getProcedures().
        JDBC.assertFullResultSet(rs[0], expRS, false);

        // Check the ODBC variant of getProcedures(). It's identical to the
        // JDBC variant, except that it lacks the last column.
        for (int i = 0; i < expRS.length; i++) {
            Object[] jdbcRow = expRS[i];
            Object[] odbcRow = new Object[jdbcRow.length - 1];
            System.arraycopy(jdbcRow, 0, odbcRow, 0, odbcRow.length);
            expRS[i] = odbcRow;
        }
        JDBC.assertFullResultSet(rs[1], expRS, false);

        rs = getProcedureColumns(null, "%", "GETPCTEST%", "%");

        expRS = new Object[][] {
                {null,schema,"GETPCTEST1","OUTB",i(4),i(12),"VARCHAR",i(3),i(6),null,null,i(1),null,null,i(12),null,i(6),i(1),"YES",genid,i(12),i(0)},
                {null,schema,"GETPCTEST1","A",i(1),i(12),"VARCHAR",i(3),i(6),null,null,i(1),null,null,i(12),null,i(6),i(2),"YES",genid,i(12),i(1)},
                {null,schema,"GETPCTEST1","B",i(1),i(2),"NUMERIC",i(5),i(14),i(0),i(10),i(1),null,null,i(2),null,null,i(3),"YES",genid,i(12),i(2)},
                {null,schema,"GETPCTEST1","C",i(1),i(5),"SMALLINT",i(5),i(2),i(0),i(10),i(1),null,null,i(5),null,null,i(4),"YES",genid,i(12),i(3)},
                {null,schema,"GETPCTEST1","E",i(1),i(5),"SMALLINT",i(5),i(2),i(0),i(10),i(1),null,null,i(5),null,null,i(5),"YES",genid,i(12),i(4)},
                {null,schema,"GETPCTEST1","F",i(1),i(4),"INTEGER",i(10),i(4),i(0),i(10),i(1),null,null,i(4),null,null,i(6),"YES",genid,i(12),i(5)},
                {null,schema,"GETPCTEST1","G",i(1),i(-5),"BIGINT",i(19),i(40),i(0),i(10),i(1),null,null,i(-5),null,null,i(7),"YES",genid,i(12),i(6)},
                {null,schema,"GETPCTEST1","H",i(1),i(8),"DOUBLE",i(52),i(8),null,i(2),i(1),null,null,i(8),null,null,i(8),"YES",genid,i(12),i(7)},
                {null,schema,"GETPCTEST1","I",i(1),i(8),"DOUBLE",i(52),i(8),null,i(2),i(1),null,null,i(8),null,null,i(9),"YES",genid,i(12),i(8)},
                {null,schema,"GETPCTEST1","K",i(1),i(91),"DATE",i(10),i(6),i(0),i(10),i(1),null,null,i(9),i(1),null,i(10),"YES",genid,i(12),i(9)},
                {null,schema,"GETPCTEST1","L",i(1),i(92),"TIME",i(8),i(6),i(0),i(10),i(1),null,null,i(9),i(2),null,i(11),"YES",genid,i(12),i(10)},
                {null,schema,"GETPCTEST1","T",i(1),i(93),"TIMESTAMP",i(29),i(16),i(9),i(10),i(1),null,null,i(9),i(3),null,i(12),"YES",genid,i(12),i(11)},
                {null,schema,"GETPCTEST2","PA",i(1),i(4),"INTEGER",i(10),i(4),i(0),i(10),i(1),null,null,i(4),null,null,i(1),"YES",genid,i(2),i(0)},
                {null,schema,"GETPCTEST2","PB",i(1),i(-5),"BIGINT",i(19),i(40),i(0),i(10),i(1),null,null,i(-5),null,null,i(2),"YES",genid,i(2),i(1)},
                {null,schema,"GETPCTEST3A","STRING1",i(1),i(12),"VARCHAR",i(5),i(10),null,null,i(1),null,null,i(12),null,i(10),i(1),"YES",genid,i(2),i(0)},
                {null,schema,"GETPCTEST3A","STRING2",i(4),i(12),"VARCHAR",i(5),i(10),null,null,i(1),null,null,i(12),null,i(10),i(2),"YES",genid,i(2),i(1)},
                {null,schema,"GETPCTEST3B","STRING3",i(1),i(12),"VARCHAR",i(5),i(10),null,null,i(1),null,null,i(12),null,i(10),i(1),"YES",genid,i(2),i(0)},
                {null,schema,"GETPCTEST3B","STRING4",i(2),i(12),"VARCHAR",i(5),i(10),null,null,i(1),null,null,i(12),null,i(10),i(2),"YES",genid,i(2),i(1)},
                {null,schema,"GETPCTEST4BX","RETPARAM",i(4),i(4),"INTEGER",i(10),i(4),i(0),i(10),i(1),null,null,i(4),null,null,i(1),"YES",genid,i(1),i(0)},
        };
        if ( supportsBoolean )
        {
            expRS = appendArray
                (
                 expRS,
                 new Object[][]
                 {
                     {null,schema,"GETPCTEST5","INARG",i(1),i(16),"BOOLEAN",i(1),i(1),null,null,i(1),null,null,i(16),null,null,i(1),"YES",genid,i(3),i(0)},
                     {null,schema,"GETPCTEST5","OUTARG",i(4),i(16),"BOOLEAN",i(1),i(1),null,null,i(1),null,null,i(16),null,null,i(2),"YES",genid,i(3),i(1)},
                     {null,schema,"GETPCTEST5","INOUTARG",i(2),i(16),"BOOLEAN",i(1),i(1),null,null,i(1),null,null,i(16),null,null,i(3),"YES",genid,i(3),i(2)},
                 }
                 );
        }

        Object[][] jdbcExpRS = new Object[expRS.length][];
        Object[][] odbcExpRS = new Object[expRS.length][];
        for (int i = 0; i < jdbcExpRS.length; i++) {
            Object[] row = expRS[i];

            // JDBC variant has always null in column #15 (SQL_DATA_TYPE) and
            // column #16 (SQL_DATETIME_SUB)
            Object[] jdbcRow = row.clone();
            jdbcRow[14] = jdbcRow[15] = null;

            // ODBC variant lacks column #20 (SPECIFIC_NAME)...
            ArrayList<Object> odbcRow =
                    new ArrayList<Object>(Arrays.asList(row));
            odbcRow.remove(19);
            // ... and it is a bit different in the datetime rows
            if (i == 9) {
                odbcRow.set(9, null);
            }
            if (i >= 9 && i <= 11) {
                odbcRow.set(10, i(2));
            }

            jdbcExpRS[i] = jdbcRow;
            odbcExpRS[i] = odbcRow.toArray();
        }

        JDBC.assertFullResultSet(rs[0], jdbcExpRS, false);
        JDBC.assertFullResultSet(rs[1], odbcExpRS, false);

        if ( supportsBoolean ) { s.execute("drop procedure GETPCTEST5"); }
        s.execute("drop procedure GETPCTEST4Bx");
        s.execute("drop procedure GETPCTEST4B");
        s.execute("drop procedure GETPCTEST4A");
        s.execute("drop procedure GETPCTEST3B");
        s.execute("drop procedure GETPCTEST3A");
        s.execute("drop procedure GETPCTEST2");
        s.execute("drop procedure GETPCTEST1");
        commit();
    }

    /**
     * Append one two-dimensional array to another.
     * @param target
     * @param suffix
     * @return appended array
     */
    private Object[][] appendArray(Object[][] target, Object[][] suffix)
    {
        int targetLength = target.length;
        int suffixLength = suffix.length;
        int resultLength = targetLength + suffixLength;

        Object[][] result = new Object[resultLength][];
        System.arraycopy(target, 0, result, 0, targetLength);
        System.arraycopy(suffix, 0, result, targetLength, suffixLength);

        println( "Appended array" );

        return result;
    }

    /**
     * Helper method for testing getProcedures - calls the ODBC procedure
     * @param catalog
     * @param schemaPattern
     * @param procedureNamePattern
     * @return the result set of the query
     * @throws SQLException 
     */
    private ResultSet getProceduresODBC(
            String catalog, String schemaPattern, String procedureNamePattern)
        throws SQLException 
    {
        CallableStatement cs = prepareCall("CALL SYSIBM.SQLPROCEDURES(" +
                "?, ?, ?, 'DATATYPE=''ODBC''')");
            cs.setString(1, catalog);
            cs.setString(2, schemaPattern);
            cs.setString(3, procedureNamePattern);
            cs.execute();
            return cs.getResultSet();
    }
    
    /**
     * Helper method for testing getProcedures - calls dmd.getProcedures for
     * the JDBC call, and getProceduresODBC for the ODBC procedure
     *
     * @param catalog
     * @param schemaPattern
     * @param procedureNamePattern
     * @return an array of two result sets corresponding to JDBC and ODBC
     *         respectively
     *
     * @throws SQLException 
     */
    private ResultSet[] getProcedures(
            String catalog, String schemaPattern, String procedureNamePattern)
        throws SQLException 
    {
        ResultSet[] rs = new ResultSet[2]; 
        DatabaseMetaData dmd = getDMD();
        rs[0]= dmd.getProcedures(catalog, schemaPattern, procedureNamePattern);
        rs[1]= getProceduresODBC(catalog, schemaPattern, procedureNamePattern);
        
        String[] columnNames = new String[] {
                "PROCEDURE_CAT","PROCEDURE_SCHEM","PROCEDURE_NAME",
                "RESERVED1","RESERVED2","RESERVED3",
                "REMARKS","PROCEDURE_TYPE","SPECIFIC_NAME"};

        int[] columnTypes = new int[] {
                Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.INTEGER,
                Types.INTEGER, Types.INTEGER, Types.VARCHAR, Types.SMALLINT,
                Types.VARCHAR};

        boolean[] nullability = new boolean[] {
                false, false, false, true, true, true, true, true, false};
        
        // JDBC result set
        assertMetaDataResultSet(rs[0], columnNames, columnTypes, nullability);
        
        // change the shape for ODBC - one less column, no SPECIFIC_NAME (9)
        
        String[] odbcColumnNames = new String[columnNames.length - 1];
        System.arraycopy(columnNames, 0, odbcColumnNames, 0, odbcColumnNames.length);
        int[] odbcColumnTypes = new int[columnTypes.length - 1];
        System.arraycopy(columnTypes, 0, odbcColumnTypes, 0, odbcColumnTypes.length);
        boolean[] odbcNullability = new boolean[nullability.length - 1];
        System.arraycopy(nullability, 0, odbcNullability, 0, odbcNullability.length);
        
        // Change column names
        odbcColumnNames[4 - 1] = "NUM_INPUT_PARAMS";
        odbcColumnNames[5 - 1] = "NUM_OUTPUT_PARAMS";
        odbcColumnNames[6 - 1] = "NUM_RESULT_SETS";
              
        // ODBC result set.
        assertMetaDataResultSet(
                rs[1], odbcColumnNames, odbcColumnTypes, odbcNullability);

        return rs;        
    }

    /**
     * Helper method for testing getProcedureColumns - calls the ODBC procedure
     * @param catalog
     * @param schemaPattern
     * @param procedureNamePattern
     * @param columnNamePattern
     * @return the result set of the query
     * @throws SQLException 
     */
    private ResultSet getProcedureColumnsODBC(String catalog, 
            String schemaPattern, String procedureNamePattern,
            String columnNamePattern) throws SQLException 
    {
        CallableStatement cs = prepareCall("CALL SYSIBM.SQLPROCEDURECOLS(" +
                "?, ?, ?, ?, 'DATATYPE=''ODBC''')");

        cs.setString(1, catalog);
        cs.setString(2, schemaPattern);
        cs.setString(3, procedureNamePattern);
        cs.setString(4, columnNamePattern);
        cs.execute();
        return cs.getResultSet();
    }
    
    /**
     * Helper method for testing getProcedureColumns - calls 
     * dmd.getProcedureColumns for the JDBC call, and 
     * getProcedureColumnssODBC for the ODBC procedure
     * @param catalog
     * @param schemaPattern
     * @param procedureNamePattern
     * @param columnNamePattern
     * @return an array of two result sets corresponding to JDBC and ODBC
     *         respectively
     * @throws SQLException 
     */
    private ResultSet[] getProcedureColumns(String catalog, 
            String schemaPattern, String procedureNamePattern,
            String columnNamePattern) throws SQLException 
    {
        ResultSet[] rss = new ResultSet[2]; 
        DatabaseMetaData dmd = getDMD();
        rss[0]= dmd.getProcedureColumns(catalog, schemaPattern, 
                procedureNamePattern, columnNamePattern);
        rss[1]= getProcedureColumnsODBC(catalog, schemaPattern,
                procedureNamePattern, columnNamePattern);
        
        String[] columnNames = new String[] {
                "PROCEDURE_CAT","PROCEDURE_SCHEM","PROCEDURE_NAME",
                "COLUMN_NAME", "COLUMN_TYPE", "DATA_TYPE", "TYPE_NAME",
                "PRECISION", "LENGTH", "SCALE",
                "RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF",
                "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH",
                "ORDINAL_POSITION", "IS_NULLABLE", "SPECIFIC_NAME" //};
                // interesting, we seem to have two extra columns vs the API
                ,"METHOD_ID", "PARAMETER_ID"};

        int[] columnTypes = new int[] {
                Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
                Types.SMALLINT, Types.INTEGER, Types.VARCHAR, Types.INTEGER,
                Types.INTEGER, Types.SMALLINT, Types.SMALLINT, Types.SMALLINT,
                Types.VARCHAR, Types.VARCHAR, Types.INTEGER, Types.INTEGER,
                Types.INTEGER, Types.INTEGER, Types.VARCHAR, Types.VARCHAR //};
                , Types.SMALLINT, Types.SMALLINT};

        boolean[] nullability = new boolean[] {
                true, false, false, false, false, false, false, false, false, true,
                true, false, true, true, true, true, true, false, false, false//};
                , false, false};

        
        // JDBC result set
        assertMetaDataResultSet(rss[0], columnNames, columnTypes, nullability);
        
        // Change expected shape for ODBC
        
        // One less column for ODBC, (20) SPECIFIC_NAME is missing.
        
        String[] odbcColumnNames = new String[columnNames.length - 1];
        System.arraycopy(columnNames, 0, odbcColumnNames, 0, 19);
        System.arraycopy(columnNames, 21 - 1, odbcColumnNames, 20 - 1, 2);
        
        int[] odbcColumnTypes = new int[columnTypes.length - 1];
        System.arraycopy(columnTypes, 0, odbcColumnTypes, 0, 19);
        System.arraycopy(columnTypes, 21 - 1, odbcColumnTypes, 20 - 1, 2);
        
        //      SQL_DATA_TYPE NULL in JDBC, valid type in ODBC.
        // otherwise the same as JDBC
        boolean[] odbcNullability = new boolean[] {
                true, false, false, false, false, false, false, false, false, true,
                true, false, true, true, false, true, true, false, false, false
                , false};

        
        // And change some column names.
        odbcColumnNames[8 - 1] = "COLUMN_SIZE";
        odbcColumnNames[9 - 1] = "BUFFER_LENGTH";
        odbcColumnNames[10 - 1] = "DECIMAL_DIGITS";
        odbcColumnNames[11 - 1] = "NUM_PREC_RADIX";
        
        // And some column types.
        odbcColumnTypes[6 - 1] = Types.SMALLINT;
        odbcColumnTypes[15 - 1] = Types.SMALLINT;
        odbcColumnTypes[16 - 1] = Types.SMALLINT;
             
        // odbc result set
        assertMetaDataResultSet(
            rss[1], odbcColumnNames, odbcColumnTypes, odbcNullability);
        
        return rss;        
    }

    /**
     * Test DatabaseMetaData.getFunctionColumns()
     *
     * @throws java.lang.Exception
     */
    public void testGetFunctionColumns() throws Exception
    {
        // this method is supported in database meta data only from 10.2 onward

        boolean supportsBoolean = true;
        Version dataVersion = getDataVersion( getConnection() );
        if ( dataVersion.compareTo( new Version( 10, 7, 0, 0 ) ) < 0 ) { supportsBoolean = false; }
        
        if ( dataVersion.compareTo( new Version( 10, 2, 0, 0 ) ) < 0 ) { return; }

        DatabaseMetaData dmd = getDMD();
        Statement s = createStatement();
        getConnection().setAutoCommit(false);
        
        s.execute("create function f_gfc_1 (" +
                "a VARCHAR(3), b NUMERIC, c SMALLINT, " +
                "e CHAR(3), f INTEGER, g BIGINT, h FLOAT, i DOUBLE PRECISION, " +
                "k DATE, l TIME, T TIMESTAMP ) returns int "+
                "language java external name " +
                "'org.apache.derbyTesting.BlahBlah.blah'" +
        " parameter style java"); 

        if ( supportsBoolean )
        {
            s.execute("create function f_gfc_2 ( a boolean) returns boolean "+
                      "language java external name " +
                      "'org.apache.derbyTesting.functionTests.BlahBlah.blah'" +
                      " parameter style java");
        }

        // We have to use reflection to get the getFunctionColumns() method.
        // That is because we compile this test to run on old versions of the
        // vm whose DatabaseMetaData doesn't include this method, even though
        // our actual drivers do.
        Method gfcMethod = dmd.getClass().getMethod
            ( "getFunctionColumns", new Class<?>[] {
                String.class, String.class, String.class, String.class, } );
        
        ResultSet rs = (ResultSet) gfcMethod.invoke(
                dmd, (Object[])new String[] { null, "%", "F_GFC_%", "%" } );

        JDBC.GeneratedId genid = new JDBC.GeneratedId();

        Object[][] expRS = new Object[][] {
            {null,schema,"F_GFC_1","",i(4),i(4),"INTEGER",i(10),i(4),i(0),i(10),i(1),null,null,i(0),"YES",genid,i(11),i(-1)},
            {null,schema,"F_GFC_1","A",i(1),i(12),"VARCHAR",i(3),i(6),null,null,i(1),null,i(6),i(1),"YES",genid,i(11),i(0)},
            {null,schema,"F_GFC_1","B",i(1),i(2),"NUMERIC",i(5),i(14),i(0),i(10),i(1),null,null,i(2),"YES",genid,i(11),i(1)},
            {null,schema,"F_GFC_1","C",i(1),i(5),"SMALLINT",i(5),i(2),i(0),i(10),i(1),null,null,i(3),"YES",genid,i(11),i(2)},
            {null,schema,"F_GFC_1","E",i(1),i(1),"CHAR",i(3),i(6),null,null,i(1),null,i(6),i(4),"YES",genid,i(11),i(3)},
            {null,schema,"F_GFC_1","F",i(1),i(4),"INTEGER",i(10),i(4),i(0),i(10),i(1),null,null,i(5),"YES",genid,i(11),i(4)},
            {null,schema,"F_GFC_1","G",i(1),i(-5),"BIGINT",i(19),i(40),i(0),i(10),i(1),null,null,i(6),"YES",genid,i(11),i(5)},
            {null,schema,"F_GFC_1","H",i(1),i(8),"DOUBLE",i(52),i(8),null,i(2),i(1),null,null,i(7),"YES",genid,i(11),i(6)},
            {null,schema,"F_GFC_1","I",i(1),i(8),"DOUBLE",i(52),i(8),null,i(2),i(1),null,null,i(8),"YES",genid,i(11),i(7)},
            {null,schema,"F_GFC_1","K",i(1),i(91),"DATE",i(10),i(6),i(0),i(10),i(1),null,null,i(9),"YES",genid,i(11),i(8)},
            {null,schema,"F_GFC_1","L",i(1),i(92),"TIME",i(8),i(6),i(0),i(10),i(1),null,null,i(10),"YES",genid,i(11),i(9)},
            {null,schema,"F_GFC_1","T",i(1),i(93),"TIMESTAMP",i(29),i(16),i(9),i(10),i(1),null,null,i(11),"YES",genid,i(11),i(10)},
        };

        if ( supportsBoolean )
        {
            expRS = appendArray
                (
                 expRS,
                 new Object[][]
                 {
                     {null,schema,"F_GFC_2","",i(4),i(16),"BOOLEAN",i(1),i(1),null,null,i(1),null,null,i(0),"YES",genid,i(1),i(-1)},
                     {null,schema,"F_GFC_2","A",i(1),i(16),"BOOLEAN",i(1),i(1),null,null,i(1),null,null,i(1),"YES",genid,i(1),i(0)},
                 }
                 );
        }

        JDBC.assertFullResultSet(rs, expRS, false);

        if ( supportsBoolean ) { s.execute("drop function f_gfc_2"); }
        s.execute("drop function f_gfc_1");
        commit();
    }

    /**
     * Convert an {@code int} to a {@code java.lang.Integer}.
     * @param i
     * @return an Integer
     */
    private static Integer i(int i) {
        return i;
    }

    /**
     * Test methods added by JDBC 4.1
     *
     * @throws java.lang.Exception
     */
    public void test_jdbc4_1() throws Exception
    {
        Version dataVersion = getDataVersion( getConnection() );
        if ( dataVersion.compareTo( new Version( 10, 8, 0, 0 ) ) < 0 ) { return; }

        Statement s = createStatement();
        DatabaseMetaData dmd = getDMD();
        println( "Testing JDBC 4.1 methods on a " + dmd.getClass().getName() );
        Wrapper41DBMD wrapper = new Wrapper41DBMD( dmd );

        //
        // generatedKeyAlwaysReturned()
        //
        assertEquals( true, wrapper.generatedKeyAlwaysReturned() );

        //
        // getPseudoColumns()
        //
        ResultSet   rs = wrapper.getPseudoColumns( null, null, null, null );
        assertMetaDataResultSet
            (
             rs,
             new String[]
             {
                 "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME",
                 "DATA_TYPE", "COLUMN_SIZE", "DECIMAL_DIGITS", "NUM_PREC_RADIX",
                 "COLUMN_USAGE", "REMARKS", "CHAR_OCTET_LENGTH", "IS_NULLABLE",
             },
             new int[]
             {
                 Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
                 Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER,
                 Types.VARCHAR, Types.VARCHAR, Types.INTEGER, Types.VARCHAR,
             },
             new boolean[]
             {
                 true,  true,   false,  false,
                 false, false,  true,   true,
                 false, true,   true,   false,
             }
             );
        JDBC.assertFullResultSet( rs, new String[][] {} );

        //
        // IS_GENERATEDCOLUMN added to ResultSet returned by getColumns()
        //
        s.execute( "create table t_jdbc41( a int, b int, c generated always as ( -a ) )" );
        ResultSet   rs2 = dmd.getColumns( null, schema, "T_JDBC41", null );
        String[][] expectedRows = new String[][]
        {
            {
                "", schema, "T_JDBC41", "A",
                "4", "INTEGER", "10", null,
                "0", "10", "1", "",
                null, null, null, null,
                "1", "YES", null, null,
                null, null, "NO", "NO",
                null
            },
            {
                "", schema, "T_JDBC41", "B",
                "4", "INTEGER", "10", null,
                "0", "10", "1", "",
                null, null, null, null,
                "2", "YES", null, null,
                null, null, "NO", "NO",
                null
            },
            {
                "", schema, "T_JDBC41", "C",
                "4", "INTEGER", "10", null,
                "0", "10", "1", "",
                "GENERATED ALWAYS AS ( -a )", null, null, null,
                "3", "YES", null, null,
                null, null, "NO", "YES",
                null
            },
        };
        JDBC.assertFullResultSet( rs2, expectedRows );


        s.execute( "drop table t_jdbc41" );
    }
    
    /**
     * Test methods added by JDBC 4.2
     *
     * @throws java.lang.Exception
     */
    public void test_jdbc4_2() throws Exception
    {
        Version dataVersion = getDataVersion( getConnection() );
        if ( dataVersion.compareTo( new Version( 10, 10, 0, 0 ) ) < 0 ) { return; }

        Statement s = createStatement();
        DatabaseMetaData dmd = getDMD();
        println( "Testing JDBC 4.2 methods on a " + dmd.getClass().getName() );
        Wrapper42DBMD wrapper = new Wrapper42DBMD( dmd );

        //
        // getMaxLogicalLobSize()
        //
        assertEquals( 0L, wrapper.getMaxLogicalLobSize() );

        //
        // supportsRefCursors()
        //
        assertEquals( false, wrapper.supportsRefCursors() );
    }

    public void testBugFixes() throws SQLException {
        
        Statement s = createStatement();
        getConnection().setAutoCommit(false);        
        DatabaseMetaData dmd = getDMD();

        // test DERBY-655, DERBY-1343
        // If a table has duplicate backing index, then it will share the 
        // physical conglomerate with the existing index, but the duplicate
        // indexes should have their own unique logical congomerates 
        // associated with them. That way, it will be possible to 
        // distinguish the 2 indexes in SYSCONGLOMERATES from each other.
        s.execute("CREATE TABLE Derby655t1(c11_ID BIGINT NOT NULL)");
        s.execute("CREATE TABLE Derby655t2 (c21_ID BIGINT NOT NULL primary key)");
        s.execute("ALTER TABLE Derby655t1 ADD CONSTRAINT F_12 Foreign Key (c11_ID) REFERENCES Derby655t2 (c21_ID) ON DELETE CASCADE ON UPDATE NO ACTION");
        s.execute("CREATE TABLE Derby655t3(c31_ID BIGINT NOT NULL primary key)");
        s.execute("ALTER TABLE Derby655t2 ADD CONSTRAINT F_443 Foreign Key (c21_ID) REFERENCES Derby655t3(c31_ID) ON DELETE CASCADE ON UPDATE NO ACTION");

        ResultSet rs = dmd.getImportedKeys("", schema, "DERBY655T1");
        JDBC.assertDrainResults(rs, 1);
        
        s.execute("drop table Derby655t1");
        s.execute("drop table Derby655t2");
        s.execute("drop table Derby655t3");

        // 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.          
        if(JDBC.vmSupportsJDBC3()){
            s.execute("create procedure isReadO() language java external name " +
                    "'org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.isro'" +
            " parameter style java"); 
            s.execute("call isReadO()");
        }
    }
    /**
     * method used in testBugFixes, for testing nexted connection metadata
     * @throws SQLException
     */
    public static void isro() throws SQLException {
        DriverManager.getConnection(
        "jdbc:default:connection").getMetaData().isReadOnly();
    }

    /**
     * getColumns() used to fail with a truncation error if an auto-increment
     * column had a start value or an increment that was very large (that is,
     * when its CHAR representation exceeded 12 characters). DERBY-5274.
     *
     * @throws java.sql.SQLException
     */
    public void testGetColumns_DERBY5274() throws SQLException {
        // Disable auto-commit to allow easy cleanup.
        setAutoCommit(false);

        Statement s = createStatement();

        // Create a test table with an identity column whose start value and
        // increment are very large.
        final long bignum = 648518346341351400L;
        s.execute("create table derby5274(x bigint not null " +
                  "generated always as identity (" +
                  "start with " + bignum + ", increment by " + bignum + "))");

        // Expected values for various columns in the meta-data.
        String[][] expected = {
            {"TABLE_SCHEM", schema},
            {"TABLE_NAME", "DERBY5274"},
            {"COLUMN_NAME", "X"},
            {"COLUMN_DEF",
                "AUTOINCREMENT: start " + bignum + " increment " + bignum},
            {"IS_NULLABLE", "NO"},
        };

        // Get meta-data for the column in the test table. This used to fail
        // with a truncation error before DERBY-5274.
        ResultSet rs = getDMD().getColumns(null, null, "DERBY5274", null);

        // Verify that the returned meta-data looks right.
        assertTrue("No columns found", rs.next());
        for (String[] expected1 : expected) {
            String label = expected1[0];
            String expectedVal = expected1[1];
            assertEquals(label, expectedVal, rs.getString(label));
        }

        // There's only one column in the test table, so there should be no
        // more rows in the meta-data.
        JDBC.assertEmpty(rs);

        // Clean up.
        rollback();
    }
    
    /**
     * Dummy method to test getProcedureColumns
     *
     * @param a
     * @param b
     * @param k
     * @param c
     * @param e
     * @param d
     * @param f
     * @param g
     * @param j
     * @param i
     * @param l
     * @param h
     * @param T
     * @return the {@code j} byte array
     */
    public static byte[] getpc(
        String a, BigDecimal b, short c, byte d, short e,
        int f, long g, float h, double i, byte[] j, Date k, Time l, Timestamp T)
    {
        return j;
    }

    /**
     * Overload getpc to further test getProcedureColumns
     *
     * @param a
     * @param b
     */
    public static void getpc(int a, long[] b)
    {
    }

    /**
     * Overload getpc to further test getProcedureColumns
     * private method shouldn't be returned with alias, ok with procedure
     *
     * @param a
     * @param b
     */
    private static void getpc(int a, long b)
    {
    }

    /** 
     * instance method for getProcedureColumns testing
     * with method alias, this should not be returned by getProcedureColumns
     * but DB2 returns this with a java procedure
     * @param a
     * @param b
     */ 
    public void getpc(String a, String b) {
    }

    /**
     * this method should notbe seen by getProcedureColumns as
     * it has no parameters and no return value.
     */
    public static void getpc4a() {
    }

    /**
     * Check a method with no parameters and a return value works
     * for getProcedureColumns
     *
     * @return the int 4
     */
    public static int getpc4b() {
        return 4;
    }

    /**
     * Reading of DatabaseMetaData obtained earlier, after a connection
     * is closed.
     *
     * @throws java.sql.SQLException
     */
    public void testDMDconnClosed() throws SQLException {
        ResultSet rs_ = getConnection().getMetaData().
        getTables("%","%","%",null); // should work
        getConnection().close();
        try {
            //should throw exception since the connection is closed
            rs_.next();
            fail("No Exception throw when getting metadata.");
        } catch(SQLException sqle) {
            assertSQLState("XCL16", sqle);
        }
    }

    /**
     * <p>
     * Get the version of the data in the database.
     * </p>
     * @param conn the current connection
     * @return data base version
     * @throws java.lang.Exception
     */
    @SuppressWarnings({"BroadCatchBlock", "TooBroadCatch", "UseSpecificCatch"})
    private Version getDataVersion( Connection conn )
        throws Exception
    {
        PreparedStatement ps = null;
        ResultSet               rs = null;

        try {
            ps = conn.prepareStatement( "values syscs_util.syscs_get_database_property('DataDictionaryVersion')" );
            rs = ps.executeQuery();

            rs.next();

            String retval = rs.getString( 1 );
            int  dotIdx = retval.indexOf( '.' );
            int major = Integer.parseInt( retval.substring( 0, dotIdx ) );
            int minor = Integer.parseInt( retval.substring( dotIdx + 1, retval.length() ) );

            return new Version( major, minor, 0, 0 );
        }
        catch (Exception se)
        {
            printStackTrace( se );
            return null;
        }
        finally
        {
            if ( rs != null ) { rs.close(); }
            if ( ps != null ) { ps.close(); }
        }
    }
}