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
_______________________________________________
jboss-user mailing list
[email protected]
https://lists.jboss.org/mailman/listinfo/jboss-user
