So Many fdQueries

[bazzer747 25]

In my application I have a Data Module which has 2 FD Connections (one for Test site one for Live site) and over 20 FDQueries for all the tables and views (I connect to MS SQL Servers). My application fails quite regularly with error saying cannot access a closed dataset, and I think it is because at some stage I've used ExecSQL to insert or update a record and this appears to change the query in the FDQ. Which means I'm having to re-specify the sql query in code several times throughout the application.

 

It seems to me that this isn't very good coding/design. I can't use the FDCExecSQL command as I'm flipping between the Test and Live connections, again I feel this isn't the right way to do this.

 

If anybody can suggest a better way to manage my connectivity to avoid having to re-specify queries I would be very grateful.

[Attila Kovacs 629]

I won't go into a conversation about good practices because I would not do anything anymore without ORM, and you would not rewrite your app from the scratch.

But this could help you eventually:

 

var CurrentConnection: TFDConnenction;

CurrentConnection := LiveConnection  /  TestConnenction

CurrentConnection.ExecSQL('whatever');

 

 

btw, what is FDCExecSQL?

 

 

[haentschman 92]

Hi...

Quote

...and over 20 FDQueries for all the tables

...I'm having to re-specify the sql query in code several times throughout the application.

...It seems to me that this isn't very good coding/design

...another variant: https://en.delphipraxis.net/topic/219-dimowa®-sql-resource-creator/

Quote

A tool that manages the resource strings according to the tutorial mentioned in the german link (https://www.delphipraxis.net/49505-sql-dateien-als-resource-einbinden.html).

This is another way to manage SQL statements. The SQL are stored in separate files in a folder structure in the project. SQL can be tested in the preferred DBMS editor.

Important: SQL Statements OUTSIDE of the Sourcecode (pas, dfm) in resources *.res. (without SQL.Add; SQL.Add; SQL.Add... )

 

[bazzer747 25]

FDSQL - sorry, shorthand! Meant FDConnection1.ExecSQL (see http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Executing_Commands_(FireDAC)).

 

Thanks for tips here, I'll certainly look into both (Good job Chrome can translate pages :-)).

[haentschman 92]

Quote

I'm flipping between the Test and Live connections

[Breainstorming ]

Version1:

In my applications i have a Interface for every database system. In the interface all queries are created dynamically. The connection is created in this interface. All use this connection...

For you: Give your datamodule a public property (example: "CurrentConnection") . Connect all queries with this "CurrentConnection". If you change the "CurrentConnection" with the "LiveConnection" then the queries works with the "LiveConnection".

Datamodule....

private
  FCurrentConnection: TFDConnection;
public
  property CurrentConnection: TFDConnection;
...
// All queries
Query.Connection := FCurrentConnection;

Application...
Datamodule.CurrentConnection := FDLiveConnection;
// or
Datamodule.CurrentConnection := FDTestConnection;

 

Version2:

Create separate INI with the connection string for Test / Live. Over a build configuration the application loads his own INI.

 

Edited February 8, 2019 by haentschman

[bazzer747 25]

Hi,

 

Many thanks for all suggestions., I done a lot more thinking of my problems and come up with the following code:

procedure ConnectLiveTest( fdc: String );
var i: Integer;
begin
for i :=  0 to dm.ComponentCount -1 do
  if dm.Components[ i ] is TFDQuery then
  begin
    if fdc = 'Live' then
    TFDQuery( dm.Components[ i ]).Connection:= dm.fdcLive1
    else
    TFDQuery( dm.Components[ i ]).Connection:= dm.fdcTest1;

    TFDQuery( dm.Components[ i ]).Active    := True;
  end;
end;

This allows me to quickly switch between Test and Live MSSQL Server databases. And (whilst I'm still testing) appears to work OK.