Re: 4GL - confused about update cursors, transactions, "with hold" and best way to carry out a specific task
Paul Roberts wrote in message <68enff$cuc@news.informix.com>...
>I'm a bit confused over the conditions about what can and cannot happen
>within a transaction, ought my cursor be "WITH HOLD", and so on. My DB is
>non-MODE ANSI but has transactions. My manual suggests that "each update...
>must take place within a transaction". I feel that I don't really want to
>use transactions here - and certainly I don't want to put the entire
>FOREACH within a transaction, as there will be many many updates and I'll
>get a long transaction error.
1. If you can lock your table in share mode, do this:
lock table my_table in share mode
declare c1 cursor for
select data1, data2, data3,data4, data5
from my_table
foreach c1 into p_data1, p_data2, p_data3, p_data4, p_data5
[compute p_result based on these values and DB lookups]
update my_table
set result = p_result
where data1 = p_data1
and ...
and data5 = p_data5
[do exception check]
end foreach
2. otherwise:
# you have declare it as "with hold", otherwise "commit work"
# or "rollback work" will close this cursor
declare c1 cursor with hold for
select data1, data2, data3,data4, data5
from my_table
foreach c1 into p_data1, p_data2, p_data3, p_data4, p_data5
begin work
beclare c2 cursor for
select data1, data2, data3, data4, data5
where data1 = p_data1
and ...
and data5 = p_data5
for update of result
open c2
fetch c2
[do exception check, roll back if neccessary]
[compute p_result based on these values and DB lookups]
update my_table
set result = p_result
where current of c2
[do exception check, roll back if neccessary]
commit work
end foreach
3. In most case, it is good to have a serial field in a table. Assume the
serial field of your table is myid:
declare c1 cursor with hold for
select myid from my_table
foreach c1 into p_myid
begin work
beclare c2 cursor for
select * where myid = p_myid
for update of result
open c2 fetch c2 into p_myrecord.*
[do exception check, roll back if neccessary]
[compute p_myrecord.result based on these values and DB lookups]
update my_table
set result = p_myrecord.result
where current of c2
[do exception check, roll back if neccessary]
commit work
end foreach>
>If there is a problem using an update cursor in this situation then I could
>always do without it by issuing my update statement along the lines of:-
>
> UPDATE my_table
> SET result = p_result
> WHERE data1 = p_data1
> AND ...
> AND data5 = p_data5
>
>Would that be much slower? I could always add a serial column to the table
>and use that as the basis for the update - or I could use rowid (but only
>if the table isn't fragmented, yes)?
Slower is still minor, serial column will definaly help. BUT, you will get
into problem if other process updated one of the key value after you fetch
the record and before you update it. That will be the major problem unless
you can ensure your program is the only process that will update this table.
We do not do that if there is an alternative way.
Holp it helps.
Happy New Year!
Ken