[Solved] Change embedded HSQL database into Multi-Users one

[MINTHETNAUNG]

Hi there, I have got this one embedded HSQL database and one calc macro program using that database. For a single user, I have no problem using it. But then, when many users try to use that program, I have got SQL runtime exception error. I researched on it and found out that solution is to change the database into multi-user server mode. So, how could I do it? please...

[Villeroy]

1) Download a more recent HSQL version. 2.3.4 is most compatible with your office suite. AOO and LibreOffice have some glitches with the latest versions of HSQL but 2.3.4 is as good as can be.

2) The package comes with a command line client sqltool.jar, with the database driver hsqldb.jar, source code, full documentation and tons of example files for all platforms.

3) I recomment a dedicated directory for Base documents with two subdirectories "database" and "driver". Save a copy of your original document in the Base directory and a copy of hsqldb.jar in the driver subdirectory.

4) My [Python] Macro to extract and reconnect embedded HSQLDB depends on the above recommendation. It extracts your embedded database to subdirectory "database" and connects your document to the extracted copy using the given driver/hsqldb.jar. If there is no driver/hsqldb.jar then it will use the hsqldb.jar 1.8 of your office installation which is not what you want for server mode.

5) Test if every aspect of your database works like the embedded one. This is not the server mode. It is the same file mode as with the embedded database but with a different driver connecting to a stand-alone database. In this mode you have a reasonable level of data safety even when your office suite crashes. And you have all HSQL features, including groups and users for the database but not simultanious multi-user access.

6) Copy the database directory and hsqldb.jar and sqltool.jar to a server machine on your local network and add some required configuration files.
6.1) Write a little start script for testing. And while your are in it, write a stop script for controlled server shutdown while testing. Killing the server may be OK while testing but later you want a stop script.

7) Since Windows is not (does not want to be) able to run Java programs as a service, you have 3 options to start the service:
7.1) Use your little start script to start manually when the database is needed.
7.2) Auto-start your little script which requires that you log in at the server machine. And don't forget to add an auto-shutdown mechanism.
7.3) https://yarlagaddasuresh.wordpress.com/ ... s-service/ This can start a HSQL service on startup with no log-in. I never tested this.

8) Once you have your hsql server up and running, change the connection URL of your Base document from jdbc:hsqldb:file:///.... to jdbc:hsqldb:hsql:server... via menu:Edit>Database>Connection...
------
A somewhat lengthy Linux topic. HSQL can be started just like any other Linux service if you know the startup mechanism of your Linux system: viewtopic.php?f=13&t=91740&p=439646#p439646 Yes, it looks complex but it is the same complexity for all services. A HSQL service on a Raspberry Pi should be possible in the same way.

[MINTHETNAUNG]

Thank you so much. It works! ^ ^

[Villeroy]

Thank you so much. It works! ^ ^

Does the linked tutorial Configure HyperSQL (HSQL) as a Window’s ServiceConfigure HyperSQL (HSQL) as a Window’s Service work for you or are you running your own start/stop scripts? I would like to have some feedback on this method.

[MINTHETNAUNG]

I am running my own start/stop scripts though. But the above tutorial link works, too.