RPG Stored Procedure bypass returning result set?

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 - 4109 days ago
Last viewed on: 23 Apr 2024 (5184 views)

Main URL:

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

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: 11 years 3 months 19 days 17 hours 22 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: 11 years 3 months 16 days 19 hours 31 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: 11 years 3 months 16 days 19 hours 1 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