Hi Helen,

I will have another serious look at the Docs, Survey demo and Book and
report back. But once more, thank you for the help.

Daniel

> > > 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