Subject Re: [IBO] OnAfterCommit event / OnAfterPost event
Author G. Nau
Am 1 May 2007 um 20:02 hat Helen Borrie geschrieben:

> At 06:17 AM 1/05/2007, Gunther Nau wrote:
> > > Could you explain what this extra statement does? With your
> > > transaction in Autocommit mode, if it was applicable to database
> > > state *before* the dataset Post occurred, it will be out of date by
> > > the time the dataset's work has been Posted and the transaction committed.
> > > On face value, it seems like something you should be doing on the
> > > database side, in an After trigger.
> >Doing a trigger is difficult, as there is some Delphi stuff to do.
> >To explain what should be done in the application:
> >*It a program to manage parts in a stock
> >*the parts table contains a field "autolabelprintonnewprice"
> >So when a user is editing the part data and the price is changed and the
> >autolabelprintonnewprice=1 then
> >a.) a label should be printed by fastreport
> >b.) if the label is successfully printed (user has not pressed cancel in the
> >print dialog nor any printing error occured), then the fields "lastlabeldate"
> >and "lastlabelprice" should be set to the actual values.
> >Part b.) is an update-sql, which is send to the database in Fastreports
> >OnafterPrint event.
> >
> >So my question is: where do I have to start the delphi procedure? which is
> >-checking if a label has to be printed
> >-start the fastreport label printing form (which is doing the table
> >update after
> >successful print)
> You don't say whether your label print routine uses the same dataset
> that the data change routine uses. Assuming yes, I think this is
> going to be a matter of adjusting the workflow of your program in
> order to make both updates safe.
> I think that both Autocommit and tiReadCommitted are unsafe here,
> especially if the storeperson is updating multiple stock items at
> once. I think you need to arrange things so that committing the
> change to the stock item occurs independently of the label-print
> routine AND before the label-print routine is called.
> I would write the print routine as a self-contained procedure that
> takes arguments including the primary key of the stock item record
> and a var argument for the autolabelprintonnewprice flag, let's say .
> Don't execute any DML statements in this routine!!
> Wrap the updating of the stock item record and the printing (or not)
> of the label in one autonomous procedure. Separate your Post and
> Commit events by disabling Autocommit and by starting and committing
> your transaction explicitly. Use Snapshot (tiConcurrency) to ensure
> that other transactions don't interfere with yours.
> 1. Define a property in your application, e.g. PrintLabel
> integer. Initialise it to zero right at the start of each run of
> your autonomous procedure.
> 2. Use the BeforePost event of the dataset in which the edit is done
> to set the PrintLabel variable (or property) to 1 if
> (Dataset.autolabelprintonnewprice = 1).
> 3. In the AfterPost event of the dataset, call your LabelPrint
> routine. This ensures that the label won't be printed if the Post
> event fails. Pass the current value of the PrintLabel property as
> your var input argument to this routine.
> In the called routine
> a) if the var has the value of 1, create your FR label object and
> print the label
> b) if the FR object completes execution without errors, set the var to 0.
> 4. On return of the print procedure, read the var and compare its
> value with the current value of the PrintLabel property). At this
> stage, your transaction still holds any lock that it acquired from
> the Post action. You can proceed to resolve the situation accordingly, i.e.
> a) if they are equal and 0, you're done.
> b) if they are equal and 1, you know that the label didn't get
> printed for some reason. Provide a button so that the user can fix
> the printer problem and try the print routine again. Run this loop
> until the LabelPrint routine returns might want to provide a
> "drop-out mechanism" in case the printer is totally dead.
> There will be a final point, where either a) is the situation, or :
> i) the label is printed and you want to update the flag on the record; or
> ii) the label still needs to be printed
> In case i) the transaction can be committed and followed up with an
> update to reset the flag to 0.
> In case ii) (depending on your rules and requirements) you might want
> to commit the transaction, leaving the record in a state where a
> label is missing and wanted. You might want to write a record to an
> error log or a reprint batch (or whatever the rules want).
> Or, if your rules say the update of the stock item must not proceed
> if a label can't be printed, then this part of your routine would
> roll the transaction back.
> This all sounds a lot more involved than it is. It's just my
> pedantic way of pleading with you not to pass the control of a
> database update over to a print routine and risk having bad glue or
> faulty ink causing your inventory system's integrity to come unstuck!
> Oh, and a wonderful opportunity to preach some dogma about _using_
> transactions as task wrappers, instead of implementing tortuous
> spaghetti code to try to work around them!
> Helen
thanks for your time and patience to write down all that hints and tips.
I digged through my application and found, that a lot of issues you
mentioned are already realized.
I also managed to sequentialize the storing and printing process, so they are
better seperated now.
At the moment I'm working on a last small issue, that everything is working
(=updating and printing fine) except on the *first* record in the table.
So I'll have to check if there is an initialiation or similar blocking that record
with a transaction, which was not noticed until today.

Bottom line is: Problem solved!