CALL Statement

The CALL data manipulation statement executes a procedure or a table procedure. The values of output parameters return in the form of 0 to 1 result row for the call of a procedure and 0 to multiple rows for the call of a table procedure. When the CALL statement is:
idmscu19
The CALL data manipulation statement executes a procedure or a table procedure. The values of output parameters return in the form of 0 to 1 result row for the call of a procedure and 0 to multiple rows for the call of a table procedure. When the CALL statement is:
  • Submitted through the command facility, the values of all parameters are contained in the result rows and displayed in tabular form.
  • Embedded in an application program, at most a single row can return and the values in the result row are stored in host variables.
  • Dynamically prepared, the result rows must return through a cursor just as if the prepared statement were a SELECT statement.
This article describes the following information:
2
2
Authorization
To issue a CALL statement, you must either own or have the SELECT privilege on the procedure or table procedure explicitly named in the statement.
Syntax
►►─── CALL ───┬─ 
procedure-reference
 ───────┬─────────────────────────────────►◄               └─ 
table-procedure-reference
 ─┘
Parameters
  • procedure-reference
    Identifies the procedure that is invoked, the input values that pass to the procedure and optionally the host.variables for passing and returning values of input/output parameters.
  • table-procedure-reference
    Identifies the table procedure that is invoked, the input values that pass to the table procedure and optionally the host.variables for passing and returning values of input/output parameters.
