CODEWITHSUNDEEP
delphifiredac
Juke
Juke

Reputation: 135

How to Splice Delphi FireDAC Query Code at Design Time

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.

Upvotes: 0

Views: 303

Answers (1)

Uwe Raabe
Uwe Raabe

Reputation: 47714

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);

Upvotes: 4

Related Questions