BUG: Can't have transaction in foreach loop?
I have one stored procedure that contains a foreach loop that calls a
second stored procedure. If the second procedure contains a BEGIN
WORK...COMMIT WORK block, then the foreach loop in the outer procedure
only executes once. I do not get any errors, the loop just seems to
ignore the other ~7000 items it is supposed to process. If I comment
out the BEGIN WORK, COMMIT WORK lines, then everything works as
expected. There is no other transaction in force at the time.
I can't find any docs saying that this is the expected behavior and I
see plenty of examples of SPs that contain transactions, so I am
assuming this is a bug?
We are running 7.30.UC7 on Linux.
e.g:
create procedure bar(a integer):
--BEGIN WORK
:
--COMMIT WORK
end procedure;
create procedure foo():
foreach select a from bigtable into b
-- if the BEGIN WORK/END WORK are not commented out in bar
-- then this loop just executes once, otherwise 7000 times.
begin
:
call bar(b);
:
end
end foreach
:
end procedure;
Sent via Deja.com http://www.deja.com/
Before you buy.
You need to use the WITH HOLD clause in the FOREACH statement in order
for the cursor it creates to remain open across transactions. e.g.:
foreach with hold select a from bigtable into b
HTH,
--
Irwin Goldstein
Objective Software Systems, Inc.
http://www.objectsoft.com
In article <8ap4ej$dkn$1@nnrp1.deja.com>,
kevine@my-deja.com wrote:
> I have one stored procedure that contains a foreach loop that calls a
> second stored procedure. If the second procedure contains a BEGIN
> WORK...COMMIT WORK block, then the foreach loop in the outer procedure
> only executes once. I do not get any errors, the loop just seems to
> ignore the other ~7000 items it is supposed to process. If I comment
> out the BEGIN WORK, COMMIT WORK lines, then everything works as
> expected. There is no other transaction in force at the time.
>
> I can't find any docs saying that this is the expected behavior and I
> see plenty of examples of SPs that contain transactions, so I am
> assuming this is a bug?
>
> We are running 7.30.UC7 on Linux.
>
> e.g:
>
> create procedure bar(a integer)> :
> --BEGIN WORK
> :
> --COMMIT WORK
> end procedure;
>
> create procedure foo()> :
> foreach select a from bigtable into b
> -- if the BEGIN WORK/END WORK are not commented out in bar
> -- then this loop just executes once, otherwise 7000 times.
> begin
> :
> call bar(b);
> :
> end
> end foreach
> :
> end procedure;
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Sent via Deja.com http://www.deja.com/
Before you buy.