org.apache.derbyTesting.functionTests.tests.jdbcapi
Class AutoGenJDBC30Test

java.lang.Object
  junit.framework.Assert
      junit.framework.TestCase
          org.apache.derbyTesting.junit.BaseTestCase
              org.apache.derbyTesting.junit.BaseJDBCTestCase
                  org.apache.derbyTesting.functionTests.tests.jdbcapi.AutoGenJDBC30Test

All Implemented Interfaces:

junit.framework.Test

public class AutoGenJDBC30Test
extends BaseJDBCTestCase

Tests the JDBC 3.0 ability to establish a result set of auto-generated keys.

Converts the old jdbcapi/autoGeneratedJdbc30.java test to JUnit. The old harness test number is preserved in the comment for each fixture.

Field Summary

private static java.lang.String[]	ROUTINES Routines that should be created before the tests are run.
private static java.lang.String[][]	TABLES Tables that should be created before the tests are run.

Fields inherited from class org.apache.derbyTesting.junit.BaseTestCase

DEFAULT_DB_DIR, DERBY_LOG, ERRORSTACKTRACEFILE

Constructor Summary

AutoGenJDBC30Test(java.lang.String name)
Creates a new AutoGenJDBC30Test instance.

Method Summary

static int	addMe(int p1) External code for the AddMe SQL function, which is called by the testInsertAutoGenExecuteSQLfunc fixture.
private static junit.framework.Test	baseSuite(java.lang.String name) Tests are only run if JDBC 3.0 available, and database objects get created only once for the suite run.
int	getKeyValue(java.sql.ResultSet r) Gets the key value from the result set.
static java.lang.String	MyMethodWithNoInsert() External code for the MMWNI() SQL function, which is called by the testInsertNoAutoGenExecuteSQLfunc fixture.
void	runInsertFourWaysKeyIsNull(java.lang.String sql) 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.
void	setUp() Sets up the connection for a test case, clears all tables and resets all auto-generated keys used by the test fixtures.
static junit.framework.Test	suite() Implements suite() to run in embedded and client configurations.
void	testColumnIndexes() Test that use of columnIndexes to indicate which keys should be made available works as expected.
void	testColumnNames() Test that use of columnNames to indicate which keys should be made available works as expected.
void	testDelete() Requests generated keys for an DELETE statement.
void	testDeleteAutoGenNoGenKeysJDBC20syntax() Deletes rows from a table with an auto-generated column in the JDBC 2.0 way (with no generated key feature), then fetches key.
void	testGetKeyAfterCommit() 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.
void	testGetKeyAfterRollback() 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.
void	testGetKeyAfterSavepointRollback() 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.
void	testGetKeyMetadataAfterInsert() Inserts one row into a table with an auto-generated column, then examines the metadata for the generatedKeys ResultSet.
void	testInsertAutoGenExecuteSQLfunc() Inserts a row into a table with a SQL function in the VALUES clause; the table has an auto-generated column.
void	testInsertJDBC20syntax() Inserts one row into a table with an auto-generated column, but in the JDBC 2.0 way (with no generated key feature).
void	testInsertManyRowsAfterOneRowKey() Requests generated keys for a multi-row insert statement after a one-row insert into a table with an auto-generated key.
void	testInsertManyRowsNoPriorKey() Requests generated keys for a multi-row insert statement.
void	testInsertNoAutoGen() 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).
void	testInsertNoAutoGenExecuteSQLfunc() Inserts a row into a table with a SQL function in the VALUES clause; the table does not have an auto-generated column.
void	testInsertNoGenColAfterOneRowKey() 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.
void	testInsertNoGenKeys() Inserts one row into a table with an auto-generated column, but with NO_GENERATED_KEYS.
void	testInsertOneRowKey() Requests generated keys after doing a one-row insert into a table that has an auto-generated column.
void	testInsertSubqueryNoWhereClause() 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.
void	testInsertSubqueryWhere1is2() 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.
void	testInsertSubqueryWhereClause() 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.
void	testNoSql() Requests generated keys for a new statement that hasn't executed any SQL yet.
void	testResultSetGarbageCollection() 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).
void	testSelect() Requests generated keys for a Select statement (non-insert).
void	testUpdate() Requests generated keys for an UPDATE statement.
void	testUpdateAutoGenNoGenKeys() Updates a row in a table with an auto-generated column and NO_GENERATED_KEYS, then fetches key.
private void	testUserGivenColumns(int[] colIndexes, java.lang.String[] colNames, int expectedVal) 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.
void	testUserGivenColumnsEmpty() Verify that if user specifies an empty array for columNames or columnIndexes, it is the same as NO_GENERATED_KEYS
private void	testUserGivenColumnsError(int[] colIndexes, java.lang.String[] colNames) 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.
void	testUserGivenColumnsNull() 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).
void	verifyNullKey(java.lang.String description, java.sql.ResultSet r) Verifies that the generated key in a result set is null.

Methods inherited from class org.apache.derbyTesting.junit.BaseJDBCTestCase

