RE: Returning Result Sets from SQLRPGLE - positioning the cursor

Aaron,

Thanks!  That worked like a charm (once I remembered to be sure my cursor 
gets closed, otherwise things act a little funny ;-)

Here's the code:

c/exec sql
c+ Set Option DatFmt=*ISO, Commit=*NONE, CloSqlCsr=*ENDMOD
c/end-exec 

c/exec sql
c+ declare C1 scroll cursor for
c+
c+ select *
c+ from filename
c+ where fieldname = :value
c+ order by fieldname
c+
c+ for fetch only
c/end-exec

c/exec sql
c+ open C1
c/end-exec

c/exec sql
c+ fetch relative :start from C1 for :num_rows rows into :ds_returnData
c/end-exec

c/exec sql
c+ set result sets array :ds_returnData for :num_rows rows
c/end-exec              



Mike Eovino
E-Commerce Manager
EDPS





"Bartell, Aaron L. (TC)" <ALBartell@xxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
06/30/2004 11:50 AM
Please respond to RPG programming on the AS400 / iSeries
 
        To:     RPG programming on the AS400 / iSeries 
<rpg400-l@xxxxxxxxxxxx>
        cc: 
        Subject:        RE: Returning Result Sets from SQLRPGLE - 
positioning the cursor


Here is how you can do the "start at row" functionality:

     C/EXEC SQL
     C+ FETCH RELATIVE :startRRN FROM C1 INTO :myDS
     C/END-EXEC 

I would be interested to know about your second question also concerning 
the
passing of an SQL result set to a Java program.

HTH,
Aaron Bartell

-----Original Message-----
From: rpg400-l-bounces+albartell=taylorcorp.com@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+albartell=taylorcorp.com@xxxxxxxxxxxx] On Behalf 
Of
meovino@xxxxxxxxxxxxxxxxx
Sent: Wednesday, June 30, 2004 10:48 AM
To: RPG programming on the AS400 / iSeries
Subject: Returning Result Sets from SQLRPGLE - positioning the cursor

We're getting into using embedded SQL in RPG programs to return result 
sets
to Net.Data macros.  It works great.  We've figured out how to return the
entire result set, but how do we limit the result set to the first 'n' 
rows and then how do we get the result set to start at row 'n + 1' and
return 'n' more rows? 

What we're looking to do is page through large result sets rather than
display them all at once.  When using SQL in Net.Data, we used the
START_ROW_NUM and RPT_MAX_ROWS variables to page through result sets.

Here's what we're doing to return a result set:

c/exec sql
c+ declare C1 cursor for
c+
c+ select *
c+ from filename
c+ where fieldname = :value
c+ order by fieldname
c+
c+ for fetch only
c/end-exec

c/exec sql
c+ open C1
c/end-exec

c/exec sql
c+ set result sets cursor C1
c/end-exec

Also, does anyone know if can we call an SQLRPGLE program that returns a
result set from a Java servlet, so we can use JSP's to format and display
the result set?

Thanks!

Mike E.
--
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.
--
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.