[Solved] HSQL Server setup

Discuss the database features

[Solved] HSQL Server setup

Postby gkick » Fri Nov 22, 2019 11:19 pm

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
Last edited by gkick on Mon Nov 25, 2019 7:41 am, edited 1 time in total.
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 188
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: HSQL Server setup

Postby sasa » Sat Nov 23, 2019 12:22 am

Hi,

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

For database migration read Backing Up and Restoring Database

Best
LibreOffice 6.3 on Xubuntu 18.04
sasa
 
Posts: 11
Joined: Wed Feb 08, 2017 12:54 am

Re: HSQL Server setup

Postby gkick » Sat Nov 23, 2019 5:49 am

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?
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 188
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: HSQL Server setup

Postby Villeroy » Sat Nov 23, 2019 1:04 pm

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27728
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HSQL Server setup

Postby sasa » Sat Nov 23, 2019 1:16 pm

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
LibreOffice 6.3 on Xubuntu 18.04
sasa
 
Posts: 11
Joined: Wed Feb 08, 2017 12:54 am

Re: HSQL Server setup

Postby Villeroy » Sat Nov 23, 2019 5:00 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27728
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HSQL Server setup

Postby gkick » Mon Nov 25, 2019 7:41 am

Thanks guys, slowly getting there :knock:
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 188
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile


Return to Base

Who is online

Users browsing this forum: No registered users and 1 guest