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" ODB file created by the "New Database" wizard. This wizard uniquely 'embeds' the user-data files associated with HSQLDB 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 (Writer, Calc, etc) so consider saving all related documents and databases to a protected folder
utilizing the latest version history
Note the status bar below (shown at the bottom of the Base window) indicates an 'embedded database' is in use.
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. This Base 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. Existing "embedded databases" can be converted
to this "split database" configuration while maintaining seamless database access with Base or other front-ends
Note the status bar below indicates a split-database configuration.
It remains prudent to protect this (split database) 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
where you can run the database without fear of losing critical data (recommend SpiderOak
for personal encryption
and unlimited file-history
A split database can also be run in 'server mode' in support of multiple users (see the definitions, notes, and links below). Some users also export their Forms
which effectively hides the Base environment at runtime.
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 database projects including encrypted, multi-user (50+ simultaneous users) database-access 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 HSQLDB support with Base.
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 in terms of automatic engine start-up and shutdown with Base. The available Base templates
utilize this single-user configuration.'server mode'
employs HSQLDB in the standalone server/listener configuration requiring separate engine start-up/shutdown. This mode enables concurrent, multi-user, read/write access with role-based 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 (see the related links below). 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 portability 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. Cloud storage won't effect database performance because everything is run from your local disk; cloud storage is simply an automated backup and synchronization medium. Most cloud storage solutions include 'file history/versioning.' This feature is essential with databases and other critical documents, since you can recover previous versions of each file in the event of file-corruption/accidental deletion/etc. And while all of these services encrypt your files in-transit and on the cloud servers, only 'client-side encryption' can guarantee your data is secure from prying-eyes at the cloud server-farm or in response to a court-order or government-oversight. In response, we are now seeing the rapid introduction of cloud-backup services featuring 'client-side encryption.' This ensures that nobody can access the contents of your cloud-based files without access to your personal computer(s). Wuala and SpiderOak have implemented client-side encryption, while Boxcrypt is an add-on 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 non-concurrent 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 also 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 (preset; no coding necessary). The resulting macro-enhanced 'split HSQL database' templates or add-ins have eased split-database adoption, while largely supplanting the 'New database' wizard in Base, and effectively eliminating the need for a default database engine in Base. Simply download a macro-enhanced template, which instantly creates a new 'split HSQL database' utilizing 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 of a macro-derived session class path,
Note: Multi-user support is also possible through future macro automation (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:Server mode (multi-user) setup links: