Stored procedure parameters

Hello all,

I’m working on a fairly simple stored procedure but having a small issue. This procedure is built to add a record to a table. I’m also trying to get it to check the SQLSTATE and have that value come out of the procedure so that I can know if problems occurred with the adding of the record. Here is the procedure itself:

CREATE OR REPLACE PROCEDURE ADDSRVZIP(
IN SRVCTRNUM NUMERIC(6),
IN SRVCTRZIP CHAR(5),
OUT CHANGE_STATUS CHAR(1),
OUT CHANGE_MESSAGE CHAR(64)
)
LANGUAGE SQL
MODIFIES SQL DATA
DYNAMIC RESULT SETS 0

BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

INSERT INTO SRVZIPP
VALUES(SRVCTRNUM, SRVCTRZIP);

IF (SQLSTATE <> '00000') THEN
SET CHANGE_STATUS = 1;
SET CHANGE_MESSAGE = 'INSERT FAILURE. SQLCODE: '||SQLCODE||'.';
RETURN;
END IF;

SET CHANGE_STATUS = 0;
SET CHANGE_MESSAGE = 'ZIP CODE WAS INSERTED!';
RETURN;


END

Here is the program that I’m using to call the procedure for testing purposes:

‚ **************************************************************************
‚ *
‚ * Program Name....:
‚ * Written By......:
‚ * Date Created....:
‚ *
‚ * Program Function:
‚ *
‚ *
‚ *
‚ *ƒ Modification Log
‚ *
‚ * Date | Ref | Programmer | Description of Change
‚ * -----------+-------+-------------------+--------------------------------
‚ * | | |
‚ *
‚ **************************************************************************

d in_srvctr s 6s 0 inz(202404)
d in_zip s 5a inz('12345')
d in_status s 1a inz
d in_message s 64a inz

‚ **************************************************************************
‚ *ƒ Mainline processing
‚ **************************************************************************

/Free
KB01 c/exec sql
KB01 c+ set option
KB01 c+ commit = *none,
KB01 c+ closqlcsr = *endmod
KB01 c/end-exec
C/Exec SQL
C+ Call ADDSRVZIP(:in_srvctr,
C+ :in_zip,
C+ :in_status,
C+ :in_message)
C/End-Exec

*inlr = *on;

/End-Free

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!



Bill Howie


AOMobile - AmTrust Financial Services, Inc. Download on the App Store℠<https://itunes.apple.com/us/app/aomobile/id626262396?mt=8&uo=4> | Get it on Google Play™<https://play.google.com/store/apps/details?id=com.amtrust.aomobile>
- Apple and the Apple logo are trademarks of Apple Inc., registered in the U.S. and other countries. App Store is a service mark of Apple Inc.
- Android is a trademark of Google Inc.

________________________________
Confidentiality Notice: This email message is intended only for the individual or entity to which it is addressed. This email may contain information that is proprietary or privileged, confidential and exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you received this email by accident, please notify the sender immediately and destroy this email and all copies of it.