[SOLVED] How to enable JDBC connection pooling on Exasol?

How to enable JDBC connection pooling on Exasol?

We use Hibernate with Exasol in a web app. When we set Hibernate's hibernate.connection.pool_size property to a value greater than 1 (or the corresponding property of any JDBC connection pool, e.g, HikariCP, c3p0, to a value greater than 1), we get:

java.sql.SQLException: syntax error, unexpected FOR_READ_ONLY_, expecting end_of_input or ';' [line 1, column 93] (Session: 1622929410050974585)
    at com.exasol.jdbc.ExceptionFactory.createSQLException(ExceptionFactory.java:164)
    at com.exasol.jdbc.ExceptionFactory.createSQLException(ExceptionFactory.java:21)
    at com.exasol.jdbc.AbstractEXAPreparedStatement.<init>(AbstractEXAPreparedStatement.java:62)
    at com.exasol.jdbc.AbstractEXAPreparedStatement_14.<init>(AbstractEXAPreparedStatement_14.java:14)
    at com.exasol.jdbc.EXAPreparedStatement.<init>(EXAPreparedStatement.java:12)
    at com.exasol.jdbc.DialectGeneric.createPreparedStatement(DialectGeneric.java:10)
    at com.exasol.jdbc.AbstractEXAConnection.prepareStatement(AbstractEXAConnection.java:608)
    at org.hibernate.id.enhanced.TableGenerator.prepareStatement(TableGenerator.java:618)
    at org.hibernate.id.enhanced.TableGenerator.access$200(TableGenerator.java:127)

From the Exasol's user manual,

The Connection Pooling of the driver manager is deactivated by default. You can explicitly activate it in the configuration tool "ODBC Data Source Administrator". But please note that in that case reused connections keep their session settings which were set via SQL commands (see ALTER SESSION).

Here, ODBC is mentioned, whereas we use JDBC. So my question is, how to enable JDBC connection pooling on Exasol?

Should we add/update a row in EXA_PARAMETERS view/table?

Solution

for JDBC, EXASOL itself doesn't have an integrated connection pooling. But we find the following library very useful: http://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi For instance, this is also used in JMeter and works fine there. However, we do not have any experience combining this library with hibernate. By the way, did you use https://github.com/exasol/hibernate-exasol?

Please let us know about the progress, we are happy to help! (Contact us: www.exasol.com/contact)

Best regards, Team Exasol