Question

In this chapter we will show, how we can handle errors.

Method name	Description
$h->err()	Returns the native database engine error code from the last driver method called.
$h->errstr()	Returns the native database engine error message from the last DBI method called.
$h->state()	Returns a state code in the standard SQLSTATE five character format.

The above three methods deal with error messages.

DBI dynamic attribute	Description
$DBI::err	Equivalent to $h->err()
$DBI::errstr	Equivalent to $h->errstr()
$DBI::state	Equivalent to $h->state()

The second table gives a list of DBI dynamic attributes, which are related to error handling. These attributes have a short lifespan. They should be used immediately after the method that might cause an error.

Default error handling

By default, the errors are returned by Perl DBI methods.

#!/usr/bin/perl

use strict;
use DBI;

my $dsn = "dbi:mysql:dbname=mydb";
my $user = "user12";
my $password = "34klq*";

my $dbh = DBI->connect($dsn, $user, $password) 
  or die "Can't connect to database: $DBI::errstr";

my $sth = $dbh->prepare( 
  q{ SELECT Id, Name, Price FROM Cars }
  )
  or die "Can't prepare statement: $DBI::errstr";

my $rc = $sth->execute()
  or die "Can't execute statement: $DBI::errstr";

while (my($id, $name, $price) = $sth->fetchrow()) {
  print "$id $name $price\n";
}

# check for problems which may have terminated the fetch early
warn $DBI::errstr if $DBI::err;

$sth->finish();
$dbh->disconnect();

In the first script we deal with the default behaviour of returning error codes.

my $dbh = DBI->connect($dsn, $user, $password) 
  or die "Can't connect to database: $DBI::errstr";

We call the connect() method to create a database connection. If the attempt fails, the method returns undef and sets both $DBI::err and $DBI::errstr attributes. The die() method prints the error message in case of a failure and terminates the script.

my $sth = $dbh->prepare( 
  q{ SELECT Id, Name, Price FROM Cars }
  )
  or die "Can't prepare statement: $DBI::errstr";

We call the prepare() statement. If the method fails, the die() method prints an error message and terminates the script.

my $rc = $sth->execute()
  or die "Can't execute statement: $DBI::errstr";

Again. We call the execute() method and check for errors. The method returns undef if it fails.

warn $DBI::errstr if $DBI::err;

We check for problems which may have terminated the fetch method early.

Raising exceptions

Checking for errors each time we call a DBI method may be tedious. We could easily forget to do so if we had a larger script. The preferred way of dealing with possible errors is to raise exceptions. To raise exceptions, we set the RaiseError attribute to true.

#!/usr/bin/perl

use strict;
use DBI;

my $dsn = "dbi:mysql:dbname=mydb";
my $user = "user12";
my $password = "34klq*";
my %attr = ( RaiseError => 1 );

my $dbh = DBI->connect($dsn, $user, $password, \%attr) 
  or die "Can't connect to database: $DBI::errstr";

my $sth = $dbh->prepare("SELECT * FROM Cars LIMIT 5");   
$sth->execute();

while (my($id, $name, $price) = $sth->fetchrow()) {
  print "$id $name $price\n";
}

$sth->finish();
$dbh->disconnect();

In the connection attributes, we set the RaiseError attribute to 1. When an error occurs, the exceptions are raised rather than error codes returned.

my %attr = ( RaiseError => 1 );

We set the RaiseError attribute to 1.

my $dbh = DBI->connect($dsn, $user, $password, \%attr) 
  or die "Can't connect to database: $DBI::errstr";

The connect() method is the only method that we check for the return code.

my $sth = $dbh->prepare("SELECT * FROM Cars LIMIT 5");   
$sth->execute();

The prepare() and execute() methods do not check for the return error codes. If they fail, an exception is thrown and the Perl DBI will call the die() method and print the error message.

Error subroutines

With the HandleError connection handle attribute, we can set a reference to a subroutine, which is called when an error is detected. The subroutine is called with three parameters: the error message string that RaiseError and "PrintError" would use, the DBI handle being used, and the first value being returned by the method that failed (typically undef ).

If the subroutine returns a false value then the RaiseError or PrintError attributes are checked and acted upon as normal.

#!/usr/bin/perl

use strict;
use DBI;

