Re: begin works and commit works in storeprocs

rudolph@aqumen.co.za (Rudolph Terblanche) wrote: :I am new to informix .We are working on informix 7.1. :I want to know if I can begin work and commit inside a storeproc. :Before you answer here is the problem.It is in a foreach loop.I :want to process transactions that I get with the foreach select :statement and these transactions is in updates in 5 different places :that must be committed together.If one of these is wrong it must be :rolled back otherwise it must be committed.The problem is it exits out :of :the for each statement as soon as it gets to to 'commit work' statement. :it doesn't process the rest of the transaction That's because the ANSI sql standard spesifies that a commit work shall close all open cursors. Your foreach is therefore closed as soon as you execute the commit work. Luckily Informix has learened that this is stupid behavior (the ANSI commity isn't allways too smart) and have implemented a "with hold" option on cursors. Such cursors are kept open accross transactions. I don't remember off hand exactly where you write the "with hold" in a stored procedure, but it should be in the manual. :Here is some sample code of what I want to do. : :Foreach TxFT For : Select tx.number,tx.amount into nNumber,nAmount : for transactiondt : where txind <> 'E' : begin work; ?????????????????????? : let nError = 0; : if nAmount = 0 then : nError = 1; : end if; : if nError = 0 then : nError = UpdateBalances(nNumber,nAmount); {call other storeproc} : end if; {that returns :errorcode} Make sure these other stored procedures do not have their own begin/commit work. You allways have to control transactions from one place only, as they can't be nested. : if nError = 0 then : commit work; ????????????? : else : rollback work; ???????????? I wouldn't do the insert below here. If you get to this place due to a database error it's likely the whole instance may be down. I would write such errors to a file. You can do that with the "system" command in stored procedures. : begin work; ???????????? : insert into errordt (txerrornumber,txerrocode) : values(nNumber,nError); : commit work; ????????????????? : end if; :end Foreach; : :I hope somebody can help me .It is kind of critical. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org