Just for the record, I was able to get the DtaQ data into a CLOB field with this:
__________________________________________________________
CREATE OR REPLACE PROCEDURE BTSGDEV.GETMQOUT
(
OUT OUT_MESSAGES CLOB(16M )
)
LANGUAGE SQL
SET OPTION DBGVIEW = *SOURCE
BEGIN
DECLARE DQNAME CHAR(10);
DECLARE DQLIB CHAR(10);
DECLARE DQRCDLN DECIMAL(5);
DECLARE DQDATA CHAR(32500);
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 < 16500000 DO
SET DQRCDLN = 0;
SET DQDATA = '';
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;
________________________________________________________________________
The real trick was eliminating the CLOB type as the receiver of the data queue message... Once I decided to reduce that queue max length to < 32K, then character functions worked as expected... I also reduced the output CLOB field to 16M, which should still be plenty of space to package the responses... Everyone is happy.
Thanks Charles and Chuck!
-Eric DeLong
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Saturday, April 04, 2015 11:58 AM
To: midrange-l@xxxxxxxxxxxx
Subject: 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.
--
Regards, Chuck
--
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.
midrange.com
