Trap TFDConnection error on data module create etc?

[Chris1701 0]

So recently I had an issue that really drove me crazy and I'm trying to fix it so it doesn't happen again. Details: I have a Delphi VCL database application, I develop this on my desktop Windows 11 dev computer using the latest Delphi 11.3 Patch 1 and in that environment I connect to my Firebird 4 database server located on a dedicated Windows Server 2022 computer for the purpose of testing. The target for this program is a Windows 11 tablet where the Firebird server runs on the local machine. When the program runs normally in the OnCreate event of the data module the proper server settings are loaded into the TFDConnection and then the connection is opened. So when I build the application on my dev computer the TFDConnection obviously must not have Connected checked otherwise when the application is transferred to the target tablet and it's run I get a fatal error and the program exits. Recently while I was reviewing one or more TFDQuery's the IDE must have automatically checked the connected property of the TFDConnection so when the application was built then transferred and I went to use it, it was unusable and there was no way that I could figure out to fix the problem in the moment without going back to my office and opening the project in the IDE and unchecking the connected property, building and transferring the program again.

 

So here's my question, if that happens again how can I trap the TFDConnection error so that the program can continue since once I'm in the program I can then manually set the connection options to what they are supposed to be and open the database and everything will work for that session? Now fyi I've tried several things but none of them seemed to work: First setup TFDConnection.OnError event to try and trap it; dropped a TApplicationEvents component on the Data Module form and tried to trap it using TApplicationEvents.OnException. So how can I trap that error?

 

A second idea that crossed my mind, is there anyway in the IDE to script or to say somehow "On application build all TFDConnections.Connected should be unchecked?" so that the IDE unchecks those components before building? That would then make it unnecessary to add any error trapping code to the application.

[weirdo12 19]

There is an TFDConnection property that you can set to tell it how use/save the state of the Connected property - ConnectedStoredUsage.

[Sherlock 663]

Is there no "DesignConnection" property? I recall using DOA components from AllroundAutomation 20 years ago that had this nifty feature.

[weirdo12 19]

If both of those are False the state of Connected will be ignored.

 

 

You can test this by setting Connected to True, saving the file, closing it and reopening it. Connected will be False.

 

Or create a new app. Drop a TFDConnection component on the main form. Set DriverName to SQLite. Add a BeforeConnect event handler. Put some dummy code in it so it does get deleted because it's empty.  Set a break point on it. Set Connected to True. You should get prompted for a password. Click OK. Now run the app. The breakpoint won't occur.

 

Now check auRunTime. Run the app again. It will break at the BeforeConnect event handler.

 

Edited July 13, 2023 by weirdo12

[Fr0sT.Brutal 900]

Familiar issue... I avoided it by clearing any connection requisites from design-time properties. Also ensured no admin login will occur in binary for the price of ability to query data in design-time.

If you need that, you can add special check in the code: if connection is active in FormCreate, then throw exception. You won't leave such alert unnoticed. More smart option is to add this check to build script if you use one so that it won't produce binary unless you turn connection off.

Edited July 13, 2023 by Fr0sT.Brutal

[Stano 143]

25 minutes ago, Fr0sT.Brutal said:

Familiar issue... I avoided it by clearing any connection requisites from design-time properties.

I do that too. The problem is if I use TFDTabale and I want to generate a list of table fields. In that case the connection must be active.
It's annoying. In a large project this must cause problems.

[Fr0sT.Brutal 900]

1 hour ago, Stano said:

I do that too. The problem is if I use TFDTabale and I want to generate a list of table fields. In that case the connection must be active.
It's annoying. In a large project this must cause problems.

Yeah, field generation will be painful. I got rid of table fields in design-time but assigning grid columns manually really annoys. Luckily it's not the frequent task.

Anyway the alternative could be some kind of pre-build/post-build processing that would remove connection properties from DFM and restore it back.

Edited July 13, 2023 by Fr0sT.Brutal

[Tom Chamberlain 47]

We prevent this issue with GExperts -> Set Component Properties and having it set the Active and Connected properties to False on all DB components before compiling. It does it automatically so we don't have to remember to do it 🙂  One of the best features of GExperts for us.

[Stano 143]

3 hours ago, Tom Chamberlain said:

We prevent this issue with GExperts -> Set Component Properties and having it set the Active and Connected properties to False on all DB components before compiling. It does it automatically so we don't have to remember to do it 🙂  One of the best features of GExperts for us.

Did I install it just for that?

[Anders Melander 1782]

1. Create the TFDConnection in code (e.g. in the DM constructor).
2. Open the connection after the DM has been created.

For complex applications, I use a design where the application startup is divided into stages. The stage progression is centrally controlled and broadcast to all interested parties.

Something like this:

// The following could be in the .dpr file
type
  TRunStageBoot = bsInit..bsReady;
  TRunStageShutdown = bsShutdown..bsShutdown;
begin
  // Broadcast startup progression
  for var Stage := Low(TRunStageBoot) to High(TRunStageBoot) do
    RunStageNotify(Stage);
      
  Application.Run;
  
  // Broadcast shutdown progression
  for var Stage := Low(TRunStageShutdown) to High(TRunStageShutdown) do
    RunStageNotify(Stage);
end.

...and the run stage management:

type
  TRunStage = (
    bsInit,			// Whatever needs to run before the DMs are created. E.g. load config.
    bsCreateModules,		// Create DMs
    bsConnectDatabase,		// Connect to database
    bsLoadDatabaseSchema,	// Validate and update database schema
    bsLogin,			// Perform application login
    bsInitUI,			// Create main UI (e.g. mainform)
    bsReady,			// Show UI
    bsShutdown			// Application shutdown
   );
  
  IRunStageSubscriber = interface
    {...GUID...}
    procedure RunStageNotify(Stage: TRunStage);
  end;

  TRunStageDelegate = reference to procedure(Stage: TRunStage);

procedure RunStageNotify(Stage: TRunStage);

procedure RegisterRunStageHandler(Delegate: TRunStageDelegate); overload;
procedure RegisterRunStageHandler(const Subscriber: IRunStageSubscriber); overload;
// ...similar for unsubscribe...

implementation

var
  // Create on demand in RegisterRunStageHandler. Free in finalization.
  RunStageSubscribers: TList<IRunStageSubscriber>; 
  RunStageDelegates: TList<TRunStageDelegate>;
  
procedure RegisterRunStageHandler(Delegate: TRunStageDelegate);
begin
  RunStageDelegates.Add(Delegate);
end;

procedure RegisterRunStageHandler(const Subscriber: IRunStageSubscriber);
begin
  RunStageSubscribers.Add(Subscriber);
end;

procedure RunStageNotify(Stage: TRunStage);
begin
  for var Subscriber in RunStageSubscribers do
    Subscriber.RunStageNotify(Stage);
    
  for var Delegate in RunStageDelegates do
    Delegate(Stage);
end;
...

...and the DM with the connection would then look something like this:

type
  TDataModuleDatabase = class(TDataModule, IRunStageSubscriber)
  private
    FConnection: TFDConnection;
  private
    // IRunStageSubscriber
    procedure RunStageNotify(Stage: TRunStage);
  public
    constructor Create(AOwner: TComponent); override;
  end;

var
  DataModuleDatabase: TDataModuleDatabase;

implementation

constructor TDataModuleDatabase.Create(AOwner: TComponent);
begin
  inherited;
  FConnection := TFDConnection.Create(Self);
  // Setup params on connection
  ...

  // Register so we will be notified when the connection should be opened
  RegisterRunStageHandler(Self);
end;

procedure TDataModuleDatabase.RunStageNotify(Stage: TRunStage);
begin
  case Stage of
    bsConnectDatabase:
      FConnection.Open;
  end;
end;

procedure RunStageNotify(Stage: TRunStage);
begin
  case Stage of
    bsCreateModules:
      DataModuleDatabase := TDataModuleDatabase.Create(Application);
  end;
end;

initialization
  // Register so we will be notified when the DM should be created
  RegisterRunStageHandler(RunStageNotify);
end;

 

[Chris1701 0]

12 hours ago, weirdo12 said:

If both of those are False the state of Connected will be ignored.

 

 

You can test this by setting Connected to True, saving the file, closing it and reopening it. Connected will be False.

 

Or create a new app. Drop a TFDConnection component on the main form. Set DriverName to SQLite. Add a BeforeConnect event handler. Put some dummy code in it so it does get deleted because it's empty.  Set a break point on it. Set Connected to True. You should get prompted for a password. Click OK. Now run the app. The breakpoint won't occur.

 

Now check auRunTime. Run the app again. It will break at the BeforeConnect event handler.

 

