Thanks Charles.
One more question....I'm obviously confused as to when I should use
dynamic or static statements.
Is there a general rule as to when to use which technique? It appears
the problems each solve overlap a bit.

Thanks...
- Steve

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Wednesday, August 20, 2008 1:13 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: AW: Returning Record Sets from SQLRPGLE Web Services -
Follow UpQuestion on FETCH

Doesn't look like that's supported.

Not a big deal since you're already (uneccessaily) using dynamic SQL.
As it turns out you do need dynamic SQL for this statement.

d SelectSQL s 400a

/free
selectSQL = 'select * '
+ 'from (select * '
+ ' from ( select DiCono, '
+ ' DiBstc, Didl01'
+ ' from F5301 '
+ ' where DiMcu = ' + InMCU
+ ' order by DiCono '
+ ' fetch first ' +
%char(nGetRecs) + ' rows only '
+ ' ) t1 '
+ ' order by DiCono desc '
+ ' fetch first ' + %char(InRecsPerPage)
+ ' rows only '
+ ' ) t2 '
+ ' order by DiCono ';

/end-free

Since you have to reprepare this for every page, I went ahead and got
rid of the parameter marker for DiMcu.

Just as an FYI, if varibles and/or parameter markers had worked then
you should have used static SQL for this task:

exec SQL
declare cursor C1 for
select *
from (select *
from ( select DiCono,
DiBstc, Didl01
from F5301
where DiMcu = :InMCU
order by DiCono
fetch first :nGetRecs rows only '
) t1
order by DiCono desc
fetch first :InRecsPerPage rows only
) t2
order by DiCono;

exec SQL
open C1


HTH,
Charles



On Wed, Aug 20, 2008 at 10:36 AM, Coyle, Stephen F.
<SCoyle@xxxxxxxxxxxxx> wrote:
I know this thread has gone off on a bit of a tangent but I have a
follow up to the original question.

The SQL statement posted to allow the paging works fine when run from
within an SQL session. When I embed it in the RPG program it does not
like my using a parameter value for the number of rows to fetch.
"Token ? was not valid. Valid tokens: ROW ROWS."

Is a variable not valid for FETCH ROWS? If not, can anyone point me in
the right direction. Thanks again.

Here is what I have:

D SelectSQL S 400A Inz('select * -

D from (select * -

D from ( select
DiCono,-

D DiBstc,
Didl01
-
D from F5301 -

D where DiMcu =
?
-
D order by
DiCono
-
D fetch first ?
-

D Rows Only -

D ) t1 -

D order by DiCono desc
-

D fetch first ? rows
only
-
D ) t2 -

D order by DiCono')


EXEC SQL
Prepare S1 from :SelectSQL ;

EXEC SQL
Declare C1 Cursor For S1 ;

EXEC SQL
Open C1 Using :InMCU, :nGetRecs, :InRecsPerPage ;

EXEC SQL
Fetch C1 for :InRecsPerPage Rows Into :OutDATA ;

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Walden H. Leverich
Sent: Wednesday, August 20, 2008 9:50 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: AW: Returning Record Sets from SQLRPGLE Web Services

OK, I'm confused then. Are you saying that the example program would
no
longer run, or that it would continue to run, but would not have
access
to the newly added column? The former disturbs me, but the latter
makes
sense, and seems ok since it's unlikely my program would know what to
do
with this new column since it wasn't there when the program was last
compiled.

-Walden

--
Walden H Leverich III
Tech Software
(516) 627-3800 x3051
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)
--
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 thread ...

Follow-Ups:
Replies:

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

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