/*
   Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.AutoGenJDBC30Test
   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 governing permissions and limitations
   under the License.
 */

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

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;

/**
 * Tests the JDBC 3.0 ability to establish a result set of auto-generated keys.
 * <p>
 * Converts the old jdbcapi/autoGeneratedJdbc30.java test to JUnit.
 * The old harness test number is preserved in the comment for each fixture.
 */
public class AutoGenJDBC30Test extends BaseJDBCTestCase {

    /**
     * Routines that should be created before the tests are run.
     */
    private static final String[] ROUTINES = {
        // Used by testInsertNoAutoGenExecuteSQLfunc
        "CREATE FUNCTION MMWNI() RETURNS VARCHAR(20) " +
        "READS SQL DATA LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '" +
        AutoGenJDBC30Test.class.getName() + ".MyMethodWithNoInsert'",

        // Used by testInsertAutoGenExecuteSQLfunc
        "CREATE FUNCTION AddMe(P1 INT) RETURNS INT " +
        "READS SQL DATA LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '" +
        AutoGenJDBC30Test.class.getName() + ".addMe'",
    };

    /**
     * Tables that should be created before the tests are run.
     * The first element in each row is the name of the table and the second 
     * element is the SQL text that creates it.
     */
    private static final String[][] TABLES = {
        
        { "t11_AutoGen", 
          "create table t11_AutoGen (c11 int, " +
          "c12 int generated always as identity (increment by 1))" },

        { "t31_AutoGen",
          "create table t31_AutoGen (c31 int, " +
          "c32 int generated always as identity (increment by 1), " +
          "c33 int default 2)" },

        { "t21_noAutoGen",
          "create table t21_noAutoGen (c21 int not null unique, c22 char(5))" },

        { "t21_feed_table",
          "create table t21_feed_table (c21 int not null unique, c22 char(5))"},
    };

    /**
     * Creates a new AutoGenJDBC30Test instance.
     *
     * @param name name of the test
     */
    public AutoGenJDBC30Test(String name) {
        super(name);
    }

    /**
     * Implements suite() to run in embedded and client configurations.
     */
    public static Test suite() {
        BaseTestSuite suite = new BaseTestSuite("AutoGenJDBC30Test");

        suite.addTest(baseSuite("AutoGenJDBC30Test:embedded"));

        suite.addTest(TestConfiguration.clientServerDecorator(
            baseSuite("AutoGenJDBC30Test:client")));
        return suite;
    }

    /**
     * Tests are only run if JDBC 3.0 available, and database objects get 
     * created only once for the suite run.
     *
     * @param name name of the test
     */
    private static Test baseSuite(String name) {

        BaseTestSuite suite = new BaseTestSuite(name);

        if (!JDBC.vmSupportsJDBC3()) {
            // empty suite
            return suite;
        }

        suite.addTestSuite(AutoGenJDBC30Test.class);

        // Create database objects only once for entire test run
        return new CleanDatabaseTestSetup(suite) 
        {
            /**
            * Creates the database objects used in the test cases.
            * @throws SQLException 
            */
            protected void decorateSQL(Statement s) throws SQLException
            {
                for (int i = 0; i < ROUTINES.length; i++) {
                    s.execute(ROUTINES[i]);
                }
                for (int i = 0; i < TABLES.length; i++) {
                    s.execute(TABLES[i][1]);
                }
            }
        };
    } // End baseSuite

    /**
     * Sets up the connection for a test case, clears all tables and resets
     * all auto-generated keys used by the test fixtures.
     * @throws SQLException 
     */
    public void setUp() throws SQLException
    {
        Connection conn = getConnection();
        conn.setAutoCommit(false);
        Statement s = createStatement();
        for (int i = 0; i < TABLES.length; i++) {
            s.execute("DELETE FROM " + TABLES[i][0]);
        }
        s.execute("ALTER TABLE t11_AutoGen ALTER COLUMN c12 RESTART WITH 1");
        s.execute("ALTER TABLE t31_AutoGen ALTER COLUMN c32 RESTART WITH 1");
        s.close();
        conn.commit();
    }

    // TESTS

    /**
     * Requests generated keys for a new statement that hasn't executed any 
     * SQL yet. 
     * Old harness Test 1.
     * Expected result: a NULL ResultSet.
     * @throws SQLException 
     */
    public void testNoSql() throws SQLException
    {
        Statement s = createStatement();
        assertNull("Expected NULL ResultSet", s.getGeneratedKeys());
        s.close();
    }

    /**
     * Requests generated keys for a Select statement (non-insert).
     * Old harness Test 2.
     * Expected result: a NULL ResultSet.
     * @throws SQLException 
     */
    public void testSelect() throws SQLException
    {
        String sql="select * from t11_AutoGen";

        Statement s = createStatement();
        s.execute(sql, Statement.RETURN_GENERATED_KEYS);
        assertNull("Expected NULL ResultSet after s.execute()", 
            s.getGeneratedKeys());

        s.close();

        PreparedStatement ps = 
            prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.execute();
        assertNull("Expected NULL ResultSet after ps.execute()", 
            ps.getGeneratedKeys());

        ps.close();
    }

    /**
     * Requests generated keys for a multi-row insert statement.
     * Old harness Test 3.
     * Expected result: ResultSet has one row with a NULL key because it 
     * inserts more than one row and there was no prior one-row insert into 
     * a table with an auto-generated key.
     * @throws SQLException 
     */
    public void testInsertManyRowsNoPriorKey() throws SQLException
    {
        String sqlStmt="insert into t31_AutoGen(c31) values (99), (98), (97)";
        runInsertFourWaysKeyIsNull (sqlStmt);
    }

