DB2 Version 10.1 for Linux, UNIX, and Windows

Calling stored procedures from CLI applications

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:

  1. 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.
  2. Initialize the IN, INOUT, and OUT parameter application variables.
  3. 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.
  4. Optional: Prepare the CALL statement by calling SQLPrepare().
  5. 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.
  6. 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.
  7. 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