dbi error handling

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"
[download]

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.
[download]

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 http://docstore.mik.ua/orelly/linux/dbi/ch04_05.htm 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

Comment on dbi error handling Select or Download Code

Replies are listed 'Best First'.
Re: dbi error handling by erix (Prior) 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.	[reply]
Re^2: dbi error handling by runrig (Abbot) on Dec 21, 2010 at 17:02 UTC
I've started the habit of using: `HandleError => sub { Carp::confess($_[0]) },` [download] 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.	[reply] [d/l]
Re^3: dbi error handling by Anonymous Monk on Dec 22, 2010 at 03:31 UTC
Could you explain that a little bit more (I'm a perl beginner) please? Where does that code go? thanks	[reply]
Re^3: dbi error handling by Anonymous Monk on Dec 22, 2010 at 04:01 UTC
I've seen this in the dbi docs `http://search.cpan.org/~timb/DBI-1.615/DBI.pm#HandleError` [download] What data is in $_[0]	[reply] [d/l]
Re^4: dbi error handling by runrig (Abbot) on Dec 22, 2010 at 18:16 UTC

Back to Seekers of Perl Wisdom