Proposal to support ESCAPE for Like-Criteria

[Jakob Braeuchi <jb...@gmx.ch>]

hi all,

i had a long discussion with paul r. nase about like criteria containing escaped 
wildcards (example: first name like 'h%\%' escape '\' looks for firstname 
starting with 'h' and ending with '%'). for details see:

  http://article.gmane.org/gmane.comp.jakarta.ojb.user/12505

ojb currently does not properly support escaping of wildcards. while 
experimenting with this feature i found that we have some dbms-dependent problems:

1.) escape character \ is the default in mysql so escape '\' is not accepted. 
we'd need '\\'
2.) replacing wildcards *, ? by %, _ does not work for ms-access.
3.) escaping wildcards is different in ms-access

so i propose to use a manual escaping in like-criteria without replacing 
wildcards. the user will have to provide the escape clause for the like-criteria:

addLike(attribute, criteria, escapeClause);
ie:
addLike("firstname", "h%\\%", "escape '\\'");

ms_access: addLike("firstname", "h*[*]");

the escape clause will be appended to the like-clause when building the sql.
i'll implement a dedicated LikeCriteria to support escaping.

what do you think about this ?

jakob





---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org

RE: Proposal to support ESCAPE for Like-Criteria

[Larry Streepy <la...@staffmix.com>]

I would argue that specialized handling of the wildcard characters belongs
in the DB-specific classes.  One of the goals (at least in my mind) is that
OJB makes it fairly easy to move between database platforms without massive
changes to your application.  The proposal below breaks that paradigm by
requiring DB-specific handling for something as simple as wildcard handling.

I know I get no real vote, but take this as a plea to move this handling
into the DB-specific layers.

Thanks.
Larry.


> -----Original Message-----
> From: Jakob Braeuchi [mailto:jbraeuchi@gmx.ch]
> Sent: Saturday, February 21, 2004 6:44 AM
> To: OJB Developers List; Nase.Paul@mayo.edu
> Subject: Proposal to support ESCAPE for Like-Criteria
> 
> hi all,
> 
> i had a long discussion with paul r. nase about like criteria containing
> escaped
> wildcards (example: first name like 'h%\%' escape '\' looks for firstname
> starting with 'h' and ending with '%'). for details see:
> 
>   http://article.gmane.org/gmane.comp.jakarta.ojb.user/12505
> 
> ojb currently does not properly support escaping of wildcards. while
> experimenting with this feature i found that we have some dbms-dependent
> problems:
> 
> 1.) escape character \ is the default in mysql so escape '\' is not
> accepted.
> we'd need '\\'
> 2.) replacing wildcards *, ? by %, _ does not work for ms-access.
> 3.) escaping wildcards is different in ms-access
> 
> so i propose to use a manual escaping in like-criteria without replacing
> wildcards. the user will have to provide the escape clause for the like-
> criteria:
> 
> addLike(attribute, criteria, escapeClause);
> ie:
> addLike("firstname", "h%\\%", "escape '\\'");
> 
> ms_access: addLike("firstname", "h*[*]");
> 
> the escape clause will be appended to the like-clause when building the
> sql.
> i'll implement a dedicated LikeCriteria to support escaping.
> 
> what do you think about this ?
> 
> jakob
> 
> 
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org