Stored procedures created
To create the stored procedures, run this code:
CREATE PROCEDURE iontest/GETORDERS
LANGUAGE RPGLE
SPECIFIC iontest/GETORDERS NOT DETERMINISTIC EXTERNAL
NAME IONTEST/GETORDERS PARAMETER STYLE GENERAL
CREATE PROCEDURE iontest/PUTORDERS (IN string varChAR(32000))
LANGUAGE RPGLE SPECIFIC iontest/putorders NOT DETERMINISTIC
EXTERNAL NAME
iontest/putorders PARAMETER STYLE GENERALThis is the code of the GETORDERS procedure:
H option(*nodebugio: *SRCSTMT : *SHOWCPY ) CVTOPT(*VARCHAR)
H DFTACTGRP( *NO ) ACTGRP(*CALLER) BndDir( 'QC2LE' )
H DEBUG(*YES) ALWNULL(*USRCTL)
d GetOrders PR
d numResults 10i 0
d GetOrders PI
d numResults 10i 0
D MYDBCLOB s SQLTYPE(DBCLOB:400)
d Results ds occurs(5 )
d resultxml 10000A varying
d x s 10i 0
d y s 2 0
d wrkxml s 64000 varying
d recxml s 32000 varying
D rtnstr s 50A
D icv_RtnCod s 10i 0
d ordh e ds extname(orders)
d qualified
D Nullind Ds
D Nullsh 5i 0 Dim(5)
D Nullsind Ds
D Nullsd 5i 0 Dim(5)
d ordd e ds extname(orderLine)
d qualified
D W3CALCOBJ PR extpgm('W3CALCOBJ')
D CMD_LENGTH s 15p 5
D CMD_TO_RUN s 2000
D CMD_return s 50
D Qcmdexc PR extpgm('QCMDEXC')
D cmd_TORUN 2000 CONST options(*varsize)
D CMD_LENGTH 15p 5 CONST
/COPY QSYSINC/QRPGLESRC,QTQICONV
/free
cmd_TO_RUN = 'Addlible IONTEST';
cmd_length = %checkr(' ':cmd_to_run);
monitor;
callp Qcmdexc (cmd_TO_RUN:CMD_LENGTH);
on-error;
cmd_TO_RUN = cmd_TO_RUN;
endmon;
// Provide the ability to "suspend" this job.
callp(e) W3CALCOBJ ();
x = 0;
wrkxml = '';
exec sql declare cursorord cursor for select * from IONTEST/orders;
exec sql open cursorord;
exec sql fetch next from cursorord into :ordh :nullsh;
if sqlCode < 0;
endif;
if sqlCode <> 100;
dou sqlCode = 100;
// insert into ProcOrd
exec sql insert into iontest/Procord (ordernum, PROCESSst)
values(:ordh.ordernum, 'SENT');
exsr createxml;
if %len(wrkxml) + %len(recxml) < 32000;
wrkxml += recxml;
else;
if (X+1) >= 10;
x += 1;
%occur(Results) = x;
resultxml = '<DataArea>'+ wrkxml + '</DataArea>';
wrkxml = '';
if x >= 10;
leave;
endif;
endif;
endif;
exec sql fetch next from cursorord into :ordh :nullsh;
enddo;
endif;
if %len(wrkxml) > 1;
x += 1;
%occur(Results) = x;
resultxml = '<DataArea>'+ wrkxml + '</DataArea>';
wrkxml = '';
endif;
if x > 0;
exsr CreateResultSet;
endif;
exec sql close cursorord;
// exec sql close cursorordl;
*inlr = *On;
RETURN;
//-------------------//
// createxml //
//-------------------//
begsr createxml;
recxml = '<MySalesOrder>';
recxml += '<orderID>' + %trim(ordh.ORDERNUM) + '</orderID>';
if Nullsh(2) = 0;
recxml += '<orderStatus>' + %trim(ordh.orderstat)
+ '</orderStatus>';
endif;
if Nullsh(3) = 0;
recxml += '<orderDateTime>' + %trim(%char(ordh.ORDERTSTMP))
+ '</orderDateTime>';
endif;
recxml += '<customer>' + %trim(ordh.customer)
+ '</customer>';
// get order lines
exec sql declare cursorordl cursor for
select * from IONTEST/orderline where :ordh.orderNum = orderNum
order by orderNum,linenum;
exec sql open cursorordl;
exec sql fetch next from cursorordl into :ordd :nullsd;
if sqlCode < 0;
endif;
IF SQLcODE <> 100;
dou sqlCode = 100;
recxml += '<MySalesOrderLine>';
recxml += '<lineNumber>' + %trim(%char(ordd.LINENUM))
+ '</lineNumber>';
if Nullsd(3) = 0;
recxml += '<item>' + %trim(ordd.item)
+ '</item>';
endif;
if Nullsd(4) = 0;
recxml += '<quantity>' + %trim(%char(ordd.quantity))
+ '</quantity>';
endif;
if Nullsd(5) = 0;
recxml += '<price>' + %trim(%char(ordd.price))
+ '</price>';
endif;
recxml += '</MySalesOrderLine>';
exec sql fetch next from cursorordl into :ordd :nullsd;
enddo;
endif;
recxml += '</MySalesOrder>';
exec sql close cursorordl;
endsr;
//-------------------//
// CreateResultSet //
//-------------------//
begsr CreateResultSet;
EXEC SQL SET RESULT SETS ARRAY :Results FOR :x ROWS;
endsr;
/end-freeNote: If you use version 7.1, you can use XML functions such
as XMLELEMENT and XMLAGG to build the XML easier. See the following statement
for an example.
SELECT xmlserialize(content
xmlelement(name "DataArea",
xmlagg(
xmlelement(Name "MySalesOrder",
xmlelement(Name "orderID", ORDERNUM),
xmlelement(Name "orderStatus", orderstat),
...
)
)
)
AS CLOB(1M) ) as "result" FROM cursorordThis is the code of the PutORDERS procedure:
H option(*nodebugio: *SRCSTMT : *SHOWCPY ) CVTOPT(*VARCHAR)
H DFTACTGRP( *NO ) ACTGRP(*CALLER)
H DEBUG(*YES) ALWNULL(*USRCTL)
d PutOrders PR
d inXML 32000A varying
d PutOrders PI
d inXML 32000A varying
d x s 3 0
d Y s 3 0
d ordernumber s 20
d orderstatusw s 20
d ordertimestmp s Z
d custnum s 20
d lastTimestmp s Z
d timewrk s 26
d linew s 5 0
d itemw s 20
d Quantityw s 5 0
d pricew s 7 2
d numnsch s 2 0
D DataArea ds qualified
D Sync likeDS(Sync)
D MySalesOrder likeDS(MySalesOrder) Dim(10)
D xmlnMySalesOrder...
D 10I 0
D Sync ds qualified template
D TenantID likeds(TenantID)
D AccountingEntityID...
D likeDS(AccountingEntityID)
D LocationID likeDS(LocationID)
D ActionCriteria likeDS(ActionCriteria)
D TenantID ds qualified template
D data 30A
D AccountingEntityID...
D ds qualified template
D data 30A
D LocationID ds qualified template
D data 30A
D ActionCriteria ds qualified template
D ActionExpression...
D likeDS(ActionExpression)
D ActionExpression...
D ds qualified template
D actionCode 20A
D data 30A
D
D MySalesOrder ds qualified template
D orderID likeDS(orderID)
D orderStatus likeds(orderStatus)
D dateTime likeDS(dateTime)
D customer likeDS(customer)
D MySalesOrderLine...
D likeds(MySalesOrderLine)
D dim(10)
D xmlnMySalesOrderLine...
D 10I 0
D orderID ds qualified template
D data 30A
D orderStatus ds qualified template
D data 30A
D dateTime ds qualified template
D data 30A
D customer ds qualified template
D data 30A
D MySalesOrderLine...
D ds qualified template
D lineNumber likeDs(lineNumber)
D item likeDs(item)
D quantity likeDs(quantity)
D price likeDS(price)
D lineNumber ds qualified template
D data 30A
D item ds qualified template
D data 30A
D quantity ds qualified template
D data 30A
D price ds qualified template
D data 30A
D W3CALCOBJ PR extpgm('W3CALCOBJ')
D CMD_LENGTH s 15p 5
D CMD_TO_RUN s 2000
D CMD_return s 50
D Qcmdexc PR extpgm('QCMDEXC')
D cmd_TORUN 2000 CONST options(*varsize)
D CMD_LENGTH 15p 5 CONST
/free
cmd_TO_RUN = 'Addlible IONTEST';
cmd_length = %checkr(' ':cmd_to_run);
monitor;
callp Qcmdexc (cmd_TO_RUN:CMD_LENGTH);
on-error;
cmd_TO_RUN = cmd_TO_RUN;
endmon;
// Provide the ability to "suspend" this job.
callp(e) W3CALCOBJ ();
monitor;
XML-Into DataArea
%XML(inxml:'case=any allowextra=yes countprefix=xmln '
+ ' allowmissing=yes datasubf=data');
on-error;
// send escape message
return;
endmon;
// Process Order
if DataArea.xmlnMySalesOrder <> 0;
for x = 1 to DataArea.xmlnMySalesOrder;
ordernumber = DataArea.MYSALESORDER(x).ORDERID.DATA;
orderstatusw = DATAAREA.MYSALESORDER(x).ORDERSTATUS.DATA;
custnum = DataArea.MYSALESORDER(x).CUSTOMER.DATA;
timewrk = DataArea.MYSALESORDER(x).DateTime.DATA;
timewrk = %subst(timewrk:1:10)+'-'+%subst(timewrk:12:8)
+'.000000';
timewrk = %xlate(':':'.':timewrk);
ordertimestmp =%timestamp(timewrk);
lastTimestmp = %timestamp();
// process header file
exec sql insert into iontest/ORDERS
(ordernum, orderstat, ORDERTSTMP, customer, LASTMOD)
values(:ordernumber, :orderstatusw, :ordertimestmp, :custnum,
:lastTimestmp);
if DataArea.MySalesOrder(x).xmlnMySalesOrderLine <> 0;
for y = 1 to DataArea.MySalesOrder(x).xmlnMySalesOrderLine;
linew = %int(
DataArea.MySalesOrder(x).MySalesOrderLine(y).lineNumber);
itemw =DataArea.MySalesOrder(x).MySalesOrderLine(y).item;
quantityw = %int(
DataArea.MySalesOrder(x).MySalesOrderLine(y).quantity);
pricew = %dec(
DataArea.MySalesOrder(x).MySalesOrderLine(y).price:7:2);
exec sql insert into iontest/ORDERline
(ordernum, LINENUM , item, QUANTITY, price)
values(:ordernumber, :linew, :itemw, :quantityw, :pricew);
endfor;
endif;
endfor;
endif;
*inlr = *On;
RETURN;
/End-Free