Calling stored procedures with array parameters from CLI applications

CLI applications can use a SQL CALL statement to call stored procedures with array parameters.

To call a stored procedure with array parameters:

1. 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.
2. Initialize the application variables that correspond to the IN, INOUT, and OUT parameters.
3. Create a CALL SQL statement.
   Remember: If the uncataloged stored procedure is called, ensure that it does not call any of the CLI schema functions. Calling CLI schema functions from uncataloged stored procedures are not supported. The CLI schema functions include following functions:

   • SQLColumns()
   • SQLColumnPrivileges()
   • SQLForeignKeys()
   • SQLPrimaryKeys()
   • SQLProcedures()
   • SQLProcedureColumns()
   • SQLSpecialColumns()
   • SQLStatistics()
   • SQLTables()
   • SQLTablePrivileges()
4. Optional: Prepare the CALL statement by calling the SQLPrepare() function.
5. Bind each parameter of the CALL procedure statement by calling the SQLBindParameter() function. Ensure that each parameter is bound correctly (to SQL_PARAM_INPUT, SQL_PARAM_OUTPUT, or SQL_PARAM_INPUT_OUTPUT), otherwise unexpected results can occur when the CALL procedure statement is made.
   Remember:

   • When you are calling stored procedures on Db2® for z/OS® servers, the call to stored procedures with array parameters are supported on the following Db2 for z/OS servers:
     • Db2 for z/OS Version 11 server in new function mode (NFM).
   • When you are calling stored procedures on Db2 for IBM® i servers, the call to stored procedures with array parameters are supported on Db2 for IBM i V7R1 and later servers.
6. For each array parameter, use the SQLSetDescField() function with the SQL_DESC_CARDINALITY and SQL_DESC_CARDINALITY_PTR arguments to set the maximum cardinality of the array value and pointer to a variable that contains the cardinality of a parameter.
   You must specify appropriate descriptor handle parameter for the SQLSetDescField() function that is based on stored procedure argument type:

   • Implementation parameter descriptor handle (hIPD) for INPUT stored procedure argument.
   • Application parameter descriptor handle (hAPD) for OUTPUT stored procedure argument.
   • Both hIPD and hAPD for INOUT stored procedure argument.

   cliRC = SQLSetDescField( hIPD,
                            1,
                            SQL_DESC_CARDINALITY,
                            (SQLPOINTER) 5,
                            SQL_IS_SMALLINT);
   
   cliRC = SQLSetDescField( hAPD,
                            1,
                            SQL_DESC_CARDINALITY_PTR,
                            &actInCardinality,
                            SQL_IS_SMALLINT);

7. Run the CALL procedure statement with the SQLExecDirect() function, or if the CALL procedure statement was prepared in step 4, run the SQLExecute() function.
   Note:

   • If an application that called a stored procedure is terminated before the stored procedure completes, execution of the stored procedure is also terminated. It is important that a stored procedure contains a logic to ensure that the database is in both a consistent and desirable state when the stored procedure is terminated prematurely.
   • The CLI driver saves extra network traffic that is associated with sending the implicit COMMIT statement when the following conditions are met:
     • When the application is connected to the Db2 for z/OS Version 11 server 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.
8. 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 the stored procedure ran successfully, any variables that are bound as OUT parameters contains data that the stored procedure passed back to the CLI application. If the stored procedure returns one or more result sets through non-scrollable cursors, the result sets can be retrieved with the SQLFetch() function.

CREATE TYPE int_array AS INTEGER array[5];
CREATE PROCEDURE array_out (IN var1 int_array, OUT var2 int_array)
LANGUAGE SQL
BEGIN
  FOR v AS SELECT val, idx FROM UNNEST(var1) WITH ORDINALITY AS T(val, idx) ORDER BY idx ASC
  DO
    SET var2[idx] = val - 1;
  END FOR;
END

SQLINTEGER param1[5] = {1,2,3,4,5};
SQLINTEGER param2[5];
SQLINTEGER actInCardinality = 5;
SQLINTEGER actOutCardinality = 0;

...

cliRC = SQLPrepare(hstmt,
  "CALL ARRAY_OUT (?, ?)",
  SQL_NTS);
cliRC = SQLBindParameter( hstmt,
                          1,
                          SQL_PARAM_INPUT,
                          SQL_C_LONG,
                          SQL_INTEGER,
                          4,
                          0,
                          param1,
                          4,
                          NULL);

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

 cliRC = SQLSetDescField( hIPD,
                         1,
                         SQL_DESC_CARDINALITY,
                         (SQLPOINTER) 5,
                         SQL_IS_SMALLINT);

 cliRC = SQLSetDescField( hAPD,
                         2,
                         SQL_DESC_CARDINALITY,
                         (SQLPOINTER) 5,
                         SQL_IS_SMALLINT);
 

cliRC = SQLSetDescField( hAPD,
                         1,
                         SQL_DESC_CARDINALITY_PTR,
                         &actInCardinality,
                         SQL_IS_SMALLINT);

cliRC = SQLSetDescField( hAPD,
                         2,
                         SQL_DESC_CARDINALITY_PTR,
                         &actOutCardinality,
                         SQL_IS_SMALLINT);  

clciRC = SQLExecute(hstmt);