Re: (Fwd) How to loop through a database, row by row, and select and update one row at a time

On Apr 13, 2010, at 1:56 AM, Tim Bunce wrote:

> However, when I put any sort of an update command after that, as in:
>
>   while ( @xml_content = $sth->fetchrow_array() ) {
>   $sth = $dbh->prepare("SELECT msgid, xmlcontent FROM messages WHERE  
> msgid = 1892362");
>   print "Message ID = $msgid\n";
>   $sth->execute();
>
>   $update_cmd = "UPDATE messages SET alteredcontent = '$alteredmsg'  
> WHERE msgid = $msg_id";
>   $sth = $dbh->do($update_cmd);
>   }

$sth is the handle to your cursor that you're iterating through. When  
you redefine it in the first line, you kill the one you were iterating  
through, ergo no more lines.

The proper (Perl, that is, dunno about the SQL...) is to use more than  
one cursor:

while ( @xml_content = $sth->fetchrow_array() ) {
   $sth2 = $dbh->prepare("SELECT msgid, xmlcontent FROM messages WHERE  
msgid = 1892362");
   print "Message ID = $msgid\n";
   $sth2->execute();

[I presume there's some missing steps in here, because otherwise  
absolutely nothing has happened....]

   $update_cmd = "UPDATE messages SET alteredcontent = '$alteredmsg'  
WHERE msgid = $msg_id";
   $dbh->do($update_cmd);
   }

Also, doing 'prepare()' like this inside of a loop is horribly  
inefficient, and can easily be avoided by doing the prepare statement  
outside the look with execution parameters, then put the value of the  
parameter in the execute() statement:

$sth2 = $dbh->prepare("SELECT msgid, xmlcontent FROM messages WHERE  
msgid = ?");

while ( @xml_content = $sth->fetchrow_array() ) {
   print "Message ID = $msgid\n";
   $sth2->execute(1892362);

....

This is a LOT faster, from experience.

Also "SELECT msgid, xmlcontent FROM messages WHERE msgid = 1892362" is  
also mildly inefficient, why are you returning the msgid when you  
already know it?

do "SELECT xmlcontent FROM messages WHERE msgid = 1892362" instead.

It's not much, but you're saving cycles and memory inside of a loop.

-- 
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs