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 - 1520 days ago
Last viewed on: 22 Mar 2017 (2677 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)

I'm am working on my first RPG stored procedure thanks to Chris Ringer :-) and I have a situation where I want to check for a couple of conditions before allowing the cursor to open and return the result set. Well, if those conditions aren't met I want to bypass the processing.

Since I'm passing the return of the result set I'm getting an SQLSTATE of 38501, stating that there was a problem with the program.

Is there a way of handling this? Thanks for any input.

Chris

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: 4 years 2 months 18 days 20 hours 11 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: 4 years 2 months 15 days 22 hours 20 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: 4 years 2 months 15 days 21 hours 50 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