Perl mysql, why does this fetchrow_array not work?

Question

I have Perl code like so:

$pre = $dbh->prepare("select distinct skills from people");
$pre->execute;

my @preList;
while (my $row = $pre->fetchrow_array()) {
    push (@preList,$row);
}
print "Pre list: $_" foreach @preList;

When I try the exact same statement in sql it works perfectly, but when I run this program it prints out nothing (suggesting an empty @preList). I am probably doing something very stupid, but cannot see it. I have used this format for getting data before, which works perfectly. Can anyone see what I've done wrong?

@sputnick - We don't see the connect, there might be a RaiseError there, but I am doubtfull. — runrig
– runrig, Commented Oct 11, 2012 at 18:35

daniel gratzer · Accepted Answer · 2012-10-11 23:02:57Z

7

Right now you are doing, fetchrow_array which returns a list but you are trying to store it in a scalar, you can either change it to fetchrow_arrayref or change $row to @row, or change my $row to my ($row).

edited Oct 11, 2012 at 23:02

answered Oct 11, 2012 at 14:00

daniel gratzer

54k11 gold badges100 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dgBP Over a year ago

perfect! using @row worked like a charm. I'm puzzled, though, as I've used that exact format before and it worked without @row. maybe there was a column type difference or something :/

ysth Over a year ago

no, perl won't return an array in scalar context (in fact perl never returns arrays, only lists). @bladepanthera: see my answer for the likely culprit

runrig Over a year ago

@ysth - Perhaps it depends on the DBD. Just tested w/DBD::Oracle, and selecting one column works fine w/scalar context. I would not depend on this universally though, as the docs warn against it.

ysth Over a year ago

@runrig: but was the value selected false? it should not depend on the DBD unless the select returns multiple columns.

ysth · Accepted Answer · 2012-10-11 16:42:06Z

When you say:

while (my $row = $pre->fetchrow_array()) {

a value is returned from a row, assigned into $row, then tested for truth. As soon as a skills value that is false is returned, the loop will terminate, without putting that value in @preList. If the first row of results has a skills that is NULL or "" or "0", you will see the results you are seeing.

Changing to:

while (my ($row) = $pre->fetchrow_array()) {

(or using @row, as others have suggested), a value is still returned from each row, and assigned into $row, but instead of the value being tested for truth, the number of elements returned from fetchrow_array is tested for truth (and fetchrow_array returns an empty list when it runs out of result rows, so this number is 1 for each row of results and then 0 to terminate the loop). This happens because while() expects a scalar to test, so gives the assignment scalar context, and in perl, a list assignment in scalar context returns a count of elements on the right side of the assignment, specifically so that code such as this will just work.

key_ · Accepted Answer · 2012-10-22 13:52:24Z

2

$pre = $dbh->prepare("select distinct skills from people");
$pre->execute;

my @preList;
while (my $row = $pre->fetch()) {
    push (@preList,$row);
}
foreach @preList{
    print "Pre list row: " . join(',',@$_);
}

or:

$pre = $dbh->prepare("select distinct skills from people");
$pre->execute;

my $preList = $pre->fetchall_arrayref();

foreach @$preList{
    print "Pre list row: " . join(',',@$_);
}

edited Oct 22, 2012 at 13:52

answered Oct 11, 2012 at 14:25

key_

5774 silver badges15 bronze badges

Comments

gcbenison · Accepted Answer · 2012-10-11 13:59:18Z

0

You should try replacing fetchrow_array in your code with fetchrow_arrayref.

answered Oct 11, 2012 at 13:59

gcbenison

12k4 gold badges46 silver badges82 bronze badges

Collectives™ on Stack Overflow

Perl mysql, why does this fetchrow_array not work?

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related