Problems with parameterized LIKE emulation

[mverbist]

Hi,

I wanted a parameterized LIKE in EJBQL.
Seems this isn't possible until EJB 2.1
I found a way around for the start of a word.
It goes like this.
SELECT OBJECT(l) FROM Label l WHERE SUBSTRING(l.name, 1, ?2) = ?1
Where the second parameter is the length of the like-string.
This worked fine on MySQL.
But now I have done a migration to Oracle, it results in an error.
The JBoss console gives me this:
15:43:47,984 DEBUG [findLike] Executing SQL: SELECT t0_l.ID FROM IRIS_LABELS t0_
l WHERE substring(t0_l.NAME FROM 1 FOR ?) = ?
15:43:47,984 DEBUG [findLike] Find failed
java.sql.SQLException: ORA-00907: missing right parenthesis

Anybody an idea why this would fail on Oracle and not on MySQL?

Thx.

[lafr]

You may find it easier to use the jboss-ql, a superset of ejb-ql.
One example from me (XDoclet):
* @ejb.finder signature="java.util.Collection findByMatchcode( java.lang.String matchcode )"
* view-type="local"
* query="SELECT OBJECT(o) FROM Address AS o WHERE o.matchcode LIKE '%SIEMENS%'"
* result-type-mapping="Local"
* @jboss.query signature="java.util.Collection findByMatchcode( java.lang.String matchcode )"
* query="SELECT OBJECT(o) FROM Address AS o WHERE o.matchcode LIKE ?1 ORDER BY o.addressId"

[mverbist]

Easyer yes.
But less portable also.
And since we are using JBoss for development environment and Websphere for production, I won't be able to do it this way.
Thanks for the reply though.

The only option I see left is to go full BMP in stead of CMP.
Or does anyone see an other solution?