I do this in Delphi with FireDAC on Firebird:

with TFDQuery.Create do
    try
        ...
        SQL.Text := 'INSERT INTO t(c1) SELECT c2 FROM t2 WHERE c2 = 0';
        ExecSQL;

        WriteLn(RowsAffected);
    finally
        Free;
    end;

Why does .RowsAffected return 1 even when no records are inserted (when SELECT wouldn't match any row)?

I do this in Delphi with FireDAC on Firebird:

with TFDQuery.Create do
    try
        ...
        SQL.Text := 'INSERT INTO t(c1) SELECT c2 FROM t2 WHERE c2 = 0';
        ExecSQL;

        WriteLn(RowsAffected);
    finally
        Free;
    end;

Why does .RowsAffected return 1 even when no records are inserted (when SELECT wouldn't match any row)?

• delphi
• firebird
• firedac

• 2 1. please do not use With. It will cause more problems than it is worth writing less Code. Use inline Variables instead, its the closest to the With statement. 2. maybe it is similar problem: stackoverflow/questions/62240342/… – fisi-pjm Commented Mar 14 at 16:31
• @fisi-pjm I saw that other question earlier but I'm not sure if its actually related or not since that question was about MySQL and was affected by MySQL's own flags, but this question is about Firebird instead. – Remy Lebeau Commented Mar 14 at 21:00
• Firebird itself will report the correct count, which can for example be verified in ISQL with SET COUNT; and then executing the statement. Maybe Firedac does a shortcut and always reports 1 for an insert (i.e. they only considered INSERT ... VALUES ... which always produces one row (or an error), and not INSERT ... SELECT ... which can produce 0 or more rows). – Mark Rotteveel Commented Mar 15 at 10:06
• @fisi-pjm - I don's see any wrong in "with", if you absolutely know, what you do. I checked resolution form related question, but in FireBird there is no this option. @{Mark Rotteveel} - I need ceep compatibility with 3.0, but here: "COUNT - token unknown". – Dżyszla Commented Mar 17 at 7:21
• @Dżyszla to the With part: Yes, but unfortunately code itself is not always based on you. Code will change from time to time and using with, will obscure changes in code from you. In your Example (I know unlikely but never say never) what if FDQuery will introduce writeln. You will never notice the logic change in your code unless some bugs show up. And even your code Repo will tell you nothing about it. You will have a Hard time finding that one. And people (or AI Systems) who do not know what they do, will copy this code here and use it right away. – fisi-pjm Commented Mar 17 at 8:32

1 Answer 1

Change your ExecSQL to an Open

Open('INSERT INTO T2 (C2) SELECT C1 FROM T1 WHERE C1=0 RETURNING 1');

OK, it's a trick but now you can check that RowsAffected and RecordCount are equal

showmessage(Format('rows affected %d, rows inserted %d',[FDQuery1.RowsAffected,FDQUery1.RecordCount]));

tested with Firebird 5 perhaps older versions (<4) don't accept this.

Why always>=1 ? should be a good question for @dmitry arefiev

For fb<=4 what about

function InsertCount(Q : TFDQuery) : integer;
begin
Try 
 Q.ExecSQL('INSERT INTO T2 (C2) SELECT C1 FROM T1 WHERE C1=0');
 Q.Open('SELECT COUNT(1) FROM T1 WHERE C1=0');
 Result:=Q.Field[0].asInteger;  
Except
 Result:=0;
End;
end;