Re: Stored procedure parameters

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 05/18/2017
03:53:02 PM:

I’ve created the procedure using the RUNSQLSTM command and compiled
the program against it. I’ve successfully added a record by calling
the RPG program, so I know that works. So I tried to run the RPG
program again, knowing it would fail because the record already
existed and unique keys are specified. This is in fact what
happened, but I don’t seem to be getting any value in my
“in_message” field coming out of the procedure to denote the
failure. Is there some basic part of this scenario of creating a
procedure in this way that I’m forgetting to do? Any help is as
always appreciated. Thanks in advance!

I use this type of coding when I want to report hard errors. I
think if you run your stored procedure through debug you will find that
either your message statements are not being executed (because your
SQLSTATE variable is not being set as you think) or the procedure doesn't
even get to where you are checking for SQLSTATE.

Get Current Diagnostics Condition 1 CurState = Returned_SqlState;
If SubStr(CurState,1,2) <> '00' Then
Set Str1 = ErrPfx || 'SQL LIFO Company validation failed.'
|| ' (' || CurState || ')';
Signal SqlState '88W11' Set Message_Text = Str1;
End If;


Sincerely,

Dave Clark