call db procedure a with ResultSet into Map/List()

Important note: Returning the result set does not work with Oracle databases.

Executes a stored procedure (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 of parameters c to i. Characters that are needed for a valid syntax need to be placed in the statement. There is no limit to the resultset being retrieved. If a limit is needed it has to be set using the LIMIT clause in the SQL statement.

The resultset will be written to a named map or a named list with name k. If a map is used, the value of each row's first column is used as key, unless parameter n defines an alternative column. All columns are concatenated together using the delimiter j and saved to the list or the map using the key. Before starting to write the result to the map or list it will be cleared.

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, let the function performs the encoding, false will leave the data unchanged. If there are no columns of type BLOB, the parameter has no effect.

Parameters


Parameter

Description

a

SQL statement.

b

Database alias.

c

(optional) Input parameter &1.

d

(optional) Input parameter &2.

e

(optional) Input parameter &3.

f

(optional) Input parameter &4.

g

(optional) Input parameter &5.

h

(optional) Input parameter &6.

i

(optional) Input parameter &7.

j

Delimiter.

k

Map/List name.

l

(optional) Use list instead of map. Default = false.

m

Encode Base64 blob content {true, false}.

n

Index of the column that is used as map key. Default: 1.

Examples


Assume a stored procedure GetAllProfiles(searchterm), which returns the profile ID and the profile name as resultset.


Parameter a

b

c

d…i

j

k

l

m

n

Result

CALL GetAllProfiles("&1")

testdb

demoprofil


;

profile_map




5

CALL GetAllProfiles("&1")

testdb

demoprofil


;

profile_list

true



5


Note: The examples above apply to a call in a MySQL database. If the executing database is an MSSQL, embed the actual statements in brackets, e.g. {CALL GetAllProfiles("&1")} (so put the SQL statement in curly brackets). If the database is connected via a connector, the command execute must be used instead of call. For other databases, please refer to the documentation of the respective manufacturer for the correct syntax.