Board index » delphi » Pattern Match Query

Pattern Match Query

Hi, Every Delphi guru,

    I'm new to Delphi32 (2.0). Right now I'm using Delphi32 with Access 7.0's
database format. I'm try to use TQuery to form a dynamic query. The query is
simple. What I want do is to search all memo fields in a table to find output
a certain pattern. As Borland describes that the pattern match SQL should like
the following,

     Select * from table_name
     where field_a like '%criteria%'

Since I want to use dynamic sql to pass the parameter, I change the form to

    Select * from table_name
    where field_a like :criteria

at run time I assign the parameter by using ParamByName ('criteria').AsString
to send the parameter but I can't get any returned dataset.

I use query build to test and pass the same parameter as I pass in the
program, it works. So can any of the member in the group can help me out?
Thanks in advance.

Name  : Mike Liang, Tamkang University, Taipei, Taiwan
e-mail  : m...@tknet.tku.edu.tw

 

Re:Pattern Match Query


Quote
m...@tknet.tku.edu.tw (Mike Liang) wrote:
> Hi, Every Delphi guru,

>     I'm new to Delphi32 (2.0). Right now I'm using Delphi32 with Access 7.0's
> database format. I'm try to use TQuery to form a dynamic query. The query is
> simple. What I want do is to search all memo fields in a table to find output
> a certain pattern. As Borland describes that the pattern match SQL should like
> the following,

>      Select * from table_name
>      where field_a like '%criteria%'

> Since I want to use dynamic sql to pass the parameter, I change the form to

>     Select * from table_name
>     where field_a like :criteria

> at run time I assign the parameter by using ParamByName ('criteria').AsString
> to send the parameter but I can't get any returned dataset.

> I use query build to test and pass the same parameter as I pass in the
> program, it works. So can any of the member in the group can help me out?
> Thanks in advance.

Looking at your two examples above I fail to see you using the
wildcard characters in the second version.  The '%' is used as
wildcard.  What you have in your parameterized version is the
same as doing:

   where field_a = :criteria

which I assume is not a desired effect.  If you want to continue
using the parameterized version try something like:

    Tquery.ParamByName('criteria').AsString := '%' + strVal + '%';

Lastly, with most databases case-sensitive issues will arise...
You may consider changing your query to something like:

    select * from table_name
    where upper(field_A) like upper(:criteria);

However, this will cause your indexes to be ignored with most
databases :-(

HTH,

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Chris Kasten
Programmer/Analyst

#include <std/disclaimer.h>
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Other Threads