select into map(a,b,list name c, delimiter d, map e,f,g,h,i)

This function executes an SQL statement a on the database being represented by alias b. Placeholders in the form of &parameterNumber being defined in the statement are replaced by the values in list c. Characters that are needed for a valid syntax (e.g. quotes around strings) need to be placed in the statement. The resultset is not limited. If a limit is needed, it has to be set using the LIMIT clause in the SQL statement.

The result set will be written into a map with name e. The value of each row's first column is used as the key, all remaining columns are concatenated using the delimiter d and saved to the map using the key. The map will be cleared before values are added (can be suppressed with parameter h).

The return value of the function is the number of rows in the result set of the SQL query.

If there are columns of type BLOB in the result set, parameter f determines whether or not the binary data should be written to the result lists (entries in the map) in a Base64 format. If m is true, the function performs the encoding, false will leave the data unchanged. If there are no columns of type BLOB, the parameter has no effect.

Important note: In addition to the syntax &parameterNumber for parameters it is possible to use the syntax @parameterNumber:parameterType@ for prepared statements. The use of this prevents SQL Injection Attacks and should be preferred.

Parameters


Parameter

Description

a

SQL statement to be executed.

b

The database alias.

c

Name of the list with 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@). Use function "remove from list()" after this function here (without emptying the map ↔ parameter "h") on a field inside your loop node.

d

Delimiter.

e

Name of the map.

f

(optional) "true" if data in columns of type BLOB should be encoded with Base64. Default: "false".

g

true for read-only mode.

h

(optional) true if the map is not to be cleared before adding values. Default: false

i

(optional) If "true", SQL errors coming from the database are ignored. Default: "false".

Examples


Assume a map "zipcodes" containing all ZIP codes of Germany (imaginary 20000 entries).


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 20000 entries. Each entry has the ZIP code as key and the values of the city and the county the ZIP code belongs to, e.g. {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

Creates the same map with only one single entry. The list "mylist" contains one entry "80336".