RE: Strange behaviour of FOREACH (INFORMIX-4GL Version

It is most likely that this is caused by the update during the foreach. Keep in mind that when you open a cursor a subset of data is stored in memory. When you then issue a update statement that on a record in your subset, the data in the table changes, but (dependent on what you are doing) the data in the subset may stay the same. When the fields you update are part of the where clause, the data in your table differs from data in the subset in the cursor thus relusting in 'notfound' or any number of possible errors. For the same reason you should never set the isolation mode to dirty read, since dirty means the presence of the data is not guaranteed. A rollback might remove a record your cursor has read in when it was just inserted. When the rollback occurs the data is remobed from the table, but NOT in the subset of the cursor. Regards, Robert A. Reissaus ------------------------------------------------------------------------ R.A. Reissaus Risdi, Amsterdam, the Netherlands Ibm/Informix Consultants Mobiel : +31(0)6 51185352 : Mobile E-mail : r.a.reissaus@risdi.com Website : http://www.risdi.com ------------------------------------------------------------------------ Disclaimer: `Externe E-Mail wordt door partijen niet gebruikt voor het aangaan van verplichtingen` `Any e-mail messages from sending parties named in this e-mail are given in good faith but shall not be binding nor shall they be construed as constituting any obligation on the part of any such party.` `Alle uitgaande e-mail wordt gescand op virussen en andere ongemakken, maar uiteraard kan geen garantie worden afgegeven. Wij accepteren geen aansprakelijkheid voor enige schade indien onverhoopt toch een (al dan niet bekend) virus aanwezig blijkt te zijn.` `All e-mail send is being scanned for viruses. However we can not guarantee no virussen will be present. We accept no liability for any damage that might result from any ( known or unknown) viruses.` ------------------------------------------------------------------------ -----Oorspronkelijk bericht----- Van: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] Namens Ravi Verzonden: maandag 3 oktober 2005 9:46 Aan: informix-list@iiug.org Onderwerp: Re: Strange behaviour of FOREACH (INFORMIX-4GL Version 7.30.HC4P1) Hi, Update on this problem posted by me: While reading from the table 'tab1', after some processing, we also update one timestamp field in the same table 'tab1'. So in short the program is something like this: FOREACH cur1 ... <Do some processing here> ... EXECUTE upd_stmnt_for_tab1 END FOREACH Regards, Ravi. Fernando Nunes wrote: > Ravi wrote: > > Hi All, > > > > We are facing a very strange issue while using FOREACH. The > > environment is as follows: > > > > HP-UX 11.11 > > Informix 4GL 7.30.HC4P1 > > Informix Dynamic Server Version 9.30.HC2 > > > > 4GL program is opening a cursor on table 'tab1' with certain > > conditions in the where clause. When a FOREACH is applied to > > traverse through this cursor, it is not returning all the rows which > > satisfy the conditions. Due to this, the output of the program is > > not as expected. The behaviour is also not consistent as sometimes > > it gives 'x' number of records less and sometimes it gives 'y' > > number of records less. > > > > Please note: > > (1) The cursor declared is a plain cursor and not scroll cursor and > > without any WITH HOLD clause. > > (2) The isolation is set to dirty read before opening the cursor. > > > > Kindly let us know if there is any resolution to this issue. Is this > > some kind of bug in Informix 4GL tool for the version mentioned > > above. > > > > Thanks. > > > > Regards, > > Ravi. > > > > > And the contents of the table are not changing while your program > runs? > > Regards. sending to informix-list