You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-dev@db.apache.org by Parthasarathy T <pa...@gmail.com> on 2006/10/12 11:11:40 UTC
Doubt in the SQLExpression formed by buildLike method
Hi all,
I have a small doubt in the SQLExpression formed by the buildLike method. If
i understand the comments correctly if criteria = 50\%, it will be changed
to columnName = 50%
/**
* Takes a columnName and criteria and builds an SQL phrase based
* on whether wildcards are present and the state of the
* ignoreCase flag. Multicharacter wildcards % and * may be used
* as well as single character wildcards, _ and ?. These
* characters can be escaped with \.
*
* e.g. criteria = "fre%" -> columnName LIKE 'fre%'
* -> UPPER(columnName) LIKE UPPER('fre%')
* criteria = "50\%" -> columnName = '50%'
*
* @param columnName A column name.
* @param criteria The value to compare the column against.
* @param comparison Whether to do a LIKE or a NOT LIKE
* @param ignoreCase If true and columns represent Strings, the
* appropriate function defined for the database will be used to
* ignore differences in case.
* @param db Represents the database in use, for vendor specific
functions.
* @param whereClause A StringBuffer to which the sql expression
* will be appended.
*/
static void buildLike(String columnName,
String criteria,
SqlEnum comparison,
boolean ignoreCase,
DB db,
StringBuffer whereClause)
{
Check the loop logic below - first sb is filled with *5 *and then *0 *and
then it encounters \ - the code now enters the *case BACKSLASH: *where we
check the next character while is % so it enters the % case and so the
position gets incremented by 1 automatically - the checkWildcard at this
time will be still \ and not % and so when the checkWildcard gets appended
to sb we will be appending \ and we would have skipped % altogether (because
of position++)
At the end of the loop we will be having sb= 50\ and not 50%. Is this a
bug??
Most of time we may not have faced the problem because we would have come in
criteria = 50\\% (because of *quoteAndEscapeText() *method appending an
extra slash for most dbs).
StringBuffer sb = *new* StringBuffer();
* StringBuffer sb = new StringBuffer();
while (position < criteria.length())
{
char checkWildcard = criteria.charAt(position);*
* switch (checkWildcard)
{
case BACKSLASH:
// Determine whether to skip over next character.
switch (criteria.charAt(position + 1))
{
case '%':
case '_':
case '*':
case '?':
case BACKSLASH:
position++;
break;
}
break;
case '%':
case '_':
escapeCharFound = true;
equalsOrLike = comparison.toString();
break;
case '*':
equalsOrLike = comparison.toString();
checkWildcard = '%';
break;
case '?':
equalsOrLike = comparison.toString();
checkWildcard = '_';
break;
}*
* sb.append(checkWildcard);
position++;
}
whereClause.append(equalsOrLike);*
sb.append(checkWildcard);
position++;
}
Thanks,
*T.Parthasarathy *• SunGard • Offshore Services • Divyasree Chambers
Langford Road • Bangalore 560025 India
Tel +91-80-2222-0501 • Mobile +91-99450-00394 • Fax +91-80-2222-0511 • *
www.sungard.com*
*Please note my email address –
Parthasarathy.Thandavarayan@sos.sungard.com. Please update your
contact list and use this address for all
future communication.*
CONFIDENTIALITY: This email (including any attachments) may contain
confidential, proprietary and privileged information, and unauthorized
disclosure or use is prohibited. If you received this email in error, please
notify the sender and delete this email from your system. Thank you.
Re: Doubt in the SQLExpression formed by buildLike method
Posted by Thomas Fischer <tf...@apache.org>.
Hm, at least the result of the method does not match the javadoc comment.
I would think that the code is faulty.
But even if the code was correct, the logic of removing the escape
character and replacing a LIKE with an = if a % sign is escaped is dubious
at least. What should happen for LIKE "%test\%" ???
My personal feeling is that the LIKE should not be changed in any case (if
the user wants a like comparison, he gets it, otherwise he should not have
used LIKE) and that the \ should be replaced with the db-specific escape
character.
Correctly escaping the wildcards would probably mean that we would have to
add a method in the DB adapter which does the escaping. We should check
whether this is possible for all DB's (i.e. are the escape characters the
same e.g. for string constants in the like clause and ordinary strings ?)
But then again, should this behaviour changed in a bugfix release ?
In my opinion, correctly escaping the wildcard would be a good thing to
do, but we should keep the LIKE -> = replacement till the next major
release.
Any other opinions ?
Thomas
On Thu, 12 Oct 2006, Parthasarathy T wrote:
> Hi all,
>
> I have a small doubt in the SQLExpression formed by the buildLike method. If
> i understand the comments correctly if criteria = 50\%, it will be changed
> to columnName = 50%
>
> /**
> * Takes a columnName and criteria and builds an SQL phrase based
> * on whether wildcards are present and the state of the
> * ignoreCase flag. Multicharacter wildcards % and * may be used
> * as well as single character wildcards, _ and ?. These
> * characters can be escaped with \.
> *
> * e.g. criteria = "fre%" -> columnName LIKE 'fre%'
> * -> UPPER(columnName) LIKE UPPER('fre%')
> * criteria = "50\%" -> columnName = '50%'
> *
> * @param columnName A column name.
> * @param criteria The value to compare the column against.
> * @param comparison Whether to do a LIKE or a NOT LIKE
> * @param ignoreCase If true and columns represent Strings, the
> * appropriate function defined for the database will be used to
> * ignore differences in case.
> * @param db Represents the database in use, for vendor specific
> functions.
> * @param whereClause A StringBuffer to which the sql expression
> * will be appended.
> */
> static void buildLike(String columnName,
> String criteria,
> SqlEnum comparison,
> boolean ignoreCase,
> DB db,
> StringBuffer whereClause)
> {
>
> Check the loop logic below - first sb is filled with *5 *and then *0 *and
> then it encounters \ - the code now enters the *case BACKSLASH: *where we
> check the next character while is % so it enters the % case and so the
> position gets incremented by 1 automatically - the checkWildcard at this
> time will be still \ and not % and so when the checkWildcard gets appended
> to sb we will be appending \ and we would have skipped % altogether (because
> of position++)
> At the end of the loop we will be having sb= 50\ and not 50%. Is this a
> bug??
>
> Most of time we may not have faced the problem because we would have come in
> criteria = 50\\% (because of *quoteAndEscapeText() *method appending an
> extra slash for most dbs).
>
> StringBuffer sb = *new* StringBuffer();
>
> * StringBuffer sb = new StringBuffer();
> while (position < criteria.length())
> {
> char checkWildcard = criteria.charAt(position);*
>
> * switch (checkWildcard)
> {
> case BACKSLASH:
> // Determine whether to skip over next character.
> switch (criteria.charAt(position + 1))
> {
> case '%':
> case '_':
> case '*':
> case '?':
> case BACKSLASH:
> position++;
> break;
> }
> break;
> case '%':
> case '_':
> escapeCharFound = true;
> equalsOrLike = comparison.toString();
> break;
> case '*':
> equalsOrLike = comparison.toString();
> checkWildcard = '%';
> break;
> case '?':
> equalsOrLike = comparison.toString();
> checkWildcard = '_';
> break;
> }*
>
> * sb.append(checkWildcard);
> position++;
> }
> whereClause.append(equalsOrLike);*
>
> sb.append(checkWildcard);
>
> position++;
>
> }
>
> Thanks,
>
> *T.Parthasarathy *� SunGard � Offshore Services � Divyasree Chambers
> Langford Road � Bangalore 560025 India
> Tel +91-80-2222-0501 � Mobile +91-99450-00394 � Fax +91-80-2222-0511 � *
> www.sungard.com*
>
> *Please note my email address �
> Parthasarathy.Thandavarayan@sos.sungard.com. Please update your
> contact list and use this address for all
> future communication.*
>
> CONFIDENTIALITY: This email (including any attachments) may contain
> confidential, proprietary and privileged information, and unauthorized
> disclosure or use is prohibited. If you received this email in error, please
> notify the sender and delete this email from your system. Thank you.
>