[hibernate-issues] [Hibernate-JIRA] Resolved: (HHH-811) Error when using Expression.ilike with Firebird

Error when using Expression.ilike with Firebird ----------------------------------------------- Key: HHH-811 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-811 Project: Hibernate Core Issue Type: Bug Components: core Affects Versions: 3.1 beta 1 Environment: WIN2K - SP4, J2SDK(1.4.2_08), Tomcat 5.5.7 (Netbeans 5.1), Firebird 1.5.1.4481, JDBC 1.5.5 (jaybird). Reporter: Bento XVI Assignee: Strong Liu Priority: Minor I'm using hibernate with firebird (my dialect property is org.hibernate.dialect.FirebirdDialect). If I create a criteria like this "criteria.add(Expression.ilike(String, String, MatchMode.ANYWHERE));", I receive the following error: SEVERE: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -804 Function unknown LOWER 30/07/2005 11:29:59 org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet jsp threw exception org.hibernate.exception.GenericJDBCException: could not execute query The problem is that Firebird doesn't have the LOWER function. The sql generated by hibernate (select this_.cod_field as cod1_0_, this_.txt_field as txt2_0_0_ from MYTABLE this_ where lower(this_.txt_field) like ? order by this_.txt_field asc) throws the same error if executed directly in Firebird. I'm posting this bug as "Critical" because it produces a crash. But, at the same time lots of workarounds are possible. The most simple is to use createQuery instead createCriteria. Another one is to write my own FirebirdDialect and override the getLowercaseFunction() and make it returns "upper" instead "lower".