[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - case insensitive finder

[sigitm]

Hi!

I'm trying to build a finder that uses user input to lookup entries in a database.
I'm using a CMP entity EJB (2.1) in JBoss 4.0.5.GA (using Hypersonic), with this finder:

 * @ejb.finder
  |  * 		signature = "java.util.Collection findByFields(java.lang.String id, java.lang.String author, java.lang.String title, java.lang.String keywords)"
  |  *  	query = "SELECT OBJECT(o) FROM DocumentEJB o WHERE o.id LIKE ?1 AND o.author LIKE ?2 AND o.title LIKE ?3 AND o.keywords LIKE ?4"


The finder is case sensitive, but I need to make a case insensitive lookup.
I tried with this trick I found in this forum:


 * @ejb.finder
  |  * 		signature = "java.util.Collection findByFields(java.lang.String id, java.lang.String author, java.lang.String title, java.lang.String keywords)"
  |  *  	query = "SELECT OBJECT(o) FROM DocumentEJB o WHERE LCASE(o.id) LIKE LCASE(?1) AND LCASE(o.author) LIKE LCASE(?2) AND LCASE(o.title) LIKE LCASE(?3) AND LCASE(o.keywords) LIKE LCASE(?4)"


... but it doesn't work:


org.jboss.ejb.plugins.cmp.ejbql.ParseException: Encountered "LCASE" at line 1, column 44.
  | Was expecting one of:
  |     "NOT" ...
  |     "(" ...
  |     <COLLECTION_VALUED_PATH> ...
  |     <STRING_VALUED_PATH> ...
  |     "CONCAT" ...
  |     "SUBSTRING" ...
  |     <BOOLEAN_VALUED_PATH> ...
  |     <DATETIME_VALUED_PATH> ...
  |     <ENTITY_VALUED_PATH> ...
  |     <IDENTIFICATION_VARIABLE> ...
  |     <NUMERIC_VALUED_PATH> ...
  |     "LENGTH" ...
  |     "LOCATE" ...
  |     "ABS" ...
  |     "SQRT" ...
  |     "MOD" ...
  |     "+" ...
  |     "-" ...
  |     <INTEGER_LITERAL> ...
  |     <FLOATING_POINT_LITERAL> ...
  |     <NUMERIC_VALUED_PARAMETER> ...
  | 


So, how can I do?
Is there a way to make the LIKE command case insensitive?


Thanks.

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4007999#4007999

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4007999