SPUG: querying multiple databases with one $dbh

[Dave O]

I don't believe that MySQL supports Oracle-like DBLink functionality,
although you can use the SQL as described if the 2 logical databases are
in the same physical MySQL instance, thus accessible with a single socket
and $dbh.  That's probably what she was doing.

	Dave

On Fri, 26 May 2006, Michael R. Wolf wrote:

> 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
>
>
> _____________________________________________________________
> Seattle Perl Users Group Mailing List
>      POST TO: spug-list at pm.org
> SUBSCRIPTION: http://mail.pm.org/mailman/listinfo/spug-list
>     MEETINGS: 3rd Tuesdays
>     WEB PAGE: http://seattleperl.org/
>
>
>