DB2 Version 10.1 for Linux, UNIX, and Windows

Calling stored procedures in C and C++ embedded SQL applications

You can use the anonymous blocks or the EXEC SQL CALL statements to call stored procedures in C and C++ embedded SQL applications.

Calling stored procedures by using the EXEC SQL CALL statement

DB2® supports the use of input, output, and input and output parameters in SQL procedures. The IN, OUT, and INOUT keywords in the CREATE PROCEDURE statement indicate the mode or intended use of the parameter. IN and OUT parameters are passed by value, and INOUT parameters are passed by reference.

When working with C and C++ applications, you can call an INOUT_PARAM stored procedure by using the following statement:

      EXEC SQL CALL INOUT_PARAM(:inout_median:medianind, :out_sqlcode:codeind,
                          :out_buffer:bufferind);

In the previous statement inout_median, out_sqlcode, and out_buffer are host variables and medianind, codeind, and bufferind are null indicator variables.

Note: You can also call stored procedures dynamically by preparing a CALL statement.

Calling stored procedures by using the anonymous block

C and C++ embedded SQL applications can call stored procedures by using an anonymous block when the PRECOMPILE option COMPATIBILITY_MODE is set to ORA.

Read syntax diagramSkip visual syntax diagram
>>-EXECUTE BEGIN--procedure-name-------------------------------->

>--+----------------------------+--END; END-EXEC;--------------><
   |    .-,----------------.    |                   
   |    V                  |    |                   
   '-(------| argument |---+--)-'                   

argument

|--+--------------------+--+-expression-+-----------------------|
   '-parameter-name--=>-'  +-DEFAULT----+   
                           '-NULL-------'   
Parameter description
procedure-name
A name of the procedure, which is described in the catalogue, that you want to call.
argument description
parameter-name
The name of the parameter that the argument is assigned to. When you assign an argument to a paramater by name, all the arguments that follow the (parameter) must be assigned by name.

You can only specify a named argument once (implicitly or explicitly).

Named arguments are not supported on a call to an uncataloged procedure.

expression or DEFAULT or NULL
Each specification of expression, the DEFAULT keyword, or the NULL keyword is an argument of the CALL. The nth unnamed argument of the CALL statement corresponds to the nth parameter that is defined in the CREATE PROCEDURE statement for the procedure.

Named arguments correspond to the same named parameter, regardless of the order in which arguments are specified.

The DEFAULT keyword is used in the CREATE PROCEDURE statement if you have specified it; otherwise the null value is used as the default.

If the NULL keyword is specified, the null value is passed as the parameter value.

Each argument of the CALL statement must be compatible with the corresponding parameter in the procedure definition as follows:
  • IN parameter
    • The argument must be assignable to the parameter.
    • The assignment of a string argument uses the storage assignment rules.
  • OUT parameter
    • The argument must be a single variable or parameter marker.
    • The argument must be assignable to the parameter.
    • The assignment of a string argument uses the retrieval assignment rules.
  • INOUT parameter
    • The argument must be a single variable or parameter marker.
    • The argument must be assignable to the parameter.
    • The assignment of a string argument uses the storage assignment rules on invocation and the retrieval assignment rules on return.
Related concepts:
Routines: Procedures
Related reference:
SQL PL samples