Not getting err code,type and state with Perl DBI (DBD::ODBC) on Sqlserver

Question

I have a perl script which does the below to just run a insert stmnt and doing a deliberate RAISERROR. Now i can see that the error is generated but has the wrong error code - SQL-42000 , but the error diagnostic methods don't return anything. Using $DBI::err,$DBI::errstr,$DBI::state also doesnt print the correct thing. Only $DBI::errstr is coming up correctly.

$dbh->{RaiseError} = 1;
$dbh->{PrintError} = 1;

$dbh->begin_work;
eval {
      $dbh->do(INSERT INTO ..);
      $dbh->do (RAISERROR ('User defined error',16,1) --purposely raising error
      $dbh->commit;
      1;
};
     if ($@) {
     print $@;
     print "err() ==>".$dbh->err();
     print "errstr() ==>".$dbh->errstr();
     print "state() ==>".$dbh->state();
     $dbh->rollback or warn "rollback failed";
}

Output:

DBD::ODBC::db do failed: [unixODBC][FreeTDS][SQL Server]User defined error (SQL-42000) ..
err() =>
errstr() =>
state() =>

Answer 1

Your code sample is broken (the --purposely and missing ;, missing quotes in do call) and the error 42000 is because you have an error calling raiserror which is "Incorrect syntax near ...". I can't tell because you do show real working code. Anyway your code rewritten as the following works for me:

use DBI;
use strict;
use warnings;

my $dbh = DBI->connect('dbi:ODBC:xxx','xx','xx');

$dbh->{RaiseError} = 1;
$dbh->{PrintError} = 1;

$dbh->begin_work;
eval {
      #$dbh->do(INSERT INTO ..);
      $dbh->do (q/RAISERROR ('User defined error',16,1)/); #--purposely raising error
      $dbh->commit;
      1;
};
     if ($@) {
     print $@;
     print "err() ==>".$dbh->err();
     print "errstr() ==>".$dbh->errstr();
     print "state() ==>".$dbh->state();
     $dbh->rollback or warn "rollback failed";
}

and outputs:

DBD::ODBC::db do failed: [unixODBC][Easysoft][SQL Server Driver][SQL Server]User defined error (SQL-42000) at so1.pl line 16.
DBD::ODBC::db do failed: [unixODBC][Easysoft][SQL Server Driver][SQL Server]User defined error (SQL-42000) at so1.pl line 16.
err() ==>1errstr() ==>[unixODBC][Easysoft][SQL Server Driver][SQL Server]User defined error (SQL-42000)state() ==>42000