On Tue, May 08, 2007 at 11:00:54PM +0200, To persistence_at_glassfish.dev.java.net wrote:
> On Tue, May 08, 2007 at 09:42:08PM +0200, Michael Bouschen wrote:
> > I think your query works. The actual value of the input parameter might
> > include the wild card, e.g. q.setParameter("param", "hello%").
>
> Hmmm interesting thought. My experience so far with e.g. Oracle's ADF framework
> has led me to believe that the % in the query will be taken literally since it's
> part of the parameter and not concatted to the parameter. For some reason this
> is different in ADF. I'll have a look at this to see what happens.
Michael,
Thanks again for your comments. I have tried out your suggestion and found that
this indeed works like a charm :-) So, passing on a parameter with % and _ in it
makes the LIKE behave as expected.
One slight annoyance is that it's not allowed to combine LIKE and a String
fuction like UPPER() when applied to the named parameter. In other words, this
is not allowed:
select m from MyTable m where upper(m.name) like upper(:name)
This, however, IS allowed:
select m from MyTable m where upper(m.name) like :name
If the value passed to :name is put to upper case before this query is
executed, a case insensitive query is created.
Please note that the above can only be applied when m.name is a String column
(varchar2, clob, etc).
Thanks, Wouter
--
People: "If she weighs the same as a Duck, she's made of wood!"
Sir Bedevere: "And therefore...?"