The user-community revisited this problem with data corruption
in the Fall of 2010. The results are very encouraging. To begin, I should clarify that the data loss issue is not
attributed to the built-in HyperSQL database engine
(HSQLDB). We find no fault or instability associated with this third-party database engine. The fault lies entirely with Base, and is limited to the all-in-one "embedded database" file created by the "New Database" wizard. This wizard 'embeds' the HSQL-database files inside
the Base container file (.odb) where they become susceptible to corruption by Base
. And unfortunately, a similar propensity for file corruption
can impact all OOo/AOO/LibO users so consider saving all documents and databases to a protected folder
utilizing the latest version history
There is a fix, however, for Base...The data loss problem is easily overcome
because Base also supports a traditional "split database
" configuration using the 'Connect to an e
xisting database' wizard. In this configuration, the back-end
database files (Tables & Data) are maintained separately & exclusively by the database engine. So the database data is inherently safe
from corruption by Base. Problem solved.
And despite the wizard's name, a new database is generated, as needed, by the HSQLDB engine. The wizard is a bit daunting, so simply download a preset Base template
which automatically creates a split HSQL 2.3.x database within a portable folder.
It remains prudent to protect this folder using automated backup/file-history options as mentioned previously, because several front-end
components remain inside the Base (.odb) file including your Forms, Queries, Reports and Macros. And while you're not likely to encounter back-end
database corruption in this configuration, no system is fully immune...enough said. So for peace-of-mind, simply move (drag-&-drop) the split-database folder to your cloud backup folder
for personal encryption
and unlimited file-history
features) where you can run the database without fear of losing critical data.
This 'split-database' configuration also supports simultaneous database sharing among networked users (see: server mode
and the related daily CHECKPOINT considerations
). Some users also export their Forms
which effectively hides the Base environment at runtime. Legacy "embedded databases" can be converted
to this "split database" configuration while maintaining seamless database access with Base or other front-ends
You'll find additional resources including an automated wizard
under the "Related posts
" links below. But suffice to say that we encourage the use of HSQLDB with Base in a proper configuration, even over alternative engines, due to the tight-integration including seamless engine startup
. Additional benefits include unsurpassed: ease-of-setup, Base-community support, cross-platform and portability options. In fact, when paired with the latest HSQLDB 2.x
engine there's little reason to supplant HSQLDB
for small-to-medium size projects including encrypted, multi-user databases on a network (LAN) with role-based security. This is not to overlook the merits of other good options with Base such as H2
, SQL Server Express
, Oracle Database XE
, etc. If you have significant experience with these or need features not available with HSQLDB 2.x then your choice is clear. But rest assured, these are not
necessary to achieve a reliable
, even full-featured
, desktop-database with Base. And I think you'll find that the user-community is best primed for Base+HSQLDB support.
Here's a glossary of terms
associated with this topic. 'file mode'
runs HSQLDB "in-process
" with *Office for seamless database access by a single-user. The user-experience is similar to the default "embedded database
" configuration with no need to start/stop the engine manually.'server mode'
employs HSQLDB in the standalone server/listener configuration requiring separate engine start-up/shutdown. This mode enables concurrent, read/write access with user-security over a network. It also contributes to data durability
through additional layers of protection.'split database'
terminology is adopted to provide distinction from the Base default "embedded database" configuration. A split database
refers to the separation of files on disk; separate front-end (Base) files and back-end (database) files. So a portable split-database will consist of multiple files in a single folder
rather than 'embedded' within a single [zip-archive
] file. The status bar in Base will reflect "JDBC
" when running a split database configuration based on the bundled Java engine (HSQLDB).
Note: A split database can be run in 'file' or 'server' mode interchangeably. Server-mode supports multiple, concurrent users but is more difficult to setup and manage. File-mode is preferred in single-user environments due to the relative ease, including seamless engine management and database portability when using an available Base template (.odb). 'Class path'
Note: Microsoft has also adopted this 'split database' terminology as they recognized the need to separate MS Access databases into the respective components for a variety of reasons, including data-durability.
Note: The current templates are a boon for split-database portability. But to take it to the next level, consider installing LibreOffice Portable & JavaPortable to a cloud folder or perhaps a fast portable drive. This allows you to run your database application entirely from the portable folder without installation on the host computer.
Note: Cloud storage has come-of-age through synchronized folders such as we find with Dropbox, Google Drive, SkyDrive, Box, Wuala, SpiderOak and many more. This concept does not effect performance since everything is mirrored on your local disk. These automated backup solutions feature an invaluable 'file history/versioning' feature that's essential with databases and other critical documents; you can recover previous versions in the event of file-corruption. And while all of these services encrypt your files in-transit and while stored on the cloud servers, until recently there was no guarantee that your sensitive data was secure from prying-eyes at the server-farm or in response to government-level court orders. In response, we are now seeing the rapid introduction of 'client-side encryption' which ensures that nobody can access the contents of your cloud-based files without access to your computer(s). Wuala and SpiderOak have implemented client-side encryption, while Boxcrypt can be used to add this critical layer of encryption to most other popular cloud storage solutions.
I should emphasize that a cloud-sync'd folder is not appropriate for simultaneous read/write database sharing among multiple users, although coordinated (serial time-based) access is possible. Otherwise, concurrent internet access to your database involves running the HSQL engine in 'web server mode' and the appropriate 'https' protocol in the HSQLDB startup string. This is different than the 'hsql' protocol used for LAN database access. For web access, you'll generally need admin access to the web-server in order to setup HSQLDB and the necessary Java support. Many web hosting services already offer Java support in the form of Tomcat, Jetty, GlassFish, etc. Once setup, running HSQLDB in web-server mode allows concurrent internet access from Base or other front-ends. HSQLDB 2.x supports connection pooling and MVCC (multi-version concurrency control) so in theory, HSQLDB running within an adequate JVM (Java heap set to 1GB RAM or more; -Xmx1024m) provides a robust online database capable of supporting hundreds of simultaneous read/write users -- seamlessly using Base (*Office) for remote access. Realistically, web-server database deployment is the domain of IT professionals using MySQL, PostgreSQL, Firebird, SQL Server, Oracle, etc. combined with browser-based front-ends hosting Rich Internet Apps.
refers to the location of critical program components in Java environments. In database parlance the 'class path' refers to the location (folder path) of a JDBC driver
. Drivers are necessary to connect with any RDBMS engine including the built-in HSQLDB engine, SQLite, or otherwise.
Note: The user-community has traditionally relied on the 'global' class path settings in support of 'split HSQL databases.' This 'global' solution worked but it disabled support for legacy 'embedded databases' while risking inadvertent database upgrade and compatibility headaches -- effectively limiting the Base installation to a specific HSQLDB version while killing database portability. Recent user-community developments have eliminated use of the global class path setting, thereby eliminating the drawbacks. We now leverage 'session' class path settings accessible only through macros. The resulting macro-enhanced 'split HSQL database' templates or add-ins have eased split-database adoption, while largely supplanting the 'New database' wizard and even eliminating the need for a default database in Base. Simply download a macro-enhanced template and you'll instantly be using new 'split HSQL database' leveraging the latest HSQLDB engine in a fully-portable database package (folder).we now strongly discourage manual/global class path setup, in favor of adopting macro-enhanced templates for all single-user HSQL database needs with Base.
Given the advantages,
Note: Multi-user support is also possible through future templates, but for now it is necessary to revert to the manual/global class path setup process on each computer when supporting multiple users accessing a 'split HSQL database' running in 'server-mode.' This requirement will be relatively easy to eliminate as template development continues.'Data-source URL'
refers to the database connection settings. These settings are stored within the Base (.odb) file. We can setup the data-source URL with a macro or manually in Base (Edit > Database > Properties
). This somewhat-cryptic string of characters determines the database-access mode: file or server mode.
'File mode' is seamless, allowing us to click directly on a properly-configured Base file (.odb) for startup. Related links (must reads)
But 'server mode' requires separate HSQLDB startup for Table access. So it becomes a two-step process: (1) start HSQLDB in 'server mode' (2) and click mydb.server.odb to open the front-end in Base. This process is typically automated using a script/batch file. For example, the provided Windows script file ('START.vbs') automates all aspects of 'server mode' through a single mouse click. The associated Windows batch files (server.start.bat and server.stop.bat) will require some tweaking to reflect your actual folder-paths. This setup process is consolidated in a nice Windows system-tray app created by Greengiant224 found here.
(in particular the linked post) Split HSQL database setup instructions:Legacy database recovery and migration:Base limitations as a database manager:Slow database remedies:Multi-user setup links: