Bwahahahahahahaha!!!


Sorry. My evil twin went wild on me.

The cleanest way is to do the join in the application. I currently join
across Oracle (2 DB's), Access (never bothered to count), DB2 (3 DB's),
and VSAM (1 DB) all at once. The mechanism for the join is simple.

I have two kinds of application joins - preloading joins where the
independent result set is large and the dependent sets are small, and
looping join for when the independent set is small and the dependent
sets are large.

Both use essentially the same actual join mechanism, but with slightly
different control logic.

Load the small result sets into memory, in a HashMap or a TreeMap. Key
the map by whichever column(s) you need to locate the result you
eventually want to join against.

The first column in each result set is used as a key to look up the rows
in the other Map, and the columns of the dependent rows are tacked onto
the end of the independent result set's row. If you need to key further
joins off of the independent result sets, make sure that those keys are
represented in the Map as well.

For performance, I run each query in its own thread with a syncpoint
between kicking off the queries and actually starting the join.

For what it's worth, this could be applied at the UDF level if someone
was really ambitious and masochistic (I'd wait for Vulcan, myself).

On Thu, 2005-08-11 at 23:41 +0000, Adam wrote:
> --- In firebird-support@yahoogroups.com, "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.
>