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]
to 
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.

In reply to Re: DBI doesn't return all rows! by ikegami
in thread DBI doesn't return all rows! [SOLVED] by halfcountplus

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.