RE: RPG SQL and Cursors -- RPG400-L

You could make use of: -

This SQL WHENEVER statement defines the host language label to which control is passed if an SQLERROR (SQLCODE < 0) occurs in an SQL statement.

________________________________

From: rpg400-l-bounces@xxxxxxxxxxxx on behalf of hockchai Lim
Sent: Fri 19/11/2010 21:03
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: RPG SQL and Cursors

It makes a whole world of a different if that is a batch job that processing
millions of records. If programmer forgot to handle some unexpected SQL
error, the job just going to move on to the next statement. hhmmm, I
personally prefer it to wait for me to look at it first.

The basic problem is that RPG RLA default behaviour is: MSGW if unexpected
error is encountered. SQL default behaviour is: the programmer did not tell
me what to do, so I'm just going to move on to the next statement. I like
the RPG RLA default behavious better. My preference vs yours :).

"Alan Campin" <alan0307d@xxxxxxxxx> wrote in message
news:mailman.15556.1290200085.2702.rpg400-l@xxxxxxxxxxxxxxx

Just because no one checks it does not mean it should not be done. RPG
programmers just assume that nothing will go wrong and when it does there
is
nothing to deal with it.

The difference between SQL and RLA is that SQL will proceed on it's way if
an error occurs and you don't check the state. RLA will blow up. Most of
the
time that doesn't help much because the user just press enter until they
end
the program and never say a word. Unless you have code to check for
errors,
the fact that RLA blows up doesn't do you much good.

On Fri, Nov 19, 2010 at 1:21 PM, hockchai Lim
<lim.hock-chai@xxxxxxxxxxxxxxx

wrote:

That is back to what I've said
"So, a lot of time, I see a lot of code that programmer added to SQL/Java
program to just to handle this type of unexpected exceptions that
probably
will never occur. Great stuff, all kind of sophisticated exception
handling
code that will just make you get lost on what exactly the business
function
that the program is trying to achieve."

In RPG RLA, if I need to chain to a record:
FMYFILE UF E K DISK
/free

chain (key1) MYFILE;
... do something..

reade (key2) MYFILE;
... do something.

/end-free.

aaahhh, no exception trapping, how dare...... Well, truely, there is no
need to. The chance of it having problem is 0 to non and even if I'm
just
so bad luck that the program encounter problem because of
file-missing/record lock/damage/backup..), will the job is just going to
go
to MSGW.

Now: show me your sql version. My guess is that you will need to do some
exception trapping for it to work nicely. Yes, you can probably create
some
utility to call and get it to play nice, but the fact is that programmer
tends to forget to do that.

"Alan Campin" <alan0307d@xxxxxxxxx> wrote in message
news:mailman.15538.1290197494.2702.rpg400-l@xxxxxxxxxxxxxxx

<<When RLA/RPG encountered an unexpected error, the
program goes into MSGW and programmer can study the caused of problem
and
handle the error appropriately.>>

Except for a little problem called a user. The second they see the
error
they press enter and proceed on their happy way.

When SQL/Java encountered an unexpected

error, well, it either kills the entire process or keep trucking on

That assumes you do not check the SQLSTATE. If a programmer doesn't
check
SQSTATE after every SQL operation, then it is a poor programmer

On Fri, Nov 19, 2010 at 10:50 AM, hockchai Lim <
lim.hock-chai@xxxxxxxxxxxxxxx> wrote:

so, I take it that there is really no way for SQL update to handle the
same
thing that I've described in RLA. As for design problem with

interactive

job that has long lasting lock, well, I agreed that it is not the best
design. But record lock is a funny thing, you never know when it will
happen. But I do know that when it is time for you to about to sit
down
to
enjoy your thanksgiving dinner, baam, you get a call from the operator
about
a record lock problem.... Besides the application that I'm dealing
with
is
more than 15 years old.... Time for rewrite, sure, now who is going to
cough
out the money :).

But, here is the basic problem with using SQL vs RPG RLA or Java vs.
RPG
(Time to go to war): When RLA/RPG encountered an unexpected error,
the
program goes into MSGW and programmer can study the caused of problem

and

handle the error appropriately. When SQL/Java encountered an
unexpected
error, well, it either kills the entire process or keep trucking on
(Beautiful stuff, why wait for people, just more on).
So, a lot of time, I see a lot of code that programmer added to
SQL/Java
program to just to handle this type of unexpected exceptions that
probably
will never occur. Great stuff, all kind of sophisticated exception
handling
code that will just make you get lost on what exactly the business
function
that the program is trying to achieve.

I do love SQL and Java and I use them quite a bit. But RPG/RLA is
just
much
more... hm what is the word....

<dieter.bender@xxxxxxxxxxxx> wrote in message
news:mailman.15470.1290186950.2702.rpg400-l@xxxxxxxxxxxxxxx

I don't use bulk updates for transactional data, one transaction,
normaly
is a unit of work from one user and if it doesn't succeed, rollback
and
message to user. In a batch process, same thing: rollback , working

further

on and try it later (automatically). To get speed to Batch processes,
parallelisation is the main thing!!!
If an interactive Job helds long lasting record locks, you have a
problem
with your design; before each EXFMT or read of a 5250 just a
rollback
and
there will be no locks.

Dieter

--------------------------------------------------
From: "hockchai Lim" <lim.hock-chai@xxxxxxxxxxxxxxx>
Sent: Friday, November 19, 2010 5:37 PM
Newsgroups: midrange.rpg400-l
To: <rpg400-l@xxxxxxxxxxxx>
Subject: Re: RPG SQL and Cursors

agree that commitment control should be used. But how do you
resolve

the

lock problem. If you roll out the RBAR, and some one've locked the
very
last record that you want to update, how do you resolve this one

tinny

lock problem then? Also, if your bulk update takes a long time
(Say

more

that 30 secs) to complete, it could be locking up a lot of records

for

that 30 secs.

For a RLA, it is quite simple, the "Program Infor Feed back area"

will

tell you what job locks the record. So, if the job is an
interactive
job, the program can send a message to the user to get out of the
record
or his job will be killed in 1 min. For a batch job, the program

will

normally be coded to wait for it or error out and let programmer
decide
what to do. The programmer can look at joblog to tell what job is

causing

the lock and provide appropriate action. Now... how do you do that

using

sql update?

<dieter.bender@xxxxxxxxxxxx> wrote in message
news:mailman.15443.1290183748.2702.rpg400-l@xxxxxxxxxxxxxxx

... in OLTP database with concurrent read and write access to the
database, you would have the requirement of transaction isolation.
(You
should always use commit with SQL updates anyway!!!) The bigger
the
transaction gets, the slower it will be and if a SQL bulk
operation

dies

at the very last record, caused by a lock conflict, you would have

to

issue a rollback, if you need consistent data at every time.

Throwing

out RBAR is shooting a little bit short...

D*B

--------------------------------------------------
From: "hockchai Lim" <lim.hock-chai@xxxxxxxxxxxxxxx>
Sent: Friday, November 19, 2010 4:19 PM
Newsgroups: midrange.rpg400-l
To: <rpg400-l@xxxxxxxxxxxx>
Subject: Re: RPG SQL and Cursors

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.

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