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

On Tue, Apr 13, 2010 at 1:56 AM, Tim Bunce <[email protected]> wrote:

> ----- Forwarded message from Troy Mulder <[email protected]> -----
>
> Date: Mon, 12 Apr 2010 17:48:37 -0400
> From: Troy Mulder <[email protected]>
> To: [email protected]
> Subject: How to loop through a database, row by row, and select and update
>        one  row at a time
>
>   Hello Tim (is it Dr. Bunce?),
>
>   My name is Troy Mulder, and I am trying to get a perl script to interface
> with a PostgreSQL database. I
>   am trying to step through each row of the database, and read one column
> of the row, and update another
>   column of the row.
>
>   When I follow the online tutorial and use the $sth =
> $dbh->fetchrow_array() method in a while condition,
>   as follows:
>
>   while ( @xml_content = $sth->fetchrow_array() ) {
>
>   I am able to select the two columns of interest. And I can do this for
> LIMIT 10 rows with no problem,
>   just using the select command 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();
>   }
>
>   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);
>

Here is the problem.

You clobber $sth - so it doesn't work.

In fact, $dbh->do(..) doesn't return a statement handle at all.




>   }
>
>


-- 
Jonathan Leffler <[email protected]>  #include <disclaimer.h>
Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."