3

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

Alister
  • 6,527
  • 4
  • 46
  • 70

1 Answers1

7

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;
Victoria
  • 7,822
  • 2
  • 21
  • 44
  • 1
    Nice, escape sequences are exactly what I'm looking for. – Alister Dec 18 '17 at 20:41
  • Just be careful at [possible conflict with reserved chars](https://stackoverflow.com/q/43465988/8041231) when macro preprocessing is enabled. Make sure to properly escape all _special_ chars in your SQL commands (if your commands use any). – Victoria Dec 18 '17 at 20:48