You should use the pre-compiler expanded fields... XML_CLOB_DATA when assigning data to or from the CLOB field... It looks like the CLOB data was assigned directly to XML_CLOB, which causes the first four bytes to be used to specify the "varying length" information.
-Eric DeLong
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Needles,Stephen J
Sent: Friday, July 11, 2014 11:14 AM
To: RPG programming on the IBM i (AS/400 and iSeries) (rpg400-l@xxxxxxxxxxxx)
Subject: SQL0311 error when using CLOB as input parameter weird length errors concerning the CLOB
Ok...IBM 7.1 with latest TR (TR6?). Lots of info below and I'm sure that I didn't get it all.
Here is the general scenario...
PGMA is executed by a SQL stored proc and receives a CLOB(1048576). The CLOB contains Call data (typically XML, but not always) and is passed to PGMLOGGER that accepts the CLOB and writes it to a log table.
The variable's definition for the XML (XML_IN is the incoming parameter and XML_CLOB the value being written to the table) is being changed from CHAR(32000) to CLOB(1048576).
The CLOB in the table is defined: (XML_String aka XML_CLOB)
XML_STRING FOR COLUMN WBXXML CLOB(1048576) NOT NULL DEFAULT ' '
The variable in PGMLOGGER that accepts the parameter and writes it to the log is defined:
DXML_CLOB SQLTYPE(CLOB:1048576)
The listing (via a STRDBG session)displays this as:
D*XML_CLOB SQLTYPE(CLOB:1048576)
DXML_CLOB A LEN(1048580)
DXML_CLOB_LEN 10U 0 OVERLAY(XML_CLOB)
DXML_CLOB_DATA A LEN(1048576) OVERLAY(XML_CLOB:5)
When PGMA calls PGMLOGGER to log the data, the parameter contains the correct data:
EVAL Parm_XML_In
PARM_XML_IN =
....5...10...15...20...25...30...35...40...
1 '<Name>WPGQSL2020</Name>QB432960|||I|N||
After entering into PGMLOGGER, the value is changed:
EVAL XML_IN
XML_IN_LEN OF XML_IN = 1289060756
XML_IN_DATA OF XML_IN =
....5...10...15...20...25...30...35...40
1 'e>WPGQSL2020</Name>QB432960|||I|N||
Consistent with the expanded definition of the SQLTYPE, XML_CLOB is redefined as a DS with two variables...XML_CLOB_LEN and XML_CLOB_DATA.
The issue is that the SQL0311 error states:
Message ID . . . . . . : SQL0311 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Date sent . . . . . . : 07/11/14 Time sent . . . . . . : 11:05:30
Message . . . . : Length in varying-length, LOB, or XML host variable not
valid.
Cause . . . . . : Host variable WBXMLD_ROW.XML_CLOB was specified. The
value in the length portion of the variable length, LOB, or XML host
variable is either negative or greater than the declared length. If the
host variable is graphic the length should be the number of DBCS characters.
The host variable number is 14. The specified length is 1289060756. The
variable is declared to have length 1048576.
Recovery . . . : Change the length portion of the varying-length, LOB, or
XML host variable to a valid positive number or zero. Try the request again.
So how did the specified length change from 1,048,576 to 1,289,060,756? How do I fix it?
In spite of the redefine that occurs, the XML_CLOB data is correct after it is populated with this statement:
c eval XML_CLOB = xml_in
EVAL XML_CLOB
XML_CLOB =
....5...10...15...20...25...30...35...40.
1 '<Name>WPGQSL2020</Name>QB432960|||I|N||
It just fails on the SQL insert.
INSERT INTO logfile
values(:Process,
:Partition,
:Routine,
:Contract,
:Log_Time,
:Entry_Date,
:Entry_Time,
:XML_Date,
:XML_Time,
:Routine_Seq,
:Job_Name,
:Job_User,
:Job_Number,
:XML_CLOB)
Steve Needles | IT Architect | Specialized Distribution | Northland Insurance Travelers
385 Washington | SB03
St. Paul, MN 55102
W: 651.310.4203
[cid:image002.jpg@01CF9CF9.2F9509C0]
________________________________
This communication, including attachments, is confidential, may be subject to legal privileges, and is intended for the sole use of the addressee. Any use, duplication, disclosure or dissemination of this communication, other than by the addressee, is prohibited. If you have received this communication in error, please notify the sender immediately and delete or destroy this communication and all copies.
TRVDiscDefault::1201
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
