Parameterized SQL format

The standard extractor (StandardExtractor) and the standard record action (StandardRecordAction) executes a list of parameterized SQL requests; parameter values are determined by the key value pairs stored in the Config object. If the value of a parameter is empty, it is possible either to consider the corresponding parameter as NULL or exclude a part of the SQL request, which comes handy for filters.

The former parser created plain text SQL requests by replacing the parameters directly in the text with the values coming from the Config object; the version 6 parser creates instances of a new intermediate class, SqlStatement, which enables the following features:

by default, parameters are translated into the platform's SQL parameters which the easiest and cleanest way to avoid SQL injection; the former method which enabled using SQL keywords (column names, sort direction like DESC or ASC, etc.) is still available with a custom parameter type, SQL injection prevention being your responsability
parameter values coming from the Config object are always strings; now, you can specify the parameter's data type and a parsing method to translate the string into the required data type
SqlStatement instances are easier to use inside platform code (.NET, PHP, Java)
SqlStatement instances can be reused with different sets of key / value pair; parse once, execute at will

Special characters and escape sequences

The following characters have special meaning: { } (used for parameter definition) [ ] (used to define a zone that can be erased from the request).

They can be escaped by doubling them: {{ }} [[ ]]

Parameter definition

Input parameters can be defined in two ways:

in legacy format: %identifier
in extended format: {%identifier[,transformer[,transformer parameter 1,...,transformer parameter n]]}

Parameters identifiers

Parameter identifiers can be either a number or a string; allowed characters are letters, _ and -. If an identifier starts with a digit, it is considered to be a number; if it starts with either a letter or a _ or a -, it is considered to be a string.

If a parameter is a number, the key in the Config object is determined as follows:

the number is 1: the key is ELEMENTID

the number is different from 1: the key depends on the prefix and first parameters given to the Parse(String, String, Int32, DictionaryString, IFormatterFactory, DictionaryString, KeyValuePairSqlType, IFormatterFactory) method with the following formula: prefix followed by a number calculated as first - 1 + number.

for example, if prefix is "REC" and first is 1, %2 is associated to the value of the REC1 key.

Note
The standard extractor uses "REC" as prefix and the value associated with "FIRSTREC" as first (1 if "FIRSTREC" is not a number) The standard record action uses "ENR" as prefix and the value associated with "FIRSTENR" as first (1 if "FIRSTENR" is not a number)

Note

The standard extractor uses "REC" as prefix and the value associated with "FIRSTREC" as first (1 if "FIRSTREC" is not a number)

The standard record action uses "ENR" as prefix and the value associated with "FIRSTENR" as first (1 if "FIRSTENR" is not a number)

If a parameter is a string, the key in the Config object is the string.

Note
Parameter names are case insensitive; the Config object considers the keys "elementid", "Elementid", "ElementID" and "ELEMENTID" as the same key.

Data types and transformers

The data type is used to define the SQL data type of the parameter, among the following values: NoParam, String, Boolean, Byte, UInt16, UInt32, UInt64, SByte, Int16, Int32, Int64, Currency, Decimal, Single, Double, Date, Time, DateTime.

NoParam is a special value: it tells the system not to create a SQL parameter, but to replace the parameter with the determined value in the SQL request's text. The other values are defined as the corresponding data types in the .NET framework.

As described in the extended format, the data type name is not directly used in the SQL format; it is inferred from the transformer used. The role of the transformer is to transform the string stored in the Config object into the target data type.

The built-in transformers are the following:

