On Thu, 2005-07-07 at 10:31 +0000, pungsven wrote:

> My question is what went wrong and how can I fix it?
> I think it maybe has something to do with when one user edits a record
> while another is reading it.

I can't answer "what went wrong" ... Helen or Ann would be better able
to answer that.

However, as a general principle, you should not be using ISAM style live
edits on table rows with a RDBMS. This question would not arise if you
were not using live edits. The RDBMS approach sidesteps this sort of
technical issue by addressing the design problem slightly differently.

The retrieval for display and edit should be a separate transaction from
the update operations.

Behind the scenes, whatever the application appears to do, it should:
1. start a transaction,
2. fetch the row(s),
3. commit,

4. allow the user to edit the record until he indicates he is done
(return control to the GUI),
5. perform any internal validation,

6. start a new transaction
7. Perform any validation against the DBMS
8. insert/update/delete the affected row(s), and
9. commit

On your updates, you may wish to guarantee that the record was unchanged
by some mechanism such as a timestamp (there are some issues with this
in Firebird - the API only supports 1 second precision - but it can be
done) or a generator based rowstamp (cleaner in Firebird then timestamp,
if less informative for business purposes).

To guarantee that rows that were changed by another user between the
first fetch and the commit are not updated without review, validate
against the DBMS using something like "select count(*) where
primaryKeyField=:primaryKeyField and rowstamp = :rowstamp". If the
count is 0, rollback the transaction reload the row, and return to the
GUI piece.

>From this point, you can get fancy and apply deltas or invent some

mechanism for merging or approving the changes if you want to, but a
simple message and display of the current values is generally
sufficient.