DBI doesn't return all rows! [SOLVED]

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

I have a postgreSQL db with a table with 6500 names in it. However, when I do a "select name from table" via DBI, I only get 3200 rows back.

#!/usr/bin/perl -w

use strict;
use warnings FATAL => qw(all);
use DBI;

my $DBH = DBI->connect("DBI:Pg:dbname=inxay","hombre", 0, { 'RaiseErro
+r'=>1 } ) 
    || die "DBI connect failed: $!";

my @Names;
my $q = $DBH->prepare("SELECT name FROM directory_project");
$q->execute();
while (my $n = $q->fetchrow_array()) { push @Names,$n }

my $e = $q->err;
print $e if ($e);

print $#Names."\n";

$DBH->disconnect || warn $DBH->errstr;
[download]

As stated, using the exact same query via psql (CLI to postresql) yields the entire table, not just half of it. There is no error thrown, but I do get this

DBI::db=HASH(0x112e210)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at ./onehundred.pl line 21.

What's up? I had assumed up to now that DBI was rock solid stuff. 6500 strings is nothing...

Never mind gang, that column was blank at that point in the table, which I presumed it could not be, ha ha.

20100422 Janitored by Corion: Added formatting, code tags, as per Writeup Formatting Tips

Comment on DBI doesn't return all rows! [SOLVED] Download Code

Replies are listed 'Best First'.

Re: DBI doesn't return all rows!
by ikegami (Patriarch) on Apr 21, 2010 at 20:52 UTC

I have a postgreSQL db with a table with 6500 names in it. However, when I do a "select name from table" via DBI, I only get 3200 rows back.
#!/usr/bin/perl -w

use strict;
use warnings FATAL => qw(all);
use DBI;

my $DBH = DBI->connect("DBI:Pg:dbname=inxay","hombre", 0, { 'RaiseErro
+r'=>1 } ) 
    || die "DBI connect failed: $!";

my @Names;
my $q = $DBH->prepare("SELECT name FROM directory_project");
$q->execute();
while (my $n = $q->fetchrow_array()) { push @Names,$n }

my $e = $q->err;
print $e if ($e);

print $#Names."\n";

$DBH->disconnect || warn $DBH->errstr;
[download]
As stated, using the exact same query via psql (CLI to postresql) yields the entire table, not just half of it. There is no error thrown, but I do get this

DBI::db=HASH(0x112e210)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at ./onehundred.pl line 21.

What's up? I had assumed up to now that DBI was rock solid stuff. 6500 strings is nothing...

Your statement handle is still active because you didn't read all the rows returned by your query.

You didn't read all the rows returned by your query because $q->fetchrow_array() returned a false value.

That means one of your records appears to have a false value for name. Likely possibilities for this value are NULL (which becomes undef) or an empty string.

The solution is to change

while (my $n = $q->fetchrow_array())
[download]

while (my ($n) = $q->fetchrow_array())
[download]

The latter uses a list assignment instead of a scalar assignment. In scalar context (as is the case here), the list assignment returns the number of elements returned by its RHS (fetchrow_array). Since fetchrow_array only returns an empty list (0 elements) when there is no more data to read, this will prevent your loop from exiting prematurely.

[reply]
[d/l]
[select]