i'd prefer to have the whole escape handling in one place, in critria. also i do not know if the escape handling is platform independent.
jakob
Nase, Paul R. wrote:
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]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
