error handling with dbi

[gandolf989]

I am working on a script that will run on Redhat 6.5 with Perl 5.10.1 and an Oracle 11.2.0.3 client. I need to connect to a few dozen Oracle databases using 10g, 11g or 12c. Unfortunately, there are two possible passwords that could get used, and I don't have a list of which password to try. Hence, I want to try one password and if that does not work try the other. Otherwise, I will just add it to an array that will print out at the end and I can look at those databases and possibly change the password. I am using the system to connect to every database.

How should I do the error handling to deal with not being able to connect to a database? Also when I get an error and try to exit, the script doesn't exit, but restarts from the beginning. How can I change that behavior?

Thanks in advance!

sub connect_to_db {
   my $tns_name            = shift;
   my $logger              = shift;
   my $run_as_user         = shift;
   my $system_password     = shift;
   my $system_password2    = shift;

   my $dbh;
   my $correct_password;

#  try connecting with one password then the other, if both passwords 
+fail mark it as failed
   try {
      $dbh = DBI->connect( "DBI:Oracle:".$tns_name, $run_as_user, $sys
+tem_password,
           { PrintError => 1, RaiseError => 1, AutoCommit => 1 } );
      $correct_password = $system_password;
   } catch {
      print "wrong password, try another\n";
   };


   if ( undef $dbh ) {
      try {
         $logger->info( 'password two |'.$system_password2.'|');
         $dbh = DBI->connect("DBI:Oracle:".$tns_name,  $run_as_user, $
+system_password2,
              { PrintError => 1, RaiseError => 1, AutoCommit => 1 } );
         $correct_password = $system_password2;
      } catch {
         print "wrong password, add it to the connection issues list\n
+";
      };
   }

   if ( undef $dbh ) {
      push @db_connect_issues, "error:".$tns_name." -- ".$DBI::errstr;
   } else {
      $users_and_passwords{ 'INSTALLER'}{$run_as_user } = $correct_pas
+sword;
      print ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> The right password is
+ $correct_password <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<\n";
   }

   return $dbh;
}

[1nickt]

You are calling undef() on your DB handle, rather than testing to see if it's defined.

Try changing:

if ( undef $dbh ) {

to:

if ( not defined $dbh ) {

Hope this helps!

[crusty_collins]

I would not try the hit or miss approach.

use a file or put it in the script and define which database for each password.

Connect to each individually.

env.ini

CARDTESTSID    = CARD1
CARDTESTSVER   = card1
CARDTESTUSER  =  pass
CARDTESTPASS   = pass
CARDTESTPRT    = 1521

CARDSID    = CARD2
CARDSVER   = card2
CARDUSER   = pass
CARDPASS   = pass
CARDPRT    = 1521

PRISMTESTSID = JET1
PRISMTESTSVER = jet1
PRISMTESTUSER = pass
PRISMTESTPASS = pass
PRISMTESTPRT = 1522

PRISMSID = JET2
PRISMSVER = jet2
PRISMUSER = pass
PRISMPASS = pass
PRISMPRT = 1522

A simple parser for .ini

sub getEnv {
   my $file = shift(@_);

   unless (-f $file) {
      print "Could not find $file.\n";
      exit(1);
   }

   open (FILE, "< $file");
   while (<FILE>) {
     chomp;
     next if /^\s*\#/;
     next unless /=/;
     my ($key, $variable) = split(/=/,$_,2);
     $variable =~ s/\s+//g;
     $key      =~ s/\s+//g;
     $ref->{config}->{$key} = $variable;
   }
   close FILE;
}

[gandolf989]

Unfortunately, I don't create all of the databases. I query the databases that I need to connect to from a database. I just need to figure out the error handling and that should be enough. I will try using "not defined" in the if.

[jeffa]

There are many, many ways to accomplish what you wish. But you also have to ask yourself if it really worth your time to write a script for this task when you should have reliable configuration tools. Having said that, here is a small script that demonstrates using DBI's HandleError hook. The code will stop after take the first successful connection, which may not be quite what you want:

use strict;
use warnings;
use DBI;
use Data::Dumper;

sub dbi_error { warn "failed with @_\n" }

my @attempts = (
    [qw( wrong wrong )],    # this would be a wrong user/pass
    [qw( correct pass )],   # this would be a correct user/pass
    [qw( wrong wrong )],    # and another wrong one
);

my $dbh;
for (@attempts) {
    $dbh ||= DBI->connect(
       qw(DBI:mysql:information_schema:localhost), @$_,
       { HandleError => sub { dbi_error( @$_ ) } },
    );
}

print Dumper $dbh->selectall_arrayref('select * from TABLES', {Slice=>
+{}});

But as you can see, DBI does indeed allow you to control what happens when an error occurs. You do not necessarily need to wrap the call in a try-catch block. Hope this helps!