NoParam: a special transformer associated with the NoParam pseudo data type
String: returns the input
Boolean: if the input value is "true" or "1", the output is true, else it is false
Byte: tries to parse the input as an unsigned byte in decimal format; if the input is not a number or overflows, returns null
UInt16: tries to parse the input as an unsigned short in decimal format; if the input is not a number or overflows, returns null
UInt32: tries to parse the input as an unsigned int in decimal format; if the input is not a number or overflows, returns null
UInt64: tries to parse the input as an unsigned long in decimal format; if the input is not a number or overflows, returns null
SByte: tries to parse the input as an signed byte in decimal format; if the input is not a number or overflows, returns null
Int16: tries to parse the input as an signed short in decimal format; if the input is not a number or overflows, returns null
Int32: tries to parse the input as an signed int in decimal format; if the input is not a number or overflows, returns null
Int64: tries to parse the input as an signed long in decimal format; if the input is not a number or overflows, returns null
Currency: tries to parse the input as a decimal; if the input is not a number or overflows, returns null. Accepts one format parameter which is the culture identifier used to parse the decimal; the default culture is the so called InvariantCulture
Decimal: tries to parse the input as a decimal; if the input is not a number or overflows, returns null. Accepts one format parameter which is the culture identifier used to parse the decimal; the default culture is the so called InvariantCulture
Single: tries to parse the input as a single-precision floating point number; if the input is not a number or overflows, returns null. Accepts one format parameter which is the culture identifier used to parse the decimal; the default culture is the so called InvariantCulture
Double: tries to parse the input as a double-precision floating point number; if the input is not a number or overflows, returns null. Accepts one format parameter which is the culture identifier used to parse the decimal; the default culture is the so called InvariantCulture
Date: tries to parse the input as a date, without time; if the input is not a date, returns null. Accepts two format parameters: the first is the date format in the .NET format, the second one is the culture identifier. The default format is the french date format, dd/MM/yyyy, and the default culture is the so called InvariantCulture
Time: tries to parse the input as a time, without date; if the input is not a time, returns null. Accepts two format parameters: the first is the date format in the .NET format, the second one is the culture identifier. The default format is the french date format, dd/MM/yyyy, and the default culture is the so called InvariantCulture
DateTime (or Datetime): tries to parse the input as a date with time; if the input is not a date, returns null. Accepts two format parameters: the first is the date format in the .NET format, the second one is the culture identifier. The default format is the french date format, dd/MM/yyyy, and the default culture is the so called InvariantCulture
DecimalToByte: tries to parse the input as a decimal, then converts the decimal to a byte with a fixed precision; if the input is not a decimal, returns null; if the input multiplied by 10 to the precision's power is not an integer, it is rounded. The goal of this format is to circumvent the lack of a fixed precision decimal data type in SQLite; the idea is to store the data as an integer, and to translate it back into a decimal. Accepts two format parameters: the first is the precision (0 by default), the second the culture identifier (the so called InvariantCulture by default)
DecimalToUInt16: tries to parse the input as a decimal, then converts the decimal to an unsigned short with a fixed precision; if the input is not a decimal, returns null; if the input multiplied by 10 to the precision's power is not an integer, it is rounded. The goal of this format is to circumvent the lack of a fixed precision decimal data type in SQLite; the idea is to store the data as an integer, and to translate it back into a decimal. Accepts two format parameters: the first is the precision (0 by default), the second the culture identifier (the so called InvariantCulture by default)
DecimalToUInt32: tries to parse the input as a decimal, then converts the decimal to an unsigned int with a fixed precision; if the input is not a decimal, returns null; if the input multiplied by 10 to the precision's power is not an integer, it is rounded. The goal of this format is to circumvent the lack of a fixed precision decimal data type in SQLite; the idea is to store the data as an integer, and to translate it back into a decimal. Accepts two format parameters: the first is the precision (0 by default), the second the culture identifier (the so called InvariantCulture by default)
DecimalToUInt64: tries to parse the input as a decimal, then converts the decimal to an unsigned long with a fixed precision; if the input is not a decimal, returns null; if the input multiplied by 10 to the precision's power is not an integer, it is rounded. The goal of this format is to circumvent the lack of a fixed precision decimal data type in SQLite; the idea is to store the data as an integer, and to translate it back into a decimal. Accepts two format parameters: the first is the precision (0 by default), the second the culture identifier (the so called InvariantCulture by default)
DecimalToSByte: tries to parse the input as a decimal, then converts the decimal to a signed byte with a fixed precision; if the input is not a decimal, returns null; if the input multiplied by 10 to the precision's power is not an integer, it is rounded. The goal of this format is to circumvent the lack of a fixed precision decimal data type in SQLite; the idea is to store the data as an integer, and to translate it back into a decimal. Accepts two format parameters: the first is the precision (0 by default), the second the culture identifier (the so called InvariantCulture by default)
DecimalToInt16: tries to parse the input as a decimal, then converts the decimal to a signed short with a fixed precision; if the input is not a decimal, returns null; if the input multiplied by 10 to the precision's power is not an integer, it is rounded. The goal of this format is to circumvent the lack of a fixed precision decimal data type in SQLite; the idea is to store the data as an integer, and to translate it back into a decimal. Accepts two format parameters: the first is the precision (0 by default), the second the culture identifier (the so called InvariantCulture by default)
DecimalToInt32: tries to parse the input as a decimal, then converts the decimal to a signed int with a fixed precision; if the input is not a decimal, returns null; if the input multiplied by 10 to the precision's power is not an integer, it is rounded. The goal of this format is to circumvent the lack of a fixed precision decimal data type in SQLite; the idea is to store the data as an integer, and to translate it back into a decimal. Accepts two format parameters: the first is the precision (0 by default), the second the culture identifier (the so called InvariantCulture by default)
DecimalToInt64: tries to parse the input as a decimal, then converts the decimal to a signed long with a fixed precision; if the input is not a decimal, returns null; if the input multiplied by 10 to the precision's power is not an integer, it is rounded. The goal of this format is to circumvent the lack of a fixed precision decimal data type in SQLite; the idea is to store the data as an integer, and to translate it back into a decimal. Accepts two format parameters: the first is the precision (0 by default), the second the culture identifier (the so called InvariantCulture by default)

