So why should I avoid 'embedded databases'?

So why should I avoid 'embedded databases'?

In Base parlance, an 'embedded database' is a single-file database. Specifically, this refers to the inclusion of both front-end and back-end database components within a single, zip-archive file. So with Base, an 'embedded database' is simply a zip-archive file (.odb) which includes files generated by OpenOffice as well as user-files generated and maintained by the bundled HSQLDB engine. The contents of this (.odb) file are automatically unzipped to a temporary folder upon opening the (.odb) file in *Office. When the file is "closed," the latest contents are repackaged into a single zip-archive by *Office. This re-packaging process is problematic and commonly leads to file-corruption, beyond the capabilities of the auto-recovery feature built-into *Office. The first signs of a corrupted Base .odb file is a popup dialog offering to open the file with the proper "filter" selection. In many cases the file is recoverable through tedious manual recovery. But much like recovering a crashed hard-drive, the process requires some expertise with file-recovery tools and knowledge of HSQLDB's automatic backup system.

So it's best to avoid these 'embedded database' files whenever possible. This means avoiding the wizard dubbed 'Create a new database' in Base. Thankfully, this particular wizard isn't necessary to create new HSQL databases with Base.

In fact, Base offers a robust alternative to leveraging the 'Connect to an existing database' wizard. The wizard connects Base to various data-sources in a traditional 'split database' architecture. In this configuration the database files remain separate. The Base (.odb) file, in this case, contains only front-end components (connection parameters, stored queries, forms, reports, and macros). The back-end database files are managed exclusively by their respective driver or database engine. Due to the separation of roles at the file-level, a Base crash cannot corrupt the back-end database files. This 'split' configuration is the normal Base configuration with PostgreSQL, MySQL, H2, etc. As expected, connecting Base with HSQLDB in this configuration also achieves the proper data-reliability. Everything we need to generate a 'split HSQL database' is included with Base. But manual setup is tedious, and the resulting database is scarcely portable. Things certainly don't get any easier with PostgreSQL, MySQL, etc. In response, the user-community developed macro-enhanced Base templates. These templates have become the best way to generate a new, full-featured, portable database using Base with the latest HSQLDB engine.