Convert server database to embedded

[misitu]

Yes, I know this is backwards...

My aim (need) is to take a copy, like backup of script, database, properties, from the server instance and create from it an embedded instance. The reason is for offline work, trial data, demonstrations, and so on (every time I start the hsqldb service I'm in the habit of backing up afterwards in a variety of ways).

I've been looking at a number of different tweaks but so far I can't get the embedded version to start.

I'm looking at a scriptable solution using copy, 7zip, and so on.

Am quite stuck at the moment so any clues will be eagerly accepted.

AOO 4.1.6, Windows 11, HSQL 2.5.1 (upgrades paused for external causes)

[Villeroy]

This is a backup script for Windows which safely backups the database without stopping the service. Adjust the absolute paths and names. Start it from the Windows task scheduler.

Code: Select all

REM Backup one database
SET DBN=MyDatabase
SET HOST=192.168.1.1
SET BDIR=E:\hsql\database\backups\
SET URL=jdbc:hsqldb:hsql://%HOST%/%DBN%,User=SA,Password=
SET JAR="E:\hsql\Drivers\hsqldb\lib\sqltool.jar"
SET CMD="BACKUP DATABASE TO '%BDIR%' BLOCKING AS FILES;"

E:
CD %BDIR%
DEL %DBN%.*
java.exe -jar %JAR% --sql %CMD%  --inlineRC url=%URL%

Inspecting the connection URL of an embedded HSQLDB, I get the following URL to be set.

Code: Select all

ThisComponent.DataSource.URL

which is "sdbc:embedded:hsqldb"

P.S.

AOO 4.1.6, Windows 11, HSQL 2.5.1 (upgrades paused for external causes)

You can not run embedded HSQL with version 2, unless you utilize https://prrvchr.github.io/jdbcDriverOOo/

[misitu]

Thanks!

Your suggestion in the main is what I already run.

I will attempt to explore further, it seemed to me that the zipped .odb of a server database when unzipped has some content radically different from that of an embedded. I've been looking for a tweak to map one to the other.



I am obliged for your swift response. Later, to confirm success or otherwise.

[Villeroy]

Create a new embedded HSQLDB.
Try running the CREATE statements of your script file. If this works, you get all the tables, relations and indices.
Copy the data.
Copy queries forms and reports.