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.

Boldfaced

If you want to do this in procedure itself, you can do exception handling using Try..Catch blocks. See documentation here https://msdn.microsoft.com/en-us/library/ms175976.aspx – Incurrent

Do you want to continue with the script with just capturing the errors? – Fame

@Fame Yes I want to continue the script. – Boldfaced

Look at this Perl DBI Capturing Errors – Fame

@Fame I tried it but it didn't work. Reports the same error. – Boldfaced

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

Kajdan

Why not simply switch from RaiseError to PrintError? – Centuple

@Chankeypathak How to prevent the dbi abort? – Boldfaced

@Boldfaced As dgw has mentioned turning off RaiseError and enabling PrintError will prevent the abort. – Kajdan

@ChankeyPathak I got the answer for my question by this question. I used your trick also. Thanks for your help. – Boldfaced

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

Boldfaced