matchExp case sensitivity behavior is database dependent, as you've found.
I don't think there's any database-independent way to do
case-sensitivity-specific queries, which is why likeIgnoreCaseExp() is
used.
You're probably better off writing custom sql to handle this if you
can't be sure you've dealt with escaping.
For example, Oracle handles it by using "UPPER(parameter) like
UPPER(value)" (if I remember offhand).
Another possiblity is to use likeIgnoreCaseExp to pull in results, but
then go in and manually filter out anything that's not an exact match
in your code. That's probably the safest bet and the most portable.
Of course, you then have to deal with the possiblity that someone's
password is "%"
On 8/1/06, Øyvind Harboe <oyvind.harbo..ylin.com> wrote:
> I need a matchExp which is case insensitve. When I run against MS
> Access, then matchExp is indeed case insensitive, whereas when I run
> against Derby, it is not.
>
> likeIgnoreCaseExp is *almost* what I'm looking for, but it allows wildcards.
>
> In my app, I do a matchExp(Users.PASSWORD_PROPERTY, password), where
> using likeIgnoreCaseExp would work much too well :-) Don't even ask
> why the passwords are stored in cleartext in the database.
>
> If I could find a way to safely escape the password, then I could use
> likeIgnoreCaseExp, i.e . "%" and other special chars for
> likeIgnoreCaseExp should be escaped so they are treated as literal
> chars.
>
> Problems with writing an escape fn from scratch:
>
> - how do I know that I've written a complete and correct escape function?
> - how do I know that the code I wrote won't break with improvements to
> likeExp() and likeIgnoreCaseExp()?
>
>
> --
> Øyvind Harboe
> http://www.zylin.com
>
This archive was generated by hypermail 2.0.0 : Tue Aug 01 2006 - 17:42:54 EDT