Problems changing database with pooled connection

[Mark Williams 14]

Within an ISAPI dll I create a pooled connection:

 

Params := TStringList.Create;
 try
  Params.Add('DriverID=PG');
  Params.Add('User_Name=*****');
  Params.Add('UnknownFormat=BYTEA');
  Params.Add('Pooled=True');

  FDManager := TFDManager.Create(Nil);
  with FDManager do
    begin
       ResourceOptions.SilentMode:=True;
       ResourceOptions.AutoReconnect:=True;
       AddConnectionDef(FD_POOLED_CONN, 'PG', Params);
      Active := true;
    end;
 finally
   Params.Free;
 end;

The server is left as the default ie localhost.

 

The server is a machine on my local network (ip 192.168.0.12). It is also a web server with an external id (say 52.132.222.67)

 

It hosts a number of databases and to change the referenced database I call the following:

  var
    Def : IFDStanConnectionDef;
begin

  try
    Def : =FDManager.ConnectionDefs.FindConnectionDef(FD_POOLED_CONN);

    if Def.Params.Values['Database'] <> dbName then
      Def.Params.Values['Database'] := dbName;

    if assigned(Query) then
      Query.Close;

    if assigned(Query) then
      Query.ConnectionName := FD_POOLED_CONN;

  except
    on E: Exception do
      AddToLog('Error SetDatabase: '+e.Message, leMajorError);
  end;

If I call the dll via the external ip address then my function to change the referenced database works just fine.

 

If I use local ip it fails with the following error:

Quote

[FireDAC] [Stan] [Def] -251. Cannot change definition [pooled_connection]. It has associated connection.

Why do I get this error if I use the local ip, but not if I call the dll with the external ip and is there any way around it?

[Dmitry Arefiev 101]

FDManager.Active := False;

// change what you need

[Mark Williams 14]

Thanks.

 

Does setting active to false lose the connection? Ie does it have to reconnect all over again?

 

Also, any idea why I don't get the same problem using the external ip address?

[Dmitry Arefiev 101]

Other option is to use TFDManager.CloseConnectionDef

http://docwiki.embarcadero.com/RADStudio/Sydney/en/Multithreading_(FireDAC)#Connection_Pooling
 

[Mark Williams 14]

Thanks again.

 

But by using either active := false or CloseConnectionDef am I having to reconnect all over again with all the necessary overhead? I am not trying to switch server, merely which table I wish to query.  

 

I am obviously keen to avoid any additional overhead.

 

I also still don't understand why I only get this error when I connect with an internal ip not with a local ip. It seems to me that if there is an error with the one there should also be an error with the other. 

 

 

[Dmitry Arefiev 101]

Yes, it is. But you can use always full object (table) name.

[Mark Williams 14]

1 minute ago, Dmitry Arefiev said:

Yes, it is. But you can use always full object (table) name.

Sorry I don't understand what you mean

[Jacek Laskowski 57]

21 hours ago, Dmitry Arefiev said:

Yes, it is. But you can use always full object (table) name.

@Dmitry Arefiev  Dmitry, please explain what you mean?

[Mark Williams 14]

My dll calls the function shown in my original post to change the database as required (it needs to have access to 3 databases on the same server).

 

If I call my dll using the external ip (eg https://52.132.222.67/MyDLLLocation/MyDLLName/MyDLLFunction) all works okay. The moment I substitute the external ip with an internal ip (192.168.0.12) I get the cannot change definition error.

 

I really want to avoid re-establishing a connection almost every time the dll is called. 

 

There are a number of things I do not understand and would like to understand:

 

1. Why is this happening at all? Is it because the pooled connection somehow thinks that its server property is being changed?
2. Why does the pooled connection think there is an associated connection in one instance (ie where local ip used), but doesn't when an external ip is used?
3. Is this to do with localhost being the server and, if so, how do I get around this?
4. If my only solution is as @Dmitry Arefievsuggests to use the "full object" can someone please tell me how to do that? Also, does it avoid the problem of re-establishing the connection each call?

But I would still like to understand why I am getting this different behaviour for external and internal ips.

[Jacek Laskowski 57]

Try debugging the difference in the firedac code, just before the error, how the manager's state differs?

[Mark Williams 14]

That's a good suggestion although debugging an isapi dll is a  bit tricky. I suppose I could monitor the connection by way of a flat file and see what that tells me.

 

 

[Jacek Laskowski 57]

Try to use OutputDebugString() function from WinAPI and this tool for viewing output:

 

https://docs.microsoft.com/en-us/sysinternals/downloads/debugview

[Mark Williams 14]

It has taken me an age to get back around to this, but I have finally uncovered the problem. It was not with FireDAC but my code.