Problem with formula

dejan · **Posted:** Sat Nov 06, 2004 4:07 pm

I am using Hibernate 2.1.6 with SQL Server. I have following property in my mapping file (I want to convert decimal column to date):

Code:

<property formula="CAST(MODIFIED as datetime) - 2" name="modified" type="java.util.Date"/>

This formula is valid sql expression in SQL Server. However, Hibernate generates:

Code:

CAST(badgeacces0__1_.MODIFIED badgeacces0__1_.as badgeacces0__1_.datetime) -2 as f6_

instead of something like:

Code:

CAST(badgeacces0__1_.MODIFIED as datetime) -2 as f6_

Why Hibernate appends table alias to reserved words? Is it possible to prevent this? Am I doing something wrong?

Thanks in advance,
Dejan

anthony · **Posted:** Sat Nov 06, 2004 4:12 pm

try changing

Code:

<property formula="CAST(MODIFIED as datetime) - 2" name="modified" type="java.util.Date"/> 

to

Code:

<property formula="(CAST(MODIFIED as datetime) - 2)" name="modified" type="java.util.Date"/> 

dejan · **Posted:** Sat Nov 06, 2004 5:51 pm

It does not help. The same sql is generated except this time sql expression is in braces.

Regards,
Dejan

anthony · **Posted:** Sat Nov 06, 2004 6:25 pm

ok debug with hibernate source, you may be able to extend some dialect

dejan · **Posted:** Sat Nov 06, 2004 6:34 pm

anthony wrote:

ok debug with hibernate source, you may be able to extend some dialect

OK. Can you give me some hints. How Hibernate knows what are reserved words for some dialect? Or how it decide what to prefix with table aliases?

Thanks in advance,
Dejan

dejan · **Posted:** Sat Nov 06, 2004 8:26 pm

I did hacked a little and now it works. I have created new Dialect by extending SqlServerDialect, and I have registered 'as' and 'datetime' keywords as functions so Template.renderWhereStringTemplate() method does not prefix them with table alias. It is ugly but works :)

However, I think that 'as' is standard sql keyword that should be in KEYWORDS map of net.sf.hibernate.sql.Template class. Also, isIdentifier() method should be little smarter and check if token is not reserved word for given dialect. That way hacks like the one I have done would not be needed!

Regards,
Dejan

anthony · **Posted:** Sun Nov 07, 2004 4:06 am

be carefull test an hql like this:

select obj from Object as obj and tell me if it works

dejan · **Posted:** Sun Nov 07, 2004 5:07 am

anthony wrote:

be carefull test an hql like this:

select obj from Object as obj and tell me if it works

Yes it does work (with custom dialect). I haven't tried to change net.sf.hibernate.sql.Template class to add things I have explained in previous post.

Regards,
Dejan

yamichaud · **Joined:** Mon Jun 25, 2012 10:52 am **Posts:** 2

Is there any chance for this problem to be corrected. I'm using Hibernate 3.6.10 and I'm still facing this aliasing issue when mapping many-to-one with formula.

Code:

<formula>cast(dest as integer)</formula>

is making the following sql query :

Code:

cast(this_.dest as this_.integer)

Regards

Yoann

matguls · **Joined:** Thu Apr 04, 2013 1:45 pm **Posts:** 1

hi all,

I found the solution, just use double-quote around the reserved word. For example:

Code:

cast(my_date as "date")