RE: Doing a SET with variables

Thanks again. GREAT lesson learned! Fortunately didn't destroy ALL my
information... :-(

Dave 

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, November 30, 2006 10:51 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Doing a SET with variables

Now we're talking about a "cursor"

exec sql declare c1 cursor for
select this, that, etc, from myfile
where this=:myselection;

exec sql open c1

exec sql fetch c1 into :this, :that, :etc; // your error trapping here
// your program logic here exec sql update myfile set ...
 where current of c1;

If you do a single row fetch, like
exec sql select this, that, etc into :this, :that, :etc from myfile
where mykey=:mykey;

Do not then "assume" sql will only update the row you last fetched if
you do a exec sql update myfile set this=:this, ...;



Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Turnidge, Dave" <DTurnidge@xxxxxxxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
11/30/2006 11:16 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
RE: Doing a SET with variables






Thank you. That is VERY helpful.

Next, regarding the WHERE clause. *IF* I have just "selected" the
record, and displayed it, and changed something on it, do I need a WHERE
clause? Won't I be updating the record I just retrieved?

Thanks again,

Dave 

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, November 30, 2006 10:06 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: Doing a SET with variables

First, you are missing a WHERE clause.  You better have one.

Dynamic SQL is great, but can impact performance.  Be nice to see if you
could do something like the following (which I haven't tried) (sample is
V5R4)

exec sql Update $AUDITSTG/SAVHISTORY
case FileFieldValue<>:HostFieldValue
  Set FileFieldValue=:HostFieldValue
...
where ...;

But, if it doesn't and you continue to use "dynamic" SQL (suggested
search words or important concepts are double quoted) then you'll need
to do one of the following two solutions

"PREPARE" a statement
"EXECUTE" a statement

or
"EXECUTE IMMEDIATE" a statement

like

exec sql prepare stmt1 from :SelectTwo;
exec sql execute stmt1;

or

exec sql execute immediate :SelectTwo;


Rob Berendt