Re: Stored Procedure

The following modification should work: > let j = 1; begin work; > foreach cur1 for select testschalter into i from import > if i > 100 and i < 1000 then > update import set testschalter = j where current of cur1;> commit work; begin work; > let j = j + 1; > end if > end foreach commit work Also, declare the update cursor with hold as all open cursors are implicitly closed whenever you do a commit work unless you declare them with hold. You need to do a begin work before you fetch the first row as you are fetching it for update. If your program logic/business rules allow it, you can speed things up a bit by doing a mod on j and only doing the inner commit/begin work pair every 10, 20 , 30 etc rows (i.e. if j mod 10 = 0 then commit begin end if) Jonathan Leffler wrote: > 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