(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
[Fwd: Re: failed: ERROR OCIEnvNlsCreate. Check (everything)]
Keeping dbi-users on CC seems to be too hard ... ;-) Original Message Subject:Re: failed: ERROR OCIEnvNlsCreate. Check (everything) Date: Tue, 13 Apr 2010 07:22:27 -0400 From: Perl Diety misterp...@gmail.com To: Alexander Foken alexan...@foken.de References: q2sb914e70a1004060656o63d399f3le68b76b458079...@mail.gmail.com 4bbc460f.5050...@easysoft.com 4bbcf0b3.7010...@foken.de q2lb914e70a1004081009r8c0efbd5ma60d9cba75d67...@mail.gmail.com 4bbe2931.9080...@foken.de h2hb914e70a1004081330s1f38f568g3d8dae4e911d8...@mail.gmail.com 4bbeb655.9000...@foken.de Hey Alexander- just getting back to work. I appreciate your detailed comments and I will review today! On Fri, Apr 9, 2010 at 1:08 AM, Alexander Foken alexan...@foken.de mailto:alexan...@foken.de wrote: Please keep dbi-users on Cc. The NLS_LANG issue posted by Sven Miller looks relevant. On 08.04.2010 22:30, Perl Diety wrote: To give you some more detail about what works and what does- we've run Perl CGI on this server for years. We access a MYSQL database every day, and all of that works 100% fine. We access library functions, system libs, etc again with no issues. So, DBI is ok, and most of Apache is also ok. The lone problem is ORACLE 10 access. So I think this is a good indicator that this is specifically related to Oracle ENV vars, or something to do with the DBD or DBI. Can't be DBI, or else MySQL access would also fail. Most likely are DBD::Oracle (especially the compiled part), Oracle client libraries, environment variables used by Oracle, and filesystem permission issues. Our system admn did update the DBI and DBD to the latest version Shouldn't hurt -- but HOW did he update? RPM install or compile on that machine? RPMs are precompiled, and thus need a very specific version of the Oracle client libraries -- those they were compiled against. A compile on the machine should link against the Oracle client libraries that are installed and you want to use, so there should be no problem unless the machine is really fed up, full of different Oracle client versions, Perl versions, and DBI versions. Sure, you can install multiple versions of Oracle, Perl and DBI on the same machine, but in that case, you must know very exactly what you are doing, and it is very likely that you end with a broken installation. and rebuilt the server. Which server? Oracle? In that case, you need to re-compile DBD::Oracle after updating Oracle. Also, the script accesses Oracle fine from a command line. Which sort of suggests that the DBD is OK? Right. If the simple-and-stupid script from my last mail works on the command line, it must be an issue with filesystem permissions or environment variables. As far as your step-by-step, I did that, and when I add in the CONNECT is when this error is thrown and it dies. I tried to get more info like fatalsToBrowser, inspecting $! and ORa Error codes etc. Comment out all code added by the connect step and the following steps, and compare the values of $, $, $(, $), and %ENV for CGI mode and command line mode. With Data::Dumper, the Useqq and Sortkeys options are really useful. $ and $ are user IDs, both values should be equal for a single run, but they should differ between CGI and command line mode. Typically, they are less than 100 (often 80) for CGI mode, and at least 1000 (100 on old systems) for command line mode. $( and $) are space-separated lists of group IDs, the first value is the primary group ID, the following values are additional group IDs. Again, both values should be equal for a single run, and they are very likely to differ between CGI and command line mode. Especially the first number should be different. Look up the IDs in the output of getent group (or the file /etc/group), and find out which groups are not available in CGI mode. Compare with the owning group(s) of the Oracle client library files and the Oracle configuration files, especially those files that aren't world readable (i.e. ls -l output showing -rwxr-x--- or -rw-r- instead of -rwxr-xr-x or -rw-r--r--). If the non-world-readable files are owned by a group available in command line mode, but not in CGI mode, you have a permission problem. Either put the user running Apache into the relevant group(s), or change the filesystem permissions (preferably though the Oracle provided unharden script). Compare the entire %ENV between CGI and command line mode. All variables available in command line mode, but not set or set to a different value in CGI mode, are suspect. Some obvious ones aren't relevant, like BASH and BASH_*, HIST*, PS1 to PS4, PWD, OLDPWD, SHELL, DISPLAY.
