Hello
I'm trying to avoid an 'infinite loop' when I use "with hold" i'm updating 24000 records.

I try to use this stored procedure, but don't work. The error message is
255: Not in transaction.

What's is the problem?

CREATE PROCEDURE foreach_ex()
define varExtremo_a char(40);
define varTecnologia char(10);
FOREACH cur1 FOR
SELECT extremo_a INTO varExtremo_a
FROM vunif_temp
WHERE Tecnologia = 'WLL'
IF varExtremo_a is null THEN
UPDATE vunif_temp
SET extremo_a = 'xx'
WHERE CURRENT OF cur1;
END IF
END FOREACH

END PROCEDURE;

An example can be readed in
http://www.dbcenter.cise.ufl.edu/tri...fm.html#125314

Hi,

Please test this

CREATE PROCEDURE foreach_ex()
define varExtremo_a char(40);
define varTecnologia char(10);

on exception set ....
..........
end exception

FOREACH cur1 with hold FOR
SELECT extremo_a INTO varExtremo_a
FROM vunif_temp
WHERE Tecnologia = 'WLL'
IF varExtremo_a is null THEN
begin work;
UPDATE vunif_temp
SET extremo_a = 'xx'
WHERE CURRENT OF cur1;
if sqca.sqlcode = 0 then
commit work
else
rollback work
end if;
else
continue foreach
END IF
END FOREACH

Gustavo.

Hi,

Excuse me, i remember that you can't declare with hold whit SELECT FOR UPDATE and can't use WHERE CURRENT OF...
I think that you must change sentence UPDATE add clausule WHERE with primary key.

Gustavo.

why not just add ;

AND extremo_a = NULL

to your cursor and use a unique (primary) key

OR
can you just use SQL


UPDATE vunif_temp
SET extremo_a = 'xx'
WHERE Tecnologia = 'WLL'
AND extremo_a is null

Hi,

I can't check the documentation right now, but I'm pretty sure that you need to open a transaction for the named cursors inside the stored procedures to work properly. But I'd recomend not to begin/commit it in every loop: it will be the best way to kill your server performance. It will work better if you open the tx, do the job, and commit/rollback the transaction at the end (you can keep a variable with the results, for instance).

Check it. I'm writing from what I remember, and it could be not too accurate.

And my opinion is that the update statement that 'nog' has written will be the best and fastest way to do your job, if you can use it.

Hope it helps.