homepage Welcome to WebmasterWorld Guest from 54.167.10.244
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
Forum Library, Charter, Moderators: coopster & jatar k & phranque

Perl Server Side CGI Scripting Forum

    
Query DBI cross two mysql databases
dbi join from two databases
Perllover




msg:3793375
 10:35 pm on Nov 24, 2008 (gmt 0)

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




msg:3793627
 7:50 am on Nov 25, 2008 (gmt 0)

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




msg:3793664
 9:37 am on Nov 25, 2008 (gmt 0)

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




msg:3871667
 8:39 pm on Mar 16, 2009 (gmt 0)

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]

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved