• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using Trim function in Hibernate

 
Deepak Ram
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • 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
 
Stevi Deter
Ranch Hand
Posts: 265
Hibernate Java Spring
  • Mark post as helpful
  • send pies
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic