#1725 Accessing generated values, where non exists throws exception on second try.

[Jens Schauder]

Affected version 2.7.4
Last good version 2.7.3

When running an insert on a table that has no generated columns, but still trying to obtain generated keys,
this works only once. On the second attempt an exception is thrown complaining about a cursor not being open

java.lang.RuntimeException: java.sql.SQLException: invalid cursor state: identified cursor is not open

    at org.springframework.data.jdbc.repository.JdbcRepositoryEmbeddedWithReferenceIntegrationTests.insertDataSource(JdbcRepositoryEmbeddedWithReferenceIntegrationTests.java:113)
    at org.springframework.data.jdbc.repository.JdbcRepositoryEmbeddedWithReferenceIntegrationTests.dsTwo(JdbcRepositoryEmbeddedWithReferenceIntegrationTests.java:96)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
Caused by: java.sql.SQLException: invalid cursor state: identified cursor is not open
    at org.hsqldb.jdbc.JDBCUtil.sqlException(JDBCUtil.java:349)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(JDBCUtil.java:63)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(JDBCUtil.java:80)
    at org.hsqldb.jdbc.JDBCResultSet.checkClosed(JDBCResultSet.java:7533)
    at org.hsqldb.jdbc.JDBCResultSet.next(JDBCResultSet.java:323)
    at org.springframework.data.jdbc.repository.JdbcRepositoryEmbeddedWithReferenceIntegrationTests.insertDataSource(JdbcRepositoryEmbeddedWithReferenceIntegrationTests.java:108)
    ... 4 more
Caused by: org.hsqldb.HsqlException: invalid cursor state: identified cursor is not open
    at org.hsqldb.error.Error.error(Error.java:155)
    at org.hsqldb.error.Error.error(Error.java:116)
    ... 8 more

If you are wondering why one would try to obtain generated keys, when there are no columns generating anything:
This is done in Spring Data JDBC where the database schema is not under our control.

See related issue in Spring Data https://github.com/spring-projects/spring-data-relational/issues/1929

Test for reproducing the issue:

@Test
@Order(1)
void dsOne() {
    insertDataSource();
}

@Test
@Order(2)
void dsTwo() {
    insertDataSource();
}

private void insertDataSource() {
    try (Connection connection = dataSource.getConnection()) {

        PreparedStatement preparedStatement = connection.prepareStatement(INSERT_MAIN + "(?, ?)", Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setInt(1, 4711);
        preparedStatement.setString(2, "text1");
        preparedStatement.executeUpdate();

        try (ResultSet resultSet = preparedStatement.getGeneratedKeys()) {
            while (resultSet.next()) {
                System.out.println("rs element");
            }
        }
    } catch (SQLException ex) {
        throw new RuntimeException(ex);
    }
}

Table:

CREATE TABLE dummy_entity2
(
  ID   BIGINT,
  TEST VARCHAR(100)
)

[Fred Toussi]

Thanks for reporting. Will be fixed next year.

[Fred Toussi]

This will have a high priority for a new bug fix release early next year.

[Fred Toussi]

I have checked this but cannot see the issue.

Please attach your .java test file to this bug report.

[Jens Schauder]

I created a reproducer on Github: https://github.com/schauder/issue-hsqldb-1725

[Fred Toussi]

Thanks again. Fixed and committed to SVN /base/trunk/