× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. August 2008

Re: AW: Returning Record Sets from SQLRPGLE Web Services - FollowUpQuestion on FETCH

Dyanmic is the correct choice, if the statement can change.

However, changes to WHERE and ORDER BY may not require the statement
to actually change...

Consider:

select * from mytable
where (:searchFld1 = ' ' or myFld1 = :searchFld1)
and (:searchFld2 = 0 or myFld2 = :searchFld2)

The above effectively gives you two different WHERE clauses.

Similarly, on the order by:

select * from mytable
order by case when :sort = 'NAME' then nameFld
when :sort = 'DATE' then char(dateFld)
else NULL
end

Note that the sort columns must have the same data type.

HTH,
Charles


On Wed, Aug 20, 2008 at 3:40 PM, Coyle, Stephen F. <SCoyle@xxxxxxxxxxxxx> wrote:
OK...
So is dynamic the correct choice only when the actual SQL clauses are
variable? For example the where or order by clause changes between
executions?

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

General rule:

ALWAYS USE STATIC, unless you have to use dynamic.

Corollary: If you have to use dynamic, use parameter markers and only
prepare it once if at all possible.

Note that the above rules apply to _ANY_ database system no matter
what language you're using.

I'm in the midst of fixing a .NET application that's using dynamic
statements to an Oracle DB when it doesn't need to.

HTH,
Charles


On Wed, Aug 20, 2008 at 1:45 PM, Coyle, Stephen F.
<SCoyle@xxxxxxxxxxxxx> wrote:
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.





--
Charles Wilt
--
Software Engineer
CINTAS Corp.
--
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.





--
Charles Wilt
--
Software Engineer
CINTAS Corp.
--
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.






As an Amazon Associate we earn from qualifying purchases.

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