Subject Re: [IBO] RE: help
Author Helen Borrie (TeamIBO)
At 09:46 PM 26-02-02 -0500, you wrote:
>Hi Jason,
>I have a question, in regards to the IBO Query & Transaction...
>My application has a screen where you can create a new record, and then
>multiple detail items under that record. So, I am running two transactions
>on two queries.

Why? The whole purpose of a transaction is to throw a rope around tasks
that involve data dependencies. Separating the master and the detail into
separate transactions totally defeats this.

Should one infer that you are using separate transactions in lieu of a

>I am having a problem though. I am only committing the
>changes to the detail right before I commit the changes to the master

No, this is the wrong approach. Master-linking links the master and detail
datasets together through the Datasource property of the detail and by
internally matching the linking column of the detail (usually a foreign
key) to the primary key of the master. It posts the master record first,
to avoid FK violations when the details are posted. It is essential that
the two datasets be in the same transaction.

>The problem is, if I add a few items, then begin adding
>another...but made a mistake...and need to cancel the current one or delete
>one that was in error, the record still shows open and also posts when I
>commit the transaction.

Post and commit work the other way around. Datasets are posted and then
the transaction is committed. If Autocommit is set true on the
transaction, then each post of a dataset causes the transaction to be

But if your code takes explicit control of the transaction, Autocommit
becomes disabled. From here on, until a Commit or Rollback is explicitly
called, Post causes a new record version to be stored on the server but it
remains uncommitted.

>Let me give you a brief code overview, and maybe you can tell me where I am
>going wrong...
>if not MasterTrn.InTransaction then
> MasterTrn.StartTransaction;

Don't use InTransaction for this test, use TransactionIsActive.

>if not DetailQry.InTransaction then
> DetailQry.StartTransaction;

If DetailQry is a TIBOQuery, then this is not a valid call, since these are
a property and a method of a transaction.


...and if DetailQry is a transaction, then that's an invalid call because
you can't Append to a transaction...

>[if Detail OK] DetailQry.ApplyUpdates;

ApplyUpdates isn't a valid call unless you are using CachedUpdates.

>[if Detail Cancel] DetailQry.CancelUpdates;

..same with CancelUpdates..
>[if Detail Delete] DetailQry.Delete;
>[When Master is Posted]...
>if DetailQry.InTransaction then
> DetailQry.Commit;

You can't call commit on a query.


CachedUpdates true?

>If MasterQry.InTransaction then
> MasterQry.CommitRetaining;

...not a transaction...

>[When Master is Canceled]...
>if DetailQry.InTransaction then
> DetailQry.Rollback;

..not a transaction...


CachedUpdates? but didn't you already call Cancel or CancelUpdates?

>If MasterQry.InTransaction then
> MasterQry.Rollback;

..already been there...

>Any ideas? Please.

Yes. Start again from the beginning. Stop trying to fight transaction
control and, instead, work with it.

At your stage, I strongly recommend to work with a TIBODatabase, not with
transaction objects you don't understand. Make sure you set AutoCommit to
False if you want to take full explicit control of the transaction.

If you want to be able to cancel (delete) rows from the detail buffer
during the course of the transaction, then cache the detail set (by setting
its CachedUpdates property to true). In this way, you can call Delete on
the dataset and simply remove the unwanted detail row from the cache buffer
before you call ApplyUpdates to the dataset. (The native IBO data access
has no need of this, since you can simply call ClearBuffers on the row
object and whiff the unwanted row right out of existence..)

Hook up the Master and Detail queries to the IBODatabase through their
Database property. This hooks them into the internal transaction of the
IBODatabase. (Don't do anything else with respect to transactions).

Hook the detail query to the master query through the detail's datasource
property. (Make sure the linking key from the detail to the master has the
same columns and columnnames as the Master's KeyLinks (primary key) - this
is an inherited TDataset limitation).

And be clear about which methods apply to which classes. You POST datasets
and you COMMIT transactions (including database objects, which encapsulate
a transaction). You ApplyUpdates (Cancel..., etc.) to a cache buffer, but
only if you are using one of course.

That's about the limit of my suggestions as a starting point to get
client/server behaviour working properly.

Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at