Jump to content
bazzer747

So Many fdQueries

Recommended Posts

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.

Share this post


Link to post

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?

 

 

Share this post


Link to post

Hi...:classic_cool:

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... :classic_cool:)

 

Share this post


Link to post
Quote

I'm flipping between the Test and Live connections

[Breainstorming :classic_cool:]

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". :classic_cool:

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 by haentschman

Share this post


Link to post

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.

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

×