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 0....you 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