Hi Stephen,

I have been tasked with finding a way to accept a 16M CLOB from .net
via an SQL Stored Procedure call. This SQL will contain a 16M CLOB
containing XML.

First of all, I'd look into what's meant by '16MB'. Although single-level store has a 16mb limit on the size of a space, that limit is /approximately/ 16mb... not exactly. It's actually a little bit smaller than 16mb... if you need to handle sizes larger than the single-level storage limit, you'll need to know that up-front.

But let's assume the single-level storage limit is okay for the sake of discussing what to do...

At IBM i version 6.1, you may not need to monkey around with pointers, since RPG natively supports data up to (approx) 16mb long. Simply declaring your CLOB as a 16mb long VARYING(4) string should work.

At v5r4, RPG doesn't support data longer than 64k. Referencing it with a pointer may be a workaround, but you still have a big problem -- XML-INTO can't read data addressed by a pointer variable -- it uses the size of the RPG variable to determine the length of the data. So you'll need to copy it from your CLOB parameter to either a *STMF or *USRSPC object on disk, and ask RPG to parse that. A *USRSPC will run faster, but has the same approx 16mb single-level storage limit that RPG variables, dynamic memory, etc, all have. (A *STMF does not have this limit.)

So you'll define a "dummy" CLOB variable, consisting of an integer for the length of the data, and then an alphanumeric string for the data itself. Remember that the CLOB will not be declared or allocated by your RPG program -- the database will take care of putting it in memory. Your parameter will overlay that memory.

So while v5r4 can't declare a variable large enough to overlay 16mb of memory, it doesn't really need to, either, because it's not allocating the memory. RPG's variable can view just a portion of the CLOB data -- while using pointers and APIs to copy the whole chunk of data to the user space. Then, you tell RPG to parse the XML from the user space.

The code would look like this (Obviously, you didn't post any details of the XML -- so please adjust the XML parsing accordingly):

H DFTACTGRP(*NO) ACTGRP('KLEMENT') BNDDIR('QC2LE')

/copy usrspc_h

D Clob_t ds qualified
D len 10i 0
D data_start 1a

D TESTFUNC PR ExtPgm('TESTFUNC')
D Clob likeds(Clob_t)
D retval 10i 0
D n_clob 5i 0
D n_retval 5i 0
D State 5a
D Function 517a varying const
D Specific 128a varying const
D errorMsg 70a varying
D TESTFUNC PI
D Clob likeds(Clob_t)
D retval 10i 0
D n_clob 5i 0
D n_retval 5i 0
D State 5a
D Function 517a varying const
D Specific 128a varying const
D errorMsg 70a varying

D MyData_t ds
D whatever 1a

D MyHandler PR 10i 0
D loaded 10i 0
D mydata likeds(MyData_t) dim(1)
D const
D count 10i 0 value

D Ignore DS qualified
D bytesProv 10i 0 inz(%size(Ignore))
D bytesAvail 10i 0 inz(0)

D memcpy pr * extproc('memcpy')
D dst * value
D src * value
D len 10u 0 value

D TEMPSPC C 'XMLDATA QTEMP'
D x s 10i 0
D ifsPath s 100a varying
D p_TempSpc s *

/free
*inlr = *on;

// ---------------------------------------------
// Create a user space in QTEMP
// ---------------------------------------------

QUSDLTUS( TEMPSPC: Ignore );

monitor;
QUSCRTUS( TEMPSPC
: 'XMLDATA'
: Clob.len
: *blanks
: '*ALL'
: 'XML data from CLOB' );
on-error;
State = '38999';
ErrorMsg = 'Unable to create user space';
return;
endmon;


// ---------------------------------------------
// Copy data from CLOB parameter into the
// user space
// ---------------------------------------------

QUSPTRUS( TEMPSPC: p_tempSpc );
memcpy( p_TempSpc: %addr(Clob.data_start): Clob.len );


// ---------------------------------------------
// Parse the XML
// ---------------------------------------------

ifsPath = '/QSYS.LIB/'
+ %trimr(%subst(TEMPSPC:11)) + '.LIB/'
+ %trimr(%subst(TEMPSPC:1:10)) + '.USRSPC';

monitor;
xml-into %handler(MyHandler:x)
%xml(ifsPath : 'doc=file +
path=mydata');
on-error;
State = '38999';
ErrorMsg = 'Error parsing XML document';
return;
endmon;


// ---------------------------------------------
// bye-bye user space
// ---------------------------------------------

QUSDLTUS( TEMPSPC: Ignore );
return;
/end-free


P MyHandler B
D PI 10i 0
D loaded 10i 0
D mydata likeds(MyData_t) dim(1)
D const
D count 10i 0 value
/free
// FIXME: insert the XML logic
return 0;
/end-free
P E


If you don't have the USRSPC_H copy book (heh... why would you?) you can get a copy here:
http://www.scottklement.com/rpg/copybooks/usrspc_h.rpgle.txt

To set this up as a function in SQL so that it can be called (hopefully?) by your .NET application, you'd run the following one-time SQL statement:

Create Function TESTFUNC ( parm1 CLOB(16M) )
returns Integer
external name TESTFUNC
program type main
language rpgle
parameter style sql
no sql
not deterministic
disallow parallel

Obviously, there are lots of details here that I'm leaving up to you (like choosing a better name than TESTFUNC) but, hopefully the parts that you didn't already understand are clarified by my example.

If not, ask questions.

This thread ...

Follow-Ups:
Replies:

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

This mailing list archive is Copyright 1997-2019 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].