PreparedStatement Does Not Work For Sybase IQ In Java

Question

Me and my team faced a problem. We are trying to retrieve some data from Sybase IQ database and are using where clause to filter out and get specific data.

The SQL is tested and works fine but it fails when using Prepared Statement.

Tests done:

1. If we run the query (with or without where clause parameters), it works fine.
2. If we run the query with parameters hard coded in the Prepared Statement, it also works fine.
3. If we set the parameters of prepared statement programmatically, it does not work.

The above tests confirm the JDBC connection is working fine.

The same error appears when PreparedStatement, JdbcTemplate or NamedParameterJdbcTemplate is used, so I suspect there might be an issue between PreparedStatement and Sybase IQ.

Could anyone help to investigate that? We have found a workaround for that, but it would be really useful to know why this was not working.

I found very similar thread (How do I execute PreparedStatement(select object_id()) in sybase iq?) about the same issue, but nobody provided an accepted and correct answer there, so I decided to create a new question for this.

The code used is:

Class.forName("com.sybase.jdbc4.jdbc.SybDriver");

PreparedStatement stmt = con.prepareStatement("select * from myView where off = ? and acc = ?");

stmt.setString(1, "260");
stmt.setString(2, "9050V");
ResultSet set = stmt.executeQuery();

The error message is:

Exception in thread "main" java.sql.SQLException: JZ0SA: Prepared Statement: Input parameter not set, index: 0.
    at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(Unknown Source)
    at com.sybase.jdbc4.jdbc.SybStatement.handleSQLE(Unknown Source)
    at com.sybase.jdbc4.jdbc.SybStatement.sendQuery(Unknown Source)
    at com.sybase.jdbc4.jdbc.SybPreparedStatement.sendQuery(Unknown Source)
    at com.sybase.jdbc4.jdbc.SybStatement.executeQuery(Unknown Source)
    at com.sybase.jdbc4.jdbc.SybPreparedStatement.executeQuery(Unknown Source)

JDBC Driver used (Maven dependency):

<dependency>
            <groupId>com.sybase</groupId>
            <artifactId>jconn4</artifactId>
            <version>7.0</version>
</dependency>

Answer 1

I faced the same issue with sybase iq. I added the following two lines of code to solve the issue, before preparedStatement.execureQuery() statement.

 preparedStatement.setFetchSize(Integer.MAX_VALUE);
 preparedStatement.setFetchDirection(ResultSet.FETCH_FORWARD);

Not sure if it is the correct thing to do but it worked.

OR

you can set preparedStatement.setCursorName("SomeCursorName"); this also solved the issue. But in case of multi-threaded environment you need to set a unique cursor name. May be using a random number or something and don't create too many cursors limit them to number of concurrent threads that would be executed at the same time.