How to close MSSQL connections and switch to another database in delphi?

Data Abstract
1

Can anybody tell me how to change database connection from one MSSQL database to another? I change the connection string in ConnectionManager.Connections[i].ConnectionString,
clear the ConnectionPool and deactivate/activate all data tables, but when I try to access data on all tables it still points to the old database, instead of the new one (I know, because of the different data in those databases). Debug shows that the connection string was updated with the new data.

The only thing that works 100% is an application restart. This is a DataAbstract/RemObjects SDK service application, so restart is not really an option.

2

can you show piece of code what you do exactly, pls?

3

Here are the procedures for starting/stopping the server.

procedure TServerDataModule.Stop;
const sMethodName = 'Stop';
begin
  TraceIn(sClassName, sMethodName);
  try
    ROIndyTCPServer1.Active  := false;  // TROIndyTCPServer
    ROSuperTCPServer1.Active := false;  // TROSuperTCPServer
    Server.Active := false;             // TROIndyHTTPServer

    TVideoObjects.Active := false;      // TDAMemDataTable
    TVideoGlobal.Active := false;       // TDAMemDataTable

    ConnectionManager.ClearPool;        // TDAConnectionManager
  finally
    TraceOut(sClassName, sMethodName);
  end;
end;

procedure TServerDataModule.Start;
const sMethodName = 'Start';
begin
  TraceIn(sClassName, sMethodName);
  try
    try
      Server.Port   := Port;
      ConnectionManager.Connections[0].ConnectionString := fParameters;

      ApDBVersion := 0;

      TVideoObjects.Active := true;      // TDAMemDataTable
      TVideoGlobal.Active := true;       // TDAMemDataTable

      Server.Active := true;
      ROSuperTCPServer1.Active := true;
      ROIndyTCPServer1.Active  := true;
    except
      on E: Exception do
        LogError(sClassName, sMethodName, E.Message);
    end;
  finally
    TraceOut(sClassName, sMethodName);
  end;
end;
4

Do you use TDALocalDataAdapter on server-side or not?
if it is used, you need to clear ServiceInstance too:

LocalDataAdapter.ServiceInstance := nil;
5

I have tried it just now, but it did not help. Added code to clear the data adapter serviceInstance to the Stop() procedure, but it still connects to the old database.

6

my code was:

procedure TMainForm.Button1Click(Sender: TObject);
begin
  ServerDataModule.LocalDataAdapter.ServiceInstance := nil;
  ServerDataModule.ConnectionManager.ClearPool;
  ServerDataModule.ConnectionManager.Connections[0].ConnectionString := 'ADO?AuxDriver=SQLOLEDB.1;Database=Northwind;Integrated Security=SSPI;';
...
end;
7

Ok, I see the problem now. I placed “ServiceInstance := nil;” after all other lines. When I moved it before “ClearPool”, it started working.
Thank you.

1 Like