----- 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);
}
Suddenly it no longer works after reading the first row. It doesn't go to
the next line and continue
selecting and updating in the while loop until all rows are updated.
Can you please help me, and tell me what I am doing wrong?
Respectfully,
-Troy
----- End forwarded message -----
