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

I've got the following line in a script that writes information back to a database, but when it fails the 'dienice' subroutine is not called. Any ideas why? 
$sth = $dbh->prepare("select task_id, originator_name, creation_date, 
+due_date, severity from tasks order by task_id") or &dienice ("Can't 
+get problem report: $!");

$sth->execute;

[download]
..... 
sub dienice {
    my($msg) = @_;
    print "<h2>Error</h2>\n";
    print "$msg\n";
    exit;
}

[download]

Replies are listed 'Best First'.
(jeffa) Re: Script does not die when DB connection fails
by jeffa (Bishop) on May 25, 2001 at 17:54 UTC
    Try this instead: 
    $sth = $dbh->prepare("
    select task_id, originator_name, 
    creation_date, due_date, severity 
    from tasks 
    order by task_id
");
$sth->execute or dienice("Can't get problem report: " . $dbh->errstr);

    [download]
    arhuman is right about testing the validity of execute, not prepare. I also wanted to show you that your SQL code can be nicely formatted so you can easily tell the difference between it and your Perl Code. Also, use $dbh->errstr instead of $! to get errors back from the database. You can also use $DBI::errstr if you do not have a valid DBI::db object (for example, when you first attempt to create the object).

    UPDATE: Listen to davorg - he speaks with great wisdom, even if he tells you Pony is really the universal answer for today.

    Jeff 

    R-R-R--R-R-R--R-R-R--R-R-R--R-R-R--
L-L--L-L--L-L--L-L--L-L--L-L--L-L--
[reply]
[d/l]
[select]

      Why not check the return code from both prepare and execute. On many database systems prepare will attempt to compile the SQL and will return details of syntax errors.

      And rather than checking the return code from every DBI call, why not set the RaiseError attribute when connecting to the database.

      --
      <http://www.dave.org.uk>

      "Perl makes the fun jobs fun
      and the boring jobs bearable" - me

[reply]
        ... why not set the RaiseError attribute when connecting to the database.

        Here, one reason not to do that is that you've got a custom dienice routine you want to call when database calls fail. Now, you *could* redirect errors by setting $SIG{__DIE__}, to point to your dienice routine, but as a couple of wise monks pointed out to me, that could wreak havoc if any modules you've imported contain dies in eval blocks.

        There are two solutions that I can think of that recommend themselves: either keep RaiseError set to false and do all your error checking manually, calling dienice, OR wrap all your DB calls in eval blocks and call dienice with the contents of $@ if it is set (i.e. the standard Perl exception-handling mechanism).

        HTH. 

        perl -e 'print "How sweet does a rose smell? "; chomp ($n = <STDIN>); 
+$rose = "smells sweet to degree $n"; *other_name = *rose; print "$oth
+er_name\n"'

        [download]
[reply]
[d/l]
[select]
      Probably a silly question but I get the following now 'Can't get task ID: DBI::db=HASH(0x355bf4)->errstr' when I use $dbh->errstr, I assumed I'd get an error message?
[reply]

        $dbh->errstr is a function call. You can't put it in a quoted string.

        Update: arturo asks me to point out that you can interpolate function calls in quoted strings by doing something nasty and kludgy like:

        "@{ [ $dbh->errstr ] }" --
        <http://www.dave.org.uk>

        "Perl makes the fun jobs fun
        and the boring jobs bearable" - me

[reply]
[d/l]
      Thanks for that, DOH! Your right.
[reply]
Re: Script does not die when DB connection fails
by arhuman (Vicar) on May 25, 2001 at 17:47 UTC
    Excuse the silly question, but are you SURE it's the prepare rather than the execute which fails ?
    (in clear : why no error checking on the execute)

    "Only Bad Coders Code Badly In P</b
[reply]
Re: Script does not die when DB connection fails
by stephen (Priest) on May 25, 2001 at 20:28 UTC
    If you set $dbh->{'RaiseError'} to true, then your code will die when either the prepare() or execute() fails. 
    eval {
    local $dbh->{'RaiseError'} = 1;
    my $sth = $dbh->prepare("select task_id, originator_name, creation
+_date, due_date, severity from tasks order by task_id");
    $sth->execute();
    # .. other stuff
};
length($@) and dienice("Can't get problem report: $@");

    [download]

    stephen

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom