seems like mysql does not like '\'. it works with '|' :
SELECT A0.LASTNAME,A0.FIRSTNAME,A0.ID FROM PERSON A0 WHERE A0.FIRSTNAME LIKE 'h%|%' ESCAPE '|'
jakob
Jakob Braeuchi wrote:
hi paul,
i implemented the escape handling with LikeCriteria which results in the following sql:
SELECT A0.LASTNAME,A0.FIRSTNAME,A0.ID FROM PERSON A0 WHERE A0.FIRSTNAME LIKE 'B\%' ESCAPE '\'
unfortunately this is not accepted by mySql. it works for hsqldb and sapdb (maxdb).
jakob
Nase, Paul R. wrote:
Jakob,
Perhaps I am misunderstanding your question. When you say "why can't we add the
ESCAPE to the search argument?", do you mean add it like the following?
criteria.addLike("*\\%* ESCAPE '\\'") // to search for any text containing
// the '%' character.
If this is not what you mean, then I will need some clarification.
If this 'is' the case, it will not work because the desired sql statement is:
select * from mytable where desc like ? ESCAPE '\'
? Is a replacement var that will be replace with "%\\%%" after the
statement is prepared.
What adding to the search argument using the addLike as shown above, it would
actually produce:
select * from mytable where desc like ?
? Is replaced with "%\\%% ESCAPE '\\' ". Which isn't what we want to search
for.
Does this help?
Paul
-----Original Message-----
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED] Sent: Friday, February 20, 2004 2:49 PM
To: OJB Users List
Subject: Re: Query with criteria - like using '%' or '_'
hi paul,
Nase, Paul R. wrote:
Jakob,
i just don't see why we can't construct the whole string in criteria.
in appendSelectionCriteria you simply append " ESCAPE '\\' ". why can't this be done in criteria#generateSQLSearchPattern ?
We cannot just append this to the search pattern, because when the sql is actually generated, it is generated with replacement variables (i.e.
" MYCOLUMN LIKE ? ", so if we do it as part of the generateSQLSearchPattern() it will then be included as part of the text to search for. We would need the sql statement to look like " MYCOLUMN LIKE ? ESCAPE '\\' ".
why can't we add the ESCAPE to the search argument ?
jakob
in this case i would transfer the pattern handling to the new LikeCriteria.
Very good idea!
Paul
-----Original Message----- From: Jakob Braeuchi [mailto:[EMAIL PROTECTED] Sent: Friday, February 20, 2004 1:48 PM To: OJB Users List Subject: Re: Query with criteria - like using '%' or '_'
hi paul,
Nase, Paul R. wrote:
I don't think there are any platform issues, we aren't talking about path names to look for files or anything like this. We are simply Talking about an SQL92/SQL98 feature that is not being utilized.
This character could have been anything, I just so happened to pick the '\' character because this is the most common character used for such a thing.
well, we can try it.
Anyways, I don't think you'll be able to fix this in one place.
The string is parsed in one place, and the statement is generated in another. And I don't think there is a way to generate any text - escaping or otherwise - that gives the desired results.
i just don't see why we can't construct the whole string in criteria.
in appendSelectionCriteria you simply append " ESCAPE '\\' ". why can't this be done in criteria#generateSQLSearchPattern ?
I admit there is probably a better way to implement this, but the bottom line is - the like feature on the Criteria has a bug. It will not let you build a query to search for a '%' or a '_'.
Another way you could implement this is to add another class called LikeCriteria(All the other Criteria have one of these - why not like?). Then add the method SqlQueryStatement.appendLikeCriteria().
this is no problem. until now there was no need to treat like separately.
Call the new method in SqlQueryStatement.appendCriteria() when this new LikeCriteria is encountered. But of course, you'd still have to parse the original string appropriately somewhere. So in this case you have 3 files to change - Criteria, a new LikeCriteria, and SqlQueryStatement - instead of 2.
in this case i would transfer the pattern handling to the new LikeCriteria.
jakob
Paul
-----Original Message----- From: Jakob Braeuchi [mailto:[EMAIL PROTECTED] Sent: Friday, February 20, 2004 11:45 AM To: OJB Users List Subject: Re: Query with criteria - like using '%' or '_'
hi paul,
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]
--------------------------------------------------------------------- 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]
