Interoperability Issue: Prepared Statements Pooling c3p0 with HSQLDB 2.7.3 and 2.7.4

[skarzhevskyy]

When using c3p0 with the latest versions of HSQLDB (2.7.3 and 2.7.4) for integration testing of application configurations, setting c3p0.setMaxStatements to 2 or more results in an error during the second or third iteration of reads from a database connection using PreparedStatement.

It seems that c3p0's PreparedStatement pooling does not maintain the connection in a way that HSQLDB accepts. Specifically, connections appear to be reused in a way that leads to HSQLDB considering the connection closed....

Workaround
Setting dataSourceC3PO.setMaxStatements(0) resolves the problem, effectively disabling PreparedStatement pooling in c3p0.

The same version of HSQLDB used with Apache Commons DBCP2 does not exhibit the problem, suggesting it might be specific to c3p0's handling of PreparedStatement pooling.

Environment
Database: HSQLDB 2.7.3 or 2.7.4, (older versions worked)
c3p0: 0.10.1
Java: 17

References

• HSQLDB Change Log: https://hsqldb.org/doc/2.0/changelist_2_0.txt
• Test application to demonstrate the issue attached.

During iteration, the same HSQL connection is reused (as seen in the logs), and it results in an exception.

DB: HSQL Database Engine 2.7.4
connection iteration #1 started; connection: org.hsqldb.jdbc.JDBCConnection@5ce33a58
connection iteration #2 started; connection: org.hsqldb.jdbc.JDBCConnection@73ff4fae
connection iteration #3 started; connection: org.hsqldb.jdbc.JDBCConnection@5ce33a58
Exception in thread "main" java.sql.SQLNonTransientConnectionException: connection exception: closed
    at org.hsqldb.jdbc.JDBCUtil.sqlException(JDBCUtil.java:233)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(JDBCUtil.java:63)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(JDBCUtil.java:80)
    at org.hsqldb.jdbc.JDBCStatementBase.checkClosed(JDBCStatementBase.java:155)
    at org.hsqldb.jdbc.JDBCStatementBase.getResultSet(JDBCStatementBase.java:214)
    at org.hsqldb.jdbc.JDBCPreparedStatement.getResultSet(JDBCPreparedStatement.java:3407)
    at org.hsqldb.jdbc.JDBCPreparedStatement.executeQuery(JDBCPreparedStatement.java:254)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:743)
    at com.pyx4j.entity.rdb.hsql.bugs.PreparedStatementsPooling.runQuerySigleRow(PreparedStatementsPooling.java:136)
    at com.pyx4j.entity.rdb.hsql.bugs.PreparedStatementsPooling.main(PreparedStatementsPooling.java:102)
Caused by: org.hsqldb.HsqlException: connection exception: closed
    at org.hsqldb.error.Error.error(Error.java:155)
    at org.hsqldb.error.Error.error(Error.java:116)

PreparedStatementsPooling.java.txt