CODEWITHSUNDEEP
mysqlsql-serveroracle-databasepostgresql
Azuan
Azuan

Reputation: 928

Heterogeneous database connection

I am a student, major in database management. On my 5th semester, we are required to create a system using heterogeneous database. We must use at least 4 different database. My choice would be MySQL, MS SQL Server, Oracle and PostgreSQL since these are among the most popular and matured database.

The problem is that so far, no group has ever manage to connect to 4 different database. I have heard that using Java hibernate spring could connect to different database, but I am trying to connect to different database on the database level, not on the application level. Using something like Oracle's database link. But as far as I know, MySQL does not have this feature.

If in the industry, what are the common ways to deal with heterogeneous database? Or is there any standard library for me to do this? I hope to get some guide on how should I deal with heterogeneous database using the industry standard

Upvotes: 6

Views: 1489

Answers (2)

kgrittn
kgrittn

Reputation: 19491

You might want to look into the Foreign Data Wrapper capabilities. It is part of the SQL standard, and is intended to allow access between different database products. PostgreSQL has options to connect to all the others you mentioned.

I imaging that other products have similar capabilities.

Upvotes: 0

MicSim
MicSim

Reputation: 26796

Heterogeneous databases is a tough area and there's a lot of research going on. You can't expect an out of the box solution. It vastly depends on the databases, schemas, data, security concerns involved. To get you going, read this paper: A Multidatabase System as 4-Tiered Client-Server Distributed Heterogeneous Database System

If you are free in choosing the scenario, then make your life as easy as you can:

  • use the same schema on all databases
  • use plain JDBC access for each database (you will learn more this way and you don't have to deal with ORM framework bloat)
  • just use one single, simple table at the beginning
  • build up the required components for a distributed scenario (check the linked paper and search the internet for details)
  • put everything together
  • enjoy

Upvotes: 2

Related Questions