You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by Robert Dietrick <ro...@nokia.com> on 2006/01/28 05:44:30 UTC
case insensitive queries
I'm having difficulties getting a portion of my WHERE clause to be case
insensitive with Oracle, and I'm wondering if this problem's been solved
already.
I've tried Criteria.setIgnoreCase(true), which forces all parts of the
WHERE clause to ignore case, and I've tried
Criterion.setIgnoreCase(true), which does absolutely nothing (does this
work with other databases???).
I've also tried the custom route: Criteria.add(columnName,
"UPPER("+columnName+") = " + someValue.toUpperCase()). But this is
inherently dangerous when the value (someValue) is coming from an end
user. (May allow users to execute arbitrary SQL.)
What I want is something which results in the left half being converted
to upper case and the right half still using a bind variable:
UPPER(columnName) = ?
Anyone know if this is possible? I'm using the latest version (3.2).
Thanks.
-rob
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: case insensitive queries
Posted by Robert Dietrick <ro...@nokia.com>.
I've found the bug in the Criteria class.
In the method Criteria.Criterion.appendPSTo(), case sensitivity is
determined by the variable ignoreCase, which is defined in the Criteria
class (NOT the Criterion class). This explains exactly the behavior I
experienced (case sensitivity may be turned off for ALL where clause
elements but not individual ones).
This bug only manifests itself when using PreparedStatements
(doPSSelect()). The Criterion.appendTo() method correctly uses the
local ignoreStringCase variable.
I'll create an issue in scarab and submit the patch.
-Rob
ext Thomas Fischer wrote:
> As far as I know, Criterion.setIgnoreCase should do the trick. I have used
> it myself with oracle.
> If it does not work anymore, it is a bug. Can you please produce a test
> case and create an issue in scarab ?
>
> Thanks,
>
> Thomas
>
> Robert Dietrick <ro...@nokia.com> schrieb am 28.01.2006 05:44:30:
>
>> I'm having difficulties getting a portion of my WHERE clause to be case
>> insensitive with Oracle, and I'm wondering if this problem's been solved
>> already.
>>
>> I've tried Criteria.setIgnoreCase(true), which forces all parts of the
>> WHERE clause to ignore case, and I've tried
>> Criterion.setIgnoreCase(true), which does absolutely nothing (does this
>> work with other databases???).
>>
>> I've also tried the custom route: Criteria.add(columnName,
>> "UPPER("+columnName+") = " + someValue.toUpperCase()). But this is
>> inherently dangerous when the value (someValue) is coming from an end
>> user. (May allow users to execute arbitrary SQL.)
>>
>> What I want is something which results in the left half being converted
>> to upper case and the right half still using a bind variable:
>> UPPER(columnName) = ?
>>
>> Anyone know if this is possible? I'm using the latest version (3.2).
>>
>> Thanks.
>>
>> -rob
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: torque-user-help@db.apache.org
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: case insensitive queries
Posted by Thomas Fischer <fi...@seitenbau.net>.
As far as I know, Criterion.setIgnoreCase should do the trick. I have used
it myself with oracle.
If it does not work anymore, it is a bug. Can you please produce a test
case and create an issue in scarab ?
Thanks,
Thomas
Robert Dietrick <ro...@nokia.com> schrieb am 28.01.2006 05:44:30:
> I'm having difficulties getting a portion of my WHERE clause to be case
> insensitive with Oracle, and I'm wondering if this problem's been solved
> already.
>
> I've tried Criteria.setIgnoreCase(true), which forces all parts of the
> WHERE clause to ignore case, and I've tried
> Criterion.setIgnoreCase(true), which does absolutely nothing (does this
> work with other databases???).
>
> I've also tried the custom route: Criteria.add(columnName,
> "UPPER("+columnName+") = " + someValue.toUpperCase()). But this is
> inherently dangerous when the value (someValue) is coming from an end
> user. (May allow users to execute arbitrary SQL.)
>
> What I want is something which results in the left half being converted
> to upper case and the right half still using a bind variable:
> UPPER(columnName) = ?
>
> Anyone know if this is possible? I'm using the latest version (3.2).
>
> Thanks.
>
> -rob
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org