SPUG: querying multiple databases with one $dbh
Dave O
cxreg at pobox.com
Fri May 26 16:05:35 PDT 2006
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/
>
>
>
More information about the spug-list
mailing list