-->
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
  [ 10 posts ] 
  Previous topic | Next topic 
Author Message
alecswan
 Post subject: HQL string functions
PostPosted: Tue Aug 02, 2005 6:47 pm 
Regular
Regular

Joined: Fri Mar 26, 2004 11:45 am
Posts: 75
According to Hibernate documentation length(), as well as other EJB-QL 3.0 functions, such as trim(), are supported. However, the following HQL query does not work:

select length(trim(obj.ObjName))
from Objective as obj

While this HQL query works (on SQL Server):

select len(ltrim(obj.ObjName))
from Objective as obj

Am I missing something or should the documentation be corrected?

Thanks.
Top
 Profile  
 
jdl
 Post subject: Re: HQL string functions
PostPosted: Wed Aug 03, 2005 11:05 am 
Regular
Regular

Joined: Thu May 26, 2005 2:08 pm
Posts: 99
alecswan wrote:
select length(trim(obj.ObjName))
from Objective as obj


Using Hibernate 3.0.5 and MySQL 4.0.24, both of those functions worked for me.
Top
 Profile  
 
alecswan
 Post subject:
PostPosted: Wed Aug 03, 2005 11:51 am 
Regular
Regular

Joined: Fri Mar 26, 2004 11:45 am
Posts: 75
This is because MySQL supports both length and trim. For databases where these functions are named differently, Hibernate should be able to translate these function names to something the database understands. For example,
if we are using SQLServer dialect, then "length" should be translated to "len".

But I guess it just doesn't work in this release.

Regards.

Alec
Top
 Profile  
 
sven
 Post subject:
PostPosted: Wed Aug 03, 2005 12:07 pm 
Expert
Expert

Joined: Thu Dec 04, 2003 12:36 pm
Posts: 275
Location: Bielefeld, Germany
When looking at the correspondig Dialect classes (SQLServerDialect and SybaseDialect - SQLServerDialect extends SybaseDialect) you can see that length is handled as len, whereas there is no substitution for trim, since as far as I know there is only ltrim and rtrim for SQL server.

Maybe you can combine these as a substitution for trim?

This is possbily something for JIRA.

Best regards
Sven
Top
 Profile  
 
jdl
 Post subject:
PostPosted: Wed Aug 03, 2005 12:14 pm 
Regular
Regular

Joined: Thu May 26, 2005 2:08 pm
Posts: 99
As a quick test to see how the dialects work, I did the following. It worked fine. You should be able to do something similar for the functions that you need.

I wanted to use a function "foo(string)" and have it turned into "length(string)" in the SQL.

Code:
package hibernatetest;

import org.hibernate.Hibernate;
import org.hibernate.dialect.MySQLDialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;

public class TestMySQLDialect extends MySQLDialect {

     public TestMySQLDialect() {
        super();
       
        registerFunction( "foo", new SQLFunctionTemplate(Hibernate.STRING, "length(?1)") );
    }

}




Now when I put "foo(...)" into my HQL, it came out as "length(...)" in the SQL.


Go dig into "SQLServerDialect" and "SQLFunctionTemplate" to see what's registered for your dialect.
Top
 Profile  
 
sven
 Post subject:
PostPosted: Wed Aug 03, 2005 12:21 pm 
Expert
Expert

Joined: Thu Dec 04, 2003 12:36 pm
Posts: 275
Location: Bielefeld, Germany
Well, in this case you won't need it for length, but you might combine ltrim and rtrim like this:

Code:
registerFunction( "trim", new SQLFunctionTemplate(Hibernate.STRING, "ltrim(rtrim(?1))") );


Haven't tested it...

Best regards
Sven
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Wed Aug 03, 2005 1:09 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Sven, I comitted that patch to CVS.
Top
 Profile  
 
alecswan
 Post subject:
PostPosted: Wed Aug 03, 2005 1:28 pm 
Regular
Regular

Joined: Fri Mar 26, 2004 11:45 am
Posts: 75
Thanks for applying the trim patch.

As for the "length" function I can see that it was mapped in SybaseDialect class relatively recently (2005/5/26). So, my eclipse hibernate3 plugin does not have this latest code. So, you are right, it is fixed in the latest build.

Thanks.
Top
 Profile  
 
prashantjain
 Post subject:
PostPosted: Thu May 04, 2006 5:47 am 
Regular
Regular

Joined: Wed Mar 08, 2006 2:07 am
Posts: 50
Location: Bangalore
This is still not working for me !

I am using version 3.1.2

Change log says :
Code:
Changes in version 3.1 alpha 1 (24.06.2005)
------------------------------------
    * [HHH-538] - length() function does not work in SQLServerDialect



Code inside SybaseDialect :

Code:
   public SybaseDialect() {
      super();
......
      registerFunction( "len", new StandardSQLFunction("len", Hibernate.LONG) );
.....
      registerFunction( "length", new StandardSQLFunction( "len", Hibernate.INTEGER ) );
.....
}




Still I am getting this exception :
Seems like "length" is not getting changed to "len" as it should be for SQLServerDialect (my dialect).


Code:
Exception in thread "AWT-EventQueue-2" org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not execute query; bad SQL grammar [select patientflo0_.ENC_ID as ENCOUNTER1_, patientflo0_.LAST_UPDATE_TIME as LAST2_, patientflo0_.VAR as VAR3_, patientflo0_.PARAMETER_VALUE as PARAMETER4_10_, patientflo0_.DELETED as DELETED10_ from patientflo patientflo0_ where and patientflo0_.VAR=1 and length(patientflo0_.PARAMETER_VALUE)>0]; nested exception is java.sql.SQLException: 'length' is not a recognized function name.
java.sql.SQLException: 'length' is not a recognized function name.


Shouldnt there be a line of code such as

registerFunction("len",new SQLFunctionTemplate("len(?1)");

in the dialect class!



regards
Prashant
Top
 Profile  
 
prashantjain
 Post subject:
PostPosted: Thu May 04, 2006 10:42 am 
Regular
Regular

Joined: Wed Mar 08, 2006 2:07 am
Posts: 50
Location: Bangalore
It sorted out when I replaced an older jar which was being referenced in my classpath during runtime, which newer 3.1.2 version.
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
  [ 10 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.