Are You Using The Secret Alternative To SQLite?

I know the best way to traumatize a software developer who has been using Delphi for some time. You just show them this image:

bde-config the horror, the horror

The BDE Administration Manager. So many ‘happy’ hours trying to resolve “PDOXUSERS.NET is in use” messages and learning all about the purpose of arcane settings like SharedDir and NETDIR. Shudder. If you wanted to store a database accessed via the BDE on a network drive there was a whole bunch of things you’d have to do to make sure it didn’t get corrupted. Despite this, developers became experts at repairing Paradox database corruption. Things were pretty bleak, although it its defense this was a time when almost everything we have come to take for granted today was still in its nascent stages; dial-up was common (and erratic) and almost everyone had at least one AOL CD as a drinks coaster.

Why did people use the BDE if it had so many problems?

Well, the short answer was: it worked. In fact, once you got it configured it worked pretty well and was extremely easy to use. It allowed you to link to various databases like DBase and Paradox in a simple way. So simple that Paradox became a de facto non SQL database technology along with DBase which had problems of its own.

SQLite the universal toolkit

Along into that pre-Windows arena sidled SQLite. It was designed from its inception as a simple database which would allow SQL-like commands to be executed against a set of tables contained in a single ‘flat’ file. It had – and still has – limitations and quirks like AUTOINC columns not being the same as ROWID columns, or the fact it has no native DATETIME or BOOLEAN types.

Oh, and like PostgreSQL, a name which still trips me up when I have to say it out loud, the SQLite name is annoying. Seequal light; shouldn’t it be pronounced “seequal ite”?

Despite this it has become massively popular.

What’s the secret native Delphi alternative to SQLite?

Hiding in plain sight is a pretty impressive database system which almost completely replaces SQLite for desktop apps written in Delphi and C++ Builder. This database is Absolute Database from Technology Partner Component Ace.

Component Ace Absolute Database website

Why isn’t Absolute Database more well-known?

For whatever reason Absolute Database hasn’t captured the imagination of Delphi developers in quite the same way as other systems. I’m not entirely sure why this is.

In my ‘day job’ we use Absolute Database for a desktop product – which can also be multi-user and with the data stored on a network server. That desktop product has close to 10,000 individual installations. Like any flat file database people do crazy things like experience powercuts in the middle of data being written or some malicious malevolence called “Windows Updates” clobbers the server and reboots slightly less gracefully than the Windows API documentation would suggest is the case. On app start-up the Absolute Database database silently recovers and automatically repairs any damage, no user intervention required.

It’s tried and tested for us. Out typical client profile for the desktop version of our products are small to medium-sized businesses and the ubiquitous ‘mom and pop shop’. We do have client-server products too which use full-blooded RDBMS like MySQL and PostgreSQL but the product line using Absolute Database is designed to be easy for ‘regular’ users to install and use without the need for DB administrators, technical staff or, in many cases no proper network and attendant IT staff.

My list of key features of Absolute Database

  • Single-file for each database. Note though your apps can use multiple databases simultaneously if you choose – we do this with one for local and/or volatile temporary data and one for shared and networked data.
  • Absolute database comes in two ‘flavors’ – the personal edition is for single-user accessing the data; the multi-user version allows the developer to tick a box allowing multi-user, multi-client shared access of the same database file with all the kind of locking mechanisms you would expect.
  • It’s aimed squarely as BDE replacement and SQLite alternative without any need for drivers or linking of static libraries of DLLs.
  • Fully written in Delphi and everything needed to access the database is built right into your code so there’s nothing to deploy with your apps apart from the database.
  • Unlike SQLite it supports ALL the data types you would expect such as DATETIME, BOOLEAN and so on (both missing from SQLite) as well as keys, indices and, well everything really including autoincrement fields and other such norms for a relational database.
  • Self-repairing. In the event of corruption being detected the Absolute Database system automatically repairs and recovers itself.
  • It is TDatabase, TDataSource and other data access component compatible. Because of this everything which can link to a TDatasource will work such as reporting components, third-party grids and so on.
  • Very cost effective with no royalty or deployment fees and has the full Delphi source code available. Pay once and you’re done.
  • Works in almost every single version of Delphi you could possibly own right from Delphi 4 up to the present day. You also only need the Professional Edition and it works in the current Delphi Community Edition too.

Is there a database desktop equivalent for Absolute Database?

Not only is there an equivalent of the venerable Database Desktop but Component Ace also supply full source code for it. This app serves as a great tool to create, query and maintain databases based on the Absolute Database format but also is a great reference tool on how to access the data and perform various non-obvious functions like creating or repairing DB tables at runtime.

Absolute Database database manager
Absolute Database – Database Manager tool

Where to get the Absolute Database

You can find Absolute Database at Component Ace’s official website here:

What are the key features of Absolute Database and why is it like SQLite?

Like SQLite the Absolute Database stores all the tables in a single file. This file can reside either on your local hard drive or a network share. By default Absolute database files end with an “.abs” file extension although in reality they could be called anything you like.

The key Absolute Database features listed by Component Ace are as follows

  • No BDE; no DLLs
  • Single-file database
  • SQL’92 (DDL & DML) support
  • Compatible with standard and third-party database controls
  • Single-user and multi-user mode (file-server)
  • Works all versions of Windows and doesn’t require any updates or service packs
  • Ultra-fast in-memory tables
  • Unmatched ease-of-use
  • Strong encryption
  • BLOB compression
  • Free for personal use
  • Full source code available
  • Royalty-free distribution

Absolute Database avoids the DLL hell

For me the benefit was the lack of any form of additional drivers or files to deploy with the application while retaining full TDatasource compatibility. The database functionality is compiled right into your app and as far as I am concerned this has the added advantage of making your programs immune from what we used to call “DLL hell” where shared driver dlls and SDKs on the host computer were updated either by another application which used the same database SDK and dlls or by an upgrade or patch to the operating system.

With the “everything in your exe” approach used by Absolute Database the small amount of overhead of the compiled-in database access code is easily outweighed by the advantage of it meaning your application has bulletproof immunity from those kind of computer environmental catastrophes which still persist to this day for situations like apps which access MySQL; where things go inexplicably wrong when trying to connect to the MySQL server and after much hair-pulling and renting of clothes it transpires there is more than one “libmysql.dll” on the system and, furthermore, your 32bit app is trying to load a 64bit library or vice versa.

Absolute Database in the RAD Studio Delphi IDE
Absolute Database in the RAD Studio Delphi IDE

Try it for free

The personal, non multi-user version of Absolute Database is free for personal use so you don’t have to take my word for it (although Component Ace have no direct connection with me and have had no input on this article – my views are my own). Try comparing Absolute Database for yourself versus SQLite and see how you get along. If you want to take things further then you’ll probably have to pay for the commercial version which adds things like access to the full source code and the ability to run multi-user but by that time you, like me, might be wondering why it’s the best kept secret database on the internet.