----- Forwarded message from Troy Mulder <mulderta@gmail.com> -----

Date: Mon, 12 Apr 2010 17:48:37 -0400
From: Troy Mulder <mulderta@gmail.com>
To: Tim.Bunce@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 -----

--_6c2a8752-2dd0-4722-8ef7-919dc0ecfcc1_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Sounds more like an SQL problem to me.

=20

The way you are doing that update is the most inefficient  ways possible.=20

=20

They doing this as one SQL statement with an Update select or Join rather t=
hat select one subset and iterate over it to update another.

=20

=20

Anyway in you perl code=20

=20

>$sth =3D $dbh->prepare("SELECT msgid=2C xmlcontent FROM messages WHERE msg=
id =3D 1892362")=3B
> print "Message ID =3D $msgid\n"=3B
> $sth->execute()=3B


=20

you are not setting '$alteredmsg'  to anything you would have to do this

=20

 my ($alteredmsg) =3D $sth->fetchrow_array()=3B

=20

=20
 >$update_cmd =3D "UPDATE messages SET alteredcontent =3D '$alteredmsg' WHE=
RE msgid =3D $msg_id"=3B


>my $sth2 =3D $dbh->do($update_cmd)=3B

=20

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 SQ=
L

=20

cheers

=20

=20



=20
> Date: Tue=2C 13 Apr 2010 09:56:45 +0100
> From: Tim.Bunce@pobox.com
> To: dbi-users@perl.org
> CC: mulderta@gmail.com
> Subject: (Fwd) How to loop through a database=2C row by row=2C and select=
 and update one row at a time
>=20
> ----- Forwarded message from Troy Mulder <mulderta@gmail.com> -----
>=20
> Date: Mon=2C 12 Apr 2010 17:48:37 -0400
> From: Troy Mulder <mulderta@gmail.com>
> To: Tim.Bunce@pobox.com
> Subject: How to loop through a database=2C row by row=2C and select and u=
pdate
> one row at a time
>=20
> Hello Tim (is it Dr. Bunce?)=2C
>=20
> My name is Troy Mulder=2C and I am trying to get a perl script to interfa=
ce with a PostgreSQL database. I
> am trying to step through each row of the database=2C and read one column=
 of the row=2C and update another
> column of the row.
>=20
> When I follow the online tutorial and use the $sth =3D $dbh->fetchrow_arr=
ay() method in a while condition=2C
> as follows:
>=20
> while ( @xml_content =3D $sth->fetchrow_array() ) {
>=20
> I am able to select the two columns of interest. And I can do this for LI=
MIT 10 rows with no problem=2C
> just using the select command as in:
> while ( @xml_content =3D $sth->fetchrow_array() ) {
> $sth =3D $dbh->prepare("SELECT msgid=2C xmlcontent FROM messages WHERE ms=
gid =3D 1892362")=3B
> print "Message ID =3D $msgid\n"=3B
> $sth->execute()=3B
> }
>=20
> However=2C when I put any sort of an update command after that=2C as in:
>=20
> while ( @xml_content =3D $sth->fetchrow_array() ) {
> $sth =3D $dbh->prepare("SELECT msgid=2C xmlcontent FROM messages WHERE ms=
gid =3D 1892362")=3B
> print "Message ID =3D $msgid\n"=3B
> $sth->execute()=3B
>=20
> $update_cmd =3D "UPDATE messages SET alteredcontent =3D '$alteredmsg' WHE=
RE msgid =3D $msg_id"=3B
> $sth =3D $dbh->do($update_cmd)=3B
> }
>=20
> 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.
>=20
> Can you please help me=2C and tell me what I am doing wrong?
>=20
> Respectfully=2C
>=20
> -Troy
>=20
> ----- End forwarded message -----
 		 	   		 =20
_________________________________________________________________
Videos that have everyone talking! Now also in HD!
http://go.microsoft.com/?linkid=3D9724465=

--_6c2a8752-2dd0-4722-8ef7-919dc0ecfcc1_--

--001636cd737fa8f1a204841fee9f
Content-Type: text/plain; charset=ISO-8859-1

On Tue, Apr 13, 2010 at 1:56 AM, Tim Bunce <Tim.Bunce@pobox.com> wrote:

> ----- Forwarded message from Troy Mulder <mulderta@gmail.com> -----
>
> Date: Mon, 12 Apr 2010 17:48:37 -0400
> From: Troy Mulder <mulderta@gmail.com>
> To: Tim.Bunce@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);
>

Here is the problem.

You clobber $sth - so it doesn't work.

In fact, $dbh->do(..) doesn't return a statement handle at all.




>   }
>
>


-- 
Jonathan Leffler <jonathan.leffler@gmail.com>  #include <disclaimer.h>
Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

--001636cd737fa8f1a204841fee9f--

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

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