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 '\\' ".
>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]
