Joining MySQL and Informix tables

I have a table in MySQL that I need to join with a couple of tables in a different server. The catch is that these other tables are in Informix.

I could make it work by selecting the content of a MySQL table and creating a temp table in Informix with the selected data, but I think in this case it would be too costly.

Is there an optimal way to join MySQL tables with Informix tables?

Answers


I faced a similar problem a number of years ago while developing a Rails app that needed to draw data from both an Informix and a MySQL database. What I ended up doing was using of an ORM library that could connect to both databases, thereby abstracting away the fact that the data was coming from two different databases. Not sure if this will end up as a better technique than your proposed temp table solution. A quick google search also brought up this, which might be promising.


This can sometimes be solved in the database management system with a technique called federation. The idea is that you create virtual tables in one of the two systems that makes queries to the other database system on demand.

For both MySQL and MariaDB there is the FederatedX storage engine that unfortunately only works with other MySQL/MariaDB systems. This is a fork of the older, but as far as I know unmaintained, Federated storage engine.

Some might also consider migrating to MariaDB where you can use the CONNECT storage engine which contains an ODBC client.


What I ended up doing is manually (that is, from the php app) keeping in sync the mysql tables with their equivalents in informix, so I didn't need to change older code. This a temporary solution, given that the older system, which is using informix, is going to be replaced.


Need Your Help

Button does not display

android xml

I am using android studio and I am trying to add this button to a blank activity but the button does not appear when I run the app and I don't know what the issue is.

How to update Oracle CLOB column with long string using C# and Oracle Data Access Client

c# sql oracle oracle11g oracle-sqldeveloper

I'm trying to update a CLOB column in my database with a long string containing the HTML contents of an email. There are 18,000 characters in the record I'm having an issue with.