What is the wildcards format for TEdit search function in FireDAC Delphi

Question

This is my code for searching data using the TEdit component that triggers TFDQuery with parameter:

qryItems.ParamByName('searches').AsString := Format('%%%s%%',[edtSearch.Text]);

If I remove the wildcards (Format('%%%s%%')) format, it works. The wildcards will help me filter the query.

I like the code, its clean, simple, and straight forward. But, I am still not sure if it is correct — it is not returning anything!

My question is: Does the code above works for query filtering from TEdit.OnChangeTracking event? Otherwise, what is the correct way of doing this?

UPDATE 1: Heres the code from TFDQuery Editor:

SELECT category.name AS category, item.name, item.description
FROM item
JOIN category ON item.category_id = category.list_id
WHERE item.description LIKE :searches
ORDER BY item.sellable
LIMIT 100

Now, I am trying to access this from this code during runtime but it is not working:

qryItems.ParamByName('searches').AsString := Format('%%%s%%',[edtSearch.Text]);

I think the culprit here is this code Format('%%%s%%',[edtSearch.Text]), I am not getting this right.

Answer 1

A short answer is that you want to end up with a parameter assignment like this:

  FDQuery1.Params[0].AsString := '%a%';
  FDQuery1.Open();

assuming the value you want to match in your LIKE expression is simply the letter a. Or, if you want to use Format, you could do something like this:

  FDQuery1.Params[0].AsString := Format('%%%s%%', [edFilter.Text]);

The reason for the three hash-signs in a row is that the first one 'escapes' the second one in the expression Format evaluates, and the third one, immediately before the 's' combines with it to act as the placeholder for a string as Format constructs its result.

However, given that you are not completely familiar with working with datasets and filtering, I think you are making this unnecessarily difficult for yourself in at least two respects:

• FMX + LiveBindings is not entirely bug free and has some quirks which may well get in your way.

• The syntax for using the LIKE operator, which uses hash-signs (#), clashes with the use of hash signs for resolving parameters in the Format function. This, in particular, can be extremely confusing, especially when you are trying to obtain a syntactically valid LIKE expression, whether it is for inclusion in the Sql your query uses or in a 'local filter, i.e. one which uses the Filter + Filtered properties of the FDQuery.

So, I am going to make a suggestion which might possibly be unwelcome initially, which is to do your exploration of things like filtering in a VCL application such as the one below. It will only take a few minutes to set up, but will probably save you some time and wear and tear on the nervous system compared with trying to get it right in an FMX + LiveBinding application which is under development. Here is how:

1. Create a new VCL application and add these components to it.

    FDConnection1: TFDConnection;
    FDGUIxWaitCursor1: TFDGUIxWaitCursor;
    FDQuery1: TFDQuery;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    DataSource1: TDataSource;
    edFilter: TEdit;
    btnLocalFilter: TButton;
    btnSqlFilter: TButton;

2. Add the code below to the form's file.

3. Put a debugger breakpoint on the line

case FilterMode of

and exlore the app's behaviour changing the contents of the edFilter control and clicking the two buttons, once you've adapted the code to the data you have available. Mine uses an Author's table, I can't remember where I got it from but maybe it was from the Pubs sample database for Sql-Server.

The app shows - as I'm sure you've gathered - that you can filter the data displayed by your app either server-side by changing the Sql used to retrieve the data or client-side by using the Filter property of the FDQuery. So that you can easily see what's going on, the Sql for server-side filtering is constructed by concatenating the contents of edFilter.Text with the rest of the Sql, but in real life, you should never do that because of its exposure to the Sql Injection exploit.

Code

type
  TFilterMode = (fmLocal, fmSql);

type
  TForm1 = class(TForm)
  [...]
  public
    { Public declarations }
    FilterMode : TFilterMode;
  end;
[...]
const
      sOrderBy = ' order by lastname, forename';
      sSql = 'select * from authors';
      sFilteredSql = sSql + ' where lastname like  :lastname%';
      sLocalFilter = 'lastname like ''%%s%%''';

procedure TForm1.OpenFDQuery;
var
  S : String;
begin
  if FDQuery1.Active then FDQuery1.Close;
  FDQuery1.Params.Clear;
  FDQuery1.Filter := '';
  FDQuery1.Filtered := True;
  case FilterMode of
    fmSql : begin
      FDQuery1.Sql.Text := '';
      //  WARNING - don't do this for real - risk of Sql Injection exploit
      //  use a parameterised query instead - see http://docwiki.embarcadero.com/RADStudio/Rio/en/Using_Parameters_in_Queries
      S := 'select * from authors where lastname like ''%' + edFilter.Text + '%''';
      FDQuery1.Sql.Text := S;
    end;
    fmLocal : begin
      FDQuery1.Sql.Text := sSql + sOrderBy;
      S := 'lastname like ''%' + edFilter.Text + '%''';
      FDQuery1.Filter := S;
      FDQuery1.Filtered := True;
    end;
  end;

  FDQuery1.Open;
end;

procedure TForm1.ApplySqlFilter;
begin
  FilterMode := fmLocal;
  OpenFDQuery;
end;

procedure TForm1.ApplyLocalFilter;
begin
  FilterMode := fmLocal;
  OpenFDQuery;
end;

procedure TForm1.btnLocalFilterClick(Sender: TObject);
begin
  ApplyLocalFilter;
end;

procedure TForm1.btnSqlFilterClick(Sender: TObject);
begin
  ApplySqlFilter;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  edFilter.Text := 'a';
end;