Query DBI cross two mysql databases

dbi join from two databases

[Perllover]

Working several years with one of my favorite modules; DBI I bumped into a challenge:

Joining a query from two different database handles.

It is not about the SQL syntax or the two handles. I know I can keep two connections alive, for example: dbh and dbh2.

I want to execute a join query into one 'fetchall_arrayref'.

In my admin appl. Navicat my query works, but in my perl script not.

Am I missing something?

Greetings from Holland.

My query:

SELECT
database_a.klanten.id,
database_a.klanten.naam,
database_a.klanten.voornaam,
database_a.klanten.tussenv,
database_a.klanten.gebdatum,
database_a.klanten.straat,
database_a.klanten.huis_num,
database_a.klanten.toev,
database_a.klanten.postcode,
database_a.klanten.plaats,
database_a.klanten.land,
database_a.klanten.email,
database_b.bet_soort.soort_vb,
database_b.producten.bestelhoev,
database_b.producten.valuta_premium,
database_b.producten.bedrag_premium,
database_b.producten.soort_premium,
database_b.producten.aantal_premium,
database_b.producten.dosis_premium
FROM
database_a.klanten,
database_b.bet_soort,
database_b.producten
WHERE
database_a.klanten.id = '#*$!x' AND
database_b.bet_soort.id_vb = 'x' AND
database_b.producten.prod_id = '#*$!x'

[phranque]

perhaps you are looking for the FEDERATED storage engine [dev.mysql.com] which is available beginning with MySQL 5.0.3.
i believe in this case you would just use the one file handle and mysql should handle the access to the "remote" db.

[Perllover]

Thank you phranque!

Yes I have missed something, Federated tables is (at last) in MySQL.

You set the connection params in the table create..

Example:

create table users
(user_name varchar(100) not null, info varchar(200))
engine = federated
connection = 'mysql://x:y@127.0.0.1:11306/mydb/user_list'

I dont know about the safety...

I also found it to be possible with DBIx::MyServer.

Roaming the net I found some great artikles from Giuseppe Maxia a MySQL guru.

One on O'Reilly with the brillant title: "MySQL Federated Tables: The Missing Manual"

[oreillynet.com...]

and another useful artikel that sums it all up again for quick guidance: we all know but how do you do that exactly ;-)

[perlmonks.org...]

Enjoy,

Greetings,
Marco-Paul Breyer
From the dutch mountains...

[edited by: phranque at 11:32 am (utc) on Nov. 25, 2008]
[edit reason] fixed link [/edit]

[Perllover]

Sometimes a solution is closer than you think.

If you make a View ( > mySql 5.x )of the above query you can query that View with the DBI!

Greetings from Holland

[edited by: Perllover at 8:40 pm (utc) on Mar. 16, 2009]