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;

[download]

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

In reply to Re^2: Why does DBI prepare break my loop? by McA
in thread Why does DBI prepare break my loop? by aditya1977

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.