SQL Query over identical table on multiple systems

1
votes

Overview of application:
I have a Delphi application that allows a user to run queries over MySQL databases on different systems, using a TADOQuery component to retrieve the data. The user can retrieve data from an identical tables on two different systems (e.g. table_xyz in database_abc on system1 and system 2) by running two separate queries, and then the two sets of data are written to file and manually joined on the PC to form one report.

Question:
Is it possible to select data from an identical table/database on different systems with one query? I'm pretty confident that this isn't possible due to the limitation of the TADOQuery's connection string having to point to a single system, but I want to make sure that this is correct.

I know that it's possible to retrieve data from two identical tables in two different databases on the same system by using a UNION statement, e.g.:
select * from database1.table_xyz UNION select * from database2.table_xyz

What I need to do is similar to the above example, only retrieving data from databases that are on different systems. Basically, is it possible to somehow incorporate the connection string in the SQL statement, or connect in a different way somehow?

Like I said, I'm sure that this isn't possible, but it doesn't hurt to check!

1
mysqlsqldelphi
With MSSQL server this is possible because you have the notion of a "linked server". So server1 knows of server 1 as a linked server and as far as queries are concerned they can treat the linked server as if it were a different table prefixed by the server name. But I don't know about MySQL but you may want to do a search to see if MySQL supports something similar. - Shiv Kumar
I believe the Federated storage engine supports something similar dev.mysql.com/doc/refman/5.5/en/federated-description.html - Shiv Kumar

1 Answers

2
votes

That's called "distributed queries", and require database engine support. In MySQL, this can help you: http://dev.mysql.com/tech-resources/articles/mysql-federated-storage.html (available from 5.0 onwards). In Oracle that's called "database links" and as Shiv Kumar said as "linked servers" in MS SQL. One of the drawbacks of the federate engine is it requires you to create a table that mirrors the "remote" one, while other databases simply let you access the remote table once a link to the remote server is established, without having to keep declarations in sync. Also the remote database must be another MySQL database (other engines support distributed queries against etherogenous servers).