IBM Support

Call SQL Stored Procedure with Dynamic Results Set from SQLRPGLE

How To


Summary

The following is an example of creating a simple stored procedure that returns a DYNAMIC RESULT SETS 1. Then a sample of SQLRPGLE which can be used to call this stored procedure, returning the results to the program.

Additional Information

The following example is provided in its current condition ("as-is"), IBM does not promise that it will be free of errors, accurate, or suitable for any particular purpose.

SQL Stored Procedure: 
CREATE OR REPLACE PROCEDURE yourlib.GET_QIWS_REC () 
    LANGUAGE SQL 
    DYNAMIC RESULT SETS 1 
BEGIN 
    DECLARE C1 CURSOR FOR 
        Select cusnum, lstnam from qiws.qcustcdt for read only;
    OPEN C1;
    RETURN; 
END ;
SQLRPGLE program source: 
**free                                                                   
exec sql include sqlca ;                                                 
                                                                         
dcl-s rsQIWS sqltype(result_set_locator) ;                               
dcl-s MyStmt char(500) ;                                                 
dcl-s MyProc char(128) ;                                                 
                                                                         
dcl-ds dsQIWS qualified DIM(20);                                         
  custnum zoned(6:0);                                                    
  lastname char(10);                                                     
end-ds;                                                                  
                                                                         
MyProc = 'yourlib/GET_QIWS_REC' ;                                           
MyStmt = 'CALL ' + %trim(MyProc) ;                                       
                                                                         
exec sql prepare S1 from :MyStmt ;                                       
dsply ('Prepared statement returned SQLCODE ' + %char(sqlcode) + '.') ;  

exec sql execute S1 ;                                                    
dsply ('CALL statement returned SQLCODE ' + %char(sqlcode) + '.') ;      
                                                                            
exec sql ASSOCIATE RESULT SET LOCATOR (:rsQIWS) WITH PROCEDURE :MyProc;                                              
dsply ('Associate result set returned SQLCODE ' + %char(sqlcode) + '.') ;   
                                                                            
exec sql ALLOCATE QIWSCurs CURSOR FOR RESULT SET :rsQIWS;  
dsply ('Allocate cursor returned SQLCODE ' + %char(sqlcode) + '.') ;        
                                                                            
exec sql FETCH QIWSCurs FOR 20 ROWS INTO :dsQIWS;        

exec sql CLOSE QIWSCurs;                                                  
                                                                            
       dump(a);                                                             
   *inlr = *on;

Review the program dump spool file that is created.  Review the contents of the dsQIWS data structure in the dump, it will contain the data from the QIWS/QCUSTCDT file returned in the result set.

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001iICAAY","label":"IBM i Db2-\u003EStored Procedures"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"6.1.0;7.1.0;7.2.0;7.3.0;7.4.0;7.5.0;7.6.0"}]

Document Information

Modified date:
09 October 2025

UID

ibm17247591

Page Feedback