Re: Cursors

Nils Myklebust wrote: > Deepak Pant <Deepak.Pant@blr.sni.de> wrote: > :Hello, > :I have a query regarding the informix cursors. The usual behaviour > :on the opening of the cursor is that a number of records which match > :the where clause in the open cursor statement are fetched. A fetch > :from the cursor fetches the records from the local cache and not > :from the server. In my application this is causing a problem since > :other informix sessions may be updating the table as well and hence > :the values which are provided by the cursor may be invalid. > :Now is it possible that whenever I issue a fetch cursor the records > :are fetched from the server/database itself and not the cursor which > :probably has gone stale. > This does depend on the development tool you are using. > Assuming it's Informix 4GL or NewEra: > Sadly Informix have no direct support for "key set cursors" which > would have solved this problem. You can however easily implement them > yourself. Simply create the cursor to select the primary key of the > table (or tables) you want to read. > When you need the actual data use another cursor to select a row at a > time using the primary key from the first cursor. This will guarantee > that the data isn't stale (or at least not as stale as using one > cursor). > We often use this approach within the same application to let a user > scroll back and forth in a cursor doing updates as they go. In this > way they allways see the updated data, not the prior versions that are > in the cursor even though the user have done the updates themselves. > This does of course require a cursor with hold if you are using > transactions to avoide the scroll cursor beeing closed by the commits > after the updates. Nils' suggestion is excellent, however, it does not protect your users from overwriting data changed by others while he/she is editing the current record, as Nils points out. Immediately before updating the record you should FETCH the row that you are about to update with a CURSOR...FOR UPDATE so that the row is locked and compare the values returned for all columns, or for an indicative column like a timestamp or update count, to the original values that you FETCHED. Then only if the values have not changed UPDATE ... WHERE CURRENT OF <cursorname of cursor for update>. This will give you the safety of locking rows but permits greater concurrency since the lock in only momentary. Art S. Kagel