In this text in other places a "macro" and "escape sequence" are interchangeable phrases. AnyDAC supports three kinds of macro instructions:

• Substitution variables. They allow to put substitution parameters in a command text. This is to extend the use of parameters. For example, to parameterize a table name in FROM clause or the column names in SELECT clause, the substitution variables can be used but the parameters are of no use.
• Escape sequences. They allow writing DBMS independent SQL commands.
• Conditional substitutions. They allow expand SQL command conditionally, depending on application defined attributes or currently attached DBMS.

Setting CommandText or SQL property values will automatically fill the Macros collection property when ResourceOptions.MacroCreate is True. At Prepare call, the AnyDAC command preprocessor transforms command text into a form understood by the DBMS when ResourceOptions.MacroExpand is True. This means, the macros are not visible to the DBMS. 

The Params collection property is filled automatically when ResourceOptions.ParamCreate is True. At Prepare call AnyDAC replaces the AnyDAC parameter markers with the DBMS native markers when ResourceOptions.ParamExpand is True. 

The escape sequences and conditional substitutions are processed when ResourceOptions.EscapeExpand is True. 

If the application needs to support multiple DBMS’s, then it must be aware that their SQL dialects may be different. AnyDAC escape sequences allow you to write SQL dialect independent SQL commands. 

For example, the function to convert string to upper case is different in MySQL, Oracle and Microsoft SQL Server. But the following command will work on any DBMS: 

SELECT {ucase(Name)} FROM MyTable

Note, with SQLite add the uADStanExprFuncs unit to your "uses" clause. In more complex cases, parts of the command or even the full command must be written differently. Then, the AnyDAC conditional escape sequence will help: 

{IF Oracle} SELECT * FROM OracleTab {fi}
{IF MSSQL} SELECT * FROM MSSQLTab {fi}

A substitution variable starts with ‘!’ or ‘&’ symbol and is followed by the macro variable name. For example: 

 SELECT * FROM &TabName

The symbols have the following meaning: 

• ‘!’ - “string” substitution mode. Macro value will be substituted “as is”, directly into the command text without any transformations. 

• ‘&’ – “SQL” substitution mode. Macro value will be substituted depending on the macro data type, using target DBMS syntax rules. 

To use the macros use the code like this: 

ADQuery1.SQL.Text := 'SELECT * FROM &TabName';
ADQuery1.MacroByName('TabName').AsRaw := 'Orders';
ADQuery1.Open;

The macros are processed when ResourceOptions.MacroCreate and MacroExpand are True. 

A parameter marker starts with ':' symbol and is followed by the parameter name. For example: 

SELECT * FROM Orders WHERE OrderDate > :FROM_DATE

The '?' symbol is recognized an unnamed parameter marker. This is for DataSnap compatibility and should not be used in an ordinary AnyDAC application. 

To use the parameters read the "Using parameters" chapter at "Executing Command". The parameters are processed when ResourceOptions.ParamCreate and ParamExpand are True. 

AnyDAC has 5 kinds of escape sequences: 

• Allowing constant substitution. 

• Allowing identifier substitution. 

• Conditional substitution. 

• LIKE operator escape sequence. 

• Scalar functions. 

The escape sequences are processed when ResourceOptions.EscapeExpand is True. 

The constant substitution escape sequences allow writing constants in command text, independent on DBMS syntax and regional settings. Following describes escape sequences expansion to DBMS syntax:

The identifier substitution escape sequence allows abstracting from DBMS specific identifier quoting rules. Read the "Object Names" chapter for more details. The syntax is:

The escape function sequence allows abstracting from a DBMS specific set of a build-in functions and their syntax. The syntax is:

Conditional substitution escape sequences allow substitute text into command, depending on either a DBMS the application is connected to, either on a macro variable value. Beside different syntaxes, these constructions handle parameters and macros in different ways. Escape sequence syntaxes are:

AnyDAC DBMS identifiers are case insensitive. Following table lists them:

For example: 

{iif (Oracle, TO_CHAR, MSSQL, CONVERT)}          -> ‘TO_CHAR’ on Oracle and ‘CONVERT’ on SQL Server.
{iif (&v1, Me, &v2, You, We)}                   -> ‘Me’ if &v1 has nonempty value, ‘you’ if &v2 has nonempty value, otherwise ‘We’.
{if Oracle} TO_CHAR {fi} {if MSSQL} CONVERT {fi} -> TO_CHAR on Oracle and CONVERT on SQL Server.
{if &v1} Me {fi} {if &v2} You {fi}              -> ‘Me’ if &v1 has nonempty value + ‘you’ if &v2 has nonempty value.

Note, escape functions IF/IIF have the same names. To distinguish a function from a conditional substitution, use {fn IF(...)} or {fn IIF(...)} syntax. 

To transmit special characters - ‘!’, ‘&’, ‘:’, '?', ‘{‘ or ‘}’ to the DBMS, you will need: 

• to double special character. For example, ‘{{‘. 

• on MySQL precede this character by ‘\’ character. For example, ‘\”’. 

• for '!', '&', '{', '} set ResourceOptions.MacroCreate, MacroExpand and EscapeExpand to False. 

• for ':', '?' set ResourceOptions.ParamCreate, ParamExpand to False. 

Otherwise '!', '&', '{', '}' characters will be treated as a macro command. And ':', '?' as a parameter marker, excluding the following cases: 

• Oracle PL/SQL assignment operator is detected by AnyDAC and will not be treated as parameter marker. 

• Firebird EXECUTE BLOCK parameters inside of BEGIN … END are detected by AnyDAC and will not be treated as parameter marker. 

• TSQL label is detected by AnyDAC and will not be treated as parameter marker.

Object Names