Calling a DB2 Stored Procedure Using SQL Passthru

How to:

DB2 stored procedures are supported using SQL Passthru. These procedures need to be developed within DB2 using the CREATE PROCEDURE command.

The adapter supports stored procedures with IN, OUT, and INOUT parameters.

The output parameter values that are returned by stored procedures are available as result sets. These values form a single-row result set that is transferred to the client after all other result sets are returned by the invoked stored procedure. The names of the output parameters (if available) become the column titles of that result set.

Note that only the output parameters (and the returned value) referenced in the invocation string are returned to the client. As a result, users have full control over which output parameters have their values displayed.

The server supports invocation of stored procedures written according to the rules of the underlying DBMS. Note that the examples shown in this section are SQL-based. See the DBMS documentation for rules, languages, and additional programming examples.


Top of page

x
Syntax: How to Invoke a Stored Procedure
SQL DB2 EX procname [parameter_specification1] 
[,parameter_specification2]...
END

where:

DB2

Is the ENGINE suffix for DB2.

procname

Is the name of the stored procedure. It is the fully or partially qualified name of the stored procedure in the native RDBMS syntax.

You can employ either SQL or SYS naming conventions to control the separator character used for interpreting multipart names, as described in Setting Naming Conventions.

parameter_specification

IN, OUT, and INOUT parameters are supported. Use the variation required by the stored procedure:

IN

Is a literal (for example, 125, 3.14, 'abcde'). You can use reserved words as input. Unlike character literals, reserved words are not enclosed in quotation marks (for example, NULL). Input is required.

OUT

Is represented as a question mark (?). You can control whether output is passed to an application by including or omitting this parameter. If omitted, this entry will be an empty string (containing 0 characters).

INOUT

Consists of a question mark (?) for output and a literal for input, separated by a slash: /. (For example: ?/125, ?/3.14, ?/'abcde'.) The out value can be an empty string (containing 0 characters).



Example: Invoking a Stored Procedure

Note that this sample employs the SQL naming convention, where the "." character is used as the separator in multipart table names. If your site uses the SYS[TEM] naming convention (typical in IBM i environments), the "/" character is used as a separator in multipart table names. In this case, adjust the separator character as needed to conform to the SYS naming convention. For details, see Setting Naming Conventions.

In this example, a user invokes a stored procedure, edaqa.test_proc01, supplies input values for parameters 1, 3, 5 and 7, and requests the returned value of the stored procedure, as well as output values for parameters 2 and 3.

Note that parameters 4 and 6 are omitted; the stored procedure will use their default values, as specified at the time of its creation.

SQL DB2 EX edaqa.test_proc01 125,?,?/3.14,,'abc',,'xyz'
END


Example: Sample Stored Procedure

Note that this sample employs the SQL naming convention, where the "." character is used as the separator in multipart table names. If your site uses the SYS[TEM] naming convention (typical in IBM i environments), the "/" character is used as a separator in multipart table names. In this case, adjust the separator character as needed to conform to the SYS naming convention. For details, see Setting Naming Conventions.

This stored procedure uses out and inout parameters:

CREATE PROCEDURE EDAQA.PROCP3 (   OUT chSQLSTATE_OUT   CHAR(5),
                                  OUT intSQLCODE_OUT   INT,
                                  INOUT l_name char(20),
                                  INOUT f_name char(20))
    RESULT SETS 1
    LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
    -- Declare variable
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE SQLCODE INT DEFAULT 0;
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
        SELECT
           EDAQA.NF29005.SSN5 AS SSN5,
           EDAQA.NF29005.LAST_NAME5 AS LAST_NAME5,
           EDAQA.NF29005.FIRST_NAME5 AS FIRST_NAME5,
           EDAQA.NF29005.BIRTHDATE5 AS BIRTHDATE5,
           EDAQA.NF29005.SEX5 AS SEX5
        FROM
           EDAQA.NF29005
        WHERE
           (
             ( EDAQA.NF29005.LAST_NAME5 =  l_name )
        AND
             ( EDAQA.NF29005.FIRST_NAME5 = f_name )
           );
     -- Cursor left open for client application
    OPEN cursor1;
    SET chSQLSTATE_OUT = SQLSTATE;
    SET intSQLCODE_OUT = SQLCODE;
    SET l_name = 'this is first name';
    SET f_name = 'this is last name';
END P1   @

iWay Software