I guess you've edited that code since dreadpiratepeter commented since I see $id defined in the foreach loop. I commented on your post to dbi-users (which coincidentally used foreach my $ids but I'll reproduce some of it here.

I don't think we can help you because we don't know what HandleIds() does. I note it may return -1 and causes all of the remaining ids in your batch to be skipped. Some further comments below:

You could prepare your update statement once outside of the loop and use placeholders:

my $update_ids = $dbh->prepare(q/update queue set processed = ? where 
+id = ?/);
# later:
$update_ids->execute('T', $ids);

[download]
          foreach my $id (@ids2process){
                 $count++;
                my $ret = HandleIds($id);
                next if($ret == -1);

[download]

We don't know what HandleIds does and it causes all remaining ids to be ignored. This is probably where your problem is.

            eval{
                    my $update_ids = $dbh->prepare("UPDATE queue set p
+rocessed = \'T
\' where id = $ids");
                     $updated = $update_ids->execute();
            }; 
            if($@) { $log->error("Unable to update record for - id: $i
+d,
updated: $updated");}
            else { $log->info("Updated record for - id: $id, updated:
$updated");}

[download]

What if your code is wrong and you pass an id which does not exist in the table - you do not know the update changed nothing. Check $updated contains 1.

There is something wrong with the above logic due to HandleIds() again. If your select returns 10 rows but HandleIds returns -1 for the last id, next causes the loop to end and your commit did not get called. Much better to do:

Personally I don't like turning off AutoCommit like this I prefer to do:

$dbh->begin_work;
  #do my work;
$dbh->commit;

[download]

i.e., put the commit outside the loop then you don't care how many ids were processed, you just commit all the updates.

Did you intend to sleep 5s or 10s when no ids were found - you are doing the latter.

In reply to Re: Update is not working properly with Perl DBI Module by mje
in thread Update is not working properly with Perl DBI Module by shree

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.