Re: Increment DB using Perl

Replies are listed 'Best First'.
Re^2: Increment DB using Perl by Anonymous Monk on Mar 08, 2005 at 16:33 UTC
Sorry, here is the code I am testing #!/usr/bin/perl -w use strict; use DBI; # Set up database connection my $user = "content"; my $pass = "content"; my $dbh = DBI->connect("DBI:ODBC:LocalServer",$user, $pass, {RaiseErro +r => 1}); my $dbh2 = DBI->connect("DBI:ODBC:LocalServer",$user, $pass, {RaiseErr +or => 1}); my $new_id = 0; my $sql = "select id from request"; my $sql2 = ''; my $sth = $dbh->prepare($sql); my $sth2 = ''; my $sql_data = ($sth->execute() \|\| die $sth->errstr); while (my $pointer = $sth->fetchrow_hashref) { $new_id++; $sql2 = "UPDATE request SET ID='$new_id'"; $sth2 = $dbh2->prepare($sql2); $sth2->execute() \|\| die $sth2->errstr; } [download]	[reply] [d/l]
Re^3: Increment DB using Perl by jZed (Prior) on Mar 08, 2005 at 16:38 UTC
First, you don't need two database handles - you can almost certainly just use two statement handles on the same database handle. Second, don't put quotes around '$new_id' -- quotes are for delimiting strings in SQL, not for numbers. Third, don't do the prepare inside the loop, the whole point is prepare only needs to be done once. Fourth, you don't need \|\|die $sth2->errstr, you have RaiseError on. Fifth, what are you expecting to happen with $sql_data and $pointer, you never use them? You better describe what you want to happen. But thanks for the code :-).	[reply]
Re^4: Increment DB using Perl by Anonymous Monk on Mar 08, 2005 at 16:43 UTC
Ok, I can get rid of the code that isn't going to be used, but I just need to see every row "ID" in this table updated with numbers incremented like: for every row the first would have 0 and second row 1 and so forth...	[reply]
Re^5: Increment DB using Perl by jZed (Prior) on Mar 08, 2005 at 16:46 UTC
Re^6: Increment DB using Perl by Anonymous Monk on Mar 08, 2005 at 16:55 UTC
Some notes below your chosen depth have not been shown here
Re^3: Increment DB using Perl by RazorbladeBidet (Friar) on Mar 08, 2005 at 16:41 UTC
The problem is not with code, but the SQL. You're setting the ID to $new_id for EVERY row. That means you're only seeing the last result. -------------- It's sad that a family can be torn apart by such a such a simple thing as a pack of wild dogs	[reply]
Re^4: Increment DB using Perl by Anonymous Monk on Mar 08, 2005 at 16:45 UTC
And how would I fix that? To get 0 on the first row, 1 on the second and so forth?	[reply]
Re^5: Increment DB using Perl by RazorbladeBidet (Friar) on Mar 08, 2005 at 16:49 UTC
Re^3: Increment DB using Perl by Joost (Canon) on Mar 08, 2005 at 16:44 UTC
`"UPDATE request SET ID='$new_id'";` [download] Always sets ALL rows in request to $new_id. You should use some kind of WHERE clause to limit the effect, but that might not help you here. Also, it isn't exactly good practice to change the primary keys of existing rows (I'm assuming request.id is the primary key). ~~Maybe you want to skip the SELECT and just do 1 query like~~ `UPDATE request set ID=ID+1;` [download] updated: that last piece of code doesn't do what is wanted. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l] [select]