Retrieving a Result Set from a Stored Procedure Call on IBM i (Magic xpa 3.x)

Objective

Running the Automatic Program Generator (APG) on DSQL creates arguments only.

How can we configure a Magic xpa Direct SQL program to both send the required arguments to the stored procedure and the result set’s returned fields?

Solution

Combine two programs, one calling the stored procedure passing correct parameters and the other working with the correct result set.

Implementation Steps

The implementation consists of three steps:

1. Creating the Direct SQL program that sends parameters.

2. Creating a program that handles the result set.

3. Combining the two programs into one solution.

Stage 1 – Creating the program that sends parameters

For this sample we will use a table definition that is the same table used in the stored procedure (the expected result set).

The stored procedure is a simple one for this sample:

CREATE PROCEDURE MGCOLLECT/SampleSP (IN p1 CHAR(12))

RESULT SET 1

LANGUAGE SQL

BEGIN

DECLARE C1 CURSOR WITH RETURN FOR

SELECT * FROM MGCOLLECT/KB_Sample WHERE MyString > P1;

OPEN C1;

END;

• Create a program calling the stored procedure using the APG button to generate the required arguments.

Note: After clicking the APG button the input parameter is discarded.

Stage 2 – Creating a Program that Handles the Result Set

1. Create a program that handles the expected result set.

2. Write the query into a DSQL task and click the APG button.

Stage 3 – Combining the Two programs into One Solution

• Copy the program from Stage 1 and make the following modifications:

1. 1. Change the virtual to a parameter – this is the variable sent to the procedure.

   2. In the DSQL window, remove the output parameter and use the variable as an input parameter.

   3. Add the output parameters, making sure to copy the virtual variables exactly as they appear in the program from Stage 2. Add these variables as output parameters to the DSQL window.
      
      Do not click the APG button again.