Note
The default transformer is "String"

Important
Breaking change: the transformer of parameters defined in the legacy format is "String"; before, it worked as "NoParam". Therefore, legacy parameters from the previous versions intended to make SQL keywords variable will NOT work without change.

Legacy format

Copy

SELECT
  MY_FIELD
FROM
  MY_TABLE
WHERE
  ID='%1'

-- is transformed into:

SELECT
  MY_FIELD
FROM
  MY_TABLE
WHERE
  ID=@PARAM1

-- with @PARAM1 a string SQL parameter whose value is the value of the Config parameter ELEMENTID

New format

Copy

SELECT
  MY_FIELD
FROM
  MY_TABLE
WHERE
  ID='{%1,String}'

-- is transformed into:

SELECT
  MY_FIELD
FROM
  MY_TABLE
WHERE
  ID=@PARAM1

-- with @PARAM1 a string SQL parameter whose value is the value of the Config parameter ELEMENTID

SELECT
  MY_FIELD
FROM
  MY_TABLE
WHERE
  ID={%1,String}

-- is also transformed as above (note the missing single quotes)

SELECT
  MY_FIELD
FROM
  MY_TABLE
WHERE
  ID={%1,Int32}

-- is transformed into:

SELECT
  MY_FIELD
FROM
  MY_TABLE
WHERE
  ID=@PARAM1

-- with @PARAM1 a signed int SQL parameter whose value is the value of the Config parameter ELEMENTID parsed as an integer, and NULL if this value is not an integer

SELECT
  MY_FIELD
FROM
  MY_TABLE
WHERE
  ID={%1,Decimal,fr-FR}

-- is transformed into:

SELECT
  MY_FIELD
FROM
  MY_TABLE
WHERE
  ID=@PARAM1

-- with @PARAM1 a decimal SQL parameter whose value is the value of the Config parameter ELEMENTID parsed as a decimal in french format (which uses a "," as a decimal separator), and NULL if this value is not such a number

Parameters containing SQL keywords

Copy

