>-----Original Message----- >From: Paul DuBois [mailto:[EMAIL PROTECTED] >Sent: Wednesday, April 05, 2006 10:56 AM >To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org >Subject: Re: Not exactly a dbi question > >On 4/5/06 9:46, "Garrett, Philip (MAN-Corporate)" ><[EMAIL PROTECTED]> wrote: > >> >> >>> -----Original Message----- >>> From: Paul DuBois [mailto:[EMAIL PROTECTED] >>> Sent: Wednesday, April 05, 2006 10:41 AM >>> To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org >>> Subject: Re: Not exactly a dbi question [snip] >>>>>> >>>>>> Sorry, I've been answering your question while assuming the mysql
>>>>>> driver conformed to the documented DBI interface. >>>>>> >>>>>> Perhaps this is a mysql thing, not a DBD::mysql thing? >>>>> >>>>> Perhaps. What is the non-conformity to the documented DBI interface >>>>> to which you refer? >>>> >>>> The DBI pod says: >>>> >>>> For a non-SELECT statement, execute returns the number of rows >>>> affected, if known. If no rows were affected, then execute >> returns >>>> "0E0", which Perl will treat as 0 but will regard as true. Note >> that >>>> it is not an error for no rows to be affected by a statement. If >> the >>>> number of rows affected is not known, then execute returns -1. >>>> >>>> The OP said that execute() was returning 1, whether the row was >>>> affected or not. >>> >>> I don't think there is an error here unless the meaning of "affected" >>> becomes defined more precisely. For non-SELECT statements, "rows >>> affected" can mean either "rows matched" >>> (regardless of whether actually changed) or "rows changed". The >>> default for MySQL is the rows-changed value, and that was also the >>> default for DBD::mysql until the 2.9002 change. The default for >>> DBD::mysql now is the rows-matched value. >>> >>> Does the DBI spec require some particular interpretation of >>> "affected"? (The JDBC spec requires the rows-matched value.) >> >> It's making more sense now. >> >> I'm a little confused though -- how does a DML operation have a >> different number for rows-matched and rows-changed? Isn't the point of >> DML to change all rows matched? I'll go looking at the JDBC docs for >> an explanation. > > It's most easily seen for a statement such as this: > > UPDATE tbl_name SET col_name = 0 WHERE col_name = 0; > > If you've selected the rows-changed count, $sth->rows() will always > return 0, because the statement doesn't actually change any col_name > value from its current value. > > If you've selected the rows-matched count, $sth->rows() will return > the number of rows for which col_name is 0. Thanks for the explanation. I'm glad we had this discussion, because I'm sure that would have bitten me had I been using JDBC or MySQL. I tend to think that the row is still affected in a logical sense. Whether the DBMS backend decides to physically write a row that hasn't actually changed really isn't any of my business. It gets even more confusing if there's a trigger on the table. In that case, a trigger could be fired even though the row wasn't physically updated (with Oracle, anyway). Philip