    /**
     * Requests generated keys for a multi-row update statement after a
     * one-row update into a table with an auto-generated key.
     * Expected result: ResultSet has one row with a non-NULL key for the
     * one-row update.
     * @throws SQLException 
     */
    public void testUpdateManyRowsAfterOneRowKey() throws SQLException
    {
        // Do a one-row insert into a table with an auto-generated key.
        Statement s = createStatement();
        s.execute("insert into t11_AutoGen(c11) values (99)", Statement.RETURN_GENERATED_KEYS);
        int expected=1;
        int keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.execute()", expected, keyval);
        
        // Do a one-row update of a table with an auto-generated key.
        s.execute("update t11_AutoGen set c12=default where c11=99", Statement.RETURN_GENERATED_KEYS);
        expected=2;
        keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.execute()", expected, keyval);
    	
        String sql="insert into t11_AutoGen(c11) values (99), (98), (97)";
        s.execute(sql, Statement.RETURN_GENERATED_KEYS);
        keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.execute()", expected, keyval);

        // Do a one-row update of a table with an auto-generated key.
        s.execute("update t11_AutoGen set c12=default where c11=97", Statement.RETURN_GENERATED_KEYS);
        expected=6;
        keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.execute()", expected, keyval);
        
        // Do a multi-row update of a table with an auto-generated key.
        s.execute("update t11_AutoGen set c12=default where c11=99", Statement.RETURN_GENERATED_KEYS);
        keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.execute()", expected, keyval);
    }

    /**
     * Requests generated keys for a multi-row insert statement after a
     * one-row insert into a table with an auto-generated key.
     * Old harness Test 7.
     * Expected result: ResultSet has one row with a non-NULL key for the
     * one-row insert.
     * @throws SQLException 
     */
    public void testInsertManyRowsAfterOneRowKey() throws SQLException
    {
        // Do a one-row insert into a table with an auto-generated key.
        Statement s = createStatement();
        s.execute("insert into t11_AutoGen(c11) values (99)");

        /* Although the insert into t31_AutoGen below inserts into a table 
         * with an auto-generated column, it won't increment the key from 1 
         * to 2 because it's a multi-row insert.  Instead, the key it fetches
         * will be for the previous insert into t11_AutoGen.
         */
        int expected=1;
        String sql="insert into t31_AutoGen(c31) values (99), (98), (97)";

        s.execute(sql, Statement.RETURN_GENERATED_KEYS);
        int keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.execute()", expected, keyval);

        s.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
        keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.executeUpdate()", expected, keyval);

        s.close();

        PreparedStatement ps = 
            prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.execute();
        keyval = getKeyValue (ps.getGeneratedKeys());
        assertEquals("Key value after ps.execute()", expected, keyval);

        ps = prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.executeUpdate();
        keyval = getKeyValue (ps.getGeneratedKeys());
        assertEquals("Key value after ps.executeUpdate()", expected, keyval);

        ps.close();
    }

    /**
     * Requests generated keys after doing an insert into a table that doesn't
     * have a generated column (and there hasn't been a one row insert into 
     * a table with auto-generated keys yet).
     * Old harness Test 4.
     * Expected result: ResultSet has one row with a NULL key.
     * @throws SQLException 
     */
    public void testInsertNoAutoGen() throws SQLException
    {
        // The original test inserted 21 and 22.
        Statement s = createStatement();
        s.execute("insert into t21_noAutoGen values(21, 'true')");
        s.execute("insert into t21_noAutoGen values(22, 'true')");

        s.execute("insert into t21_noAutoGen values(23, 'true')", 
            Statement.RETURN_GENERATED_KEYS);
        verifyNullKey("s.execute()", s.getGeneratedKeys());

        s.executeUpdate("insert into t21_noAutoGen values(24, 'true')",
            Statement.RETURN_GENERATED_KEYS);
        verifyNullKey("s.executeUpdate()", s.getGeneratedKeys());

        s.close();

        PreparedStatement ps = prepareStatement(
            "insert into t21_noAutoGen values(25, 'true')", 
            Statement.RETURN_GENERATED_KEYS);
        ps.execute();
        verifyNullKey("PreparedStatement.execute()", ps.getGeneratedKeys());

        ps = prepareStatement("insert into t21_noAutoGen values(26, 'true')", 
            Statement.RETURN_GENERATED_KEYS);
        ps.executeUpdate();
        verifyNullKey("ps.executeUpdate()", ps.getGeneratedKeys());

        ps.close();
    }

    /**
     * <p>
     * Regression test for DERBY-5823 where the temporary row holder code
     * failed when switching from an in-memory to an on-disk representation.
     * </p>
     *
     * <p>
     * Note that ideally the transition should never have happened in the first
     * place, so this test verifies that either the transition logic can deal
     * with the degenerate case where the row template is zero-length, or the
     * insert code is smart enough to understand that there are no
     * auto-generated keys for the query.
     * </p>
     */
    public void testDerby5823() throws SQLException {
        setAutoCommit(false);
        PreparedStatement ps = prepareStatement(
                "insert into t21_feed_table values (?,?)");
        ps.setString(2, "false");
        // Just make sure we exceed the threshold for when the temporary row
        // holder overflows to disk (implementation detail).
        // When this test was written the threshold was five (5).
        for (int i=0; i < 250; i++) {
            ps.setInt(1, i);
            ps.executeUpdate();
        }
        commit();
        setAutoCommit(true);
        final String insertSql =
                "insert into t21_noAutoGen select * from t21_feed_table";
        // No keys will be auto-generated by the insert query.
        Statement s = createStatement();
        s.execute(insertSql,
                Statement.RETURN_GENERATED_KEYS
                );
        verifyNullKey("s.execute()", s.getGeneratedKeys());
        // For good measure we also test with a prepared statement.
        s.execute("delete from t21_noAutoGen");
        // Again, no keys will be auto-generated by the insert query.
        ps = prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
        ps.executeUpdate();
        verifyNullKey("ps.executeUpdate()", ps.getGeneratedKeys());
    }

    /**
     * Requests generated keys after doing a one-row insert into a table that 
     * has a generated column, but the insert is via a subquery with no where
     * clause.
     * Old harness Test 5a.
     * Expected result: ResultSet has one row with a NULL key.
     * @throws SQLException 
     */
    public void testInsertSubqueryNoWhereClause() throws SQLException
    {
        // Setup
        Statement s = createStatement();
        s.execute("insert into t21_noAutoGen values(21, 'true')");
        s.close();

        String sql="insert into t11_AutoGen(c11) select c21 from t21_noAutoGen";
        runInsertFourWaysKeyIsNull (sql);
    }

