We have a multi-table view in an Oracle 8i database the tables of which are
updated by various applications on a number of clients. Other clients that
have a DBGrid showing the view do not see the changes to the underlying
tables until they disactivate and then re-activate the Query, losing in the
process the position currently selected in the DBGrid. The grid shows the
first N records, not the N that were on screen.

The Refresh method on the Dataset does not work (says there is no Key
field).
We then tried Bookmarks and later ClientDataSet with TProvider and Refresh,
with some improvement. The grid returns to show, following the refresh, some
N records that are not the first N of the dataset. Depending on what records
have been added and deleted by the other (table-updating) clients, those N
records may or may not coincide with the N that were on screen before the
refresh. For example, if the DBGrid selection before the refresh was on the
47-th record of the query, following the refresh it is still on the 47-th record,
but that may or may not be the record previously selected, as other records
may have been added and / or deleted ahead of the 47-th record by any of
those table-updating clients.

We want to be able to update the contents of the DBGrid in a timer event in
such a way as to always point the selection following the refresh at the same
record as was selected prior to the refresh, or at the succeding record if the
selected record has now disappeared (was deleted by the table-updating
clients). We would like to avoid re-executing the full query every update
cycle, as that would be a heavy load for the DB.

Anyone know how to achieve this ????

Jim Fleming
24-May-2002

===================================================

You really must do a full query update when data has been changed in the
table.  Instead of attacking the problem like you're thinking about now,
think about this:

Build your own copy (the initial query) of the dataset in memory on the
client.
Use a trigger in Oracle to signal (using a socket connection) when a row has
been updated to all attached clients.
Each client will be in charge of loading the updated row and re-displaying
accordingly.
You can pass a simple "UPDATED 5427194" or "DELETED 49310237" message to the
clients.  They can react as necessary.

You're basically building your own distributed event system.  Don't try to
rely on the bound TDataset components to do this because they really do need
to be re-loaded each time.  Use an in-memory TDataset (available all over
the web) and add/delete/update rows to it as you receive events from the
database.

This concept also works well (even a little better) in an n-tier
environment.

Eric