| /* |
| 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); |
| } |
| } |
