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

6.8k views Asked by At

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;
1

There are 1 answers

1
Arioch 'The On BEST ANSWER

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.