Firebird - RETURNING not working

Firebird - RETURNING not working

Hello.
I'm having problem in Firebird with RETURNING value with latest Unidac 10.1.0 on Delphi 11 update 3.

Problem occurs when "same" query is used for SELECT and INSERT/UPDATE.

I've attach db.txt with sample table T1 with three fields (ID,f1,f2).


Here is example that don't work in Firebird (it works on PostgreSQL)
  1. var
      q: TUniQuery;
      res: integer;
    begin
      UniConnection1.connect;
      q := TUniQuery.Create(nil);
      try
        q.Connection := UniConnection1;
        q.SQL.Add('SELECT COUNT(*) AS NumRec FROM T1');
        q.Open;
        q.Close;
        // same TUniQuery for insert
        q.Connection := UniConnection1;
        q.SQL.Clear;
        q.SQL.Add('INSERT INTO T1 (f1,f2) values (:f1,:f2) RETURNING ID');
        q.ParamByName('f1').AsInteger := 2;
        q.ParamByName('f2').AsString := 'Second';
        q.ExecSQL;
        res := q.ParamByName('RET_ID').AsInteger;
      finally
        q.Free;
      end;
    end;
Record is written to table but result in variable "res" is 0 - ERROR?!?

If I declare another TUniQuery just for INSERT than it works.
  1. var
      q,q1: TUniQuery;
      res: integer;
    begin
      UniConnection1.connect;
      q := TUniQuery.Create(nil);
      q1 := TUniQuery.Create(nil);
      try
        q.Connection := UniConnection1;
        q.SQL.Add('SELECT COUNT(*) AS NumRec FROM T1');
        q.Open;
        q.Close;

        q1.Connection := UniConnection1;
        q1.SQL.Clear;
        q1.SQL.Add('INSERT INTO T1 (f1,f2) values (:f1,:f2) RETURNING ID');
        q1.ParamByName('f1').AsInteger := 3;
        q1.ParamByName('f2').AsString := 'Third';
        q1.ExecSQL;
        res := q1.ParamByName('RET_ID').AsInteger;
      finally
        q.Free;
        q1.Free;
      end;
    end;
Record is written to table and result in variable "res" is 3 - OK.

On PostgreSQL result in first example is 2, and in second example is 3 which is correct.

I think that this could be because of the latest support for Firebird 5:
  1. Added support for multiple rows being returned by DML with the RETURNING clause
I've tried other commands after "Q.Close;"
  1.     q.Close;
        q.Connection := nil;
        q.Params.Clear;
        q.Params.ClearAndResetID;
It didn't help.
If I destroy and create query than it works.
It looks like something is not reset after close and disconnect on Firebird database.

I suppose that this is some error because this work before on Firebird (and still working on PostgreSQL).
Thanks.