Setting "Server" programmatically with a TFDConnection

Question

TFDConnection.Params.Server is not a valid published property in Delphi XE7. How can I set the server location programmatically? I have 2 MySQL servers (test and production) that are at different ip's and based on what I am doing in the application, I want to easily switch back and forth between the 2 servers.

Lewiss

So, what is the problem ? Close the connection (or disable the Active property), change the Server value in the Params string list and connect. – Antoneantonella

That's what I'm saying ... TFDConnection.Params.Server shows the error: 'TFDConnectionDefParams' does not contain a member named 'server'. Of course I'm actually using the "Name" of the object though, not "TFDConnection". – Lewiss

You can access it as a string list, e.g. FDConnection.Params[FDConnection.Params.IndexOfName('Server')] := '...';. Or you can try (don't have FireDAC by hand), (FDConnection.Params as TFDPhysMySQLConnectionDefParams).Server := '...' after adding FireDAC.Phys.MySQL unit (the latter would be safer). – Antoneantonella

@TLama: Since TFDConnectionDefParams is a TStringList descendant, you can use the TStrings.Values[] property instead: FDConnection.Params.Values['Server'] := '...'; – Dirt

@Remy, I forgot Values[], thanks! It looked a bit overcomplicated to me... Still, I would say that (FDConnection.Params as TFDPhysMySQLConnectionDefParams).Server := '...' should be the preferred way for changing that particular connection definition parameter due to its type safety. – Antoneantonella

Answer 1

Please read the documentation, it tells you exactly how to define a FireDAC connection for MySQL:

Working with Connections (FireDAC)

Connect to MySQL Server (FireDAC)

You would specify the DB server as part of a Connection Definition:

Defining Connection (FireDAC)

Connection Definitions can be defined in an external .ini file, which you can then reference in the TFDManager.ConnectionDefFileName property, or load dynamically using the TFDManager.LoadConnectionDefFile() method.

[MySQL_Connection_1]
DriverID=MySQL
Server=192.168.1.100
...

[MySQL_Connection_2]
DriverID=MySQL
Server=192.168.1.101
...

Or dynamically using the TFDManager.ConnectionDefs property:

var
  oDef: IFDStanConnectionDef;
begin
  oDef := FDManager.ConnectionDefs.AddConnectionDef;
  oDef.Name := 'MySQL_Connection_1';
  oDef.DriverID := 'MySQL';
  oDef.Server := '192.168.1.100';
  ...
  oDef.Apply;

  oDef := FDManager.ConnectionDefs.AddConnectionDef;
  oDef.Name := 'MySQL_Connection_2';
  oDef.DriverID := 'MySQL';
  oDef.Server := '192.168.1.101';
  ...
  oDef.Apply;

var
  oParams: TStrings;
begin
  oParams := TStringList.Create;
  oParams.Add('Server=192.168.1.100');
  ...
  FDManager.AddConnectionDef('MySQL_Connection_1', 'MySQL', oParams);

  oParams.Clear;
  oParams.Add('Server=192.168.1.101');
  ...
  FDManager.AddConnectionDef('MySQL_Connection_2', 'MySQL', oParams);

Either way, you can then tell TFDConnection which Connection Definition to use to reach each database when needed:

FDConnection1.ConnectionDefName := 'MySQL_Connection_1';
// or: FDConnection1.ConnectionDefName := 'MySQL_Connection_2';
FDConnection1.Connected := True;

Alternatively, you can specify the connection parameters directly in the TFDConnection.Params property if you do not want to pre-define separate connection definitions:

FDConnection1.DriverName := 'MySQL';
FDConnection1.Params.Clear;
FDConnection1.Params.Add('Server=192.168.1.100');
// or: FDConnection1.Params.Values['Server'] := '192.168.1.100';
...
FDConnection1.Connected := True;

Dirt

Answer 2

Late answer but it's simple to do.

Restating what TLama said in a comment:

The param properties can vary depending on the driver type, so set the driver type to MySQL and then cast the PARAMS as the driver type. Just do:

(Conn1.Params as TFDPhysMySQLConnectionDefParams).Server := '127.0.0.1';

This way the compiler can verify the param at compile time.

Turfman

True, yet the compiler cannot guarantee that Params will be of type TFDPhysMySQLConnectionDefParams (the connection object can be connected to a different database type). – Antoneantonella

He states he is working with MySQL - not other types. – Turfman

Answer 3

This works for me. Add any additional parameters as needed

var
oParams: TStrings;
begin
oParams := TStringList.Create;
oParams.Add('Server=' + YourServer);
oParams.Add('Database=' + YourDatabase);
oParams.Add('OSAuthent=Yes');

FDManager.AddConnectionDef('CNX1', 'MSSQL', oParams);
FDConnection.ConnectionDefName := 'CNX1';
FDConnection.Connected := true;
if FDConnection.Connected then
ShowMessage('Connected');
oParams.Free;

Orelu