AW: AW: SQL record exists

Hi,

clear is an RPG OPCode that initializes my variable isFound.

In my example I neither used an indicator variable nor checked the SQLCOD or
SQLSTT to determine if found or not, i.e. if a NULL value is returned or
not.
If a NULL value is returned, the Host variable stays unchanged, i.e. if
isFound was 1 before executing the SQL statement and no record was found (a
NULL value is returned) it will be still 1.
If I'd work with Indicator variables, the indicator variable will be set to
-1 (if a null value is returned).

If I initialize the variable before executing the SQL statement, it still is
unchanged if not found and I easily can check for isFound = 1.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Antonio
Fernandez-Vicenti
Gesendet: Sunday, 07. February 2010 14:54
An: Midrange Systems Technical Discussion
Betreff: Re: AW: SQL record exists

Luis, maybe I did not make it very clear in my question:
I already knew that " :variable" (with a colon) refers to a host (RPG)
variable.
My question was: (as from your example):
... where Number = :Number;
in this sentence Number and :Number refer to different variables: the
one from RPG (:Number) and the SQL variable (Number).
Don't they?

In Brigitta's post I was asking:

Clear IsFound;
Exec SQL Select 1 Into :iSFound
From MyFile
Where ....
Fetch first row only;


my question was relative to the variable being "cleared" IsFound without
colon
versus the receiving one in the Select ":iSFound" with colon...
TIA
----------------------------------------------------------------------------
----

Luis Rodriguez escribió:

Antonio,

In short, yes.

In embedded RPG SQL, when you prefix a variable with : it means that
variable belongs to the RPG program. For example, if I say:

EXEC SQL
Select 1 from my table
Where Number = :Number;

The First "Number" belongs to the SQL table. The second one to your

program.

HTH,

Luis Rodriguez
IBM Certified Systems Expert ? eServer i5 iSeries


On Sun, Feb 7, 2010 at 6:21 AM, Antonio Fernandez-Vicenti <
afvaiv@xxxxxxxxxxxxxx> wrote:

Birgitta Hauser escribió:

To avoid this problem enhance the statement as follows:

Clear IsFound;
Exec SQL Select 1 Into :iSFound
From MyFile
Where ....
Fetch first row only;

Sorry if I'm asking a stupid question, but... are the variables:
- "IsFound" being cleared by "Clear"
- and "*:*iSFound" (prefixed with "colon :"...) in the Select
referring to the same variable?
Sorry if I'm short in SQL knowledge...

--
Antonio Fernandez-Vicenti
afvaiv@xxxxxxxxxxxxxx

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.