exception while executing HQL statement

vrkraju · **Joined:** Fri Apr 27, 2007 8:17 am **Posts:** 2

Hi,

I am getting exception while executing the following HQL statement.
The exception is , 'substr is not recongnised function' in the query.though i have used substring method. I am using jTDS driver for SqlServer 2005.

The query is ,
"select substring(ltrim(usr_first_name),1,1) from ep_usr ".

Hibernate version: hibernate3
Name of the database : SqlServer 2005.

Please provide a solution for this.

Thanks,
Ravikumar Raju V.

chucky · **Joined:** Mon Jun 12, 2006 5:33 am **Posts:** 63

Hi vrkraju,
what is the whole function call? Is it a createQuery?
For native SQL, also consider using formula in the mapping files.

vrkraju · **Joined:** Fri Apr 27, 2007 8:17 am **Posts:** 2

Hi,

I am using createQuery function. The whole query is

session.createQuery("select upper(substring(ltrim(usr_First_Name),1,1)) from usr");

The same query should be worked in both Oracle and SqlServer database.
Pls tell me what are the changes to be done.

For SqlSever i am using , jTDS dirver.
For Oracle it is, ojdbc14 driver.

Thanks,
Ravi.

chucky · **Joined:** Mon Jun 12, 2006 5:33 am **Posts:** 63

Hi vrkraju,
1/ - when using the createQuery function, you must provide a HQL as argument and NOT native SQL. So your query is class and object oriented.

2/ - in you query :

Code:

select upper(substring(ltrim(usr_First_Name),1,1)) from usr

it seams that you have a class called usr (lowercase.... Strange for a java class) and a property called usr_First_Name. If this is the case try this:

Code:

select upper(substring(ltrim(u.usr_First_Name),1,1)) from usr u

If not, replace the corresponding mappings for usr (use the class name) and for usr_First_Name (use the property name).

Hope this helps