-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 
  Previous topic | Next topic 
Author Message
leebase
 Post subject: Bind Variables in the Order by
PostPosted: Fri Feb 11, 2005 12:11 pm 
Beginner
Beginner

Joined: Tue Nov 02, 2004 1:34 pm
Posts: 45
Does hibernate 2 support bind variables in the order by clause of a named query?

As in: order by :orderByField :orderyByDirection

If not...how does one dynamically change the order of a named query?

Lee

--------------------------------------------------------
Hibernate version: 2.1.4

Mapping documents:

Code:
   <query name="com.mycompany.NddMetric.allRecords">
         <![CDATA[
           from com.mycompany.NddMetric as NddMetric
           order by UPPER(metric_name)
         ]]>
     </query>
   
    <query name="com.mycompany.NddMetric.allRecords2">
         <![CDATA[
           from com.mycompany.NddMetric as NddMetric
           order by :orderByField :orderbyDirection
         ]]>
     </query>


Code between sessionFactory.openSession() and session.close(): NA

Full stack trace of any exception that occurs:

Code:
Hibernate: select * from ( select count(distinct nddmetric0_.METRIC_ID) as x0_0_ from NDD_METRIC nddmetric0_ ) where rownum <= ?
Hibernate: select * from ( select nddmetric0_.METRIC_ID as METRIC_ID, nddmetric0_.REPORT_OWNER_NAME as REPORT_O2_, nddmetric0_.SHORT_NAME as SHORT_NAME, nddmetric0_.REPORT_OWNER_ID as REPORT_O4_, nddmetric0_.REPORT_NAME as REPORT_N5_, nddmetric0_.REPORT_ID as REPORT_ID, nddmetric0_.LOGIC_DESCRIPTION as LOGIC_DE7_, nddmetric0_.LOGIC_SHORT_DESC as LOGIC_SH8_, nddmetric0_.LOGIC_ORDER as LOGIC_OR9_, nddmetric0_.LOGIC_ID as LOGIC_ID, nddmetric0_.MODULE_NAME as MODULE_11_, nddmetric0_.MODULE_TYPE as MODULE_12_, nddmetric0_.MODULE_DESCRIPTION as MODULE_13_, nddmetric0_.MODULE_ID as MODULE_ID, nddmetric0_.TABLE_NAME as TABLE_NAME, nddmetric0_.TABLE_ID as TABLE_ID, nddmetric0_.DATABASE_NAME as DATABAS17_, nddmetric0_.DATABASE_ID as DATABAS18_, nddmetric0_.DATA_STORE_NAME as DATA_ST19_, nddmetric0_.DATA_STORE_DESCRIPTION as DATA_ST20_, nddmetric0_.DATA_STORE_LINK as DATA_ST21_, nddmetric0_.CONTACT_PHONE as CONTACT22_, nddmetric0_.CONTACT_NAME as CONTACT23_, nddmetric0_.DATA_STORE_ID as DATA_ST24_, nddmetric0_.FUNCTIONAL_AREA_NAME as FUNCTIO25_, nddmetric0_.FUNCTIONAL_AREA_DESCRIPTION as FUNCTIO26_, nddmetric0_.FUNCTIONAL_AREA_SHORT_NAME as FUNCTIO27_, nddmetric0_.FUNCTIONAL_AREA_ID as FUNCTIO28_, nddmetric0_.LAST_MOD_BY as LAST_MO29_, nddmetric0_.LAST_MOD_DATE as LAST_MO30_, nddmetric0_.METRIC_NAME as METRIC_31_, nddmetric0_.METRIC_DESCRIPTION as METRIC_32_, nddmetric0_.METRIC_NUMBER as METRIC_33_, nddmetric0_.DATA_STEWARD as DATA_ST34_ from NDD_METRIC nddmetric0_ order by  ? ? ) where rownum <= ?
WARN  - [JDBCExceptionReporter.logExceptions] SQL Error: 1745, SQLState: 42000
ERROR - [JDBCExceptionReporter.logExceptions] ORA-01745: invalid host/bind variable name

WARN  - [JDBCExceptionReporter.logExceptions] SQL Error: 1745, SQLState: 42000
ERROR - [JDBCExceptionReporter.logExceptions] ORA-01745: invalid host/bind variable name

