SQL variable
lazarus / 16-Databases

----------------------------------------------------------------------
GKRAFT63 · 2015-09-10 14:59

I have a simple address book SQLite database table, I'd like to add a
search feature to.  I'd like to have a edit box to search for a last
name string and to refresh the search as the user types.  I have
googled all over the internet, and I don't know if it's even feasible.
This is what I have so far. Getting a syntax error. Among others when
trying different approaches. Thanks for your help.

procedure TForm1.Edit1Change(Sender: TObject);
const
s1:string = 'Select * FROM ADDRESSBK WHERE LASTNAME LIKE ''%''';
s2:string = '%';
begin
 SQLQuery1.Active:=false;
 SQLQuery1.SQL.clear;
 SQLQuery1.SQL.text:=s1+Edit1.text+s2;
 SQLQuery1.Active:=true;
end;

----------------------------------------------------------------------
paweld · 2015-09-10 16:06

Code: [Select]procedure TForm1.Edit1Change(Sender: TObject);
begin
 SQLQuery1.Filtered:=false;
 SQLQuery1.Filter:='LASTNAME=''*'+Edit1.text+'*'''; //edit: added ''
 SQLQuery1.Filtered:=true;
end;

----------------------------------------------------------------------
derek.john.evans · 2015-09-10 16:11

Why is there a '' at the end of s1? Shouldn't that be at the end of
s2?

----------------------------------------------------------------------
Leledumbo · 2015-09-10 17:27

Displaying the content of SQLQuery1.SQL.text should easily reveal that
you have:
Code: [Select]Select * FROM ADDRESSBK WHERE LASTNAME LIKE '%'%if
Edit1.Text contains xxx then it will be:
Code: [Select]Select * FROM ADDRESSBK WHERE LASTNAME LIKE
'%'xxx%Didn't you see the obvious syntax error?

----------------------------------------------------------------------
GKRAFT63 · 2015-09-10 19:31

Quote from: Leledumbo on September 10, 2015, 07:27:14 pmDisplaying the
content of SQLQuery1.SQL.text should easily reveal that you have:
Code: [Select]Select * FROM ADDRESSBK WHERE LASTNAME LIKE '%'%if
Edit1.Text contains xxx then it will be:
Code: [Select]Select * FROM ADDRESSBK WHERE LASTNAME LIKE
'%'xxx%Didn't you see the obvious syntax error?

Yes I know I was trying all kinds of combinations. I should have just
made a label and tested it. Thanks.

----------------------------------------------------------------------
GKRAFT63 · 2015-09-10 19:54

Thanks all I got it working nicely!
Here is the code:

procedure TForm1.Edit1Change(Sender: TObject);
const
s1:string = 'Select * FROM ADDRESSBK WHERE LASTNAME LIKE ''%';
s2:string = '%''';
begin
 SQLQuery1.Active:=false;
 SQLQuery1.SQL.clear;
 SQLQuery1.SQL.text:=s1+Edit1.text+s2;
 SQLQuery1.Active:=true;
 end;

----------------------------------------------------------------------
derek.john.evans · 2015-09-10 20:21

It might also help you to read this:

https://en.wikipedia.org/wiki/SQL_injection

----------------------------------------------------------------------
GKRAFT63 · 2015-09-11 00:54

Will do.
Thanks again.

----------------------------------------------------------------------
magleft · 2015-09-14 06:28

Try this code
Code: [Select]procedure TForm1.Edit1Change(Sender: TObject);
const
s1:string = 'Select * FROM ADDRESSBK WHERE LASTNAME LIKE ';
begin
 SQLQuery1.Active:=false;
 SQLQuery1.SQL.clear;
 SQLQuery1.SQL.text:=s1+quotedstr('%'+Edit1.text+'%');
 SQLQuery1.Active:=true;
end;