-->
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.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: Hibernate : invalid ORDER BY expression
PostPosted: Mon May 08, 2017 2:06 pm 
Newbie

Joined: Mon Mar 06, 2017 9:15 am
Posts: 18
I have tried to add some Order to my Criteria like this.

Code:
crit.addOrder(Order.asc("priority")); // Not the real property though


And test on my System using a MsAccess database with HSQLDialect And i get

Quote:
Caused by: org.hsqldb.HsqlException: invalid ORDER BY expression


So i tried to show the query, it reads like this

Code:
Hibernate:
/* criteria query */ select
    distinct this_.i_id as y0_
from
    T_FIT_ARTICLE this_
where
    (
        this_.b_export_ready=?
        or this_.d_application_deadline>=?
    )
order by
    this_.d_application_deadline asc limit ?
//FROM HERE THE STACK TRACE


the thing is, i used the @OrderBy annotation on my collection before, which produce almost the same query, and this works :
Code:
order by
    elements2_.i_order_nr asc limit ?


But with the attributes in my Entity, it causes exception.

By the way, if i try to test the same programm with MYSQLServer, it works perfectly.

Any idea why or how could i avoid this ?

Many thanks !


Top
 Profile  
 
 Post subject: Re: Hibernate : invalid ORDER BY expression
PostPosted: Mon May 08, 2017 2:21 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
Quote:
And test on my System using a MsAccess database with HSQLDialect


Why are you using the HSQLDialect with MsAccess? If you want to use MsAccess, you need to use a 3rd party Dialect that's built for Access since Hibernate does not support MsAccess.

Quote:
By the way, if I try to test the same program with MYSQLServer, it works perfectly.


There is no MYSQLServer database. There is MySQL and SQL Server though. Either way, for MySQL you need to use a MySQL Dialect while for SQL Server you need to use a SQL Server Dialect.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate : invalid ORDER BY expression
PostPosted: Mon May 08, 2017 2:53 pm 
Newbie

Joined: Mon Mar 06, 2017 9:15 am
Posts: 18
vlad wrote:
Quote:
And test on my System using a MsAccess database with HSQLDialect


Why are you using the HSQLDialect with MsAccess? If you want to use MsAccess, you need to use a 3rd party Dialect that's built for Access since Hibernate does not support MsAccess.

Quote:
By the way, if I try to test the same program with MYSQLServer, it works perfectly.


There is no MYSQLServer database. There is MySQL and SQL Server though. Either way, for MySQL you need to use a MySQL Dialect while for SQL Server you need to use a SQL Server Dialect.


Because i use ucanaccess as driver http://ucanaccess.sourceforge.net/site.html to connect with the MS Accesss database, and this middleware uses HSQLDialect.

There is no Hibernate supported Dialect for MsAccess, so i have no other choice.


Top
 Profile  
 
 Post subject: Re: Hibernate : invalid ORDER BY expression
PostPosted: Mon May 08, 2017 4:21 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
Then you should have mentioned this instead of MsAccess.

The reason why you got an exception is easy to understand if you know the execution order of SQL clauses. ORDER BY is executed after SELECT so it can only reference selected columns. Therefore, you must make sure you always select that column you use for ordering the result set.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


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

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.