5

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;
Arioch 'The
  • 15,799
  • 35
  • 62
Avrob
  • 533
  • 2
  • 10
  • 20

1 Answers1

11

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.

Arioch 'The
  • 15,799
  • 35
  • 62
  • `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... – Arioch 'The Apr 23 '23 at 19:45