At 12:12 AM 9/03/2008, you wrote:

>Thanks so much Helen.
>
>"One way to do this is to perform a "dummy update" as soon as the user
>selects the record for editing."
>How would one do this?

Set PessimisticLocking true on the dataset for this particular task.

>Really I suppose that I'm looking for column level locking rather than
>row level locking.

Not possible.

>I have had to implement a method to allow users to dictate row
>sequencing from within a grid. ie the sequence of each row in the
>table must be adjustable with regard to the others. The standard
>DevExpress Grid method becomes unworkable with large numbers of rows
>and I have developed a method that scales OK. This uses a "sequence"
>column and regular background recalibration of sequence numbers so
>that the system copes with input of new rows simultaneously by
>multiple users, correctly (or nearly correctly) sequenced. Also there
>are a number of other background processes which frequently update
>certain other columns holding statistical data.

Aarrgh. This sounds scary.

>To make this work reasonably well I've (probably unwisely) implemented
>a timer in each client to provide automated background updates on each
>client every 3 minutes so that data is up to date on each client.

If by this you mean you force a Post every three minutes, this is even scarier if the user transaction is in Autocommit. I hope not. It will be an absolute guarantee that users who are editing the same record will snafu one another's work every three minutes.

>But each document can be open for editing for much longer than this.
>So I was thinking of using an update trigger to detect whether the
>document has in the meantime been "issued" by another user and if so,
>blocking the update on certain columns if they have been changed and
>providing an error message. This way I assume that I can achieve the
>effect of column locks while still allowing the background refereshes?

No. A trigger, like everything else, can see only what the transaction sees.

><grimace>
>Scratches head, waiting for Helen to ask what he's been smoking
></grimace>

Hmmm.

>I seem to recall that the TIB components include automatic updating of
>data when it changes on the server

You're using the word "updates" and "updating" rather freely so that your description here might be scarier than it really is. For yourself (as the developer) you must be clear about what's going on:

a) an "update" by a user, which is to start editing a record while in possession of a set in a read-write transaction. Until the user posts a change from that transaction, any other transaction can do so because, at that point, the database engine doesn't know what is happening in any client application.

b) The PessimisticLocking property - available to *all* IBO statement classes including TIBOQuery - is a mechanism that steps in and attempts to perform a dummy update on any record that the user tries to put into a client-side state that presumes a change. The dummy update fails if another transaction already has the lock. If it succeeds, that transaction gets the lock and blocks updates (including dummy updates) from all other transactions and throws an exception. IBO intercepts this exception and prevents the dataset from going into an "editing" mode.

c) If you are using Autocommit transactions (as indicated by your original message) then PessimisticLocking becomes a no-op, along with the protection that the engine provides against overlapping writes. Worse still, if your transactions are configured to do CommitRetaining instead of hard Commit on transactions, you are *causing* transactions be long-running and nothing else you do can avoid that.

d) If you use the Firebird client interface the way it is meant to be used, i.e. no Autocommit transactions and no CommitRetaining calls, with the appropriate transaction isolation and lock resolution settings, then you can part totally from the misconception that you're fixing anything by doing "background updates" or forcing commits by the clock. Accept that Firebird has no way to enable column-level locking and there's nothing you can do to make it so.

e) Separating the client's "Post" operations from its committing operations is all it takes to make the as-designed concurrency handling available "out-of-the-box". For you, amongst other things, that means separating statement-level, client-side operations (e.g., "edit" and "post") from transaction-level operations (packaging multiple statement-level operations inside a mutually consistent task context with a fallback option).

>, but I am using TIBO components and am now heavily invested in DevExpress TDataset controls etc.

All of the important principles of the TIB_Dataset/TIB_BDataset classes are embedded in the TIBODataset. The trap with any Delphi components (even the native TIB_ ones and especially some of those Gee-Whiz controls) is that it is possible to "go around" Firebird's protective features and delude yourself that you don't have to care about transactions and multi-user issues.

There are some things in TIB_ that you can't get in TIBO due to the impoverished data-linking capabilities of the generic VCL TDatasource (such as search mode and a few more). Some people think the gain from plugging into a sexy GUI is worth the cost of denied features. So be it. But it doesn't justify making unnecessary compromises in the data access layer.

To me, it looks a lot like, for no good reasons, you're keeping your dog in a muzzle and trying (unsuccessfully) to do the barking yourself. Study the help text for the parent classes closely and ask on the IBO list about anything you have doubts about.

./heLen