Re^6: Dead Lock replication in Sql server

I haven't copied my code exactly the way it is. its just and mock up example. I trying to use TryCatch package to catch my exception that returns from the database (sql server). But i couldn't able to do it ( my experience in using perl is 4 days). If there are any example which i can refer where the database throws certain error (say Deadlock 1205) i need to handle data error.please let me know if there are any references or links which can i refer. Thanks

Comment on Re^6: Dead Lock replication in Sql server

Replies are listed 'Best First'.
Re^7: Dead Lock replication in Sql server by poj (Abbot) on Jul 03, 2017 at 07:50 UTC
Try #!/usr/bin/perl use warnings; use strict; use DBI; use Exception::Class::TryCatch; my $dbh = dbh(); # get a handle my $sql =<< 'SQL'; BEGIN TRANSACTION UPDATE dead_test SET a = '11' where b=1 WAITFOR DELAY '00:00:05' UPDATE dead_test1 SET a = '22' where b=2 COMMIT SQL my $sth = $dbh->prepare($sql); eval { $sth->execute() }; if ( catch my $err){ if ($err =~ /deadlocked/){ print $err->error; eval { $dbh->rollback }; } } # connect sub dbh { my $dsn = "DBI:ODBC:mssql"; my $dbh = DBI->connect($dsn, 'user', 'password', { RaiseError => 1, PrintError => 0, AutoCommit => 0 }) or die (Error connecting " $DBI::errstr"); } [download] poj	[reply] [d/l]

Replies are listed 'Best First'.

Re^7: Dead Lock replication in Sql server
by poj (Abbot) on Jul 03, 2017 at 07:50 UTC

Try

#!/usr/bin/perl
use warnings;
use strict;
use DBI;
use Exception::Class::TryCatch; 

my $dbh = dbh(); # get a handle 

my $sql =<< 'SQL';
  BEGIN TRANSACTION 
    UPDATE dead_test  SET a = '11' where b=1
    WAITFOR DELAY '00:00:05'
    UPDATE dead_test1 SET a = '22' where b=2
  COMMIT
SQL
my $sth = $dbh->prepare($sql);
 
eval { $sth->execute() }; 
if ( catch my $err){ 
  if ($err =~ /deadlocked/){
    print $err->error;
    eval { $dbh->rollback };
  }  
}

# connect
sub dbh {
  my $dsn = "DBI:ODBC:mssql";
  my $dbh = DBI->connect($dsn, 'user', 'password',
     { RaiseError => 1, 
       PrintError => 0,
       AutoCommit => 0 }) 
  or die (Error connecting " $DBI::errstr");
}
[download]

[reply]
[d/l]