Jump to content
Mark Williams

Problems changing database with pooled connection

Recommended Posts

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?

Share this post


Link to post

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?

Share this post


Link to post

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. 

 

 

Share this post


Link to post
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

Share this post


Link to post

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.

Share this post


Link to post

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.

 

 

Share this post


Link to post

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. 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×