Board index » delphi » Transaction losing master record with Paradox

Transaction losing master record with Paradox

Hello, everyone. We're having this problem for a few months, maybe someone
has the light...

We use Delphi 3, BDE and Paradox. Our invoice database is implemented using
a complex master-detail relationship. The master table has four child
tables, and one of these has another child table under it. So it's a
three-level master-detail structure.

We want the user to be able to edit the invoice as a whole single entity, so
if he / she adds or modify a record, enters some data, and then decides to
Cancel, all the changes will be undone, even if he added / changed / deleted
records in the detail tables.

We implemented it through transaction control, and it works completely well.
The trick is done this way: The master TTable's BeforeEdit and BeforeInsert
events call Database.StartTransaction. AfterPost calls Database.Commit and
AfterCancel calls Database.Rollback. On the BeforeEdit / BeforeInsert /
BeforeDelete (that is, Before Anything) of the child TTables, we call the
Edit method of the Master TTable, so the first change is always done to the
master TTable, triggering StartTransaction. Delphi, BDE and Paradox handle
it nicely and it works as expected.

But from time to time, it happens that one of the invoices disappears. When
we search through each table, we discover that the detail records are all
there, in every child table, but the master record, the header one, is not
there!!!! It puzzles us because if it was a Transaction failure, no record
should be there, or at least the master would but the details not.

We haven't had any other problems using Paradox, but this is making us think
of migrate to Access. But the newsgroup's messages do not encourage using
Access at all... So now it's a dead-end; it seems to be better working
around the problem with Paradox. Also, TTables are working fine until now
and changing to TQuery with Local SQL to see if it works would be a
headache.

Any suggestions, similar experiences, hints, anything?

Sorry for the big posting... and thank you!

Andr de Oliveira
Otimiza Ltda. - Brasil

 

Re:Transaction losing master record with Paradox


I implement the same thing with processes similar to yours.

Several hints to you:-

(1) I set all TTables CachedUpdates = True, so implicit Transcation Control
is started whenever something inserted or edited;

(2) And, I use the Database.ApplyUpdates([MasterTable, DetailTable1,
DetailTable2]) construct to commit;
     Mind the sequence of tables within the brackets, you should always put
MasterTable first, level by level;
     If the applyupdate is for deletion, reverse the order of the TTables;
     That means, for deletion, apply for the DetailTable of the lowest level
first

(3) And, I use the MasterTable.CancelUpdates, DetailTable1.CancelUpdates,
etc. altogether to rollback;

(4)Follow this help sequence "cached updates"->"Working with cached updates
(Delphi Help)"->">>",
    Something about Cache Update for multiple Master-detail implementation
is mentioned.
    Also, clicking ">>" to skip several pages, you will find a topics called
"Applying updates for master/detail tables";

Regards,
Steven
steveny...@microtechsystem.com

Quote
>Hello, everyone. We're having this problem for a few months, maybe someone
>has the light...

>We use Delphi 3, BDE and Paradox. Our invoice database is implemented using
>a complex master-detail relationship. The master table has four child
>tables, and one of these has another child table under it. So it's a
>three-level master-detail structure.

>We want the user to be able to edit the invoice as a whole single entity,
so
>if he / she adds or modify a record, enters some data, and then decides to
>Cancel, all the changes will be undone, even if he added / changed /
deleted
>records in the detail tables.

>We implemented it through transaction control, and it works completely
well.
>The trick is done this way: The master TTable's BeforeEdit and BeforeInsert
>events call Database.StartTransaction. AfterPost calls Database.Commit and
>AfterCancel calls Database.Rollback. On the BeforeEdit / BeforeInsert /
>BeforeDelete (that is, Before Anything) of the child TTables, we call the
>Edit method of the Master TTable, so the first change is always done to the
>master TTable, triggering StartTransaction. Delphi, BDE and Paradox handle
>it nicely and it works as expected.

>But from time to time, it happens that one of the invoices disappears. When
>we search through each table, we discover that the detail records are all
>there, in every child table, but the master record, the header one, is not
>there!!!! It puzzles us because if it was a Transaction failure, no record
>should be there, or at least the master would but the details not.

>We haven't had any other problems using Paradox, but this is making us
think
>of migrate to Access. But the newsgroup's messages do not encourage using
>Access at all... So now it's a dead-end; it seems to be better working
>around the problem with Paradox. Also, TTables are working fine until now
>and changing to TQuery with Local SQL to see if it works would be a
>headache.

>Any suggestions, similar experiences, hints, anything?

>Sorry for the big posting... and thank you!

>Andr?de Oliveira
>Otimiza Ltda. - Brasil

Other Threads