    /**
     * Requests generated keys after doing a one-row insert into a table 
     * that has a generated column, but the insert is via a subquery with
     * a "where 1=2" clause.
     * Old harness Test 5B.
     * Expected result: ResultSet has one row with a NULL key.
     * @throws SQLException 
     */
    public void testInsertSubqueryWhere1is2() throws SQLException
    {
        // Setup
        Statement s = createStatement();
        s.execute("insert into t21_noAutoGen values(21, 'true')");
        s.close();

        String sql = 
            "insert into t11_AutoGen(c11) select c21 from t21_noAutoGen " +
            "where 1=2";
        runInsertFourWaysKeyIsNull (sql);
    }

    /**
     * Requests generated keys after doing a one-row insert into a table 
     * that has a generated column, but the insert is via a subquery with
     * a "where c21=23" clause.
     * Old harness Test 5c.
     * Expected result: ResultSet with one row with a NULL key.
     * @throws SQLException 
     */
    public void testInsertSubqueryWhereClause() throws SQLException
    {
        // Setup
        Statement s = createStatement();
        s.execute("insert into t21_noAutoGen(c21,c22) values(23, 'true')");
        s.close();

        String sql=
            "insert into t11_AutoGen(c11) select c21 from t21_noAutoGen " +
            "where c21=23";
        runInsertFourWaysKeyIsNull (sql);
    }

    /**
     * Requests generated keys after doing a one-row insert into a table 
     * that has an auto-generated column.
     * Old harness Test 6.
     * Expected result: ResultSet has one row with a non-NULL key.
     * @throws SQLException 
     */
    public void testInsertOneRowKey() throws SQLException
    {
        String sql="insert into t11_AutoGen(c11) values (99)";

        Statement s = createStatement();

        s.execute(sql, Statement.RETURN_GENERATED_KEYS);
        int keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.execute()", 1, keyval);

        s.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
        keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.executeUpdate()", 2, keyval);

        s.close();

        PreparedStatement ps = 
            prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.execute();
        keyval = getKeyValue (ps.getGeneratedKeys());
        assertEquals("Key value after ps.execute()", 3, keyval);

        ps = prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.executeUpdate();
        keyval = getKeyValue (ps.getGeneratedKeys());
        assertEquals("Key value after ps.executeUpdate()", 4, keyval);

        ps.close();
        
        /*
        DERBY-3249 - Returned generated key result sets have the wrong
        concurrency. Test should be expanded to handle all concurrencies/types.
        
        // Test the type of the Statement object does not affect the
        // type of the generated key ResultSet (checked in getKeyValue)
        s = this.createStatement(ResultSet.CONCUR_UPDATABLE, ResultSet.TYPE_SCROLL_INSENSITIVE);
        s.execute(sql, Statement.RETURN_GENERATED_KEYS);
        keyval = getKeyValue(s.getGeneratedKeys());
        assertEquals("Key value after s.execute()", 5, keyval);
        */
    }


    /**
     * After a one-row insert into a table with an auto-generated key, next
     * inserts into a table that does not have an auto-generated key, then
     * requests generated keys.
     * Old harness Test 8.
     * Expected result: ResultSet has one row with a non-NULL key. All four
     * queries in this test return the same result because they fetch the
     * key generated for the previous insert, not the current one.
     * @throws SQLException 
     */
    public void testInsertNoGenColAfterOneRowKey() throws SQLException
    {
        // Do a one-row insert into a table with an auto-generated key.
        Statement s = createStatement();
        s.execute("insert into t11_AutoGen(c11) values (99)");

        /* The insert into t21_noAutoGen below doesn't insert into a table 
         * with an auto-generated column, so it won't increment the key from 
         * 1 to 2.  The key it fetches will be for the previous insert into 
         * t11_AutoGen.
         */
        int expected=1;

        s.execute("insert into t21_noAutoGen values(27, 'true')", 
            Statement.RETURN_GENERATED_KEYS);
        int keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.execute()", expected, keyval);

        s.executeUpdate("insert into t21_noAutoGen values(28, 'true')",
            Statement.RETURN_GENERATED_KEYS);
        keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.executeUpdate()", expected, keyval);

        s.close();

        PreparedStatement ps = prepareStatement(
            "insert into t21_noAutoGen values(29, 'true')", 
            Statement.RETURN_GENERATED_KEYS);
        ps.execute();
        keyval = getKeyValue (ps.getGeneratedKeys());
        assertEquals("Key value after ps.execute()", expected, keyval);

        ps = prepareStatement("insert into t21_noAutoGen values(30, 'true')", 
            Statement.RETURN_GENERATED_KEYS);
        ps.executeUpdate();
        keyval = getKeyValue (ps.getGeneratedKeys());
        assertEquals("Key value after ps.executeUpdate()", expected, keyval);

        ps.close();
    }

    /**
     * Requests generated keys for an UPDATE statement.
     * Old harness Test 9.
     * Expected result: a NULL ResultSet.
     * @throws SQLException 
     */
    public void testUpdateOneRowKey() throws SQLException
    {
        Statement s = createStatement();
        s.execute("insert into t11_AutoGen(c11) values(999)");

        String sqlStmt="update t11_AutoGen set c12=default where c11=999";
        s.execute(sqlStmt, Statement.RETURN_GENERATED_KEYS);
        int keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.execute()", 2, keyval);

        s.executeUpdate(sqlStmt, Statement.RETURN_GENERATED_KEYS);
        keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.executeUpdate()", 3, keyval);

        s.close();

        PreparedStatement ps = prepareStatement(
            sqlStmt, Statement.RETURN_GENERATED_KEYS);
        ps.execute();
        keyval = getKeyValue (ps.getGeneratedKeys());
        assertEquals("Key value after ps.execute()", 4, keyval);

        ps = prepareStatement(sqlStmt, Statement.RETURN_GENERATED_KEYS);
        ps.executeUpdate();
        keyval = getKeyValue (ps.getGeneratedKeys());
        assertEquals("Key value after ps.executeUpdate()", 5, keyval);

        ps.close();
    }

