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]
