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
features; Windows 8 includes a built-in "History" feature which generates historical copies of selected files on a user-defined schedule.
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. It remains prudent to protect the database folder (data files + .odb file) with automated backup/versioning software 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 truly immune...enough said. This configuration also supports simultaneous database sharing among networked users. Some users also export their Forms
which effectively hides the Base environment at runtime. Legacy "embedded databases" can be ported
to this "split database" configuration while maintaining seamless database access with Base or other front-end
You'll find additional resources including an automated wizard
and manual setup instructions
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 multi-user, read/write access with user-security over a network. It also contributes to data durability
through additional layers of protection, while easing database portability to a degree.“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 split database will consist of multiple files in a single folder
rather than 'embedded' within a single [zip-container] file. A split database can be run in file/server modes interchangeably. The status bar in Base will reflect "JDBC
" when running a split database configuration based on the bundled Java engine (HSQLDB).
Note that each computer requiring "split database" access must be configured accordingly. Simply follow the manual steps.“Data-source URL”
Ultimately, for maximum portability on the Windows platform, simply install LibreOffice Portable & JavaPortable to a USB thumb-drive or cloud folder and add this 'split database' support to the portable folder. This allows you to run your database application entirely from the portable folder without installation on the host computer.
NOTE: Dropbox no longer saves file version history in their free accounts, so a free Dropbox account is no longer recommended or appropriate for database storage. Wuala and SugarSync are now recommended. Furthermore, a cloud-based sync-folder is not appropriate for simultaneous read/write database sharing among users, although coordinated (serial time-based) access is possible. Otherwise, concurrent internet access to your database involves 'web server mode' and the appropriate 'https' protocol in the HSQLDB startup string. This is different than the 'hsql' protocol used for LAN databse 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. Realistically, web-server database deployment is the domain of MySQL, PostgreSQL, Firebird, SQL Server, Oracle, etc. combined with browser-based front-ends hosting Rich Internet Apps.
refers to the database connection settings. These are setup in Base (E
dit > Datab
ase > P
roperties) and stored in the individual Base (.odb) file. This somewhat-cryptic string of characters determines the database-access mode (file or server mode...in this case). Please skim the following notes concerning the data-source URL setting:
The Quick Fix includes two, pre-configured, Base front-end files (.odb). One is configured for 'file mode' access and the other for 'server mode' access, differing only by their respective data-source URL. You'll initially choose one or the other mode (file/server) for development. You can always switch modes by changing the 'Data-source URL' of your .odb file -- thereby preserving your Forms, Reports, Queries or Macros. Either mode provides access to your "split database" files. To switch modes, simply open both provided .odb files in separate instances of Base, and proceed to copy&paste the selected data-source URL string as appropriate: Edit > Database > Properties. This copy&paste procedure is recommended because the provided data-source URL's reflect community expertise and best practices developed over a number of years.'Class path'
'File mode' is seamless, allowing us to click directly on the Base (mydb.file.odb) file for startup. 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 should be 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. But if you're using LibO or non-default folder-paths for OOo or Java, then the associated Windows batch files (server.start.bat and server.stop.bat) will require some tweaking to reflect your folder-paths. And if you don't mind this one-time setup of folder-paths, I think you'll really like/prefer the Windows system-tray app created by Greengiant224 found here.
The Data-source URL can affect database portability. When run in 'file mode,' this URL reflects the database folder-path explicitly, so moving the database folder will require corresponding adjustments to the folder-path in the Data-source URL. On the other hand, when running the same database in 'server mode,' the Data-source URL does not include the folder-path of the database. The 'server mode' start-up string is "relative" to the folder in which it is run, so we can theoretically move the database folder at will when running in 'server mode.' Now, keep in mind that the provided Windows batch files (.bat files included in the database-folder) assume default installation paths for Base and Java, so these 'server mode' batch files might require modification when moving the database folder to another computer with different installation paths for either OOo/AOO/LibO, or Java.
is a global setting in AOO/LibO used to record the location (folder path) of JDBC driver
files. Drivers are necessary to connect with any RDBMS engine including the built-in HSQLDB engine, SQLite, or otherwise. But only JDBC drivers are referenced by this Class Path setting.
We must setup the 'Class path' to the associated JDBC driver to support 'split' (non-embedded) HSQL databases in AOO/LibO. This setting is found in AOO/LibO under: Tools > Options... > *Office > Java > Class Path... > Add Archive... Related links (must reads)
Upon pressing 'Add Archive...' you'll need to locate the 'hsqldb.jar' file stored within your AOO/LibO installation (example MS Windows installation paths):
- Code: Select all Expand viewCollapse view
C:\Program Files(x86)\OpenOffice.org 3.4\Basis\program\classes\hsqldb.jar
C:\Program Files(x86)\LibreOffice 3.5\program\classes\hsqldb.jar
Yes, as ridiculous as it sounds, AOO/LibO doesn't know the location ('Class path') of the JDBC driver for the built-in HSQL database engine. This situation could be remedied today by AOO/LibO developers by adding a single line to the 'fundamental[basis].ini' configuration file that's installed with AOO/LibO. For example:
- Code: Select all Expand viewCollapse view
Otherwise, this tiny omission in the Base defaults creates some work on our part, as we pursue a stable, seamless configuration with Base. And the work is ongoing because you'll need to repeat this 'Class Path' setup process (using Tools > Options... ) on every computer used to access your split HSQL databases. Plus, you'll need to repeat this setup process each time you upgrade your installation(s) of AOO/LibO.
JAR files are simply zip-container files that contain Java code. Naturally, a JDBC driver (written in Java) is used to access a particular Java database engine such as HSQLDB, H2 or Apache Derby. ODBC can also be "cross-platform," but only if the driver is re-written for each platform. JDBC uses the same file/code on all platforms. In any case, most mainstream database engines include both ODBC and JDBC drivers (such as PostgreSQL, MySQL/MariaDB, Firebird, Oracle and MS SQL Server). By the same token, some Java database engines offer a native ODBC driver option. For instance, H2 is compatible with the official PostgreSQL ODBC driver.
IMPORTANT NOTE: if you're setting this 'Class path' as part of upgrading to HSQLDB 2.x, this setting will effectively disable support for 'embedded database' files, simply because the two drivers have the same name (hsqldb.jar). You can always delete this setting to regain all-in-one database support, but consider installing both AOO and LibO while setting up one for HSQLDB 2.x support, and leaving the other in it's default state for HSQLDB 1.8 'embedded database' support.
(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: