Hi Helen,
I think I did not explain myself very good the first time. I
understand transactions and how one will overwrite others. That is
not what is happening here. Things only don't work when a blob is
edited or inserted, but work as expected when only the TIBOQuery is
working with non-blob fields. Please read agian and see Explanations
below.

--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:
> 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.

EXACLTY!! that is what it is not doing. I am not trying to update one
field at a time I want it to update the whole row. I edit a blob and
post and then edit a non-blob then post. Then commit is called the
blob edit is not saved. This is all inside the transaction on the MDI
form. No other transaction has commited or has a post to commit
before this edit-post-commit of the MDI transaction. The MDI
transaction is started after the other transaction so there are two
transactions active at this time when the MDI transaction commits.
Everything works fine if both are in the same transaction. But if
they are in different transactions I get the strange behavior of
loosing the edits. If I edit a blob then a non-blob post and commit I
loose the blob edit. If I edit a non-blob then a blob the non-blob
edit is not saved. This is all inside the MDI transaction and not the
other transaction that was started first. If I then commit and
refersh the first transaction I can see the changes that actually
posted in the MDI transaction (that is the blob if it was edited last
or the non-blob if it was edited last). At this point both
transaction have the same data but I have lost an edit- not a whole
row edit but a partial edit of that row depending on the order of
blob and non-blob edits as explained above. If I don't edit any blob
fields and just edit non-blob fields all works fine in all
transactions. It is just when a blob is edited things don't work. So
it can't be anything to do with one transaction overwriting another,
just blobs in TIBOQuery. I should try it with IB native controls and
see if the problem is the same.

>
> >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.
Yes, that is what I am doing
>
> >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.

I should have explained my design better. I understand updating and
transaction isolation and I catch all deadlock errors. I am not
exactly updating the same table in two transactions in different
forms. The MDI form only works with one record in the table at a time
while the other has a view of all the records. I need to be able to
update and rollback each MDI transaction. I work with a large blob
and have several Notes or memos in the MDI form. All works fine as
long as no blobs are in the TIBOQuery.

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

I changed the TIBOQuery to a memory dataset and update the data with
DSQL's and all works fine for blob and non-blob fields. I actually
just cut and pasted the SQL from the TIBOQuery in the DSQL's. It is
actually a lot thinner than the TIBOQuery and seems to be a little
faster.

>
> Helen
Thanks for your time anyway,
Stephan Anderson