http://qs321.pair.com?node_id=99493

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

I've bumped into a rather odd problem when trying to use some of the aggregate DBI functions, like selectcol_arrayref. Since many of them return array references, I tend to dereference the array when I call the method. For example, 
@ary = @{$dbh->selectcol_arrayref($sql)};

[download]
My problem occurs when the statement in $sql returns zero rows. Consider the following script: 
#!/usr/bin/perl -w
use strict;
use DBI;
$\ = "\n";
# Input dbname, user & pass on the command line
my ($dbname, $dbuser, $dbpass) = @ARGV;

my $dbh = DBI->connect("DBI:Oracle:$dbname", $dbuser, $dbpass, {RaiseE
+rror => 1}) or die $DBI::errstr;

# This query returns no rows
my $sql = qq~ SELECT dummy FROM dual WHERE dummy = '1' ~;

# This works just fine...
my $ref = $dbh->selectcol_arrayref($sql) or die $dbh->errstr, $!;
print "\$ref is defined: $ref" if defined $ref;
print for @$ref;

# But this one dies!
my @ary = @{$dbh->selectcol_arrayref($sql)} or die $dbh->errstr, $!;
print for @ary;

[download]
In the second attempt to make the query, $dbh->errstr is undef, meaning that the DB query worked fine. But, </code>$!</code> contains this: 
No such file or directory at ./dbtest.pl line 17.

[download]
If I remove the die, everything works the way I want it to, but I would like to know why it is dying to begin with.

Replies are listed 'Best First'.
Re: Dereferencing and DBI methods
by runrig (Abbot) on Jul 25, 2001 at 02:22 UTC
    You have 'RaiseError=>1' in your connect, so you do not need all those 'or die...' clauses in your DBI calls. Your select is returning a reference to an array with zero rows. In scalar/boolean context that is zero, i.e., false. So the statement dies.
[reply]
Re: Dereferencing and DBI methods
by aquacade (Scribe) on Jul 25, 2001 at 02:03 UTC

    Try the following instead:

    # But this one used to die!
my @ary = $dbh->selectrow_array($sql) or die $dbh->errstr, $!;
print for @ary;

    [download]
[reply]
[d/l]
      You just made it look like the first example.

      My point is that some of the aggregate functions only return refs, and don't have a counterpart that returns an actual array. selectcol_arrayref, fetchall_arrayref, fetchrow_hashref to name a few. So, you have to either dereference them when you make the call, or use an intermediate variable, or dereference them as you use them (and the first is usually most convenient for me; it makes the things I do later a lot more readable).

[reply]
[d/l]
[select]
        Not quite, mine says 'row' not 'col' as your first example does. Are you trying to avoid using bind_parm/bind_col? I find they are most useful and very easy to maintain. A good editor with a column mode that can insert sequence numbers helps (like ultraedit) with binding the references.
[reply]

Back to Seekers of Perl Wisdom