× 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.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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.