AW: AW: SQL record exists

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

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