Putting HSQLDB in server mode?

Discuss the database features

Putting HSQLDB in server mode?

Postby MTP » Wed Dec 26, 2012 4:07 pm

I've been working on creating a database for my company for several months; I got it developed to the point I wanted to start actually using it and changed it from an embedded to a split database. I want a couple of other people to be able to have access to and modify the database. As this is a small project with just a few users (no more than 6) I did not upgrade HSQL, still using 1.8 that comes with Base.

Things seemed to work for about a day; when one of the other users modified the database in the morning, it corrupted all the subsequent additions I made that day. (The data file exploded from less than 1 MB to 800 MB and several of my tables became inaccessable due to "ran out of memory" errors.)

I'm guessing my problem is that I didn't change HSQL into server mode? I've been searching for directions on how to do this, but they all seem to be on oooforum.org, which times out every time I try to load a page from that site. Is there anything on this forum or otherwise non-oooforum.org sites that could direct me here?
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Putting HSQLDB in server mode?

Postby F3K Total » Wed Dec 26, 2012 9:55 pm

Have you seen this tutorial ?
R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 896
Joined: Fri Dec 16, 2011 8:20 pm

Re: Putting HSQLDB in server mode?

Postby DACM » Wed Dec 26, 2012 10:18 pm

MTP wrote:I'm guessing my problem is that I didn't change HSQL into server mode? I've been searching for directions on how to do this, but they all seem to be on oooforum.org, which times out every time I try to load a page from that site. Is there anything on this forum or otherwise non-oooforum.org sites that could direct me here?

Yes, you must run in 'server mode' in order to provide multiple users with simultaneous (concurrent) read/write access to your database. Otherwise, you can expect data corruption as the host operating system attempts to lock the file upon multi-user access with limited success.

