How to join two query's from two different databases?

[Stannite]

Hi all,

I am working on a project where i have to TzConnection components which both have a TZQuery connected to them. The TZ connection components point two different databases, one is local and the other is remote.

I need to have a query which retrieves data using left and inner join from both connections. Can this be done or do i need to create multiple query's and join them afterwards?

any help will be appreciated

[Pitfiend]

I think that what you are asking is imposible to do with components alone. You must do that in the sql queries, but first you need to instruct your database engine about how to reach remote server from local one (ask your dba, that depends on your data engines).

If your servers are homogeneous (same engine in both sides), you are lucky and may have good chances to do what you are asking for, just need to namespace every object accordingly to their respective databases. For example:

select * from localserver.localdb.table as a, remoteserver.remotedb.table as b where a.fieldid=b.fieldid

If your engines are heterogeneous (for example mysql on one side and sqlserver on the other), you may need 3rd party libraries/products to bridge the gap. Again ask your dba about details. When the requirements are meet, the queries may look as my previous example.

The other solution I can imaginate, is you need to handle your queries in a two step process. First step you gather all the remote result into a local temporary table. Second step you apply any query to your local data and the temporary one.

In any case, you can't rely on this kind of process for real time applications. Any remote connection is subject to eventual lag.

[Stannite]

the databases are the same, i matched my local database with the remote.
Both are mysql.

i was wondering if this could work since a tzquery points to one tzconnection and for this to work i would think i need two tzconnections right?

i will try the two step process for now.

thanks for your answer

[EgonHugeist]

Stannite,

I know one german uses the TZGroupedConnection and it's Connection-Groups to join two SQLite databese with one TZQuery-Object. I don't know how but he wrote his approach works nice for hime.. Just a hint which can be checked on your side.

[mdaems]

Maybe a stupid idea, but testing looks like fun:
Two connections + two zquery (one for each connection). Then join the TZQueries in a Master-Detail relationship. The linkfields then match the corresponding records from the dfferent databases, so you don't have to write the link logic yourself. Or not?

Mark

BTW : can't you use the federation storage engine to link the databases together in mysql itself?