I have a table, Address, with a couple fields, 'numStart' and 'numEnd'. When a user searches for Addresses he can specify the Street name and optionally, a Number. I would like to do a query like this:

from Address where street.name like ? and and ? between numStart and numEnd

The only problem is that 'numStart' and 'numEnd' are TEXT fields, not numbers. (I inherited the database ...) So HQL chokes. If I run the equivalent SQL query directly in mySql it works, it seems to convert the text field values to numbers on the fly.

Can I do this with HQL?

Thanks,
Bob

Hi,
If you compare the number with varchar column in sql then number will be automatically converted into string(Note that values in that column value must be number - only column type is varchar). This is rthe reason sql is working fine.


If you want to do this in HQL then,

use setString() of Query object to set the number in your example. Again note that value in 'numStart' and 'numEnd' must be number value.

Cool, I'll give it a try. Since I am using Spring with Hibrnate, up until now I have been using Spring's getHibernateTemplate() but I guess I can use a Query object. I'll let the list know if it worked, many thanks.

Bob