RE: AW: Returning Record Sets from SQLRPGLE Web Services - Follow UpQuestion on FETCH

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.