Update MySQL within Perl loop failing (fetchrow_array)

Question

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";

@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();
}

What is the external command that you are going to call? Normally one wouldn't execute a select and then update each of the results via a separate update command, when you could easily move the where clause used in the select statement to the where clause of the update statement. — Red Cricket
– Red Cricket, Commented Sep 9, 2013 at 3:29
finish is used when you don't want to iterate over the rest of the results returned by SELECT. It's useless after an UPDATE — ikegami
– ikegami, Commented Sep 9, 2013 at 7:11

Andy Lester · Accepted Answer · 2013-09-09 02:36:15Z

4

Building a SQL statement with variables and then prepare()ing it defeats the purpose of the prepare. You should build the SQL statement with a placeholder ? instead of $id, prepare() it, and then execute($id) it. As it is, you are leaving yourself open to SQL injection attacks.

Also, it seems that you are not using the warnings and strict pragmas. These two lines should be at the top of every program you write:

use warnings;
use strict;

They will save you much heartache and frustration in the future.

answered Sep 9, 2013 at 2:36

Andy Lester

94.2k16 gold badges106 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ikegami · Accepted Answer · 2013-09-09 01:47:16Z

4

In your loop, you overwrite the handle over from which you are fetching. Use a different variable. (Changing $sth = ...; to my $sth = ...; will do.) While we're at it, let's move the prepare out of the loop.

my $sth_get = $dbh->prepare("SELECT * FROM accounts WHERE field3 = false");
my $sth_upd = $dbh->prepare("UPDATE accounts SET field3=true WHERE id = ?");

$sth_get->execute();
while ( my ($field1, $field2, $field3) = $sth_get->fetchrow_array() ) {
    ...
    $sth_upd->execute($id);
}

answered Sep 9, 2013 at 1:47

ikegami

391k17 gold badges291 silver badges555 bronze badges

Comments

Red Cricket · Accepted Answer · 2013-09-09 01:07:18Z

1

You are stomping on your $sth variable when you execute this line ...

$sth = $dbh->prepare($update_customer_status);

Why not save off the result of $sth->fetchrow_array() to an array variable. Something like ...

my @select_results_AoA = $sth->fetchrow_array();

... and then iterate over the array ...

for my @row ( @select_resilts_AoA ) {

... instead of ...

while ( my ($field1, $field2, $field3) = $sth->fetchrow_array() ) {

answered Sep 9, 2013 at 1:07

Red Cricket

10.6k24 gold badges96 silver badges193 bronze badges

2 Comments

ikegami Over a year ago

Right problem, but your solution doesn't help at all. Maybe you meant to use selectall_arrayref?

Red Cricket Over a year ago

@ikegami Yes selectall_arrayref would be more appropriate. Thanks.

Collectives™ on Stack Overflow

Update MySQL within Perl loop failing (fetchrow_array)

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related