FireDac get the count of rows affected by FDQuery.execsql command

Question

How we can get the count of affected by FDQuery.ExecSQL command of FireDAC after execution of INSERT, UPDATE or DELETE commands on table1 of SQLITE database?

FDQuery.SQL.Text:='UPDATE table1 SET col1='abc' WHERE id<100';
FDQuery.ExecSQL;

How we can get the number of affected rows ?

The same query in ADO returns from MSSQL database the number of affected rows.

AffectedCount:=ADOQuery.ExecSQL;

Bonzer

Answer 1

There are a lot of ways.

---

n := FDConnection1.ExecSQL('UPDATE table1 SET col1=''abc'' WHERE id<100');

http://docwiki.embarcadero.com/RADStudio/Seattle/en/Executing_Commands_(FireDAC) http://docwiki.embarcadero.com/Libraries/Seattle/en/FireDAC.Comp.Client.TFDCustomConnection.ExecSQL

Executes a SQL command and returns the number of affected rows.

---

FDQuery.SQL.Text:='UPDATE table1 SET col1=''abc'' WHERE id<100';
FDQuery.ExecSQL;
n := FDQuery.RowsAffected;

http://docwiki.embarcadero.com/RADStudio/Seattle/en/Executing_Commands_(FireDAC) http://docwiki.embarcadero.com/Libraries/Seattle/en/FireDAC.Comp.Client.TFDRdbmsDataSet.RowsAffected

Getting DBMS Feedback

Use the TFDQuery.RowsAffected property to get the number of rows processed by the command (for example, the number of deleted rows by the DELETE command.) Note: For MS SQL Server, RowsAffected can be unexpectedly equal to -1 when a stored procedure or a table trigger omits SET NOCOUNT ON. Then, use the TFDQuery.RecordCount property to get the number of fetched rows.

---

https://www.sqlite.org/lang_corefunc.html#changes

FDQuery.SQL.Text:='UPDATE table1 SET col1=''abc'' WHERE id<100';
FDQuery.ExecSQL;
FDQuery.SQL.Text:='SELECT changes()';
FDQuery.Open;
n := FDQuery.Fields[0].AsInteger;

---

http://docwiki.embarcadero.com/Libraries/Seattle//en/Data.DB.TDataSet.PSExecuteStatement

PSExecuteStatement returns the number of rows affected by executing ASQL.

I do not know if AnyDAC implements this though.

Guardado

MS SQL Server, RowsAffected can be unexpectedly equal to -1 .... SET NOCOUNT ON ping back from 2023. I spent few days trying to graso why one ADOQuery based kegacy app stopped working, and i even when that far to patch RTL to re-implement RowsAffected... Today i found i already wrote about this 7 years ago... – Sausa