Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

error handling with dbi

by gandolf989 (Scribe)
on Feb 16, 2016 at 15:47 UTC ( #1155365=perlquestion: print w/replies, xml ) Need Help??

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; }

Replies are listed 'Best First'.
Re: error handling with dbi
by 1nickt (Abbot) 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 ) {
    to:
    if ( not defined $dbh ) {
    Hope this helps!


    The way forward always starts with a minimal test.
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
    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; }
    "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)
      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.
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=> +{}});
    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)
    

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1155365]
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (6)
As of 2019-11-15 07:33 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Strict and warnings: which comes first?



    Results (80 votes). Check out past polls.

    Notices?