Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
RPG Stored Procedure bypass returning result set?
has no ratings.
Published: 04 Jan 2013
Revised: 23 Jan 2013 - 1852 days ago
Last viewed on: 18 Feb 2018 (3381 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.

RPG Stored Procedure bypass returning result set? Published by: Chris Proctor on 04 Jan 2013 view comments(3)

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

COMMENTS

(Sign in to Post a Comment)
Posted by: chrisp
Premium member *
Portland, OR
Comment on: RPG Stored Procedure bypass returning result set?
Posted: 5 years 1 months 14 days 5 hours 18 minutes ago

I think I need to add something to the stored procedure to bypass the error if returned, but I'm not sure what. Thanks, all!

 

Posted by: DaleB
Premium member *
Reading, PA
Comment on: RPG Stored Procedure bypass returning result set?
Posted: 5 years 1 months 11 days 7 hours 27 minutes ago

So, you're saying you want to return with no result set, which is not the same as a result set with no rows?

There are a couple things you could try. See SQL Programming, there's a section in Routines, Stored Procedures, Returning a completion status to the calling program.

  • You could use SIGNAL, and your caller would see that in SQLSTATE. SQL Reference gives guidelines on values you should use for SQLSTATE (generally, user-defined values, not those used by SQL).
  • You could add an extra INOUT parameter to indicate your bypass condition.
  • You could send an escape message (using QSQCALL). Your caller would deal with it like any other escape message.

Extra parameter sounds easy, but feels like extra overhead, since you should be testing SQLSTATE anyway.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: RPG Stored Procedure bypass returning result set?
Posted: 5 years 1 months 11 days 6 hours 56 minutes ago

Thanks for the input, Dale. What I thought of was, if the counter for number of status = '0' records comes back zero, set the parameter for the select of the status to some bogus value (I used 'X'). It wouldn't find any, so it returned a blank result set. This seemed to work great and obviously was very simple.

I will check out your suggestion, because I'm sure I'll run into other cases where it might be a better solution. Thanks again!

Chris