call db procedure a, alias b, [c,d,e,f,g,h,i,j],k,l,m,[n]

Group

Basic Functions


Calls a stored procedure of a database. Parameter a contains the SQL for the procedure call, b the database alias. Optional parameters c to j are used as input parameters in the procedure call. The input parameters in the stored procedure call in a are to be specified in the form of @index:type@, e.g. @0:s@,@1:f@. The index starts at 0.

Note: We talk about parameters of this function here and input parameters and output parameters of the stored procedure.


A maximum of 8 parameters is supported. If more are needed, use function call db procedure a, alias b,c,d,e,f.

Parameter k is a comma-separated list of positions that define which columns of the resultset of the function are interpreted as output parameter values of the stored procedure. As a result, a list of output parameter values is generated. The index starts at 1.

Parameter l specifies the position in the list of output parameter values generated by k. The value of this position is returned as the result of the function. The index starts at 1.


Note: Some databases return the input parameters and output parameters of the stored procedure in the result set (for example MySQL, see example below). Other databases, on the other hand, only return the output parameters of the stored procedure in the result set. Parameter e must be adjusted accordingly.

If parameter n is set to true, the procedure will be executed again if an error occurs.

Types of Parameters


i

Integer.

l

Long.

f

Float.

r

Double or Real.

d

Date.

t

Timestamp.

s

Varchar or String.

b

BLOB.

Description of Parameters


Parameter

Description

a

SQL for the call of the stored procedure.

b

Database alias.

c

(optional) Input parameter for the stored procedure.

d

(optional) Input parameter for the stored procedure.

e

(optional) Input parameter for the stored procedure.

f

(optional) Input parameter for the stored procedure.

g

(optional) Input parameter for the stored procedure.

h

(optional) Input parameter for the stored procedure.

i

(optional) Input parameter for the stored procedure.

j

(optional) Input parameter for the stored procedure.

k

Comma-separated list of positions that define which columns of the resultset of the procedure call are interpreted as output parameter values of the stored procedure. As a result, a list of output parameter values is generated. The index starts at 1. Note: If k is left empty, no output parameter is defined.

l

The position in the list of output parameter values generated in k. The value of this position is returned as the result of the function. The index starts at 1.

If set to -1, the function returns true if the procedure call was successful, false otherwise. The latter would otherwise lead to an exception.

m

Maximum number of rows to be read. Default: 1.

Note: Some databases perform data-changing statements (UPDATE, DELETE) only on a limited number of rows. In that case, use 0 (unlimited) to make sure that more than one row is getting updated/deleted.

n

(optional) true for retry in case of an error. Default: true.

Examples


Assume the following stored procedure (using MySQL).


CREATE PROCEDURE `adding_test`
(INOUT p1 VARCHAR(10), IN p2 INTEGER, IN p3 INTEGER, OUT p4 REAL)
BEGIN
DECLARE answer VARCHAR(10);
DECLARE result REAL;
IF p1 = 'plus' THEN
SET answer = 'added';
SET result = p2 + p3;
ELSE
SET answer = 'subtracted';
SET result = p2 - p3;
END IF;
select answer into p1;
select result into p4;
END

Now the stored procedure is called. The example applies to a call in a MySQL database. Note: If the executing database is an MSSQL, embed the actual statements in brackets, e.g. {call adding_test(@0:s@,@1:i@,@2:i@,@3:r@)} (so put the SQL statement in curly brackets). For other databases, please refer to the documentation of the respective manufacturer for the correct syntax.

Parameter a

call adding_test(@0:s@,@1:i@,@2:i@,@3:r@)

Parameter b

hub

Parameter c

plus

Parameter d

1

Parameter e

2

Parameter f

0

Parameter k

4

Parameter l

1

Parameter m


Result

3.0

The resolved call will be call adding_test('plus',1,2,0). Parameter k describes that position 4 of the resultset is to be interpreted as an output parameter of the stored procedure and parameter l describes that the first output parameter (and there is only one here, namely position 4 in the resultset) is to be used as the result of the function.

So from the result set (added,1,2,3.0) position 4 is taken, resulting in the list (3.0). Then the first position is taken from the list (3.0). So the result of the function is 3.0.