    /**
     * Requests generated keys for an DELETE statement.
     * Old master Test 10.
     * Expected result: a NULL ResultSet.
     * @throws SQLException 
     */
    public void testDelete() throws SQLException
    {
        Statement s = createStatement();
        s.execute("insert into t11_AutoGen(c11) values(999)");

        String sqlStmt="delete from t11_AutoGen";
        s.execute(sqlStmt, Statement.RETURN_GENERATED_KEYS);
        assertNull("Expected NULL ResultSet after s.execute()",
            s.getGeneratedKeys());

        s.executeUpdate(sqlStmt, Statement.RETURN_GENERATED_KEYS);
        assertNull("Expected NULL ResultSet after s.executeUpdate()", 
            s.getGeneratedKeys());

        s.close();

        PreparedStatement ps = prepareStatement(
            sqlStmt, Statement.RETURN_GENERATED_KEYS);
        ps.execute();
        assertNull("Expected NULL ResultSet after ps.execute()", 
            ps.getGeneratedKeys());

        ps.executeUpdate();
        assertNull("Expected NULL ResultSet after ps.executeUpdate()", 
            ps.getGeneratedKeys());

        ps.close();
    }

    /**
     * Does a one-row insert into a table with a generated column, commits,
     * then requests generated keys for an insert into a table without a
     * generated column.
     * Old master Test 11.
     * Expected result: ResultSet has one row with a non-NULL key.
     * The original code output this message: "expected to see resultset with 
     * one row of NULL value but instead get one row of non-NULL value from 
     * getGeneratedKeys".
     * @throws SQLException 
     */
    public void testGetKeyAfterCommit() throws SQLException
    {
        // Setup transaction
        Statement s = createStatement();
        s.execute("insert into t11_AutoGen(c11) values(999)");

        Connection conn = getConnection();
        conn.commit();

        /* The insert into t21_noAutoGen below doesn't insert into a table 
         * with an auto-generated column, so it won't increment the key from 
         * 1 to 2.  The key it fetches will be for the previous insert into 
         * t11_AutoGen.
         */
        int expected=1;
        s.execute("insert into t21_noAutoGen values(31, 'true')", 
            Statement.RETURN_GENERATED_KEYS);
        int keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.execute()", expected, keyval);

        s.executeUpdate("insert into t21_noAutoGen values(32, 'true')",
            Statement.RETURN_GENERATED_KEYS);
        keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.executeUpdate()", expected, keyval);

        s.close();

        PreparedStatement ps = prepareStatement(
            "insert into t21_noAutoGen values(33, 'true')", 
            Statement.RETURN_GENERATED_KEYS);
        ps.execute();
        keyval = getKeyValue (ps.getGeneratedKeys());
        assertEquals("Key value after ps.execute()", expected, keyval);

        ps = prepareStatement("insert into t21_noAutoGen values(34, 'true')", 
            Statement.RETURN_GENERATED_KEYS);
        ps.executeUpdate();
        keyval = getKeyValue (ps.getGeneratedKeys());
        assertEquals("Key value after ps.executeUpdate()", expected, keyval);

        ps.close();
    }

    /**
     * Does a one-row insert into a table with a generated column, next does
     * a rollback, then requests generated keys for an insert into a table 
     * without a generated column.
     * Old master Test 12.
     * Expected result: ResultSet has one row with a non-NULL key.
     * The original code output this message: "had expected to see resultset 
     * with one row of NULL value but instead get one row of non-NULL value 
     * from getGeneratedKeys".
     * @throws SQLException 
     */
    public void testGetKeyAfterRollback() throws SQLException
    {
        Connection conn = getConnection();
        Statement s = createStatement();

        s.execute("insert into t11_AutoGen(c11) values(999)");
        conn.rollback();

        /* The insert into t21_noAutoGen below doesn't insert into a table 
         * with an auto-generated column, so it won't increment the key from 
         * 1 to 2.  The key it fetches will be for the previous insert into 
         * t11_AutoGen, a value that never changes in this fixture.
         */
        int expected=1;

        s.execute("insert into t21_noAutoGen values(35, 'true')", 
            Statement.RETURN_GENERATED_KEYS);
        int keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.execute()", expected, keyval);

        s.executeUpdate("insert into t21_noAutoGen values(36, 'true')",
            Statement.RETURN_GENERATED_KEYS);
        keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.executeUpdate()", expected, keyval);

        s.close();

        PreparedStatement ps = prepareStatement(
            "insert into t21_noAutoGen values(37, 'true')", 
            Statement.RETURN_GENERATED_KEYS);
        ps.execute();
        keyval = getKeyValue (ps.getGeneratedKeys());
        assertEquals("Key value after ps.execute()", expected, keyval);

        ps = prepareStatement("insert into t21_noAutoGen values(38, 'true')", 
            Statement.RETURN_GENERATED_KEYS);
        ps.executeUpdate();
        keyval = getKeyValue (ps.getGeneratedKeys());
        assertEquals("key value after ps.executeUpdate()", expected, keyval);

        ps.close();
    }

    /**
     * Inserts one row into a table with an auto-generated column while inside
     * a savepoint unit, does a rollback, then gets keys after an insert
     * into a table without an auto-generated column.
     * Old master Test 13.
     * Expected result: ResultSet has one row with a non-NULL key, and the
     * key value should be the same before and after the rollback.
     * @throws SQLException 
     */
    public void testGetKeyAfterSavepointRollback() throws SQLException
    {
        Connection conn = getConnection();
        Statement s = createStatement();
        Savepoint savepoint1 = conn.setSavepoint();

        int expected=1;

        s.execute("insert into t11_AutoGen(c11) values(99)", 
            Statement.RETURN_GENERATED_KEYS);
        int keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value before rollback", expected, keyval);

        conn.rollback(savepoint1);

        s.execute("insert into t21_noAutoGen values(39, 'true')",
            Statement.RETURN_GENERATED_KEYS);
        keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after rollback", expected, keyval);

        s.close();
    }

