Have an amazing solution built in RAD Studio? Let us know. Looking for discounts? Visit our Special Offers page!
CodeDelphiNews

Extract, Transform, Load – The Magic Behind HeidiSQL

Extract, Transform, Load - The Magic of HeidiSQL - hero image

HeidiSQL is a wildly successful open source database management tool. Apart from being extremely useful in the management of MySQL, SQL Server, PostgreSQL, and SQLite databases, it is also open-source and that source code is written in Delphi. Furthermore, if we integrate it with IDE Software like RADStudio, it will be more powerful. Moving on, we’ve already given a brief overview of it before but at that time we only scratched the surface of this wonderfully artful example of Delphi programming at its best. The code itself is packed with really great techniques. Let’s take a closer look at it.

HeidiSQL main screen

Things you will need to compile the HeidiSQL code

  • You need RAD Studio Delphi 10.4 or higher.  I used Delphi 10.42.
  • It’s easier if you have some form of Git source code control client installed.  I used my favorite GitHub Desktop client.
  • The HeidiSQL source relies on two custom components – the source for them is included in the HeidiSQL source download.
  • You should also download and install madExcept.
Download the HeidiSQL source code

Installing madExcept

If you haven’t come across madExcept before you’re missing out! It’s a really great tool for intercepting and reporting on program exceptions which occur while your program is running. The website explains in more detail but I thoroughly recommend it.

  1. Go to http://www.madshi.net/madExceptDescription.htm
  2. Download and run the installer
  3. Make sure you check madExcept v5. The installer is a little bit confusing – click on version 5 and it will select it to be installed (the default is not to install version 4 or 5 which has confused me in the past!)
madExcept installer screen

When it’s properly installed there will be an extra menu item added to your RAD Studio tools menu

madExcept additional menu item

Getting the HeidiSQL source code

Head on over to the HeidiSQL site and click on the “download source” button. This will take you to the following link: https://github.com/HeidiSQL/HeidiSQL

Syncing HeidiSQL source code with GitHub desktop

Installing the required third-party components

HeidiSQL relies on two additional components. Note that both components are very popular so make sure you don’t already have them installed. If you don’t have them installed follow the instructions below. The required items are:

  • SynEdit to provide a syntax-highlighted query editor area.
  • VirtualTreeView to implement a number of very fast tree and listview style UI views.

Installing SynEdit

Navigate to the ./HeidiSQL/components/synedit/Packages/Delphi10.4/SynEdit.groupproj project and load it. Right click and compile the SynEdit_R project and then right click and select “install” for the SynEdit_D design-time package.

Installing VirtualTreeView

Navigate to the ./HeidiSQL/components/virtualtreeview/packages/Delphi10.4/VirtualTrees.groupproj and load it. Right click and compile the VirtualTreesR runtime package.  Now right-click and select “install” for the VirtualTreesD design-time package.

Compiling required resource (.res) files

Compile resources

There’s a little bit of a gap in the steps I saw about compiling HeidiSQL from source code. There are a few .rc resource files for things like icons and fonts and there didn’t appear to be anywhere saying that they needed to be compiled. I may have missed them (I did try compiling the various group projects) in which case let me know in the comments and I’ll update this post with the correction – but until then do the following:

  • Navigate to the .HeidiSQL root source folder. In there is a batch file called “build-res.bat” – run that file. It should complete without errors.
  • Now navigate to the .HeidiSQLsourcevcl-styles-utils folder. In there is a file called “CompileResources.bat”
  • Edit that file with a text editor and remove the paths at the start of the “brcc32.exe” line so is just says “brcc32.exe AwesomeFont.rc
  • Now save and then run that batch file.
  • Edit the second file called “CompileResources_zip.bat” in the same way, removing the path; then save and execute it.
  • Both should run the Borland Resource command-line compiler and create the .res files required by the main project.

Opening and compiling the HeidiSQL Delphi source code

Right, we’re ready to open the main HeidiSQL project and should be able to compile it without errors!

Navigate to ./HeidiSQL/packages/Delphi10.4 and open the “heidisql.dproj” project.

It should open without any errors.

If you hit CTRL+F9 to build it will build the source code and create your own personal copy of the HeidiSQL exe which you should find in the .HeidiSQLout folder

HeidiSQL compiled in RAD Studio Delphi
HeidiSQL compiled with RAD Studio 10.42 Delphi

Little gems found in the HeidiSQL source code

The HeidiSQL Delphi code is a cornucopia of interesting methods, little tricks and techniques.

View HeidiSQL Delphi source code

For example, it recently introduced support for custom styles. The styles are embedded in the app using a .res compiled resource. If you navigate to the project’s source (main menu, project, view source).  Scroll down and you will see the line: {$R ....resstyles.RES}

It still uses the TStyleManager to safely load those styles but they are loaded from the internal resource. The advantage of this the style files do not have to be shipped with the end-user compiled application but the downside is that the app is limited to the embedded selection. Personally when I do this myself I prefer to look for external style files as a part of the process which allows for people supplying new styles simply by placing them in a designated folder which my code scans and loads into the list. That said, the HeidiSQL code leans toward doing things its own way and trying to be as lean as possible which I think is an admirable approach.

HeidiSQL is polyglottal

HeidiSQL can “speak” to several of the major relational database systems. What’s remarkable is that it does this without using any of the popular database component sets such as FireDAC, AnyDAC, MyDAC and so on.

If we navigate to the dbConnection unit and then the CreateConnection method we will see the following code:

HeidiSQL polyglottal SQL database connections

This is the key point where HeidiSQL can so deftly twist its tongue around the various SQL dialects.

From then on HeidiSQL executes SQL queries with very little variation required. It’s a remarkably terse piece of code overlaying a superbly succinct programming style. Developer Ansgar Becker really knows his stuff. I’ve always said that if you read another programmer’s code for long enough you will truly get an idea of the mind which created it.

Other hidden gems

HeidiSQL Export dialog in RAD Studio

Following through the various elements of the HeidiSQL code reveals many other delights. There is a logging section which records and reports back usage and environment statistics to an external web server via a PHP script. The app can also optionally log the queries executed to a local log file.

There is also a custom updater which can check for and run an automatic update of the executable. Look at the “UpdateCheck” unit for details.

HeidiSQL also has an excellent export facility allowing users to take the results of their SQL queries and export them in all sorts of formats including the slightly esoteric LaTex and WikiMarkup. It’s this kind of ability to run queries to extract the data, almost irrespective of SQL database server technology, show them in a user-friendly view, transform and export them to something else which has very quickly made HeidiSQL the goto DB manager tool for many people.

The HeidiSQL project accepts donations at their official web page here: https://www.heidisql.com/donate.php – it’s definitely a deserving Delphi project.


RAD Studio Delphi is the power behind some of the world’s most popular software – why not try it yourself and see what it can do for you?


Reduce development time and get to market faster with RAD Studio, Delphi, or C++Builder.
Design. Code. Compile. Deploy.
Start Free Trial   Upgrade Today

   Free Delphi Community Edition   Free C++Builder Community Edition

About author

Ian is the Embarcadero Developer Advocate, a professional writer, presenter, and host. He is a prolific software developer, voice actor, designer and poet. Ian is British American, born in London, now living in Dallas, Texas. "I get up early every day and write code".

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

IN THE ARTICLES