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.

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` — Shakeer Mirza, Jan 02 '17 at 08:50
Do you want to continue with the script with just capturing the errors? — AbhiNickz, Jan 02 '17 at 09:43
Look at this [Perl DBI Capturing Errors](http://stackoverflow.com/questions/4822991/perl-dbi-capturing-errors) — AbhiNickz, Jan 02 '17 at 09:47
@AbhiNickz I tried it but it didn't work. Reports the same error. — mkHun, Jan 02 '17 at 09:56

Chankey Pathak · Accepted Answer · 2017-01-02T10:56:40.473

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

@mkHun As dgw has mentioned turning off RaiseError and enabling PrintError will prevent the abort. — Chankey Pathak, Jan 02 '17 at 10:47
@ChankeyPathak I got the answer for my question by [this](http://stackoverflow.com/questions/41440383/how-to-get-the-stored-procedure-result-using-perl/41441486?noredirect=1#comment70098780_41441486) question. I used your trick also. Thanks for your help. — mkHun, Jan 04 '17 at 06:01

score 0 · Answer 2 · edited May 23 '17 at 11:46

0

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

edited May 23 '17 at 11:46

Community

1
1

answered Jan 04 '17 at 06:00

mkHun

5,891
8
38
85

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

2 Answers2

Linked