error handling with dbi

gandolf989 has asked for the wisdom of the Perl Monks concerning the following question:

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;
}
[download]

Comment on error handling with dbi Download Code

Replies are listed 'Best First'.
Re: error handling with dbi by 1nickt (Canon) on Feb 16, 2016 at 16:09 UTC
You are calling undef() on your DB handle, rather than testing to see if it's defined. Try changing: `if ( undef $dbh ) {` [download] to: `if ( not defined $dbh ) {` [download] Hope this helps! The way forward always starts with a minimal test.	[reply] [d/l] [select]
Re: error handling with dbi by crusty_collins (Friar) on Feb 16, 2016 at 16:09 UTC
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` [download] 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; }` [download] "We can't all be happy, we can't all be rich, we can't all be lucky – and it would be so much less fun if we were. There must be the dark background to show up the bright colours." Jean Rhys (1890-1979)	[reply] [d/l] [select]
Re^2: error handling with dbi by gandolf989 (Scribe) on Feb 16, 2016 at 16:33 UTC
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.	[reply]
Re: error handling with dbi by jeffa (Bishop) on Feb 16, 2016 at 20:22 UTC
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=> +{}});` [download] 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! jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply] [d/l]


Do you know where your variables are?
	PerlMonks