4 Replies Latest reply on Aug 25, 2007 6:31 PM by kumlali

    Entity-query of Contact List sample does not run against Ora

    kumlali Newbie

      Hi all,

      I'm trying Contact List sample to be run against Oracle. Everything goes well until I make a search by name. When I enter 'Gav' for the first name and 'K' for the last name, Seam (Oracle actually) gives an error that says: ORA-01722: invalid number! I found the reason was "contacts" entity-query found in components.xml:

       <fwk:entity-query name="contacts"
       max-results="5">
       <fwk:ejbql>from Contact</fwk:ejbql>
       <fwk:order>lastName</fwk:order>
       <fwk:restrictions>
       <value>lower(firstName) like lower( #{exampleContact.firstName} + '%' )</value>
       <value>lower(lastName) like lower( #{exampleContact.lastName} + '%' )</value>
       </fwk:restrictions>
       </fwk:entity-query>
      


      This query definition creates following SQL that HSQL accepts, but Oracle does not:

      select *
      from (
       select ...
       from CONTACT c
       where (lower(c.firstName) like lower('Gav') + '%')
       and (lower(c.lastName) like lower('K') + '%')
       order by ...
      ) where rownum <= ...
      


      What part Oracle does not allow is concatenation operation, '+'. Therefore, following SQL is perfectly acceptable (also by HSQL):
      select *
      from (
       select ...
       from CONTACT c
       where (lower(c.firstName) like lower('Gav%'))
       and (lower(c.lastName) like lower('K%'))
       order by ...
      ) where rownum <= ...
      


      What entity-query should I write to let Seam generate Oracle friendly SQL?

      Regards,

      Ali Sadik Kumlali