Database Error Handling

sowais has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks! I am somewhat new to PERL and wanted to get some help in error handling for DB access. I have written the below code for a simple record INSERT, which works perfectly fine, but I know there is room for improvement and would appreciate it if you guys can offer any tips for error handling. Thanks in advance!

use strict;
use warnings;
use DBI;

my $server_name = 'Smoke\Reporting';
my $database_name = 'DBtest';

#do not need user/pass to connect
my $DSN = "driver={SQL Server};server=$server_name;database=$database_
+name;"; 

my $dbh = DBI->connect("dbi:ODBC:$DSN");

if (!$dbh) {            #testing connection to DB
    print "Could not connect to database: $DBI::errstr";
}

if($dbh) {
    print "Connected to DB!!";
}

my $sql = "INSERT INTO RecordCount VALUES ('filename','date','count')"
+;
  
my $sth = $dbh->prepare($sql);
$sth->{ChopBlanks} = 1;
$sth->execute();
  
$dbh->disconnect();

exit 0;
[download]

Comment on Database Error Handling Download Code

Replies are listed 'Best First'.
Re: Database Error Handling by runrig (Abbot) on Aug 12, 2013 at 16:40 UTC
You are checking for errors on the connect(), but not on the prepare, nor the execute. Use RaiseError on the connect, and then you don't have to check on any of them, it is taken care of for you.	[reply]
Re: Database Error Handling by mje (Curate) on Aug 12, 2013 at 16:45 UTC
You'll save yourself a lot of code writing if you simply enable RaiseError (see the DBI pod) on your DBI handle. If your connection fails in the above code it prints an error and falls through into code which uses your $dbh. You test !$dbh then $dbh separately but one is the opposite of the other so two if tests are not required. ChopBlanks has nothing to do with inserts so that line is redundant. You can test the return of the execute on line 25 to see if the insert inserted any rows as it is not an error to insert no rows.	[reply]