[Tutorial] Avoiding data loss with built in HSQLDB

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
User avatar
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

[Tutorial] Avoiding data loss with built in HSQLDB

Post by r4zoli »

For .odb files with embedded HSQLDB database.

When you use Base as front end to real database (MySQL, postgreSQL, Firebird etc.) then database server keeps data.

From usual posts on data loss issue:
My problem is that Base keeps crashing on me and, although I frequently save manually and have it set to autosave every 15 minutes, the auto-recover facility "recovers" my database back to a stage I had it at many hours ago. Yesterday I lost a full days work and tonight the same thing has happened again.
OOo Base works when opens odb file with built in HSQLDB, first unpack it into memory, start HSQLDB server load data file into database server, when you run a query it runs always in memory, data not saved until you save it manually, and if you saved into disks, your data in memory if you change again or crash happens, may be auto recovery helps. Auto save, backup not working with odb files.
Low on memory
It is a real problem, OOo Base open all data into memory and if it is low, crash can happens when database size reach memory size, and it cannot control.
When you add new items as queries, forms or reports all things require space in memory, and in disk accordingly.

To lower risks on data loss it is good to reduce the size of a Base database when you invested enough work into.
To do this, choose Tools -> SQL, execute the SHUTDOWN COMPACT command, close file and open back.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

[Tutorial] Avoid data loss by avoiding "Embedded databases"

Post by DACM »

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 features.
  • 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 existing 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 features).

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, PostgreSQL, MySQL/MariaDB, Firebird, 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).

    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.
'Class path' 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).

    Given the advantages of a macro-derived session class path,
    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.

    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.

    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.

Related links (must reads) (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:
Base window status bar when accessing a Split HSQL database
Base window status bar when accessing a Split HSQL database
Split_HSQL_database.jpg (8.58 KiB) Viewed 62308 times
Base window status bar when accessing an Embedded HSQL database
Base window status bar when accessing an Embedded HSQL database
Embedded_HSQL_database.jpg (5.11 KiB) Viewed 62308 times
User avatar
Posts: 31220
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] Avoiding data loss with built in HSQLDB

Post by Villeroy »

For completeness, a screenshot of a database document accessing a HSQLDB in server mode. The connection URL starts with jdbc:hsqldb:hsql:host/db_name instead of jdbc:hsqldb:file:path/file_name.
HSQL_Server.png (6.64 KiB) Viewed 12622 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply

Return to “Base”