Calling stored procedures from CLI applications

CLI applications call stored procedures by issuing the CALL procedure statement.

Before you begin

Ensure that you have the CLI environment initialized and the database connection is established before calling the stored procedure.

Procedure

To call a stored procedure:

Declare application host variables corresponding to each of the IN, INOUT, and OUT parameters of the stored procedure.
Ensure that data types and lengths of the application variable match the data types and lengths of the stored procedure arguments. The CLI driver supports calling stored procedures with parameter markers.
Initialize the application variables that correspond to the IN, INOUT, and OUT parameters.
Compose a CALL SQL statement.
The following example is a CALL statement with a parameter marker argument:
```
  SQLCHAR *stmt = (SQLCHAR *)"CALL OUT_LANGUAGE (?)";
```
To use named parameter markers (for example, :language), you must explicitly enable a named parameter processing by setting the EnableNamedParameterSupport configuration keyword to TRUE:
```
  SQLCHAR *stmt = (SQLCHAR *)"CALL OUT_LANGUAGE (:language)";
```
You can optimize application performance through use of the parameter markers in the CALL procedure statement and bind the host variables to those parameter markers. You can specify string literals for IN arguments in a CALL statement by enclosing the literal CALL statement within the ODBC call escape clause delimiters { }. The following example is a literal CALL statement:
```
  SQLCHAR *stmt = (SQLCHAR *)"{CALL IN_PARAM (123, 'Hello World!')}";
```
When string literals and the ODBC escape clause are used in a CALL procedure statement, the string literals can be specified only for IN mode stored procedure arguments. The INOUT and OUT mode stored procedure arguments must still be specified with parameter markers.
Optional: Prepare the CALL statement by calling the SQLPrepare() function.
Bind each parameter of the CALL procedure statement by calling the SQLBindParameter() function. Ensure that each parameter is bound correctly to the SQL_PARAM_INPUT parameter type, the SQL_PARAM_OUTPUT parameter type, or the SQL_PARAM_INPUT_OUTPUT parameter type. Unless each parameter is bound correctly to the corresponding parameter type, unexpected result can occur from the CALL procedure statement processing. An example of incorrect parameter binding is when an input parameter is bound incorrectly with the SQL_PARAM_OUTPUT parameter type.
You can make a batch CALL statement for a stored procedure to reduce network flow between the database client and the database server. When a batch CALL statement is made for a stored procedure, CLI applications can avoid repeated calls to the SQLExecute() function or the SQLExecDirect() function for each different set of stored procedure arguments. The CLI application can make a batch CALL statement with the following additional steps:
1. Declare array variables for the stored procedure arguments. If the declared array variable is for INPUT argument, you must populate the array with required data. The stored procedure arguments that are of the INOUT type or the OUT type can also retrieve data in the form of array after the execution of the CALL statement:
```
SQLINTEGER param1[5] = {1,2,3,4,5};
SQLINTEGER param2[5] = {0,0,0,0,0};
```
2. Set the SQL_ATTR_PARAMSET_SIZE statement attribute to specify the array size to be used for the stored procedure arguments:
```
// specifying batching array size of 5
cliRC = SQLSetStmtAttr( hstmt,
                        SQL_ATTR_PARAMSET_SIZE,
                        (SQLPOINTER) 5,
                        SQL_IS_INTEGER );
```
3. Bind the array variable with the SQLBindParameter() function for each parameter of the CALL procedure statement:
```
cliRC = SQLBindParameter( hstmt,
                          1,
                          SQL_PARAM_INPUT,
                          SQL_C_LONG,
                          SQL_INTEGER,
                          0, 0,
                          param1, ...);

cliRC = SQLBindParameter( hstmt,
                          2,
                          SQL_PARAM_OUTPUT,
                          SQL_C_LONG,
                          SQL_INTEGER,
                          0, 0,
                          param2, ...);
```
Important: The following statement attributes are not supported by batch call statements:
- SQL_ATTR_INTERLEAVED_PUTDATA
- SQL_ATTR_INTERLEAVED_STREAM_PUTDATA
- SQL_ATTR_INTERLEAVED_GETDATA
- SQL_ATTR_INTERLEAVED_STREAM_GETDATA
- SQL_ATTR_STREAM_OUTPUTLOB_ON_CALL
Run the CALL procedure statement with the SQLExecDirect() function, or if the CALL procedure statement was prepared in step 4, use the SQLExecute() function.
Note:
- If an application or thread that calls a stored procedure is terminated before the stored procedure completes, execution of the stored procedure is also terminated. A stored procedure must contain logic to ensure that the database is in both a consistent and desirable state when the stored procedure is terminated.
- The CLI driver saves extra network traffic that is associated with sending the implicit COMMIT statement when the following conditions are met:
  - The connected database server is Db2® for z/OS® Version 11 in new function mode (NFM).
  - The autocommit behavior is enabled (SQL_ATTR_AUTOCOMMIT is ON) when the stored procedure is called.
  - Entire result-set that is returned from the stored procedure is contained within the single query block. The default query block size is 64K and it can be adjusted with the FET_BUF_SIZE db2cli.ini keyword or the FetchBufferSize IBM® data server driver configuration keyword.
