Retrieve value of a column after update?-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

You have also the RETURNING Unified support Ok, doc only shows INSERT SQL but UPDATE works too