db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From j..@apache.org
Subject svn commit: r520230 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting: functionTests/tests/jdbcapi/AutoGenJDBC30Test.java functionTests/tests/jdbcapi/_Suite.java functionTests/tests/jdbcapi/build.xml junit/BaseJDBCTestCase.java
Date Tue, 20 Mar 2007 02:32:07 GMT
Author: jta
Date: Mon Mar 19 19:32:06 2007
New Revision: 520230

URL: http://svn.apache.org/viewvc?view=rev&rev=520230
Log:
DERBY-2398 (partial): converts jdbcapi/autoGeneratedJdbc30.java to JUnit.

Converts all tests in autoGeneratedJdbc30.java to JUnit fixtures: 
- Unlike the original test, each fixture is a standalone test, so there's no
  longer a visible progression of incrementing keys like there was in the old
  master. Instead, the Setup() method resets keys to 1 before each fixture is 
  run.
- Runs in both embedded and client.
- An empty suite is returned if the VM doesn't support JDBC 3.0.
- Warning: the testResultSetGarbageCollection() fixture takes a noticeable
  amount of time to run.

Also adds a prepareStatement(String sql, int autoGeneratedKeys) utility method 
to BaseJDBCTestCase.java

To do: remove old harness files.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/AutoGenJDBC30Test.java
  (with props)
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/build.xml
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/AutoGenJDBC30Test.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/AutoGenJDBC30Test.java?view=auto&rev=520230
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/AutoGenJDBC30Test.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/AutoGenJDBC30Test.java
Mon Mar 19 19:32:06 2007
@@ -0,0 +1,1152 @@
+/*
+   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.Savepoint;
+import java.sql.Statement;
+import java.sql.SQLException;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.BaseJDBCTestSetup;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+/**
+ * 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))" },
+    };
+
+    /**
+     * 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() {
+        TestSuite suite = new TestSuite("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) {
+
+        TestSuite suite = new TestSuite(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 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();
+    }
+
+    /**
+     * 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();
+    }
+
+
+    /**
+     * 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 testUpdate() throws SQLException
+    {
+        Statement s = createStatement();
+        s.execute("insert into t11_AutoGen(c11) values(999)");
+
+        String sqlStmt="update t11_AutoGen set c11=1";
+        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();
+    }
+
+    /**
+     * 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();
+        }
+    }
+
+    /**
+     * Verifies that an exception is raised if a columnIndexes array is passed, 
+     * which signals the driver that the auto-generated keys indicated in the 
+     * given array should be made available for retrieval (feature not 
+     * supported).
+     * Old master Test21, Test21ps
+     * Expected result: Exception 0A000 should occur.
+     * @throws SQLException 
+     */
+    public void testColumnIndexesError() throws SQLException
+    {
+        Statement s = createStatement();
+        int colPositions[] = new int[1];
+        colPositions[0] = 1;
+
+        String sql="insert into t11_AutoGen(c11) " +
+            "select c21 from t21_noAutoGen";
+
+        try {
+            s.execute(sql, colPositions);
+            fail("Expected s.execute to fail");
+        } catch (SQLException se) {
+            assertSQLState("0A000", se.getSQLState(), se);
+        }
+
+        try {
+            s.executeUpdate(sql, colPositions);
+            fail("Expected s.executeUpdate to fail");
+        } catch (SQLException se) {
+            assertSQLState("0A000", se.getSQLState(), se);
+        }
+
+        try {
+            /* Deliberately not adding this prepareStatement wrapper to
+             * BaseJDBCTestCase.java because Derby doesn't support passing
+             * the array.
+             */
+            Connection conn = getConnection();
+            PreparedStatement ps=conn.prepareStatement(sql, colPositions);
+            fail("Expected prepareStatement to fail");
+        } catch (SQLException se) {
+            assertSQLState("0A000", se.getSQLState(), se);
+        }
+    }
+
+    /**
+     * Verifies that an exception is raised if a columnNames array is passed, 
+     * which signals the driver that the auto-generated keys indicated in the 
+     * given array should be made available for retrieval (feature not 
+     * supported).
+     * Old master Test22, Test22ps
+     * Expected result: Exception 0A000 should occur.
+     * @throws SQLException 
+     */
+    public void testColumnNamesError() throws SQLException
+    {
+        Statement s = createStatement();
+        String colNames[] = new String[1];
+        colNames[0] = "C11";
+
+        String sql="insert into t11_AutoGen(c11) " +
+            "select c21 from t21_noAutoGen";
+
+        try {
+            s.execute(sql, colNames);
+            fail("Expected s.execute to fail");
+        } catch (SQLException se) {
+            assertSQLState("0A000", se.getSQLState(), se);
+        }
+
+        try {
+            s.executeUpdate(sql, colNames);
+            fail("Expected s.executeUpdate to fail");
+        } catch (SQLException se) {
+            assertSQLState("0A000", se.getSQLState(), se);
+        }
+
+        try {
+            /* Deliberately not adding this prepareStatement wrapper to
+             * BaseJDBCTestCase.java because Derby doesn't support passing
+             * the array.
+             */
+            Connection conn = getConnection();
+            PreparedStatement ps=conn.prepareStatement(sql, colNames);
+            fail("Expected prepareStatement to fail");
+        } catch (SQLException se) {
+            assertSQLState("0A000", se.getSQLState(), se);
+        }
+    }
+
+    // Local utility methods.
+
+    /**
+     * 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
+    {
+        assertNotNull(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
+    {
+        assertNotNull("ResultSet is NULL", 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);
+    }
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/AutoGenJDBC30Test.java
------------------------------------------------------------------------------
    svn:eol-style = native

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/AutoGenJDBC30Test.java
------------------------------------------------------------------------------
    svn:executable = *

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java?view=diff&rev=520230&r1=520229&r2=520230
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java
Mon Mar 19 19:32:06 2007
@@ -88,6 +88,10 @@
             // even to load, even though the suite method
             // is correctly implemented.
             suite.addTest(DataSourcePropertiesTest.suite());
+
+            // Tests JDBC 3.0 ability to establish a result set of 
+            // auto-generated keys.
+            suite.addTest(AutoGenJDBC30Test.suite());
         }
 		
         return suite;

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/build.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/build.xml?view=diff&rev=520230&r1=520229&r2=520230
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/build.xml
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/build.xml
Mon Mar 19 19:32:06 2007
@@ -108,6 +108,7 @@
 		<pathelement path="${junit}"/>
       </classpath>
       <include name="${this.dir}/*.java"/>
+      <exclude name="${this.dir}/AutoGenJDBC30Test.java"/>
       <exclude name="${this.dir}/autoGeneratedJdbc30.java"/>
       <exclude name="${this.dir}/dbMetaDataJdbc30.java"/>
       <exclude name="${this.dir}/checkDataSource.java"/>
@@ -147,6 +148,7 @@
         <pathelement path="${junit}"/>
       </classpath>
       <!--exclude name=""/-->
+      <include name="${this.dir}/AutoGenJDBC30Test.java"/>
       <include name="${this.dir}/autoGeneratedJdbc30.java"/>
       <include name="${this.dir}/dbMetaDataJdbc30.java"/>
       <include name="${this.dir}/checkDataSource30.java"/>

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java?view=diff&rev=520230&r1=520229&r2=520230
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java Mon
Mar 19 19:32:06 2007
@@ -145,6 +145,20 @@
     {
         return getConnection().prepareStatement(sql);
     }    
+    /**
+     * Utility method to create a PreparedStatement using the connection
+     * returned by getConnection and a flag that signals the driver whether
+     * the auto-generated keys produced by this Statement object should be
+     * made available for retrieval.
+     * @return Statement object from
+     * prepareStatement(sql, autoGeneratedKeys)
+     * @throws SQLException
+     */
+    public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
+        throws SQLException
+    {
+        return getConnection().prepareStatement(sql, autoGeneratedKeys);
+    }    
 
     /**
      * Utility method to create a CallableStatement using the connection



Mime
View raw message