3

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>
Chetan
  • 1,707
  • 10
  • 17
Mantas
  • 33
  • 7
  • Please add the execute statement. – RealSkeptic May 28 '19 at 16:12
  • Although JDBC drivers should use 1-based indexes for parameters (required by the JDBC specification), have you tried using 0-based indexes (as the error message also uses a 0-based index)? In any case, you should update your question with the full exception stacktrace and specify the **full** version of your Sybase driver and Sybase server. – Mark Rotteveel May 28 '19 at 16:21
  • Which JDBC driver are you using for the connection? – RealSkeptic May 28 '19 at 16:23
  • Edited the question. Yes, I have tried using 0-based indexes - ArrayOutOfBoundsException was thrown, so definitely 1-based index has to be used. – Mantas May 28 '19 at 16:29
  • From the code you posted: `select * from view where off = ? and acc = ?`. Is `view` a database table? What is the data type of `off` and `acc`? – Abra May 28 '19 at 17:10
  • have you tried using jConnect ,iAnywhere , jTDS , or jdbc:sqlserver drivers? – Dr Phil May 28 '19 at 17:13
  • @Abra view stands for database view. Data types of off and acc are char(3) and char(5). I have tried using char[] instead of String in Java code for parameters, but that did not help. – Mantas May 29 '19 at 08:39
  • @DrPhil We are using jConnect. Please check edited post. – Mantas May 29 '19 at 08:39
  • Sometimes `select *` can cause problems in JDBC. Did you try replacing the `*` with the list of the names of the columns in `view`? – Abra May 29 '19 at 12:29
  • @Abra I have tried selecting one column instead of select *, still same error. – Mantas May 29 '19 at 16:12

3 Answers3

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.

B--rian
  • 5,578
  • 10
  • 38
  • 89
rajukemburi
  • 31
  • 6
1

I used jconn4 Connection Property LITERAL_PARAMS=true and it worked fine. Be careful, performance should be affected.

When set to “true,” any parameters set by the setXXX methods in the PreparedStatement interface are inserted literally into the SQL statement when it is executed. If set to “false,” parameter markers are left in the SQL statement and the parameter values are sent to the server separately.

Joz0Ho1
  • 59
  • 3
0

stmt.setString(1, "260"); stmt.setString(2, "9050V");

Array index starts with 0 no? is it not a zero based indexing in your API used?

Rejji
  • 1
  • 1
  • According to Java documentation, PreparedStatement indices start from 1 - see https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setString(int,%20java.lang.String) – Mantas May 30 '19 at 14:17
  • Is this an answer? Should this be a comment instead? – kk. May 30 '19 at 14:24