How to save mssql error at runtime in a variable and continue working in perl?

Question

I am trying to save a mssql runtime error in a 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 also does not work.

My procedure is as follows, (sample)

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

When I run the script, it shows

Output signal

 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. 

Do not show output even three . Displays only one.

Answer 1

The PrintError descriptor attribute tells DBI to call Perl warn () (which usually results in errors being printed to the screen upon detection) and the handle RaiseError attribute (which tells DBI to call the Perl die () function after the error, usually resulting in script will terminate immediately). - Perl DBI Programming

Therefore, you can use below to deal with the situation.

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

I am trying to save an error in a variable

The $die_message will contain an error message.

---

Another option is to set RaiseError to 0 and PrintError to 1 so that you get warn , but the program is not die .

PrintError

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

Raiseerror

The RaiseError attribute can be used to force errors to be raised and not just return error codes in the usual way. This is disabled by default. When set to "on", any method that results in an error will cause DBI to efficiently execute die("$class $method failed: $DBI::errstr") , where $class is the driver class and $method is the name of the failed method.

Source - DBI docs

---

You can also do it manually.

 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 to my question from this answer.

Last answer

 do { while(my @row=$sth->fetchrow_array()) { if ($sth->errstr) { my $Error = $sth->errstr; } print $row[0]."\n\n"; } } while ($sth->{odbc_more_results});