Filter Dataset using SQL Query

Question

I am using Zeos and mysql in my delphi project. what I would like to do is filter dataset using a textbox.

to do that, I am using following query in textbox 'OnChange' Event:

ZGrips.Active := false;
ZGrips.SQL.Clear;
ZGrips.SQL.Add('SELECT Part_Name, Description, OrderGerman, OrderEnglish FROM Part');
ZGrips.SQL.Add('WHERE Part_Name LIKE ' + '"%' + trim(txt_search.Text) + '%"');
ZGrips.Active := true;

after I run and type first character in textbox, I get empty dataset in my DBGrid, so DBGrid is showing nothing, then If I type second character I get some result in DBGrid. and even more strange behavior: if I will use AS Clause in my SQL Query like:

Part_Name AS blablabla,
Description AS blablabla,
OrderGerman AS OG,
OrderEnglish AS OE

in that case DBGrid is showing only 2 columns: Part_Name and Description, I dont understand why it is ignoring 3rd and 4th columns.

thanks for any help in advance.

enter image description here

It's not a good idea to keep re-executing a SQL query as a user types into a text box. Instead, do something like a) place a TTimer on your form with an interval of 2-300 b) in your text edit, just enable the timer c) in its OnTimer event, disable the TTimer and execute your SQL query. This may not solve your problem but should make it easier to work out what's going wrong. Of course, in an ideal world, you would execute your SQL query in a background thread so as not to stall the UI while it executes. — MartynA, Jun 25 '14 at 07:48
Oh, and someone will probably suggest that you re-write your SQL query as a parameterised one, to reduce the scope for SQL injection - http://en.wikipedia.org/wiki/Sql_injection. — MartynA, Jun 25 '14 at 07:52
Agree with MartynA about putting it on a timer, this allows the user to type in several characters quickly without executing the query on each character change. — J__, Jun 25 '14 at 07:57
ok, I will put timer to form and look if it changes something. I wonder, I am using same method to another form and works great.. — , Jun 25 '14 at 08:20
one more note: this grid is from TMS Grid pack - TDBAdvGrid. when I am using DBGrid, it shows all 4 columns, but DBAdvGrid only 2 columns.. — , Jun 25 '14 at 08:23
you have different columns in your search query than in your original query? Angebottext_Deutsch vs Ordergerman?? — whosrdaddy, Jun 25 '14 at 13:51
@J__, you're recommending a SQL-injection. That's very poor advice. — Johan, Jun 25 '14 at 21:19

score 1 · Accepted Answer · edited May 23 '17 at 12:19

Always use parameters
Firstly you need to use parameters, otherwise your query will break or worse when the user enters the wrong characters in the search box.
See: How does the SQL injection from the "Bobby Tables" XKCD comic work?
Parameters also makes you query faster, because the database engine only have to decode the query once.
If you change a parameter the engine will know that the query itself has not changed and will not re-decode it.

Don't use clear and add
Just supply the SQL as text in one go, it's faster.
This is esp. true in a loop, outside the loop you will not notice the difference.

Your code should read something like:

procedure TForm1.SetupSearch; //run this only once.
var
  SQL: string;
begin
  ZGrips.Active:= false;
  SQL:= 'SELECT Part_Name, Description, OrderGerman, OrderEnglish FROM Part' +
        'WHERE Part_Name LIKE :searchtext');  //note no % here.
  ZGrips.SQL.Text:= SQL;  //don't use clear and don't use SQL.Add.
end; 

//See: http://docwiki.embarcadero.com/Libraries/XE2/en/Vcl.StdCtrls.TEdit.OnChange  
procedure TForm1.Edit1Change(Sender: TObject);
begin
  if Edit1.Modified then begin
    Timer1.Active:= true;
  end;
end;

procedure TForm1.Timer1Timer(Sender: TObject);
begin
  Timer1.Active:= false;
  if Edit1.Text <> ZGrips.Params[0].AsString then begin
    ZGrips.Params[0].AsString:= Edit1.Text + '%'
    ZGrips.Active:= true;
  end;
end;

Use a timer
As per @MartinA's suggestion, use a timer and start the query only ever so often.
The wierd behaviour you're getting maybe because you're stopping and reactivating a new query before the old one has had time to finish.

The Params[index: integer] property is a bit faster than the ParamsByName property.
Although this does not really matter outside a loop.

Allow the database to use an index!
Using only a trailing wildcard % is faster than using a leading wildcard because the database can only use an index is there is a trailing wildcard.
If you want to use a leading wildcard, then consider storing the data in reverse order and use a trailing wildcard instead.

Full-text indexes are much better than like
Of course if you use both a leading and a trailing wild card then you have to use a full-text index.
In MySQL you'll than use the MATCH AGAINST syntax,
see: Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?
and: Which SQL query is better, MATCH AGAINST or LIKE?

The lastest versions of MySQL support full-text indexes in InnoDB.
Remember to never use MyISAM, it's unreliable.

Thanks for your help. I followed your advices and now it works fine as expected. you were right - I was stopping and starting new query too quickly. timer solved that problem here. also changed weird syntax near LIKE, now using parameter here like this: ... := '%' + txt_search.Text + '%' two wildcards at start and end will cause any problem on innoDB engine? On 'OnChange' event of TEdit I am not using 'if' clause to see if 'Edit1.Modified', as if 'OnChange' event has occured, that is caused by modifing of Edit1. right? — , Jun 26 '14 at 09:28
The test for the `Modified` is in there for a reason. The `OnChange` can be triggered when the edit has not actually changed. The is Modified doublechecks the change and prevents a new query from firing when no change has actually happened in the EditBox. — Johan, Jun 27 '14 at 14:05
The leading `%` with `like` does not cause a problem as such, but prevents MySQL (or any other database for that matter) from using an index on that row, greatly slowing down the query. — Johan, Jun 27 '14 at 14:08

Filter Dataset using SQL Query

1 Answers1