File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Look up data from different databases Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Look up data from different databases" Watch "Look up data from different databases" New topic

Look up data from different databases

Suneel Setlur
Ranch Hand

Joined: Nov 26, 2000
Posts: 202
I have a situation where I need to look up data from different databases.This is the case :
I have data on Oracle and also data on Informix. I have entire access to the data on Oracle but not on the other. The only thing I can do is read data. How do I frame my query to retrieve data. Can some help me. Please give it to me as a sequence of steps

Thomas Paul
mister krabs
Ranch Hand

Joined: May 05, 2000
Posts: 13974
I don't see what the problem is... simply create two separate Connection objects, one for each database.

Associate Instructor - Hofstra University
Amazon Top 750 reviewer - Blog - Unresolved References - Book Review Blog
Suneel Setlur
Ranch Hand

Joined: Nov 26, 2000
Posts: 202
Hi Thomas
Thanks for the answer. But I think I must also add that I don't have a common field to link the 2 databases. What I wanted to know was that do I need to create a relation table and then frame a query. If I do need to, how do I go about it?

Ashish Saraf

Joined: Sep 08, 2001
Posts: 14
problem here is how to fire a query on two connections simultaniously?
Any suggestions?
Ashish Saraf
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

as of the latest release of jdbc(not including the jdk 1.4 beta) you can only have one connection per statement/prepared statement. So you can not do this in java as you would need two different connections if you were to join 2 tables from different databases in one statement.
You can have 2 different connections open at the same time, but you can only have 1 connection for a statement.

[This message has been edited by Jamie Robertson (edited September 24, 2001).]
Anders Domeij

Joined: Sep 29, 2001
Posts: 4
Ashish & Jamie,
Db2 UDB has a function called federated databe support.
This lets a DB2 server 'front' two or more different databases so that an application can use only one connection (to the DB2 server) and actually access two other databases.
I agree. Here's the link:
subject: Look up data from different databases
It's not a secret anymore!