On 4/5/06 10:04, "Garrett, Philip (MAN-Corporate)"
<[EMAIL PROTECTED]> wrote:
>
>
>> -----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.
>
The MySQL Connector/J driver automatically tells the server to return the
rows-matched value due to the JDBC requirement.
> 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
>
