Hi all,

i am focusing an error, when trying to use ilike operator in a hql query.
I get everytime the message:
unexpected token: ilike

When using only like it works without any Problem.

Unfortunatly Postgres is using ilike as case sensitive, so I need to use ilike.

Is this a bug in the WhereParser ?
I was not able to find anything in the Threads...
Everyone in the Threads i found suggested using a Criteria and they are right it works in it, but I need to use it with a Query.

Thanks in advance.
Ali

Stats:
------------------------------------------
Hibernate version: 3.1.2[/b]
Code between sessionFactory.openSession() and session.close():
Query theQuery = theSession.createQuery("SELECT comp " +
"FROM Address adr, House h, Postcode pc, Street str, Town t, State sta, Country c, Company comp " +
"WHERE adr.houseid = h.id " +
"AND h.postcodeid = pc.id " +
"AND h.streetid = str.id " +
"AND str.townid = t.id " +
"AND t.stateid = sta.id " +
"AND sta.countryid = c.id " +
"AND pc.postcodenumber LIKE :postcodenumber " +
"AND t.name ilike :townname " +
"AND comp.adressesid = adr.adressid " +
"AND comp.name1 ilike :companyname " +
"ORDER BY comp.name1");
theQuery.setString("postcodenumber", postcodenumber+"%");
theQuery.setString("townname", townname+"%");
theQuery.setString("companyname", companyname+"%");

list=find(theSession, transaction, theQuery);


Exception:
Message
line 1:607: unexpected token: ilike


Database: PostgreSQL 8.1

The generated SQL :
SELECT comp FROM de.haiberg.adman.app.beans.adressbase.Address adr
, de.haiberg.adman.app.beans.adressbase.House h, de.haiberg.adman.app.beans.adressbase.Postcode pc, de.haiberg.adman.app.beans.adressbase.Street str, de.
haiberg.adman.app.beans.adressbase.Town t, de.haiberg.adman.app.beans.adressbase.State sta, de.haiberg.adman.app.beans.adressbase.Country c, de.haiberg.a
dman.app.beans.company.Company comp WHERE adr.houseid = h.id AND h.postcodeid = pc.id AND h.streetid = str.id AND str.townid = t.id AND t.stateid = sta.i
d AND sta.countryid = c.id AND pc.postcodenumber LIKE :postcodenumber AND t.name ilike :townname AND comp.adressesid = adr.adressid AND comp.name1 ilike
:companyname ORDER BY comp.name1

HQL does not support the ilike operator. Have a look at refdocs section 14.9, "Expressions", for a list of what you can put in there.

Your options are either to switch to Criteria, which do support ilike, or else do it the old fashioned way: force both sides of the like operator to be in the same case. HQL supports both upper() and lower(), so you code might look like "upper(t.name) like upper(:townname)", etc.

Thanks for the Help.

I know the workaround with lower() or upper(), but the problem with this is that you can not use indexes for this fields, when using this functions on the fields. This is only good for small tables.
As a result you may have problems on a table with some 100 million entries, when querying them.

My idear was to create a new Dialect and to overwrite the like operator return value.
As a result the query with like should generate ilike on every like usage.
But I don't know if it is possible and how to do it.
It would realy help me, if someone knows how to do it.

My next try would be to use:
query substitutions, to replace like with ilike in the query.
using:
<prop key="hibernate.query.substitutions">like ilike</prop>

in hibernate-config.xml

Hope it works...

Greets
Ali

Hello,

you could/should use a function based index for comparing case insensitive.

Taken from the PostgreSQL Docs :



Take a look at : http://www.postgresql.org/docs/8.1/inte ... ional.html

Hope this helps.