On Apr 13, 2010, at 1:56 AM, Tim Bunce wrote:
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);
}
$sth is the handle to your cursor that you're iterating through. When
you redefine it in the first line, you kill the one you were iterating
through, ergo no more lines.
The proper (Perl, that is, dunno about the SQL...) is to use more than
one cursor:
while ( @xml_content = $sth->fetchrow_array() ) {
$sth2 = $dbh->prepare("SELECT msgid, xmlcontent FROM messages WHERE
msgid = 1892362");
print "Message ID = $msgid\n";
$sth2->execute();
[I presume there's some missing steps in here, because otherwise
absolutely nothing has happened....]
$update_cmd = "UPDATE messages SET alteredcontent = '$alteredmsg'
WHERE msgid = $msg_id";
$dbh->do($update_cmd);
}
Also, doing 'prepare()' like this inside of a loop is horribly
inefficient, and can easily be avoided by doing the prepare statement
outside the look with execution parameters, then put the value of the
parameter in the execute() statement:
$sth2 = $dbh->prepare("SELECT msgid, xmlcontent FROM messages WHERE
msgid = ?");
while ( @xml_content = $sth->fetchrow_array() ) {
print "Message ID = $msgid\n";
$sth2->execute(1892362);
....
This is a LOT faster, from experience.
Also "SELECT msgid, xmlcontent FROM messages WHERE msgid = 1892362" is
also mildly inefficient, why are you returning the msgid when you
already know it?
do "SELECT xmlcontent FROM messages WHERE msgid = 1892362" instead.
It's not much, but you're saving cycles and memory inside of a loop.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
