IBM DB2 for i V7.1: HLL Programs Can Now Easily Process Result Sets From Stored Procedures

Published on 30 November 2010

View in HTML

Share this page:

IBM Form #: TIPS0801

Authors: Sandra Cabral and Hernando Bedoya

Abstract

Stored Procedures are used across different platforms, operating systems, and diversified computing environments. Stored Procedures are great programming resources that can be used to distribute the logic of your application and, in a client server environment, they can improve performance tremendously by reducing the information traffic across a network.

In IBM i V5.4 and V6.1, traditional programming languages, such as RPG and COBOL, did not have the ability to retrieve result sets returned by a Stored Procedure unless they used the SQL Call Level Interface for the Stored Procedure call. With the release of IBM i V7.1 in April 2010, several enhancements were added to IBM DB2 i for V7.1. One of these enhancements is that the SQL for DB2 for i V7.1 now supports two new SQL statements: ASSOCIATE LOCATOR and ALLOCATE CURSOR . These two SQL statements allow RPG and COBOL programs to easily consume result sets coming from Stored Procedures.

In this TechNote, we show an RPG program that uses ASSOCIATE LOCATOR and ALLOCATE CURSOR to consume the result set returned by the following SQL Stored Procedure:

CREATE PROCEDURE QGPL/MyProc (IN STD_ID_IN CHAR(3))DYNAMIC RESULT SETS 1LANGUAGE SQL SPECIFIC QGPL/MyProc BEGIN DECLARE C1 CURSOR FOR SELECT STD_ID, a.CLASS_ID, CLASS_DESC, TEACHER_ID, GRADE
FROM
QGPL/STUDENT a,
QGPL/CLASS b
WHERE STD_ID = STD_ID_IN and a.CLASS_ID = b.CLASS_ID;
OPEN C1; END ;

Here are the tables referenced by the Stored Procedure:

CREATE TABLE QGPL/STUDENT (

STD_ID CHAR(3),
STD_NAME CHAR(20),
CLASS_ID CHAR(3),
GRADE CHAR(1)) ;

CREATE TABLE QGPL/CLASS (
CLASS_ID CHAR(3),
CLASS_DESC CHAR(20),
TEACHER_ID CHAR(3));

RPG program using the ASSOCIATE LOCATOR and ALLOCATE CURSOR SQL statements

The following RPG program calls the Stored Procedure above, opens the result set as a cursor, and fetches the rows from the cursor and places them into a data structure array:

D rTranscript S SQLTYPE(RESULT_SET_LOCATOR)

// The data structure is used for fetching data from the cursor

D* SQL Pre-compiler will convert this definition to 20I 0
D
D* Data fields:
D Std_Id S 3
D Class_Id S 3
D Class_Desc S 20
D Teacher_Id S 3
D Grade S 1
C
/Free
// SQL diagnostics area replaces the SQLCA:
EXEC SQL SET OPTION SQLCA = *NO;

// Invoke procedure that has 1 result set

Exec SQL CALL MYPROC('S01'); // Stored procedure

// Did the procedure return a result set?
If SQLCODE <> 466;
Dsply 'No data was found.';
*INLR = *Off;
Return;
EndIf;

// ALLOCATE CURSOR is used to link the result set with a cursor.
Exec SQL
ASSOCIATE RESULT SET LOCATOR
(:rTranscript)
WITH PROCEDURE MyProc;

// ALLOCATE CURSOR statement is used for each result set. The cursor names can be different from the cursor names in the stored procedure.

Exec SQL
ALLOCATE Transcript CURSOR
FOR RESULT SET :rTranscript;

// Fetch record and put values into host variables:
EXEC SQL FETCH Transcript INTO :Std_Id, :Class_Id, :Class_Desc,
:Teacher_Id, :Grade;

DoU SQLCode = 100; // Process result set:
Dsply Class_Desc;
EXEC SQL FETCH Transcript INTO :Std_Id, :Class_Id, :Class_Desc,
:Teacher_Id, :Grade;
EndDo;

// Close cursor
Exec SQL CLOSE Transcript;

*INLR = *Off;
Return;
/End-Free

Special Notices

The material included in this document is in DRAFT form and is provided 'as is' without warranty of any kind. IBM is not responsible for the accuracy or completeness of the material, and may update the document at any time. The final, published document may not include any, or all, of the material included herein. Client assumes all risks associated with Client's use of this document.