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.

[misitu]

OK, let's start again with a potential skeleton of a process. I would try first as command line and then recreate as a windows shortcut.

I have the server-based database with properties, script, data.
I just want to repackage this as an open office embedded database.

So I need to create the .odb file.

I could create this for use as input to an instantantation which at run time can be copied to a folder that already contains the script, properties, data files copied from the live system. ZIp that folder and should be runnable.

I want to be able to create from the live (server-based) backup an instance that can be run without starting the server. Also of course not touching the live data.

So the idea is to create a blank .odb whereby I can add the copies of LIVE and run independently.

Seems not to be a big deal?

[Villeroy]

The embedded HSQL 1.8 is incompatible with HSQL 2.x.
You may try https://prrvchr.github.io/HyperSQLOOo/ which promises to create embedded HSQL2 databases.

[misitu]

Thanks, will look at.