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

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: [email protected]
> To: [email protected]
> CC: [email protected]
> 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 <[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);
> }
> 
> 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