Re: Learning CLOB, getting clobbered!

Eric,

A CLOB seems a poor choice to me...I'm assuming the caller will just end up
parsing the data back into individual responses?

If so, I'd consider a UDF that returns the next response, along with a
"Numbers" table. A numbers table is just what it sounds like, a table
containing a single (large) integer column so that you have 1 record with
every number from 0 to whatever.

Then for instance, lets say you want 1000 responses at a time,

select GetNextReponse()
from NUMBERS_TBL
number between 1 and 1000

Charles

On Fri, Apr 3, 2015 at 12:15 PM, DeLong, Eric <EDeLong@xxxxxxxxxxxxxxx>
wrote:

Hi all,

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...
-Eric DeLong
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.