-- Example of an SQL request with a parameter intended to output SQL keywords; this will NOT work with version 6

SELECT
  MY_FIELD
FROM
  MY_TABLE
ORDER BY
  MY_FIELD %ASCORDESC

-- This will work

SELECT
  MY_FIELD
FROM
  MY_TABLE
ORDER  BY
  MY_FIELD {%ASCORDESC,NoParam}

Tip
You can create additional transformers by implementing the IFormatterFactory interface.

Formatters

Formatters are the opposite of the transformers; they are used to format data coming from the database before inserting it into the XML document.

They can be declared anywhere in the SQL request, with the following syntax: {column_number[,formatter[,formatter parameter 1,...,formatter parameter n]]}. The column number starts at 0.

The built-in transformers are the following:

Simple: transforms the value into a string with no special formatting; for example, if the value is the decimal 1.5, and the server is configured in french by default, the output will be "1,5".
Note
this transformer escapes a string containing XML data; to transfer the XML data in the document preserving the nodes, use the Xml or Unblob transformers
DateTime: if the data is a Date, a Time or a DateTime, formats it using the parameters; the first is the date format in the .NET format, the second one is the culture identifier.
Decimal: if the data is any kind of decimal number, formats it using the given culture identifier.
Float: if the data is any kind of floating point number, formats it using the given culture identifier.
Double: if the data is any kind of double precision floating point number, formats it using the given culture identifier.
Xml: transforms the value into a string with no special formatting, then attempts to parse it as XML and insert it as such in the XML document. If the XML cannot be parsed, it falls back to escaping the string
Unblob: transforms the value into a string with no special formatting, then attempts to parse it as XML and insert it as such in the XML document. The difference with the Xml transformer is that if a node contains an "id" attribute, the node name is searched in the XSLMATCH table to fetch additional data; for more information, see the Unblob(String, XmlWriter, Config, Boolean, String, String, Boolean, Boolean, StringBuilder, StringWriter, XmlTextWriter, StackString, Int32, DictionaryString, UnblobConfig, DictionaryString, IFormatterFactory) method.
IntToDecimal: the opposite of the various DecimalToIntXXX transformers; it reads an integer, and outputs a decimal according to the first parameter, the precision, and the second parameter, the culture identifier.

Note
this transformer escapes a string containing XML data; to transfer the XML data in the document preserving the nodes, use the Xml or Unblob transformers

Note
The default formatter is "Simple"

Exclusion zones

There are two ways of dealing with an empty value for a parameter:

define an exclusion zone around the parameter using [ and ]; in this case, the whole zone is deleted when the SQL request is executed
do nothing special: the empty value is treated as any other value; thus, for a NoParam parameter, an empty string is inserted, for a String parameter, the parameter's value is an empty string, and for the other data types, since an empty string cannot be interpreted as a number, or a DateTime, the parameter's value is NULL

Exclusions

Copy

-- supposing both ELEMENTID and REC1 are empty

SELECT
  MY_FIELD
FROM
  MY_TABLE
WHERE
  MY_PROPERTY='%1'[ AND
  MY_OTHER_PROPERTY='%2']

-- is transformed into:

SELECT
  MY_FIELD
FROM
  MY_TABLE
WHERE
  MY_PROPERTY=@PARAM1

-- with @PARAM1 a string SQL parameter whose value is an empty string; note that the exclusion zone has completely disappeared

Note
Exclusion zones can be nested as in the example below

Nested exclusions

Copy

-- with the following request:

SELECT
  MY_FIELD
FROM
  MY_TABLE[
WHERE
  MY_PROPERTY='%1'[ AND
  MY_OTHER_PROPERTY='%2']
]

-- if ELEMENTID is empty and REC1 not empty:

SELECT
  MY_FIELD
FROM
  MY_TABLE

-- if ELEMENTID is not empty and REC1 is empty:

SELECT
  MY_FIELD
FROM
  MY_TABLE
WHERE
  MY_PROPERTY=@PARAM1