    /**
     * Inserts one row into a table with an auto-generated column, then 
     * examines the metadata for the generatedKeys ResultSet.
     * Old master Test 14.
     * @throws SQLException 
     */
    public void testGetKeyMetadataAfterInsert() throws SQLException
    {
        Statement s = createStatement();

        s.execute("insert into t31_AutoGen(c31) values (99)", 
            Statement.RETURN_GENERATED_KEYS);
        ResultSet rs = s.getGeneratedKeys();
        ResultSetMetaData rsmd = rs.getMetaData();
        assertEquals("ResultSet column count", 1, rsmd.getColumnCount());
        assertEquals("Column type", "DECIMAL", rsmd.getColumnTypeName(1));
        assertEquals("Column precision", 31, rsmd.getPrecision(1));
        assertEquals("Column scale", 0, rsmd.getScale(1));
        int keyval = getKeyValue (rs);
        assertEquals("Key value", 1, keyval);

        rs.close();
        s.close();
    }

    /**
     * Inserts one row into a table with an auto-generated column, but 
     * with NO_GENERATED_KEYS.
     * Old master Test 15.
     * Expected result: NULL ResultSet.
     * @throws SQLException 
     */
    public void testInsertNoGenKeys() throws SQLException
    {
        Statement s = createStatement();

        String sql="insert into t31_AutoGen(c31) values (99)";

        s.execute(sql, Statement.NO_GENERATED_KEYS);
        assertNull("Expected NULL ResultSet after s.execute()", 
            s.getGeneratedKeys());

        s.executeUpdate(sql, Statement.NO_GENERATED_KEYS);
        assertNull("Expected NULL ResultSet after s.executeUpdate", 
            s.getGeneratedKeys());

        s.close();

        PreparedStatement ps = 
            prepareStatement(sql, Statement.NO_GENERATED_KEYS);
        ps.execute();
        assertNull("Expected NULL ResultSet after ps.execute()",
            ps.getGeneratedKeys());

        ps = prepareStatement(sql, Statement.NO_GENERATED_KEYS);
        ps.executeUpdate();
        assertNull("Expected NULL ResultSet after ps.executeUpdate", 
            ps.getGeneratedKeys());

        ps.close();
    }

    /**
     * Inserts one row into a table with an auto-generated column, but 
     * in the JDBC 2.0 way (with no generated key feature).
     * Old master Test 16.
     * Expected result: NULL ResultSet.
     * @throws SQLException 
     */
    public void testInsertJDBC20syntax() throws SQLException
    {
        Statement s = createStatement();

        String sql="insert into t31_AutoGen(c31) values (99)";

        s.execute(sql);
        assertNull("Expected NULL ResultSet after s.execute()", 
            s.getGeneratedKeys());

        s.executeUpdate(sql);
        assertNull("Expected NULL ResultSet after s.executeUpdate", 
            s.getGeneratedKeys());

        s.close();

        PreparedStatement ps = prepareStatement(sql);
        ps.execute();
        assertNull("Expected NULL ResultSet after ps.execute()",
            ps.getGeneratedKeys());

        ps = prepareStatement(sql);
        ps.executeUpdate();
        assertNull("Expected NULL ResultSet after ps.executeUpdate", 
            ps.getGeneratedKeys());

        ps.close();
    }

    /**
     * Updates a row in a table with an auto-generated column and 
     * NO_GENERATED_KEYS, then fetches key.
     * Old master Test 17.
     * Expected result: NULL ResultSet.
     * @throws SQLException 
     */
    public void testUpdateAutoGenNoGenKeys() throws SQLException
    {
        Statement s = createStatement();

        // Insert a row for us to update
        s.execute("insert into t31_AutoGen(c31) values (99)");

        String sql="update t31_AutoGen set c31=98";

        s.execute(sql, Statement.NO_GENERATED_KEYS);
        assertNull("Expected NULL ResultSet after s.execute()", 
            s.getGeneratedKeys());

        s.executeUpdate(sql, Statement.NO_GENERATED_KEYS);
        assertNull("Expected NULL ResultSet after s.executeUpdate", 
            s.getGeneratedKeys());

        s.close();

        PreparedStatement ps=prepareStatement(sql, Statement.NO_GENERATED_KEYS);
        ps.execute();
        assertNull("Expected NULL ResultSet after ps.execute()",
            ps.getGeneratedKeys());

        ps = prepareStatement(sql, Statement.NO_GENERATED_KEYS);
        ps.executeUpdate();
        assertNull("Expected NULL ResultSet after ps.executeUpdate", 
            ps.getGeneratedKeys());

        ps.close();
    }

    /**
     * Deletes rows from a table with an auto-generated column in the JDBC 2.0 
     * way (with no generated key feature), then fetches key.
     * Old master Test 18.
     * Expected result: NULL ResultSet.
     * @throws SQLException 
     */
    public void testDeleteAutoGenNoGenKeysJDBC20syntax() throws SQLException
    {
        Statement s = createStatement();

        String sql="delete from t31_AutoGen";

        s.execute(sql);
        assertNull("Expected NULL ResultSet after s.execute()", 
            s.getGeneratedKeys());

        s.executeUpdate(sql);
        assertNull("Expected NULL ResultSet after s.executeUpdate", 
            s.getGeneratedKeys());

        s.close();

        PreparedStatement ps=prepareStatement(sql);
        ps.execute();
        assertNull("Expected NULL ResultSet after ps.execute()",
            ps.getGeneratedKeys());

        ps = prepareStatement(sql);
        ps.executeUpdate();
        assertNull("Expected NULL ResultSet after ps.executeUpdate", 
            ps.getGeneratedKeys());

        ps.close();
    }

