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

[fhh]

String concatenation in SQL is either done by the concat() function (ANSI) or by using two pipes (|| - supported by almost all rdbms).

Regards

Felix

[kumlali]

Thanks Felix. I was actually looking for EJB QL expression, not SQL function. Anyway, your reply helped me find following comment from Hibernate Reference document[1]:

string concatenation ...||... or concat(...,...) (use concat() for portable EJB-QL queries)

After I changed the query as follows, it worked with both Oracle and HSQL:

 <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( concat(#{exampleContact.firstName}, '%' ))</value>
 <value>lower(lastName) like lower( concat(#{exampleContact.lastName}, '%') )</value>
 </fwk:restrictions>
 </fwk:entity-query>

Sample's code in CVS should be changed to make the application more portable, I believe. If Seam developers think the same way, I can create a Jira issue.

Regards,

Ali Sadik Kumlali


[1] http://www.hibernate.org/hib_docs/entitymanager/reference/en/html/queryhql.html

[christian.bauer]

Please open a JIRA issue.

[kumlali]

Done: http://jira.jboss.org/jira/browse/JBSEAM-1864

Regards,

Ali Sadik Kumlali