search via TSQLQuery

[CraigC]

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]

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]

Of 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.

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][+][-]

1. procedure TfrmSelectCust.leSearchKeyPress(Sender: TObject; var Key: char);
2. var
3.   s: string;
4.   so : TLocateOptions;
5. begin
6.   // leSearch.text returns nil when checked.  Use Key!
7.  
8.   if Key in ['A'..'Z', 'a'..'z'] then
9.   begin
10.     with dmARData.tblCustomers_ do
11.     begin
12.       s := '%' + Key + '%';
13.       Close;
14.       SQL.Clear;
15.       SQL.Add('SELECT * FROM Customers WHERE Name LIKE :CNP');
16.       // Use ParamByName to bind the parameter safely and prevent SQL injection
17.       // Customer Name Pattern  CNP
18.       ParamByName('CNP').AsString := s;
19.       Open;
20.       FetchAll;
21.       ShowMessage('S: ' + s + LineEnding +
22.                   'Records: ' + IntToStr(RecordCount));
23.     end;
24.   end;
25. end;

[dsiders]

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][+][-]

1. SQL.Add('SELECT * FROM Customers WHERE Name LIKE ''%Abc%''');

I assume use with  parameter would need the same safe-quoting:

Code: MySQL  [Select][+][-]

1. SQL.Add('SELECT * FROM Customers WHERE Name LIKE '':CNP''');

Not tested...

[paweld]

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]

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.

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][+][-]

1. SELECT * FROM mastertab WHERE :parname='''' OR name LIKE :parname ORDER BY name
2.  

Code: Pascal  [Select][+][-]

1. procedure TForm1.esearchChange(Sender: TObject);
2. begin
3.   if esearch.Text = '' then
4.     masterquery.ParamByName('parname').AsString := ''  
5.   else
6.     masterquery.ParamByName('parname').AsString := '%' + esearch.Text + '%';
7.   masterquery.Refresh;
8. 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][+][-]

1. //Somewhere above
2. Const
3.   MyWHEREClause=' WHERE name like :parname ';
4. //..............
5. masterquery.SQL.Text:='select * from mastertab {WHERECLAUSE} order by name;';
6.  
7.  
8. //Later....
9. procedure TForm1.esearchChange(Sender: TObject);
10. begin
11.   if esearch.Text = '' then
12.     masterquery.SQL.Text:=StringReplace(masterquery.SQL.Text, '{WHERECLAUSE}', '', [rfReplaceAll])  //Delete the "placeholder"
13.   else
14.     Begin
15.        masterquery.SQL.Text:=StringReplace(masterquery.SQL.Text, '{WHERECLAUSE}', MyWHEREClause, [rfReplaceAll]);
16.        masterquery.ParamByName('parname').AsString := '%' + esearch.Text + '%';
17.     End;
18.   masterquery.Refresh;
19. end;