CODEWITHSUNDEEP
delphifiredac
fewrandom
fewrandom

Reputation: 318

Firedac TFDquery.RowsAffected - returns 1 when should return 0

I have a table 'EMAIL' with unique field 'addr'. When I make a query from mysql console (via Putty) it returns '0 rows affected' (since the 'id' didnt actually change), but FIREDAC always returns rowsAffected=1 (or 2 or 3 if there are more fields). Why is it so, how do I fix that?

+----+-------+------+------+
| id | owner | addr | pass |
+----+-------+------+------+
|  1 |  NULL | test | NULL |
+----+-------+------+------+
mysql> update EMAIL set id=last_insert_id(id);
Query OK, 0 rows affected (0.00 sec)                        // 0 affected!
Rows matched: 1  Changed: 0  Warnings: 0

FIREDAC (FDconnection + FDquery)

procedure test;
var conn:TFDCONNECTION;
    query:TFDQUERY;
begin;

conn:=TFDCONNECTION.Create(nil);
query:=TFDQUERY.Create(nil);
query.Connection:=conn;

conn.Params.Add('DriverID=MySQL');
conn.Params.Add('CharacterSet=utf8');
conn.Params.Add('Server=192.168.56.11'); //ubuntu server, v 14.14 5.7.30
conn.Params.Add('User_Name=root');
conn.Params.Add('Password=mypass');
conn.Params.Add('Database=MYDB');
conn.Params.Add('Port=3306');

query.SQL.Text:='update EMAIL set id=last_insert_id(id)';
query.ExecSQL;

//never shows '0', always thinks all the rows have been updated!
showmessage(inttostr( query.RowsAffected ));

query.Close;

conn.Free;
query.Free;
end;

[SOLVED!] I edited the source code of 'FireDAC.Phys.MySQL', line 471, commented out the 'CLIENT_FOUND_ROWS' flag (thanks to @Olivier)

Upvotes: 1

Views: 1211

Answers (1)

Olivier
Olivier

Reputation: 18122

For a MySQL database, in the case of UPDATE queries, the number of affected rows (returned by mysql_affected_rows()) depends on the CLIENT_FOUND_ROWS flag.

If the flag is disabled, then only records that are actually modified are taken into account.

If the flag is enabled, then all processed records (whether they are modified or not) are taken into account.

By default, CLIENT_FOUND_ROWS is disabled, but FireDAC enables it when it calls mysql_real_connect().

Upvotes: 1

Related Questions