(Fwd) How to loop through a database, row by row, and select and update one row at a time

----- 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 -----
0
4/13/2010 8:56:45 AM
perl.dbi.users 11100 articles. 1 followers. Follow

4 Replies
1040 Views

Similar Articles

[PageSpeed] 49

--_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_--
0
byterock
4/13/2010 10:40:27 AM
--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--
0
jonathan
4/13/2010 3:29:29 PM
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


0
johnson
4/13/2010 4:15:05 PM
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
0
martin
4/13/2010 4:33:55 PM
Reply:

Similar Artilces:

Gridview paging enabled. Has checkbox to select the row . If user select the checkbox on one page and move to 2nd page to select the rows there also to export the selected rows to excel. But doing so
HiI am using Gridview and that has paging enabled. Using checkbox to select the particular row of the gridview to export the selected rows to the excel . Everything is working fine except for one case.Let user selected the "check" box for, five rows which user would like to export to excel (two of rows on page 1 of results and 3 of them on page 2 of results), however it only exports the 3 row details from page 2, because it seems to de-select the 2 rows from page 1 when I move to page 2 and vice versa. The code i am using is as :protected void BtnExcel_Click(object sender, EventArg...

I want to select top 3 rows except the first from 4 rows of a Table. It should select from top 2 row,top3 row and top4 row.
 Hi, Let say I've 4 rows in a table. I want to select top 3 rows except the first from 4 rows of a Table. It should select from top 2 row,top3 row and top4 row. Thanks in advance Asghar Ali Mohammed http://www.aliwebdev.com(Web Designer and Developer)Do not forget to "MARK AS ANSWER" on the post that helped you. If you can use Linq, you can use Skip. http://msdn.microsoft.com/en-us/vcsharp/aa336757.aspxThanks, EdMicrosoft MVP - ASP/ASP.NET Hii dear..U can fire the Query select Top(3) from Table1 order by id desc.. "A conclusion is whe...

DataSet rows being deleted, but after the update , the sql database is not updated. The delete rows still in the database.
 Stepping thru the code with the debugger shows the dataset rows being deleted.   After executing the code, and getting to the page presentation. Then I stop the debug and start the page creation process again ( Page_Load ).    The database still has the original deleted dataset rows. Adding rows works, then updating works fine, but deleting rows, does not seem to work.   The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.     cDependChildTA.Fill(cDependChildDs._ClientDependentC...

First Row, Next Row, Prior Row, Last Row
I have a SQL Anywhere 7.0 Database, have a table Product_detail which contains Column Name Datatype width Null Default batch_id char(10) No None prod_id Integer No None employee_id Integer No None quantity Integer No None price Numeric (14, 2) No None Its primary key is batch_id I have a Freeform Datawindow with this table I want to provide the first row, next row, prior row and last row Ho...

Row updating, row updated confusion
Okay this is the first time I've ever had to do code behind for row updating logic in a gridview. So be patient with me if it's totally incorrect. Do I put the gridview rebind code in the Row Updating or Updated event?  Currently, I have it in both as I'm simply troubleshooting to try to get it to work.  With this code, not only is the record not updating but the record disappears from the gridview upon refreshing.Protected Sub GridView2_RowUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdatedEventArgs) Handles GridView2.RowUpdated ...

Reduce rows displayed but putting 3 rows in one row
Hi all, I'm using PB6.5. I have an SQL DW that fetch some information from the DB but should display only two columns: a code and a value. Also they are displayed in the trailer of a group if that's actually important. In order to reduce displayed rows count, i would like to display each 3 rows in one row(Code,Value,Code,Value,Code,Value). The question is how can i do that?. I've tried to use computed fields and in the second and third pair values to use code[1],value[1],code[2],value[2] but only the code[1],value[1] are OK(Showing next row's data), the third ...

Update a rows in one table for each row in other table
Hello all, i am a newbie in SQL and i want to ask for your help in order to do the following update statement. I have a table tblUsers which holds all the users, one of the fields in their GroupId. In the UserGroup table i have a field "UsersCount" and in that field i want to keep the number of the users in that group. Basically i want everytime i am saving a user to recalculate the users in all usergroups. I am trying to have something like:SELECT User_GroupId, count(User_GroupId) as UsersCount FROM tblUsersWHERE DeletedFlag = 0 GROUP BY User_GroupId and then:"FOR EACH" User_GroupIdUPDAT...

update: how to delete a row only if the row is selected first
i have tried everything to get past this error and im finding it hard to delete a row from my database, can any1 help? code so far - delete with confirmation:   <asp:GridView ID="GridView1" runat="server" AllowPaging="True" CellPadding="4" ForeColor="#333333" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" DataKeyNames="fileID" OnRowCommand="GridView1_RowCommand" OnRowDataBound="GridView1_RowDataBound" OnRowDeleting...

how to drag and drop one row from one datawindow and update the exist row in other datawindow ?
I liked to drag and drop a field (assume: name) from a row of one datawindow (dw_1) to another datawindow (dw_2), and also update the exist row in dw_2. Thanks for help. -- Best regards, Ridwan Goldbase Technology Visit our Homepage : http://www.geocities.com/Wallstreet/Floor/5251/index.html http://members.tripod.com/goldbase http://members.xoom.com/goldbase * Check out the Y2K (Year 2000 Problems - Millenium Bugs) Take a look at the Drag Drop entries in the help file. Basically, you need to turn on Dragging using Drag(Begin!). When you drop onto dw_2, the drag...

Grid view rows operated using keyboard or arrow keys
Hi All..i need GridView rows - operated using keyboard and when a row is focused or selected, it should be it be in editable mode. when the focus moves to next row, the last row data, should get updated to database and new the data is to be displayed. The selected row must be now in edit mode. this to be done using arrow keys (Up and Down). It would be great help if you provide me a solution for this. Hi:   Please refer to following articles: http://forums.asp.net/p/1119365/1743285.aspx http://aspalliance.com/895_Understanding_the_JavaScript___doPostBack_Function.1   And foll...

Selecting a gridview row and keeping the last selected row in a gridview highlighted after mouse moves over a new row.
What is the best way to show a gridview row highlighted once selected and keep it highlighted once the mouse moves over a different row? Thanks, Steve hi,you can use <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="#FFFFFF" /> plz let me know if i misunderstood you Thanks for your help. One problem; however, is that after the row has been selected and I check the "grid.SelectedIndex" it has a value of -1 and not the index of the row that was selected on the grid. What do you recommend? Thanks, Steve hi, sorry i din...

how to update a row using a seperate update button not command button without selecting the row?
How to update a row using a seperate update button not command button without selecting a row? Simple question but dificult to do. The reason I ask this is that when a user clicks 'Update' I want each row to update the database. Please note I can't use findcontrol as their are no headers This is my code so far: protected void UpdateButton_Click(object sender, EventArgs e){    foreach (GridViewRow row in CellGridView.Rows)     {          string stext = CellGridView.Rows.ToString();     &nb...

returning the database row ID from a selected row from a GridView
Hi I'd like to be able to display a gridView with rows of data, and when the user selects a row, somehow return the ID value for the respective row from the database, not the row in the gridView - if that makes sense. If possible, I don't want the ID displayed in the gridView. Thanks for any helpRichard Use DataKeyNames and SelectedDataKey Property of GridViewSomnath Mali.NET Developer , Pune INDIA.Please Mark As Answer If my reply helped you. I’m not sure what it is your describing; could you elaborate? ThanksRichard 1. <asp:GridView DataKeyNames="ProductID" Al...

View severel rows from database in one gridview-row
I have a database with the columns name, day, activity. The content look like this: Peter, monday, work Alice, Thuesday, London Alice, Monday, Holiday  .... I want to show this in a gridview where each person is shown in one row and the columns are the days of the week. The example above should look like this on the screen: name, monday, thuesday, wednesday, thursday,... Peter,work,,,,,, alice,Holiday, London Is that possible ? Thanks Hi:   From your description I think you can use ItemTemplate. First just create this layout. The DataSource only have to select di...

Web resources about - (Fwd) How to loop through a database, row by row, and select and update one row at a time - perl.dbi.users

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Dodgy database exposes details of 191 million US voters
... years, there's a high chance that your personal details are now out in the wild. Security researcher Chris Vickery found a misconfigured database ...

Huge database of 191 million U.S. voters found on the open Internet
... such bulk information online, a recent discovery seems to indicate. According to VentureBeat , a security researcher found the exposed database ...

3.3 million Hello Kitty fans' details have been exposed in a huge database leak
... week after week, the hacks, breaches and data leaks continue to roll in. The latest victim? Hello Kitty. CSO Online is reporting that a database ...

Massive Voter Database Left Open For Public Consumption
Gosh, we could have avoided all the angst about the Clinton/Sanders voter database breach if only we'd known about this major database , accessible ...

Database configuration issues expose 191 million voter records
A misconfigured database has led to the disclosure of 191 million voter records. The database, discovered by researcher Chris Vickery, doesn't ...

Database error publishes data of 3 million Hello Kitty fans
Online researcher Chris Vickery uncovered a database this weekend containing the personal information of 3.3 million accounts associated with ...

191 Million US Voter Registration Records Leaked In Mystery Database
A huge database of 191 million US citizens is uncovered. But no one knows who carelessly left it open, whilst the FBI have been contacted.

Database Leak Exposes 3.3 Million 'Hello Kitty' Fans
... was accessible. Vickery told Reuters by e-mail that the company had plugged the holes he had found in three servers. But he said the database ...

Resources last updated: 1/7/2016 3:25:00 PM