assertCallError, assertCheckTable, assertCompileError, assertEquals, assertEquals, assertEquals, assertEquivalentDataType, assertGetIntError, assertNextError, assertPreparedStatementError, assertSQLExceptionEquals, assertSQLState, assertSQLState, assertStatementError, assertStatementError, assertStatementError, assertStatementError, assertTableRowCount, assertUpdateCount, assertUpdateCount, checkAllConsistency, checkEstimatedRowCount, commit, createStatement, createStatement, createStatement, dropTable, dropTable, getConnection, getDatabaseProperty, getLastSQLException, initializeConnection, openConnection, openDefaultConnection, openDefaultConnection, openUserConnection, prepareCall, prepareCall, prepareCall, prepareStatement, prepareStatement, prepareStatement, prepareStatement, prepareStatement, prepareStatement, rollback, runScript, runScript, runSQLCommands, setAutoCommit, tearDown, usingDB2Client, usingDerbyNetClient, usingEmbedded

Methods inherited from class org.apache.derbyTesting.junit.BaseTestCase

alarm, assertEquals, assertEquals, assertEquals, assertExecJavaCmdAsExpected, assertSecurityManager, assertThrowableEquals, execJavaCmd, fail, getFailureFolder, getJavaExecutableName, getSystemProperty, getTestConfiguration, getTestResource, openTestResource, println, printStackTrace, readProcessOutput, removeDirectory, removeDirectory, removeSystemProperty, runBare, setSystemProperty, traceit

Methods inherited from class junit.framework.TestCase

countTestCases, createResult, getName, run, run, runTest, setName, toString

Methods inherited from class junit.framework.Assert

assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertFalse, assertFalse, assertNotNull, assertNotNull, assertNotSame, assertNotSame, assertNull, assertNull, assertSame, assertSame, assertTrue, assertTrue, fail, fail

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Field Detail

ROUTINES

private static final java.lang.String[] ROUTINES

Routines that should be created before the tests are run.

TABLES

private static final java.lang.String[][] TABLES

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.

Constructor Detail

AutoGenJDBC30Test

public AutoGenJDBC30Test(java.lang.String name)

Creates a new AutoGenJDBC30Test instance.

Parameters:

name - name of the test

Method Detail

suite

public static junit.framework.Test suite()

Implements suite() to run in embedded and client configurations.

baseSuite

private static junit.framework.Test baseSuite(java.lang.String name)

Tests are only run if JDBC 3.0 available, and database objects get created only once for the suite run.

Parameters:

name - name of the test

setUp

public void setUp()
           throws java.sql.SQLException

Sets up the connection for a test case, clears all tables and resets all auto-generated keys used by the test fixtures.

Overrides:

setUp in class junit.framework.TestCase

Throws:

java.sql.SQLException

testNoSql

public void testNoSql()
               throws java.sql.SQLException

Requests generated keys for a new statement that hasn't executed any SQL yet. Old harness Test 1. Expected result: a NULL ResultSet.

Throws:

java.sql.SQLException

testSelect

public void testSelect()
                throws java.sql.SQLException

Requests generated keys for a Select statement (non-insert). Old harness Test 2. Expected result: a NULL ResultSet.

Throws:

java.sql.SQLException

testInsertManyRowsNoPriorKey

public void testInsertManyRowsNoPriorKey()
                                  throws java.sql.SQLException

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:

java.sql.SQLException

testInsertManyRowsAfterOneRowKey

public void testInsertManyRowsAfterOneRowKey()
                                      throws java.sql.SQLException

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:

java.sql.SQLException

testInsertNoAutoGen

public void testInsertNoAutoGen()
                         throws java.sql.SQLException

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:

java.sql.SQLException

testInsertSubqueryNoWhereClause

public void testInsertSubqueryNoWhereClause()
                                     throws java.sql.SQLException

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:

java.sql.SQLException

testInsertSubqueryWhere1is2

public void testInsertSubqueryWhere1is2()
                                 throws java.sql.SQLException

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:

java.sql.SQLException

testInsertSubqueryWhereClause

public void testInsertSubqueryWhereClause()
                                   throws java.sql.SQLException

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:

java.sql.SQLException

testInsertOneRowKey

public void testInsertOneRowKey()
                         throws java.sql.SQLException

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:

java.sql.SQLException

testInsertNoGenColAfterOneRowKey

public void testInsertNoGenColAfterOneRowKey()
                                      throws java.sql.SQLException

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:

java.sql.SQLException

testUpdate

public void testUpdate()
                throws java.sql.SQLException

Requests generated keys for an UPDATE statement. Old harness Test 9. Expected result: a NULL ResultSet.

Throws:

java.sql.SQLException

testDelete

public void testDelete()
                throws java.sql.SQLException

Requests generated keys for an DELETE statement. Old master Test 10. Expected result: a NULL ResultSet.

Throws:

java.sql.SQLException

testGetKeyAfterCommit

