Best Practices for Database Development in Delphi with Firebird

[Noor 0]

Hello everyone,

 

I'm seeking insights on database development practices in Delphi with Firebird. I have two specific questions:

 

• When it comes to database development in Delphi, what is the recommended approach: utilizing data-aware components like drag-and-drop fields and linking with TFDQuery for CRUD operations, or segregating CRUD operations into separate units for forms? I'm particularly interested in understanding the balance between ease of use, efficiency and code usability. Any insights or examples you can provide would be greatly appreciated.
• Often, we encounter situations where we need to fix a bug or implement changes in just one form out of many (over 100 forms) within our system. Currently, we update the complete executable, which includes all VCL forms in one project. What would be the best approach to handle such incremental updates efficiently without impacting all clients? I'm eager to learn about effective strategies in this scenario.

 

 

Thank you for your valuable input.

Warm regards,
Noor

[Die Holländer 49]

For a (very) long time I use dataobjects to query the database and I'm still very happy about it.

I would otherwise get mad hiding all the actual queries away from sourcecode by adding

them in the TFDQuery properties. If you do it in the classic way using a datamodule then

in the long run with large applications you will end up with a lot of TFDQueries on the module

and your application will start very slow. 

 

For example to use the DataObject looks like this:

MyDataObject:=TMyDataObject.Create('DatabaseName');
try
  MyDataObject.Code:='1234';
  If not MyDataObject.qryMyName.eof then
  Begin
    txtEdit.Text:=MyDataObject.Name;
  End;
Finally
  MyDataObject.Free;
End;

 

You can decide to make such dataobject for every table in your database.

Use the main table if you have Joins in the Query.

If you have a new query you add a new Setter in the object.

In this way you can reuse the queries in your entire code. (or in a comple other project/application)

 

For example if your TFDQuery is in the Public section you can still do things in the GUI like:

 

MyDBGrid.Datasource.DataSet:=MyDataObject.qryMyName;

 

Note: If you need more parameters in your query you can create the property like:

property CodePersonsID[aCode: Integer] : Integer write SetCodePersonsID;
procedure SetCodePersonsID(aCode: Integer; aPersonsID: Integer);
MyDataObject.CodePersonsID[45]:=99888;

 

A simple DataObject can look like this:

TMyDataObject = Class(TObject)
  Private
    fDataBaseName: String;
  protected
     procedure SetCode(aCode : String);
     function GetCode : String;
     function GetName : String;
  Public
    qryMyName : TFDQuery;

    procedure All;
    property Code : String read GetCode write SetCode;
    property Name : String read GetName;

    Constructor Create(aDataBaseName: String);
    Destructor Destroy; Override;
End;

implementation

uses SysUtils;

Constructor TMyDataObject.Create(aDataBaseName: String);
Begin
  Inherited Create;
  fDataBaseName:=aDataBaseName;

  //Create here your own TFDQuery.Create procedure..
  database_initQuery(fDataBaseName, 'qryMyName', qryMyName);
End;

Destructor TMyDataObject.Destroy;
Begin
  If qryMyName<>NIL then qryMyName.Free;
  Inherited Destroy;
End;

procedure TMyDataObject.All;
Var SQLText : String;
Begin
  SQLText:='select * from "MyTable" ';
  qryMyName.Active:=False;
  qryMyName.SQL.Clear;
  qryMyName.SQL.Add(SQLText);
  qryMyName.Active:=True;
  qryMyName.First;
End;

procedure TMyDataObject.SetCode(aCode : String);
Var SQLText : String;
Begin
  SQLText:='select * from "MyTable" where Code= :CODE';

  qryMyName.Active:=False;
  qryMyName.SQL.Clear;
  qryMyName.SQL.Add(SQLText);

  qryMyName.ParamByName('CODE').AsString:=aCode;
  qryMyName.Active:=True;
  qryMyName.First;
End;

Function TMyDataObject.GetCode : String;
Begin
  With qryMyName do
  Begin
    if FindField('code')<>NIL then
      Result:=FieldByName('code').AsString
	else Result:='';
  End;
End;

Function TMyDataObject.GetName : String;
Begin
  With qryMyName do
  Begin
    if FindField('name')<>NIL then
      Result:=FieldByName('name').AsString
	else Result:='';
  End;
End;

 

Edited April 8 by Die Holländer

[PeterBelow 239]

