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.
midrange.com