public void testGetKeyAfterCommit()
                           throws java.sql.SQLException

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:

java.sql.SQLException

testGetKeyAfterRollback

public void testGetKeyAfterRollback()
                             throws java.sql.SQLException

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:

java.sql.SQLException

testGetKeyAfterSavepointRollback

public void testGetKeyAfterSavepointRollback()
                                      throws java.sql.SQLException

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:

java.sql.SQLException

testGetKeyMetadataAfterInsert

public void testGetKeyMetadataAfterInsert()
                                   throws java.sql.SQLException

Inserts one row into a table with an auto-generated column, then examines the metadata for the generatedKeys ResultSet. Old master Test 14.

Throws:

java.sql.SQLException

testInsertNoGenKeys

public void testInsertNoGenKeys()
                         throws java.sql.SQLException

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:

java.sql.SQLException

testInsertJDBC20syntax

public void testInsertJDBC20syntax()
                            throws java.sql.SQLException

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:

java.sql.SQLException

testUpdateAutoGenNoGenKeys

public void testUpdateAutoGenNoGenKeys()
                                throws java.sql.SQLException

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:

java.sql.SQLException

testDeleteAutoGenNoGenKeysJDBC20syntax

public void testDeleteAutoGenNoGenKeysJDBC20syntax()
                                            throws java.sql.SQLException

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:

java.sql.SQLException

testInsertNoAutoGenExecuteSQLfunc

public void testInsertNoAutoGenExecuteSQLfunc()
                                       throws java.sql.SQLException

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:

java.sql.SQLException

testInsertAutoGenExecuteSQLfunc

public void testInsertAutoGenExecuteSQLfunc()
                                     throws java.sql.SQLException

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:

java.sql.SQLException

testResultSetGarbageCollection

public void testResultSetGarbageCollection()
                                    throws java.sql.SQLException

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:

java.sql.SQLException

testColumnIndexes

public void testColumnIndexes()
                       throws java.sql.SQLException

Test that use of columnIndexes to indicate which keys should be made available works as expected.

Throws:

java.sql.SQLException

testColumnNames

public void testColumnNames()
                     throws java.sql.SQLException

Test that use of columnNames to indicate which keys should be made available works as expected.

Throws:

java.sql.SQLException

testUserGivenColumnsEmpty

public void testUserGivenColumnsEmpty()
                               throws java.sql.SQLException

Verify that if user specifies an empty array for columNames or columnIndexes, it is the same as NO_GENERATED_KEYS

Throws:

java.sql.SQLException

testUserGivenColumnsNull

public void testUserGivenColumnsNull()
                              throws java.sql.SQLException

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:

java.sql.SQLException

testUserGivenColumns

private void testUserGivenColumns(int[] colIndexes,
                                  java.lang.String[] colNames,
                                  int expectedVal)
                           throws java.sql.SQLException

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.

Parameters:

colIndexes - Array of column indexes indicating which keys should be made available. Must be null if colNames is non-null.

colNames - Array of column names indicating which keys should be made available. Must be null if colIndexes is non-null.

expectedVal - First expected autogenerated key; will be incremented for each successful INSERT statement.

Throws:

java.sql.SQLException

testUserGivenColumnsError

private void testUserGivenColumnsError(int[] colIndexes,
                                       java.lang.String[] colNames)
                                throws java.sql.SQLException

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.

Parameters:

colIndexes - Array of column indexes indicating which keys should be made available. Must be null if colNames is non-null.

colNames - Array of column names indicating which keys should be made available. Must be null if colIndexes is non-null.

Throws:

java.sql.SQLException

runInsertFourWaysKeyIsNull

public void runInsertFourWaysKeyIsNull(java.lang.String sql)
                                throws java.sql.SQLException

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.

Parameters:

sql - The SQL statement to be executed

Throws:

java.sql.SQLException - if a database error occurs

verifyNullKey

public void verifyNullKey(java.lang.String description,
                          java.sql.ResultSet r)
                   throws java.sql.SQLException

Verifies that the generated key in a result set is null.

Parameters:

description - Text to be output for the assertion

r - ResultSet

Throws:

java.sql.SQLException - if a database error occurs

getKeyValue

public int getKeyValue(java.sql.ResultSet r)
                throws java.sql.SQLException

Gets the key value from the result set.

Parameters:

r - ResultSet

Throws:

java.sql.SQLException - if a database error occurs

MyMethodWithNoInsert

public static java.lang.String MyMethodWithNoInsert()
                                             throws java.sql.SQLException

External code for the MMWNI() SQL function, which is called by the testInsertNoAutoGenExecuteSQLfunc fixture.

Throws:

java.sql.SQLException - if a database error occurs

addMe

public static int addMe(int p1)
                 throws java.sql.SQLException

External code for the AddMe SQL function, which is called by the testInsertAutoGenExecuteSQLfunc fixture.

Parameters:

p1 - integer input argument to be used in calculation

Throws:

java.sql.SQLException - if a database error occurs