error in Executing PreparedStatement over DB2 Express-C

While executing a query by Statement object, works fine but executing same query by PreparedStatement object throws a SQL Exception.

This query works fine...

 String query = "SELECT A, B, C, D, LOBJ FROM TABLE WHERE LOBJ = 'sGMMEMDEML2';

Statement stmt = con.createStatement()

ResultSet rs = stmt.executeQuery(query);

This query throws sql exception (DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null)...

String query = "SELECT A, B, C, D, LOBJ FROM TABLE WHERE LOBJ = ?;

PreparedStatement preStmt = con.prepareStatement(query);

preStmt.setString(1, 'sGMMEMDEML2');

ResultSet rs = preStmt.executeQuery();

The column LOBJ in view TABLE is 10 char long but its specified value in where clause may or may not 10 char long due to some restriction in Ap开发者_如何学编程plication.

Can anybody help me out, how this can be executed with PreparedStatement.

Thanks in advance.

You may want to turn on DB2 compatibility to Oracle or MySQL (run those commands in DB2 command line):

db2set DB2_COMPATIBILITY_VECTOR=ORA # or MYS
db2stop
db2start

Note, however, that this affects only newly created databases (at least some DB2_COMPATIBILITY_VECTOR implications). Consult DB2 documentation for details (note, however, that MYS thing is not documented very well, but mentioned at least in C-Express 9.7.4 release notes.

Try to print the prepared statement and run query against the database.

From the IBM DB2 Error Codes:

SQLCODE=-302

THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER position-number IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE

As you have told that LOBJ is 10 char long and in your query

preStmt.setString(1, 'sGMMEMDEML2');

your input string has lengh of 11 characters. So just check it before querying to your database.

We are using DB2 9.7.x and I have the same problem now and found out that this occurs in correlation with setting the DB2 JDBC connection parameter "deferPrepares" to "false".

If you set "deferPrepares" to "true" or omit the parameter from the connection string, then the PreparedStatement executes without an error. This happens only when comparing string columns by "=" or "<>". If you compare the column by "like", then there is no error.

I regard this behavior as a major bug, because it deviates from the behavior shown in any SQL command window.

Unfortunately we had set deferPrepares to false to work around another problem with boolean fields mapped by Hibernate to Smallint columns. This problem at least is fixed in DB2 JDBC Drivers Version 9.7 Fix Pack 8 (see APAR no. IC88582 at http://www-01.ibm.com/support/docview.wss?uid=swg21415236).

So my recommendation would be to set deferPrepares to true and use JDBC drivers of Fix Pack 8 or later.