[Solved] Put HSQLDB in server mode
[Solved] Put HSQLDB in server mode
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?
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
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
Re: Putting HSQLDB in server mode?
Have you seen this tutorial ?
R
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
Re: Putting HSQLDB in server mode?
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.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, 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:
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: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)
- 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)
- 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.
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 ...
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.
- 7a. To run HSQLDB in file mode using Base, simply download and open this Base file: mydb.file.odb
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). - 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.).
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
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Re: Putting HSQLDB in server mode?
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
- Hagar Delest
- Moderator
- Posts: 32705
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Putting HSQLDB in server mode?
+1.
I'm really impressed by the level of details and the quality of the posts, especially with such complex topics!
I'm really impressed by the level of details and the quality of the posts, especially with such complex topics!
LibreOffice 24.2 on Xubuntu 24.04 and 7.6.4.1 portable on Windows 10
Re: Putting HSQLDB in server mode?
You're certainly welcome!MTP wrote:...this detailed response! It covers all my questions plus extra ones I didn't even know to ask.
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
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
-
- Posts: 40
- Joined: Fri Oct 10, 2014 10:14 am
Re: Putting HSQLDB in server mode?
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:
So I'm wondering if there are any steps I'm overlooking that are necessary under Windows 10, given the additional security features.
TIA
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.
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
Re: Putting HSQLDB in server mode?
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 40
- Joined: Fri Oct 10, 2014 10:14 am
Re: Putting HSQLDB in server mode?
That makes sense. Additional steps I've tried: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.
- 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);
Apache OpenOffice 4.1.2 Windows 10 Enterprise
-
- Posts: 40
- Joined: Fri Oct 10, 2014 10:14 am
Re: Putting HSQLDB in server mode?
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:
That got it working, although all of our previous tables are gone.
Cheers
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
...
Cheers
Apache OpenOffice 4.1.2 Windows 10 Enterprise
-
- Posts: 40
- Joined: Fri Oct 10, 2014 10:14 am
Re: Putting HSQLDB in server mode?
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.
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
Re: Putting HSQLDB in server mode?
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 40
- Joined: Fri Oct 10, 2014 10:14 am
Re: Putting HSQLDB in server mode?
Thank you for your reply, Villeroy. I'm including some screenshots below.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.
None of the mydb files are getting updated.
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?
A test table is working and seems to be stored in some mystery location.
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
-
- Posts: 40
- Joined: Fri Oct 10, 2014 10:14 am
Re: Putting HSQLDB in server mode?
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