Retrieve value of a column after update?

Question

I update a counter (no autoincrement ... not my database ...) with this FDQuery SQL:

UPDATE CountersTables
SET Cnter = Cnter + 1
OUTPUT Inserted.Cnter
WHERE TableName = 'TableName'

I execute FDQuery.ExecSQL and it works: 'Cnter' is incremented.

I need to retrieve the new 'Counter' value but the subsequent command

newvalue := FDQuery.FieldByName('Cnter').AsInteger

Fails with error:

... EDatabaseError ... 'CountersTables: Field 'Cnter' not found.

What is the way to get that value?

Answer 1

TFDQuery.ExecSQL() is meant for queries that don't return records. But you are asking your query to return a record. So use TFDQuery.Open() instead, eg:

FDQuery.SQL.Text :=
  'UPDATE CountersTables' +
  ' SET Cnter = Cnter + 1' +
  ' OUTPUT Inserted.Cnter' +
  ' WHERE TableName = :TableName';
FDQuery.ParamByName('TableName').AsString := 'TableName';
FDQuery.Open;
try
  NewValue := FDQuery.FieldByName('Cnter').AsInteger;
finally
  FDQuery.Close;
end;

If the database you are connected to does not support OUTPUT, UPDATE OUTPUT into a variable shows some alternative ways you can save the updated counter into a local SQL variable/table that you can then SELECT from.