Re: grab info from two databases

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Tue, 28 Aug 2001 12:33:41 +0200
Message-ID: <9mfs0e$omb$1@ctb-nnrp1.saix.net>

"Andrew Phuong" <caop_at_infosci.com.au> wrote

> I've been told that one process can only connect to 1 database at a time.
> In which case how can i grab information from two databases without having
> to disconnect from one to get to the other?

What do you mean with "one process" Andrew?

Any "process" (i.e. Unix process, Windows process, pthread or Windows thread) can open _multiple_ database connections to the same or different databases. This single process then has multiple database handles (query handles/session handles/whatever) to communicate with the databases.

The problems with this approach is essentially overheads on the client. Let's say you need to pull 100MB of data from database A, and after processing it, insert the results into database B.

Using a client process it will look something like this (set your mail reader font to fix and not proportional):

database A             +-> database B
   |                   |

+--> Application --+

This likely means that you are pulling 100MB of data over the network twice (if the application resides on a different platform).

A more integrated approach is to make use of distributed database. Here is how it looks:

       database A --> database B
           |
single logical database view
           |
       Application

Here the application sees a single database. Behind the scenes, database A is linked via a distributed database connection to database B (which is what Sybrand referred to).

The advantage is that should you pull a 100MB worth of data across, that goes directly from database B and into database A (assuming you do not also pull it down to application level for processing and then up again to db A, in which case you succeeded in tripling the network traffic).

Distributed databases are great - IF used correctly. Things like a distributed join can be hell (and often is) on resources and the network.

What to do if you do not have a distributed connection/support between database A and database B? For example, you are running SQL-Server and want to hook it up Informix.

Certain development tools allow heterogeneous joins in the client SQL, which is resolved at runtime. Delphi with Borland's BDE supported (still does?) this. You code you SQL SELECT statement, adding the database driver connection details as part of the table names to join. BDE then runs the SQL for you, pulls down the data from these different databases, performs the join locally (huge warning bells should be ringing in you head now), and then supply the data to you.

The bottomline is that whoever told you that one process can only connect to one database is a clueless idiot.

Wait.. don't tell me. You cubicle is just across Dilbert's! Damn.. it is weird how many share the same boss/manager as Dilbert... Whippings will continue until morale improves.

--
Billy

Received on Tue Aug 28 2001 - 05:33:41 CDT