Page 1 of 1

[Solved] HSQL Server setup

PostPosted: Fri Nov 22, 2019 11:19 pm
by gkick
Mssing just one piece. Can start the server by way of a start bat and a props file, can connect using Swing, however the connection string for swing does not work when trying to connect from base, although the first driver class test is ok, the test connection can not be established using the same url as per swing jdbc:hsqldb:hsql://localhost/testdb

Gonna figure that one out eventually, however at present I have no idea of how to migrate my fully developed db on to the server.
Read various posts but they all seem to relate to converting an embedded db, using some templates like mydb.server. Drag drop between split and mydb.server, but exclude tables.
Thats where I get lost. Is it possible to just copy my db in its entirety to the HSQL\HSQLDB folder where all the server databases seem to sit?

Thanks

Re: HSQL Server setup

PostPosted: Sat Nov 23, 2019 12:22 am
by sasa
Hi,

For connection string read Using OpenOffice / LibreOffice as a Database Tool.

For database migration read Backing Up and Restoring Database

Best

Re: HSQL Server setup

PostPosted: Sat Nov 23, 2019 5:49 am
by gkick
Thank you Sasa,

Still stuck after rereading appendix d of the manual which gives the connection string as
jdbc:hsqldb:hsql://localhost/mydb;default_schema=true (in my case mydb is PaA alias ctcdb)
the db now opens up from base without any tables ???
Here is the Start bat

Code: Select all   Expand viewCollapse view
cd\
e:
cd hsqldb-2.5.0\hsqldb
java -cp lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:hsqldb/PaA --dbname.0 ctcdb


Here are the props
Code: Select all   Expand viewCollapse view
server.database.0=file:E:/PlayPen/db0/PaA;ifexists=true
server.dbname.0=ctcdb


Am connected but where are the tables,
both, the startbatch and the server.properties files reside within the HSQL folder

What am I missing?

Re: HSQL Server setup

PostPosted: Sat Nov 23, 2019 1:04 pm
by Villeroy
This batch script switches to the driver directory and starts all the databases that are defined in the configuration file:
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


The stop script whih stops the database named "Praxis":
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

Re: HSQL Server setup

PostPosted: Sat Nov 23, 2019 1:16 pm
by sasa
Hi,

I don`t have hsqldb server, so pure guess:

cd\
e:
cd hsqldb-2.5.0\hsqldb
java -cp lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:hsqldb/PaA --dbname.0 ctcdb


Command create new database PaA in directory e:hsqldb-2.5.0\hsqldb\hsqldb.

This path smell:
Code: Select all   Expand viewCollapse view
server.database.0=file:E:/PlayPen/db0/PaA


e:hsqldb-2.5.0\hsqldb\hsqldb\PaA vs E:/PlayPen/db0/PaA

Best

Re: HSQL Server setup

PostPosted: Sat Nov 23, 2019 5:00 pm
by Villeroy
I forgot to add my backup script which is triggered before every system backup.
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%

In the last line hsqltool %JAR% connects to the running database %URL% and executes the SQL command %CMD%. %CMD% blocks the database, creates a checkpoint and dumps all files belonging to the specified %DBN% into the specified %BDIR%.
Before doing that, you have to delete any existing files or add some kind of versioning by dates in file name. Since my system backup writes every night to different media (one per weekday), I can simply delete yesterdays hsql backup for the specified %DBN%.
If I remember correctly, Windows can't resolve "localhost" in this context. Therefore I used the fix IP number of that server.

Re: HSQL Server setup

PostPosted: Mon Nov 25, 2019 7:41 am
by gkick
Thanks guys, slowly getting there :knock: