• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Using Trim function in Hibernate

 
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I want to use TRIM function in my HQL. This is the query in SQL form:

select A.CODE, trim(A.DSCR) from CODETABLE A where (A.CONDITION='100') order by trim(A.DSCR)

I want to write this query in HQL. This is my code in Hibernate:

StringBuffer qryStr = new StringBuffer();
qryStr.append("Select A.CODE, trim(A.DSCR) ");
qryStr.append("from CODETABLE A ");
qryStr.append("WHERE A.CONDITION = '100' ");
qryStr.append(" ORDER BY trim(A.DSCR)");
Query qry = session.createQuery(qryStr.toString());

List lst = qry.list();

When the query is executed, Hibernate throws the exception listed below.

Please let me know what mistake I am committing here in the usage of the TRIM function. My query runs fine without the TRIM function.

I am using DB2 and Hibernate 3.0 and the dialect is DB2390Dialect.

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:1502) at org.hibernate.loader.Loader.list(Loader.java:1482) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:365) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:268) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:782) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74) at com.met.us.aoc.dao.AOCUtilityDAO.getPaymentTypes(AOCUtilityDAO.java) at com.met.us.aoc.struts.action.AOCSearchTabAction.execute(AOCSearchTabAction.java:81) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196) at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414) at javax.servlet.http.HttpServlet.service(HttpServlet.java:743) at javax.servlet.http.HttpServlet.service(HttpServlet.java:856) at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:966) at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:478) at com.ibm.ws.wswebcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:463) at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:3129) at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:238) at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:811) at com.ibm.ws.wswebcontainer.WebContainer.handleRequest(WebContainer.java:1433) at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:93) at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:465) at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:394) at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:102) at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:152) at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:213) at com.ibm.io.async.AbstractAsyncFuture.fireCompletionActions(AbstractAsyncFuture.java:195) at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:136) at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:194) at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:782) at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:863) at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1510) Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0214N An expression in the ORDER BY clause in the following position, or starting with "1" in the "ORDER BY" clause is not valid. Reason code = "2". SQLSTATE=42822 at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source) at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source) at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source) at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(Unknown Source) at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeQuery(Unknown Source) at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteQuery(WSJdbcPreparedStatement.java:878) at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:559) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:107) at org.hibernate.loader.Loader.getResultSet(Loader.java:1183) at org.hibernate.loader.Loader.doQuery(Loader.java:363) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:203) at org.hibernate.loader.Loader.doList(Loader.java:1499) ... 32 more
 
Ranch Hand
Posts: 265
Hibernate Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Deepak,

The first thing I see in your code is that you're passing in the exact same SQL query to Hibernate Session.createQuery(String). If you want to use SQL, you need to use Session.createSQLQuery(String). Or you need to write the query properly in HQL to use createQuery.

Secondly, have you enabled logging of Hibernate's SQL? That would enable you to see the SQL Hibernate is generating and understand why certain errors are thrown. This article on JavaLobby gives a quick overview of how to enable this.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic