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
  [ 7 posts ] 
  Previous topic | Next topic 
Author Message
andreas_eriksson
 Post subject: Order.asc(...) ??
PostPosted: Tue Dec 16, 2003 12:03 pm 
Regular
Regular

Joined: Wed Aug 27, 2003 2:55 am
Posts: 90
Location: Sweden
Hi,

Have a look at this code:

Code:
/* (non-Javadoc)
* @see eriksson.andreas.dao.CityDao#findByName(java.lang.String)
*/
public City[] findByName(String cityNameToFind) {
    Session session = this.obtainSession();
    try {
        Criteria criteria = session.createCriteria(CityImpl.class)
            .add(Expression.like(FIND_BY_NAME_COLUMN, cityNameToFind))
            .addOrder(Order.asc(FIND_BY_NAME_ORDER_COLUMN));
        List cityList = criteria.list();
        return (City[]) cityList.toArray(new City[cityList.size()]);
    } catch (HibernateException he) {
        logger.warn("Unable to perform city find by city name", he);
    } finally {
        this.releaseSession(session);
    }
    return null;
}


The constants FIND_BY_NAME_COLUMN and FIND_BY_NAME_ORDER_COLUMN both are asigned the String value 'cityName'.

My CityImpl value object have these properties:

Code:
public CityImpl implements City {
    private String cityId;
    private String cityName;

    // Setters and getters
}


The following can be read from the console when running on DEBUG-level:

Code:
16:56:05,501 DEBUG BatcherImpl:192 - about to open: 0 open PreparedStatements, 0 open ResultSets
16:56:05,511 DEBUG BatcherImpl:226 - prepared statement get: select this.cityId as cityId0_, this.cityName as cityName0_ from cities this where this.cityName like ? order by this.cityName asc
Hibernate: select this.cityId as cityId0_, this.cityName as cityName0_ from cities this where this.cityName like ? order by this.cityName asc
16:56:05,511 DEBUG BatcherImpl:232 - preparing statement
16:56:05,581 DEBUG JDBCExceptionReporter:36 - SQL Exception
java.sql.SQLException: ORDER BY clause invalid because it includes a column name that is not part of the result table
   at com.mimer.jdbc.Connection.a(Unknown Source)
   at com.mimer.jdbc.aa.a(Unknown Source)
   at com.mimer.jdbc.Statement.a(Unknown Source)
   at com.mimer.jdbc.PreparedStatement.a(Unknown Source)
   at com.mimer.jdbc.PreparedStatement.<init>(Unknown Source)
   at com.mimer.jdbc.Connection.prepareStatement(Unknown Source)
   at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:216)
   at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:323)
   at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:233)
   at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:61)
   at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:699)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:180)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:128)
   at net.sf.hibernate.loader.Loader.list(Loader.java:918)
   at net.sf.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:109)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3435)
   at net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:177)
   at eriksson.andreas.dao.CityDaoImpl.findByName(CityDaoImpl.java:91)
   at eriksson.andreas.services.AreaServiceImpl.getCitiesByName(AreaServiceImpl.java:109)
   at eriksson.andreas.client.TestClient.main(TestClient.java:65)
16:56:05,591  WARN JDBCExceptionReporter:38 - SQL Error: -12223, SQLState: 42000
16:56:05,591 ERROR JDBCExceptionReporter:46 - ORDER BY clause invalid because it includes a column name that is not part of the result table
16:56:05,611 DEBUG JDBCExceptionReporter:36 - SQL Exception
java.sql.SQLException: ORDER BY clause invalid because it includes a column name that is not part of the result table
   at com.mimer.jdbc.Connection.a(Unknown Source)
   at com.mimer.jdbc.aa.a(Unknown Source)
   at com.mimer.jdbc.Statement.a(Unknown Source)
   at com.mimer.jdbc.PreparedStatement.a(Unknown Source)
   at com.mimer.jdbc.PreparedStatement.<init>(Unknown Source)
   at com.mimer.jdbc.Connection.prepareStatement(Unknown Source)
   at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:216)
   at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:323)
   at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:233)
   at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:61)
   at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:699)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:180)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:128)
   at net.sf.hibernate.loader.Loader.list(Loader.java:918)
   at net.sf.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:109)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3435)
   at net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:177)
   at eriksson.andreas.dao.CityDaoImpl.findByName(CityDaoImpl.java:91)
   at eriksson.andreas.services.AreaServiceImpl.getCitiesByName(AreaServiceImpl.java:109)
   at eriksson.andreas.client.TestClient.main(TestClient.java:65)
16:56:05,611  WARN JDBCExceptionReporter:38 - SQL Error: -12223, SQLState: 42000
16:56:05,611 ERROR JDBCExceptionReporter:46 - ORDER BY clause invalid because it includes a column name that is not part of the result table
16:56:05,621 ERROR JDBCExceptionReporter:38 - SQLException occurred
java.sql.SQLException: ORDER BY clause invalid because it includes a column name that is not part of the result table
   at com.mimer.jdbc.Connection.a(Unknown Source)
   at com.mimer.jdbc.aa.a(Unknown Source)
   at com.mimer.jdbc.Statement.a(Unknown Source)
   at com.mimer.jdbc.PreparedStatement.a(Unknown Source)
   at com.mimer.jdbc.PreparedStatement.<init>(Unknown Source)
   at com.mimer.jdbc.Connection.prepareStatement(Unknown Source)
   at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:216)
   at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:323)
   at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:233)
   at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:61)
   at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:699)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:180)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:128)
   at net.sf.hibernate.loader.Loader.list(Loader.java:918)
   at net.sf.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:109)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3435)
   at net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:177)
   at eriksson.andreas.dao.CityDaoImpl.findByName(CityDaoImpl.java:91)
   at eriksson.andreas.services.AreaServiceImpl.getCitiesByName(AreaServiceImpl.java:109)
   at eriksson.andreas.client.TestClient.main(TestClient.java:65)
16:56:05,621  WARN CityDaoImpl:94 - Unable to perform city find by city name
net.sf.hibernate.JDBCException: SQLException occurred
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3438)
   at net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:177)
   at eriksson.andreas.dao.CityDaoImpl.findByName(CityDaoImpl.java:91)
   at eriksson.andreas.services.AreaServiceImpl.getCitiesByName(AreaServiceImpl.java:109)
   at eriksson.andreas.client.TestClient.main(TestClient.java:65)
Caused by: java.sql.SQLException: ORDER BY clause invalid because it includes a column name that is not part of the result table
   at com.mimer.jdbc.Connection.a(Unknown Source)
   at com.mimer.jdbc.aa.a(Unknown Source)
   at com.mimer.jdbc.Statement.a(Unknown Source)
   at com.mimer.jdbc.PreparedStatement.a(Unknown Source)
   at com.mimer.jdbc.PreparedStatement.<init>(Unknown Source)
   at com.mimer.jdbc.Connection.prepareStatement(Unknown Source)
   at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:216)
   at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:323)
   at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:233)
   at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:61)
   at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:699)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:180)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:128)
   at net.sf.hibernate.loader.Loader.list(Loader.java:918)
   at net.sf.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:109)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3435)
   ... 4 more
16:56:05,641 DEBUG SessionFactoryUtils:179 - Closing Hibernate session
16:56:05,641 DEBUG SessionImpl:544 - closing session
16:56:05,651 DEBUG SessionImpl:3182 - disconnecting session
16:56:05,651 DEBUG SessionImpl:556 - transaction completion


Whats wrong since there really is a column named 'cityName' is a part of the result table?

Running on JDK1.4.2 and Hibernate 2.1 rc1.

Regards, Andreas
Top
 Profile  
 
dmmorris
 Post subject:
PostPosted: Tue Dec 16, 2003 1:56 pm 
Regular
Regular

Joined: Tue Sep 02, 2003 5:09 pm
Posts: 81
Location: Whitefish Montana
What database are you using? Different databases implement this differently. Some allow you to specify anything in the order by, others only the expression/column, and others the alias is supported.
Top
 Profile  
 
andreas_eriksson
 Post subject:
PostPosted: Tue Dec 16, 2003 2:23 pm 
Regular
Regular

Joined: Wed Aug 27, 2003 2:55 am
Posts: 90
Location: Sweden
I'm using Mimer SQL Engine 9.1 (Swedish product http://www.mimer.com).

I've got it working with everything until now :-/

Any tips?

Regards, Andreas
Top
 Profile  
 
dimas
 Post subject:
PostPosted: Tue Dec 16, 2003 4:06 pm 
Expert
Expert

Joined: Tue Sep 16, 2003 4:06 pm
Posts: 318
Location: St. Petersburg, Russia
It looks like Mimer SQL Engine does not recognize column because it has assigned alias ("this.cityId as cityId0_") and it wants the same alias to be used in the ORDER BY clause. You have no control over it.

I'm not an SQL guru and do not know what specification says exactly. But to me alias is nothing more than _second_ name for a thing so Mimer is just wrong.

Maybe Hibernate can be modified to use generated aliases in the ORDER BY clause. Hope someone from Hibertate team will asnwer.
Top
 Profile  
 
dmmorris
 Post subject:
PostPosted: Tue Dec 16, 2003 4:50 pm 
Regular
Regular

Joined: Tue Sep 02, 2003 5:09 pm
Posts: 81
Location: Whitefish Montana
This does seem kind of strange because a similar problem used to affect DB2 on the iSeries. In that case DB2 only worked with the alias and would not allow qualified names in the order by and I had to intercept the order by statement build and remove the table qualification.
Top
 Profile  
 
dimas
 Post subject:
PostPosted: Tue Dec 16, 2003 8:01 pm 
Expert
Expert

Joined: Tue Sep 16, 2003 4:06 pm
Posts: 318
Location: St. Petersburg, Russia
If several databases demonstrate such a behavior, I think Hibernate team will look into this issue. But request on JIRA is usually a better motivation to start than posting on this forum :)
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Tue Dec 16, 2003 8:53 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
I will not change anything here. Any database which has a problem with this should fix the problem themselves. It would be quite difficult to fix this inside Hibernate. Note that none of these dbs have a problem with using the direct column reference in the WHERE clause, and note that the other example of a db which had a problem subsequently fixed it.

So contact the Mimer people and make them fix this absurd limitation.
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
  [ 7 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.