    /**
     * Inserts a row into a table with a SQL function in the VALUES clause;
     * the table does not have an auto-generated column.
     * Old master Test 19.
     * Expected result: ResultSet has one row. The key value is NULL if 
     * there has been no prior insert into a table with an auto-generated 
     * column; otherwise, the value is not NULL. 
     * The old master referenced an old issue for which this test was added. 
     * getGeneratedKeys() threw an exception if an insert statement included a 
     * SQL routine and set the flag to generate a generatedKeys ResultSet.
     * @throws SQLException 
     */
    public void testInsertNoAutoGenExecuteSQLfunc() throws SQLException
    {
        Statement s = createStatement();

        // Insert into a table that does not have an auto-gen column.
        s.execute("insert into t21_noAutoGen values(40, MMWNI())",
            Statement.RETURN_GENERATED_KEYS);
        verifyNullKey("First insert", s.getGeneratedKeys());
        assertTableRowCount("T21_NOAUTOGEN", 1);

        // Now insert into a table that has an auto-gen column.
        s.execute("insert into t31_AutoGen(c31) values (99)",
            Statement.RETURN_GENERATED_KEYS);
        int keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after insert into t31_AutoGen", 1, keyval);

        // Insert again into the table that does not have an auto-gen column.
        s.execute("insert into t21_noAutoGen values(42, MMWNI())",
            Statement.RETURN_GENERATED_KEYS);
        keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after insert into t21_noAutoGen", 1, keyval);
        assertTableRowCount("T21_NOAUTOGEN", 2);

        s.close();
    }

    /**
     * Inserts a row into a table with a SQL function in the VALUES clause;
     * the table has an auto-generated column.
     * Old master: no test, but this seemed a natural addition given
     * testInsertNoAutoGenExecuteSQLfunc().
     * Expected result: ResultSet has one row with a non-NULL key value.
     * @throws SQLException 
     */
    public void testInsertAutoGenExecuteSQLfunc() throws SQLException
    {
        String sql="insert into t31_AutoGen(c31) values (AddMe(1))";

        Statement s = createStatement();

        s.execute(sql, Statement.RETURN_GENERATED_KEYS);
        int keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.execute()", 1, keyval);

        s.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
        keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.executeUpdate()", 2, keyval);

        s.close();

        PreparedStatement ps = 
            prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.execute();
        keyval = getKeyValue (ps.getGeneratedKeys());
        assertEquals("Key value after ps.execute()", 3, keyval);

        ps = prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.executeUpdate();
        keyval = getKeyValue (ps.getGeneratedKeys());
        assertEquals("Key value after ps.executeUpdate()", 4, keyval);

        ps.close();
    }

    /**
     * Verifies fix for an old issue in which the ResultSet returned by 
     * getGenerateKeys was incorrectly tied to the activation of the 
     * PreparedStatement; so, when the ResultSet was garbage collected, the
     * activation was closed, resulting in an "Activation closed, operation
     * execute not permitted" exception on subsequent executes (warning:
     * this fixture takes a noticeable time to run).
     * Old master Test 20.
     * Expected result: no exceptions should occur.
     * @throws SQLException 
     */
    public void testResultSetGarbageCollection() throws SQLException
    {
        Connection conn = getConnection();

        PreparedStatement ps = 
            prepareStatement("insert into t11_AutoGen(c11) values(?)", 
            Statement.RETURN_GENERATED_KEYS);

        for (int i = 0; i < 100; i++) 
        {
            ps.setInt(1, 100+i);
            ps.executeUpdate();

            ResultSet rs = ps.getGeneratedKeys();
            while (rs.next()) {
               rs.getInt(1);
            }
            rs.close();
            conn.commit();

            System.runFinalization();
            System.gc();
            System.runFinalization();
            System.gc();
        }
    }

    /**
     * Test that use of columnIndexes to indicate which keys should be
     * made available works as expected.
     *
     * @throws SQLException 
     */
    public void testColumnIndexes() throws SQLException
    {

        // Valid (typical) usage.

        int [] colIndexes = new int [] { 2 };
        testUserGivenColumns(colIndexes, null, 1);
        

        // Mulitple columns. one not an identity column.
        colIndexes = new int[] {1,2};
        testUserGivenColumnsError(colIndexes, null);
        
        
        // Derby client can't differentiate between
        // valid and invalid identity columns. So the
        // other tests do not apply.  
        if (usingDerbyNetClient())
            return;

        // Non-existent column index.
        colIndexes = new int[] {100};
        testUserGivenColumnsError(colIndexes, null);

        // Valid column index but not an auto-gen column.

        colIndexes[0] = 1;
        testUserGivenColumnsError(colIndexes, null);

        /* If user specifies the same column index multiple times,
         * things should still work.  We effectively just take the
         * one and ignore the rest.
         */

        colIndexes = new int [] { 2, 2, 2 };
        testUserGivenColumns(colIndexes, null, 5);

        // Multiple col indexes, one of which is invalid.

        colIndexes[1] = 100;
        testUserGivenColumnsError(colIndexes, null);


        /* Multiple col indexes, one of which is invalid and another
         * of which is not an auto-gen column.
         */

        colIndexes[2] = 100;
        testUserGivenColumnsError(colIndexes, null);

        // Same as previous but with "bad" indexes switched.

        colIndexes[1] = 100;
        colIndexes[2] = 1;
        testUserGivenColumnsError(colIndexes, null);
    }

    /**
     * Test that use of columnNames to indicate which keys should be
     * made available works as expected.
     *
     * @throws SQLException 
     */
    public void testColumnNames() throws SQLException
    {
    
        // Valid (typical) usage.

        String [] colNames = new String [] { "C12" };
        testUserGivenColumns(null, colNames, 1);

        // column name array is of length > 1
        colNames = new String[] {"C12","C13"};
        testUserGivenColumnsError(null, colNames);
             
        if (usingDerbyNetClient())
            return;
       
        // Non-existent column name.

        colNames= new String[] {"NOTTHERE"};
        testUserGivenColumnsError(null, colNames);
        
        // Valid column name but not an auto-gen column.

        colNames[0] = "C11";
        testUserGivenColumnsError(null, colNames);

        // "null" column name.

        colNames[0] = null;
        testUserGivenColumnsError(null, colNames);

        /* If user specifies the same column name multiple times,
         * things should still work.  We effectively just take the
         * one and ignore the rest.
         */

        colNames = new String [] { "C12", "C12", "C12" };
        testUserGivenColumns(null, colNames, 5);

        // Multiple col names, one of which is invalid.

        colNames[1] = "NOTTHERE";
        testUserGivenColumnsError(null, colNames);

        // Multiple col names, one of which is not an auto-gen column.

        colNames[1] = "C11";
        testUserGivenColumnsError(null, colNames);

        // Multiple col names, one of which is null.

        colNames[1] = null;
        testUserGivenColumnsError(null, colNames);

        /* Multiple col names, one of which is invalid and another
         * of which is not an auto-gen column.
         */

        colNames[1] = "C11";
        colNames[2] = "NOTTHERE";
        testUserGivenColumnsError(null, colNames);

        // Same as previous but with "bad" names switched.

        colNames[1] = "NOTTHERE";
        colNames[2] = "C11";
        testUserGivenColumnsError(null, colNames);
    }

