My question centers around how to map the result set to an output link. The stage documentation indicates that a CursorOutput parameter should be used "when the stored procedure returns a record or a result set." However, the parameter entry tab insists that every parameter be mapped to a column, presumably to one on the output link in this case.
The columns on my output link are defined as the several fields in the result set (shown at the bottom of this post.) As far as I can tell, there is no way to declare a "result set" type output column and there is no "result set stage" that I can pipe the result to. Attempts to map the cursor to one or several output columns result in a segmentation violation.
Hopefully, someone has some experience with this new stage type and has gotten it to work before. How should I set up the parameter list, parameter mapping and output links and columns in order to get this stored procedure stage to work properly?
For reference, I have included the stored procedure code below. Thank you for your assistance.
Code: Select all
CREATE PROCEDURE GITDBA.SELCAT (IN TABLENAME VARCHAR(30) )
RESULT SETS 1
LANGUAGE SQL
COLLID D4R
WLM ENVIRONMENT SDD40H1
RUN OPTIONS 'NOTEST(NONE,*,*,*)'
------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------
P1: BEGIN
DECLARE FIELD VARCHAR(10);
DECLARE CURSOR1 CURSOR WITH RETURN FOR
SELECT TBCREATOR, TBNAME, NAME, COLNO, COLTYPE, LENGTH
FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME = TABLENAME
AND TBCREATOR = 'INV'
ORDER BY COLNO;
OPEN CURSOR1;
END P1
Column definitions:
TBCREATOR CHAR(8)
TBNAME VARCHAR(18)
NAME VARCHAR(18)
COLNO SMALLINT
COLTYPE CHAR(8)
LENGTH SMALLINT
