× 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. November 2010

Re: RPG SQL and Cursors

Elbert,

It seems strange to me to just return the key with SQL then read in
the data with chain, why don't you just select the fields you need and
return them in one shot?

I assume that since this is a search, you usually return more than a
single record?

In that case, I'd usually FETCH xx ROWS at a time where xx is some
multiple of the number of records I can display at a time. Each row
fetched would contain all the data I needed, no additional i/o's
required.

As far as updates, again, it seems very strange to mix your i/o
methods like this. You really want to try to UPDATE the set of rows
in one shot.

If you'd care to give an example where you felt you had to do it the
way you describe, perhaps we can offer alternatives.

HTH,
Charles


On Fri, Nov 19, 2010 at 9:32 AM, Elbert Cook <elbert@xxxxxxxxxxxxxxx> wrote:
Kinda off topic, I'll ask here anyway.

Our Open Order Inquiry screen has dozens of search fields. Years ago we had
dozens of logical files embedded in our F-Specs. We haves since abandoned
all logical F-Specs leaving only the physical file in the F-Specs. I use SQL
to build a custom selection based on the search criteria.

In a Fetch :Into loop, I return 1 field (column) only, the Order number. I
then use the Order number to chain to the physical order header and order
detail files.

Normally if updating fields and using SQL I use the SQL UPDATE.

However on rare occasion for specific task, I use the prior method to
update. SQL returns the key value, then chain with the key value and update
the physical.

Is my approach an efficient use of SQL.

Elbert


-----Original Message-----
From: Charles Wilt [mailto:charles.wilt@xxxxxxxxx]
Sent: Friday, November 19, 2010 12:57 AM
To: RPG programming on the IBM i / System i
Subject: Re: RPG SQL and Cursors

Aaron,

When it comes to displaying or printing, you're pretty much stuck with
working row by row...at least as long as you're dealing with 5250
screens or RPG reports.  GUI's or report writers get to deal with
sets.  Even with those, is there a cursor involved?  Sure, but at
least it's internal to the DBMS.

Jeff Moden, the guy who coined the term RBAR, says the following:
"First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of
what you want to do to a column."

To put it another way, if you're thinking "for each record, that meets
such and such criteria, do this..." you're likely to end up with a
cursor or other non-set based solution.

Charles


On Thu, Nov 18, 2010 at 4:18 PM, Aaron Bartell <aaronbartell@xxxxxxxxx>
wrote:
Now you have me curious.  What do you do with a set of records as a whole
vs. process them row by row?  It would seem to me that at some point a row
needs to be processed by iteself.

Aaron Bartell
www.MowYourLawn.com/blog
www.OpenRPGUI.com
www.SoftwareSavesLives.com



On Thu, Nov 18, 2010 at 2:23 PM, Charles Wilt
<charles.wilt@xxxxxxxxx>wrote:

Surprisingly, nobody's thrown my name out here yet...but "if you're
using a cursor you're probably doing something wrong" is definitely a
phrase I've thrown out here many times.

The point being SQL is designed for sets or records, but to many
people do things row by row since that what they are used to.

It's not just our platform, happens on other too.  One of the MS SQL
Server guru's coined the term RBAR - Row By Agonizing Row....pherhaps
I'll steal it :)

Sometimes cursors are the right or the only way to do something...but
too often they are a poor choice.

In any event, like Scott, I'm confused as to what you're asking.
SQL-CLI doesn't really factor into the use or non-use of cursors.

What do you want to do?

Charles

On Thu, Nov 18, 2010 at 12:34 PM, Jeff Young <cooljeff913@xxxxxxxxx>
wrote:
I have seen it indicated in many posts in the past that if you are
using
cursors
in SQL, you are doing something wrong.
If I have only RPGLE and CL to work with and SQL-CLI is not an option,
how would
I select multiple rows for a set of conditions and return them in an
ordered
condition using embedded SQL with a CURSOR?

Thanks,

Jeff Young
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical
Solutions
V5R2
IBM  Certified Specialist- e(logo) server i5Series Technical Solutions
Designer V5R3
IBM  Certified Specialist- e(logo)server i5Series Technical
Solutions Implementer V5R3
--
This is the RPG programming on the IBM i / System i (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 IBM i / System i (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 IBM i / System i (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 IBM i / System i (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 IBM i / System i (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 ...
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.