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'

What do you mean by multiple connection strings? Do you want to make more than one handle, each connecting to a different server? — simbabque
– simbabque, Commented Jan 5, 2017 at 10:55
server is same, the passwords are different with privileges. — Robie Nayak
– Robie Nayak, Commented Jan 5, 2017 at 10:57
So user and password are different, and you want multiple handles? Please specify what your output should be. — simbabque
– simbabque, Commented Jan 5, 2017 at 10:59
if it fails to connect with the first connection string it should try the second one and capture the errors if any. — Robie Nayak
– Robie Nayak, Commented Jan 5, 2017 at 11:02

simbabque · Accepted Answer · 2017-01-05 11:15:46Z

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.

Collectives™ on Stack Overflow

multiple connection strings to a database with different password

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related