problem with $dbh->execute in a For loop

[Ravi]

Hi All,
I am trying to execute a select statement using the DBI module
of perl in a for loop. I am getting a strange behaviour, the select
statement is excuting correctly only for the last element in the for
loop.
I am including the portion of the code :

#Get the connection to the database
my $dbh = &getConnection();
my @acodes;
my @bcodes = ('ADADADA', 'ADASDASDAS', 'BBBBBB', 'CCCCCCC');

#Create an SQL statement for getting the list of acodes
my $sql = $dbh->prepare("SELECT DISTINCT Vial.ACODE from VIAL
WHERE VIAL.BCODE = ?");

my @row;
my $bcode;
$i = 0;

for($i=0; $i<@bcodes; $i++){

print "i :".$i."\n";
$sql->bind_param(1, $bcodes[$i]);
$sql->execute();
$acodes[$i] = "";
while(@row = $sql->fetchrow_array){
$acodes[$i] = $row[0];
}

}
#Close the connection to the databse
&closeConnection($dbh);

When I try to print the @acodes, it only has an entry for the
corresponding last element of the @bcode.
When I remove the for loop in the above code, and hardcode
the elements present in @bcodes it works fine.

It would be a great help, if anyone can let me know what I am doing
wrong.

Thanks in advance.
Ravi.

[David Frauzel]

ra******@yahoo.com (Ravi) wrote in
news:11*************************@posting.google.co m:

for($i=0; $i<@bcodes; $i++){ .... while(@row = $sql->fetchrow_array){
$acodes[$i] = $row[0];
}
}

Just a stab in the dark...

If my understanding of fetchrow_array hasn't lapsed, I believe the while
loop you have is going to fetch every single row until it reaches the last
row - so that every single $acodes[$i] is going to be populated with only
the last row fetched. (Unless you can assume you will always have only one
row to fetch - but that makes the while loop redundant and dangerous for
future expansion of the db.)

Does removing the while loop produce the desired effect?

[Ravi]

David Frauzel <net.weathersongATnemo> wrote in message news:<15******************************@news.terane ws.com>...

ra******@yahoo.com (Ravi) wrote in
news:11*************************@posting.google.co m:

for($i=0; $i<@bcodes; $i++){

...

while(@row = $sql->fetchrow_array){
$acodes[$i] = $row[0];
}
}

Just a stab in the dark...

If my understanding of fetchrow_array hasn't lapsed, I believe the while
loop you have is going to fetch every single row until it reaches the last
row - so that every single $acodes[$i] is going to be populated with only
the last row fetched. (Unless you can assume you will always have only one
row to fetch - but that makes the while loop redundant and dangerous for
future expansion of the db.)

Does removing the while loop produce the desired effect?

Yes the sql statement will always give me a single row, and no it
still does not work if I remove while loop.
I added a print statement inside the while loop to check see if it
is getting the result:
for($i=0; $i<@bcodes; $i++){

print "i :".$i."\n";
$sql->bind_param(1, $bcodes[$i]);
$sql->execute();
$acodes[$i] = "";
while(@row = $sql->fetchrow_array){
$acodes[$i] = $row[0];
print "result :".$row[0];
}
}

strange behaviour, only for the last element in the bcodes array it
goes into the while loop, for all others it does not. I am sure that
for all the elements in the bcode array there is an entry in the
database. If I move the first element to the last element of the bcode
array, now it enters the while loop for the last element (which was
the first element in the bcode array, before moving it to the last
position in the bcode array).

Is it possible that perl is not waiting for the fetchrow_array to
fetch data completely from the database ? Is there a workaround for it
?

Thanks in advance
Ravi.

[David Frauzel]

ra******@yahoo.com (Ravi) wrote in
news:11**************************@posting.google.c om:

Yes the sql statement will always give me a single row, and no it
still does not work if I remove while loop.
Like I said, then, there's no need for the while loop. It's redundant at
best, and a potential time bomb at worst. You may want to look up
selectrow_array instead of fetchrow_array, since you only need it for one
row, and selectrow_array reduces three statements into one. It allows for
param binding.
strange behaviour, only for the last element in the bcodes array it
goes into the while loop, for all others it does not. I am sure that
for all the elements in the bcode array there is an entry in the
database. If I move the first element to the last element of the bcode
array, now it enters the while loop for the last element (which was
the first element in the bcode array, before moving it to the last
position in the bcode array).

How about testing with a row-count to verify there really is an entry in
the db?

(Warning: back-of-the-napkin code...)

for(@bcodes){

$count = "SELECT COUNT(*) FORM VIAL WHERE VIAL.BCODE = $_";
print "$count -> " .
$dbh->selectrow_array($count) .
"\n";

push @acodes, $dbh->selectrow_array($sql, {}, $_);

}

Without the test code, you could reduce this to a one-liner:

#Get the connection to the database
my $dbh = &getConnection();
my @acodes;
my @bcodes = ('ADADADA', 'ADASDASDAS', 'BBBBBB', 'CCCCCCC');

#Create an SQL statement for getting the list of acodes
my $sql = $dbh->prepare("SELECT DISTINCT Vial.ACODE from VIAL
WHERE VIAL.BCODE = ?");

push @acodes, $dbh->selectrow_array($sql, {}, $_) for @bcodes;

#Close the connection to the databse
&closeConnection($dbh);

[Kris Wempa]

> Like I said, then, there's no need for the while loop. It's redundant at

best, and a potential time bomb at worst. You may want to look up
selectrow_array instead of fetchrow_array, since you only need it for one
row, and selectrow_array reduces three statements into one. It allows for
param binding.

This is incorrect. Here is the description of fetchrow_array from CPAN:

----------------
fetchrow_array:
An alternative to fetchrow_arrayref. Fetches the next row of data and
returns it as a list containing the field values. Null fields are returned
as undef values in the list.

If there are no more rows or if an error occurs, then fetchrow_array returns
an empty list. You should check $sth->err afterwards (or use the RaiseError
attribute) to discover if the empty list returned was due to an error.

---------------

I've written a lot of Perl code to interface to mysql and the loop he is
using looks good. Each time fetchrow_array is called, it gets the NEXT row
in the result set and assigns each field to the corresponding array
elements: row[0], row[1], ....

[David Frauzel]

"Kris Wempa" <calmincents(NO_SPAM)@yahoo.com> wrote in
news:bk*********@kcweb01.netnews.att.com:

Like I said, then, there's no need for the while loop. It's redundant
at best, and a potential time bomb at worst. You may want to look up
selectrow_array instead of fetchrow_array, since you only need it for
one row, and selectrow_array reduces three statements into one. It
allows for param binding.

This is incorrect. Here is the description of fetchrow_array from
CPAN:

I'm not sure which part of my statement was incorrect? Please let me
know, as I'm doing a lot of work with mysql myself, and I'd rather know
if some of my knowledge is faulty. I've read the CPAN.org doc you quoted
several times, if that makes a difference. :}
I've written a lot of Perl code to interface to mysql and the loop he
is using looks good. Each time fetchrow_array is called, it gets the
NEXT row in the result set and assigns each field to the corresponding
array elements: row[0], row[1], ....

I mentioned that the while loop was redundant because Ravi specifically
stated he only "expects" one row to be returned. When this is the case,
you don't need a while loop, and using a "bare" fetchrow_array (or
selectrow_array) works as needed: it fetches the "next" row, which is the
first row, and that's all you want, so that's all you need. This is how
my own mysql code has operated in dozens of places, so I have no reason
to believe otherwise.

I was recommending selectrow_array as an alternative to fetchrow_array,
because it combines the three statements into one, making three lines of
code into one. The only reason I've ever found to not use selectrow_array
is when you're working on a server with an older version of DBI, and your
only option is to use fetchrow_array. (After prepare and execute.)

I don't see anything wrong, *syntactically* with his code either (though
I presented a more idiomatic version, it is functionally equivalent), so
my guess is that it's a problem with the db, or the statement. Which is
why I suggested the COUNT(*) test. :}

[Kris Wempa]

I no longer have the earlier threads so I'm not sure what I thought was
wrong in your comment. I also see your point about combining 3 statements
into one. I must have misunderstood what you were saying. I don't see how
the while loop is a potential "time bomb", however. As long as it evaluates
to a false condition when there are no more rows, there shouldn't be a
problem. Sorry about all the confusion.

"David Frauzel" <net.weathersongATnemo> wrote in message
news:e0******************************@news.teranew s.com...

"Kris Wempa" <calmincents(NO_SPAM)@yahoo.com> wrote in
news:bk*********@kcweb01.netnews.att.com:

Like I said, then, there's no need for the while loop. It's redundant
at best, and a potential time bomb at worst. You may want to look up
selectrow_array instead of fetchrow_array, since you only need it for
one row, and selectrow_array reduces three statements into one. It
allows for param binding.

This is incorrect. Here is the description of fetchrow_array from
CPAN:

I'm not sure which part of my statement was incorrect? Please let me
know, as I'm doing a lot of work with mysql myself, and I'd rather know
if some of my knowledge is faulty. I've read the CPAN.org doc you quoted
several times, if that makes a difference. :}

I've written a lot of Perl code to interface to mysql and the loop he
is using looks good. Each time fetchrow_array is called, it gets the
NEXT row in the result set and assigns each field to the corresponding
array elements: row[0], row[1], ....

I mentioned that the while loop was redundant because Ravi specifically
stated he only "expects" one row to be returned. When this is the case,
you don't need a while loop, and using a "bare" fetchrow_array (or
selectrow_array) works as needed: it fetches the "next" row, which is the
first row, and that's all you want, so that's all you need. This is how
my own mysql code has operated in dozens of places, so I have no reason
to believe otherwise.

I was recommending selectrow_array as an alternative to fetchrow_array,
because it combines the three statements into one, making three lines of
code into one. The only reason I've ever found to not use selectrow_array
is when you're working on a server with an older version of DBI, and your
only option is to use fetchrow_array. (After prepare and execute.)

I don't see anything wrong, *syntactically* with his code either (though
I presented a more idiomatic version, it is functionally equivalent), so
my guess is that it's a problem with the db, or the statement. Which is
why I suggested the COUNT(*) test. :}