    /**
     * Verify that if user specifies an empty array for columNames or columnIndexes,
     * it is the same as NO_GENERATED_KEYS
     * @throws SQLException
     */
    public void testUserGivenColumnsEmpty() throws SQLException
    {
        Statement s = createStatement();

        String sql="insert into t11_AutoGen(c11) values (99)";

  
        s.execute(sql, new String[] {});
        assertNull("Expected NULL ResultSet after s.execute()", 
            s.getGeneratedKeys());

        s.executeUpdate(sql, new String[] {});
        assertNull("Expected NULL ResultSet after s.executeUpdate()", 
            s.getGeneratedKeys());

        PreparedStatement ps = null;
        
        ps = prepareStatement(sql, new String[] {});
        ps.execute();
        assertNull("Expected NULL ResultSet after ps.execute()", 
        ps.getGeneratedKeys());

        ps = prepareStatement(sql, new String[] {});
        ps.executeUpdate();
        assertNull("Expected NULL ResultSet after ps.executeUpdate()", 
                ps.getGeneratedKeys());       
       // No columnIndexes yet for derby client. 
       if (usingDerbyNetClient())
            return;
       
       s.execute(sql,  new int[] {});
       assertNull("Expected NULL ResultSet after s.execute()", 
           s.getGeneratedKeys());

       s.executeUpdate(sql, new int[] {});
       assertNull("Expected NULL ResultSet after s.executeUpdate()", 
           s.getGeneratedKeys());

       if (!usingEmbedded())
       {
           // Can't run these with embedded now because of DERBY-3430
           ps = prepareStatement(sql, new int[] {});
           ps.execute();
           assertNull("Expected NULL ResultSet after ps.execute()", 
                   ps.getGeneratedKeys());

           ps = prepareStatement(sql, new int[] {});
           ps.executeUpdate();
           assertNull("Expected NULL ResultSet after ps.executeUpdate()", 
                   ps.getGeneratedKeys());

       }
       
    
        
    }
    
    /**
     * Verify that if a user specifies a *NULL* column index or column
     * name array to indicate which keys should be made available, Derby will
     * effectively disable autogenerated keys (i.e. same as if user passed
     * NO_GENERATED_KEYS).
     *
     * Expected result: a NULL result set.
     * @throws SQLException 
     */
    public void testUserGivenColumnsNull() throws SQLException
    {
        
        Statement s = createStatement();

        String sql="insert into t11_AutoGen(c11) values (99)";

  
        s.execute(sql, (String[]) null);
        assertNull("Expected NULL ResultSet after s.execute()", 
            s.getGeneratedKeys());

        s.executeUpdate(sql, (String[]) null);
        assertNull("Expected NULL ResultSet after s.executeUpdate()", 
            s.getGeneratedKeys());


        PreparedStatement ps;
        ps = prepareStatement(sql, (String[]) null);
        ps.execute();
        assertNull("Expected NULL ResultSet after ps.execute()", 
            ps.getGeneratedKeys());

        ps = prepareStatement(sql, (String[]) null);
        ps.executeUpdate();
        assertNull("Expected NULL ResultSet after ps.executeUpdate()", 
            ps.getGeneratedKeys());
        
       // No columnIndexes yet for derby client. 
       if (usingDerbyNetClient())
            return;
       
       s.execute(sql, (int[]) null);
       assertNull("Expected NULL ResultSet after s.execute()", 
           s.getGeneratedKeys());

       s.executeUpdate(sql, (int[]) null);
       assertNull("Expected NULL ResultSet after s.executeUpdate()", 
           s.getGeneratedKeys());

       ps = prepareStatement(sql, (int[]) null);
        ps.execute();
        assertNull("Expected NULL ResultSet after ps.execute()", 
            ps.getGeneratedKeys());

        ps = prepareStatement(sql, (int[]) null);
        ps.executeUpdate();
        assertNull("Expected NULL ResultSet after ps.executeUpdate()", 
            ps.getGeneratedKeys());


        ps.close();
        
    }

    // Local utility methods.

    /**
     * Verify that if user specifies *valid* column indexes or column
     * names to indicate which keys should be made available, Derby will
     * return the correct results.
     *
     * Expected result: one row with a non-NULL key.
     *
     * @param colIndexes Array of column indexes indicating which keys
     *  should be made available.  Must be null if colNames is non-null.
     * @param colNames Array of column names indicating which keys should
     *  be made available.  Must be null if colIndexes is non-null.
     * @param expectedVal First expected autogenerated key; will be
     *  incremented for each successful INSERT statement.
     *
     * @throws SQLException
     */
    private void testUserGivenColumns(int [] colIndexes, String [] colNames,
        int expectedVal) throws SQLException
    {
        assertTrue("Exactly one of colIndexes or colNames should be null",
            ((colIndexes != null) ^ (colNames != null)));

        boolean useIndexes = (colIndexes != null);
        Statement s = createStatement();

        String sql="insert into t11_AutoGen(c11) values (99)";

        if (useIndexes)
            s.execute(sql, colIndexes);
        else
            s.execute(sql, colNames);

        int keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.execute()", expectedVal++, keyval);

        if (useIndexes)
            s.executeUpdate(sql, colIndexes);
        else
            s.executeUpdate(sql, colNames);

        keyval = getKeyValue (s.getGeneratedKeys());
        assertEquals("Key value after s.executeUpdate()",
            expectedVal++, keyval);

        s.close();

        PreparedStatement ps = null;
        if (useIndexes)
            ps = prepareStatement(sql, colIndexes);
        else
            ps = prepareStatement(sql, colNames);

        ps.execute();
        keyval = getKeyValue (ps.getGeneratedKeys());
        assertEquals("Key value after ps.execute()", expectedVal++, keyval);

        if (useIndexes)
            ps = prepareStatement(sql, colIndexes);
        else
            ps = prepareStatement(sql, colNames);

        ps.executeUpdate();
        keyval = getKeyValue (ps.getGeneratedKeys());
        assertEquals("Key value after ps.executeUpdate()",
            expectedVal++, keyval);

        ps.close();
    }