ERROR - [JDBCException.<init>] Could not execute query
java.sql.SQLException: ORA-01745: invalid host/bind variable name

   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
   at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
   at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
   at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
   at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831)
   at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496)
   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840)
   at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
   at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536)
   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:955)
   at net.sf.hibernate.loader.Loader.list(Loader.java:946)
   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 com.sbc.netrics.struts.database.NDDMetricService.getAllRecords(NDDMetricService.java:175)
   at com.sbc.netrics.struts.action.MetricCRUDAction.prepareBrowse(MetricCRUDAction.java:286)
   at com.sbc.netrics.struts.action.MetricCRUDAction.browse(MetricCRUDAction.java:58)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:324)
   at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)
   at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:216)
   at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
   at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
   at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
   at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:284)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204)
   at com.sbc.netrics.struts.database.HibernateFilter.doFilter(HibernateFilter.java:40)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:233)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
   at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:245)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:199)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:195)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:164)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:156)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
   at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:972)
   at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:211)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:805)
   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:696)
   at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:605)
   at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:677)
   at java.lang.Thread.run(Thread.java:534)
ERROR - [MetricCRUDAction.prepareBrowse] metricList failed: net.sf.hibernate.JDBCException: Could not execute query
INFO  - [HibernateFilter.doFilter] commit transaction via filter



Name and version of the database you are using: Oracle 9i

The generated SQL (show_sql=true):

Code:
Hibernate: select * from ( select nddmetric0_.METRIC_ID as METRIC_ID, nddmetric0_.REPORT_OWNER_NAME as REPORT_O2_, nddmetric0_.SHORT_NAME as SHORT_NAME, nddmetric0_.REPORT_OWNER_ID as REPORT_O4_, nddmetric0_.REPORT_NAME as REPORT_N5_, nddmetric0_.REPORT_ID as REPORT_ID, nddmetric0_.LOGIC_DESCRIPTION as LOGIC_DE7_, nddmetric0_.LOGIC_SHORT_DESC as LOGIC_SH8_, nddmetric0_.LOGIC_ORDER as LOGIC_OR9_, nddmetric0_.LOGIC_ID as LOGIC_ID, nddmetric0_.MODULE_NAME as MODULE_11_, nddmetric0_.MODULE_TYPE as MODULE_12_, nddmetric0_.MODULE_DESCRIPTION as MODULE_13_, nddmetric0_.MODULE_ID as MODULE_ID, nddmetric0_.TABLE_NAME as TABLE_NAME, nddmetric0_.TABLE_ID as TABLE_ID, nddmetric0_.DATABASE_NAME as DATABAS17_, nddmetric0_.DATABASE_ID as DATABAS18_, nddmetric0_.DATA_STORE_NAME as DATA_ST19_, nddmetric0_.DATA_STORE_DESCRIPTION as DATA_ST20_, nddmetric0_.DATA_STORE_LINK as DATA_ST21_, nddmetric0_.CONTACT_PHONE as CONTACT22_, nddmetric0_.CONTACT_NAME as CONTACT23_, nddmetric0_.DATA_STORE_ID as DATA_ST24_, nddmetric0_.FUNCTIONAL_AREA_NAME as FUNCTIO25_, nddmetric0_.FUNCTIONAL_AREA_DESCRIPTION as FUNCTIO26_, nddmetric0_.FUNCTIONAL_AREA_SHORT_NAME as FUNCTIO27_, nddmetric0_.FUNCTIONAL_AREA_ID as FUNCTIO28_, nddmetric0_.LAST_MOD_BY as LAST_MO29_, nddmetric0_.LAST_MOD_DATE as LAST_MO30_, nddmetric0_.METRIC_NAME as METRIC_31_, nddmetric0_.METRIC_DESCRIPTION as METRIC_32_, nddmetric0_.METRIC_NUMBER as METRIC_33_, nddmetric0_.DATA_STEWARD as DATA_ST34_ from NDD_METRIC nddmetric0_ order by  ? ? ) where rownum <= ?
Top
 Profile  
 
leebase
 Post subject:
PostPosted: Mon Feb 14, 2005 12:11 pm 
Beginner
Beginner

Joined: Tue Nov 02, 2004 1:34 pm
Posts: 45
No thoughts? I got around the problem by not using a named query...instead building the sql in my class and using createQuery. Still....should this be necessary in order to be able to change the orderby at runtime?

Lee
Top
 Profile  
 
michael
 Post subject:
PostPosted: Mon Feb 14, 2005 12:23 pm 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
No, using parameter placeholders like this is not even possible with PreparedStatements.

Look at the Criteria API for dynamic stuff like that.
Top
 Profile  
 
leebase
 Post subject:
PostPosted: Mon Feb 14, 2005 3:59 pm 
Beginner
Beginner

Joined: Tue Nov 02, 2004 1:34 pm
Posts: 45
michael wrote:
No, using parameter placeholders like this is not even possible with PreparedStatements.

Look at the Criteria API for dynamic stuff like that.


Thanks...at least I now know "it's not me" :)

It is strange that you can use placeholders in the where clause but not the order by.

I did look into the Criteria....but was stymied by the need for "or"

I want to find all values where:

upper(field1) is like "%search string%" or
upper(field2) is like "%search string%" or
upper(field3) is like "%search string%" or

and so on.

Lee
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.