So if both of those are unchecked but the TFDConnection is checked when I build the executable will not have that Connected property checked on the data modules creation?

[Chris1701 0]

5 hours ago, Anders Melander said:

1. Create the TFDConnection in code (e.g. in the DM constructor).
2. Open the connection after the DM has been created.

For complex applications, I use a design where the application startup is divided into stages. The stage progression is centrally controlled and broadcast to all interested parties.

Something like this:

// The following could be in the .dpr file
type
  TRunStageBoot = bsInit..bsReady;
  TRunStageShutdown = bsShutdown..bsShutdown;
begin
  // Broadcast startup progression
  for var Stage := Low(TRunStageBoot) to High(TRunStageBoot) do
    RunStageNotify(Stage);
      
  Application.Run;
  
  // Broadcast shutdown progression
  for var Stage := Low(TRunStageShutdown) to High(TRunStageShutdown) do
    RunStageNotify(Stage);
end.

...and the run stage management:

type
  TRunStage = (
    bsInit,			// Whatever needs to run before the DMs are created. E.g. load config.
    bsCreateModules,		// Create DMs
    bsConnectDatabase,		// Connect to database
    bsLoadDatabaseSchema,	// Validate and update database schema
    bsLogin,			// Perform application login
    bsInitUI,			// Create main UI (e.g. mainform)
    bsReady,			// Show UI
    bsShutdown			// Application shutdown
   );
  
  IRunStageSubscriber = interface
    {...GUID...}
    procedure RunStageNotify(Stage: TRunStage);
  end;

  TRunStageDelegate = reference to procedure(Stage: TRunStage);

procedure RunStageNotify(Stage: TRunStage);

procedure RegisterRunStageHandler(Delegate: TRunStageDelegate); overload;
procedure RegisterRunStageHandler(const Subscriber: IRunStageSubscriber); overload;
// ...similar for unsubscribe...

implementation

var
  // Create on demand in RegisterRunStageHandler. Free in finalization.
  RunStageSubscribers: TList<IRunStageSubscriber>; 
  RunStageDelegates: TList<TRunStageDelegate>;
  
procedure RegisterRunStageHandler(Delegate: TRunStageDelegate);
begin
  RunStageDelegates.Add(Delegate);
end;

procedure RegisterRunStageHandler(const Subscriber: IRunStageSubscriber);
begin
  RunStageSubscribers.Add(Subscriber);
end;

procedure RunStageNotify(Stage: TRunStage);
begin
  for var Subscriber in RunStageSubscribers do
    Subscriber.RunStageNotify(Stage);
    
  for var Delegate in RunStageDelegates do
    Delegate(Stage);
end;
...

...and the DM with the connection would then look something like this:

type
  TDataModuleDatabase = class(TDataModule, IRunStageSubscriber)
  private
    FConnection: TFDConnection;
  private
    // IRunStageSubscriber
    procedure RunStageNotify(Stage: TRunStage);
  public
    constructor Create(AOwner: TComponent); override;
  end;

var
  DataModuleDatabase: TDataModuleDatabase;

implementation

constructor TDataModuleDatabase.Create(AOwner: TComponent);
begin
  inherited;
  FConnection := TFDConnection.Create(Self);
  // Setup params on connection
  ...

  // Register so we will be notified when the connection should be opened
  RegisterRunStageHandler(Self);
end;

procedure TDataModuleDatabase.RunStageNotify(Stage: TRunStage);
begin
  case Stage of
    bsConnectDatabase:
      FConnection.Open;
  end;
end;

procedure RunStageNotify(Stage: TRunStage);
begin
  case Stage of
    bsCreateModules:
      DataModuleDatabase := TDataModuleDatabase.Create(Application);
  end;
end;

initialization
  // Register so we will be notified when the DM should be created
  RegisterRunStageHandler(RunStageNotify);
end;

 

I'm not dismissing your approach out of hand and I see how that could work but I have maybe two dozen assorted query's that point to that connection. In the forms oncreate event I would then have to iterate through all TFDQuery's and populate the connection field after the creation of the TFDConnection. So I think that may be a little overkill for this instance, but I'm saving your post and code samples because I believe that I may have other projects where it would be very useful, thanks for taking the time to post!

[Fr0sT.Brutal 900]

6 hours ago, Chris1701 said:

In the forms oncreate event I would then have to iterate through all TFDQuery's

