Re: begin works and commit works in storeprocs

Nils & Family, I missed Rudolphs original post so I respond to Nils' post. Here comes some preachiness: While I know it is legal syntax to BEGIN and COMMIT work within a stored procedure, I have always felt this is bad practice because the program calling the procedure could easily be in a transaction already at the time of the call, resulting in the error "Already in a transaction". (No, I don't remember the error number and I probably don't want to know you if you have it memorized.. ;-) This said, I would use a FOREACH loop in my 4GL code and place the BEGIN WORK and COMMIT WORK statements inside the loop. There is also the issue FOREACH cursor INTO variables.* BEGIN WORK EXECUTE prepared statements that call the procedure USING any variables the procedure might need END FOREACH Nils' answer is fine if you absolutely MUST put the FOREACH loop inside the procedure. This awful situation is possible only if you have horrid security considerations that forbid a user from reading the table he working on. In that case, only a stored procedure will allow the needed access to the table. And in that case, you would (grumble, grumble) need to BEGIN & COMMIT WORK inside the procedure. Most environments are not as restrictive and should allow you to do your table access, calling the procedure for each row you fetch. As for the syntax of WITH HOLD: 4GL: DECLARE yutz_curs CURSOR WITH HOLD FOR statement DECLARE yutz_curs CURSOR WITH HOLD FOR prepared-statement-ID SPL: FOREACH cursor_name WITH HOLD FOR statement . . . . END FOREACH >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 'commit work' >:statement. it doesn't process the rest of the transaction > >That's because the ANSI sql standard specifies 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 learned that >this is stupid behavior (the ANSI committee isn't always too smart) and >have implemented a "with hold" option on cursors. Such cursors are >kept open across 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. Bottom line: If you can, take those WORK statements out of the procedure. -- -- Jake (In pursuit of undomesticated aquatic avians) +----------------------------------------------------------+ |Aside from that, how did you enjoy the play, Mrs. Lincoln?| +----------------------------------------------------------+