stored procedure, cursor, and transaction

We have a scenario that I am not sure works by design or not, couldn't find any info on it. We have a stored procedure that acts as a wrapper for several other stored procedures. It uses foreach loop to process those several stored procedures on a record by record basis. One of the stored procedures also calls stored procedures that also calls stored procedures so the entire process is probably 4-5 layers deep. At a bottom layer, we started using a stored procedure that has a transaction (begin work/commit work) defined. That routine runs without error, but when it all returns back up to the wrapper stored procedure, when it hits the end of the loop, it drops out instead of moving to the next record. Is creating a transaction at a lower level supposed to kill a cursor at a higher level? If we are coding this wrong, is there a proper way to make this work? TIA, Randy

Thank you. Time to start editing :) Randy -----Original Message----- From: Marco Greco [mailto:marco@4glworks.com] Sent: Tuesday, December 11, 2012 10:18 AM To: ids@iiug.org Cc: Kennedy, Randy Subject: Re: stored procedure, cursor, and transaction [29061] On 11/12/12 17:11, Kennedy, Randy wrote: > We have a scenario that I am not sure works by design or not, couldn't > find any info on it. > > We have a stored procedure that acts as a wrapper for several other > stored procedures. > It uses foreach loop to process those several stored procedures on a > record by record basis. > One of the stored procedures also calls stored procedures that also > calls stored procedures so the entire process is probably 4-5 layers deep. > At a bottom layer, we started using a stored procedure that has a > transaction (begin work/commit work) defined. > That routine runs without error, but when it all returns back up to > the wrapper stored procedure, when it hits the end of the loop, it > drops out instead of moving to the next record. > > Is creating a transaction at a lower level supposed to kill a cursor > at a higher level? If we are coding this wrong, is there a proper way > to make this work? > > TIA, > Randy > Cursors do indeed get closed on commit, unless they are declared WITH HOLD. For stored procedures, you do that using the WITH HOLD clause in the FOREACH statement. This unluckily means that you will have to go through your five layers of SP's and adding 'WITH HOLD' to each FOREACH statement. -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

On 11/12/12 17:11, Kennedy, Randy wrote: > We have a scenario that I am not sure works by design or not, couldn't find > any info on it. > > We have a stored procedure that acts as a wrapper for several other stored > procedures. > It uses foreach loop to process those several stored procedures on a record by > record basis. > One of the stored procedures also calls stored procedures that also calls > stored procedures so the entire process is probably 4-5 layers deep. > At a bottom layer, we started using a stored procedure that has a transaction > (begin work/commit work) defined. > That routine runs without error, but when it all returns back up to the > wrapper stored procedure, when it hits the end of the loop, it drops out > instead of moving to the next record. > > Is creating a transaction at a lower level supposed to kill a cursor at a > higher level? If we are coding this wrong, is there a proper way to make this > work? > > TIA, > Randy > Cursors do indeed get closed on commit, unless they are declared WITH HOLD. For stored procedures, you do that using the WITH HOLD clause in the FOREACH statement. This unluckily means that you will have to go through your five layers of SP's and adding 'WITH HOLD' to each FOREACH statement. -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Committing or rolling back a transaction will close all existing cursors not declared with the WITH HOLD optional clause. You can try adding that to the declaration of the cursor in the outer SPL routine. Not sure if it is supported in SPL and can't test it right now. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Dec 11, 2012 at 12:11 PM, Kennedy, Randy <RKennedy@scottsdaleaz.gov>wrote: > We have a scenario that I am not sure works by design or not, couldn't find > any info on it. > > We have a stored procedure that acts as a wrapper for several other stored > procedures. > It uses foreach loop to process those several stored procedures on a > record by > record basis. > One of the stored procedures also calls stored procedures that also calls > stored procedures so the entire process is probably 4-5 layers deep. > At a bottom layer, we started using a stored procedure that has a > transaction > (begin work/commit work) defined. > That routine runs without error, but when it all returns back up to the > wrapper stored procedure, when it hits the end of the loop, it drops out > instead of moving to the next record. > > Is creating a transaction at a lower level supposed to kill a cursor at a > higher level? If we are coding this wrong, is there a proper way to make > this > work? > > TIA, > Randy > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae934118580340b04d0978667