#1738 Regression in 2.7.4 with respect to auto-generated keys in prepared statements

[Tobias Riemenschneider]

I am using a Vert.x JDBC client with HSQLDB. Following the upgrade from HSQLDB 2.7.3 to 2.7.4, I have noticed a regression with regard to auto-generated keys when using prepared statements. I have created a reproduction of the issue in https://github.com/riemenschneider/JDBCPreparedQueryTest, which involves creating a new table and using a prepared statement to insert three rows. The test runs fine with HSQLDB 2.7.3, but when I switch to HSQLDB 2.7.4, I get the following error:

java.sql.SQLException: invalid cursor state: identified cursor is not open
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCResultSet.checkClosed(Unknown Source)
    at org.hsqldb.jdbc.JDBCResultSet.next(Unknown Source)
    at io.vertx.jdbcclient.impl.actions.JDBCQueryAction.decodeReturnedKeys(JDBCQueryAction.java:180)
    at io.vertx.jdbcclient.impl.actions.JDBCQueryAction.decode(JDBCQueryAction.java:68)
    at io.vertx.jdbcclient.impl.actions.JDBCPreparedQuery.execute(JDBCPreparedQuery.java:61)
    at io.vertx.jdbcclient.impl.actions.JDBCPreparedQuery.execute(JDBCPreparedQuery.java:41)
    at io.vertx.jdbcclient.impl.ConnectionImpl.lambda$schedule$2(ConnectionImpl.java:193)
    at io.vertx.core.impl.ExecuteBlocking$1.execute(ExecuteBlocking.java:36)
    at io.vertx.core.impl.WorkerTask.run(WorkerTask.java:57)
    at io.vertx.core.impl.TaskQueue.run(TaskQueue.java:80)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
    at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
    at java.base/java.lang.Thread.run(Thread.java:1583)
Caused by: org.hsqldb.HsqlException: invalid cursor state: identified cursor is not open
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    ... 15 more

[Tobias Riemenschneider]

Looks like the Vert.x JDBC client is not even required for reproducing the issue

        try (Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:.")) {
            try (Statement statement = connection.createStatement()) {
                statement.execute("CREATE TABLE test2 (id INT, data BIGINT)");
                try (PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO test2 (id, data) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS)) {
                    preparedStatement.setInt(1, 1);
                    preparedStatement.setLong(2, 42L);
                    preparedStatement.executeUpdate();
                    preparedStatement.setInt(1, 2);
                    preparedStatement.setLong(2, 0L);
                    preparedStatement.executeUpdate();
                    preparedStatement.setInt(1, 3);
                    preparedStatement.setNull(2, java.sql.Types.BIGINT);
                    preparedStatement.executeUpdate();
                }
                try (ResultSet resultSet = statement.getGeneratedKeys()) {
                    resultSet.next();
                }
            }
        }

I have added a second test to the reproducer.

[Fred Toussi]

Thanks for reporting. This issue has already been fixed and committed to SVN for the next release, which will happen after Java 25.