(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 -
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: 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
Re: (Fwd) How to loop through a database, row by row, and select and update one row at a time
On Tue, Apr 13, 2010 at 1:56 AM, Tim Bunce tim.bu...@pobox.com wrote:
- 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);
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.leff...@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.
Re: (Fwd) How to loop through a database, row by row, and select and update one row at a time
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
Re: (Fwd) How to loop through a database, row by row, and select and update one row at a time
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
