Bruce Johnson wrote:
>
> On Apr 13, 2010, at 1:56 AM, Troy Mulder 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.
>
In addition to what Bruce has said, unless your code is a lot more
complex than what you've given us (and your Perl knows something your
database does not or is doing some processing your database would find
difficult) you can probably do this a load faster in a procedure and
simply call that from Perl.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
