On Tue, Apr 13, 2010 at 1:56 AM, Tim Bunce <tim.bu...@pobox.com> wrote:
> ----- Forwarded message from Troy Mulder <mulde...@gmail.com> -----
>
> Date: Mon, 12 Apr 2010 17:48:37 -0400
> From: Troy Mulder <mulde...@gmail.com>
> To: tim.bu...@pobox.com
> 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 <jonathan.leff...@gmail.com> #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."
