[Tutorial] Splitting an "embedded HSQL database"

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: 1138
Joined: Tue Nov 03, 2009 7:24 am

[Tutorial] Splitting an "embedded HSQL database"

Post by DACM »

Convert an existing Base .odb file, containing an "embedded HSQL 1.8 database", into a reliable and upgradeable "split database":
  • NOTE: If you're starting a new database project with an empty database, then consider a split-database template which effectively replaces the 'New Database' wizard in Base, allowing you to bypass this this conversion process.
  • A. Ensure global macro security in *Office (OpenOffice / LibreOffice) is set to Medium (or Low):
    • Tools (*Office) > Options (Preferences) > *Office > Security > Macro Security > Medium
    B. Remove any global Class Path to hsqldb.jar that you may have setup manually in *Office:
    • Tools (*Office) > Options (Preferences) > *Office > Java/Advanced > Class Path > Remove
    • NOTE: Steps A & B above are necessary to allow the provided macro/extension to manage the connection between Base (.odb) and your database in a split database configuration. Specifically, the macro derives the necessary connection settings based on the location of the HSQL database engine/driver (hsqldb.jar), and then stores those settings within the Base (.odb) file. This means you can move your split-database folder as desired, or you may choose to disable this macro-automation after initial setup if you don't plan to move or rename your split-database folder. Feel free to rename your Base front-end file (.odb) in any case.

      NOTE: The 'global' Class Path setting in step B is blank by default, but it may have been populated manually for a few reasons: (1) legacy split-database support required a 'global' class path prior to the advent of our current macro-derived 'session' class path, or (2) a 'global' class path remains necessary for 'server mode' database operation in support of multiple concurrent users. A 'global' Class Path overrides a 'session' Class Path derived by macro, so unless you're running HSQLDB in 'server mode' it's imperative that you avoid manual/global Class Path usage in Base, particularly as we adopt HSQLDB 2.3.x.

      NOTE: We can take it from here... ;) Several forum members can perform the remaining steps for you in a matter of minutes. Simply post a sanitized copy of your .odb file (containing your 'embedded database') as a new topic in the Base forum. Files under 128KB can be posted directly to the forum, while larger files will require a [free] cloud file-distribution service with 'direct file linking' such as Mediafire, FileToLink, Dropbox or similar. Or we can accommodate more discrete file exchange through the forum's Private Messaging service. If you don't have a preference, we'll upgrade the database to HSQLDB 2.3.x and employ a 'portable' split-database template as outlined in OPTION 1 below. We can return the working database to you as a single zip-archive file(.zip) or executable (.exe) containing your split-database folder.
    C. Create a new folder on your computer to serve as a dedicated split-database folder (one per database)
    D. Add a copy of your current Base .odb file to this new split-database folder
    E. Migrate your existing Tables (database records) through file extraction:
    • a. Open the Base .odb file with a zip file-extraction tool and locate the 'database' subfolder containing the files: script, properties, data, and backup.
      • NOTE: It may be necessary to un-hide file extensions, and perhaps rename this .odb file with a .zip extension, as necessary. Don't forget to replace the original .odb file extension following the extraction step below.
      b. Extract (un-zip) the database files (script, properties, data, and backup) to the split-database folder (created in Step C).
      c. Rename these newly-extracted files with a common prefix such as: mydb.script, mydb.data, mydb.properties, and if present mydb.backup.
    Now you have a couple of options:

    OPTION 1: For a fully-portable, self-contained, split-database folder...
    • 1. Add mydb_wizard.odb and hsqldb.jar to your split-database folder
      • NOTE: mydb_wizard.odb is a typical Base file (international standard front-end database container file) generated by the Base wizard: 'Connect to an existing database.' But in this case we've included a macro that automatically connects with hsqldb.jar (HSQLDB engine) when co-located in the same folder. This effectively makes your split-database folder portable, because you can move the folder or utilize cloud folder storage, and the macro handles the necessary database-engine connection seamlessly (single-user, non-concurrent, database access). This also ensures engine/driver compatibility even if you subsequently upgrade your individual split-database to HSQLDB 2.x. In other words, by placing mydb_wizard.odb and hsqldb.jar in the same folder, with macros enabled, you effectively get a self-contained, portable, split-database folder. This allows you to maintain any number of portable split-databases, each running it's own dedicated version of HSQLDB, while optionally running all your databases simultaneously, even alongside default "embedded HSQL 1.8 databases" as necessary.

        NOTE: hsqldb.jar is an internally-licensed copy of HSQLDB As you may know, this SQL engine/driver is used by Base to support 'embedded HSQL databases.' This file (hsqldb.jar) is actually a ZIP-archive (container file), but with a .jar extension, as necessary to comply with JAVA program distribution standards. If your download manager identifies hsqldb.jar as a ZIP-archive, it might add a .zip extension automatically to the downloaded file (hsqldb.jar.zip). In this case you'll need to eliminate the added .zip extension for proper operation of the database engine. In other words, make sure the file name is simply hsqldb.jar or you'll encounter database connection problems.

        NOTE: You can substitute the latest HSQLDB 2.x engine for an automatic database upgrade. But please note, the automatic upgrade process is not always 100% trouble-free, so please ensure proper split-database function using the default HSQLDB engine (hsqldb.jar bundled with *Office) before before attempting an engine upgrade. When you're ready to upgrade, make a backup copy of your working split-database folder. Then proceed to replace the legacy hsqldb.jar file within your split-database folder with the latest version. For your convenience, simply download these internally-licensed HSQLDB 2.3.2 files: hsqldb.jar and sqltool.jar. The database will be upgraded automatically upon clicking the Tables icon in Base. And since HSQLDB 2.3 includes a fix for the AutoValue bug in Base, you should also add these settings to mydb_wizard.odb to enable AutoValue setup through the Base Table Designer GUI. If you maintain multiple databases, keep in mind that each split-database folder will require its own, dedicated copy of the HSQL engine (hsqldb.jar).
      2. Open mydb_wizard.odb in Base while selecting Enable Macros
      • NOTE: The integral macro-wizard runs for a split-second each time you open the .odb in support of database-folder portability. It quietly configures the .odb file for split-database access, as necessary. If you encounter a 'new database' popup dialog, or a popup concerning hsqldb.jar, then we've missed a step above. You should see your existing tables upon clicking the Tables icon in Base. This Base (.odb) front-end file will also be used to store your Queries, Forms, Reports and Macros (see step 3 below).

        NOTE: This .odb file, along with your extracted HSQL database files, and hsqldb.jar engine constitute your split-database. You can rename the .odb file in any case, but renaming or moving the split-database folder requires macro support as enabled in Step A (above). With macro support, you can share a database non-concurrently in a cloud folder (Dropbox, etc) or even zip-archive the entire split-database folder for electronic distribution as a single-file. This Base (.odb) file must remain inside the folder to provide these portability features. You can register this .odb file in *Office as a 'data source' (Tools > Options > Base > Databases) for seamless access from any Writer or Calc document (F4) including standalone forms. You can also maintain a desktop shortcut to this file and/or folder as desired.

        NOTE: Consider moving the entire split-database folder into a cloud folder (such as Dropbox, SpiderOak or Google Drive) to gain automated session backup with file-history and client-side 'personal' encryption, not to mention non-concurrent access to the database.

        NOTE: With mydb_wizrd.odb open in Base, your status bar at the bottom of the Base window should read: JDBC | hsqldb:file:///...

        NOTE: In a non-macro environment, you can safely bypass or disable the macro if you never plan to subsequently move or rename your split-database files or folder.

        NOTE: The associated macro is limited to single-user (non-concurrent) operation. However, a split-database folder may also be run in 'server mode' in support of concurrent read/write database access (multi-user), but this macro must be disabled or perhaps modified accordingly.
      3. Migrate your front-end components through drag-&-drop
      • NOTE: Alternatively consider step 3x below which effectively converts your existing Base .odb file for use as the front-end to your split database.

        a. With mydb_wizrd.odb open in Base, open a copy of your "embedded database" in another instance of Base.
        b. Proceed to drag-&-drop everything except the Tables into the new mydb_wizard.odb file.
        • For instance, click on a Query and drag it from your "embedded database" file to the Query icon in mydb_wizard.odb.
          Likewise, drag-&-drop each Form and Report to their respective icons.
          Macros may be copied-&-pasted as a whole, or exported/imported as a Library file.
          You're all done! (Skip step 3x below, with successful drag-&-drop migration of your front-end components)
        • 3x. Alternatively, you can bypass the drag-&-drop process in step 3 above by adding the following macro code to your existing "embedded database" .odb file, while setting the macro to auto-run as follows:
          • a. Open your .odb and press Alt-F11,
            b. then select your .odb by name in Basic Macros (CAUTION: DO NOT add this macro to the global 'My Macros' location),
            c. then click: New (button) > New Module, then paste this code (select all, copy & paste) into the Basic code window,
            d. then set to auto-run: Tools > Customize > Events > Open Document > Assign Macro > "Setup"
            e. You should also employ these AutoValue settings when employing HSQLDB 2.3 or newer
          • Code: Select all

            REM  *****  BASIC  *****
            'Instructions for use:
            ' (1) Macro security must be set to Medium (or Low) in: Tools > Options > *Office > Security > Macro Security > Medium
            ' (2) Remove any Class Path to hsqldb.jar if setup in: Tools > Options > *Office > Java/Advanced > Class Path > Remove
            ' (3) Place this .odb file in a dedicated (empty) database folder
            ' (4) Add a copy of the HSQLDB engine (hsqldb.jar) to the database folder
            ' (5) Add this macro to your .odb file and set it to autorun as follows:
            '      (5a) Open your .odb and press Alt-F11, 
            '      (5b) then select your .odb by name in Basic Macros (CAUTION: DO NOT add this macro to the global 'My Macros' location), 
            '      (5c) then click: New (button) > New Module, then paste this code into the Basic code window, 
            '      (5d) then set to auto-run: Tools > Customize > Events > Open Document > Assign Macro > "Setup"
            Sub Setup 'Tools > Customize > Events > Open Document > Assign Macro > "Setup"
              On Error Goto ErrorHandler
              'get the current path to this .odb file
               sPathURL = ThisDatabaseDocument.URL
               sPath = ConvertFromURL(sPathURL)
               sName = ThisDatabaseDocument.Title
               iLen = InStr(sPath, sName)
               sPath = Left(sPath, iLen-1)
              'setup Class Path
               sClassPath = sPath & "hsqldb.jar"
               If Not FileExists(sClassPath) Then 
                  sLine1 = "Please add a copy of the HSQLDB engine (hsqldb.jar) to the current folder :  "
                  sLine2 = "NOTE:  This is necessary for proper wizard function, but additional benefits include : " 
                  sLine3 = "* enhanced portability of the database-folder"
                  sLine4 = "* ensures database compatibility across computers and *Office installations"
                  sLine5 = "* guards against inadvertent upgrade of your database since the results are uncertain and irreversible"
                  sLine6 = "* hsqldb 2.x provides a built-in database management GUI accessible by clicking hsqldb.jar."
                  iButton = MsgBox (chr(13) & sLine1 & chr(13) & chr(13) & sPath & chr(13) & chr(13) & sLine2 & _
                  chr(13) & sLine3 & chr(13) & sLine4 & chr(13) & sLine5 & chr(13) & sLine6, 18, "hsqldb.jar not found")
                  If iButton = 3 Then Exit Sub 'ThisDatabaseDocument.close(True)
                  If iButton = 4 Then Goto ClassPath
               End If
               sClassPath = ConvertToURL(sClassPath)
               ThisDatabaseDocument.DataSource.Settings.JavaDriverClassPath = sClassPath
              'get the HSQL database name from the current folder
               NextFile = Dir(sPath, 0)
               While NextFile <> ""
                  If (Right(NextFile, 7) = ".script") Then dbName = (Left(NextFile, Len(NextFile)-7))
                  NextFile = Dir
               If dbName = Empty Then 
                  sLine1 = "Optionally provide a name for your back-end data files. "
                  sLine2 = "NOTE: The particular name is not important. The default below will suffice. "
                  dbName = InputBox(sLine1 & chr(13) & chr(13) & sLine2, "Create a new database    *   JDBC  |  HSQL database engine  |  non-embedded data files   *", "mydb")
                  If dbName = "" Then dbName = "mydb"
               End If
              'check for existing database
               sLine1 = "A new database will be created in the current folder:  "
               sLine2 = "NOTE: This folder constitutes your ""database."" "
               sLine3 = "A dedicated database folder is fully portable, and as such it may be renamed or moved as desired."
               sLine4 = "NOTE: This Base front-end file (" & sName & ") must remain in this database folder.  "
               sLine5 = "You may rename this file as desired, but do maintain the .odb extension if visible. Create a desktop shortcut to this file as desired."
               sLine6 = "NOTE: The back-end HSQL data files will be named: " & dbName & ".*  "
               sLine7 = "These files must also remain in this database folder."
               If Not FileExists(sPath & dbName & ".script") Then MsgBox sLine1 & chr(13)_
                  & sPath & chr(13) & chr(13) & sLine2 & sLine3 _
                  & chr(13) & chr(13) & sLine4 & sLine5 _
                  & chr(13) & chr(13) & sLine6 & sLine7 _
                  , 64, "Please Read"
              'setup Data Source URL
               sURL_prefix = "jdbc:hsqldb:"
               sURL_args = ";default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false"
               sURL = sURL_prefix & "file:///" & sPath & dbName & sURL_args
               ThisDatabaseDocument.DataSource.URL = sURL
               ThisDatabaseDocument.DataSource.Settings.JavaDriverClass = "org.hsqldb.jdbcDriver"
            '   ThisDatabaseDocument.DataSource.User = "SA"
               Exit Sub
               MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & ")", 16, "Setup: macro code error"
            End Sub
          • NOTE: This same macro is embedded within mydb_wizard.odb as downloadable in step 1 above.
          • NOTE: Notice the status bar at the bottom of your Base window upon opening your macro-enhanced Base (.odb) file open in Base. If the status bar says: Embedded database | HSQL database engine, then we've missed a step above.
    OPTION 2: Install the split-database support as a local extension to Base, and then use the extension to convert your existing .odb to a suitable front-end for your split-database...
    • 1. Reference these steps by Villeroy...
      • NOTE: This option is similar to Option 1 > step 3x above, in that it effectively converts your existing Base (.odb) file for use as the front-end to your split-database. This option may be easier for some users than step 3x above, or otherwise performing drag-&-drop between Base files as outlined in the parent step 3 above. However unlike Option 1, Option 2 does NOT produce an inherently 'portable' database. In other words, each time you move your database you'll need to rerun the extension. If you need to access the database from multiple computers using a cloud-sync'd folder (Dropbox, Google Drive, etc.), then you'll need to rerun the extension each time you switch computers. In any case, these steps are NOT necessary with a fully-portable split-database. So if you find yourself moving the database regularly, or utilizing a cloud-sync'd folder for automated backups and non-concurrent access from multiple computers...then simply make a backup copy of your entire split database folder, and proceed to add a copy of hsqldb.jar to your split-database folder, while adding the above macro to your Base .odb file as outlined in Option 1 > step 3x above. This will eliminate reliance on the extension in portable computing scenarios.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
Posts: 31279
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] Splitting an "embedded HSQL database"

Post by Villeroy »

DACM wrote: Option 2 does NOT produce an inherently 'portable' database. In other words, each time you move your database you'll need to rerun the extension.
I added an auto-installer module to my macro extension and another module to extract forms to stand-alone documents.
Topic with description and attached extension: viewtopic.php?f=21&t=77543
I had a first success with stand-alone Writer reports: viewtopic.php?f=29&t=81229 (to be continued)
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
Posts: 1
Joined: Wed Mar 07, 2018 9:04 am

Re: [Tutorial] Splitting an "embedded HSQL database"

Post by axramos »


I lost three days of my work for using the database included in LibreOffice... this procedure successfully fixed the problem and now I am working for a full month now without any problems!!!
LibreOffice 6.0.2 on MacOS 10.11.6
Post Reply