Re: for update cursors and foreach

On Nov 22, 9:10pm, Nigel Campbell wrote: > Subject: for update cursors and foreach > Me again! The foreach statement does not allow me to include the for udpate > clause for my cursor (foreach fred for select ... for update ). What is > the Informix method for creating such cursors within a stored procedure? > > -- > Nigel Campbell Voice: (613) 738-1338 ext 3016 P.O. Box 9707,Stn.T > Business Intelligence FAX: (613) 738-0002 3755 Riverside Dr. > Cognos Incorporated Internet: nigelc@cognos.com Ottawa ON K1G 4K9 > URL: http://www.cognos.com CANADA >-- End of excerpt from Nigel Campbell Apologies as I think I sent you off course slightly. FOR UPDATE is part of the DECLARE statement syntax not SELECT syntax. The FOREACH statement in an SP does an implicit DECLARE ... FOR UPDATE if the FOREACH loop contains any UPDATE/DELETE ... WHERE CURRENT OF xx statements. Actually for all I know it may open a for update cursor at all times, the documentation doesn't say. The syntax also does not allow you to restrict the update capability to certain columns the way DECLARE does so you cannot, unfortunately, specify updates to non key columns only to get the additional speed advantage. If you need to control the locking then you need to make use of the SET ISOLATION / SET TRANSACTION, SET LOCK statements or use an UPDATE .. WHERE CURRENT OF .. to explicitly lock the row. Cheers - Jim -- ----------------------------------------------------------------------------- Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ----------------------------------------------------------------------------- My opinions are my own. They may vary with time but they remain mine!