Re^2: Increment DB using Perl

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]

Comment on Re^2: Increment DB using Perl Download Code

Replies are listed 'Best First'.
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
Well the UPDATE statement is scoped to UPDATE all rows, each time through the loop. You need a WHERE clause on it. But I still don't understand what the $pointer loop has to do with the update loop. Which rows are you trying to update? What belongs in the WHERE clause?	[reply]
Re^6: Increment DB using Perl by Anonymous Monk on Mar 08, 2005 at 16:55 UTC
Re^7: Increment DB using Perl by jZed (Prior) on Mar 08, 2005 at 16:58 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
You need a static ID. If you want to go based on the ordering of the select statement, try (not sure how many DB's support this): `SELECT ROWID, ID FROM REQUEST` [download] and then update based upon that rowid (if possible)... but it's tricky when there's no key to go off of. I'm not even 100% sure that'll work. -------------- It's sad that a family can be torn apart by such a such a simple thing as a pack of wild dogs	[reply] [d/l]
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]