[Solved] HSQL Server SETUP

[gkick]

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

[Villeroy]

D:\Daten\hsql\acl.txt

Code: Select all

allow 127.0.0.1 (Access control list)
allow 192.168.15.0/24

D:\Daten\hsql\server.properties

Code: Select all

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

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

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

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

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%

[Villeroy]

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.

[gkick]

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

[Villeroy]

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.

[gkick]

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

[Villeroy]

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.

[gkick]

@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

[Villeroy]

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.