Connect to a database conditionally

Question

I've been having some trouble connecting to my databases using the DBI module. I have a properties file, and in it it specifies whether I want to connect to my oracle or postgres DBs via a simple database=oracle or database=postgres. My property file is set up using the Config::Tiny module, and my variable are set up as so:

my $database = $config->{myDB}->{database};
                       ...

What I don't understand is even though this works for all my variables, if I try something like this to connect to whichever database is specified in the properties file...

if($database eq "oracle"){
    my $dbh = DBI->connect("dbi:Oracle:host=abc123-server;sid=XE;port=1521","User","Pass");
}
elsif($database eq "postgres"){
    my $dbh = DBI->connect("dbi:Pg:dbname=pepperoni;host=789xyz-server;port=5444;","Foo","Bar");
}else{
    print "Could not connect to a database";
}

...I end up with these errors:

Global symbol "$dbh" requires explicit package name at supportvuloop.pl line 70.
Global symbol "$dbh" requires explicit package name at reportloop.pl line 80.
Global symbol "$dbh" requires explicit package name at reportloop.pl line 81.
Global symbol "$dbh" requires explicit package name at reportloop.pl line 82.
Global symbol "$dbh" requires explicit package name at reportloop.pl line 88.

I can connect to either DB just fine when they are not part of an if condition, any ideas why it would cause errors now?

Answer 1

Your $dbh variable is not declared in correct scope. You should instead declare it before your "if" statement:

my $dbh;

if ($x) {
    $dbh=xxx1;
} elsif ($y) {
    $dbh=xxx2;
} else { # error
}
...

They was your code was structured, $dbh variable was declared as my inside the "if" block (and independently, inside "else" block), and therefore the rest of your code did not see those variables

For further reading:

• perldoc my

Answer 2

DVK already answer your question, but I wanted to give a tip. I would write the code as follows, since it separates configuration from database connection:

my %connect_info = (
   "oracle" => {
      dsn      => "dbi:Oracle:host=abc123-server;sid=XE;port=1521",
      user     => "User",
      password => "Pass",
   },
   "postgres" => {
      dsn      => "dbi:Pg:dbname=pepperoni;host=789xyz-server;port=5444;",
      user     => "Foo",
      password => "Bar",
   },
);

my $connect_info = $connect_info{$database}
   or die("Unknown database $database\n");

my $dbh = DBI->connect(
   $connect_info{dsn},
   $connect_info{user},
   $connect_info{password},
);