wood burning stoves 2.0*
The moose likes JDBC and the fly likes how to access MSAccess and Oracle in one query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to access MSAccess and Oracle in one query" Watch "how to access MSAccess and Oracle in one query" New topic
Author

how to access MSAccess and Oracle in one query

Seshi Reddy
Greenhorn

Joined: Mar 05, 2006
Posts: 1
In one scenario i want to display the data from two databases i.e, they r different database(MSAccess and Oracle). Im trying to use .properties in Java to load the database connections. pl reply ASAP.
Regards

Seshi
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42370
    
  64
Welcome to JavaRanch.

While with some DBs it is possible to retrieve data from two of them in the same query, there is no way to do that if you have two different kinds of databases. You will have to write two queries. It is possible to come up with a transaction that spans two different kinds of databases, but not if one of them is Access.


Ping & DNS - my free Android networking tools app
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Actually, some databases allow you to configure a "gateway" or "bridge" or "link" to another database through it, sometimes even a different type of database. With such a thing configured, it then becomes possible to have a single query get data from 2 databases at once with a query in the style of:



However, this can create shall we say interesting performance problems and I wouldn't recommend it as a general solution.

Oracle asserts that it can be configured somehow for connectivity to an MS Access DB, here (Generic Connectivity):
http://www.oracle.com/gateways/index.html

However, I don't know how to do it, or even where in the documentation to start looking, though...
 
 
subject: how to access MSAccess and Oracle in one query