I previously asked the following question: DB2 query Unknown column name ERRORCODE=-4460, SQLSTATE=null
We have since learned that changing from db2jcc4.jar (JCC) from db2jcc.jar (UNIVERSAL) solves the issue in our development environment. The problem is that the one tha tdoesn't work is the newer one. We don't want to step backwards on our driver without really good cause. But the reason why the query in the above link is invalid in the new driver is not understood by me.
We know it is that column...if we take it out of the results by forcing an empty space, everything works (except we don't get the data). The query works fine in other environments.
I have seen some posts implying that this error is related to the result set meta data getColumn() method being inconsistent between JDBC3 and JDBC4. But we're not doing anything special in this query that isn't being done in many other queries, at least not as far as we can tell.
Does anyone know what about this query might be setting things off? Is there a fix for this behavior...either some setting or workaround, or a new driver?
The full exception:
com.ibm.db2.jcc.a.SqlException: [jcc][10150][10300][4.3.111] Invalid parameter: Unknown >column name FILTER_VALUE_DECODE. ERRORCODE=-4460, SQLSTATE=null at com.ibm.db2.jcc.a.dd.a(dd.java:660) at com.ibm.db2.jcc.a.dd.a(dd.java:60) at com.ibm.db2.jcc.a.dd.a(dd.java:103) at com.ibm.db2.jcc.a.ib.a(ib.java:1674) at com.ibm.db2.jcc.a.yl.a(yl.java:1625) at com.ibm.db2.jcc.a.yl.getString(yl.java:1468) at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.getString(WSJdbcResultSet.java:2467) at org.hibernate.type.StringType.get(StringType.java:41) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:210) at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:501) at org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:447) at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:344) at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:647) at org.hibernate.loader.Loader.doQuery(Loader.java:745) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270) at org.hibernate.loader.Loader.doList(Loader.java:2294) ... 64 more
You are probably using Hibernate 3.x. Hibernate 3.x tries to retrieve values of columns by their columnName
(that is the ResultSetMetaData
property for the original name of the column), while JDBC requires (by specification) that they are retrieved by columnLabel
(the property for the AS
alias, or if that isn't specified the original columnname).
Older versions of JDBC weren't entirely clear about the distinction between columnName
and columnLabel
, so implementing Drivers either returned the same value for both properties, or expected the columnName
to retrieve values.
IBM changed this behavior to conform to the JDBC specification in the DB2 9.5 driver, see this document. To revert to the old behavior, you need to specify the connection property useJDBC4ColumnNameAndLabelSemantics
to DB2BaseDataSource.NO
(which has the value 2
):
Resolution
If you cannot change your applications to conform to the newResultSetMetaData
behavior but you need other features of JDBC 4.0, set theuseJDBC4ColumnNameAndLabelSemantics
Connection or DataSource property toDB2BaseDataSource.NO
(2) to keep the old behavior.
The other option is to upgrade to a newer version of Hibernate (4.x) as this (at least by default) uses the columnLabel
for retrieving values.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With