Error executing PreparedStatement on DB2 Express-C

Question

When a query is executed by a Statement object, it works fine, but executing the same query by a PreparedStatement object throws an SQL Exception.

This request is working 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 a 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 LOBJ column in the TABLE view has a length of 10 char, but its specified value is in where, where the sentence may or may not be 10 char long due to some restriction in the application.

Can someone help me on how this can be accomplished with PreparedStatement.

Thanks in advance.

Answer 1

You might want to enable DB2 compatibility with Oracle or MySQL (run these commands at the DB2 command line):

 db2set DB2_COMPATIBILITY_VECTOR=ORA # or MYS db2stop db2start 

Note, however, that this only affects newly created databases (at least some of the implications of DB2_COMPATIBILITY_VECTOR ). For more information, see the DB2 documentation (note, however, that the MYS article is not well documented, but is mentioned at least in C-Express 9.7.4 Release Notes .

Answer 2

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

Answer 3

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 said, LOBJ longer than 10 char and in your request

preStmt.setString(1, 'sGMMEMDEML2');

your input line is 11 characters long. So just check it before querying your database.

Answer 4

We use DB2 9.7.x, and now I have the same problem, and it turned out that this happens due to setting the DB2 JDBC connection parameter "deferPrepares" to "false".

If you set "deferPrepares" to "true" or omit the parameter from the connection string, then PreparedStatement will run without error. This only happens when comparing row columns with "=" or "<>". If you are comparing the “how” column, then no error occurs.

I view this behavior as a serious mistake, as it differs from the behavior displayed in any SQL command window.

Unfortunately, we set deferPrepares to false to work around another issue with the boolean fields displayed by Hibernate to Smallint columns. This problem is at least 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 was to set deferPrepares to true and use JDBC drivers Fix Pack 8 or later.