Sunday, 8 September 2013

Update MySQL within PERL Loop failing (fetchrow_array)

Update MySQL within PERL Loop failing (fetchrow_array)

I've created a Perl script which is meant to loop through an array (a
shortlist of customers who meet certain criteria), execute an external
command using system() , then update a field within each row once the
operation has completed.
It works on the first record (ie external command executes, customer
record updates), however when it gets to the second record I receive this
error:
DBD::mysql::st fetchrow_array failed: fetch() without execute() at
customer_update.pl
Through some googling I added the $sth->finish(); command, however whether
I include it or not (either inside the loop as shown, or straight
afterward) I still get the same error.
Can anyone shed any light for me as to what I am doing wrong here?
Here's an extract:
# PERL MYSQL CONNECT()
$dbh = DBI->connect('dbi:mysql:signups', $user, $pw)
or die "Connection Error: $DBI::errstr\n";
# DEFINE A MySQL QUERY
$myquery = "SELECT * FROM accounts WHERE field3 = false";
$sth = $dbh->prepare($myquery);
# EXECUTE THE QUERY
$sth->execute
or die "SQL Error: $DBI::errstr\n";
#$execute = $connect->query($myquery);
@records = $sth->rows;
print "Amount of new customers: @records\n\n";
while ( my ($field1, $field2, $field3) = $sth->fetchrow_array() ) {
#execute external command via system();
$update_customer_status = "UPDATE accounts SET field3=true WHERE id=$id";
$sth = $dbh->prepare($update_customer_status);
$sth->execute
or die "SQL Error: $DBI::errstr\n";
print "Customer record modified & MySQL updated accordingly\n\n";
$sth->finish();
}

No comments:

Post a Comment