Re^2: Why does DBI prepare break my loop?

[McA]

Hi perlfan,

sorry, but I have to disagree with your general advice not to use prepare and execute. IMHO this use case is exactly the one to use prepared statement handles. It was just abused or misunderstood by aditya1977.

I would rewrite the snippet to the following ona rush:

my $data_source = 'something';
my $username = 'user';
my $password = 'password';

$dbh = DBI->connect($data_source, $username, $password, {
    RaiseError => 1,
    AutoCommit => 1,
});

my $sql_select = "
SELECT
    volumeletter,
    peernodes
FROM
    VolData
ORDER BY
    volumeid
";

my $sql_update = "
UPDATE
    VolData
SET
    volstatus = ?
WHERE
    volumeletter = ?
";

my %NUM_2_VOLSTATUS = (
    0 => 'Not Mirrored',
    1 => 'Mirroring',
    2 => 'Resync',
    3 => 'Broken',
    4 => 'Paused',
    5 => 'Resync Pending',
);

my $sth = $dbh->prepare($sql_select);
my $sth_update = $dbh->prepare($sql_update);
$sth->execute();

# Iterate over configured volumes and update status, rrd data and grap
+hs
while ( my ($vol, $peernodes) = $sth->fetchrow_array() ) {
    # Populate a hash with current volume data
    my %voldata = getVolumeData($vol, $peernodes);
    my $vol_current_state = $NUM_2_VOLSTATUS{$voldata{'MirrorState'}} 
+|| 'Unknown';

    print "COLLECTOR: Setting volume $vol to state $vol_current_state\
+n";
    $sth_update->execute($vol_current_state, $vol);
}
$sth_update->finish;
$sth->finish;
$dbh->disconnect;

The two SQL statements are prepared and checked early. The statement for the update which is often used in the loop is prepared exactly once and used many times.

This solution has another advantage: On a database which supports server side result set cursors you really iterate over the result set row by row. You don't pull the whole result set to the DB client.

I also replaced the switch clause by a hash lookup which should be faster. More readable is it anyway, IMHO.

Regards
McA

[perlfan]

You may, TIMTOWTDI =)

[McA]

Of course! :-)