Did you calculate the new length of your data and move it into XMLCLOB_LEN?
When using RPG EVAL you need move your CLOB Data into XMLCLOB_DATA and
calculate the length of your data and move it into XMLCLOB_LEN.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Needles,Stephen J
Gesendet: Friday, 11.7 2014 18:14
An: rpg400-l@xxxxxxxxxxxx
Betreff: SQL0311 error when using CLOB as input parameter weird lengtherrors
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
