CLI applications
can use a SQL CALL statement to call stored procedures with array
parameters.
Before you begin
Ensure that you have the CLI environment
initialized and the database connection established.
Procedure
To call a stored procedure with array parameters:
- 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.
- 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()
- 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 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.
- 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);
- 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.
- 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.
Example
Following example calls a SQL stored procedure that has IN
parameter of INTEGER array: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
Following CLI application
example calls the stored procedure with an array parameter: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);