Re: Stored Procedure

On Mon, 29 Jun 1998, Hofinger, Christian wrote: > [...stored procedure edited to remove tracing...] > > create procedure pr_update_import()> > define i,j INT; > > let j = 1; > foreach cur1 for select testschalter into i from import > if i > 100 and i < 1000 then > begin work; > update import set testschalter = j where current of cur1;> commit work; > let j = j + 1; > end if > end foreach > > end procedure > > ---------------------------------------------------------- > > 255 : not in transaction The problem is that when you open a cursor with a FOR UPDATE clause (and there is an implicit FOR UPDATE clause on this cursor -- witness the fact that you use WHERE CURRENT OF cur1), you must be inside a transaction. Further, you probably cannot use a WITH HOLD cursor (speculation, but a reasonable guess). Given this, you have two alternatives: 1. Place the BEGIN/COMMIT work statements around the FOREACH loop. 2. Replace the UPDATE WHERE CURRENT OF with a searched UPDATE. Advantage of 1: you change less code, but your transaction size grows. Advantage of 2: you keep the transactions tiny, but it may be less efficient. Either way, the IF clause should be removed and the condition placed on the WHERE clause of the SELECT statement. Also, because there is no ORDER BY on the cursor, there is no guarantee that the rows will arrive in any particular sequence. As long as this doesn't matter, you're OK. If you expect the rows to arrive in sorted order, you will be in for a rude awakening some day when the rows start arriving out of order. Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix -- see http://www.perl.com/CPAN