    /**
     * Verify that if user specifies *INvalid* column indexes or column
     * names to indicate which keys should be made available, Derby will
     * throw an appropriate error.
     *
     * Expected result: Execution-time error: X0X0E or X0X0F.
     *
     * @param colIndexes Array of column indexes indicating which keys
     *  should be made available.  Must be null if colNames is non-null.
     * @param colNames Array of column names indicating which keys should
     *  be made available.  Must be null if colIndexes is non-null.
     *
     * @throws SQLException 
     */
    private void testUserGivenColumnsError(int [] colIndexes,
        String [] colNames) throws SQLException
    {
        assertTrue("Exactly one of colIndexes or colNames should be null.",
            ((colIndexes != null) ^ (colNames != null)));

        boolean useIndexes = (colIndexes != null);
        String expectedSQLState = (useIndexes ? "X0X0E" : "X0X0F");
        // Derby client will only give an error if colNames array is not of length 1.
        if (usingDerbyNetClient() && colNames != null && 
                colNames.length != 1)
            expectedSQLState = "X0X0D";
        
        Statement s = createStatement();
        String sql="insert into t11_AutoGen(c11) values (99)";

        try {

            if (useIndexes)
                s.execute(sql, colIndexes);
            else
                s.execute(sql, colNames);

            fail("Expected s.execute() to fail, but it did not.");

        } catch (SQLException se) {
            assertSQLState(expectedSQLState, se.getSQLState(), se);
        }

        try {

            if (useIndexes)
                s.executeUpdate(sql, colIndexes);
            else
                s.executeUpdate(sql, colNames);

            fail("Expected s.executeUpdate() to fail, but it did not.");

        } catch (SQLException se) {
            assertSQLState(expectedSQLState, se.getSQLState(), se);
        }

        s.close();

        PreparedStatement ps = null;
        if (useIndexes)
               ps = prepareStatement(sql, colIndexes);
        else
               ps = prepareStatement(sql, colNames);

        try {
            ps.execute();
            fail("Expected ps.execute() to fail, but it did not.");
        } catch (SQLException se) {
            assertSQLState(expectedSQLState, se.getSQLState(), se);
        }

        try {
            ps.executeUpdate();
            fail("Expected ps.executeUpdate() to fail, but it did not.");
        } catch (SQLException se) {
            assertSQLState(expectedSQLState, se.getSQLState(), se);
        }

        ps.close();
    }

    /**
     * Runs the same SQL INSERT statement four ways: 
     *   Statement.execute, 
     *   Statement.executeUpdate, 
     *   PreparedStatement.execute, and 
     *   PreparedStatement.executeUpdate,
     * and expects the resulting key value to be NULL. 
     *
     * @param sql The SQL statement to be executed
     * @exception SQLException if a database error occurs
     */
    public void runInsertFourWaysKeyIsNull (String sql)
        throws SQLException
    {
        Statement s = createStatement();
        s.execute(sql, Statement.RETURN_GENERATED_KEYS);
        verifyNullKey("After s.execute()", s.getGeneratedKeys());

        s.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
        verifyNullKey("After s.executeUpdate()", s.getGeneratedKeys());

        s.close();

        PreparedStatement ps = 
            prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.execute();
        verifyNullKey("After ps.execute()", ps.getGeneratedKeys());

        ps = prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.executeUpdate();
        verifyNullKey("ps.executeUpdate()", ps.getGeneratedKeys());

        ps.close();
    }

    /**
     * Verifies that the generated key in a result set is null.
     *
     * @param description Text to be output for the assertion
     * @param r ResultSet
     * @exception SQLException if a database error occurs
     */
    public void verifyNullKey (String description, ResultSet r) 
        throws SQLException
    {
        JDBC.assertGeneratedKeyResultSet(description, r);

        int i = 0;
        while(r.next())
        {
            assertNull(description, r.getString(1));
            i++;
        }
        assertEquals(description, 1, i);
    }

    /**
     * Gets the key value from the result set.
     *
     * @param r ResultSet
     * @exception SQLException if a database error occurs
     */
    public int getKeyValue (ResultSet r) throws SQLException
    {if(r==null) System.out.println("it is null");
        JDBC.assertGeneratedKeyResultSet("AutoGenJDBC30Test.getKeyValue", r);
        
        int i = 0;
        int retval = 0;
        while(r.next())
        {
            assertNotNull("Key value is NULL", r.getString(1));
            retval = r.getInt(1);
            i++;
        }
        assertEquals("ResultSet rows", 1, i);
        return retval;
    }

    // SQL ROUTINES (functions and procedures)

    /** 
     * External code for the  MMWNI() SQL function, which is called by
     * the testInsertNoAutoGenExecuteSQLfunc fixture.
     * @exception SQLException if a database error occurs
     */

    public static String MyMethodWithNoInsert() throws SQLException 
    {
        Connection conn = 
            DriverManager.getConnection("jdbc:default:connection");
        Statement s = conn.createStatement();
        s.executeQuery("select * from t11_AutoGen");
        s.close();
        conn.close();
        return "true";
    }

    /** 
     * External code for the AddMe SQL function, which is called by
     * the testInsertAutoGenExecuteSQLfunc fixture.
     * @param p1 integer input argument to be used in calculation
     * @exception SQLException if a database error occurs
     */
    public static int addMe (int p1) throws SQLException
    {
        Connection conn = 
            DriverManager.getConnection("jdbc:default:connection");
        Statement s = conn.createStatement();
        s.executeQuery("select * from t11_AutoGen");
        s.close();
        conn.close();
        return (p1 + p1);
    }
}