Problem Description:

We are porting a hibernate application from Oracle 10g to DB2.

We have found that Named Queries which worked with the Oracle 10g platform produce JDBC errors when executed against DB2.

When the query is changed from a Named Query to a Dynamically-generated HQL statement, the application works as expected.

When we cut and paste the generated SQL from the log file (included below) into a DB2 query tool, the query works properly as well.

We're using the following driver class: com.ibm.db2.jcc.DB2Driver
and JBoss 3.2.6.

Has anyone seen this? Does anyone have any suggestions?

Thanks,

Tom

Code:

//This code generates the error below
Query q = session.getNamedQuery(findUserByLoginId);
q.setString("loginId", loginId);
User user = (User) q.uniqueResult();

//This code functions as expected
String hql = "from User u where lower(u.loginId) = '" + loginId + "'";
User user = (User) session.createQuery(hql).uniqueResult();

Hibernate version:
Hibernate 2.1.5

Name and version of the database you are using:
DB2 v8.1.7

Mapping documents:

<query name="findUserByLoginId">
<![CDATA[
from User u where lower(u.loginId) = lower(:loginId)
]]>
</query>

Full stack trace of any exception that occurs:

2006-12-13 17:30:11,784 DEBUG [net.sf.hibernate.hql.QueryTranslator] SQL: select user0_.USER_ID as USER_ID, user0_.LOGIN_ID as LOGIN_ID, user0_.PASSWORD as PASSWORD, user0_.FIRST_NAME as FIRST_NAME, user0_.LAST_NAME as LAST_NAME, user0_.EMAIL_ADDRESS as EMAIL_AD6_, user0_.PHONE_NUMBER as PHONE_NU7_, user0_.TITLE as TITLE, user0_.STAFF_ID as STAFF_ID, user0_.INTERNAL as INTERNAL, user0_.IS_ACTIVE as IS_ACTIVE, user0_.CREATED_DATE as CREATED12_, user0_.MODIFIED_DATE as MODIFIE13_ from ESM_USER user0_ where (lower(user0_.LOGIN_ID)=lower(?))
2006-12-13 17:30:11,784 DEBUG [net.sf.hibernate.impl.BatcherImpl] about to open: 0 open PreparedStatements, 0 open ResultSets
2006-12-13 17:30:11,784 DEBUG [net.sf.hibernate.SQL] select user0_.USER_ID as USER_ID, user0_.LOGIN_ID as LOGIN_ID, user0_.PASSWORD as PASSWORD, user0_.FIRST_NAME as FIRST_NAME, user0_.LAST_NAME as LAST_NAME, user0_.EMAIL_ADDRESS as EMAIL_AD6_, user0_.PHONE_NUMBER as PHONE_NU7_, user0_.TITLE as TITLE, user0_.STAFF_ID as STAFF_ID, user0_.INTERNAL as INTERNAL, user0_.IS_ACTIVE as IS_ACTIVE, user0_.CREATED_DATE as CREATED12_, user0_.MODIFIED_DATE as MODIFIE13_ from ESM_USER user0_ where (lower(user0_.LOGIN_ID)=lower(?))
2006-12-13 17:30:11,784 DEBUG [net.sf.hibernate.impl.BatcherImpl] preparing statement
2006-12-13 17:30:11,784 DEBUG [net.sf.hibernate.type.StringType] binding 'esm_super' to parameter: 1
2006-12-13 17:30:11,784 DEBUG [net.sf.hibernate.util.JDBCExceptionReporter] SQL Exception
com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -418, SQLSTATE: 42610, SQLERRMC: null
at com.ibm.db2.jcc.b.zc.e(zc.java:1606)
at com.ibm.db2.jcc.b.zc.a(zc.java:1206)
at com.ibm.db2.jcc.a.db.h(db.java:149)
at com.ibm.db2.jcc.a.db.a(db.java:43)
at com.ibm.db2.jcc.a.r.a(r.java:30)
at com.ibm.db2.jcc.a.sb.g(sb.java:152)
at com.ibm.db2.jcc.b.zc.n(zc.java:1186)
at com.ibm.db2.jcc.b.ad.db(ad.java:1761)
at com.ibm.db2.jcc.b.ad.d(ad.java:2203)
at com.ibm.db2.jcc.b.ad.U(ad.java:489)
at com.ibm.db2.jcc.b.ad.executeQuery(ad.java:472)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:314)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:800)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:958)
at net.sf.hibernate.loader.Loader.list(Loader.java:949)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:846)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1543)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at net.sf.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:542)
at com.escholar.esm.hibernate.HibernateSecurityManager.login(HibernateSecurityManager.java:324)
at com.escholar.esm.ESMLoginModule.login(ESMLoginModule.java:11)
at com.escholar.esm.gui.struts.action.LoginAction.execute(LoginAction.java:29)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.escholar.esm.gui.ESMFilter.doFilter(ESMFilter.java:29)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:75)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:66)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:158)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:534)

As i know, you need to explicitly cast parameter to desired type. Look at description of this error in DB2 documentation:

SQL0418N - A statement contains a use of a parameter marker that is not valid. Explanation: Untyped parameter markers cannot be used in some cases as the sole argument of a scalar function.

So, it seems, that problem part of the SQL query is: where (lower(user0_.LOGIN_ID)=lower(?))

You need to convert this to:

where (lower(user0_.LOGIN_ID)=lower(CAST(? AS desiredType)))

Thanks for the feedback.

Hibernate generates that query for me.
How can I get hibernate to write the query with the cast?

Is there another way to call the query object?

I've tried to explicitly set the type in the call, but the generated SQL does not change.

In the mean time, I've changed the named query to dynamic hql, but I'd like to use the name query if possible.

Any ideas would be greatly appreciated.

As i know, Hibernate supports SQL-functions in where clause. You say, that when you try to call cast function like this:

CAST(:param AS VARCHAR(20))

in HQL Hibernate completely ignore it? If this is the case, you might consider native sql query support in Hibernate.

Thanks alot.

I tried the cast before but I didn't get the syntax quite right because of the lower. This is the one that worked.

lower(cast(:loginId as VARCHAR(255)))

Thanks again.