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
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: {{ }} [[ ]]
Input parameters can be defined in two ways:
in legacy format: %identifier
in extended format: {%identifier[,transformer[,transformer parameter 1,...,transformer parameter n]]}
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.
![]() |
---|
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.
![]() |
---|
Parameter names are case insensitive; the Config object considers the keys "elementid", "Elementid", "ElementID" and "ELEMENTID" as the same key. |
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)
![]() |
---|
The default transformer is "String" |
![]() |
---|
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. |
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
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
-- 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}
![]() |
---|
You can create additional transformers by implementing the IFormatterFactory interface. |
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".
![]() |
---|
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.
![]() |
---|
The default formatter is "Simple" |
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
-- 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
![]() |
---|
Exclusion zones can be nested as in the example below |
-- 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