Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!iuvax!cica!gatech!rebel!hisata!walt
From: walt@hisata.UUCP (Walt Hultgren)
Newsgroups: comp.databases
Subject: Re: Informix Question (Again)
Summary: Try using 3 cursors
Keywords: Informix cursors
Message-ID: <159@hisata.UUCP>
Date: 6 Oct 89 15:49:47 GMT
References: <1178@jpusa1.UUCP>
Reply-To: walt@hisata.UUCP (Walt Hultgren)
Distribution: usa
Organization: Hultgren Information Systems, Atlanta, GA
Lines: 72

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?

Here's the best solution I've found so far.  Use three cursors for each
table (or logical group of tables) that you want to query and update.

The first cursor is the one that is prepared and declared "on the fly"
based on the where text obtained from a construct statement.  For this
cursor, don't select all of the fields you want from the row, but only
enough to uniquely identify one row.

As an example, suppose you have a table of invoices called "inv".  Each
row can be uniquely identified by an invoice number "inv.numb".  In this
first cursor, don't say "select * from inv", say "select numb from inv".

Define a variable in your I4GL program to hold the "current" invoice
number;  e. g., "cur_inv_numb".  To navigate the rows returned by the
user's query, fetch the prepared cursor into cur_inv_numb.

The two other cursors are used to fetch the entire row from inv for the
current invoice number and are declared in the program source.  One is
used for inspecting rows and the other is used to delete or update rows.

These declarations might look like:

	declare inv_nolock cursor for
            select * from inv
            where inv.numb = cur_inv_numb

	declare inv_lock cursor for
            select * from inv
            where inv.numb = cur_inv_numb
            for update

For menu selections like Next, Previous, etc., do the appropriate fetch
with the query cursor, followed immediately by:

    fetch inv_nolock into pr_inv.*

where pr_inv is the "program record" corresponding to a row of inv.  This
will show the user the current contents of the row, not just the contents
of the row at the time the scroll cursor was opened.

For a Delete or Update, first do a

    fetch inv_lock into pr_inv.*

then display the row.  This shows the user the guaranteed current contents
of the row and locks it.  You can then update or delete the row using a
"where current of inv_lock" clause.  Close inv_lock immediately after the
update or delete.

An alternate way of doing things is to use ROWID instead of a unique data
value.  Whether or not this is appropriate will depend on your application.

This method will not automatically include added rows to the current user
selection like Informix 3.3 did.  I wish I knew a clever way to do this.
Everything I've thought of so far involves a lot of overhead.  Any ideas,
anyone?

Walt.

Walt Hultgren
Hultgren Information Systems, P. O. Box 386, Tucker, Georgia  30085  USA
Voice: +1 404 564 4707     UUCP: ...!{most_backbones}!gatech!hisata!walt