× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.