Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Can an RPG Stored Procedure return 2 data structure result sets?
has no ratings.
Published: 05 Mar 2013
Revised: 06 Mar 2013 - 1511 days ago
Last viewed on: 24 Apr 2017 (3914 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Can an RPG Stored Procedure return 2 data structure result sets? Published by: Chris Proctor on 05 Mar 2013 view comments(2)

Good afternoon. I have a situation where I want to return 2 data structures as results sets and I found that someone on a msgboard said you could just by calling the "set result sets array" twice, once for each DS. I tried it and the stored procedure in iNav didn't like it. Maybe I just have something defined wrong. Anyway, here's what my code looks like:

 

// return result sets to stored procedure

execsqlsetresultsetsarray :SAPOrderHdr for :hrow rows;

execsqlsetresultsetsarray :SAPOrderDtl for :drow rows;

 

And this is what I have defined in the stored procedure:

 

CreateProcedure PollForSAPOrders

 

Resultset2

specific PollForSAPOrders

externalname spncisaor

languageRPGLE

parameterstyleSQL

 

Here was the iNav message:

 

SQL State: 0100C
Vendor Code: 466
Message: [SQL0466] 1 result sets are available from procedure POLLFORSAPORDERS in MM610DVL. Cause . . . . . :   Procedure POLLFORSAPORDERS in MM610DVL was called and has returned one or more result sets. Recovery  . . . :   None.

Statement ran successfully, with warnings   (180 ms)

Low-order nibble of the byte at array offset 70 is not valid.  Byte value: 40.

Processing ended because the highlighted statement did not complete successfully

 

Any suggestions would be greatly appreciated!  Cool  

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: neilrh
Premium member *
Jackson, MI
Comment on: Can an RPG Stored Procedure return 2 data structure result sets?
Posted: 4 years 1 months 19 days 10 hours 18 minutes ago

I've not read the manual lately, but from my previous learnings: you can return 1 DS result set and many file result sets.

Posted by: Ringer
Premium member *
Comment on: Can an RPG Stored Procedure return 2 data structure result sets?
Posted: 4 years 1 months 18 days 13 hours 16 minutes ago

I have found that yes, you can only return 1 array with in one program CALL. If you have multiple SET RESULT SETS, the last one wins, the others get canceled out.

BUT you can wrap an IF around those and control which one gets built/executed/returned on a given CALL, if that helps any.

Chris RInger