At 01:59 PM 13/09/2005 +1000, you wrote:

> > Hmmm. This seems like some kind of weird game. The thread you referred
> > to, where you said your problem with DMLCaching was described, appears to
> > be about something entirely different, i.e. how to make the demo apps work
> > in the C++ Builder IDE. I see that I responded to that; and Lester
> > posted
> > some modified source to solve that problem, I understand.
>
>Correct, however, the demo will not allow me to view the forms, so I am
>unable to view the setup of the query(s), etc. So I am having to read the
>DFM files, which is fine, but it complicates things as you can imagine, so I
>may have miss something which could be the cause of my problem.

This one I can't comment on further than I did at the time. I don't have
Builder.

>I will have to read through the documentation once more, as I honestly can
>not remember. But from your reply above (please correct me if I am wrong),
>"Before type" triggers are a not compatible with PessimisticLocking ?

Triggers are routines that execute during a DML operation (insert, update,
delete), as applicable. For example, as soon as you update something (as
you do when you set a pessimistic lock, this happens BEFORE your dataset
goes into the requested DML mode). So ALL triggers will fire, both the
BEFORE and the AFTER triggers.

>The simplified 'Survey Demo' DML_CACHE demo I wrote does not use "Before
>type" triggers on the main table, they are all "After type" triggers.
>
>The only "Before type" trigger is used on the DML_CACHE table, which is only
>called once an Insertion, Update or Deletion has taken place. Thus,
>notifying the other forms, etc via a "Event"

See above.

But you have to understand that triggers fire during the writing phase, not
during the commit phase; whereas Events fire when they happen and get
queued up to wait for the commit. Until the transaction is committed, the
clients don't get the event notifications.

Do you also understand that "writing to the database" and "committing" are
two separate things? When a request to update, insert or delete succeeds,
the new record version is written to the database. However, it is
invisible to other transactions until the transaction is committed; and,
if the transaction is rolled back, your new record versions become invalid
- or, in the case of inserted recversions, they get wiped.

> > And NOWHERE do I find a description of any problem relating to DMLCaching,
> > though I'm sure that the settings in your test app WOULD mess up
> > DMLCaching.
>
>As I mention to Lester, the Demo I wrote (Which follows the Survey Demo to
>the letter), work perfectly. There is only one scenario in which it does not
>behave as I would like.
>
>I would like to stop multiple users from editing the same record at the same
>point in time, but allow multi-read of the same record. Therefore, my
>understanding is that I have to set the PessimisticLocking property = true
>on the IB_Query.

No, you are a bit mistaken here, I think. In Firebird you don't need PL to
prevent multiple users from updating the same record. Transaction
isolation takes care of this. PL is a "hack" so that Delphi programmers
have a way to stop users taking the dataset into Edit, Insert or Delete
mode at all if another Delphi application is already doing stuff to that
record.

Here you need to understand the distinction between what's happening in the
application and what's happening in the database. I'll talk about Edit
(client-side) and Update (server-side) but the distinction is the same for
the other two operations.

"Putting the dataset into Edit mode" is a purely client-side thing. It
simply means that the dataset's buffers become writeable. It does not have
any effect on the server side, at all. As far as the server is concerned,
it just has an active cursor on the set that was output by your SELECT
statement.

Of course, there is usually a time-lag between when the user's action puts
the dataset into "Edit mode" and when the dataset's Post method is
called. Between those two events, the user is going to do the changes that
will eventually be requested in an UPDATE statement. Post is the method
that actually submits the UPDATE statement to the server.

What PL does is to cause the dataset to submit a dummy UPDATE statement to
the server *before* the buffers are put into Edit mode. The default
LockSQL statement that IBO submits is

UPDATE ATABLE SET TheFirstKeyLinksCol=TheFirstKeyLinksCol

The dummy update will succeed if no other transaction has a lock on the
record; and will fail if the record is already locked. IBO swallows the
lock conflict exception, returns its own exception reporting that the
record is locked, and refuses to put the dataset into dssEdit. If the
dummy update request succeeds, the server locks the record, no exception is
returned to the client and IBO proceeds to make the buffers writeable.

For its part, the server doesn't know the difference between an update
statement that doesn't really change any values and one that does. And so,
unless you have designed your UPDATE triggers really carefully, the
successful posting of the dummy update is going to cause all of your
triggers to fire - including those you wrote to support DMLCaching.

PL is unnecessary (and undesirable) and should really be used only when you
want database-wide operations to be strictly serialised. Strict
serialisation is rarely a requirement. An appropriate combination of
transaction isolation and locking policy is sufficient for most (and some
would say *all*) conditions. The "typical" Delphi application design
causes problems for multi-user systems, though. Delphi developers expect
to get butter on both sides of the bread. The want "live" datasets with
multiple open cursors, but they also want this for (sometimes large numbers
of) multiple concurrent users all hitting the same records at once.

So PL becomes a hack that you can do for Delphi-style systems that operate
on objects that have absolute limits - such as booking systems; or in vrey
volatile MRP systems where all the stages from allocation through to
consumption are being done rapidly and continuously.

>But if I do this, and try to edit a single record (and no other instance of
>the application or form is currently editing the record in question) I get
>the following error:
>
> > ISC ERROR CODE: 335544569
> > ISC ERROR MESSAGE:
> > Dynamic SQL ERROR
> > SQL error code =-510
> > Cursor not updatable.
>
>So my original question was what have I done incorrectly?

Who knows? I think it's probable that you saw that error because of the
mistakes in your path configurations - your dataset was looking at a
different database.

>Lester replied with:
>
> > Don't use PessimisticLocking myself. If I need to lock records, a 'last
> > modified' field gets updated, and that tells other users that the
> > record is 'busy' and who it is busy with ( -ve user_id = editing, +ve
> > user_id = last > changed by )

I think Lester is probably talking here about a "gateway" table, maintained
through single quick statements in a self-contained transaction, where
users don't get to access a particular record unless they can get past the
gateway. Simply setting a flag on the record that the user wants to edit
is just a custom way of doing the PL hack, with the same drawback of
causing the update triggers to fire.

>Which from what I understand, he programmatically checks the content of the
>'last_modified' field prior to allowing the record to be edited.

Not sure what Lester was talking about.

>I was hopping that by following the sample "Survey Demo" as well as adding
>the PessimiticLocking = true I could achieve the same thing. But it appears
>that I am wrong or that my understanding and subsequent implementation is
>wrong.

Yes. If you can get the Survey demo working, I'm sure it will become clear
just how DMLCaching would work (or not) for what you want to do. I suggest
that you spend a bit more time reading up on DMLCaching, and experimenting
with it, so that you are able to give a clear description of what you are
seeking to do and what doesn't work as you think it should.

>If need be I can update the posted file without the previous errors(hardwire
>paths etc).

Well, from my point of view at least, it would be unreasonable unless you
made the effort to describe the problem.

Helen