Error occurs when using upper function

j.gohel · **Joined:** Fri Apr 13, 2007 10:58 am **Posts:** 10

Hello,

I am facing a small problem when using upper function in my hql query.

The Java code i am using is :

Code:

String hql = "select e.name from JobTitle e where upper(e.name)=upper(:name)";
 Session hSession = sessionFactory.openSession();
 Query hQuery = hSession.createQuery(hql);
  hQuery.setParameter("name", "job1");
  List l = hQuery.list();
  System.out.println(l);

It gives the error as mentioned in the stack trace below.

Hibernate version: 3.0.2

IBM DB2 Version 8.2.The jdbc driver files i am using are the following:
db2jcc_license_cu.jar and db2jcc.jar.

The generated SQL :
Hibernate: select jobtitle0_.NAME as col_0_0_ from JOBTITLE jobtitle0_ where upper(jobtitle0_.NAME)=upper(?)

org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1565)
at org.hibernate.loader.Loader.list(Loader.java:1545)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:840)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at com.performixtech.emvolve.main.TestHibernate.findEmployee(TestHibernate.java:95)
at com.performixtech.emvolve.main.TestHibernate.main(TestHibernate.java:50)
Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -418, SQLSTATE: 42610, SQLERRMC: null
at com.ibm.db2.jcc.b.id.e(id.java:1640)
at com.ibm.db2.jcc.b.id.a(id.java:1229)
at com.ibm.db2.jcc.c.fb.h(fb.java:149)
at com.ibm.db2.jcc.c.fb.a(fb.java:43)
at com.ibm.db2.jcc.c.s.a(s.java:30)
at com.ibm.db2.jcc.c.wb.g(wb.java:152)
at com.ibm.db2.jcc.b.id.n(id.java:1209)
at com.ibm.db2.jcc.b.jd.eb(jd.java:1779)
at com.ibm.db2.jcc.b.jd.a(jd.java:2232)
at com.ibm.db2.jcc.b.jd.V(jd.java:505)
at com.ibm.db2.jcc.b.jd.executeQuery(jd.java:488)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:118)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1239)
at org.hibernate.loader.Loader.doQuery(Loader.java:374)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:210)
at org.hibernate.loader.Loader.doList(Loader.java:1562)
... 7 more

[/code]

clickonchris · **Posted:** Tue Jun 09, 2009 2:38 pm

I am having this issue also. I'll post back if I find a solution

clickonchris · **Posted:** Fri Jun 19, 2009 2:33 pm

figured it out-

In your case you will want to change your hql from

Code:

select e.name from JobTitle e where upper(e.name)=upper(:name)";

to

Code:

select e.name from JobTitle e where upper(e.name)=:name";

and make :name uppercase when you set it as a parameter. eg:

Code:

q.setParameter(":name", name.toUpperCase());

jpkrohling · **Posted:** Thu Nov 05, 2009 7:52 am

https://jira.jboss.org/jira/browse/JBPAPP-3056