I have created a SQL stored procedure that takes 2 input parameters and 1 output parameter means a return value. The script of the SQL stored procedure is as follows:
CREATE OR REPLACE PROCEDURE STORED33A(
IN P_NAME CHAR(20),
IN P_GENDER CHAR(1),
OUT SUCCESS CHAR(1)
)
SPECIFIC STORED33A
BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT ' ';
DECLARE SQLCODE INTEGER DEFAULT 0;
INSERT INTO PF8_D(NAME,GENDER) VALUES(P_NAME,P_GENDER);
IF SQLCODE = 0 THEN
SET SUCCESS = '1';
ELSE
SET SUCCESS = '0';
END IF;
END I have created it using RUNSQLSTM statement that creates a *PGM object of attribute CLE. However, when I call that SQL stored procedure from STRSQL on IBM i to test whether it is working fine or not I get the error SQL0469. This is the call statement to execute the SQL stored procedure where I passed NULL for the OUT parameter.
call testlib.STORED33A('name1','M',NULL)
Below is the error message
Message ID . . . . . . : SQL0469 Severity . . . . . . . : 30 Message type . . . . . : Diagnostic Message . . . . : IN, OUT, or INOUT not valid for parameter 3 in procedure STORED33A in TESTLIB. Cause . . . . . : The IN, INOUT, or OUT attribute specified for parameter 3 when the procedure was defined is not valid. The parameter name is SUCCESS. One of the following errors occurred: — A default cannot be specified for an OUT parameter. — The attribute is not consistent with the parameter on the CALL statement. If the parameter was declared INOUT or OUT, the parameter on the CALL statement must be specified as a host variable or a global variable. — The attribute was specified as INOUT or OUT and REXX was specified as the language. The attribute must be IN if REXX is specified. Recovery . . . : Either change the attribute of the parameter on the DECLARE PROCEDURE, CREATE PROCEDURE, or ALTER PROCEDURE statement or change the parameter. Try the request again. I then tried passing a blank value for the OUT parameter but got the same SQL0469 message id.call testlib.STORED33A('name1', 'M',' ')
what is wrong here in the call statement of the SQL procedure that contains the INOUT parameter?
When we
callaSQL stored procedureusing aconstant valueinstead of aparameter marker(?)sqlcode -469is returned.You can
callaSQL stored procedureby passing aparameter markerin anINOUT parameterlike this.call testlib.STORED33A('name1','M','?')However, you cannot even call using a parameters marker in the
STRSQLsession on an IBM i machine as theUse of parameter marker, NULL, or UNKNOWN is not valid.However, You can call it by passing any value to the
OUTorINOUT parameterfrom theRun SQL scriptlikecall testlib.STORED33A('name1','M',NULL);call testlib.STORED33A('name1','M',' ');call testlib.STORED33A('name1','M',5);call testlib.STORED33A('name1','M',?);You will get the return output.
No such issue will occur when you call the
SQL stored procedurefrom theRun SQL Script. This issue will only occur in theSTRSQLsession. Therefore, to call it from theSTRSQLsession you first have to create aSQL global variableusingCREATE VARIABLEstatement like below.The variableSQLOUTwas created intestlib. It’s an*SRVPGMobject type of attributeCLE.Now again try to call the
SQL stored procedurefrom theSTRSQLsession on IBM i but this time passed theSQL global variablefor theOUT or INOUT parameterin the procedure as below.CALL statement complete.
SQL7985which means Call to procedureSTORED33Acompleted successfully.You can check the return value in the SQL global variable as follows:
the output return value in SQL global variable from SQL stored procedure call.