How should I handle $sth->execute() errors?

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

Hi Again,

I am using SQLites (Thanks to perrin's advice in this node.) And I want to be able suppress error messages generated by execute() failure in certain cases. Below is a script and its output that I hope demonstrates what I am talking about.

bash-2.03$ cat execMonks.pl 
#!/usr/local/bin/perl -w
use strict;
use DBI;
 
my $dbh = DBI->connect("dbi:SQLite:dbname=request_db","","");
 
my $createTableSQL = <<SQL;
CREATE TABLE REQUEST (
        REQ             NUMBER,
        STATUS          NUMBER
)
SQL
 
my $sth = $dbh->prepare( $createTableSQL ) or die $dbh->errstr . "\n";
$sth->execute() or warn $dbh->errstr . "\n";
$sth->finish;
 
bash-2.03$ ./execMonks.pl 
 
bash-2.03$ ./execMonks.pl 
DBD::SQLite::st execute failed: table REQUEST already exists at ./exec
+Monks.pl line 15.
table REQUEST already exists
[download]

When I execute execMonks.pl the second time I want to suppress the error message if the table REQUEST already exist. I don't want to supress failures for other types of errors. Any help would be appreciated :)

Thanks

Plankton: 1% Evil, 99% Hot Gas.

Comment on How should I handle $sth->execute() errors? Download Code

Replies are listed 'Best First'.
Re: How should I handle $sth->execute() errors? by mifflin (Curate) on Dec 03, 2003 at 18:29 UTC
You should consider using RaiseError when you connect like ... `my $dbh = DBI->connect("dbi:SQLite:dbname=request_db","","", {RaiseErr +or => 1});` [download] Now you will not have to check for errors after every dbi method you call. Your code would then be something like ... `my $dbh; eval { $dbh = DBI->connect("dbi:SQLite:dbname=request_db","","", {RaiseErr +or => 1}); $dbh->do($createTableSQL); }; if ($@) { # handle error here # the error will be in the $@ variable # the error raised could be from the connect or the do methods }` [download] Note that I change your prepare/execute/finish to just a do. This is more concise way of executing DDL statements.	[reply] [d/l] [select]
Re: How should I handle $sth->execute() errors? by Abigail-II (Bishop) on Dec 03, 2003 at 18:10 UTC
You could use something like: `$sth -> execute or $dbh -> errstr =~ /table \w+ already exists/ or die $dbh -> errstr; # Or warn.` [download] Abigail	[reply] [d/l]
Re: How should I handle $sth->execute() errors? by mpeppler (Vicar) on Dec 03, 2003 at 18:18 UTC
I don't know SQLite, but according to http://www.hwaci.com/sw/sqlite/lang.html the text of the CREATE TABLE statements are stored in the sqlite_master table, so you can probably query that table to see if your REQUEST table already exists before running the CREATE TABLE statement. Michael	[reply]
Re: How should I handle $sth->execute() errors? by Art_XIV (Hermit) on Dec 03, 2003 at 19:39 UTC
Your concern for error-handling is meritorious, but it's very common to DROP tables in db build scripts before CREATE'ing them. I don't know if that would work for SQLite, though. Hanlon's Razor - "Never attribute to malice that which can be adequately explained by stupidity"	[reply]
2Re: How should I handle $sth->execute() errors? by jeffa (Bishop) on Dec 04, 2003 at 00:05 UTC
You can do this in SQLite ... but regardless of which DBD you use, this technique will fail if the table is already gone when you issue the DROP command. It works like a charm if you add the drop statement after your code yacks the first time you try to ADD a table that already exists -- but as soon as something else DROP's the table, your script breaks "in the opposite direction," so to speak. I think mifflin's suggestion of "catching an exception" by trapping inside eval is a more robust solution. unless you are using something like MySQL that offers CREATE TABLE foo IF NOT EXISTS ... jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply]
Re: 2Re: How should I handle $sth->execute() errors? by Art_XIV (Hermit) on Dec 04, 2003 at 16:36 UTC
You'll get no arguments from me concerning error-handling. I just had a sense that the poster was not familiar with the DROP/CREATE idiom in datababase build scripts. :) Hanlon's Razor - "Never attribute to malice that which can be adequately explained by stupidity"	[reply]