Yes, the knowledge-base on oooforum.org is increasingly inaccessible. To that end, I've begun transferring the raw steps associated with 'file' and 'server' modes (of HSQLDB) to the Apache Wiki (http://wiki.openoffice.org/wiki/FAQ_(Base)#HSQLDB). Unfortunately, it appears we still have a link within the server mode steps that references the latent oooforum.org forum. I'll work to eliminate those dependencies, but in the meantime perhaps read as much as you can on available links and steps, trying links to the oooforum.org forum at different times throughout the day (and print PDF's of any links that become accessible). As F3K Total mentioned, there is considerable information on this forum pertaining to server mode as well. Perhaps pay special attention to the links at the bottom of the referenced tutorial:
Multi-user setup links:


You may find these files helpful in the process of setting-up the default HSQLDB 1.8.0.10 or HSQLDB 2.x in server mode:

You'll need to extract the 'database' files from your existing 'embedded database' file (.odb), and then run the resulting 'split database' in server-mode:
    1. Create a dedicated database folder and place all files (above; do not rename) in that folder perhaps also including a copy of your embedded database file.
    2. Setup split-database support in Base using the associated (HSQLDB) Apache Wiki tutorial steps.
    3. Make a copy of your embedded database file (.odb) for safe-keeping.
      3a. Append the copy with a .zip extension (unhide file extensions), or otherwise open the file with a zip archive tool (Windows 7 file manager, 7-zip, etc.).
      3b. Explore the 'database' folder within the archive to find the HSQLDB database files: data, script, properties and the optional backup.
      3c. Extract these four files to your database folder created in step 1.
      3d. Rename these files precisely: mydb.data, mydb.script, mydb.properties, mydb.backup.
    4. Edit mydb.START.bat, mydb.STOP.bat AND mydb.CHECKPOINT.bat to reflect the path to hsqldb.jar as follows:
      Code: Select all   Expand viewCollapse view
      @echo off
      REM Place this file in your 'database' subfolder

      REM Set hsqldb=2 below when running HSQLDB 2.x, and set the jarpath under :CONTINUE below to reflect the full path to hsqldb.jar
      set hsqldb=1

      REM Edit javapath= below to reflect the sub-path of your Java.exe file...
      REM Edit jarpath= below to reflect the sub-path of your hsqldb.jar file...
      set javapath=Java\jre7\bin\Java.exe
      set jarpath=OpenOffice 4\program\classes\hsqldb.jar
      REM set jarpath=OpenOffice.org 3\Basis\program\classes\hsqldb.jar
      REM set jarpath=LibreOffice 4\program\classes\hsqldb.jar

      ...
    5. On Windows, click START.vbs (or click server.START.bat to open a console window in order to confirm server start-up or to report any errors).
    6. Open your current embedded database file (.odb) and mydb.server.odb in two separate instances of Base.
    7. Verify Table access from mydb.server.odb by clicking the Tables icon in Base.
      NOTE: If you run into problems running HSQLDB in server mode, feel free to run HSQLDB in 'file mode' to complete the conversion process. Perform the following sub-steps (7a-7d) only if you wish to switch to 'file mode' at this point in the process:
        7a. To run HSQLDB in file mode using Base, simply download and open this Base file: mydb.file.odb
        7b. The 'data-source URL' in mydb.file.odb specifies/creates the database path and name: \users\public\databases\mydb\mydb.*
        7c. You'll need to move the database files (step 3 above) to this 'mydb' folder, or adjust the 'data-source URL' to reflect your own folder path.
        7d. File mode is single-user, but once you get HSQLDB running in server mode you can switch modes using the data-source URL setting found in mydb.server.odb.
    8. Drag&drop all Queries, Forms and Reports (not the Tables) from your (.odb) file to mydb.server.odb (or applicable .odb if performing the conversion in file-mode).
    9. To transfer embedded Macros, simply Copy&Paste the associated text between instances of Base within the Macro development environment.
    10. On Windows, click STOP.vbs (or click server.STOP.bat to open a console window in order to confirm server shutdown or to report any errors).
As you can see, you'll need to manage HSQLDB in server-mode by starting and stopping the server manually (steps 5 & 10 above). Be careful not to start multiple instances of the HSQLDB server! On the other hand, feel free to click STOP.vbs to run 'server.STOP.bat' multiple times just to make sure the server has been stopped between sessions/days/periodically. Make absolutely sure the 'path' to hsqldb.jar is correct in both server.START.bat, server.STOP.bat and server.CHECKPOINT.bat as outlined in step 4. This meticulous start/stop/checkpoint management of the HSQLDB server will be especially important after you eliminate ";shutdown=true" from the batch file (server.START.bat), as necessary for deployment into multi-user environments where the host machine is used to run Base; otherwise, closing Base will shutdown the HSQLDB server with ";shutdown=true" present. See also Greengiant224's Win32 app for managing start-up & shutdown of HSQLDB server mode.

Perhaps look over these troubleshooting threads:
Last edited by DACM on Thu May 15, 2014 8:48 pm, edited 16 times in total.
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
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Putting HSQLDB in server mode?

Postby MTP » Thu Dec 27, 2012 6:15 pm

It may be a couple of days before I can work through everything to try this out, but I just wanted to stop in and say thank you so much for this detailed response! It covers all my questions plus extra ones I didn't even know to ask.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Putting HSQLDB in server mode?

Postby Hagar Delest » Thu Dec 27, 2012 10:10 pm

+1.
I'm really impressed by the level of details and the quality of the posts, especially with such complex topics!
:super:
AOO 4.1.6 on Xubuntu 18.10 and 4.1.5 on Windows 7 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28258
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Putting HSQLDB in server mode?

Postby DACM » Sat Dec 29, 2012 10:27 am

MTP wrote:...this detailed response! It covers all my questions plus extra ones I didn't even know to ask.

You're certainly welcome!

And thank you MTP, because I didn't realize just how sparse the details were on server-mode setup -- without relying on links to the oooforum.org site. Your discovery warranted a rather detailed response.

And thank you Hagar Delest for the encouraging words!
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
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am


Return to Base

Who is online

Users browsing this forum: No registered users and 2 guests