How to Splice Delphi FireDAC Query Code at Design Time

Question

I am getting this syntax error message:

[FireDAC][Phys][SQLite] ERROR: near "ON": syntax ERROR

On my code here:

  qryItems.Active := False;
  qryItems.ResourceOptions.ParamCreate := False;
  qryItems.SQL.Text := 'SELECT category.name, item.name, item.description' +
                       'FROM item ' +
                       'JOIN category ON item.category_id = category.list_id' +
                       'WHERE item.name = :searches OR :searches IS NULL' +
                       'ORDER BY category.name LIMIT 5';
  qryItems.ParamByName('searches').AsString := Format('%%%s%%',[edtSearch.Text]);
  qryItems.Active := True;
  qryItems.SQL.Clear;
  qryItems.ExecSQL;

I tried running the code in Query Editor of TFDQuery with no issues. I am compelled to slice the query to avoid long text violations with this syntax '+' — I hope this is still the practice.

It looks like nothing is wrong with my syntax. Otherwise, I miss something here.

Just check(debug) qryItems.SQL.Text at runtime and you will see the missing spaces. — Val Marinov
@Juke: There ARE errors with your syntax. I see three of them. You're missing a space between item_description and FROM in the first two lines, between category.list_id and WHERE on the next line, and between NULL and ORDER on the next two lines. Uwe's answer below identifies those three errors perfectly, too. Try again. — Ken White

Uwe Raabe Uwe Raabe · Accepted Answer · 2020-05-05T13:06:03

The SQL lines end up like this (note the JOIN, WHERE and ORDER, highlighted for emphasis):

SELECT category.name, item.name, item.descriptionJOIN category ON item.category_id = category.list_idWHERE item.name = :searches OR :searches IS NULLORDER BY category.name LIMIT 5

You can set SQL with multiple lines like this:

  qryItems.SQL.Clear;
  qryItems.SQL.AddStrings(TArray<string>.Create(
      'SELECT category.name, item.name, item.description',
      'JOIN category ON item.category_id = category.list_id',
      'WHERE item.name = :searches OR :searches IS NULL',
      'ORDER BY category.name LIMIT 5'));

Perhaps declaring a constant my increase readability:

const
  cSQL: TArray<string> = [
    'SELECT category.name, item.name, item.description',
    'JOIN category ON item.category_id = category.list_id',
    'WHERE item.name = :searches OR :searches IS NULL',
    'ORDER BY category.name LIMIT 5'];
...
  qryItems.SQL.Clear;
  qryItems.SQL.AddStrings(cSQL);

How to Splice Delphi FireDAC Query Code at Design Time

1 Answers