Re: Calling stored procedure from RPG

[CRPence <CRPbottle@xxxxxxxxx>]

  The IBM DB2 for i 7.1 SQL provides for the ability of "HLL Programs" 
to "Easily Process Result Sets From Stored Procedures":
http://www.redbooks.ibm.com/abstracts/tips0801.html?Open
"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.

For related information about this topic, refer to the following IBM 
Redbooks publication:
IBM i 7.1 Technical Overview, SG24-7858-00
http://www.redbooks.ibm.com/Redbooks.nsf/RedbookAbstracts/SG247858.html
"

  The above was just the first result from a web search.  An InfoCenter 
link:
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafyreceiveresult.htm

  But with regard to the query by the OP, updated [inout and output] 
"parameter values" for invoked stored procedures are not the equivalent 
of a "result set" that additionally could be returned to the caller or 
client.  And of course a UDTF, a VIEW, or a GLOBAL TEMPORARY TABLE might 
be a possible alternative to a "result set" for use by some applications 
where specific benefits of an actual result set are not a requirement per:

http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafyresultsets.htm
"Returning result sets from stored procedures

In addition to returning output parameters, a stored procedure can 
return a result set (that is, a result table associated with a cursor 
opened in the stored procedure) to the application that issues the CALL 
statement. The application can then issue fetch requests to read the 
rows of the result set cursor.  ...

There are many cases where opening the cursor in a stored procedure and 
returning its result set provides advantages over opening the cursor 
directly in the application. ...
"

Regards, Chuck

On 06-Nov-2011 13:19 , Mike Cunningham wrote:
> Did IBM ever add the ability for embedded RPG to read a result set
> that was returned from another RPG program that was defined as an
> external procedure? Without using the CLI interface.
>
> CRPence on Sunday, November 06, 2011 3:01 PM  wrote:
> > On 06-Nov-2011 11:16 , Albert York wrote:
> > > I need to call a stored procedure that has two input parameters
> > > and four output parameters.
> > >
> > > Can anyone give me an example of how that would be coded in RPG?
> >
> > In a SQLRPGLE source, something like the following might be coded:
> >
> >      Exec SQL
> >          call myStoredProc  (:i1, :i2, :o1, :o2, :o3, :o4) ;