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?
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.
You have also the RETURNING Unified support Ok, doc only shows INSERT SQL but UPDATE works too. And I should use a substitution variable for tablename