RE: SQL performance question 1 -- RPG400-L

Using SQLSTT is much better than using SQLCOD. The reason is the values for SQLSTT are set by an outside body and are unlikely to change for a given error condition. SQLCOD is defined by IBM and they have made changes to the values returned for a given error condition in the past which I have been bitten by. Also, SQLCOD errors tend to be a little more generic than SQLSTT codes which can make troubleshooting a little more difficult.

Matt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Christen, Duane J.
Sent: Thursday, December 18, 2008 9:41 AM
To: 'RPG programming on the IBM i / System i'
Subject: RE: SQL performance question 1

And don't use "If uslessfield = 1" to determine if a record is found use "If sqlcod > 0 and sqlcod <> 100".

Duane Christen

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx
Sent: Thursday, December 18, 2008 8:25 AM
To: RPG programming on the IBM i / System i
Subject: Re: SQL performance question 1

SETLL would be better in this case...but if you want to use
the SQL for this (yuck) i'd use something like this

Exec SQL
Select 1 into :uselessfield from myfile where key = :hostvar;

Thanks,
Tommy Holden

From:
David FOXWELL <David.FOXWELL@xxxxxxxxx>
To:
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Date:
12/18/2008 08:12 AM
Subject:
SQL performance question 1
Sent by:
rpg400-l-bounces@xxxxxxxxxxxx

Hi,

A lot of our developers have recently been introducing SQL
into their RPG code.

We've had a discussion to see how SQL is being implemented.

A lot have replaced this kind of loop:

SETLL key file
dou %eof(file)
READE key file
if %eof
iter
endif

If condition
indicateur = *on
leave
endif
enddo

to see if a row exists, by this :

Select count(*) from myfile where key = : hostvar

I have advised :

Select key from myfile where key = : hostvar fetch first row only.

Is there a better way? Is this a standard way to see if a row
exists? I know there is EXISTS( ) but that would be the same
request (according to visual explain).

Does the RPG version still have any advantages?

Thanks.

--
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 email has been scanned by the MessageLabs Email Security System.
For more information please visit
http://www.messagelabs.com/email
______________________________________________________________________

NOTICE: This electronic mail transmission may contain confidential information and is intended only for the person(s) named. Any use, copying, or disclosure by any other person is strictly prohibited. If you have received this transmission in error, please notify the sender via e-mail.