Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to access MSAccess and Oracle in one query

 
Seshi Reddy
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Pie
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic