RE: RPG SQL and Cursors

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.