|
midrange.com
FETCH statement positioning keywords:
Keyword Positions cursor
Next On the next row after the current row
Prior On the row before the current row
First On the first row
Last On the last row
Before Before the first row (used to reset the cursor position to the
beginning without retrieving any data)
After After the last row (used to reset the cursor position to the
end)
Current On the current row (no change in position)
Relative n n < -1 positions to nth row before current
N = -1 Same as Prior keyword
N = 0 Same as Current keyword
N = 1 Same as Next keyword
N > 1 Positions to nth row after current
âno rowâ condition on a Fetch : SQLState = â02000â)
Note: Changing the value of a host variable has no effect on the cursor
until the cursor is closed and reopened. By closing & reopening a curson,
you can use the same cursor to retrieve different sets of rows in the same
program execution.
Basic logic for sequentially processing all rows of an SQL cursor:
Open cursor
If error
Handle error
Quite
Endif
While more rows and no error
Fetch next row
If normal return condition
Process current row
Else if âno rowâ condition
Do nothing(end lood)
Else if other warning condition
Handle warning(continue or end loop)
Else
Handle error(end loop)
Endif
Endwhile
Close cursor
If error
Handle error
endif
Roll back example:
FETCH RELATIVE -20 FROM MYCSR INTO :empiem
Thank you,
Karen Hodge
Senior System Analyst
Genesys Health System
1000 Healthpark Blvd, Grand Blanc, Mi 48439
Office 810.606.5180, Fax 810.606.7204
khodge@xxxxxxxxxxx
rpg400-l-request@
midrange.com
Sent by: To
rpg400-l-bounces@ rpg400-l@xxxxxxxxxxxx
midrange.com cc
Subject
10/13/2004 12:41 RPG400-L Digest, Vol 3, Issue 782
PM
Please respond to
rpg400-l@midrange
.com
Send RPG400-L mailing list submissions to
rpg400-l@xxxxxxxxxxxx
To subscribe or unsubscribe via the World Wide Web, visit
http://lists.midrange.com/mailman/listinfo/rpg400-l
or, via email, send a message with subject or body 'help' to
rpg400-l-request@xxxxxxxxxxxx
You can reach the person managing the list at
rpg400-l-owner@xxxxxxxxxxxx
When replying, please edit your Subject line so it is more specific
than "Re: Contents of RPG400-L digest..."
Today's Topics:
1. Re: subfile page up & down using embedded SQL (Richard ECUYER)
2. RE: subfile page up & down using embedded SQL (Fisher, Don)
3. RE: "Meet the AD developers" at COMMON (Bob Cozzi)
4. RE: subfile page up & down using embedded SQL (Holden Tommy)
5. RE: Trying to use my procedure from a pgm complied with
CRTBNDRPG (Bob Cozzi)
6. Re: subfile page up & down using embedded SQL (joe)
7. RE: subfile page up & down using embedded SQL (Dan Bale)
8. RE: subfile page up & down using embedded SQL (Bruce Guetzkow)
9. RE: subfile page up & down using embedded SQL
(Wayne.James@xxxxxxxxxxxx)
10. RE: Suggestion: File to use when compile (Scott Klement)
----------------------------------------------------------------------
message: 1
date: Wed, 13 Oct 2004 17:27:36 +0200
from: "Richard ECUYER" <recuyer@xxxxxxx>
subject: Re: subfile page up & down using embedded SQL
hi,
i think that like NEXT, PRIOR, you can use BEFORE and AFTER with the
appropriate values stored in the subfile.
I have no template for this, sorry.
----- Original Message -----
From: "Dan Bale" <dbale@xxxxxxxxxxxxx>
To: <RPG400-L@xxxxxxxxxxxx>
Sent: Wednesday, October 13, 2004 5:09 PM
Subject: subfile page up & down using embedded SQL
> Esteemed listers:
>
> I thought I had seen references to this before, but I am coming up empty
> searching the archives both from the search page and from google.
>
> I have a subfile inquiry app that currently loads the subfile a page at a
> time via SETLL and a READ loop. Paging works as expected; Page Down gets
> the next set of records following the one appearing at the bottom of the
> current page, and Page Up gets the set of records prior to the one
appearing
> at the top of the current page. There is also a "Position To:" entry at
the
> top that invokes the SETLL.
>
> I am attempting to introduce new features that require SQL FETCH to
replace
> the SETLL, READ, and READP operations. (Note, FWIW, this is _not_ READE
or
> READPE.) Well, not really sure how to replace the SETLL part. I think I
> understand that I would use FETCH NEXT to emulate READ and FETCH PREVIOUS
to
> emulate READP.
>
> I think I would know how to handle the Page Down routine --- just do
another
> iteration of FETCH NEXT for a new page. But the Page Up routine, this
one's
> a bugger. Since I load a subfile page from top to bottom, the SQL cursor
is
> positioned at the record that appears at the bottom of the subfile. In
> normal RPG I/O, when a Page Up is requested, I would just SETLL using the
> first subfile record, and READP from there. I'm just not getting how I
> would accomplish this using embedded SQL.
>
> And if someone has a template that shows how to code a inquiry-only
subfile
> using SQL and is willing to share it, all the better.
>
> FWIW, References I've found thus far:
> iSeriesNetwork: Paul Conte's "SQL Crib Sheet for RPG Programmers", March
> 2000, article #3326
> David Morris: http://archive.midrange.com/midrange-l/199911/msg01499.html
>
> tia,
> db
>
> --
> 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.
>
>
------------------------------
message: 2
date: Wed, 13 Oct 2004 11:46:04 -0400
from: "Fisher, Don" <Dfisher@xxxxxxxxxxxxxxxxx>
subject: RE: subfile page up & down using embedded SQL
I execute a COUNT function to determine the number of records in the file
before the "Position to" value and use FETCH RELATIVE from the beginning of
the file.
Donald R. Fisher, III
Project Manager
Roomstore Furniture Company
(804) 784-7600 extension 2124
DFisher@xxxxxxxxxxxxx
<clip>
The "position to" will be the hard part since I'm going to guess that the
user will enter something that's meaningful to them (such as an order or
part number) and not the record number in your cursor. You may need to read
through all the rows in the cursor to do this. Of course, if the field just
means "skip ahead n records", then FETCH RELATIVE will work just fine for
that too.
<clip>
------------------------------
message: 3
date: Wed, 13 Oct 2004 10:46:57 -0500
from: "Bob Cozzi" <cozzi@xxxxxxxxx>
subject: RE: "Meet the AD developers" at COMMON
Barbara,
Is "Information Development" what was called "ID" in the past? That is,
manuals & document writers?
-Bob
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Barbara Morris
Sent: Wednesday, October 13, 2004 10:21 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: "Meet the AD developers" at COMMON
If you're coming to the upcoming COMMON in Toronto, don't miss the "meet
the developers" session on Monday at noon (12:30 - 1:45, session 24AC).
George Farr (RPG/RSE development manager) will be moderating the
session, and some other IBM managers will also attend and can take some
questions.
The following developers will be on the panel. Other developers will
also be attending (in the audience, but available for questions)
Alisa L Morse Raleigh HATS
Barbara Morris Toronto RPG
Don Yantzi Toronto RSE
George Voutsinas Toronto Java
Larry Schweyer Toronto Web tooling
Melanie Steckham Toronto Information development
Satish Gungabeesoon Toronto Web facing
Vadim Berestetsky Toronto Debugger
--
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.
------------------------------
message: 4
date: Wed, 13 Oct 2004 10:48:57 -0500
from: Holden Tommy <Tommy.Holden@xxxxxxx>
subject: RE: subfile page up & down using embedded SQL
fetch relative negative (SFLPAG+currentRRN)
Thanks,
Tommy Holden
-----Original Message-----
From: Wayne.James@xxxxxxxxxxxx [mailto:Wayne.James@xxxxxxxxxxxx]
Sent: Wednesday, October 13, 2004 10:33 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: subfile page up & down using embedded SQL
The "head splitting" part of this for an old RPGer is that RELATIVE does
indeed move the cursor "back". But then the retrieval is done "forward".
If SFLPAG = 10, you will want to FETCH RELATIVE -20. The cursor will then
be left on the record after the last one displayed in the subfile.
And then there is beginning of file to consider...
L. Wayne James
Senior Developer
Agilysys, Inc.
Enterprise Solutions Group
Hospitality Solutions
11545 Wills Road
Alpharetta, GA 30004
Phone: 770-962-6425 x1252
Email: Wayne.James@xxxxxxxxxxxx
"Fisher, Don" <Dfisher@xxxxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
10/13/2004 11:22 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
To
"'RPG programming on the AS400 / iSeries'" <rpg400-l@xxxxxxxxxxxx>
cc
Subject
RE: subfile page up & down using embedded SQL
Check out the RELATIVE key word for FETCH. You can Fetch RELATIVE -10 to
move ten records backward. Substitute your subfile page size for the 10
and
it should work.
Hope that helps.
Donald R. Fisher, III
Project Manager
Roomstore Furniture Company
(804) 784-7600 extension 2124
DFisher@xxxxxxxxxxxxx
<clip>
In normal RPG I/O, when a Page Up is requested, I would just SETLL using
the
first subfile record, and READP from there. I'm just not getting how I
would accomplish this using embedded SQL.
<clip>
--
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.
------------------------------
message: 5
date: Wed, 13 Oct 2004 10:49:42 -0500
from: "Bob Cozzi" <cozzi@xxxxxxxxx>
subject: RE: Trying to use my procedure from a pgm complied with
CRTBNDRPG
>>Awesome!!! Got a compile... Thanks!
>>BTW... I was in your class at Common in Orlando last year.
Glad it worked. FYI, I'm returning to COMMON in Toronto next week. Well
sort
of. I am doing one of their pre-conference workshops: "RPG for the Web"
session on Sunday.
Anyone interested in signing up for it, feel free to do so between now and
then.
-Bob Cozzi
------------------------------
message: 6
date: Wed, 13 Oct 2004 11:06:18 -0500
from: "joe" <as400@xxxxxxx>
subject: Re: subfile page up & down using embedded SQL
In addition to the RELATIVE mentioned here, you might consider
having the system maintain the subfile page-up i.e. sflsize = sflpage + 1.
But then your 'Position to. . .' will always start at the top.
joeM
----- Original Message -----
From: "Dan Bale" <dbale@xxxxxxxxxxxxx>
To: <RPG400-L@xxxxxxxxxxxx>
Sent: Wednesday, October 13, 2004 10:09 AM
Subject: subfile page up & down using embedded SQL
> Esteemed listers:
>
> I thought I had seen references to this before, but I am coming up empty
> searching the archives both from the search page and from google.
>
> I have a subfile inquiry app that currently loads the subfile a page at a
> time via SETLL and a READ loop. Paging works as expected; Page Down gets
> the next set of records following the one appearing at the bottom of the
> current page, and Page Up gets the set of records prior to the one
appearing
> at the top of the current page. There is also a "Position To:" entry at
the
> top that invokes the SETLL.
>
> I am attempting to introduce new features that require SQL FETCH to
replace
> the SETLL, READ, and READP operations. (Note, FWIW, this is _not_ READE
or
> READPE.) Well, not really sure how to replace the SETLL part. I think I
> understand that I would use FETCH NEXT to emulate READ and FETCH PREVIOUS
to
> emulate READP.
>
> I think I would know how to handle the Page Down routine --- just do
another
> iteration of FETCH NEXT for a new page. But the Page Up routine, this
one's
> a bugger. Since I load a subfile page from top to bottom, the SQL cursor
is
> positioned at the record that appears at the bottom of the subfile. In
> normal RPG I/O, when a Page Up is requested, I would just SETLL using the
> first subfile record, and READP from there. I'm just not getting how I
> would accomplish this using embedded SQL.
>
> And if someone has a template that shows how to code a inquiry-only
subfile
> using SQL and is willing to share it, all the better.
>
> FWIW, References I've found thus far:
> iSeriesNetwork: Paul Conte's "SQL Crib Sheet for RPG Programmers", March
> 2000, article #3326
> David Morris: http://archive.midrange.com/midrange-l/199911/msg01499.html
>
> tia,
> db
>
> --
> 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.
>
------------------------------
message: 7
date: Wed, 13 Oct 2004 12:06:33 -0400
from: "Dan Bale" <dbale@xxxxxxxxxxxxx>
subject: RE: subfile page up & down using embedded SQL
AWESOME! Can I presume that a "FETCH RELATIVE -20" is more efficient than
doing a "FETCH PRIOR" in a loop 20 times? And, just to clarify, is it
RELATIVE to the access path defined by the ORDER BY? (And _not_ by RRN?)
What happens if it causes the cursor to hit BOF (or EOF when using RELATIVE
+nn)?
Thanks!
db
------------------------------
message: 8
date: Wed, 13 Oct 2004 11:22:13 -0500
from: "Bruce Guetzkow" <bruceg@xxxxxxxxxxxxxxxxxxxxxxx>
subject: RE: subfile page up & down using embedded SQL
The difficult part is the Position-To. I have used a variation on a
(manual) binary search using FETCH RELATIVE and specifying +/- values,
then comparing the returned value to the position-to value...cumbersome
at best. Also, you need to check for top/bottom of cursor.
Bruce Guetzkow
Information Services Director
United Credit Service, Inc.
Phone: 262-723-2902
Fax: 262-723-5568
Email: bruceg@xxxxxxxxxxxxxxxxxxxxxxx
Web: www.unitedcreditservice.com
------------------------------
message: 9
date: Wed, 13 Oct 2004 12:30:31 -0400
from: Wayne.James@xxxxxxxxxxxx
subject: RE: subfile page up & down using embedded SQL
I use block fetches when I do this kinda stuff.
So it will perform MUCH better than single FETCH PRIOR in a loop.
The RELATIVE is indeed in the order of the (opened) access path.
I have had poor experiences with hitting BOF and EOF. The FETCH that is
suppose to "start over" did not. I ended up working around the issue. If
I had the time, I would chase IBM with a stick over how it did not work.
*SIGH*
But do not let my poor experience deter you from your attempt. What you
are attempting is WONDERFUL, once you get it working.
L. Wayne James
Senior Developer
Agilysys, Inc.
Enterprise Solutions Group
Hospitality Solutions
11545 Wills Road
Alpharetta, GA 30004
Phone: 770-962-6425 x1252
Email: Wayne.James@xxxxxxxxxxxx
"Dan Bale" <dbale@xxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
10/13/2004 12:06 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc
Subject
RE: subfile page up & down using embedded SQL
AWESOME! Can I presume that a "FETCH RELATIVE -20" is more efficient than
doing a "FETCH PRIOR" in a loop 20 times? And, just to clarify, is it
RELATIVE to the access path defined by the ORDER BY? (And _not_ by RRN?)
What happens if it causes the cursor to hit BOF (or EOF when using
RELATIVE
+nn)?
Thanks!
db
--
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.
------------------------------
message: 10
date: Wed, 13 Oct 2004 11:41:34 -0500 (CDT)
from: Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>
subject: RE: Suggestion: File to use when compile
Maybe you should take it one step further and make it something that can
execute *ANY* CL command.
/PRECMD: OVRDBF FILE(CUSTMAS) TOFILE(FOO)
/PRECMD: SNDMSG MSG('SOMEONE RECOMPILED THE TAX CALC PGM!') TOUSR(SCOTT)
/POSTCMD: DLTOVR FILE(CUSTMAS)
Seems like it should be relatively simple for the RPG people to have the
compiler call QCMDEXC (or system() or QCAPCMD, whatever) before compiling
the program -- and this would give you a lot of flexibility.
---
Scott Klement http://www.scottklement.com
On Wed, 13 Oct 2004, Lim Hock-Chai wrote:
> yes, for that particular parm. The suggestion is really more focus on a
flexible compiler directive that can override any parm in the create
command without us have to wait for new keyword.
>
> for example:
> If I'm compiling a PF using CRTPF. I would want to be able to have the
following in my DDS
> /OVRPARM MAXMBRS(5) SIZE(*NOMAX)
>
> If I'm compiling a PRTF usign CRTPRTF. I would want to be able to have
the following in my DDS
> /OVRPARM PAGESIZE(68 80)
>
> If IBM add a new parm to CRTBNDRPG command. I would want to be able to
have the following in RPG source
> /OVRPARM NEWPARM(VALUE)
>
> The main objective would be a compiler directive that is flexiable enough
that developer would not need to wait for new keyword to be added.
>
>
>
>
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Barbara Morris
> Sent: Tuesday, October 12, 2004 7:12 PM
> To: rpg400-l@xxxxxxxxxxxx
> Subject: Re: Suggestion: File to use when compile
>
>
> Lim Hock-Chai wrote:
> >
> > I like the idea of using keyword. Compiler executing any command in
the program source sound a bit scary for me. However, it would be nice if
ibm can also add a compiler directive (/OVRPARM) to override parm values.
For example:
> >
> > /IF DEFINED(*CRTBNDRPG)
> > /OVRPARM BNDDIR(STDBNDDIR)
> > /ENDIF
> >
>
> What you specify on the H spec already overrides the parm values.
>
> /IF DEFINED(*CRTBNDRPG)
> H BNDDIR(STDBNDDIR)
> /ENDIF
>
> (By the way, BNDDIR is valid for CRTRPGMOD too.)
>
> --
> 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.
>
------------------------------
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) digest 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.
End of RPG400-L Digest, Vol 3, Issue 782
****************************************
As an Amazon Associate we earn from qualifying purchases.
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.
