DBI - selectcol_arrayref not working

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

I'm getting an undefined value as an ARRAY reference whgen I call this line of code:

my $welcome = pop(@{$dbh->selectcol_arrayref($query)});

Can't use an undefined value as an ARRAY reference at lib/MT/App/CMS.p
+m line 1175.
[download]

Does anyone have any clues for this? This is the list of Perl modules that I have installed:

Bit::Vector -- 6.4
Carp::Assert -- 0.18
Carp::Clan -- 5.8
Class::Accessor -- 0.30
Class::MethodMaker -- 2.08
Cwd -- 3.24
DBI -- 1.53
DBomb -- $Revision: 1.26 $
Date::Calc -- 5.4
Devel::StackTrace -- 1.13
Devel::Symdump -- 2.0604
Digest::SHA -- 5.44
IPC::Run -- 0.80
MIME::Base64 -- 3.07
Module::Signature -- 0.55
Object::Deadly -- 0.09
Perl -- 5.8.5
Sub::Uplevel -- 0.14
Test::Exception -- 0.24
Test::Simple -- 0.66
Tie::IxHash -- 1.21
URI -- 1.35
mod_perl2 -- 2.000003
[download]

Thanks for the help!! Paul

Comment on DBI - selectcol_arrayref not working Select or Download Code

Replies are listed 'Best First'.
Re: DBI - selectcol_arrayref not working by Fletch (Bishop) on Dec 20, 2006 at 19:53 UTC
Your `$query` is failing and you don't have `RaiseError` set on the DBI handle. Store the result to another variable and check if it's `undef` or not; if it is, check `$dbh->errstr` to see what went wrong. `my $res = $dbh->selectcol_arrayref( $query ); unless( defined $res ) { die "Error executing '$query': ", $dbh->errstr, "\n"; } my $welcome = pop @{ $res };` [download] Also of use may be using the `trace` method on your handle to watch exactly what's being sent back and forth to your underlying database. Update: Heh. So . . . do you perhaps think you should check if you're getting `undef` back?	[reply] [d/l] [select]
Re^2: DBI - selectcol_arrayref not working by PaulBerry (Novice) on Dec 20, 2006 at 20:01 UTC
Can i just say LONG LIVE THE PERL MONKS! ? Long, long, live the perl monks This was such a stupid one on my part, the tablename had switched to all lower case on migration, not sure why, but that code was EXACTLY what i needed to figure that out How do I make sure people get credit for the help their giving? I'm a recent perlmonker Paul	[reply]
Re^2: DBI - selectcol_arrayref not working by PaulBerry (Novice) on Dec 20, 2006 at 19:57 UTC
Thanks! just read this after posting my last question, time to check that out. The thing is that the same code works fine on a different server, with the same DB definitions, but I'll plug in the die errors and see what comes from that.	[reply]
Re: DBI - selectcol_arrayref not working by ikegami (Patriarch) on Dec 20, 2006 at 19:51 UTC
`$dbh->selectcol_arrayref` returns undef when some errors occur. Have you checked `$DBI::err`?	[reply] [d/l] [select]
Re^2: DBI - selectcol_arrayref not working by PaulBerry (Novice) on Dec 20, 2006 at 19:55 UTC
Sorry how do i check $DBI::err in this case? This is the entire snippet of code that I"m calling: `use huff::WelcomeMessage; my $driver = MT::Entry->driver; my $dbh = $driver->{dbh}; my $query = "SELECT Name from NGBlogWelcome"; my $welcome = pop(@{$dbh->selectcol_arrayref($query)}); $param{welcome} = $welcome;` [download]	[reply] [d/l]
Re^3: DBI - selectcol_arrayref not working by Joost (Canon) on Dec 20, 2006 at 20:00 UTC
`if (my $ref = $dbh->selectcol_arrayref($query)) { $param{welcome} = pop @$ref; } elsif ($DBI::err) { die $dbh->errstr; } else { # nothing returned, but no error? }` [download] update: you did notice that selectcol_arrayref returns ALL values in that column, right? I ask because using it to just get the very first row's value will get extremely inefficient fast. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l]
Re^3: DBI - selectcol_arrayref not working by ikegami (Patriarch) on Dec 20, 2006 at 20:15 UTC
An alternative to Joost's solution is to set the `RaiseError` flag to true, causing DBI to throw an exception instead of retuning `undef`. However, avoiding `RaiseError` allows you to provide a more meaningful error message. For example, the following dies with a meaninful error message on error. It also only reads one row since that's all you want. `my $ref = $dbh->selectcol_arrayref($query) or die("Unable to fetch blog name: ", $DBI::err \|\| "Unknown database error", "\n"); $param{welcome} = $ref->[0];` [download] Update: Added example.	[reply] [d/l] [select]
Re: DBI - selectcol_arrayref not working by perrin (Chancellor) on Dec 20, 2006 at 19:52 UTC
`$dbh->selectcol_arrayref($query)` is returning undef.	[reply] [d/l]
Re: DBI - selectcol_arrayref not working by rhesa (Vicar) on Dec 20, 2006 at 20:00 UTC
`selectcol_arrayref` can return undef for a variety of reasons, so you shouldn't blindly dereference its return value. You should probably pay attention to failures (by checking `$DBI::errstr`, for instance). I also suggest you turn the `RaiseError` attribute on when connecting. A workaround might be to wrap your line in an eval. That'd allow you to continue your business, while also logging why it failed: `my $welcome = eval { pop @{ $dbh->selectcol_arrayref($query) }; }; warn $@ if $@;` [download]	[reply] [d/l] [select]