Subject Re: [IB-Architect] Proxy Query ?
Author Ann W. Harrison
Continuing the discussion:

> > > >
> > > >For example sybase support proxy table that is similar to this idea,
> > > >and MsSql does not have problems to access more databases at all.
> >
> > Bitter as gall. InterBase doesn't have any trouble accessing several
> > databases, but its SQL interface does. That's getting higher on the
> > Firebird to do list.
>Is there any documentation on how this multidatabase access works? which
>language? Is this the yvalve?

There is documentation in the reproduction of the 1987 books
in the Programmer's guide, but it explains how to use multiple
databases, not how they work internally. The old data manipulation
language (GDML) included a database alias that could be applied
to a table.

ready my_db.gdb as mine;
ready your_db.gdb as yours;

start transaction;
for this in mine.table1
for that in yours.table1
with this.value1 = that.value1
printf ("Here: %s %d", that.salary);
if (this.salary < that.salary>
modify mine.table1 using this.salary = that.salary;

The "ready" statements establish connections to the two
databases. The "start transaction" includes the information
to start transactions on both databases - and yes, it's handled
at the Y-valve level. A single transaction handle is returned
to the program.

The commit turns into two calls: prepare and commit. The prepare
call causes both databases to write recovery information and flush
it to disk. Any deferred work is performed and everything is written
out. The commit merely changes the transaction state - a one page
write on each database. In the unlikely event of a failure after the
completion of the prepare on one database and the completion of the
commit on all databases, gfix can file the recovery information and
make the databases consistent.

If anyone cares, the nested "for" loops are as close to real
cross database operations as we went, because the next step is
a big one.

> > >2) The UDF processing has no associated transaction context.
> >
> > As above - a serious problem when trying to access back into the
> > current database, but irrelevant for foreign database access.
>I'm not sure how you are thinking, but to me this looks like a distributed
>transaction is happening. ... I assume
>the UDF would be able to do updates as well as queries.

That is so far beyond the scope of current UDF's that I never even
considered it. UDF's are not the way to establish cross-database

>By the way, how does firebird do 2pc now? Is it documented reasonably well?
>(I know, RTFM, but which one?)

As above. The operations guide has a tiny bit about recovering
limbo transactions. The whole mechanism was basically abandoned
because the BDE was supposed to handle all cross-database issues.


We have answers.