At 06:48 PM 10/08/2004 +0000, you wrote:

>Hi All,
>I have a MDI app that brings up a regular form with a grid hooked up
>to a TIBOQuery that uses a TIB_Transaction and a TIB_Connection. That
>form loads a MDI form based on the selected record. The MDI form uses
>a TIBOQuery and a separate TIB_Transaction but the same
>TIB_Connection on the same table. I use tiCommited and
>RecVersion=true and trap all deadlock errors. The problem occurs when
>I edit or insert on the MDI Form. It seems the order in which (memo
>or binary)blobs and non-blob fields are edited or inserted matters as
>to what doesn't get saved. If I edit a blob then post and do a commit
>or if AutoCommit is true then it gets saved. But if I edit a blob and
>post it and then edit a non-blob and post it all is ok until I do a
>commit or refresh(true) etc. and then the blob edit is not saved. If
>I edit a non-blob then post and then edit a blob and post and do a
>commit then the non-blob edit is not saved.

OK, the first thing to understand is that all updates in Firebird/IB are
row-level - there is no such thing as a column-level update. So, if one
transaction "updates a blob" on record XYZ, it actually updates the whole
record. If another transaction updates the same record (even if it doesn't
touch the blob) then the whole record is updated, not just the fields you
edited. If the first transaction (the one that changes the blob) commits
first, and the second (the one that didn't change the blob) is allowed to
post, it overwrites the first and restores the original data from the blob.

>I thought TIB_Connections
>can support multiple transactions? Maybe just not on the same table
>inside the same app at the same time?

TIB_Connection is an encapsulation of the database connection
structures. So what we mean here is that Firebird and IB support multiple
concurrent transactions.

>I hope I just need to tweak a
>few more of the properties and not opened up a can of worms.

I think you might need to rethink your assumptions about column-level
updating and transaction isolation. The purpose of transactions is to
isolate one task from another. Generally, to have the same user hitting
the same records in separate transactions doesn't make sense. One user's
work should be consistent within itself, not having multiple forms with
conflicting views of database state.

In the general context of database state, you can set up transactions with
a variety of parameters (isolation, lockwait, recversion) to arrive at the
conflict resolution situation you want.

If you perceive some need to keep an inconsistent view in a single user's
application, explain what you want to achieve and someone can help you to
see whether there are transaction parameter configurations that can achieve
what you are after.

>The sql
>select is very simple and basic just from the same table and no joins
>or views. All works fine if I use the same transaction or post and
>commit each field separatly(AutoCommit) but I need each MDI to be
>able to rollback any changes.

It's OK to have different forms hitting the same records, even via
different datasets. If there is some special, compelling reason, it might
even make sense to have them hitting the same records via different
transactions, though it's not obvious yet what you want to achieve by this.

Helen