8 hours ago, Noor said:

Hello everyone,

 

I'm seeking insights on database development practices in Delphi with Firebird. I have two specific questions:

 

• When it comes to database development in Delphi, what is the recommended approach: utilizing data-aware components like drag-and-drop fields and linking with TFDQuery for CRUD operations, or segregating CRUD operations into separate units for forms? I'm particularly interested in understanding the balance between ease of use, efficiency and code usability. Any insights or examples you can provide would be greatly appreciated.
• Often, we encounter situations where we need to fix a bug or implement changes in just one form out of many (over 100 forms) within our system. Currently, we update the complete executable, which includes all VCL forms in one project. What would be the best approach to handle such incremental updates efficiently without impacting all clients? I'm eager to learn about effective strategies in this scenario.

 

 

Thank you for your valuable input.

Warm regards,
Noor

Oy, that's a really open-ended subject. The RAD approach (using data-aware controls and connection/query components you create and configure at design-time) gets you results (= working application) fast but it tends to get messy fast as well, with business logic mixed into form and data moduls, poor reuse of code (unless you plan well ahead). This approach makes proper separation of application layers harder and is excellent at producing apps that are a nightmare to maintain and document. Code can be written to be self-documenting and serve as source for project documentation as well, but how do you deal with all the stuff set at designtime and scattered across dozens or even hundreds of dfm files?

 

In my opinion RAD is workable for smaller projects with a few database tables and a few forms as well. For anything more complex and expected to have a lifetime of many years with frequent updates it pays to use a proper layered approach (e.g. model-view-controller or similar), with a object-relational mapper (ORM) as interface to the database and a client UI that works with data objects instead of directly talking to database tables, queries, or stored procedures. All the FireDac stuff would be hidden inside the ORM.

 

Unfortunately Delphi does not come with an ORM, but there are some 3rd-party offerings in that area, including open-source projects like MORMOT. The learning curve can be steep if you have never dealt with such a design before, but it is worth it IMO. I wrote my own ORM for a larger project at work 20 years ago, took about half a year (part time, programming was not my main task) since nothing suitable was available at that time. It has served me well and some of the programs are still in some use...

 

As for partitioning the app into modules that can be maintained independently: that is what packages are for, but it is not as straightforward as one would hope. It's DLL hell in disguise and you have to invest some serious though into how you partition your application. Packages are all or nothing, every unit used can only be contained in one single package. All other units requiring such a unit have to get it from the (shared) package, so you have to deploy not only your own packages but also all used RTL and VCL (or FMX) and 3rd-party packages.

As long as you do not change anything in the interface part of a packaged unit you can replace the build package in the production app with an updated package. If you change an interface you have to rebuild all packages using the modified one, plus the host app. Since forms etc. are declared in a unit interface that severely limits what you can change in such a form unit without major hassle.

 

Note that this applies to units you want to use from other packages directly. It does not apply to units only used internally by a package. So it is possible to achieve a better isolation of modules if the only exposed part is a factory function for an interface the host app then uses to talk to the internal stuff of the package. In this scenario you can get away with only building with the core RTL and VCL packages and one shared package declaring the interface types used, and actually you can use DLLs (build with packages) instead of full-fledged packages. But this can still degenerate into a maintenance nightmare if you are nor careful...

[Noor 0]

Thank you, @Die Holländer, for the explanation and example provided. I'll attempt to write an example code using my years of experience with C# Entity Framework, primarily in the MVC context, and share a draft code for review.

 

And, @PeterBelow, thanks for highlighting the pros and cons of project partitioning. We've decided to maintain one standalone executable to mitigate potential issues. While RAD is suitable for simple applications, for larger, complex projects requiring long-term maintenance, maximizing reusable code is imperative.

 

Thanks,

Noor

[Die Holländer 49]

On 4/9/2024 at 7:27 AM, Noor said:

While RAD is suitable for simple applications, for larger, complex projects requiring long-term maintenance, maximizing reusable code is imperative.

Whatever you program in Delphi, compared with most other languages, it will stay RAD and you can focus on you

actual problem instead for example fighting how to put things on the screen.

It takes the Online department, with Node, Javascript and pumping JSON files through the internet, with three developers

(backend, frontend and HTML/CSS designer) about one week for one screen. 

They calling Delphi old fashioned while I look to their source and problems as if they still live in the 1980's.