EJB-QL using LIKE with input parameter

[cjne]

Hello,

This is probably not a JBossCMP specific question but i thought that someone probably knows the answer here.

My problem is that i need to do a search for users using LIKE as i would do using SQL. The search string comes from user input so it can't be hard coded in my finder method. I have tried to do a query that looks like this:
SELECT OBJECT(o) FROM Member o WHERE m.userName LIKE ?1
But that gives my an error when i deploy it (EJB-QL compilation error) howerver if i do this:
SELECT OBJECT(o) FROM Member o WHERE m.userName LIKE '%test%' it doesn't complain.

This must be a pretty common use case and i'm sure that i must have missed something, or shouldn't this be possible with EJB-QL?

Regards,
Johan ISacsson

[dsundstrom]

This is supported in JBossQL which is an extension of EJB-QL. To use JBossQL, copy (not move) your query element the ejb-jar.xml file to the jbosscmp-jdbc.xml file. Change ejb-ql tag in the jbosscmp-jdbc.xml file to jboss-ql, and make the ejb-ql tag in the ejb-jar.xml file an empty element (i.e., <ejb-ql/>). JBossQL supports parameters in IN and LIKE clauses, and the ORDER BY clause proposed for EJB 2.1.

[yyi2133]

Yes, this is a good workaround but it's also a JBOSS limitation! in fact JBoss claim to be full EJB-QL compliant but EJB-QL specification states that:
The syntax for the use of the comparison operator [NOT] LIKE in a conditional expression is as follows:

cmp_path_expression [NOT] LIKE pattern_value [ESCAPE escape_character]

The cmp_path_expression must have a string value. The pattern_value is a string literal or a string-valued input parameter in which an underscore (_) stands for [...]
So the "like" expression can be an input parameter! however this is an extract from EJB v.2.1 not 2.0 . I don't know exactly if it is also a 2.0 requirement... however, does anybody know when this feature will be available in JBOSS?

[dsundstrom]

JBoss is written to the final EJB 2.0 specification. You are quoting from a draft of the 2.1 specification. It would be stupid for JBoss main releases to be witten to a draft specification. JBoss 4.0 will be written to the EJB 2.1 specification. If you would like to use parameters in LIKE clauses you must use the non spec compliant JBossQL.

The draft EJB 2.1 spec still falls short because it does not have a UCASE or LCASE function so you can't do a caseless search.