Re: RPG SQL and Cursors -- RPG400-L

The only issue I have with using one SQL update statement to update multiple
rows is that there is no way to handle record lock easily. So, for a one
time deal, may be is a good idea. For a production process that gets run
thru out the day, it could become a headache to handle when encountering
recordlock.

<Clip>
"Charles Wilt" <charles.wilt@xxxxxxxxx> wrote in message
news:mailman.15339.1290142252.2702.rpg400-l@xxxxxxxxxxxxxxx
Scott,

You could declare a cursor that selects all items, fetch the data from
the cursor and the update the row through the cursor. The other way
is to simply issue use a single UPDATE statement.
</Clip>
Charles

On Thu, Nov 18, 2010 at 3:56 PM, Scott Klement
<rpg400-l@xxxxxxxxxxxxxxxx> wrote:

hi Charles,

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.

If this is what you're trying to express, the phrase "if you're
using a cursor you're probably doing something wrong" is not a good way
to express it! It just confuses the issue.

Look at all the confusion it's already caused.

1) People thinking they have to use native I/O anytime a cursor would be
involved! (Whey might even insert to a temp file from SQL, then read
the temp file via F-specs... )
2) People thinking they should use CLI instead of embedded.
3) People thinking they should never use a cursor.

A better approach might be to say something like:

Don't use is a cursor if:
1) You only plan to read a single row, then close it.
2) Your program doesn't need the data (i.e. reading the file purely in
order to write/update something.)

Do use a cursor if:
1) You need to read a list of rows into your program (for example: build
a subfile, print a report, generate a spreadsheet, build a web page.)

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

Every tool has good and bad uses. That doesn't make cursors "bad"!

For example, I can use a (big) wrench to pound in nails if I want to.
But often times, it screws up. I shouldn't say "if you use a wrench,
you're doing it wrong" that would be misleading when taken out of
context. Even worse would be "wrenches are bad." Instead, I should say
something like "don't use a wrench to do a hammer's job."

So...
1) Don't use a cursor where SELECT INTO or VALUES INTO would do.
2) Don't use a cursor where UPDATE or INSERT (possibly with a subselect)
would do.

Grrr... who put that soap box under my feet?!
--
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.