I have a Perl script which connects to many databases using different connection drivers.
Is there any way I can write a single disconnect function in the end to disconnect if there are any active session?
Example :
connection 1: $dbh->oracle;
connection 2: $dbh->sql
Can I have a common disconnect string for both databases?
You can implement that yourself using visit_handles from DBI.
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect( 'DBI:mysql:database=test;host=localhost', 'root', 'pw' );
my $dbh2 = DBI->connect( 'DBI:mysql:database=test;host=localhost', 'root', 'pw' );
DBI->visit_handles(
sub {
my ( $driver_handle, $info ) = @_;
if ($driver_handle->{Type} eq 'db') {
# clean up transaction or simply disconnect for each handle
$driver_handle->disconnect;
}
return 1;
}
);
The code reference passed to visit_handles will be called for each driver handle. If it returns a true value, it will subsequently call visit_child_handles with the same code reference. That way, you can match which ones are database handles (db) and disconnect them explicitly.
As Borodin states in their answer you need to take care of the half-done transactions before disconnecting. This solution only gives you a means to get all the connection handles from a single place.
visit_handles. But calling disconnect on everything explicitly will produce the same warning as the implicit disconnects done when perl destroys a program, and you need to rollback or commit on every handle that has an open transaction.
Each database connection will be represented as a separate database handle $dbh
Clearly you could write a subroutine that disconnects from all connected database handles, as long as your list of connected databases is always the same
I'm not sure why you're fixating on the connection strings. Presumably you mean the DSNs (Data Source Names) that you used in the call to DBI->connect? They are simply parameters to the connection operation and a database handle cannot be identified after the fact from its original DSN
Issuing rollback() due to DESTROY without explicit disconnect()
This implies that you are using transactions. The best way would be to call rollback or commit yourself to end the transaction. It's a bad idea to leave a transaction open when the program terminates as you are relying on the default behaviour of the database driver
The documentation for DBI::disconnect says this
Generally, if you want your changes to be committed or rolled back when you disconnect, then you should explicitly call "commit" or "rollback" before disconnecting.
You will also get the same warning if you call disconnect while a transaction is still open, so a commit or rollback is definitely the correct method
It is fine to let Perl's destruction sequence implcitly disconnect all database handles as long as no transactions are left open at the time