tables in the different db

[xinyiman]

It often happens that I have two tables that I want to join that process through are on different databases (eg MySQL and the other is on about Firebird) how can I solve this problem?! Someone can explain?

[BigChimp]

You'd have to do that programmatically in your code.

Might be easier to just import data from one db into (a temporary table in) the other database.

[tatamata]

Use Microsoft Access, simple as that!
Really, it might be the biggest advantage of MS Access in comparisson to other DB tools, that it can process heterogenous queries...
Usually, I hate Microsoft, but for me MS Access is the single most brilliant application of all times!

[xinyiman]

Access, and I hate all that is Microsoft, I already have to deal with the world of work. For my projects, absolutely not.

Anyway, thanks for the suggestion.

[goodname]

Have not tried it as it is new. PostgreSQL 9.1 has a foreign table feature that reads tables in foreign databases. It specifically refers to MySQL. Don't know about Firebird though.
http://www.postgresql.org/docs/9.1/static/sql-createforeigntable.html

http://pgsnake.blogspot.com/2011/08/postgresql-91-meet-mysql.html

[BigChimp]

Firebird can also run queries in other Firebird databases, though only Firebird databases...

I concur with tatamata that MS Access is a very handy tool for this as you can link tables without copying the data. There might be other kind of query tools around that do the same but I don't know of any. If anyone does know of such a tool, please let me know.

If you feel like implementing a hierarchical dataset (a la .Net) i.e. dataset in dataset (which models a master/detail or foreign key relationship) in FPC so you can have 1 MySQL dataset that is relationally linked (Foreign key) to a Firebird dataset, I'd be happy too, but I think it's a bit more work than starting Access or importing your data 

[TurboRascal]

Access has another drawback, it costs money (if legal) and it's not part of the MSO editions people usually buy. The other solutions are much cheaper

[goodname]

Just found out about DBI-LINK. It is written in PERL and has been around for a few years. I'm assuming that it can read from Firebird, MySQL, and other engines that PERL can connect to.

http://pgfoundry.org/projects/dbi-link/

[BigChimp]

Just found out about DBI-LINK. It is written in PERL and has been around for a few years. I'm assuming that it can read from Firebird, MySQL, and other engines that PERL can connect to.

http://pgfoundry.org/projects/dbi-link/

... seems it's bound to PostgreSQL so you can get foreign data into/out of PostgreSQL, but won't help much if you've got e.g. MySQL and Firebird.

One other suggestion: you can use tools like SQLWorkbench/J to set up a copy/synchronisation job that will keep your tables in sync, so if you can live with duplicate data in 2 databases, you could use that, too.