Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

dbi error handling

by Anonymous Monk
on Dec 21, 2010 at 00:15 UTC ( #878126=perlquestion: print w/replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks

I have seen code like this to handle errors when you retrieve a row count in sql

(my $buddy_count = $dbh->selectrow_array ("SELECT COUNT(*) FROM profil +e") ) or die "query failed"
I assumed that this query only returned undef if there was an error but looking at the docs i am wrong.
Also, in a scalar context, an undef is returned if there are no more r +ows or if an error occurred. That undef can't be distinguished from a +n undef returned because the first field value was NULL. For these re +asons you should exercise some caution if you use selectrow_array in +a scalar context, or just don't do that.
so you can't tell with the code if there was an error or no more rows - but a select count should always return one row unless there is an error? So, what is the proper way of doing this?

I have also read you should always use individual prepare and execute statements when executing select queries. What do you think about that?

Also when handling database errors is it 'better' to set RaiseError = 1 or handle the errors yourself manual with or diechecks. I've seen this link and i think the upshot is to use RaiseError=1 as the code is cleaner and easier to read. What do you think?

thanks a lot

Replies are listed 'Best First'.
Re: dbi error handling
by erix (Parson) on Dec 21, 2010 at 00:24 UTC

    select count(*) from table will always have at least one row: an empty table will give 0. (Of course, the table must exist, and the $dbh must be valid).

    selectrow_array is just a convenience (it does a combined prepare + execute) for just this kind of SQL where you can be sure there will not be too many rows.

    I'd say RaiseError => 1 is indeed the best and easiest way.

      I've started the habit of using:
      HandleError => sub { Carp::confess($_[0]) },
      in my connect() code. I started doing this because some versions of DBD::Sybase don't report the line number of the error with just RaiseError set (because the error messages had a newline on the end of them). Also, it's just nice to have a stack trace when a DBI call fails from deep within your function calls.
        Could you explain that a little bit more (I'm a perl beginner) please? Where does that code go? thanks
        I've seen this in the dbi docs
        What data is in $_[0]

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://878126]
Approved by planetscape
[choroba]: I don't know. Maybe a new capability will be given to Hermits?

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (6)
As of 2018-06-22 07:59 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (122 votes). Check out past polls.