List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 14 2002 4:45pm
Subject:Re: Making UPDATE return the no. of rows matched [from Perl]
View as plain text  
At 11:35 -0500 5/14/02, rory oconnor wrote:
>I'm having some trouble with this issue...I'm using perl DBI and mysql
>for a customer addressbook.  I know MySQL won't needlessly update a row
>if nothing's changed, but since I'm using $rc = $sth->rows; as an
>indicator of success, customers will get stuck if they decide they don't
>want to change anything and they click "update" (shopper can't move
>forward until some success indicator is sent) .  MySQL sends back "0"
>for rows affected.
>I could fake it for UPDATE queries and return successful even though no
>rows were matched, but then what if there really was a problem?  The
>address wouldn't be updated, but the customer would think it was.

If there is an error $DBI::err or $dbh->err() will contain a non-empty
error message.  That may be enough for you to disambiguate whether a
zero row count means "no rows changed" or "an error occurred".

>I know I can send back rows matched by setting the CLIENT_FOUND_ROWS
>flag, but then does that mean $sth->rows will return rows matched
>instead of rows affected for every query in my script (i have several
>different actions in my script whose success is keyed off of
>I can't find much information on how to set CLIENT_FOUND_ROWS and how to
>set the flag and use the info.  Any help is appreciated!
>(paul d - BTW, I LOVE "MySQL and Perl for the Web", it's my bible.
>thanks for writing such a great book)

Thanks.  Since you have the bible (thanks for writing that with a
little "b"), the answer to your question's in a footnote on page 37. :-)

Basically, just add ";mysql_client_found_rows=1" to the end of your
DSN string.

>On Thu, 2002-03-14 at 09:51, Paul DuBois wrote:
>>  At 10:41 +0100 3/14/02, Rune Steinseth wrote:
>>  >Hi all,
>>  >I have changed database for my Java app to MySQL.  The
>>  >app was developed with SQL Server.  Some of the logic
>>  >is dependent on getting the no. of rows updated
>>  >returned after an UPDATE query.  MySQL does only
>>  >return the rows that are really updated, not the rows
>>  >matched of an UPDATE.  This causes problems.
>>  >Is there an easy way to get MySQL always return the
>>  >number of rows matched in an UPDATE?
>>  There's a flag you can set to control this when connecting to the
>>  MySQL server, if you're using the C API or the Perl DBI API.  I
>>  don't think there's an option for this in Java, at least if you're
>  > using the MM.MySQL driver.  It seems to set the CLIENT_FOUND_ROWS
>>  flag unconditionally.  I guess you could change the source and
>>  recompile.
>>  >Regards,
>>  >Rune Steinseth
>>  >
>  > >______________________________________________________
>>  >Sjekk snørapporter...
>>  >fra 500 ski-destinasjoner i Europa
>  > >på

Making UPDATE return the no. of rows matchedRune Steinseth14 Mar
  • Re: Making UPDATE return the no. of rows matchedPaul DuBois14 Mar
    • Re: Making UPDATE return the no. of rows matchedrory oconnor14 May
      • Re: Making UPDATE return the no. of rows matched [from Perl]Paul DuBois14 May
  • Re: Making UPDATE return the no. of rows matched [from Perl]Mark Matthews15 May