All linked datasources are registered at the connection object so you can loop thru them for activation. Moreover you can filter only those which belong to a specific DM

[Chris1701 0]

23 hours ago, weirdo12 said:

If both of those are False the state of Connected will be ignored.

 

 

You can test this by setting Connected to True, saving the file, closing it and reopening it. Connected will be False.

 

Or create a new app. Drop a TFDConnection component on the main form. Set DriverName to SQLite. Add a BeforeConnect event handler. Put some dummy code in it so it does get deleted because it's empty.  Set a break point on it. Set Connected to True. You should get prompted for a password. Click OK. Now run the app. The breakpoint won't occur.

 

Now check auRunTime. Run the app again. It will break at the BeforeConnect event handler.

 

I unchecked both and checked  compiled "Connected" and tried transferring the program to the tablet and that does fix the problem. Thanks so much, this was the most simple and straightforward fix for the issue.

[Tom Chamberlain 47]

18 hours ago, Stano said:

Did I install it just for that?

No, not just for that feature I really like the component naming, renaming and replacement features.  Makes migrating/upgrade older projects faster and the Tab Order as a tree saves time.

[Stano 143]

1 hour ago, Tom Chamberlain said:

No, not just for that feature I really like the component naming, renaming and replacement features.  Makes migrating/upgrade older projects faster and the Tab Order as a tree saves time.

As I have in my signature, I am a layman. Delphi is more of a hobby. When I looked at it, I didn't see anything interesting to me there. Among other things, because I have:

• MMX
• CnPack
• FixInhsight
• Pascal Analyzer
• ...

They provide me with more than I need. I don't need it for more complex stuff - I don't have it and couldn't handle it.

But I struggle with that problem from time to time. I have to switch it all the time. Active at design time and inactive at compile time.

[weirdo12 19]

2 hours ago, Chris1701 said:

I unchecked both and checked  compiled "Connected" and tried transferring the program to the tablet and that does fix the problem. Thanks so much, this was the most simple and straightforward fix for the issue.

I'm glad you gave it a try!

[weirdo12 19]

40 minutes ago, Stano said:

But I struggle with that problem from time to time. I have to switch it all the time. Active at design time and inactive at compile time.

Please try using the ConnectedStorageUsage property and save yourself all that hassle. You can have you cake and eat it too. Just uncheck uaRunTime and the TFDConnection.Connected property will never be True at runtime even when you save your unit with it set to True.

[Stano 143]

Project FootMan.exe raised exception class EIBNativeException with message '[FireDAC][Phys][FB]I/O error during "CreateFile (open)" operation for file "D:\DOKUMENTY\DELPHI XE7\FOOTMAN\DATA\FOOTMAN.FDB"
Error while trying to open file
The process cannot access the file because it is being used by another process. '.

As you can see, it doesn't work for me. Do I have something wrong?

[weirdo12 19]

20 minutes ago, Stano said:

As you can see, it doesn't work for me. Do I have something wrong?

No, I would say the file is already in use. Maybe you have no choice but to make sure the IDE closes the connection before you run your app. The IDE and your app can't both use the file. That's a different problem and it won't be solved by ConnectedStoredUsage. But if your want you app to always start with the Connected = False  outside of the IDE it will make sure that happens.

Edited July 14, 2023 by weirdo12

[Stano 143]

2 minutes ago, weirdo12 said:

Maybe you have no choice but to make sure the IDE closes the connection before you run your app.

That's exactly how I understand it. How can it be done?

[Uwe Raabe 2057]

46 minutes ago, Stano said:

The process cannot access the file because it is being used by another process.

This usually happens with embedded drivers that require exclusive access to the database. This would even happen when you run your project, open the IDE and try to set Connected to True. Also when you run two instances of your program at once. I doubt there is any automatic solution covering all these cases.

 

It should not happen with a decent InterBase or FireBird server, which are able to cope with multiple connections at once. Perhaps using one of those during development helps to circumvent the problem.

[Stano 143]

I forgot to write that in my program this is the Embedded version. 

[weirdo12 19]

17 minutes ago, Stano said:

That's exactly how I understand it. How can it be done?

Someone would have to write an IDE plugin.

[weirdo12 19]

11 hours ago, Stano said:

I forgot to write that in my program this is the Embedded version. 

Would you consider changing to SQLite? I know it might be a huge deal if you have a lot of database specific dependencies in your code so it might not be practical.