FDManager.DeleteConnectionDef does not delete connection definition

Question

My app has a designtime TFDConnection that gets reused when it connects to another database (type).
I also derive a pooled connection from its settings, and register this with FDManager.AddConnectionDef to be used when multithreading (like here).

When setting this up a second time I accidentally called AddConnectionDef again with the same ConnectionDefName. The documentation says:

The name must be unique across other connection definitions in the ConnectionDefs list, otherwise an exception is raised.

This does not happen. No exception is raised, I just end up with two ConnectionDefs with the same name.
For those curious: the next code block demonstrates this behaviour (RSP-19107 on Quality Portal). This is not my immediately issue, because I thought Well, then I use DeleteConnectionDef to remove the old one first.
But it turns out that that does not work either. See the second block of code.

procedure TFrmFireDACConnectionNames.BtnBug1Click(Sender: TObject);
var
   lParams: TStringList;
   i,l    : integer;
begin
   lParams := TStringList.Create;
   lParams.Add('User_Name=sysdba');
   lParams.Add('Password=masterkey');
   lParams.Add('database=D:\Testing\test.gdb');
   lParams.Add('Server=localhost');
   lParams.Add('Pooled=true');
   lParams.Add('DriverID=FB');
   FDManager.AddConnectionDef('FBPooled','FB',lParams);
   lParams.Values['database'] := 'D:\Testing\test2.gdb';
   FDManager.AddConnectionDef('FBPooled','FB',lParams);

   // This shows the two identical ConnectionDefs (inspect lParams):
   lParams.Clear;
   l := FDManager.ConnectionDefs.Count;
   for i := 0 to l-1 do
      lParams.Add(FDManager.ConnectionDefs[i].Name);
   // Contents on my machine:
   // Access_Demo
   // Access_Demo_Pooled
   // DBDEMOS
   // EMPOYEE
   // MSSQL_Demo
   // RBDemos
   // SQLite_Demo
   // SQLite_Demo_Pooled
   // FBPooled           <== Duplicates
   // FBPooled

   // To check that the two added have their respective Params, inspect lParams with breakpoints on the lines below:
   lParams.Assign(FDManager.ConnectionDefs[l-1].Params);
   // Contents on my machine:
   // User_Name=sysdba
   // Password=masterkey
   // database=D:\Testing\test2.gdb
   // Server=localhost
   // Pooled=true
   // DriverID=FB
   // Name=FBPooled

   lParams.Assign(FDManager.ConnectionDefs[l-2].Params);
   // Contents on my machine:
   // User_Name=sysdba
   // Password=masterkey
   // database=D:\Testing\test.gdb
   // Server=localhost
   // Pooled=true
   // DriverID=FB
   // Name=FBPooled

   lParams.Free;
end;

Below is the sample code demonstrating the DeleteConnectionDef failing. Note that I do not even use or open a TFDConnection.

procedure TFrmFireDACConnectionNames.BtnDeleteTestClick(Sender: TObject);
var
   lParams  : TStringList;
   i,l      : integer;
   lConnName: String;
begin
   lParams := TStringList.Create;
   lConnName := 'MyConnPooled';

   lParams.Add('DriverID=FB');
   lParams.Add('User_Name=sysdba');
   lParams.Add('Password=masterkey');
   lParams.Add('Database=d:\Testing\Diverse\FireDACConnectionNames\test.gdb');
   lParams.Add('Server=localhost');
   lParams.Add('Pooled=true');

   FDManager.AddConnectionDef(lConnName,'FB',lParams);

   lParams.Clear;
   lParams.Add('DriverID=MSSQL');
   lParams.Add('User_Name=test');
   lParams.Add('Password=test');
   lParams.Add('Database=test');
   lParams.Add('Server=VS20032008');
   lParams.Add('Pooled=true');

   for l := FDManager.ConnectionDefs.Count-1 downto 0 do
      if FDManager.ConnectionDefs[l].Name = lConnName then
      begin
         FDManager.DeleteConnectionDef(lConnName);     // This gets executed
         Break;
      end;
   FDManager.AddConnectionDef(lConnName,'MSSQL',lParams);

   // Check ConnectionDefs (inspect lParams):
   lParams.Clear;
   l := FDManager.ConnectionDefs.Count;
   for i := 0 to l-1 do
      lParams.Add(FDManager.ConnectionDefs[i].Name);
   // Contents on my machine:
   // Access_Demo
   // Access_Demo_Pooled
   // DBDEMOS
   // EMPLOYEE
   // MSSQL_Demo
   // RBDemos
   // SQLite_Demo
   // SQLite_Demo_Pooled
   // MyConnPooled      <== Still duplicate
   // MyConnPooled
   lParams.Free;
end;

So what can be going on here, and how can I fix this?

