× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi Gary.

I have a program structure like this:

EXEC SQL DECLARE C1 DYNAMIC SCROLL CURSOR WITHOUT HOLD WITH RETURN TO
CLIENT
FOR SELECT * FROM QTEMP/TABLE30;

EXEC SQL DECLARE C2 DYNAMIC SCROLL CURSOR WITHOUT HOLD WITH RETURN TO
CLIENT
FOR SELECT * FROM QTEMP/TABLE40;

// Populate QTEMP/TABLE30
CallP PASE30 (CAB:PEC:CUM:PRY);

// Populate QTEMP/TABLE40
CallP PASE40 (CAB:PEC:CUM);

// Populate Array RS(x)
Exsr Carga_docum;

// Result Set TABLE30
EXEC SQL OPEN C1;

// Result Set TABLE40
EXEC SQL OPEN C2;

// Return Result Sets to client CURSORS
Exec Sql SET RESULT SETS FOR RETURN TO CLIENT CURSOR C1, CURSOR C2;

// Return Result Sets to client ARRAY
Exec Sql SET RESULT SETS ARRAY :Rs FOR :Ix ROWS;

My objetive, if this is possible, is replace the statement "SET RESULT SETS
FOR RETURN TO CLIENT CURSOR C1...", for another that bring me the
posibility of return three arrays instead of two cursors.

Reviewing documentation, it seems imposible. I think that only can return
one array:

http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzhsetrs.htm?lang=es

"Only one array can be specified in a SET RESULT SETS statement, including
any RETURN TO CLIENT array result sets from nested calls to procedures."

Thanks anyway for your help.

message: 2
date: Mon, 15 Dec 2014 13:25:13 +0000
from: Gary Thompson <gthompson@xxxxxxxxxxx>
subject: RE: Cursors vs Arrays in Stored Procedure

Jose,
Here is an RPGLE example in /Free format:

First, traditional D specs define a DS array:

* Start: data to store Pallet Detail
-------------------------------------
D PDmaxC C const(2000)
max Pallet Detail
D PDmax S 5I 0 Inz(PDmaxC)
" " "
D PD@ S 5I 0 Inz(0)
index to PD#

D PD# DS DIM(PDmaxC) Qualified
D DelLoc 4A
Delivery Location
D DelDat 7S 0 Inz(*Zero)
" Dt CYYMMDD
D DelRte 5A
" Route
D PalSeqNum 5P 0 Inz(*Zero)
Pallet Sequence Num
D ArtNum 9S 0 Inz(*Zero)
Article number
D UniLod 7P 0 Inz(*Zero)
Units to be loaded
D UniPkd 9P 0 Inz(*Zero)
" loaded on palt
D ArtNam 30A
Article Name
D ArtWgt 5P 2 Inz(*Zero)
" Case Wgt Lbs
D Upc12 12A
AM01.USRARE001,01,12
D PackTy 2A
" .ARTGRP07
D PackTyCi 5A
" .ARTGRP07 CI
D SubU 2A
" .ARTGRP08
* End : data to store Pallet Detail
-------------------------------------

The size of the array is much larger than any expected Pallet

Here, in /Free format is SQL statement for the cursor:

// Cursor to get Pallet Detail for specified Delivery
Exec SQL
DECLARE PDcsr CURSOR FOR
SELECT l95.delloc, l95.deldat, l95.delrte, l96.palseqnum,
l97.artnum, l97.unilod, l97.unipkd, am1.artnam,
am1.artwgt, SUBSTR(am1.usrare002,01,12) AS upc12,
am1.artgrp07 AS packty,
SUBSTR(xi1.datafield,41,05) AS packtyci,
am1.artgrp08 AS subu
FROM lc95 l95 JOIN lc96 l96
ON l96.delloc = l95.delloc
AND l96.deldat = l95.deldat
AND l96.delrte = l95.delrte
JOIN lc97 l97
ON l97.delloc = l95.delloc
AND l97.deldat = l95.deldat
AND l97.delrte = l95.delrte
AND l97.palseqnum = l96.palseqnum
JOIN am01 am1
ON am1.artnum = L97.artnum
AND am1.eftdat = 1999999
JOIN xi01 xi1
ON xi1.recid = 'XIC'
AND xi1.mainkey = 'PACKTY'
AND xi1.subkey = (' ' || AM1.artgrp07)
WHERE l95.delloc = :wk_DelLoc
AND l95.deldat = :wk_DelDat
AND l95.delrte = :wk_DelRte
AND L96.outnum = :wk_OutNum
AND L97.UNIPKD > 0
ORDER BY 1,2,3,4,5;


Next is an SQL statement to populate the array:

Exec SQL FETCH PDcsr FOR :PDmax ROWS INTO :PD#;

And, finally, an SQL statement to load a table in QTEMP, or a library of
your choice:
(Note the "WITH DATA" clause at the end of the SQL statement)

Exec SQL
CREATE TABLE qtemp/apartsav AS (
SELECT artnum, SUBSTR(artnam,1,18) AS artnam18,
CAST(0 AS NUMERIC(7,0)) AS artnmovipk,
artgrp7, artwgt,
SUBSTR(usrare030C,01,03) AS apshlflif,
SUBSTR(usrare030C,04,02) AS apstkseq,
SUBSTR(usrare030C,06,02) AS aplayrunt,
SUBSTR(usrare030C,08,05) AS apformat,
SUBSTR(usrare030C,13,02) AS appaltyp,
SUBSTR(usrare030C,15,03) AS apcsppal,
SUBSTR(usrare030C,18,01) AS aplayropt,
SUBSTR(usrare030C,19,01) AS aplayrreq,
SUBSTR(usrare030C,20,01) AS apdisc,
bascamind, basartref, artgrp05,
artgrp06, artgrp07, sprcod, sprdat
FROM basdbc1/am01
WHERE eftdat = 1999999
AND sprcod = ' '
AND SUBSTR(usrare030C,08,05) NOT IN (' ','00000')
AND am01.artnum IN (SELECT artnum FROM basdbc1/am02
WHERE delloc IN ('09 ','11 ','92 '))
ORDER BY 1
) WITH DATA;


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jose
Luis Hernandez Riesgo
Sent: Monday, December 15, 2014 4:05 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Cursors vs Arrays in Stored Procedure

Hi everybody.

If it's possible some help. I have one stored procedure which returns
three result sets. One array and two cursors (from two tables in qtemp).
The result sets (array and cursors) are built on a rpg program.

Somebody know if is possible, convert the return of cursors in arrays in a
easy way.

In resume, i try that one rpg program return three arrays to a stored
procedure instead of one array and two cursors.I need leave to work with
qtemp tables.

Finally, we are in V7R1.

Thanks in advance,

greetings and merry christmas to everybody.
--
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.

*****************************************


As an Amazon Associate we earn from qualifying purchases.

This thread ...


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

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

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.