Usage
Embedding a CALL statement
When embedding a CALL statement in an application program, output values return only for those parameters that you specify as host-variables, local variables, or routine parameters.
call myproc (5, :wk-out)
If the procedure or table procedure updates the value of the first parameter and the application program needs to see that value, then you must specify both parameters as host-variables. You should set the first host-variable to 5 before executing the CALL statement:
move 5 to wk-val call myproc (:wk-val, :wk-out)
Initializing parameters
It is important to initialize all host-variables, local variables, and routine parameters that you reference in a CALL statement prior to its execution. Since all such parameters are treated as input values, failure to initialize such host-variables, local variables, or routine parameters results in a data exception if its value does not conform to its data type. If there is no value to pass, you should declare the host-variable with a null indicator with a value set to -1 and set the local variables and routine parameters to null.
Dynamically executing a CALL statement
When describing the output from a dynamically prepared CALL statement, the SQLD field of the SQLDA contains a count of the number of parameters for the procedure or table procedure. The first SQLD entries within the SQLDA contain a description of those parameters.
You must return the output parameter values of a dynamically prepared CALL statement using a cursor. In other words, you must treat a dynamically prepared CALL statement as a dynamically prepared SELECT statement.
Result Sets from SQL-invoked Procedures
An SQL-invoked procedure can return results to the caller by assigning values to one or more parameters of the procedure. Using Dynamic Result Sets, an SQL-invoked procedure can return result sets in the form of rows of result tables.
To exploit result sets returned by an SQL-invoked procedure, an application must consist of at least an SQL-invoked procedure and a caller of that procedure. The caller can be an SQL client program or another SQL-invoked routine. The SQL-invoked procedure that returns the result sets can be an external procedure (COBOL, PL/I, Assembler or CA ADS) or an internal SQL procedure written in SQL.
For an SQL-invoked procedure to return result sets to its caller, it must be defined with a positive integer value for the new
Dynamic Result Sets
attribute.
A cursor declared or dynamically allocated in the SQL-invoked procedure becomes a potential returned result set if its definition contains
With Return
as the value for the new returnability attribute. Such a cursor is called a
returnable cursor
. It becomes a returned result set if it is in the open state when the SQL-invoked procedure terminates.
An SQL-invoked procedure can return multiple result sets up to the number specified by the Dynamic Result Sets attribute of the procedure. The list of returned result sets are sequenced in the order of the open of the cursors. If the procedure starts multiple sessions, then returned result sets are grouped by session and the sessions are sequenced in the order of the connects. After a procedure CALL, the new SQLCA field SQLCNRRS contains the number of result sets returned by the procedure.
The caller of an SQL-invoked procedure accesses returned result sets by allocating a dynamic cursor and associating it with the procedure through an ALLOCATE CURSOR FOR PROCEDURE statement. Such a cursor is called a
received cursor
.
A successful ALLOCATE CURSOR FOR PROCEDURE statement associates the received cursor with the first result set from the sequence of returned result sets and places the cursor in the open state. The cursor position is the same as it was when the SQL-invoked procedure terminated and the associated returned result set is removed from the list of returned result sets.
The caller of the procedure can access the next in the sequence of returned result sets by either allocating another cursor for the procedure or by closing the previously allocated received cursor. If the close is successful and the list of remaining returned result sets is not empty, the received cursor is automatically placed in the open state and associated with the result set that is now first in the list. The newly associated result set is also removed from the list. This process can be repeated until the list of returned result sets is empty.
A new invocation of the SQL-invoked procedure automatically destroys all the returned result sets from the previous invocation.
The received cursors, allocated by the caller and associated with returned result sets, are necessarily dynamic. Unless the program knows the returned columns and their data type, a DESCRIBE CURSOR statement is needed to retrieve the description of the returned result set in an SQL descriptor area (SQLDA).
Only the immediate caller of an SQL-invoked procedure can process returned result sets. There is no mechanism for the caller to return returned result sets to its caller.
Calling an SQL Procedure
An SQL procedure is an SQL-invoked procedure with language SQL. Any transaction started by this procedure is shared with the transaction of the caller. After returning from an SQL procedure, any session opened by the procedure is automatically released except for sessions that have result sets. Such sessions are released when their last result set has been processed and the associated received cursor has been closed.
When calling an SQL procedure or table procedure, if error message DB001078 with condition 38999 is returned, it might indicate that a record associated with the dialog was too large to fit into the buffer. If this occurs, see messages DC171027 and DC466014 in the IDMS log for more information on the dialog and process causing this problem and how to resolve it.
Calling an SQL-invoked Procedure Returning Result Sets
After a CALL of an SQL-invoked procedure that has been defined with a positive value for the Dynamic Result Sets attribute the number of actual returned results sets is available in the field SQLCNRRS of the SQLCA. The number of returned result sets can also be determined by issuing a GET DIAGNOSTICS statement to retrieve the IDMS_RETURNED_RESULT_SETS information item.
The successful execution of a CALL statement may result in one of two warning conditions:
  • 0100C SQL invoked procedure returned result sets
    Indicates that the number of result sets returned by the procedure is less than or equal to the value of the procedure's DYNAMIC RESULT SETS attribute.
  • 0100E Attempt to return too many result sets
    Indicates that the procedure attempted to return more result sets than permitted by its DYNAMIC RESULT SETS attribute. The actual number of result sets is reduced to the value of the DYNAMIC RESULT SETS attribute.
