[Solved] Put HSQLDB in server mode

Discuss the database features
Locked
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

[Solved] Put HSQLDB in server mode

Post by 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?
Last edited by MrProgrammer on Mon Jul 10, 2023 7:24 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: Putting HSQLDB in server mode?

Post by F3K Total »

Have you seen this tutorial ?
R
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Putting HSQLDB in server mode?

Post by DACM »

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

      @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
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Putting HSQLDB in server mode?

Post by 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.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
Hagar Delest
Moderator
Posts: 32628
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Putting HSQLDB in server mode?

Post by Hagar Delest »

+1.
I'm really impressed by the level of details and the quality of the posts, especially with such complex topics!
:super:
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Putting HSQLDB in server mode?

Post by DACM »

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
calc-learner
Posts: 40
Joined: Fri Oct 10, 2014 10:14 am

Re: Putting HSQLDB in server mode?

Post by calc-learner »

Are there any additional steps in Windows 10 or 11?

Also, does it matter what JRE version is used?

Before the pandemic, I was running this on Windows 7, but the server had a hard drive issue, so had to get a new one. And since then, we have been unable to get the database up and running.

Things I've checked:
  • Edited server.START.bat, server.STOP.bat, server.CHECKPOINT.bat to have the correct javapath and jarpath;
    Selected the same Java in Java options;
    Set the Class Path archive and folder in options.
Unfortunately, when I run server.START.bat, and the mydb.server.odb file loads in Base, clicking on tables gives an "socket creation error", SQL Status:08000, Error code: -80

So I'm wondering if there are any steps I'm overlooking that are necessary under Windows 10, given the additional security features.

TIA
Apache OpenOffice 4.1.2 Windows 10 Enterprise
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Putting HSQLDB in server mode?

Post by Villeroy »

Topmost problem: WIndows can not (does not want to) run Java programs as a service which is why we need those scripts on behalf of a logged-in user. The logged-in user needs to create sockets for the network connections. The problem of "socket could not be opened" might be due to missing priviledges of the logged-in user. Try to run the script with admin priviledges.
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
calc-learner
Posts: 40
Joined: Fri Oct 10, 2014 10:14 am

Re: Putting HSQLDB in server mode?

Post by calc-learner »

Villeroy wrote: Thu Jun 29, 2023 3:23 pm Topmost problem: WIndows can not (does not want to) run Java programs as a service which is why we need those scripts on behalf of a logged-in user. The logged-in user needs to create sockets for the network connections. The problem of "socket could not be opened" might be due to missing priviledges of the logged-in user. Try to run the script with admin priviledges.
That makes sense. Additional steps I've tried:
  • Set sbase.exe and java.exe to always run as Administrator;
    Right-clicked server.START.bat and ran as Administrator(while logged into a user with Admin privileges);
Still getting the same error when selecting the Tables in Base.
Apache OpenOffice 4.1.2 Windows 10 Enterprise
calc-learner
Posts: 40
Joined: Fri Oct 10, 2014 10:14 am

Re: Putting HSQLDB in server mode?

Post by calc-learner »

Update: In addition to the above steps, I changed the server.START.bat file to manually set the javapath and jarpath and removed all other dynamic arguments. For example:

Code: Select all

set javapath=C:\Program Files (x86)\Java\jre1.8.0_191\bin\java.exe
set jarpath=C:\... :: COMMENT: Same as above, just make sure this points to the right path in your Open Office directory containing the hsqldb.jar file.

REM set jarpath=OpenOffice.org 3\Basis\program\classes\hsqldb.jar
REM set jarpath=LibreOffice 4\program\classes\hsqldb.jar

REM IF EXIST "%PROGRAMFILES(X86)%" GOTO Win64

REM :Win32
REM set javapath=%PROGRAMFILES%\%javapath%
REM set jarpath=%PROGRAMFILES%\%jarpath%
REM GOTO CONTINUE

REM :Win64
REM set javapath=%PROGRAMFILES(X86)%\%javapath%
REM set jarpath=%PROGRAMFILES(X86)%\%jarpath%
:CONTINUE
...
That got it working, although all of our previous tables are gone.

Cheers
Apache OpenOffice 4.1.2 Windows 10 Enterprise
calc-learner
Posts: 40
Joined: Fri Oct 10, 2014 10:14 am

Re: Putting HSQLDB in server mode?

Post by calc-learner »

Additional update: I made a test table in Base, and closed out the server using the server STOP bat. The mydb.server.odb file, however, did not get modified. Yet, when I run START file again, the new test table shows up. I don't see where it is getting saved to, given none of the files in my server folder have been modified.

My JDBC is pointing to hsqldb:hsql://localhost/;default_schema=true;get_column_name=false. Is that the correct settings? It's loading okay, and can be accessed when I press F4 in other OO apps, but something seems amiss, here, as it's not updating any of the mydb files, and seems to be getting the table data from else where.
Apache OpenOffice 4.1.2 Windows 10 Enterprise
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Putting HSQLDB in server mode?

Post by Villeroy »

The Base document does not store any data. The Base document contains:
1) The configuration to connect with the actual database where the data are stored.
2) SELECT statements (queries). Views are SELECT statements stored in the actual database.
3) Embedded Writer documents (form documents) with logical hierarchies of forms, subforms and form controls.
4) Report documents.
When you change the connection details, a query, form or report, the Base document becomes unsaved.
While editing database data through your Base document, you save modified records by navigating to another record or by hitting some button saving the current record.
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
calc-learner
Posts: 40
Joined: Fri Oct 10, 2014 10:14 am

Re: Putting HSQLDB in server mode?

Post by calc-learner »

Villeroy wrote: Fri Jun 30, 2023 1:16 pm The Base document does not store any data. The Base document contains:
1) The configuration to connect with the actual database where the data are stored.
2) SELECT statements (queries). Views are SELECT statements stored in the actual database.
3) Embedded Writer documents (form documents) with logical hierarchies of forms, subforms and form controls.
4) Report documents.
When you change the connection details, a query, form or report, the Base document becomes unsaved.
While editing database data through your Base document, you save modified records by navigating to another record or by hitting some button saving the current record.
Thank you for your reply, Villeroy. I'm including some screenshots below.

Image

None of the mydb files are getting updated.

Image

Here is the CMD log after running the server.START.bat file. I noticed it seems to be getting properties from a server.properties file on the C drive. That seems a little odd. How can I make sure it gets the properties from the server folder?

Image

A test table is working and seems to be stored in some mystery location.

Image

The settings point to the correct location in Open Office Base Databases registry.

EDIT: The images didn't load from imgur, so here is a link to all 4. https://imgur.com/a/3HDAMtw

EDIT2: I checked to see if that server.properties file from the CMD log even exists in the C:\Windows\System32\ folder. It does not. :?
Apache OpenOffice 4.1.2 Windows 10 Enterprise
calc-learner
Posts: 40
Joined: Fri Oct 10, 2014 10:14 am

Re: Putting HSQLDB in server mode?

Post by calc-learner »

Good news! The issue was I just needed to run the vbs script and not the .bat file. After running the vbs script, the database loaded just fine.
Apache OpenOffice 4.1.2 Windows 10 Enterprise
Locked