You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by jt...@apache.org on 2007/03/20 03:32:07 UTC
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
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