Subject Re: [firebird-support] Re: Accessing tables across databases
Author Sam Hunt
Sam Wrote:
To get around this limitation of FB, my app uses two Delphi ADOConnections.
One connects the AuctionTracker database and the other connects the
Constituent database.
The following sql uses two TADOQueries, qAuction (connected to the
AuctionTracker db) and qConstituents (connected to the Constituent db)
to limit the number of records displayed in the Guests table (connected
to the AuctionTracker db). Works fine for me. I've created some fairly
complex queries using this technique.
Hope this helps.
Sam Hunt

with qGuests do
sql.add('select * from Guests');
auctionID='+#39+qAuction.fieldbyname('AuctionID').asstring+#39+' ');

Adam wrote:

> --- In, "joseigd" <joseigd@y...> wrote:
> > In one FB database, I need to access a handful of tables from another
> > one.
> Firebird does not currently support cross database queries.
> > Can an SP do this? I mean, an SP in DB #1 that connects to DB #2,
> > gain access to the tables it needs and then closes the connection.
> It would be possible but highly unadvisable to create an external
> function (UDF) that connected to db2, but it would not be pretty and I
> do not see how it would return more than one record per connection so
> it would be incredibly slow.
> It is a relatively trivial task to create a service that polls tables
> from one database then inserts it in another. The single transaction
> can span multiple databases, meaning you still get your atomicity. You
> could use events to know when you need to "poll and pump". You may even
> be able to do something using external tables that both systems can
> access (mind you I do not know what happens if both try and access it
> at the same time).
> Probably not what you wanted to hear, but there is currently no
> mechanism to do what you are after.
> Adam