heterogeneous joins using JDBC

Thread view:

N.V.Dev - 14 Apr 2004 06:08 GMT

All,

Would like to if joins across multiple products are possible using
JDBC

The requirement is that we have Informix and Oracle installed under
AIX and HP-UX respectively. Need to compare a set of tables between
Informix and Oracle.

If joins across multiple products are not possible can ResultSets be
compared ?

Any ideas and suggestions is appreciated!!

Thanks in advance

Dev

Reply to this Message

Robert Klemme - 14 Apr 2004 10:45 GMT

> All,
>
[quoted text clipped - 4 lines]
> AIX and HP-UX respectively. Need to compare a set of tables between
> Informix and Oracle.

It's not possible since JDBC is an abstraction of a single database's
interface. Each driver is only capable of accessing a single db. Note
that a driver that would indeed allow to access multiple database would
have to implement a great portion of database functionality including
transactions and the like. AFAIK there are products that attempted to
provide a uniform access layer to a set of heterogenous databases but
these products were never quite successful due to the complexity and
performance degradation.

> If joins across multiple products are not possible can ResultSets be
> compared ?

I would not rely on some equals() be be implemented correctly for
ResultSets. But of course you can compare them programmatically - if they
are not too big. You can tweak that a bit by employing ordering of the
results if that's possible in your case. In that case you don't need to
load the comple results into memory but you can compare them record by
record.

Regards

robert

Reply to this Message

Davis Swan - 16 Apr 2004 00:45 GMT

> > All,
> >
[quoted text clipped - 27 lines]
>
> robert

Our product, Super*SQL, is intended to provide a solution to
cross-database connectivity, amongst other things. The Beta release
allows you to connect to one database, run a query to populate a
virtual clipboard, then connect to the other database and use the
contents of the virtual clipboard in a new query.

Granted, this is a very limited approach, but it might be enough for
what you want to do. A procedural language implementation is
currently under development and scheduled for release this summer
which will allow full integration of queries from any number of
databases.

Take a look at the product at www.sqlmagic.com and see if will help
you.

Cheers
Davis Swan
President, SQL*Magic. Ltd.

Reply to this Message

Ken North - 30 Apr 2004 00:44 GMT

> Would like to if joins across multiple products are possible using
> JDBC

It's not implicit with JDBC. You'll need to use a middleware server that can
connect to heterogeneous databases. You connect to the JDBC server, which in
turn connects to one or more databases.

Take a look at "JDBCT Driver and Server Providers"
http://www.sqlsummit.com/JDBCVend.htm

Look for the products that have numerous data sources listed under the right
column.

Reply to this Message

heterogeneous joins using JDBC

Free Magazines