SPUG: querying multiple databases with one $dbh

Michael R. Wolf MichaelRWolf at att.net
Fri May 26 14:00:45 PDT 2006

A student in my Perl+CGI+DBI class is used to working in PHP and often
uses MySQL to query more than one database.  Not only does the SQL
connect to multiple databases, but it also does a join across the
multiple databases.  It does all this without her having to explicitly
connect to any databases.  She can do this directly with MySQL from
the command line, so it appears (to my niave DBI mind) that she should
be able to do it from the DBI.

    SELECT table1.a, table2.b 
      FROM db1.table1 table1, db2.table1 table2 
      WHERE table1.c = table2.c 

I'm assuming that she can do something like this.  Any comments?

    $sql = "as given above";
    $dbh = DBI::new('DBI:mysql:db1', undef, undef, {AutoCommit=>1};
    $sth = $dbh->prepare($sql);

She wants to avoid getting two $dbh's because then she'd have to do
the join from Perl rather than having MySQL do it for her.

Is MySQL connecting/disconnecting each time she runs this query, or is
there some magic where it caches the DB Handles behind the scene?

Is there another way to have multiple connections via the DBI without
having to do multiple DBI::new()'s?

Michael R. Wolf
    All mammals learn by playing!
        MichaelRWolf at att.net

More information about the spug-list mailing list