List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 24 2002 8:50pm
Subject:Re: DBI fetchrow oddity
View as plain text  
At 15:26 -0500 9/24/02, Michael Boudreau wrote:
>I am getting odd (to me) behavior from fetchrow and wonder if 
>anybody else is aware of this problem. Apologies in advance if I've 
>overlooked something really obvious, or if this is a long-squashed 
>bug and I just need to update our Perl modules.
>
>Using Perl 5.6.0, DBI 1.14, DBD::mysql 2.0414, and MySQL 3.23.49.
>
>$sth = $dbh->prepare("SELECT PeopleID, Lastname, Email
>                       FROM People");
>$sth->execute;
>$sth->bind_columns(\$PeopleID, \$Lastname, \$Email);
>while ( $sth->fetchrow ) {
>   print "ID: $PeopleID\n" ,
>         "Name: $Lastname\n" ,
>         "Email: $Email\n\n";
>}
>
>IF the last value (Email) in any row is an empty string, the 
>fetchrow method returns false (and any remaining rows are not 
>processed). Very bad when the *first* row in the data set happens to 
>have a blank value in the last column.

fetchrow is an old alias for fetchrow_array, a function that returns an
array.  The behavior you're seeing is most likely due to calling an array
function in scalar boolean context, which will often result in puzzling
results.

Further evidence that this is so is that you get the proper behavior
with fetchall_arrayref.

>
>I first thought this was due to the "Email" column in my particular 
>table, where it is not a primary key but is still required to be 
>unique. However, I was able to get the same results selecting a 
>plain old varchar column. Can also get this result with "SELECT 
>PeopleID, Lastname, Email, '' " (and adding a fourth parameter to 
>bind_columns).
>
>Removing the bind_columns statement and doing this instead
>
>while ( ($PeopleID, $Lastname, $Email) = $sth->fetchrow ) {
>    # print something
>}
>
>works as expected. Also works properly doing fetchall_arrayref and 
>stepping through the returned data structure.

You shouldn't need to step through the data structure.  Use bind_columns
as you've been doing, and then call fetchrow_arrayref instead of fetchrow.
Your variables will have the row values bound to them as you want.

>
>Is anybody else familiar with this behavior of fetchrow?
>
>=================================================
>Michael Boudreau
>Senior Electronic Publishing Developer
>The University of Chicago Press
>1427 E. 60th Street
>Chicago, IL 60637-2954
>
>phone: 773 753 3298
>fax: 773 753 3383
>=================================================

Thread
DBI fetchrow oddityUnknown Sender25 Sep
  • Re: DBI fetchrow oddityPaul DuBois25 Sep