Re: cursors and transactions

singleton transactions are created/committed around any sql issued without a begin work/commit work statement. They do NOT close open cursors. Effectively it is the commands commit work, rollback work and close <cursor> that close the cursor. Hope this helps Regards Howard Soper Nicole Guffey wrote: > I am using a select into cursor in a foreach statement in a stored > procedure. > For each row retrieved, I need to issue numerous delete statement to delete > records from a number of different tables based on information in the > retrieved row. > > EX: > FOREACH select x into y from tableA > if not exists (select * from tablez where oid = y) > begin > delete tableb where oid = y > delete tablec where oid = y > delete tabled where oid = y > end > END FOREACH > > My question has to do with when a cursor is closed. The syntax manual > states > that a select cursor will close when "The cursor is a select cursor without > a HOLD > specification, and a transaction completes using COMMIT or ROLLBACK > statements." > Is this referring only to explicitly closed transactions, or will informix > treat each individual > delete as an implied transaction and close the cursor? This seems like a > silly question to > have to ask, because I know what the answer should be, but I am new to the > Informix platform > and have learned not to make assumptions based on my knowledge/experience > with other db platforms. > > Thank you, > Nicole Guffey > DBA > J. Driscoll & Associates > nguffey@jdriscoll.com