Click or drag to resize
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:

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

Note 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 Note

The default transformer is "String"

Important note 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
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
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
-- 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 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:

Note 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
-- 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 Note

Exclusion zones can be nested as in the example below

Nested exclusions
-- 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