[Solved] HSQL Server SETUP

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

[Solved] HSQL Server SETUP

Postby gkick » Thu Feb 13, 2020 4:44 am

Hi,

Just not getting it, possibly confused by multiple threads, one for win, one for ubuntu, another one using mydb.server.odb, some using batch files, other vbscript......
Had the server running once but could not see any tables ?
No problems connecting to Postgres with non SBDC driver, however I seem to be incapable of putting together 4 script files.

Would it be at all possible for someone to put up an example of a simplistic HSQL Server setup (Windows) something like db folder in C:\Example
front and backend called Example
HSQL Driver and sqlTol in C:\DBdrivers
and all the scripts like start, stop, server.properties and backup within the Example folder ?

Thank you
Last edited by gkick on Fri Feb 14, 2020 3:19 am, edited 1 time in total.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
gkick
 
Posts: 292
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: HSQL Server SETUP

Postby Villeroy » Thu Feb 13, 2020 12:41 pm

D:\Daten\hsql\acl.txt
Code: Select all   Expand viewCollapse view
allow 127.0.0.1 (Access control list)
allow 192.168.15.0/24



D:\Daten\hsql\server.properties
Code: Select all   Expand viewCollapse view
server.address=192.168.15.1
server.acl=D:/Daten/hsql/acl.txt
---------------------------------------------------
server.database.0=file:D:/Daten/hsql/database/Praxis;ifexists=true
server.dbname.0=Praxis

The IP of this server, the acl list, the location of the database files starting with Praxis.*, ignore if not availlable (useful for test database), the dbname can be used to define an alternative database name for connection URL jdbc:hsqldb:hsql://192.168.15.1/AlternativeName;...)
---------------------------------------------------
HSQLStart.bat
Code: Select all   Expand viewCollapse view
SET CONF="D:\Daten\hsql\server.properties"
CD C:\Drivers\hsqldb\lib

java.exe -cp hsqldb.jar org.hsqldb.server.Server --props %CONF%

pause

Declare configuration file, change to library path of hsqldb.jar, start java.exe and tell it about the library to be used, the service provided by that library and pass the configuration file to that service. pause keeps the terminal window with the status message open.
---------------------------------------------------
HSQLStop.bat
Code: Select all   Expand viewCollapse view
SET TOOL="C:\Drivers\hsqldb\lib\sqltool.jar"
SET IP=192.168.15.1

java.exe -jar %TOOL% --sql "SHUTDOWN;" --inlineRC url=jdbc:hsqldb:hsql://%IP%/Praxis,User=SA,Password=

pause

This makes use of another Java program "sqltool.jar" to connect with the running database server and issue the SQL command SHUTDOWN;
---------------------------------------------------
sqltool.jar provides a lot of other features, and it's well documented. Create a similar script with the BACKUP command. It allows to write a secure backup without shutting down the server. I write my hsql backup to a path where it will be picked up by the nightly system backup.
---------------------------------------------------
A script to run the SQL command CHECKPOINT DEFRAG
Code: Select all   Expand viewCollapse view
SET TOOL="C:\Drivers\hsqldb\lib\sqltool.jar"
SET IP=192.168.15.1

java.exe -jar %TOOL% --sql "CHECKPOINT DEFRAG;" --inlineRC url=jdbc:hsqldb:hsql://%IP%/Praxis,User=SA,Password=


pause

---------------------------------------------------
A script to run a backup of the running database
Code: Select all   Expand viewCollapse view
REM Backup one database
SET DBN=Praxis
SET HOST=192.168.15.1
SET BDIR=D:\Daten\hsql\database\backups\
SET URL=jdbc:hsqldb:hsql://%HOST%/%DBN%,User=SA,Password=
SET JAR="C:\Drivers\hsqldb\lib\sqltool.jar"
SET CMD="BACKUP DATABASE TO '%BDIR%' BLOCKING AS FILES;"

D:
CD %BDIR%
DEL %DBN%.*
java.exe -jar %JAR% --sql %CMD%  --inlineRC url=%URL%
Last edited by Villeroy on Thu Oct 15, 2020 3:20 pm, edited 1 time in total.
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
User avatar
Villeroy
Volunteer
 
Posts: 29704
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HSQL Server SETUP

Postby Villeroy » Thu Feb 13, 2020 12:56 pm

