Multiple Connections on Apache::DBI?

[Mark D Wolinski <ma...@mrmark.com>]

Hi all,

I'm using DBI to connect to a mySQL DB.  I want to make two connections at
the same time, as seen here:

$DBI{database} = "DBI:mysql:db_a";
$DBI{hostname} = A hostname;
$DBI{username} = A Username;
$DBI{password} = A Password;

$DBI{db} = join (":",$DBI{database},$DBI{hostname});
$db_driver = DBI->install_driver("mysql",$DBI{'username'},$DBI{'password'});
$db_master = DBI->connect($DBI{'db'},$DBI{'username'},$DBI{'password'}, {
RaiseError => 1}) || die $DBI::errstr;

$DBI{forum_db} = "db_b";
$db_forums = DBI->connect($DBI{'db'},$DBI{'username'},$DBI{'password'}, {
RaiseError => 1}) || die $DBI::errstr;
$db_forums->do( "use $DBI{forum_db}" );

Basically, $db_forums can be any number of different db's depending upon
certain veriables, but to get a persistant connection, I'm connecting to the
server and then "use"ing the correct DB.

The problem that I have is that the system works fine under normal uses.

However, when I run it under mod_perl and Apache_DBI, the
$db_forums->do{"use $DBI{forum_db}" );  also changes the $db_master
reference as well.

So my question is, am I doing something wrong here?

$db_master will always point to one database, but $db_forums will point to
different ones.  I can "use" a the correct db before each reference to it,
but that seems like an awful lot of useless calls.

I can't figure out why changing $db_forums would affect $db_master.

Any clues are much appreciated.

Mark W

Re: Multiple Connections on Apache::DBI?

[Chris Winters <cw...@intes.net>]

* Mark D Wolinski (mark@mrmark.com) [000913 17:18]:
> Hi all,
> 
> I'm using DBI to connect to a mySQL DB.  I want to make two connections at
> the same time, as seen here:
> 
> $DBI{database} = "DBI:mysql:db_a";
> $DBI{hostname} = A hostname;
> $DBI{username} = A Username;
> $DBI{password} = A Password;
> 
> $DBI{db} = join (":",$DBI{database},$DBI{hostname});
> $db_driver = DBI->install_driver("mysql",$DBI{'username'},$DBI{'password'});
> $db_master = DBI->connect($DBI{'db'},$DBI{'username'},$DBI{'password'}, {
> RaiseError => 1}) || die $DBI::errstr;
> 
> $DBI{forum_db} = "db_b";
> $db_forums = DBI->connect($DBI{'db'},$DBI{'username'},$DBI{'password'}, {
> RaiseError => 1}) || die $DBI::errstr;
> $db_forums->do( "use $DBI{forum_db}" );
> 
> Basically, $db_forums can be any number of different db's depending upon
> certain veriables, but to get a persistant connection, I'm connecting to the
> server and then "use"ing the correct DB.
> 
> The problem that I have is that the system works fine under normal uses.
> 
> However, when I run it under mod_perl and Apache_DBI, the
> $db_forums->do{"use $DBI{forum_db}" );  also changes the $db_master
> reference as well.
> 
> So my question is, am I doing something wrong here?
> 
> $db_master will always point to one database, but $db_forums will point to
> different ones.  I can "use" a the correct db before each reference to it,
> but that seems like an awful lot of useless calls.
> 
> I can't figure out why changing $db_forums would affect $db_master.

Because it's the same handle! Since you're using the same connection
strings, Apache::DBI will connect the first time and cache the handle;
the second 'DBI->connect' call will return the already cached database handle.

You need to change the connect strings somehow so that Apache::DBI can
differentiate between the two. I've never tried it, but:

'DBI:mysql:dbname'

and 

'DBI:mysql:database=dbname'

Perform the same function even though they are syntactically
different. 

Good luck

Chris

-- 
Chris Winters
Senior Internet Developer    intes.net
cwinters@intes.net           http://www.intes.net/
Integrated hardware/software solutions to make the Internet work for you.

Re: Multiple Connections on Apache::DBI?

[Jamie Krasnoo <te...@planetphat.com>]

Apache::DBI overrides DBI in the creation of a connection to the
database in order to cache the connection. So the connections you created
below are seen as the same by Apache::DBI and when you change one you
change the other since the connection is comming from the same persistent
pool. Try specifying the database in the connection to make
different rather than switching to it after connecting.

ie:

$DBI{db2} = join (":", $DBI{forum_db},$DBI{hostname});


That should create a difference between the two so Apache::DBI will keep
them separate. And it also gets rid off an un-needed do() :)

Good luck,

Jamie

On Wed, 13 Sep 2000, Mark D Wolinski wrote:

> Hi all,
> 
> I'm using DBI to connect to a mySQL DB.  I want to make two connections at
> the same time, as seen here:
> 
> $DBI{database} = "DBI:mysql:db_a";
> $DBI{hostname} = A hostname;
> $DBI{username} = A Username;
> $DBI{password} = A Password;
> 
> $DBI{db} = join (":",$DBI{database},$DBI{hostname});
> $db_driver = DBI->install_driver("mysql",$DBI{'username'},$DBI{'password'});
> $db_master = DBI->connect($DBI{'db'},$DBI{'username'},$DBI{'password'}, {
> RaiseError => 1}) || die $DBI::errstr;
> 
> $DBI{forum_db} = "db_b";
> $db_forums = DBI->connect($DBI{'db'},$DBI{'username'},$DBI{'password'}, {
> RaiseError => 1}) || die $DBI::errstr;
> $db_forums->do( "use $DBI{forum_db}" );
> 
> Basically, $db_forums can be any number of different db's depending upon
> certain veriables, but to get a persistant connection, I'm connecting to the
> server and then "use"ing the correct DB.
> 
> The problem that I have is that the system works fine under normal uses.
> 
> However, when I run it under mod_perl and Apache_DBI, the
> $db_forums->do{"use $DBI{forum_db}" );  also changes the $db_master
> reference as well.
> 
> So my question is, am I doing something wrong here?
> 
> $db_master will always point to one database, but $db_forums will point to
> different ones.  I can "use" a the correct db before each reference to it,
> but that seems like an awful lot of useless calls.
> 
> I can't figure out why changing $db_forums would affect $db_master.
> 
> Any clues are much appreciated.
> 
> Mark W
>