EJB QL LIKE QUERY

[cnsxxx09]

Hi,

In EJB2.0 there is no way to use LIKE with a variable.
e.g.
where o.param LIKE '%?1%'

Reading about 2.1 on the Server Side I could not see any mention of it being a planned/new feature either.

I did find a reference to something in O'Reilly saying that LOCATE could be used instead, as follows:
where LOCATE(o.param, ?1) > -1
but this failed the ejb-ql parser in JBoss3.0.3 as well.

Does anyone have a workaround to get this working in EJB-QL?

I know I could perhaps do it in JBoss-QL but I need my application to also work in WebLogic.

TIA

CNS.
----

[cnsxxx09]

do I take that to mean it can't be done?

Can't believe I'm the only one wanting to do such a fundamental thing

Thanks

C.
--

[schneider]

the ejb-ql uses a PreparedStatment for executing the sql-query,
and as far as I know the PreparedStatement do not support parameters in the LIKE operator.

greetings,
rudi

[noel.rocher]

Hi CNS (what a strange name!),

The EJB2.0 spec is :

===========================================
11.2.7.9 Like expressions
The syntax for the use of the comparison operator [NOT] LIKE in a conditional expression is as follows:
single_valued_path_expression [NOT] LIKE pattern-value [ESCAPE escape-character]
The single_valued_path_expression must have a String value. The pattern-value is a string literal in
which an underscore (_) stands for any single character, a percent (%) character stands for any sequence
of characters (including the empty sequence), and all other characters stand for themselves. The
optional escape-characteris a single character string literal and is used to escape the special meaning of
the underscore and percent characters in pattern-value.
================================

Note the pattern-value definition


In EJB 2.1 it changes to :

=================================
11.2.6.9 Like expressions
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_expressionmust have a string value. The pattern_value is a string literal or a string-valued
input parameter in which an underscore (_) stands for any single character, a percent (%) character
stands for any sequence of characters (including the empty sequence), and all other characters stand for
themselves. The optional escape_character is a single-character string literal or a character-valued
input parameter (i.e., char or Character) and is used to escape the special meaning of the underscore
and percent characters in pattern_value.[23]

==========================

So calm down, it comes !!! ;o)

Noel

[noel.rocher]

Hi CNS (what a strange name!),

The EJB2.0 spec is :

===========================================
11.2.7.9 Like expressions
The syntax for the use of the comparison operator [NOT] LIKE in a conditional expression is as follows:
single_valued_path_expression [NOT] LIKE pattern-value [ESCAPE escape-character]
The single_valued_path_expression must have a String value. The pattern-value is a string literal in
which an underscore (_) stands for any single character, a percent (%) character stands for any sequence
of characters (including the empty sequence), and all other characters stand for themselves. The
optional escape-characteris a single character string literal and is used to escape the special meaning of
the underscore and percent characters in pattern-value.
================================

Note the pattern-value definition


In EJB 2.1 it changes to :

=================================
11.2.6.9 Like expressions
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_expressionmust have a string value. The pattern_value is a string literal or a string-valued
input parameter in which an underscore (_) stands for any single character, a percent (%) character
stands for any sequence of characters (including the empty sequence), and all other characters stand for
themselves. The optional escape_character is a single-character string literal or a character-valued
input parameter (i.e., char or Character) and is used to escape the special meaning of the underscore
and percent characters in pattern_value.[23]

==========================

So calm down, it comes !!!
Maybe in JBoss 4.

;o)

Noel

[noel.rocher]

Hi CNS (what a strange name!),

The EJB2.0 spec is :

===========================================
11.2.7.9 Like expressions
The syntax for the use of the comparison operator [NOT] LIKE in a conditional expression is as follows:
single_valued_path_expression [NOT] LIKE pattern-value [ESCAPE escape-character]
The single_valued_path_expression must have a String value. The pattern-value is a string literal in
which an underscore (_) stands for any single character, a percent (%) character stands for any sequence
of characters (including the empty sequence), and all other characters stand for themselves. The
optional escape-characteris a single character string literal and is used to escape the special meaning of
the underscore and percent characters in pattern-value.
================================

Note the pattern-value definition


In EJB 2.1 it changes to :

=================================
11.2.6.9 Like expressions
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_expressionmust have a string value. The pattern_value is a string literal or a string-valued
input parameter in which an underscore (_) stands for any single character, a percent (%) character
stands for any sequence of characters (including the empty sequence), and all other characters stand for
themselves. The optional escape_character is a single-character string literal or a character-valued
input parameter (i.e., char or Character) and is used to escape the special meaning of the underscore
and percent characters in pattern_value.[23]

==========================

So calm down, it comes !!!
Maybe in JBoss 4.

;o)

Noel

[bleupen]

you are correct that you cannot do this trivial thing in standard ejb-ql (check the sun forums).

i would suggest using jboss-cmp to get the job done but factor your code in such a way that you can substitute a BMP-type finder if you need to support a different application server.

i've written "query" home methods that do this. e.g. declare the method "queryByXXXByYYY()" on your Home interface but don't implement it on your standard bean class. instead, subclass your bean class with a JBossBean.java class that implements the home method (i.e. ejbHomeQueryByXXXByYYY()) using dynamic jboss-ejbql.

b

[cnsxxx09]

Hi,

Thanks for the all replies!

I cannot wait for EJB-QL2.1 (literally!) - according to WebLogic, EJB-QL2.1 isn't planned to be supported until Spring 2004 (which probably means December 2004!), and I need to make sure that it runs on JBoss *and* WebLogic.

WebLogic QL probably already supports the LIKE query (I need to investigate) - is it possible to already have a finder which uses JBoss-QL if deployed on JBoss and WebLogic-QL if deployed on WebLogic, and (possibly) defaults to standard EJB-QL if deployed on anything else?

That's plan B anyway

Cheers

C.
--

[meetian]

as far as I know,
LOCATE works in JBoss
You should tell the db server you plan to use
The postgresql server "LOCATE" function is not working
I have post one fix on forum(but no one notice)
Anyway,"Locate" workaround can work for me with postgres

James