It shouldn't matter what db is being used, I should be able to find any fields
containing a '%' or a '_' regardless. The SQL specification allows for this by
allowing the ESCAPE parameter.
I modified the Criteria.java (generateSqlSearchPattern() method) and the
SqlQueryStatement.java (appendSelectionCriteria() method) source to implement
this the way I feel is appropriate. Look for PRN, to see any changes I have
made.
In a nutshell, it allows for escaping out the '%' and '_'. When generating the
like clause, adds the " ESCAPE '\\' " parameter, ONLY when a '\' is found in the
pattern value.
The following code seems to work for me...what do you think?
Criteria.java
protected String generateSQLSearchPattern(String pattern)
{
StringBuffer sqlpattern = new StringBuffer();
char[] chars = pattern.toCharArray();
for (int i = 0; i < chars.length; i++)
{
if (chars[i] == '\\')
{
// for the escape character add the next char as is.
// PRN - ADD BEGIN
// find the next non-'\' character.
int x = i+1;
for ( ; (x < chars.length); x++) {
if (chars[x] != '\\') {
break;
}
}
boolean oddEscapes = (((x - i) % 2) > 0) ? true :
false;
if (oddEscapes) {
// only escape characters allowed are '%',
'_', and '\'
// if the escaped character is a '\', then
oddEscapes
// will be false.
// if the character following this last escape
is not a
// '%' or an '_', eat this escape character.
if ((x < chars.length) &&
((chars[x] == '%') || (chars[x] == '_'))) {
// leave the escape character in,
along with the following char
x++;
} else {
// remove the escape character, will
cause problems in sql statement.
i++; // removing the first escape
character.
if ((x < chars.length) &&
((chars[x] == '*') || (chars[x] ==
'?'))) {
// but if it is a '*' or a
'?', we want to keep these
// characters as is, they were
'escaped' out.
x++; // include the first
non-escape character.
}
}
}
if (i < chars.length) sqlpattern.append(chars,i,x - i);
i = x - 1; // set index to last character copied.
// PRN - ADD END
}
else if (chars[i] == '*')
{
sqlpattern.append("%");
}
else if (chars[i] == '?')
{
sqlpattern.append("_");
}
else
{
sqlpattern.append(chars[i]);
}
}
return sqlpattern.toString();
}
SqlQueryStatement.java
private void appendSelectionCriteria(TableAlias alias, PathInfo pathInfo,
SelectionCriteria c, StringBuffer buf)
{
appendColName(alias, pathInfo, c.isTranslateAttribute(), buf);
buf.append(c.getClause());
appendParameter(c.getValue(), buf);
//PRN - ADD BEGIN
// there is NO functionality to search for something in a like
statement that
// contains the system wildcards ('%' or '_').
Criteria.generateSQLSearchPattern()
// will normalize the string to include the '\' as an escape character
for these
// special cases.
if ((c.getClause().toUpperCase().indexOf("LIKE") >= 0) &&
(((String) c.getValue()).indexOf('\\') >= 0)) {
buf.append(" ESCAPE '\\' ");
}
//PRN - ADD END
}
Paul
-----Original Message-----
From: Gelhar, Wallace Joseph [mailto:[EMAIL PROTECTED]
Sent: Friday, February 20, 2004 11:03 AM
To: OJB Users List
Subject: RE: Query with criteria - like using '%' or '_'
I assumed (AKA ass-u-me) that OJB does a database independent wildcard replacement.
For example, MSSQL uses % as the wildcard character, but ojb will replace a * with the
database specific wildcard character.
This would require some syntax for escaping characters that I'm not sure is present
(ie how do you query for LIKE '*%' where I want to query for all instances where LIKE
'%[%]' on MSSQL) As you can see, this would require database specific character
escaping.
If you just pass the unaltered string to the db, you some of the database independence
that OJB provides. Although, the cost of implementing this behavior might outweigh
the benefits.
Thoughts?
Wally
-----Original Message-----
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
Sent: Friday, February 20, 2004 10:41 AM
To: OJB Users List
Subject: Re: Query with criteria - like using '%' or '_'
hi paul,
i'm sorry i forget that ojb manipulates the criteria in addLike. imo it
would be the best to pass the criteria withou any modifications to the
dbms. what do you think about this ?
jakob
Nase, Paul R. wrote:
> Adding a like parameter as the link suggests
> (addLike("attribute","%[%]%")) will return any records where the field
> matches <anytexthere>[<anytexthere>]<anytexthere>
> (i.e. ABC[DEF]XYZ matches, ABC[XYZ does not).
>
> The only way I have been able to get an sql query to work manually is
> to use a statement similar to:
> select * from sometable where description like '%\%%' ESCAPE '\'
> NOTE: finds all records where the description contains the '%'
> character somewhere.
>
> The first problem with what OJB is doing when I add a like criteria,
> is strip off the escape character. They do this because they are only
> allowing a '*', '\' or a '?' to be 'escaped', so they assume if you
> are using the '\' character at all, it is simply removed.
> Technically, they aren't really escaping anything, they are simply
> allowing the *, ? and every other \ to go through as is and not
> converting them to their respective % and _ characters. What OJB
> doesn't do when escaping their '*' and '?' - is check to make sure the
> next character is actually a '*' or '?'. OJB also doesn't add the
> ESCAPE parameter that works with the like clause. They could check
> the value for the like operation for any '\' character, and simply
> append " ESCAPE '\' " if they find one.
>
> So, unless I am missing something, I think the ojb support for 'like'
> is close, but not quite complete. There are basically two characters
> you simply cannot search for in a string, '%' and '_'.
>
> Paul
>
> -----Original Message-----
> From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 19, 2004 12:56 PM
> To: OJB Users List
> Subject: Re: Query with criteria - like using '%' or '_'
>
> hi paul,
>
> check out this link:
>
> http://nick.blogs.com/blog/2003/08/_in_sql_stateme.html
>
> jakob
>
> Nase, Paul R. wrote:
>
>
>>Thanks Jakob,
>>
>>But I want to find objects that contain the actual '%' character, I
>>don't want to search for objects using the '%' as the one or more
>>wildcard.
>>
>>The addLike("attribute","P%") will return all records starting with
>>the letter 'P'. So this query will search using the '%' as the normal
>>sql wildcard, it won't query for items that actually contain the '%'
>>character.
>>
>>Paul
>>
>>-----Original Message-----
>>From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
>>Sent: Thursday, February 19, 2004 12:29 PM
>>To: OJB Users List
>>Subject: Re: Query with criteria - like using '%' or '_'
>>
>>hi paul,
>>
>>use addLike("your_attribute","P%")
>>
>>hth
>>jakob
>>
>>Nase, Paul R. wrote:
>>
>>
>>
>>>I would like to perform a query where a description could have a '%'
>>>or an '_'. Is this possible using QueryByCriteria?
>>>
>>>Example:
>>>ID DESCRIPTION
>>>------- --------------------
>>>10000 40% Member Discount
>>>10001 Online Discount
>>>
>>>I'd like to be able to search using a like statement similar to:
>>>"Where Description like '%\%%' ESCAPE '\'"
>>>
>>>The current implementation will just strip off the escape character,
>>>resulting in passing my literal '\%' being pass as a wildcard.
>>>
>>>Thanks in advance.
>>>
>>>Paul Nase
>>>
>>>---------------------------------------------------------------------
>>>To unsubscribe, e-mail: [EMAIL PROTECTED]
>>>For additional commands, e-mail: [EMAIL PROTECTED]
>>>
>>>
>>
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: [EMAIL PROTECTED]
>>For additional commands, e-mail: [EMAIL PROTECTED]
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: [EMAIL PROTECTED]
>>For additional commands, e-mail: [EMAIL PROTECTED]
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
