select into list()
This function executes an SQL statement a on the database being represented by alias b. Placeholders in the form of ¶meterNumber being defined in the statement are replaced by the values of parameters c to j. Characters that are needed for a valid syntax need to be placed in the statement. The number of selected rows can be restricted with l. A value of 0 means an unlimited resultset.
Every column in the resultset leads to the creation of a list with name <value of parameter k>:<column name> (all characters in uppercase, see examples). If a list with that name already exists, the values of this existing list will be deleted.
The return value of the function is the number of rows being selected.
Is a column being selected of type BLOB, the parameter m allows to specify if the data being selected should be encoded with Base64 before being added to the result list. If m is true, the function performs an 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 use of ¶meterNumber for parameters it is possible to use the naming scheme for 'prepared statements'. The use of this prevents SQL Injection Attacks and should be preferred.
Parameters
|
Parameter |
Description |
|
a |
SQL statement to be called. |
|
b |
Database alias. |
|
c |
(optional) Parameter &1 of the statement. |
|
d |
(optional) Parameter &2 of the statement. |
|
e |
(optional) Parameter &3 of the statement. |
|
f |
(optional) Parameter &4 of the statement. |
|
g |
(optional) Parameter &5 of the statement. |
|
h |
(optional) Parameter &6 of the statement. |
|
i |
(optional) Parameter &7 of the statement. |
|
j |
(optional) Parameter &8 of the statement. |
|
k |
Prefix to be added to the name of the result lists. |
|
l |
(optional) The maximum number of rows to be selected. Default: 0. |
|
m |
(optional) true if data in columns of type BLOB should be encoded with Base64. Default: false. |
|
n |
(optional) true for read-only mode. Default: false. |
|
o |
(optional) true if column display names should be used for the creation of lists instead of the column names (see examples). Default: false. |
|
p |
(optional) If true, SQL errors coming from the database are ignored. Default: <empty> (== false). |
Examples
Defined is a table zipcodes containing all zip codes of Germany (containing imaginary 20000 rows).
|
Parameter a |
b |
c |
d…j |
k |
l |
m |
n |
o |
p |
Result |
|
select zipcode as zip, city as town from zipcodes |
testdb |
|
|
ZIPS |
0 |
|
|
|
|
20000 |
|
select zipcode as zip, city as town from zipcodes |
testdb |
|
|
ZIPS |
0 |
|
|
true |
|
20000 |
Example 1: After the call, a list ZIPS:ZIPCODE with the values of column zipcode and a list ZIPS:CITY with the values of column city have been created. Example 2: The result is the same but the names of the lists are ZIPS:ZIP and ZIPS:TOWN.
|
Parameter a |
b |
c |
d…j |
k |
l |
m |
n |
o |
p |
Result |
|
select zipcode, city from zipcodes where zipcode = &1 |
testdb |
80336 |
|
ZIPS |
0 |
|
|
|
|
1 |
Creates the two aforementioned lists of example 1 with one entry each: 80336 and Munich.