Subject Re: [ib-support] Transaction spanning 2 databases ...
Author Helen Borrie
At 11:49 AM 19-09-02 +1000, you wrote:
I wrote:

> >Fb/IB supports 2-phase commit so you don't handle the rollback logic
> >yourself. Your pseudocode model is more like this:
> >
> >start transaction;
> > connect db1;
> > connect db2;
> > Call function to update db2;
> > update db1;
> >
> >commit work;
> > except on failure
> >rollback rolls back all;

Modify this to:

connect db1;
connect db2;
start transaction;
Call function to update db2;
update db1;
commit work;
except on failure
rollback rolls back all;


>Is this possible in IB version 5.5 or just FB/Ib ?

Yes, certainly.


>And I notice you start a transaction and then connect to both db's
>whereas in our ESQL application we are already connected to the first
>db. The second db will be remote.

Yes, sorry to put you crook. Once you are connected to both (or several)
databases, you then set up the transaction with a USING clause to list the
databases that it can look at. I'm not even certain that a USING clause is
actually *required* - it's quite likely there to enable you to limit the
system resources where you have a lot of database connections around.

>Are there any esql examples of this anywhere?

Not that I'm aware of. The Programmers Guide has a little, spread around
in several places, but nothing particularly useful in the way of sample code.

But you seem to be one of our more active ESQL programmers (pioneer?) so
I'm sure your contributions once you've got it working would be effusively
received on the Really Useful pages of the Fb website. :-)

heLen