/*
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 testColumnIndexesNotImpl() throws SQLException
{
/* As of DERBY-2631 we support this with embedded. So do nothing
* for this test fixture; we'll test the functionality as part
* of a separate fixture.
*/
if (usingEmbedded())
return;
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 testColumnNamesNotImpl() throws SQLException
{
/* As of DERBY-2631 we support this with embedded. So do nothing
* for this test fixture; we'll test the functionality as part
* of a separate fixture.
*/
if (usingEmbedded())
return;
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);
}
}
/**
* Test that use of columnIndexes to indicate which keys should be
* made available works as expected.
*
* @throws SQLException
*/
public void testColumnIndexes() throws SQLException
{
/* Not supported for Derby client. We check the "not supported"
* error message as part of a different fixture.
*/
if (usingDerbyNetClient())
return;
// Valid (typical) usage.
int [] colIndexes = new int [] { 2 };
testUserGivenColumns(colIndexes, null, 1);
// Non-existent column index.
colIndexes[0] = 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 not an auto-gen column.
colIndexes[1] = 1;
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
{
/* Not supported for Derby client. We check the "not supported"
* error message as part of a different fixture.
*/
if (usingDerbyNetClient())
return;
// Valid (typical) usage.
String [] colNames = new String [] { "C12" };
testUserGivenColumns(null, colNames, 1);
// Non-existent column name.
colNames[0] = "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 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
{
/* Not supported for Derby client. We check the "not supported"
* error message as part of a different fixture.
*/
if (usingDerbyNetClient())
return;
Statement s = createStatement();
String sql="insert into t11_AutoGen(c11) values (99)";
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());
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());
s.close();
PreparedStatement 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 = 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());
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");
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
{
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);
}
}