We are using Hibernate to connect to Apache Derby, SQL Express 2008, and DB2 on an AS/400. We are having issues with a query on the AS/400 ONLY.
The following query (as reported by log4j TRACE) executes on all systems:
Code:
select count(distinct supplierbe0_.UID) as col_0_0_ from oprv2.Supplier supplierbe0_ where lower(supplierbe0_.NAME) like lower(?)
The value of the parameter, as confirmed by debugging and log4j TRACE, is '%%'.
The following query executes on all system except the AS/400:
Code:
select distinct supplierbe0_.UID as UID39_, supplierbe0_.VERSION as VERSION39_, supplierbe0_.PARENT_ID as PARENT3_39_, supplierbe0_.CONTACT_LIST as CONTACT4_39_, supplierbe0_.AAIA_SUPPLIER_ID as AAIA5_39_, supplierbe0_.DUNS_NUMBER as DUNS6_39_, supplierbe0_.GLOBAL_LOCATION_NUMBER as GLOBAL7_39_, supplierbe0_.LANG as LANG39_, supplierbe0_.CURRENCY as CURRENCY39_, supplierbe0_.ENVIRONMENT_CODE as ENVIRON10_39_, supplierbe0_.NAME as NAME39_, supplierbe0_.IDENTIFIER as IDENTIFIER39_, supplierbe0_.ADDRESS_1 as ADDRESS13_39_, supplierbe0_.ADDRESS_2 as ADDRESS14_39_, supplierbe0_.CITY as CITY39_, supplierbe0_.COUNTY as COUNTY39_, supplierbe0_.STAAT as STAAT39_, supplierbe0_.COUNTRY as COUNTRY39_, supplierbe0_.POSTAL_CODE as POSTAL19_39_, supplierbe0_.INTERNAL_ACCOUNT_NUMBER as INTERNAL20_39_, supplierbe0_.EXTERNAL_ACCOUNT_NUMBER as EXTERNAL21_39_, supplierbe0_.URL as URL39_, supplierbe0_.EDI_QUALIFIER as EDI23_39_, supplierbe0_.EDI_ID as EDI24_39_, supplierbe0_.IPO_ACTIVE as IPO25_39_, supplierbe0_.DELETED as DELETED39_ from oprv2.Supplier supplierbe0_ where lower(supplierbe0_.NAME) like lower(?) order by supplierbe0_.NAME, supplierbe0_.IDENTIFIER fetch first 10 rows only
The parameter in lower() is again '%%'. When this query executes on the AS/400, the following error is reported:
Code:
java.sql.SQLException: [SQL0131] Operands of LIKE not compatible or not valid. Cause . . . . . : The arguments of the LIKE predicate must be character, binary, graphic, or numeric. One of the following errors has occurred: -- The operand to the right of the LIKE operator is not character, binary, graphic, or numeric. -- The operands of the LIKE predicate are not compatible. -- The ESCAPE character is not character, binary, or graphic. Recovery . . . : Ensure the operands for the LIKE predicate are character, binary, graphic, or numeric. The ESCAPE character must be character, binary, or graphic. Try the request again.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:650)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:621)
at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1518)
at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:192)
at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1920)
at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1743)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
at org.hibernate.loader.Loader.doQuery(Loader.java:673)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
We are using the latest version of the jt400.jar for our driver, and the built-in DB2/400 dialect (org.hibernate.dialect.DB2400Dialect). When we try the second query using DBVisualizer and SQuirreL against the exact same system, the query executes fine. Does anyone have any idea on what might be happening?
Thanks!
Login
FAQ
Search