my $dsn = "dbi:mysql:dbname=mydb";
my $user = "user12";
my $password = "34klq*";
my %attr = ( RaiseError => 1, AutoCommit => 0, HandleError => \&handle_error );

my $dbh = DBI->connect($dsn, $user, $password, \%attr) 
  or die "Can't connect to database: $DBI::errstr";

$dbh->do("UPDATE Cars SET Price=52000 WHERE Id=1");
$dbh->do("UPDATE Car SET Price=22000 WHERE Id=8");

$dbh->commit();

$dbh->disconnect();

sub handle_error {
  
  $dbh->rollback(); 

  my $error = shift;
  print "An error occurred in the script\n";
  print "Message: $error\n";
  return 1;
}

Our own subroutine will handle the error.

my %attr = ( RaiseError => 1, AutoCommit => 0, HandleError => \&handle_error );

The HandleError attribute provides a reference to a handle_error() subroutine that is called, when an error is detected. The AutoCommit is turned off, which means that we work with transactions.

$dbh->do("UPDATE Car SET Price=22000 WHERE Id=8");

There is an error in the SQL statement. There is no Car table.

sub handle_error {
  
  $dbh->rollback(); 

  my $error = shift;
  print "An error occurred in the script\n";
  print "Message: $error\n";
  return 1;
}

This is the handle_error() subroutine. We print the error message. And return 1. If we returned 0 instead, additional error messages would appear. Returning 1 error messages associated with the RaiseError attribute are supressed.

$ ./errsub.pl
An error occurred in the script
Message: DBD::mysql::db do failed: Table 'mydb.Car' doesn't exist

Output of the example.

The kosher example

According to the Perl DBI documentation, the most robust way to deal with DBI errors is to use the eval() method.

#!/usr/bin/perl

use strict;
use DBI;
use DBI qw(:sql_types);

my $dsn = "dbi:mysql:dbname=mydb";
my $user = "user12";
my $password = "34klq*";
my %attr = ( RaiseError => 1, AutoCommit => 0 );

my $dbh = DBI->connect($dsn, $user, $password, \%attr) 
  or die "Can't connect to database: $DBI::errstr";

my @data = (
  [1, "Audi", 52642],
  [2, "Mercedes", 57127],
  [3, "Skoda", 9000], 
  [4, "Volvo", 29000], 
  [5, "Bentley", 350000], 
  [6, "Citroen", 21000],
  [7, "Hummer", 41400],
  [8, "Volkswagen", 21601] 
);

eval {
  $dbh->do("DROP TABLE IF EXISTS Cars");
  $dbh->do("CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INT)");
};

my $sql = qq{ INSERT INTO Cars VALUES ( ?, ?, ? ) };
my $sth = $dbh->prepare($sql);


foreach my $row (@data) {

  eval {
    $sth->bind_param(1, @$row[0], SQL_INTEGER);
    $sth->bind_param(2, @$row[1], SQL_VARCHAR);
    $sth->bind_param(3, @$row[2], SQL_INTEGER);
    $sth->execute();
    $dbh->commit();
  };

  if( $@ ) {
    warn "Database error: $DBI::errstr\n";
    $dbh->rollback(); 
  }
}

$sth->finish();
$dbh->disconnect();

The above code example is the most correct way of dealing with errors.

my %attr = ( RaiseError => 1, AutoCommit => 0 );

We raise exceptions rather than check for return codes. We turn the autocommit mode off and manually commit or rollback the changes.

my $sql = qq{ INSERT INTO Cars VALUES ( ?, ?, ? ) };
my $sth = $dbh->prepare($sql);

We guard against errors and security issues by using the placeholders.

eval {
  $sth->bind_param(1, @$row[0], SQL_INTEGER);
  $sth->bind_param(2, @$row[1], SQL_VARCHAR);
  $sth->bind_param(3, @$row[2], SQL_INTEGER);
  $sth->execute();
  $dbh->commit();
};

Inside the eval() method we put the error prone code. The method traps exceptions and fills the $@ special variable with error messages. We bind the variables to the placeholders with the bind_param() method.

if( $@ ) {
  warn "Database error: $DBI::errstr\n";
  $dbh->rollback(); 
}

In case of an error, we print the error message and rollback the changes.

In this part of the MySQL Perl tutorial, we were discussing error handling.