search via TSQLQuery
lazarus / 16-Databases

----------------------------------------------------------------------
CraigC · 2026-05-16 13:35

doesn't work.   Lazarus, SQLite, Linux  don't get even a flash of the
open or close.

Code: [Select]procedure TfrmSelectCust.leSearchKeyPress(Sender:
TObject; var Key: char);
var
  s: string;
  so : TLocateOptions;
begin
  // leSearch.text returns nil when checked.  Use Key!

  if Key in ['A'..'Z', 'a'..'z'] then
  begin
    with dmARData.tblCustomers_ do
    begin
      s := '%' + Key + '%';
      Close;
      SQL.Clear;
      SQL.Add('SELECT * FROM Customers WHERE Name LIKE :CNP');
      // Use ParamByName to bind the parameter safely and prevent SQL
injection
      // Customer Name Pattern  CNP
      ParamByName('CNP').AsString := s;
      Open;
    end;
  end;
end;

----------------------------------------------------------------------
CraigC · 2026-05-16 13:38

Of course when I try this in Database Workbench, it works like a
charm, I just can't see exactly how they code it.

----------------------------------------------------------------------
valdir.marcos · 2026-05-17 01:46

Quote from: CraigC on May 16, 2026, 03:38:37 pmOf course when I try
this in Database Workbench, it works like a charm, I just can't see
exactly how they code it.
Well done. That part is important.


Quote from: CraigC on May 16, 2026, 03:35:02 pm
doesn't work.   Lazarus, SQLite, Linux  don't get even a flash of the
open or close.

Code: [Select]procedure TfrmSelectCust.leSearchKeyPress(Sender:
TObject; var Key: char);
var
  s: string;
  so : TLocateOptions;
begin
  // leSearch.text returns nil when checked.  Use Key!

  if Key in ['A'..'Z', 'a'..'z'] then
  begin
    with dmARData.tblCustomers_ do
    begin
      s := '%' + Key + '%';
      Close;
      SQL.Clear;
      SQL.Add('SELECT * FROM Customers WHERE Name LIKE :CNP');
      // Use ParamByName to bind the parameter safely and prevent SQL
injection
      // Customer Name Pattern  CNP
      ParamByName('CNP').AsString := s;
      Open;
    end;
  end;
end;
As you said, that should work, but it isn't.

Please, attach a small sample to be tested and add:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TfrmSelectCust.leSearchKeyPress(Sender: TObject; var Key: char);var 
s: string;  so : TLocateOptions;begin  // leSearch.text returns nil
when checked.  Use Key!   if Key in ['A'..'Z', 'a'..'z'] then  begin 
 with dmARData.tblCustomers_ do    begin      s := '%' + Key + '%';  
  Close;      SQL.Clear;      SQL.Add('SELECT * FROM Customers WHERE
Name LIKE :CNP');      // Use ParamByName to bind the parameter safely
and prevent SQL injection      // Customer Name Pattern  CNP     
ParamByName('CNP').AsString := s;      Open;      FetchAll;     
ShowMessage('S: ' + s + LineEnding +                   'Records: ' +
IntToStr(RecordCount));    end;  end;end;

----------------------------------------------------------------------
dsiders · 2026-05-17 04:36

Quote from: CraigC on May 16, 2026, 03:35:02 pm
doesn't work.   Lazarus, SQLite, Linux  don't get even a flash of the
open or close.

Code: [Select]procedure TfrmSelectCust.leSearchKeyPress(Sender:
TObject; var Key: char);
var
  s: string;
  so : TLocateOptions;
begin
  // leSearch.text returns nil when checked.  Use Key!

  if Key in ['A'..'Z', 'a'..'z'] then
  begin
    with dmARData.tblCustomers_ do
    begin
      s := '%' + Key + '%';
      Close;
      SQL.Clear;
      SQL.Add('SELECT * FROM Customers WHERE Name LIKE :CNP');
      // Use ParamByName to bind the parameter safely and prevent SQL
injection
      // Customer Name Pattern  CNP
      ParamByName('CNP').AsString := s;
      Open;
    end;
  end;
end;                               

If you were trying to encode the SQL statement (even without use of a
parameter), it would need to be:

Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQL.Add('SELECT * FROM Customers WHERE Name LIKE ''%Abc%''');
I assume use with  parameter would need the same safe-quoting:

Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQL.Add('SELECT * FROM Customers WHERE Name LIKE '':CNP''');
Not tested...

----------------------------------------------------------------------
paweld · 2026-05-17 05:28

The query is fine, but if you want to perform a search every time the
control's content changes, you should place it in the OnChange event -
OnKeyPress is triggered before the TEdit text changes.

----------------------------------------------------------------------
Zvoni · 2026-05-18 06:55

Quote from: paweld on May 17, 2026, 07:28:41 amThe query is fine, but
if you want to perform a search every time the control's content
changes, you should place it in the OnChange event - OnKeyPress is
triggered before the TEdit text changes.

Correct.
and he's comparing a single KEY in his LIKE-Operation, not the entered
TEXT of his TEdit

Example:
He wants to search for "pawel"
He hits the "p"-key, and his resulting LIKE is "... LIKE '%p%'"
He hits the "a"-key, and his resulting LIKE is "... LIKE '%a%'" !!!!
NOT "%pa%"

Yes, in case he still wants to use KeyPress-Event, he could work
around this with a Static Const-Var inside the Event, and then
concatenate with each KeyPress,
but then it starts to get funny, when User presses backspace or
positions the cursor within a word and hits delete or backspace

Bottom line: Wrong Event for the intended functionality. Plain and
simple

EDIT: Just looked into pawel's sample-program
MasterQuery:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT *
FROM mastertab WHERE :parname='''' OR name LIKE :parname ORDER BY
name Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.esearchChange(Sender: TObject);begin  if esearch.Text = ''
then    masterquery.ParamByName('parname').AsString := ''    else   
masterquery.ParamByName('parname').AsString := '%' + esearch.Text +
'%';  masterquery.Refresh;end;
Pawel, why are you OR-ing for identical on empty input??
If esearch.Text is empty ('') his LIKE results in "... LIKE '%%'"
which returns all records.
You are implying that the SQLite-Engine is short-circuiting the OR,
and the opinions differ, if SQLite actually short-circuits a direct
"OR"
(at least what i could find)

If you want to optimize the "... LIKE '%%'" away, i'd rather go for
something like this (untested)
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i //Somewhere
aboveConst  MyWHEREClause=' WHERE name like :parname
';//..............masterquery.SQL.Text:='select * from mastertab
{WHERECLAUSE} order by name;';  //Later....procedure
TForm1.esearchChange(Sender: TObject);begin  if esearch.Text = ''
then    masterquery.SQL.Text:=StringReplace(masterquery.SQL.Text,
'{WHERECLAUSE}', '', [rfReplaceAll])  //Delete the "placeholder" 
else    Begin     
 masterquery.SQL.Text:=StringReplace(masterquery.SQL.Text,
'{WHERECLAUSE}', MyWHEREClause, [rfReplaceAll]);     
 masterquery.ParamByName('parname').AsString := '%' + esearch.Text +
'%';    End;  masterquery.Refresh;end;