AnyDAC
ContentsIndexHome
PreviousUpNext
Preprocessing Command Text

AnyDAC offers powerful SQL command text preprocessor. Allowing to write DBMS independent SQL commands and to get more flexibility while building dynamic SQL command. The preprocessor features include substitution variables, escape sequences, escape functions and conditional escapes.

Group
Topics
Topic 
Description 
List of the functions working with character strings. 
List of the functions working with numbers. 
List of the functions working with date and time. 
List of the special functions. 
Describes the CONVERT function. 
AnyDAC supports the Firebird, Oracle and PostgreSQL RETURNING phrase. 
Links
General

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. 

 

SQL dialect abstraction

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}

 

Substitution variables

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. 

 

Parameter markers

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. 

 

Escape sequences

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:

Format 
Description 
{e <number>} 
Number constant. <number> must be specified with '.' as decimal separator.
For example: {e 123.7} -> 123,7 on MSAccess 
{d <date>} 
Date constant. <date> must be specified in 'yyyy-mm-dd' format.
For example: {d 2004-08-30} -> TO_DATE('2004-08-30', 'yyyy-mm-dd') on Oracle. 
{t <time>} 
Time constant. <time> must be specified in 'hh24:mi:ss' format.
For example: {t 14:30:00} -> CONVERT(DATETIME, '14:30:00', 114) on SQL Server 
{dt <date & time>} 
Date and time constant. <date & time> must be in the above formats. 
{l <boolean>} 
Boolean constant. <boolean> is False or True. If DBMS supports Boolean data type, then sequence expands to that type constant, otherwise to numeric values 0 or 1. 
{s <string>} 
String constant. <string> is quoted or not quoted sequence of characters.
For example: {s Company '1st Coding'} -> 'Company ''1st Coding''' 

 

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

Format 
Description 
{id <identifier name>} 
Expands to DBMS specific quoted identifier syntax.
For example: {id Order Details} -> “Order Details” on Oracle. 

 

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

Format 
Description 
{fn <function name>(<arguments>)} 
The escape functions syntax and set is identical to the ODBC escape functions. In the sub topics the AnyDAC escape functions are listed. For example:
SELECT * FROM MyTab WHERE Year = {fn YEAR({fn NOW()}} or
SELECT * FROM MyTab WHERE Year = {YEAR({NOW()}} ->
SELECT * FROM MyTab WHERE Year = TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) on Oracle. 
{<function name>(<arguments>)} 
The same as above. 

 

Conditional substitution

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:

Format 
Description 
{iif (X1, Y1, …, XN, YN, YN+1) } 
Here Xi is either:
  • DBMS identifier. So, if application is connected to this DBMS, then Yi text will be substituted into command.
  • Macro variable. If it value is not empty, then Yi text will be substituted into command.
If neither of conditions is meted and YN+1 text is specified, then it will be substituted into command. Parameters and macros in either Xi and Yi will be created in Params and Macros collection. 
{if x} y {fi} 
Here X is either:
  • DBMS identifier. So, if application is connected to this DBMS, then Y text will be substituted into command.
  • Macro variable. If it value is not empty, then Y text will be substituted into command.
Parameters and macros in Y will be created in Params and Macros collection only if X is “true”. 

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

Identifier 
DBMS 
ADS 
Advantage Database Server 
ASA 
Sybase SQL Anywhere 
DB2 
IBM DB2 
INTRBASE 
Interbase or Firebird Server 
MSACCESS
MSACC 
Microsoft Access database 
MSSQL 
Microsoft SQL Server 
MYSQL 
MySQL Server 
ORACLE
ORA 
Oracle Server 
OTHER 
Any other DBMS, not listed in the table. 
POSTGRESQL
PG 
PostgreSQL Server 
SQLITE 
SQLite database 

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. 

 

Special character processing

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.

What do you think about this topic? Send feedback!