DBD::mysql::st fetchrow_array failed: fetch() without execute()

Question

fetchrow_hashref working fine, but when i use fetchrow_array getting following Error.

#!/usr/bin/perl

use warnings;
use DBI;

$DB_name    = 'database';
$DB_user    = 'root';
$DB_pwd     = '';
my $dsn = 'dbi:mysql:avm:localhost:3306';

$dbh = DBI->connect($dsn,"$DB_user","$DB_pwd");

print "\nConnection error: $DBI::errstr\n\n";

$sth  = $dbh->prepare("SELECT * FROM tblmanufacturer");
$sth->execute();

while ( ($id,$name) = $sth->fetchrow_array() ) 
{
        print "$id\t\t $name \n";
}

$sth->finish();

$dbh->disconnect();

DBD::mysql::st fetchrow_array failed: fetch() without execute() at

score 5 · Answer 1 · answered Sep 29 '11 at 16:27

5

I always use "die" on error at both "execute" and "prepare".

$sql = $dbh->prepare( $query ) or die "Unable to prepare $query" . $dbh->errstr;
$sql->execute() or die "Unable to execute '$query'.  " . $sql->errstr;

answered Sep 29 '11 at 16:27

mmrtnt

382
1
8

score 3 · Accepted Answer · answered Sep 29 '11 at 15:09

3

Check the return value of execute() and/or print "$DBI::errstr\n\n" and see if execute is failing.

print $sth->execute(),"\n";

answered Sep 29 '11 at 15:09

dlamotte

6,145
4
31
40

this 167, For print $sth->execute(),"\n"; and Use of uninitialized value $DBI::errstr for print "$DBI::errstr\n\n" – bharanikumar Bs Sep 29 '11 at 15:54
take a look at @mmrtnt's answer, it looks more worth while than mine. – dlamotte Oct 03 '11 at 20:08

score 2 · Answer 3 · edited May 23 '17 at 10:27

Another way is to capture the errors with a error handler, do what ever you need to them (send it to your log file, print them, die or continue executing the script).
This eliminates the need of " or die() " after every method. Documentation about the HandleError method can be found here.

For starters take this simple example:

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my $DB_name    = 'database';
my $DB_user    = 'root';
my $DB_pwd     = '';
my $dsn = 'dbi:mysql:avm:localhost:3306';
my ($sth, $id, $name);

my $dbh = DBI->connect($dsn,$DB_user,$DB_pwd, { PrintError => 0, ShowErrorStatement => 1, HandleError => \&dbi_error_handler,} );

$sth  = $dbh->prepare("SELECT * FROM tblmanufacturer");
$sth->execute();

while ( ($id,$name) = $sth->fetchrow_array() ) 
{
        print "$id\t\t $name \n";
}

$sth->finish();
$dbh->disconnect();

sub dbi_error_handler
{
    my( $message, $handle, $first_value ) = @_;
    # print to your log file, call your own logger etc ... 
    # here it will die() to be similar to "or die()" method, but the line number is incorect
    die($message);
    # if you return false it will check/execute RaiseError and PrintError
    return 1;
}

P.S. There is no reason to encapsulate yout string variables in quotes here :($dsn,"$DB_user","$DB_pwd");, don't do it, for more info about that, read this.

score 1 · Answer 4 · answered Apr 06 '21 at 14:00

I got this error in apache fcgid due to reusing $sth before I was finished with it. I was only seeing it in the apache error log. Putting in "or die ..." after prepare and execute statements did nothing to help, and the script appeared to work fine anyway (in normal use there was only one row fetched, but there was a possibility of more) as it had done what was expected before the error was encountered. Errors were just appearing in the Apache log. Simple fix, renamed $sth to $osth and problem went away. If you are seeing this error and re-using statement handles, try using unique statement handles to see if the problem goes away.

# rename to $ostmt
my $stmt="SELECT ...";
# rename to $osth
my $sth=$dbh->prepare($stmt) or die "Unable to prepare $stmt" . $dbh->errstr;
$sth->execute() or die "Unable to execute '$stmt'.  " . $sth->errstr;
while( my ( $f1, $f2 ... ) = $sth->fetchrow_array() ){
  # redeclare $stmt and $sth below using my $stmt, my $sth
  $stmt="SELECT ...";
  $sth=$dbh->prepare($stmt) or die "Unable to prepare $stmt" . $dbh->errstr;
  $sth->execute($f1) or die "Unable to execute '$stmt'.  " . $sth->errstr;
  my ( @stuff ) = $sth->fetchrow_array();
  # ...
  # do lots of stuff
  # ...
  # output fcgi content
} # error kicks in here

It was considerably more involved than the sample above but the error was very unhelpful so I'm leaving this answer in case it helps someone else.

DBD::mysql::st fetchrow_array failed: fetch() without execute()

4 Answers4

Linked