Re: Learning CLOB, getting clobbered!

On 03-Apr-2015 11:15 -0500, DeLong, Eric wrote:

I am writing a stored procedure to collect response data (stored in
a data queue) into a "RESPONSES" document... Because I need to pass
back as many responses as I can at one time, I'm trying to build my
output as CLOB(2G)... However, I'm not catching on to how to append
my message entries into the output CLOB... Here's my first (or
second) stab at this... Following that is the error message I get
when I run...

I guess I need to read up on "LOB LOCATOR"...

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE PROCEDURE TESTED.GETMQOUT
( OUT OUT_MESSAGES CLOB( 2G ) )
LANGUAGE SQL
SET OPTION DBGVIEW = *SOURCE
BEGIN

DECLARE DQNAME CHAR(10);
DECLARE DQLIB CHAR(10);
DECLARE DQRCDLN DECIMAL(5);
DECLARE DQDATA CLOB(64K);
DECLARE DQWAIT DECIMAL(5);

DECLARE NODATA INT;
DECLARE OUTSIZE BIGINT;

SET DQNAME = 'BTQUE_OUT';
SET DQLIB = 'BTSGDEV';
SET DQWAIT = 1;

SET NODATA = 0;
SET OUT_MESSAGES = '<RESPONSES>';
SET OUTSIZE = 11;

WHILE NODATA = 0 AND OUTSIZE < 2000000000 DO
SET DQDATA = '';
SET DQRCDLN = 0;
CALL QSYS.QRCVDTAQ (DQNAME, DQLIB, DQRCDLN, DQDATA, DQWAIT);
IF DQRCDLN > 0 THEN
SET OUT_MESSAGES = OUT_MESSAGES concat '<RESPONSE>'
concat left(DQDATA, int(DQRCDLN)) concat'</RESPONSE>';
SET OUTSIZE = OUTSIZE + DQRCDLN + 21;
ELSE SET NODATA = 1;
END IF;
END WHILE;

SET OUT_MESSAGES = OUT_MESSAGES concat '</RESPONSES>';
END;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

call TESTED.GETMQOUT ( ? )

SQL State: 0F001
Vendor Code: -423
Message: [SQL0423] Locator *N not valid.
Cause . . . . . : The value of locator *N is not currently valid. A
LOB or XML locator may have been freed by a previous FREE LOCATOR
statement or a COMMIT or ROLLBACK. A result set locator may have been
freed by a CLOSE statement or a COMMIT or ROLLBACK. An ALLOCATE
CURSOR statement cannot be run for a result set locator value that
has already had a cursor allocated.
Recovery . . . : Ensure that the locator value refers to an active
locator that has not been freed because of a FREE LOCATOR, CLOSE,
COMMIT, or ROLLBACK statement. A LOB or XML value can be assigned to
a locator variable using a SELECT INTO statement, a VALUES INTO or
SET statement, a GET DESCRIPTOR statement, or a FETCH statement. A
value can be assigned to a result set locator variable using an
ASSOCIATE LOCATORS statement or a DESCRIBE PROCEDURE statement.

Thanks in advance for any ideas...

The CREATE PROCEDURE [or DECLARE PROCEDURE] statement used to define the stored procedure referenced in the CALL was not included in the OP; i.e. what defines for the SQL, the interface to what is presumably the external program named QRCVDTAQ in QSYS.

Yet surely no matter that omission, because that non-SQL OPM external program QRCVDTAQ surely is incapable of providing a LOB built-in-type [value or as locator] as a return value to the invoker. The matching data-type for the "Data" [argument-4 of the API] decidedly must be declared otherwise, when defining the interface to the SQL. Perhaps CHARACTER [FOR BIT DATA] or BINARY might match what the API provides for that argument for the given invocation in that scenario. However then I expect the size limitation for the parameter would be around half what is supported from the API as the maximum message length as output for "Length Of Data" [argument-3 of the API]; that is far too small for the largest available Maximum Entry Length (MAXLEN) specification of 64512 on the Create Data Queue (CRTDTAQ). Anyhow, the error msg SQL0423 seems appropriate given that no locator would\could have been created by the external program QRCVDTAQ.

If the record length of the BTSGDEV/BTQUE_OUT *DTAQ referenced in the invocation is less than 32K, then the external stored procedure definition could be adjusted accordingly. Otherwise an embedded SQL program could serve in place of the attempted LANGUAGE SQL procedure, performing the native\non-SQL invocation of the API [unimpeded by the limitations on character data-type lengths], and then returning the data as a LOB result.