RE: SQL Issues

["Dane Cox" <DCox@xxxxxxxxxxxxx>]

Charles,

That's not exactly what I mean.  He is using an 'array' result set in
his first example.  The example he used later in his post is the one I'm
talking about.  Here are the two different statements...

Array:  set result sets array :rs for :rsrow rows
Cursor:  set result sets cursor c1

For some reason, I am unable to see multiple 'array' result sets coming
back from a stored procedure.  In this case, I use two different
statements to set the result sets.

/exec sql
set result sets array :rs for :rsrow rows
/end-exec

/exec sql
set result sets array :ms for :msrow rows
/end-exec

This doesn't work as expected and we only get one result set back to the
caller...the second one.
 
With your help, I am able to see multiple 'cursor' result sets coming
back.

/exec sql
set result sets cursor c1, cursor c2
/end-exec

Now that we can get multiple result sets back using the 'cursor'
technique, I am wondering how to get a simple array (or array ds) into
an SQL cursor in an RPG program?  Specifically, I would like to put
array 'rs' into cursor 'c1' and array 'ms' into cursor 'c2'.

Does that make sense?

Thanks,
Dane

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CWilt@xxxxxxxxxxxx
Sent: Tuesday, June 15, 2004 5:02 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: SQL Issues

Dane,

Chris' original post had an example.  See below.

Charles

> -----Original Message-----
> From: Dane Cox [mailto:DCox@xxxxxxxxxxxxx]
> Sent: Tuesday, June 15, 2004 5:53 PM
> To: RPG programming on the AS400 / iSeries
> Subject: RE: SQL Issues
> 
> 
> Charles,
> 
> This worked for me.  I am now getting back both cursor result sets
> through Ops Nav.
> 
> Would you happen to know a way to get data from an array within an RPG
> program into an SQL cursor so that I might return it in the 
> same manner.
> I've only seen cursors declared over files.  Any ideas?
> 
> Thanks!
> Dane 
> 
> 
> > -----Original Message-----
> > From: Chris Wolcott [mailto:CWolcott@xxxxxxxxxxxxxxxxxxxxxx]
> > Sent: Tuesday, June 15, 2004 3:07 PM
> > To: rpg400-l@xxxxxxxxxxxx
> > Subject: SQL Issues
> > Importance: High
> > 
> > 
> >  
> > I am trying to write an RPGLE program with embedded SQL.  If 
> > I create a
> > DS OCCURS() structure, populate it via RPGLE commands and 
> issue a SET
> > RESULT SETS ARRAY :name FOR :nbr ROWS command I get a result set.
> > However, if I DECLARE CURSOR, OPEN CURSOR and SET RESULT SETS 
> > CURSOR, I
> > get NOTHING.  (Not even an empty table)  I am testing it via 
> > OPSNAV Run
> > SQL Scripts.
> > 
> > Any ideas on what I'm doing wrong?
> > 
> > Using a DS:  (This gives me 10 rows, with only the first 
> > having data in
> > it, as expected.)
> > 
> > D OptList         DS           385    OCCURS(1024)
> > D   OptName                     25A               
> > D   OptCode                     10A               
> > D   OptPrms                    350A               
> > 
> > C                   CLEAR     *ALL          OptList
> > C                   EVAL      OptName = 'Test'       
> > C                   EVAL      OptCode = 'Test2'      
> > C                   EVAL      OptPrms = 'Test3'      
> > C/EXEC SQL                            
> > C+ SET RESULT SETS ARRAY :OptList FOR 10 ROWS
> > C/END-EXEC 
> > 
--
This is the RPG programming on the AS400 / 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.




NOTICE: This electronic mail message and any files transmitted with it are 
intended exclusively for the individual or entity to which it is addressed. The 
message, together with any attachment, may contain confidential and/or 
privileged information. Any unauthorized review, use, printing, saving, 
copying, disclosure or distribution is strictly prohibited. If you have 
received this message in error, please immediately advise the sender by reply 
email and delete all copies.