0 Replies Latest reply on Jan 29, 2007 4:09 PM by Marco Sigismondi

    case insensitive finder

    Marco Sigismondi Newbie

      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.