Sounds more like an SQL problem to me.
The way you are doing that update is the most inefficient ways possible.
They doing this as one SQL statement with an Update select or Join rather that
select one subset and iterate over it to update another.
Anyway in you perl code
>$sth = $dbh->prepare("SELECT msgid, xmlcontent FROM messages WHERE msgid =
>1892362");
> print "Message ID = $msgid\n";
> $sth->execute();
you are not setting '$alteredmsg' to anything you would have to do this
my ($alteredmsg) = $sth->fetchrow_array();
>$update_cmd = "UPDATE messages SET alteredcontent = '$alteredmsg' WHERE msgid
>= $msg_id";
>my $sth2 = $dbh->do($update_cmd);
as well you are using inline SQL which is relatively easy to attack with
in-line injection. I would also suggest that you use parameteres for your SQL
cheers
> Date: Tue, 13 Apr 2010 09:56:45 +0100
> From: tim.bu...@pobox.com
> To: dbi-users@perl.org
> CC: mulde...@gmail.com
> Subject: (Fwd) How to loop through a database, row by row, and select and
> update one row at a time
>
> ----- 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 -----
_________________________________________________________________
Videos that have everyone talking! Now also in HD!
http://go.microsoft.com/?linkid=9724465
