Hibernate 3.6.0

Hi
Newbie - so go easy :)
I am using Hibernate with Spring where I have an employee table with a string reference.
I want to add a new property for a specific instance which casts the reference as numeric
(in this case all the employee references are numeric)
so in employee.hbm.xml I have ..


However on loading app I am getting error (MySQLSyntaxErrorException) because the 'generated' SQL looks like


I would appreciate any pointers one may have as to where to look
I'm also unsure if spring has any 'effect' on this.

I have tried various changes including
- trying to write my own custom CastFunction, though it never gets called?
- cast(employeeno as signed) - which is what Mysql would expect.
- trying annotation in model file which same result (@Formula( ..)
- using brackets around whole formula

Thanks
Daniel

The MySQL integer type is SIGNED or UNSIGNED.
http://dev.mysql.com/doc/refman/5.5/en/ ... ction_cast

The formula has to be a SQL fragment. So I believe it should be:

Me bad! See that you have attempted this before with no luck.

For those interested, I have used some lateral thinking as a workaround

The idea behind the issue was that I need a numeric sort of the employees for a screen display
I am using the Hibernate Generic DAO framework http://code.google.com/p/hibernate-generic-dao/ for searching, so what I did in the end was allow it to do a custom sort of type "cast(employeeno as integer)"

It aint elegant, in fact its rather nasty, as you cant extend the HibernateSearchProcessor. However it does work and that will suffice for now and the real solution becomes a lower priority.

Daniel

FWIW, it appears to be a known defect. In general values referred to in the formula are replaced by their property references and there doesn't seem to be any way to escape them.

http://opensource.atlassian.com/project ... se/HHH-473