[jboss-user] [JBoss Seam] - Entity-query of Contact List sample does not run against Ora

[kumlali]

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


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

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