These two connect( ) calls will create two
different connections:
my $dbh = DBI->connect
("DBI:mysql:test:localhost", '', '',
{
PrintError => 1, # warn( ) on errors
RaiseError => 0, # don't die on error
AutoCommit => 1, # commit executes immediately
}
) or die "Cannot connect to database: $DBI::errstr";
my $dbh = DBI->connect
("DBI:mysql:test:localhost", '', '',
{
PrintError => 1, # warn( ) on errors
RaiseError => 0, # don't die on error
AutoCommit => 0, # don't commit executes immediately
}
) or die "Cannot connect to database: $DBI::errstr";
Notice that the only difference is in the value of
AutoCommit.
However, you are free to modify the handle immediately after you get
it from the cache, so always initiate connections using the same
parameters and set AutoCommit (or whatever)
afterward. Let's rewrite the second
connect( ) call to do the right thing (i.e., not
to create a new connection):
my $dbh = DBI->connect
("DBI:mysql:test:localhost", '', '',
{
PrintError => 1, # warn( ) on errors
RaiseError => 0, # don't die on error
AutoCommit => 1, # commit executes immediately
}
) or die "Cannot connect to database: $DBI::errstr";
$dbh->{AutoCommit} = 0; # don't commit if not asked to
When you aren't sure whether you're
doing the right thing, turn on debug mode.
When the $dbh attribute is altered after
connect( ), it affects all other handlers
retrieving this database handle. Therefore, it's
best to restore the modified attributes to their original values at
the end of database handle usage. As of
Apache::DBI Version 0.88, the caller has to do
this manually. The simplest way to handle this is to localize the
attributes when modifying them:
my $dbh = DBI->connect(...) ...
{
local $dbh->{LongReadLen} = 40;
}
Here, the LongReadLen attribute overrides the
value set in the connect( ) call or its default
value only within the enclosing block.
The problem with this approach is that prior to Perl Version 5.8.0 it
causes memory leaks. So the only clean alternative for older Perl
versions is to manually restore
$dbh's values:
my @attrs = qw(LongReadLen PrintError);
my %orig = ( );
my $dbh = DBI->connect(...) ...
# store the values away
$orig{$_} = $dbh->{$_} for @attrs;
# do local modifications
$dbh->{LongReadLen} = 40;
$dbh->{PrintError} = 1;
# do something with the database handle
# ...
# now restore the values
$dbh->{$_} = $orig{$_} for @attrs;
Another thing to remember is that with some database servers
it's possible to access more than one database using
the same database connection. MySQL is one of those servers. It
allows you to use a fully qualified table specification notation. So
if there is a database foo with a table
test and a database bar
with its own table test, you can always use:
SELECT * FROM foo.test ...
or:
SELECT * FROM bar.test ...
No matter what database you have used in the database name string in
the connect( ) call (e.g.,
DBI:mysql:foo:localhost), you can still access
both tables by using a fully qualified syntax.
Alternatively, you can switch databases with USE
foo and USE bar, but this approach seems
less convenient, and therefore error-prone.
