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

I'm having trouble with fetchall_arrayref() giving a very nonsensical (to me) error. Here's a little test that demonstrates it. (I know fetching results 3 rows at a time is silly, but it's only a test.) 
use DBI;
my $dbh = DBI->connect('dbi:mysql:test', 'test', 'test') or die;

$dbh->do($_) for (split /\s*;\s*/, <<'END_OF_SQL');
    drop table if exists foo;
    create table foo ( bar int );
    insert into foo set bar=5;
    insert into foo set bar=4;
    insert into foo set bar=7;
    insert into foo set bar=1;
    insert into foo set bar=3
END_OF_SQL

#####

my $sth = $dbh->prepare("select bar from foo") or die;
$sth->execute or die;

#                 $sth->fetchall_arrayref([0],   3)
while (my $rows = $sth->fetchall_arrayref(undef, 3)) {
  for (@$rows) {
    print "got @$_\n";
  }
  print "---\n";
}

__OUTPUT__
got 5
got 4
got 7
---
got 1
got 3
---

[download]
OK. This code runs fine (for me)! But if I change undef to [0] in the fetchall_arrayref() line, I get this output: 
__OUTPUT__
DBD::mysql::st fetchall_arrayref failed: fetch() without execute() at 
+foo.pl line 23.
---
DBD::mysql::st fetchall_arrayref failed: fetch() without execute() at 
+foo.pl line 23.
---
[... ad infinitum ..]

[download]
What gives? The execute() is fine, and certainly is before the fetchall_arrayref() call. But some people even seem to get these errors with the undef in there! FWIW, I'm using DBI 1.38.

Since my query only returns one column, I should be able to use [0] as the first argument to fetchall_arrayref() to explicitly just fetch the one column. Right?

It's not a huge problem for me to fetch rows one at a time and avoid fetchall_*, but it would be kinda nice to use fetchall_*, as it seems to be the fastest way to get lots of a data.

Additional: It's not the infinite loop that bothers me so much -- that can be worked around. It's that with the [0] argument, the fetchall_arrayref() doesn't work at all and I can't get the query's results.

blokhead

Replies are listed 'Best First'.
Re: DBI::fetchall_arrayref() error
by dbwiz (Curate) on Jan 12, 2004 at 00:35 UTC
    This change works for me, even though it does not make me feel good.

    It looks like a bug in the DBI to me.

    
$sth->{PrintError} =0;
while ( my $rows= $sth->fetchall_arrayref([0],3) and !$sth->err )
{
  for (@$rows) {
        print "got @$_\n";
  }
  print "---\n";
}

    [download]
[reply]
[d/l]
Re: DBI::fetchall_arrayref() error
by bart (Canon) on Jan 11, 2004 at 23:17 UTC
    According to DBI::Changes, the option to pass a limit to the rowcount as a second parameter is added in DBI 1.26.

    Changes in DBI 1.26, 13th June 2002

    Added $max_rows parameter to fetchall_arrayref() to optionally limit the number of rows returned. Can now fetch batches of rows.
    Me, as being one of the people who noticed this error, am still using DBI 1.21.

    I hate having to upgrade everything every few months — especially if everything else works very well.

    Update: In case anyone wonders, I was referring to this sentence:

    But some people even seem to get these errors with the undef in there!
[reply]

      I even tried with 1.39, and the problem exists.

      The OP didn't do anything strange, that syntax he used is in DBI document.

      "limit the number of rows"

      This has nothing to do with number of rows, but number of columns. I tried: $sth->fetchall_arrayref([0]), it fails in the same way.

      This is a bug.

[reply]
[d/l]
      still using DBI 1.21

      But the OP is using DBI 1.38 that supports this feature, so the problem is somewhere else ...

[reply]
Re: DBI::fetchall_arrayref() error
by caedes (Pilgrim) on Jan 12, 2004 at 01:50 UTC
    The only time I've ever gotten this "fetch without execute" error is when I had a corrupted table in my database. It took me forever to find the actual problem because the error message leads you to believe that it's a code problem. Try doing a cleanup of your tables and let me know it that clears it up for you.

    Update: try this URL: Corrupted MyISAM Tables

    -caedes

[reply]
Re: DBI::fetchall_arrayref() error
by Roger (Parson) on Jan 12, 2004 at 00:35 UTC
    You need to add an explicit row count check before your inner for loop to prevent infinite loop:

    while (my $rows = $sth->fetchall_arrayref(undef, 3)) {
          # ^ this condition is always true
          #   why? because when there is nothing to fetch,
          #   $rows is reference to an empty array, not undef

  last if ($#$rows < 0); # break out of infinite loop

  for (@$rows) {
     ...

    [download]

    I am using version 1.39 of DBI and I could not repeat the same error, fetch without execute, as you have indicated.

[reply]
[d/l]

      No.

      I tried this piece of code, it fails with the same error:

      use DBI;
use Data::Dumper;
use strict;

my $dbh = DBI->connect('dbi:ODBC:TestingDB',"","",{RaiseError => 1, Au
+toCommit=>1}) || die "failed";
my $sth = $dbh->prepare("select cof_name, price from coffees") or die;
$sth->execute or die;
while (my $rows = $sth->fetchall_arrayref([0,1]), 3) {
    last if ($#$rows < 0);
}
$dbh->disconnect();

      [download]

      And this does not explain why it works, if you change [0] to undef. Even if what you said is right, should not fetchall_arrayref gives user the same interface, with undef and [0].

[reply]
[d/l]
[select]
        Hi pg, I used the following to do the test earlier:
        #!C:/Perl/bin/perl
use strict;
use DBI;
use DBD::Sybase;
use Data::Dumper;

my $dbh = DBI->connect("dbi:Sybase:server=SERVER;database=DATABASE",  
+                          "USER","PASSWORD")
    or die "Can not connect to database!";

my $sth = $dbh->prepare( qq{ select amount from trades_gdw_total });

$sth->execute();

while (my $rows = $sth->fetchall_arrayref([0], 3)) {
  last if ($#$rows <= 0);
  for (@$rows) {
    print "got @$_\n";
  }
  print "---\n";
}

        [download]

        And I got the desired behaviour, with no errors reported. I am using DBI version 1.39, with DBD::Sybase, by the way.

        I studied the source code of DBI, and extracted the following code concerning the behaviour of fetchall_arrayref:
          # we copy the array here because fetch (currently) always
  # returns the same array ref. XXX

  if ($slice && @$slice) {
    
    # if called with [0]

    $max_rows = -1 unless defined $max_rows;
    push @rows, [ @{$row}[ @$slice] ]
      while($max_rows-- and $row = $sth->fetch);
  }
  elsif (defined $max_rows) {

    # if called with undef

    $max_rows = -1 unless defined $max_rows;
    push @rows, [ @$row ]
      while($max_rows-- and $row = $sth->fetch);
  }
  else {
    push @rows, [ @$row ] while($row = $sth->fetch);
  }

        [download]

        And everything points to fetch.

[reply]
[d/l]
[select]
Re: DBI::fetchall_arrayref() error
by sheep (Chaplain) on Jan 12, 2004 at 01:38 UTC

    Hello,

    Although DBI docs clearly state that:
    The execute method does not return the number of rows that will be returned by the query (because most databases can't tell in advance), it simply returns a true value.
    this solution works for me, (MySQL 3.23.58, DBI 1.38, DBD::mysql 2.9003) without an ugly error: 
    ... 
my $count = $sth->execute or die;
my $total = 0;
while (my $rows = $sth->fetchall_arrayref([0], 3)) {
die "Error: $DBI::errstr" if $DBI::err; # error different
                                                 # to 'end of while'
for (@$rows) {
    print "got @$_\n";
 }
  print "---\n";
$total += scalar @$rows;
last if $total == $count;
}

    [download]

    -sheep

    update: Corrected logic errror I found in my thinking...
[reply]
[d/l]

      I tried to print out $count, and the value was -1, when I actually selected 1000 rows back. So there is no way this will work, at least for my 1.39 version.

[reply]
Re: DBI::fetchall_arrayref() error
by Anonymous Monk on Jan 12, 2004 at 14:20 UTC
    After some inspection, here's the deal:
    fetchall_arrayref always returns an arrayref, which is always a true value. The underlying cursor is finished when the referenced array is empty OR HAS LESS ELEMENTS THAN REQUESTED. Thus, the revised idiom:
    while (my $rows = $sth->fetchall_arrayref( [@slice], $maxrows)) {
    last unless @$rows;
    
    # do stuff

    last if @$rows < $maxrows;
}

die $sth->errstr if $sth->errstr;

    [download]
    which doesn't look that great, with the two lasts and the always-true while $ref (unless some DBD decides to return undef on failure)... can anyone clean it up?
[reply]
[d/l]
      It seems the XS version returns undef after all data is returned:
      /* to simplify application logic we return undef without an error    *
+/
/* if we've fetched all the rows and called with a batch_row_count    
+*/
return &sv_undef;

      [download]
      ...but the code does not handle slices, and bounces it back to the perl sub.
[reply]
[d/l]

Back to Seekers of Perl Wisdom