tables in the different db lazarus / 16-Databases ---------------------------------------------------------------------- xinyiman · 2011-11-03 15:28 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 · 2011-11-03 16:23 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 · 2011-11-03 17:10 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 · 2011-11-03 19:34 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 · 2011-11-04 02:09 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 · 2011-11-04 08:49 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 · 2011-11-07 23:11 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 · 2011-11-08 13:46 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 · 2011-11-08 15:08 Quote from: goodname on November 08, 2011, 02:46:08 pmJust 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.