The above setup is not a true system service. It is a user program. You have to do the Windows log in and start it as a user. AFAIK, Windows does not allow any Java program to run as a system service. Microsoft at war with Java.
--------------------------
On a Mac/Linux box, it is just an ordinary server program that starts up after booting without anybody logging in. You can use passwords that are not written into the start/stop script, the hsql server writes its messages to the system log, you can start, stop, reload the server like any other server. The server script that is delivered with hsql is just brilliant because it auto-detects commonly used vanilla configurations on Unixoid systems.
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
User avatar
Villeroy
Volunteer
 
Posts: 29704
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HSQL Server SETUP

Postby gkick » Thu Feb 13, 2020 8:45 pm

Thank you, still missing a piece,
can get the server started and stopped, but can not connect the front end as it complains it can not find the driver even though there is a copy
dummy Praxis file in the link

cheers

https://www.dropbox.com/s/95ng0marrahbf ... n.rar?dl=0
Attachments
err.JPG
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
gkick
 
Posts: 292
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: HSQL Server SETUP

Postby Villeroy » Thu Feb 13, 2020 9:21 pm

hsql_batch.png
screenshot of hsql starter
Is the server program up and running? Do you have a terminal window on your server machine?


Your screenshot dialog comes from the client side (LibreOffice) which has nothing to do with the server. The server runs as a stand-alone program on a machine where LibreOffice may not even exist.
The error message indicates that E:\Daten\hsql\driver\hsqldb.jar does not exist or that the file is corrupt (does not provide the driver class).
If you use my macro suite "FreeHSQLDB", you can set up the driver path with a GUI.
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
User avatar
Villeroy
Volunteer
 
Posts: 29704
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HSQL Server SETUP

Postby gkick » Thu Feb 13, 2020 11:08 pm

Yes, server process is working fine, can start ok, can stop ok.

I am using E:\Daten\HSQL\Praxis.odb as client

connecting url hsqldb:hsql://localhost/;default_schema=true;get_column_name=false
Attachments
connected.JPG
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
gkick
 
Posts: 292
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: HSQL Server SETUP

Postby Villeroy » Thu Feb 13, 2020 11:48 pm

gkick wrote:Yes, server process is working fine, can start ok, can stop ok.

I am using E:\Daten\HSQL\Praxis.odb as client

connecting url hsqldb:hsql://localhost/;default_schema=true;get_column_name=false

OK, the host is the localhost which is the same machine as the client which is just fine for testing.
But you also need to specify the hsqldb.jar (the actual database software) which connects your office with the running server and your error message indicates that the software specified as "E:\Daten\hsql\driver\hsqldb.jar" does not exist. Does it actually?

Install FreeHSQLDB from topic viewtopic.php?f=21&t=77543 It contains a Basic library with 3 modules and a dialog.
Then run My_Macros.FreeHSQLDB>FreeHSQLDB.Main
A dialog pops up where you can specify the file location of a hsqldb:file location (does not apply when using a server connection hsqldb:hsql) and where you can specify the location of the driver file hsqldb.jar which provides the Java class (capability) "org.hsqldb.jdbcDriver".

The server uses the same software hsqldb.jar but it uses another Java class (capability), namely "org.hsqldb.server.Server" as specified in the start script when calling java.exe.
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
User avatar
Villeroy
Volunteer
 
Posts: 29704
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HSQL Server SETUP

Postby gkick » Fri Feb 14, 2020 12:50 am

@Villeroy

Thanks again, finally got it working, had the wrong jar version and missed the alias bit in the url.

Now all is working!!!

Just one final question on the topic regarding the backup script, do you run this manually before the nightly backup or run via Windows Task Scheduler ?

Very much appreciate your step by step guidance here.
Using multi user environment I think you mentioned somewhere 6 concurrent users- any noticable speed degradation?

cheers
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
gkick
 
Posts: 292
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: HSQL Server SETUP

Postby Villeroy » Fri Feb 14, 2020 1:22 am

It does not matter how you get the script started. My backup program has a configuration setting "run other program before doing the actual backup" where I specified my backup.bat

Using multi user environment I think you mentioned somewhere 6 concurrent users- any noticable speed degradation?

My database receives all data from Base forms. Any performance issue would take thousands of users typing into their Base forms very quickly.
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
User avatar
Villeroy
Volunteer
 
Posts: 29704
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 2 guests