rows affected not working..

[Faith Greenwood]

Hi,

I am making some changes to my mysql table via DBI (I am inserting the
ages of the people I already have in the table). I have the following:

my $sql="UPDATE table SET age=? WHERE name=?";
my $sth=$dbh->prepare($sql);
$sth->execute($age,$name) or warn "$DBI::errstr\n" and next;
my $rc = $sth->rows;

I have 100 records total. Of course, when I run this script the first
time I update the age column of every single person in the table and
$rc=1 for each of the 100 rows. However, running the script the 2nd
time (without changing any of the ages), I still get $rc=1 for each of
the 100 rows (it should be zero, since the row actually wasn't
updated).

To check, I updated a record via phpmyadmin and got 0 rows affected
(like it should). Why is Perl giving me the wrong response? More
importantly, how do I correct it?

thx!

 

[Peter Wyzl]

Hi,

I am making some changes to my mysql table via DBI (I am inserting the
ages of the people I already have in the table). I have the following:

my $sql="UPDATE table SET age=? WHERE name=?";
my $sth=$dbh->prepare($sql);
$sth->execute($age,$name) or warn "$DBI::errstr\n" and next;
my $rc = $sth->rows;

I have 100 records total. Of course, when I run this script the first
time I update the age column of every single person in the table and
$rc=1 for each of the 100 rows. However, running the script the 2nd
time (without changing any of the ages), I still get $rc=1 for each of
the 100 rows (it should be zero, since the row actually wasn't
updated).

To check, I updated a record via phpmyadmin and got 0 rows affected
(like it should). Why is Perl giving me the wrong response? More
importantly, how do I correct it?

If you are using DBD::mysql (usually invoked by use DBI and defining the
mysql database) you should check the value of mysql_client_found_rows
(check the DBD::mysql docs at
http://search.cpan.org/~capttofu/DBD-mysql-4.012/lib/DBD/mysql.pm)

I suspect you may have it set to TRUE but be expecting the result from
having it set to FALSE.

I could also be completely off the planet...

P

 

[Faith Greenwood]

If you are using DBD::mysql (usually invoked by use DBI and defining the
mysql database) you should check the value of mysql_client_found_rows
(check the DBD::mysql docs athttp://search.cpan.org/~capttofu/DBD-mysql-4..012/lib/DBD/mysql.pm)

I suspect you may have it set to TRUE but be expecting the result from
having it set to FALSE.

I could also be completely off the planet...

P

thanks guys! I think I will go with $sql="UPDATE table SET age=? WHERE
name=? AND age!=?" as suggested...I just am looking for something that
will alert me when the age has changed and this seems to work and to
be honest I am too tired of this project to really care at this point
so I'll just go with the easiest solution. At some point I will look
more into the client_found_rows.......thx again!