CLI applications invoke stored procedures by executing
the CALL procedure SQL statement. This topic describes how to call
stored procedures from CLI applications.
Before you begin
Before calling a stored procedure, ensure that you have initialized
your CLI application.
About this task
If the stored procedure being called is uncataloged, ensure
that it does not call any of the CLI schema functions. Calling CLI
schema functions from uncataloged stored procedures is not supported.
The
CLI schema functions are: SQLColumns(), SQLColumnPrivileges(), SQLForeignKeys(), SQLPrimaryKeys(), SQLProcedureColumns(), SQLProcedures(), SQLSpecialColumns(), SQLStatistics(), SQLTables(),
and SQLTablePrivileges().
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 the application variable data types and lengths match
the data types and lengths of the arguments in the stored procedure
signature. CLI supports
calling stored procedures with all SQL types as parameter markers.
- Initialize the IN, INOUT, and OUT parameter application
variables.
- Issue the CALL SQL statement. For example:
SQLCHAR *stmt = (SQLCHAR *)"CALL OUT_LANGUAGE (?)";
or
SQLCHAR *stmt = (SQLCHAR *)"CALL OUT_LANGUAGE (:language)";
Remember: To use named parameter markers
(for example, :language), you must explicitly enable
named parameter processing by setting the EnableNamedParameterSupport configuration
keyword to TRUE.
For optimal
performance, applications should use parameter markers for stored
procedure arguments in the CALL procedure string, and then bind the
host variables to those parameter markers. If inbound stored procedure
arguments must be specified as string literals rather than parameter
markers, however, include the ODBC call escape clause delimiters {
} in the CALL procedure statement. For example:
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 only be specified as IN mode stored
procedure arguments. INOUT and OUT mode stored procedure arguments
must still be specified using parameter markers.
- Optional: Prepare the CALL statement by calling SQLPrepare().
- Bind each parameter of the CALL procedure statement by
calling SQLBindParameter().
Note: Ensure each parameter is bound correctly (to SQL_PARAM_INPUT,
SQL_PARAM_OUTPUT, or SQL_PARAM_INPUT_OUTPUT), otherwise unexpected
results could occur when the CALL procedure statement is executed.
This would happen, for example, if an input parameter was incorrectly
bound with an InputOutputType of SQL_PARAM_OUTPUT.
Note: The CALL
procedure statement does not support an array input for a parameter
marker using the SQL_ATTR_PARAMSET_SIZE attribute.
- Execute the CALL procedure statement using SQLExecDirect(),
or if the CALL procedure statement was prepared in step 4, SQLExecute().
Note: If an application or thread that has invoked a
stored procedure is terminated before the stored procedure completes,
execution of the stored procedure will also be terminated. It is
important that a stored procedure contain logic to ensure that the
database is in both a consistent and desirable state if the stored
procedure is terminated prematurely.
- Check the return code of SQLExecDirect() or SQLExecute() when
the function has returned to determine if any errors occurred during
execution of either the CALL procedure statement or the stored procedure. If the return code is SQL_SUCCESS_WITH_INFO or SQL_ERROR, use
the CLI diagnostic functions SQLGetDiagRec() and SQLGetDiagField() to
determine why the error occurred.
If a stored procedure has executed
successfully, any variables bound as OUT parameters may contain data
that the stored procedure has passed back to the CLI application.
If applicable, the stored procedure may also return one or more result
sets through non-scrollable cursors. CLI applications should process
stored procedure result sets as they would process result sets generated
by executing SELECT statements.
Note: If
a CLI application is unsure of the number or type of columns in a
result set returned by a stored procedure, the SQLNumResultCols(), SQLDescribeCol(),
and SQLColAttribute() functions
can be called (in this order) on the result set to determine this
information.
Results
Once you have executed the CALL statement, you can retrieve
result sets from the stored procedure if applicable.
Note:
The
numeric month and day parts of a DATETYPE data type value will appear
to be reversed in procedure result sets that are returned to DB2® CLI applications if the values
are not returned in ISO format. For example, this can happen if a
local format is used instead. To ensure that DATETYPE data type value
information is correctly interpreted by a client application, the
procedures should be bound to the database with a locale-independent
DATETIME format such as ISO. For example:
db2set DB2_SQLROUTINE_PREPOPTS="DATETIME ISO"
Note:
CLI packages
are automatically bound to databases when the databases are created
or upgraded.
- Resultsets from Anonymous blocks
CLI will be prepare for resultsets whenever a SQL statement
that starts with BEGIN, but not BEGIN COMPOUND, is sent by the application.
CLI will interpret the returned cursor from the server and allow application
to retrieve the resultsets.
Example 1: Using SQLExecDirect
opt caller on
opt echo on
quickc 1 1 sample
SQLAllocStmt 1 1
getmem 1 1 SQL_C_LONG
SQLExecDirect 1 "drop table t1" -3
SQLExecDirect 1 "create table t1 (c1 int)" -3
SQLExecDirect 1 "insert into t1 values (10)" -3
SQLExecDirect 1 "insert into t1 values (20)" -3
SQLExecDirect 1 "insert into t1 values (30)" -3
SQLExecDirect 1 "begin declare c1 cursor with return to client with hold
for select c1 from t1; end" -3
SQLBindCol 1 1 sql_c_long 1
FetchAll 1
SQLFreeStmt 1 SQL_DROP
SQLTransact 1 1 SQL_COMMIT
killenv 1