Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!ames!pacbell!att!laidbak!elaited!daveb
From: daveb@elaited.i88.isc.com (Dave Burton)
Newsgroups: comp.databases
Subject: Re: Informix Question (Again)
Message-ID: <1989Oct13.155307.24019@i88.isc.com>
Date: 13 Oct 89 15:53:07 GMT
References: <1178@jpusa1.UUCP> <Oct.6.13.15.16.1989.2676@yes.rutgers.edu>
Sender: usenet@i88.isc.com (Usenet News)
Reply-To: daveb@i88.isc.com (Dave Burton)
Distribution: usa
Organization: is pretty bad/My method of
Lines: 53

In article <1178@jpusa1.UUCP>, rick@jpusa1.UUCP (Rick Mills) writes:
| I have Informix 4GL 1.10 Rev A and a question...
| I would like to declare a scrolling cursor to select
| a list of rows for a screen form. I want the ability to do a
| Next-Previous with these rows, and update selectively as I go.
| Is this possible without declaring a second cursor to
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| keep track of where I'm at in the list? Shouldn't this be easy?

In article <Oct.6.13.15.16.1989.2676@yes.rutgers.edu> emuleomo@yes.rutgers.edu (Emuleomo) writes:
|The trick I used was to declare 2 cursors.  The 1st one a SCROLL cursor
                         ^^^^^^^^^^^^^^^^^
|that will be used to retrieve the **SERIAL NUMBERS** of the records you
|need.   The 2nd cursor will be used to select ONLY ONE record; i.e. the
|record that matches the current serial number!!  Furthermore, this 2nd cursor
|will be declared  *for update*.
|Now you can do NEXT, PREVIOUS, LAST etc.. on the SCROLL cursor and
|whenever you need to update the current record, all you need to do
|is fire off the *update* cursor!! ...
|Watch out for records in the *active set* of the scroll cursor
|that may have been deleted.  My approach was to inform the user that
|the record has JUST been deleted!!

My solution to this problem, ugly as it is, is to open a single
scroll cursor for the selected rows, then next/previous/etc as
desired. Updating and deleting presents a problem in that the active
set is *not* updated after an update or delete operation. Because
it was vital for my client to see the changes in the active set,
I had to code the update and delete functions so they would:

	get the unique key of the current row
		(or the next or previous key for deletes)
	do the operation
	close the set
	open the set
	fetch until the keys matched

This allows the next/previous stuff to function as before, and the
active set to reflect the "actual" state of the database. The problem
areas with this approach are twofold:
	1) if the last row is deleted, what can be fetched?
	2) if another user is working with the same table,
		is the active set an accurate picture of the table?

Probably this is a poor solution to this problem, but I know of
no other that works as well in most cases (I'm not usually faced
with problem 2 in practice, and problem 1 may be dealt with by
aborting the query after the last delete). I'm inclined to call this
a problem with Informix 4GL - I want accuracy before performance,
and I want performance, but the active set stuff prevents this.
-- 
Dave Burton
uunet!ism780c!laidbak!daveb