multiple connection strings to a database with different password

Question

Just trying to connect a database with multiple connection strings with different passwords. The passwords have different privileges. If one password fails, it should try with another one.

The code has been written as below. Though it works fine, how can we refactor code (eval & DBH) to handle multiple connection strings?

my %config = do 'dbconfig.pl';

my $dbh = eval { DBI->connect("dbi:Pg:dbname=".$config{db}.";host=$socket_nm;port=".$config{port}."", $config{user},$config{password},{RaiseError=>1,PrintError=>0}) };

if (!$dbh) {
$dbh = eval { DBI->connect("dbi:Pg:dbname=".$config{db}.";host=$socket_nm;port=".$config{port}."",$config{user},$config{password},{RaiseError=>1,PrintError=>0}) };         
}

if ( $@ ) {
#Handle Exceptions
}

dbconfig.pl contains :

db => 'db',
port => '5432',
user => 'db_ro',
password => 'Pass01',
password2 => 'Pass02'

Answer 1

You need to use a loop and retry until you get a working connection. In each loop, you need to grab the next set of config values and try to connect with it.

my @configs = (
    {
        # ...
        user     => 'user1',
        password => 'password1',
    },
    {
        # ...
        user     => 'user2',
        password => 'password2',
    },
);

my $dbh;
while ( not $dbh ) {
    my $config = shift @configs;    # grab the next config
    if ( not $config ) {
        # we ran out of configs to try
        die "Couldn't connect to database";
    }

    # try the config
    $dbh = eval {
        DBI->connect(
            "dbi:Pg:dbname=" . $config->{db} . ";host=$socket_nm;port=" . $config->{port} . "",
            $config->{user}, $config->{password}, { RaiseError => 1, PrintError => 0 } );
    };
    # we don't need to look at $@ here, but if we care we can still do it
}

The configs are now stored in an array @configs. Inside, there are hash references. In your loop we have a lexical $config, which contains the current one we want to try. Note that this also is a hash reference, so the you need to use $config->{...} with the arrow in the dsn.

We loop as long as $dbh is not set. That's the case until the eval inside of the loop returns a working database handle object.

We also need to exit the loop if we run out of configs. Dying seemed like a good idea for that.

If you want, you can handle the errors that eval is catching for you, but for this to work you don't have to do it. If you all you care about is that you get a working connection in the end, this should be sufficient.

---

Note: Your %config = do 'dbconfig.pl' is horrible. Please use a proper config file format like JSON and a module to read it. I really like Config::ZOMG as it supports lots of different formats and lets you combine multiple files into one config hash. But Config::Simple might be enough for you here.