A call of a procedure destroys any result sets left over from a previous invocation of the same procedure.
Example
The following example illustrates the basic coding techniques to use dynamic result sets in an application. The SQL procedure, SQLROUT.PROCESSRESULTSET, calls the SQL procedure SQLROUT.CREATERESULTSET and dynamically processes the returned results sets. Included in the example are the definition of a table SQLROUT.RSTAB, the load of this table, the definitions of the SQL procedures SQLROUT.CREATERESULTSET and SQLROUT.PROCESSRESULTSET, and finally the CALL of SQLROUT.PROCESSRESULTSET.
create table SQLROUT.RSTAB   ( ID             integer,     MES               character(10)   ) in PROJSEG.PROJAREA ++ insert into SQLROUT.RSTAB values (1, 'txt1')++ insert into SQLROUT.RSTAB values (2, 'txt2')++ insert into SQLROUT.RSTAB values (3, 'txt3')++ insert into SQLROUT.RSTAB values (4, 'txt4')++ insert into SQLROUT.RSTAB values (5, 'txt5')++ insert into SQLROUT.RSTAB values (6, 'txt6')++ commit++   create procedure SQLROUT.CREATERESULTSET     (TITLE              char(10)  with default,      P_ID               integer   with default,      RESULT             char(30)  with default   )   external name CRRESSET language SQL   dynamic result sets 4 begin not atomic  declare DYNST         char(100);  declare L_ID          integer default 2;  declare TEST_CUR2 cursor with return for   select ID, MES from SQLROUT.RSTAB   where ID >= P_ID;  declare TEST_CUR4 cursor with return for  select ID, MES from SQLROUT.RSTAB  where ID < P_ID; set DYNST = 'SELECT ID, MES FROM SQLROUT.RSTAB '      || 'WHERE ID < CAST(? AS INTEGER)';  prepare 'DYNSTMT1' FROM DYNST;  allocate 'TEST_CUR1' cursor with return for 'DYNSTMT1';  prepare 'DYNSTMT3' FROM DYNST;  allocate 'TEST_CUR3' cursor with return for 'DYNSTMT3';  open TEST_CUR4;  open 'TEST_CUR3' using L_ID;  set L_ID = L_ID + 1;  open 'TEST_CUR1' using L_ID;  open TEST_CUR2; set RESULT = '4 RESULT SET RETURNED'; end ++ commit++   create procedure SQLROUT.PROCESSRESULTSET     ( TITLE       character(10) with default,       P_ID        integer with default,       CNT_RESULT_SETS      integer,       RESULT      varchar(1024) with default,       ERROR1      varchar(72) with default      )       external name PRRESSET  language SQL begin not atomic  declare L_MES         char(20);  declare L_ID        integer;  declare BINBUF        binary(200);  declare CNT           integer;  declare L_CNT_RESULT_SETS integer default 0;  declare continue handler for SQLWARNING   set RESULT = RESULT|| SQLSTATE|| ' ';  declare exit handler for SQLEXCEPTION   begin    declare C_FUN  CHAR(64);    declare L_MES varchar(256);    declare M_TEXT CHAR(256);      get diagnostics C_FUN = COMMAND_FUNCTION;    get diagnostics CONDITION 1 M_TEXT = MESSAGE_TEXT;    set ERROR1 = TRIM(CHAR(CNT))|| ' ROWS FETCHED; '||           TRIM(C_FUN)|| ' '|| TRIM(M_TEXT);    get DIAGNOSTICS CONDITION 2 M_TEXT = MESSAGE_TEXT;    set ERROR1 = ERROR1|| TRIM(M_TEXT);   end;  set RESULT = 'ROWS FETCHED: ';  call SQLROUT.CREATERESULTSET(TITLE,P_ID, L_MES);  get diagnostics CNT_RESULT_SETS = IDMS_RETURNED_RESULT_SETS;  allocate 'CUR2' for procedure    specific procedure SQLROUT.CREATERESULTSET;    while (L_CNT_RESULT_SETS < CNT_RESULT_SETS)   do    set CNT = 0;    describe cursor 'CUR2' structure using SQL descriptor SQLDA;      fetch 'CUR2' into L_ID, L_MES;    while (SQLSTATE = '00000')     do      set RESULT = RESULT|| '<'|| trim(L_MES)|| '>';      set CNT = CNT + 1;      fetch 'CUR2' into L_ID, L_MES;     end while;    close 'CUR2';    set L_CNT_RESULT_SETS = L_CNT_RESULT_SETS + 1;    set RESULT = RESULT|| '# '|| trim(char(CNT))|| '//';   end while; end ++ commit++   call SQLROUT.PROCESSRESULTSET('T4',4)++   *+  TITLE              P_ID  CNT_RESULT_SETS *+  -----              ----  --------------- *+  T4                    4                4 *+ *+  RESULT *+  ------ *+  ROWS FETCHED: 0100C <txt1><txt2><txt3>0100D # 3//<txt1>0100D *+                 # 1//<txt1><txt2>0100D # 2//<txt4><txt5><txt6># 3// *+  ERROR1 *+  ------ set options command delimiter default++
More Information