Preprocessing Command Text (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Working with Commands (FireDAC)


Topics

Topic Description
Character Macro Functions List of functions working with character strings.
Numeric Macro Functions List of functions working with numbers.
Date and Time Macro Functions List of functions working with date and time.
System Macro Functions List of special functions.
Convert Macro Function Describes the CONVERT function.
RETURNING Unified Support FireDAC supports the Firebird, Oracle, and PostgreSQL RETURNING phrase.


General

In several places of this text, a "macro" and "escape sequence" are interchangeable phrases. FireDAC supports three types of macro instructions:

  • Substitution variables. They allow placing substitution parameters in a command text in order to extend the use of parameters. For example, to parameterize a table name in the FROM clause or the column names in the SELECT clause, you can use substitution variables but not parameters.
  • Escape sequences. They allow writing DBMS-independent SQL commands.
  • Conditional substitutions. They allow expanding the SQL command conditionally, depending on application-defined attributes or on the currently attached DBMS.

Setting the CommandText or SQL property values automatically fills the Macros collection property when the ResourceOptions.MacroCreate property is set to True. When calling Prepare, the FireDAC command preprocessor transforms the 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 the ResourceOptions.ParamCreate value is True. When calling Prepare, FireDAC replaces the FireDAC parameter markers with the DBMS native markers if 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 DBMSs, it must be aware that their SQL dialects may be different. FireDAC 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 works on any DBMS: 

SELECT {ucase(Name)} FROM MyTable

Note that with SQLite, you need to add the FireDAC.Stan.ExprFuncs unit to your "uses" clause. In more complex cases, parts of the command or even the full command must be written differently. Then, the FireDAC conditional escape sequence helps: 

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

Substitution Variables

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

SELECT * FROM &TabName

The symbols have the following meaning:

  • ‘!’--“string” substitution mode. The macro value will be substituted “as is”, directly into the command text without any transformation.
  • ‘&’-–“SQL” substitution mode. The macro value will be substituted depending on the macro data type, using target DBMS syntax rules.

To use the macros, use the following code snippet: 

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

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

Parameter Markers

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

SELECT * FROM Orders WHERE OrderDate > :FROM_DATE

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

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

Escape Sequences

FireDAC has 5 types 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 of the DBMS syntax and regional settings. The following table 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 the sequence expands to that type constant, otherwise it expands to numeric values 0 or 1.
{s <string>} String constant. <string> is a 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. For more details, see "Object Names". 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 built-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 subtopics, the FireDAC 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 to substitute text into command, depending on either a DBMS the application is connected to, or 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 the application is connected to this DBMS, the Yi text will be substituted into command.
  • Macro variable. If its value is not empty, the Yi text will be substituted into command.

If neither of these conditions is met 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 the Params and Macros collection.

{if x} y {fi} Here, X is either:
  • DBMS identifier. So, if the application is connected to this DBMS, then the Y text will be substituted into command.
  • Macro variable. If its value is not empty, the Y text will be substituted into command.

Parameters and macros in Y will be created in the Params and Macros collection only if X is True.


FireDAC DBMS identifiers are case-insensitive. The following table lists them:

Identifier DBMS

ADS

Advantage Database Server

ASA

Sybase SQL Anywhere

DB2

IBM DB2

FIREBIRD

Firebird

INFORMIX

Informix

INTRBASE

InterBase

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

TDATA
TERADATA

Teradata 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, the escape functions IF/IIF have the same names. To distinguish a function from a conditional substitution, use the {fn IF(...)} or {fn IIF(...)} syntax.

Special Character Processing

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

Otherwise, the '!', '&', '{', '}' characters will be treated as a macro command, and ':', '?' as parameter markers. Excluding the following cases, when a construction is detected by FireDAC and will not be treated as a parameter marker:

  • The Oracle PL/SQL assignment operator.
  • The Firebird EXECUTE BLOCK parameters inside of BEGIN … END.
  • The TSQL label.
  • The PostgreSQL and Informix '::' cast operator.
  • The Informix ':' catalog separator.

See Also

Sample

Retrieved from "https://docwiki.embarcadero.com/RADStudio/Berlin/e/index.php?title=Preprocessing_Command_Text_(FireDAC)&oldid=252566"