Exception handling for errors in DBI->connect

[dhawal]

I need the program to continue even if there was an error in connecting to the database. So far with many tries, I have not been able to prevent program from terminating when there is an error in connecting to the database. The program works fine with correct password.

(Perl script is given at the end)

If I use 0 for RaiseError, I get following error:
-------------------------------------------------
DBI connect('acd1','intcustapp',...) failed: at tt_testdbi.pl line 12
Uncaught exception from user code:
Uncaught exception from user code:
Can't call method "prepare" on an undefined value at tt_testdbi.pl line 22.
eval {...} called at tt_testdbi.pl line 5

If I use 1 for RaiseError, I get:
---------------------------------
Uncaught exception from user code:
Uncaught exception from user code:
DBI connect('acd1','intcustapp',...) failed: at tt_testdbi.pl line 12
Carp::croak('DBI connect(\'acd1\',\'intcustapp\',...) failed: ') called at C:/Perl/site/lib/DBI.pm line 580
DBI::__ANON__(undef, undef) called at C:/Perl/site/lib/DBI.pm line 630
DBI::connect('DBI', 'dbi:Oracle:acd1', 'intcustapp', 'intcustapp2', 'HASH(0x183ef44)') called at tt_testdbi.pl line 12
eval {...} called at tt_testdbi.pl line 5

Program Code:
-------------
use strict;
use diagnostics;
use DBI;

eval { #Cath any kind of failures

my $DB = "Oracle"; # Database
my $DB_SID = "acd1"; # SID
my $DB_USER = "intcustapp"; # Database user
my $DB_PASS = "invalidpwd"; # Database password

my $dbh = DBI->connect( "dbi:$DB:$DB_SID",
$DB_USER,
$DB_PASS,
{
RaiseError => 1,
AutoCommit => 0
}
);

my $sql = qq{ SELECT sysdate, user FROM dual };
my $sth = $dbh->prepare( $sql );
$sth->execute();

my( $today_dt, $user_name );
$sth->bind_columns( undef, \$today_dt, \$user_name );

while( $sth->fetch() ) {
print "$today_dt, $user_name\n";
}

$sth->finish();
$dbh->disconnect();

};

if ($@ =~ /invalid /) # if error message has the word invalid
{
print $@;
}
elsif ($@) # some other error occurred
{
die $@;
};



Thanks
Dhawal

 

[missbarbell]

Try:

if ($@ =~ /invalid|failed/) # if error message has the word invalid or failed
{
print $@;
}


The error message that is being reported uses "failed:", which is why it'll fail to match just 'invalid'.

Barbie
Leader of Birmingham Perl Mongers

http://birmingham.pm.org

 

[dhawal]

missbarbell

Thank you for replying. Your reply helps me focus clearly back on my code. I was not thinking that the program was terminating with die @$.

Dhawal

The problem I have now is this. I see in the error console the message:

DBI connect('acd1','intcustapp',...) failed: ORA-01017: invalid username/password; logon denied (DBD: login failed) at tt_testdbi.pl line 12

I want to capture this (to get to ORA- error codes and messages). It does not seem to be in @$. Can you help me on this please?

 

[missbarbell]

Replace:

my $sql = qq{ SELECT sysdate, user FROM dual };
my $sth = $dbh->prepare( $sql );

with:

print $dbh->errstr unless($dbh);

my $sql = qq{ SELECT sysdate, user FROM dual };
my $sth = $dbh->prepare( $sql );
print $sth->errstr unless($sth);
print $sth->errstr unless(!$sth->execute());

And see what the error string reports. I don't have an Oracle connection so cannot test this. But it seems likely that the ORA value will be in the error string from DBI.


Barbie
Leader of Birmingham Perl Mongers

http://birmingham.pm.org

 

[dhawal]

Missbarbell,

You are correct once again. I did find the ORA message embedded in string from DBI. This is what I used to get it:

Thanks for your help. This is the solution I found to work:

if ($@ =~ /(ORA-.*/) # if error message contains ORA-
{
print $1; # $1 shows the embedded ORA code and message
}
elsif ($@) # some other error occurred
{
print $@;
};

I am sure I will find the other script portions you sent me helpful.

Thanks a lot for your help
Dhawal