Check the return code of the SQLExecDirect() function or the SQLExecute() function.
If the return code is SQL_SUCCESS_WITH_INFO or SQL_ERROR, use the SQLGetDiagRec() function and the SQLGetDiagField() function to determine the cause of the error.
If a batch CALL statement was issued for a stored procedure that returns multiple result-sets, the result-sets are available in order of parameter values that were specified in the parameter array. The SQLMoreResults() function, the SQLNextResult() function and the SQLFetch() function can be used to fetch the result-sets across multiple cursors.

Results

If a stored procedure ran successfully, any variables that are bound as OUT parameters contains data that the stored procedure passed back to the CLI application. The OUT parameter data can be retrieved with the SQLGetData() function. If the stored procedure returns one or more result sets through nonscrollable cursors, the result sets can be retrieved with the SQLFetch() function.

If a CLI application is unsure of the number or type of columns in a result-set that is returned by a stored procedure, the SQLNumResultCols(), SQLDescribeCol(), and SQLColAttribute() functions can be called (in listed order) on the result-set to determine this information.

If DATETYPE data is returned from the stored procedure, the returned DATETYPE data is in locale-dependent format. You can change the format of returned DATETYPE data by setting the DB2_SQLROUTINE_PREPOPTS registry variable to locale-independent value, such as ISO:

  db2set DB2_SQLROUTINE_PREPOPTS="DATETIME ISO"

Example

The following example makes a batch CALL statement for a stored procedure that has one INPUT parameter and one OUTPUT parameter:

CREATE PROCEDURE testproc (IN var1 INTEGER, OUT var2 INTEGER )
LANGUAGE SQL
BEGIN
   var2 = var1 * 10;
END

//For IN parameter var1
SQLINTEGER param1[5] = {1,2,3,4,5};

//For OUT parameter var2
SQLINTEGER param2[5] = {0,0,0,0,0};
...
cliRC = SQLPrepare( hstmt, "CALL testproc(?,?)", SQL_NTS );

cliRC = SQLBindParameter( hstmt,
                          1,
                          SQL_PARAM_INPUT,
                          SQL_C_LONG,
                          SQL_INTEGER,
                          0, 0,
                          param1, ...);

cliRC = SQLBindParameter( hstmt,
                          2,
                          SQL_PARAM_OUTPUT,
                          SQL_C_LONG,
                          SQL_INTEGER,
                          0, 0,
                          param2, ...);

// Specify batching array size of 5
cliRC = SQLSetStmtAttr( hstmt,
                        SQL_ATTR_PARAMSET_SIZE,
                        (SQLPOINTER) 5,
                        SQL_IS_INTEGER );

// The single SQLExecute() will be equivalent to making five CALL statements
cliRC = SQLExecute( hstmt );

// Print values of param2 used for OUTPUT type
for (i=0; i<5; i++)
{
    printf (“param2[%d] = %d\n”, i, param2[i]);
}

Following example makes a batch CALL statement for a stored procedure that returns multiple result-sets:

CREATE PROCEDURE testproc (IN var1 INTEGER)
LANGUAGE SQL
BEGIN
   INSERT INTO myTable VALUES (var1);

   DECLARE CURSOR c1 ... 
   DECLARE CURSOR c2 ...
   DECLARE CURSOR c3 ...

   OPEN c1 ...
   OPEN c2 ...
   OPEN c3 ...
END

SQLINTEGER param1[5] = {1,2,3};
...
cliRC = SQLPrepare( hstmt, "CALL testproc(?)", SQL_NTS );

cliRC = SQLBindParameter( hstmt,
                          1,
                          SQL_PARAM_INPUT,
                          SQL_C_LONG,
                          SQL_INTEGER,
                          0, 0,
                          param1, ...);

// Specify batching array size of 3
cliRC = SQLSetStmtAttr( hstmt,
                        SQL_ATTR_PARAMSET_SIZE,
                        (SQLPOINTER) 3,
                        SQL_IS_INTEGER );

// The single SQLExecute() will be equivalent to making five CALL statements
cliRC = SQLExecute( hstmt );

//Sequentially Fetch all result sets opened by cursors

//Start with first result-set
while ((cliRC = SQLFetch(hstmt)) != SQL_NO_DATA_FOUND) 
    { // Take some action }

//Done with 1st result set. Move on to next result set
cliRC=SQLMoreResults(hstmt);
 
//Start with 2nd result set
while ((cliRC = SQLFetch(hstmt)) != SQL_NO_DATA_FOUND) 
    { // Take some action }

//Done with 2nd result set. Move on to next result set
cliRC=SQLMoreResults(hstmt);

//Start with 3rd result set
while ( (cliRC = SQLFetch(hstmt)) != SQL_NO_DATA_FOUND)
    { // Take some action }
...