This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Agile forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Multiple databases in a single query

 
Ashish Saraf
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic