Executes an SQL SELECT statement a on the database represented by the alias b. Placeholders in the statement using the format ¶meter_number are replaced with values from the list c. Any characters required for valid statement syntax (for example, quotation marks around string values) must already be present in the statement. The result set is not limited. If you need fewer rows, use a LIMIT clause in the SQL statement.
The value of the first column serves as the key. The remaining columns in each result row are separated by the delimiter d and concatenated into a single string. This string is stored as the value under the key in the map named e. The map is cleared before writing (this can be suppressed using parameter h).
The return value of the function is the number of rows returned in the SQL query result set.
If the result set contains columns of type BLOB, parameter f determines whether binary data is Base64-encoded in the result lists (map entries). When f is true, data is Base64-encoded; otherwise data remains unchanged. If the result set contains no BLOB columns, this parameter has no effect.
IMPORTANT:
In addition to the ¶meter_number syntax, you can also use the syntax for prepared statements in the form @parameter_number:parameter_type@. Prefer this syntax because it prevents SQL injection attacks. Within prepared statements, parameters replace only individual values. SQL syntax such as value lists for IN, table or column names, operators, sort orders, or complete SQL fragments cannot be passed through placeholders.
Parameters
Parameter | Description |
|---|---|
a | SQL statement to execute. |
b | The database alias. |
c | Name of the list containing the parameters. Only the first element of the list is used. NOTE: If the list contains multiple elements and you want to process all of them, you can do this in a loop node (path value: @List:list_name@). After applying this function here (without clearing the map, see parameter h), use the remove from list(value a, name of list b) function on a field within your loop node. |
d | Delimiter string. |
e | Name of the map. |
f | (optional) true if data from a BLOB column should be Base64-encoded in the result lists. Default: false |
g | true for read-only mode. |
h | (optional) true if the map should not be cleared before writing. Default: false |
i | (optional) true to ignore SQL errors reported by the database. Default: <empty> (== false). |
Examples
Assume a table zipcodes containing postal codes for Germany (20,000 entries in this example).
Parameter a | b | c | d | e | f | g | h | i | Result |
|---|---|---|---|---|---|---|---|---|---|
select zipcode, city, county from zipcodes | testdb | ; | ZIPS | false | 20000 |
The result is a map ZIPS containing 20,000 entries with the postal code as key and city and state as value. For example: {80336=Munich;Bavaria}.
Parameter a | b | c | d | e | f | g | h | i | Result |
|---|---|---|---|---|---|---|---|---|---|
select zipcode, city, county from zipcodes where zipcode = &1 | testdb | mylist | ; | ZIPS | false | 1 | |||
select zipcode, city, county from zipcodes where zipcode = @1:i@ | testdb | mylist | ; | ZIPS | false | 1 |
Produces the map with only the entry shown above. The list mylist contains one entry: 80336.
Parameter a | b | c | d | e | f | g | h | i | Result |
|---|---|---|---|---|---|---|---|---|---|
select zipcode, city, county from zipcodes where county = '&1' | testdb | mylist | ; | ZIPS | false | 1500 | |||
select zipcode, city, county from zipcodes where county = @1:s@ | testdb | mylist | ; | ZIPS | false | 1500 | |||
select zipcode, city, county from zipcodes where county = &1 | testdb | mylist | ; | ZIPS | false | Error: string values must be enclosed in quotation marks. |
In the first two cases, produces the map with all Bavarian postal codes. The list mylist contains one entry: Bavaria.