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

Hi i am using perl and dbi to connect to a database and i have come accross the following error handling example 
$sth = $dbh->prepare(q{SELECT user_name, password from user where user
+_name = ?})
     or die("Database error: $!");

[download]
What i dont understand is what the $! part of the code does. What would happen in this case if the select statement does not succeed?

Replies are listed 'Best First'.
Re: error catching
by jettero (Monsignor) on Dec 20, 2006 at 00:12 UTC
    I'm not sure $! is populated there. $! is frequently used to print errors from syscalls and the like (see perlvar). What you should be using is "or die $dbh->errstr" in my opinion. However, mostly people leave the DBI on it's default settings, which carp() errors before you ever get to the "or" part of your statement. Therefore, die()ing may be enough anyway.

    -Paul

[reply]
[d/l]
[select]
      However, mostly people leave the DBI on it's default settings, which carp() errors before you ever get to the "or" part of your statement.
      While I don't know what people normally do, the DBI default is for some unexplicable reason to not do that: (from the docs):
      "RaiseError" (boolean, inherited)

      The "RaiseError" attribute can be used to force errors to raise exceptions rather than simply return error codes in the normal way. It is "off" by default. -snip-

      Update: just to be clear: the parent node's comments about $! and $dbi->errstr are correct, it's just that the default DBI setting is to use those, and not throw exceptions. If you want to automatically get an exception on a database/SQL error (and I suggest you should), you need to use the RaiseError option on the DBI->connect call.

      update2: jettero is correct in his correction, but we both seem to agree that you should use RaiseError to catch any further errors. The chances of anything unexpected going wrong later without notice are a lot higher if you don't.

      "What should it profit a man, if he should win a flame war, yet lose his cool?"
[reply]

        While I don't know what people normally do, the DBI default is for some unexplicable reason to not do that: (from the docs):

        False. I was reading just above where you were reading.

        By default, "DBI->connect" sets "PrintError" "on"

        I agree that you still have to die() if your $sth fails, which I thought I had implied, but it will (by default) at least print the error on its own. The RaiseError is different and actually croak()s instead of just carp()ing.

        Update, because it happens to be on my other monitor anyway, here's the relevant source [the comments are mine]: 

                Carp::croak($msg) if $attr->{RaiseError}; # not default
        Carp::carp ($msg) if $attr->{PrintError}; # default

        [download]

        -Paul

[reply]
[d/l]
[select]

        RaiseError doesn't work for the initial ->connect statement. You need to check that one manually.

        ⠤⠤ ⠙⠊⠕⠞⠁⠇⠑⠧⠊

[reply]
Re: error catching
by siva kumar (Pilgrim) on Dec 20, 2006 at 04:25 UTC
    Don't use $! for catching SQL exceptions. Use DBI::errstr or $dbh->errstr;
    Connect DB with RaiseError=>1 .
    ie.,
    my $dbh = DBI->connect($dbn,$dbu,$dbp, {RaiseError=>1}) or die "can't connect $dbn,$dbu,_ connect error $DBI::errstr" ;
    Catch the exception using,
    $sth=$dbh->prepare($query) or die("Error while preparing $query".$DBI::errstr);
    $sth->execute() or die("Error while executing $query". $DBI::errstr);
[reply]
Re: error catching
by DreyT (Initiate) on Dec 21, 2006 at 15:47 UTC
    The cleanest way is to NOT do any explicit error handling. It may be better to handle your exceptions in one place, using "eval" and Exception::Class::DBI. So when you connect, you can pass this: 
    ...
RaiseError  => 1,
HandleError => Exception::Class::DBI->handler,
...

    [download]
    Now, if any error occurs, an exception will be thrown, and you can catch that via: 
    eval {
  # if I fail, I will let you know
  $dbh->do("evil query"); 
};

my $ex = undef;

if ( $ex = Exception::Class::DBI->caught() ) {
   # log error in $ex->errstr
   # show users a general error screen
}

    [download]
    This should also take care of any "connect" trouble.
    In general, and totally IMHO, checking for return of every db statement is evil ;)
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom