Jakob, This seems to work for me.
Thanks much!!! Paul -----Original Message----- From: Jakob Braeuchi [mailto:[EMAIL PROTECTED] Sent: Saturday, February 21, 2004 3:50 PM To: OJB Users List Subject: Re: Query with criteria - like using '%' or '_' hi paul, i commited support for escaping based on your solution and additional LikeCriteria. 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]
