aspose file tools*
The moose likes JDBC and the fly likes Multiple databases in a single query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Multiple databases in a single query" Watch "Multiple databases in a single query" New topic
Author

Multiple databases in a single query

Ashish Saraf
Greenhorn

Joined: Sep 08, 2001
Posts: 14
I need to query multiple database in a single query.
e.g.
Database d1 contains table t1 with column id
Database d2 contains table t2 with column id
I need a query like
SELECT * FROM t1, t2 WHERE t1.id = t2.id;
is this possible in jdbc? if yes can anyone help with some samle code?
thanks,
Ashish Saraf

Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
In most database systems the full table name is
database.owner.table
When you just use the table name, it is using default database and owner parameters
Dan
Ashish Saraf
Greenhorn

Joined: Sep 08, 2001
Posts: 14
thanks for your reply,
could you elaborate it further please?
Also the the databases in question can be from different vendors e.g. oracle, ms sql, sybase etc. Database d1 could be oracle and Database d2 could be sybase
thus is there any way to establish that the driver associated with these individual databases support multiple databases in the single query
Is it possible to give some sample code?
Regards,
Ashish Saraf
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I'm going to take a guess here and say that you are out of luck. You can join 2 tables in 2 different databases from the same database vendor(vendor specific capabilities). But if you are trying to join or whatever functionality you need, the databases deal with tasks differently. although they accept the same SQL commands, the way Oracle joins a table is different(internal structures are different) from the way Sybase joins a table etc.. So how do you compare apples and oranges? The jdbc driver point you made earlier would also restrict this from being possible.
the above isn't fact, only a hypothesis.
Jamie
Ashish Saraf
Greenhorn

Joined: Sep 08, 2001
Posts: 14
Hi,
you can connect to two different database via URL1 and URL2
Connection conn1 = DriverManager.getConnection(URL1, Username,
Password);
Connection conn2 = DriverManager.getConnection(URL2, Username,
Password);
but my problem is how the execute query on two connections sumultaniously?
Regards,
Ashish Saraf
------------------
Anders Domeij
Greenhorn

Joined: Sep 29, 2001
Posts: 4
Ashish,
DB2 udb ver 7 has a notion of federated databases that 'tricks/fools' applications into believing that tables from databases actually remote to the DB2 server appear as local tables within the server but with different schea names.
i.e. correctly set up a query to the DB2 server referencing tables alpha.names and beta.names actually would read data from samples.name in 'server2' and employee.names in 'server3'.
The application would still only have one connection though, namely to 'server1'.
/Anders
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
Oracle can link to other remote Oracle tables once the link has been established in the database and you have security to use it
I believe its table@linkname
Perhaps the newer Oracle version 8i & 9 have even more capability, and maybe even allow to establish link to other database types (SQL SERVER, DB2). But I don't know.
Dan
Ramon Lawrence
Greenhorn

Joined: Jul 04, 2012
Posts: 1
It is possible to query multiple databases with one query using JDBC and not make any server changes.

A tutorial is available at multiple database query tutorial.

There is also a JDBC driver available called UnityJDBC that allows any type of cross-database and multiple database query. A free trial can be downloaded at UnityJDBC.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Multiple databases in a single query