At 10:08 PM 22/09/2004 +0000, you wrote:

>Hi All,
>
>I'm using a TIBOQuery to edit some data and it's working fine. I
>then use a TIBD_SQL to call a stored procedure to update another
>table with the changes. The changes aren't being applied and I
>assume it's because the changes aren't visible on the server yet.
>What am I missing? What needs to be done other than a
>TIBOQuery.Post to force writes to the server?
>
>BTW, the reason I think the changes aren't visible on the server is
>because I can open the database and execute a query in IBExpert
>after the post and the changes aren't there.

OK, a situation like this is a good one for some self-instruction about
transaction isolation.

Let's say you are looking at some view of your table in IBExpert in
Concurrency isolation.

You hop over to your app. Let's suppose that your iboquery and your
ib_dsql are both in the same transaction. For simplicity, let's say this
transaction is set to AutoCommit. You perform some DML operations through
these statement objects and Post - which, because of the AutoCommit, also
commits the work. First, the insert is posted and the transaction
ends. CommitRetaining (which is behind AutoCommit) reopens the same
transaction. Then you execute the ib_dsql and it also autocommits.

Over on the IBExpert side, you look at the tables you just updated and you
don't see the changes. Why is that? It is all about transaction isolation.

Since IBExpert's transaction began, your application has committed two
transactions. (Yes, beneath the layering, each CommitRetaining really
commits one transaction and automatically starts another, having identical
configuration to the one that just committed). But the transaction in
IBExpert is isolated to see only the snapshot of the database state as it
was when it began. IBExpert won't see your app's changes until you commit
IBExpert's transaction.

OK, so, you click the Commit button in IBExpert and you still don't see the
changes? Why is that?

Well, while the new view of database state is now available to IBExpert,
the rows you are looking at there are still the image that was in the
buffer when the (now-committed) transaction began. You then need to
refresh IBExpert's buffers to pull up the latest view and see the changes
done by your app's two transactions.

......
If, on the other hand, the IBExpert transaction is in Read Committed
isolation, it won't be necessary to commit the transaction that provided
the original view. The buffers are still out of synch with the new
database state, but IBExpert's transaction can "see" the changes. In this
case, a refresh will resynch the buffers.
......
You need to think of visibility in two ways: what the transaction can see
(depends on isolation) and what the user can see through controls (depends
on the sync state of the buffers).

--- DML operations launched via dataset methods (Insert/Edit/Delete) kick
off a series of activities in the buffers that ensure the user's unposted
changes are visible to her as long as she is looking at those buffers
through her controls.
--- DML ops executed (equivalent to "posted") and then committed through a
raw statement (ib_dsql or ib_cursor) will be *visible* to the transaction
itself, as well as to any other transactions that are in Read Committed
isolation. However, the changes are not visible to the user until any
dataset that is displaying that data is resync'ed - either by refreshing or
by one of IBO's resync'ing tricks.

Helen