On 24 July 2015 at 11:52, Robert J. Mullis <robertmullis99@xxxxxxxxxxx> wrote:
I want to create a module that has one exported procedure in it. When this
procedure is initially called, I want to open an embedded SQL cursor and
read the first record. On subsequent calls to this procedure, I want to do
some processing on the record previously read and then read the next record.
I want to repeat this until I hit EOF on the SQL cursor. Is it possible to
retain the previous values read from the SQL cursor and have the cursor read
the next record when the procedure is called subsequent times?
As Charles notes, this isn't the optimum way to process rows with SQL,
but if you really need to handle one row at a time, consider doing the
processing in a callback rather than in the procedure doing the FETCH.
Scott Klement has examples in FTPAPI and HTTPAPI if I remember
rightly. The general outline looks like this:
MAIN
dcl-s objNam char(10) inz('QRPGLESRC');
dcl-s objLib char(10) inz('BUCK');
dcl-s objtyp char(10) inz('*FILE');
dcl-s rtnCode char(7);
// Run the API that will return data to us one record at a time
// it will call a procedure that we define
rtnCode = OpenObjectList(objNam: objLib: objTyp: %paddr(details));
*inlr = *on;
// declare the procedure that will handle the returned data
dcl-proc details;
dcl-pi *n;
lst likeds(objLst);
end-pi;
dcl-s display char(50);
display = lst.objNam + ' ' + lst.objAtr + ' ' + %char(lst.objSiz) +
' ' + %char(lst.objSizMult) + ' ' + lst.objOwner;
dsply display;
end-proc;
SERVICE PROGRAM
// Open Object List
dcl-proc openObjectList export;
dcl-pi *n char(7);
objNam char(10) const;
objLib char(10) const;
objTyp char(10) const;
detailProc pointer(*proc) const;
end-pi;
// callback routine definition
dcl-pr objLstProc extproc(detailproc);
lst likeds(objlst);
end-pr;
// internal working variables
dcl-s rtnCode char(7) inz;
...
// run it
callp QGYOLOBJ(rcvVar:
rcvVarLen:
listInfo:
nbrRcdsToReturn:
sortInfo:
objQual:
objType:
autCtl:
selCtl:
nbrKeyFieldsToReturn:
keyFieldsToReturn:
errorCode);
...
// retrieve each list element and pass it to the callback
for i = 1 to listInfo.totRecords;
callp QGYGTLE (rcvVar:
rcvVarLen:
listInfo.requestHandle:
listInfo:
nbrRcdsToReturn:
i:
errorCodeGet);
...
// populate the structure to return to the callback
reset lst;
lst.objNam = rcvVar.objName;
lst.objLib = rcvVar.objLib;
lst.objTyp = rcvVar.objType;
...
// fire the callback
callp objLstProc(lst);
endfor;
// close up processing and return the the original caller
return errorcode.ErrMsgID;
end-proc;
This is an extract from a proc that does an API call, then returns the
results back one at a time to the caller, but the idea is very similar
to what you'd do with SQL. Instead of the call to QGYGTLE, you'd
FETCH, then call the callback procedure.
Hope this helps.
--buck
midrange.com
