Internal error when using bind variables on predicates with CONNECT BY and LEVEL from JDBC

lukaseder
SQL-Fighter

I've tried to reproduce this with SQL only, but I think it's strictly related to the usage of a bind variable as in the following JDBC example.

I'm using Exasol 7.0.7 on Windows with Docker:

 

docker run --name EXASOL -p 127.0.0.1:9563:8563 --detach --privileged --stop-timeout 120 exasol/docker-db:latest

 

With this setup, I'm running the following code snippet (having created a schema TEST first, but that's not relevant, I think):

 

try (Connection c = DriverManager.getConnection("jdbc:exa:localhost:9563;schema=TEST", "sys", "exasol");
    PreparedStatement s = c.prepareStatement("select count(*) from dual connect by level < ?")) {

    s.setInt(1, 10);
    try (ResultSet rs = s.executeQuery()) {
        while (rs.next())
            System.out.println(rs.getInt(1));
    }
}

 

The above fails with the following error:

 

Exception in thread "main" java.sql.SQLWarning: Successfully reconnected after internal server error, transaction was rolled back. (SessionID: 1693036685240238080)
	at com.exasol.jdbc.AbstractEXAConnection.addWarning(AbstractEXAConnection.java:1559)
	at com.exasol.jdbc.AbstractEXAConnection.setupConnection(AbstractEXAConnection.java:1534)
	at com.exasol.jdbc.AbstractEXAConnection.reconnect(AbstractEXAConnection.java:3813)
	at com.exasol.jdbc.ServerCommunication.handle(ServerCommunication.java:230)
	at com.exasol.jdbc.AbstractEXAConnection.communication(AbstractEXAConnection.java:2847)
	at com.exasol.jdbc.AbstractEXAConnection.communication_resultset(AbstractEXAConnection.java:2553)
	at com.exasol.jdbc.AbstractEXAPreparedStatement.<init>(AbstractEXAPreparedStatement.java:61)
	at com.exasol.jdbc.EXAPreparedStatement.<init>(EXAPreparedStatement.java:14)
	at com.exasol.jdbc.DialectGeneric.createPreparedStatement(DialectGeneric.java:10)
	at com.exasol.jdbc.AbstractEXAConnection.prepareStatement(AbstractEXAConnection.java:1084)
	at org.jooq.testscripts.JDBC.main(JDBC.java:21)

 

A workaround is not to use bind variables:

 

try (Connection c = DriverManager.getConnection("jdbc:exa:localhost:9563;schema=TEST", "sys", "exasol");
    PreparedStatement s = c.prepareStatement("select count(*) from dual connect by level < 10")) {

    try (ResultSet rs = s.executeQuery()) {
        while (rs.next())
            System.out.println(rs.getInt(1));
    }
}

 

1 ACCEPTED SOLUTION

Accepted Solutions

exa-SebastianN
Team Exasol
Team Exasol

Hi Lukas,

thanks again for reporting this bug! I forwarded the testcase to R&D, so that it can be fixed.

The problem is in the internal bind variable handling. If you need to use bind variables you can set the following database parameter as a workaround:

-guessPrepareParameterTypes=0

With this setting the query is running fine. Of course we will keep you posted if there are any news regarding a fix.

Cheers,

Sebastian

View solution in original post

3 REPLIES 3

exa-SebastianN
Team Exasol
Team Exasol

Hi Lukas,

thanks again for reporting this bug! I forwarded the testcase to R&D, so that it can be fixed.

The problem is in the internal bind variable handling. If you need to use bind variables you can set the following database parameter as a workaround:

-guessPrepareParameterTypes=0

With this setting the query is running fine. Of course we will keep you posted if there are any news regarding a fix.

Cheers,

Sebastian

View solution in original post

lukaseder
SQL-Fighter

Perfect, thanks a lot for the feedback, Sebastian. I don't need to work around this issue for now. I'll just deactivate this particular integration test until it stops failing in a future version.

exa-SimonR
Team Exasol
Team Exasol

This bug will be fixed in 7.1 release.