This is Delphi Tokyo 10.2.1
If you want to run this code, place a TFDPhysFBDriverLink and TFDPhysMSSQLDriverLink on your form. I tried calling .Release on those, but that did not help.

---

Correction: Placing the TFDPhysxxxDriverLink components is not necessary for running the code. I'm leaving the sentence in because the presence of their associated units is essential for the AddConnectionDefinition bug (see approved answer).

---

Issue resolved: Patches for FireDAC.Stan.Def.pas and FireDAC.Comp.Client.pas are available at that RSP-19107 link.

Answer 1

How to delete connection definition?

Problem with your deletion loop is caused by accessing the iterated object which incremented their reference count, which in turn prevented that object removal from the definition collection. I'd better avoid accessing that collection in general.

Btw. such loop makes little sense because the deletion method expects name, not index, so calling it directly will have essentially the same effect:

FDManager.DeleteConnectionDef(lConnName);

By doing so you avoid the mentioned reference count incrementing. But keep on reading.

How to prevent connection definition name duplicates?

But to the root of your problem. Connection definition names must really be unique, and that's what the manager should take care of. Unfortunately doesn't, because of the bug you've found. Before it gets fixed, you can simply ask if there is a connection definition of such name before you add one:

if not FDManager.IsConnectionDef('FBPooled') then
  FDManager.AddConnectionDef('FBPooled', 'FB', Params)
else
  raise EMyException.Create('Duplicate connection definition name!');

Code like that can be a workaround for the issue you've reported. I'll try to describe what's wrong.

What's wrong with preventing connection definition name duplicates?

To the RSP-19107 issue. Well, it's very well hidden one. I was able to reproduce the issue only if a physical driver module was included in the application^[1]. The expected exception:

[FireDAC][Stan][Def]-255. Definition name [FBPooled] is duplicated

is correctly raised when no physical driver module is included in the application. If there is a driver module included, no exception is raised, and connection definition with a duplicate name is added to the internal collection.

So, why code like this doesn't raise exception as the documentation claims when a physical driver module is included?

FDManager.AddConnectionDef('DefName', 'FB', Params);
Params.Values['Database'] := 'C:\MyDatabase.db';
FDManager.AddConnectionDef('DefName', 'FB', Params);

A duplicate check for definition name is inside the TFDDefinition.ParamsChanged method which reflects changes to the connection definition parameters. Sounds weird, but definition name that is passed to the AddConnectionDef method is later added to the definition parameters under Name key, and the engine then waits for change notification which calls the mentioned ParamsChanged method.

Definition setup in the AddConnectionDef method reads like this:

Definition.Params.BeginUpdate; { ← triggers TFDDefinition.ParamsChanging }
try
  Definition.Params.SetStrings(Params); { ← assigns the passed parameters }
  Definition.Name := 'DefName'; { ← adds (or sets) the Name key value in Params }
  Definition.Params.DriverID := 'FB'; { ← creates driver specific parameter instance }
finally
  Definition.Params.EndUpdate; { ← triggers TFDDefinition.ParamsChanged }
end;

It looks fine on the first view. But there's one little problem with the line setting Params.DriverID. It triggers creation of driver specific parameters instance (e.g. TFDPhysFBConnectionDefParams) which replaces the original Params collection. That's correct, but breaks the lock.

That's what happens, again in pseudocode:

Definition.Params.BeginUpdate; { ← Definition.Params.FUpdateCount += 1 }
try
  Definition.Params.Free;
  Definition.Params := TDriverSpecificConnectionDefParams.Create;
finally
  Definition.Params.EndUpdate; { ← Definition.Params.FUpdateCount == 0 }
end;

That's it. The Params object replacement simply cannot copy the string list's FUpdateCount value, which needs to be non-zero to trigger the OnChange event when calling EndUpdate method.

So that's the reason why TFDDefinition.ParamsChanged method is not triggered from that finally block. And if you remember one of my previous paragraphs, that is the place where duplicate check for definition name resides. Hence you are able to add duplicates when a driver module is included.

A possible fix of this issue in pseudocode would be:

var
  UpdateCount: Integer;
begin
  Definition.Params.BeginUpdate; { ← Definition.Params.FUpdateCount == n }
  try
    UpdateCount := Definition.Params.UpdateCount; { ← store the update count }
    Definition.Params.Free;
    Definition.Params := TDriverSpecificConnectionDefParams.Create;
    Definition.UpdateCount := UpdateCount; { ← set the update count for the new instance }
  finally
    Definition.Params.EndUpdate; { ← Definition.Params.FUpdateCount == n }
  end;
end;

---

^[1] Actually, if any of the FireDAC.Phys.<DBMS> driver files is in your uses list; these are included automatically by placing a TFDPhys<DBMS>DriverLink component on the form.