Putting HSQLDB in server mode?

[MTP]

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?

[F3K Total]

Have you seen this tutorial ?
R

[DACM]

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:

Running a "split database" in a multi-user environment (MS Windows oriented steps with graphics)
Running a "split database" in a multi-user environment (Manual step-by-step version)
Database user log-in (some details of the Base log-in function)
Server Mode Startup, Shutdown and Authentication Options (Windows-specific discussion with wider application)
Client Computer Setup (when using 'server mode' with networked computers)

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:

mydb.server.odb
server.START.bat (Windows-specific batch file; requires path edit step 4 below)
server.STOP.bat (Windows-specific batch file; requires path edit step 4 below)
server.CHECKPOINT.bat (Windows-specific batch file; requires path edit step 4 below)
START.vbs (Windows-specific VBscript file; runs server.start.bat silently)
STOP.vbs (Windows-specific VBscript file; runs server.stop.bat silently)

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:

http://forum.openoffice.org/en/forum/viewtopic.php?p=278610#p278610
http://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=63554
...

[MTP]

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.

[Hagar Delest]

+1.
I'm really impressed by the level of details and the quality of the posts, especially with such complex topics!

[DACM]

...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!