Calling procedures with OUT/INOUT

[worksofliam]

Shared with me from @phpdave.

---

Does Db2 for i VS Code extension support calling stored procs with OUT parameters?

Getting this error

[SQL0469] IN, OUT, or INOUT not valid for parameter 6 in procedure MY_SP in MY_LIB., 42886, -469

CREATE PROCEDURE MY_LIB.MY_SP (
IN IN_1 DECIMAL(8, 0) ,
IN IN_2 CHAR(15) ,
IN IN_3 CHAR(15) ,
IN IN_4 VARCHAR(100) ,
IN IN_5 CHAR(1) ,
OUT OUT_6 CHAR(1) ,
OUT OUT_7 VARCHAR(100) )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC MY_LIB.MY_SP 
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION  ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN

  DECLARE my_cursor CURSOR WITH RETURN FOR
  SELECT IBMREQD FROM SYSIBM.SYSDUMMY1;

  SET OUT_6 = 'IT WORKS' ;
  SET OUT_7 = '1' ;

  OPEN my_cursor ;

END  ;

CALL MY_LIB.MY_SP ('1111', '', '', '123.1', 'N', ' ', '    ')

ACS can run it and outputs

Return Code = 0 
Output Parameter #6 (OUT_6) = 1 
Output Parameter #7 (OUT_7) = 

and displays the sql result set

[ngpeter66]

There is a not so convenient way to display OUT parms from stored procedures using Db2 for i, create global variables for your OUT parms and use them in your SP call statement:

CREATE VARIABLE O6 CHAR(1);
CREATE VARIABLE O7 VARCHAR(100);
CALL MY_LIB.MY_SP ('1111', '', '', '123.1', 'N', O6,O7);
VALUES(O6,O7);

[ThePrez]

@jeber_ibm any initial thoughts on the SQL0469?

[thomprl]

I ran into this recently and came here to see if it had been fixed yet. Just thought I'd bump this up.