How to store the runtime mssql error in a variable and continue to run in perl?

Question

I'm trying to store the runtime mssql error in variable and continue with all other data.

my $sth = $dbh->prepare("exec TEST_ABC_DB.dbo.testprocedure");
$sth->execute() ;
my $db_error =$DBI::errstr; #It didn't work also I tried err and state
print "\nDB error $db_error\n";
while (@row = $sth->fetchrow_array( ) ) 
{
      print "Row: @row\n";
}

I used the eval block but it is also not working.

My procedure as follows,(sample)

CREATE procedure  testprocedure as
select 'one'
select 'three'
select 10/0
select 'five'

When I run the script it shows

The output is

Row: one
DBD::ODBC::st finish failed: [unixODBC][FreeTDS][SQL Server]Divide by zero error encountered. (SQL-22012) at testing.pl line 24.
DBI::db=HASH(0xbe79a0)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at testing.pl line 28.

Not displaying output even three. Displays the only one.

Answer 1

The PrintError handle attribute tells DBI to call the Perl warn( ) function (which typically results in errors being printed to the screen when encountered) and the RaiseError handle attribute (which tells DBI to call the Perl die( ) function upon error, typically causing the script to immediately abort). - Programming the Perl DBI

Therefore you could use below to handle the situation.

local $SIG{__DIE__} = sub {
    my ($die_message) = @_;
    #do something..
};

I'm trying to store the error in variable

In above snippet $die_message will contain the error message.

---

Another option would be to set RaiseError to 0 and PrintError to 1, so that you get the warnings but program doesn't die.

PrintError

The PrintError attribute can be used to force errors to generate warnings (using warn) in addition to returning error codes in the normal way. When set "on", any method which results in an error occurring will cause the DBI to effectively do a warn("$class $method failed: $DBI::errstr") where $class is the driver class and $method is the name of the method which failed.

RaiseError

The RaiseError attribute can be used to force errors to raise exceptions rather than simply return error codes in the normal way. It is "off" by default. When set "on", any method which results in an error will cause the DBI to effectively do a die("$class $method failed: $DBI::errstr"), where $class is the driver class and $method is the name of the method that failed.

Source - DBI docs

---

You could also do it manually by

my $dbh=DBI->connect(....{RaiseError=>1}) or die...
my $sth=$dbh->prepare(...);
{
   local $dbh->{RaiseError} = 0;
   $sth->execute;
   if ($sth->Errstr) {
       # handle the error
   }
}
# $dbh->{RaiseError} is back to normal here

Answer 2

I got the answer for my question from this answer.

The final answer is

do
{ 
while(my @row=$sth->fetchrow_array())
{
    if ($sth->errstr) 
    {
        my $Error = $sth->errstr;   

    }
    print $row[0]."\n\n";
}
} while ($sth->{odbc_more_results});