Re: Returning Multiple Result Sets from RPG with External Stored Procedure

To me, a syntax error as the outcome was an obvious effect. IMO the error in the statement would have been even more obvious, before an attempt to copy\paste, had the comma been placed in what IMO makes the apparent\attempted syntax more conspicuous. I believe *formatting* helps make an error more obvious, due to inconsistencies being visibly obvious vs subtly obfuscated; the comma tucked tightly at the end of a line is easier [for me] to miss than a comma preceding every repeated line [since\after the first]. The given statement, reformatted, along with comments to call-out the difference from one line to the next:

Exec SQL Set Result Sets with return to client
Array :allOsDs for :allIndex Rows /* conspicuously, no comma */
, Array :favOsDs for :allIndex Rows /* comma prefixes the line */
Array :dptOsDs for :allIndex Rows /* conspicuously, no comma */
;

The syntax diagram for that statement shows the comma is required for each returned set:
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference -> Statements
_i SET RESULT SETS i_
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzhsetrs.htm
"The SET RESULT SETS statement specifies the result sets that can be returned from a procedure. ..."

Thus the missing comma needs to be added:

Exec SQL Set Result Sets with return to client
Array :allOsDs for :allIndex Rows
, Array :favOsDs for :allIndex Rows
, Array :dptOsDs for :allIndex Rows
;

Regards, Chuck

On 03 Oct 2013 11:43, Ricky Thompson wrote:

It's saying that syntax isn't correct. I'm on v7r1.

On 10/3/2013 1:02 PM, Birgitta Hauser wrote:

Did you try returning all result sets within a single SET RESULT
SET statement?

Exec SQL Set Result Sets with return to client
Array :allOsDs for :allIndex Rows,
Array :favOsDs for :allIndex Rows
Array :dptOsDs for :allIndex Rows;

BTW cursors are returned as result set by opening them and not
closing them before the procedure is left (ends).