How to quote field names in FireDAC in Delphi

Question

I'm working on an application that runs on multiple databases, many of which have their own way of quoting reserved words as field names for example For example

select `key` from mytable

or

select "key" from mytable

or

select [key] from mytable

I was wondering if there was a preprocessor command (or some other mechanism) that could automatically determine the correct quote characters to use for a given database. And yes (before someone comments) I shouldn't use reserved words for field names, but the schema is already predefined (a couple decades ago).

Answer 1

For a constant SQL command string you can use the {id} identifier substitution escape sequence. An example usage:

FDQuery1.SQL.Text := 'SELECT {id Key} FROM {id MyTable}';
FDQuery1.Open;

For assignments from code you can use substitution variable macros with AsIdentifier accessor. An example usage:

FDQuery1.SQL.Text := 'SELECT &KeyCol FROM &TheTable';
FDQuery1.MacroByName('KeyCol').AsIdentifier := 'Key';
FDQuery1.MacroByName('TheTable').AsIdentifier := 'MyTable';
FDQuery1.Open;