[Solved] Problem with making split database fully portable

[Bananiel-1944]

As club secretary after I took over I changed from paper documentation to OpenOffice/Libreoffice and developed a database with several tables, many queries, several linked forms (using macros) and reports. Now has come the time to pass this all on to my successor.
Using the instructions in this forum (https://forum.openoffice.org/en/forum/v ... 83&t=61183) I tried to put my split-database completely on a USB-stick.
At opening and using the method described above, the best I could get was successful transfer (from the original db to a new one) of tables, queries, forms and reports (rather cumbersome as each item needed to be dragged from the original db to the new one). However, the transfer of the macros was not successful. In fact, I had to enter them again (OK copy/paste worked, but this is very unsatisfactory)
As I have another 6 months before transferring everything to my successor, the urgency is manageable.
Nevertheless, I would greatly prefer to do it simply.
I am running Linux Fedora20

In the bottom line of the db window is the text:
JDBC hsqldb:file:/home/name/.../members;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
NB: the new, empty database made according to the recipe in the internet link given above gave in the bottom line the new path name /media/.../mydb (on the USB-stick)

It seems that simply changing the path name as it appears in the bottom line would be the best, but I have no idea how to do that.
I hope someone has an easy solution!

Bananiel-1944

[F3K Total]

but I have no idea how to do that.

Edit->Database->Properties
R

[JPL]

It seems that simply changing the path name as it appears in the bottom line would be the best, but I have no idea how to do that.

I use next Basic code to allow to install a split database, including its hsqldb files and the .odb file, wherever the user wants and whatever the OS (Wondows or Linux). It requires that all files are in the same directory.

Code: Select all   Expand viewCollapse view

Dim oDatabase As Object, oDataSource As Object, sURL As String, sNewURL As String
   Set oDatabase = ThisDatabaseDocument
   sURL = Join(Split(oDatabase.URL, "%20"), " ")
   Set oDataSource = oDatabase.Datasource
   sNewURL = "jdbc:hsqldb:" & Left(sURL, Len(sURL) - 4) & ";default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false"
   If oDataSource.URL <> sNewURL Then oDataSource.URL = sNewURL      '   To avoid file update if not necessary

The code can be put in a routine linked to the Open Document event of the database document. The first time the Save button in the toolbar will be greyed out. As soon as the user has saved the database, the next times the macro is executed without user-visible effect.
If the database is moved again, the URL will be re-adapted.

Hoping this helps.

JPL

[Bananiel-1944]

but I have no idea how to do that.

Edit->Database->Properties
R

Thanks a lot. Things are soooo easy if you know where to look!

Changing the pathname worked well when transferring the database on a USB-stick to another Linux (from Fedora-KDE to Kubuntu), but going to Windows failed. I continue working on that.
Bananiel-1944

[Villeroy]

Is it a good idea to run a database on a stick?

I tried to put my split-database completely on a USB-stick.

[Bananiel-1944]

Is it a good idea to run a database on a stick?

I tried to put my split-database completely on a USB-stick.

No, really not for a production database. But as I am testing the portability, this should work temporarily. When I stop being secretary I need to transfer the database to my successor by using the USB-stick.
B

[Bananiel-1944]

It seems that simply changing the path name as it appears in the bottom line would be the best, but I have no idea how to do that.

I use next Basic code to allow to install a split database, including its hsqldb files and the .odb file, wherever the user wants and whatever the OS (Wondows or Linux). It requires that all files are in the same directory.

Code: Select all   Expand viewCollapse view

Dim oDatabase As Object, oDataSource As Object, sURL As String, sNewURL As String
   Set oDatabase = ThisDatabaseDocument
   sURL = Join(Split(oDatabase.URL, "%20"), " ")
   Set oDataSource = oDatabase.Datasource
   sNewURL = "jdbc:hsqldb:" & Left(sURL, Len(sURL) - 4) & ";default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false"
   If oDataSource.URL <> sNewURL Then oDataSource.URL = sNewURL      '   To avoid file update if not necessary

The code can be put in a routine linked to the Open Document event of the database document. The first time the Save button in the toolbar will be greyed out. As soon as the user has saved the database, the next times the macro is executed without user-visible effect.
If the database is moved again, the URL will be re-adapted.

Hoping this helps.

JPL

Oops, I realise I am using Libreoffice and not Openoffice. That may be the reason that the code above does not work for me.
I have to check on the difference in syntax (LibreOffice found a syntax error on each (!) line).
B

[DACM]

In the bottom line of the db window is the text:
JDBC hsqldb:file:/home/name/.../members;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
NB: the new, empty database made according to the recipe in the internet link given above gave in the bottom line the new path name /media/.../mydb (on the USB-stick)

It seems that simply changing the path name as it appears in the bottom line would be the best, but I have no idea how to do that.
I hope someone has an easy solution!

A split database folder is fully portable when using the template with macros enabled. Or when converting an 'existing' database to a split database, you can add the provided macro to enable the same level of portability automation (while skipping the drag&drop steps and the need to transfer personal macros).

You can then move the entire folder to a USB stick, cloud folder, or between Linux, Windows and Macs. Upon opening the macro-enhanced .odb, the macro will instantly configure the necessary database connection path and save it to the .odb file. It's all seamless after initial setup, but each machine/installation must have macros enabled in *Office.

It's probably not an option in your case, but in a Windows environment, you don't even have to enable macros on the host machine (or have *Office installed) if you utilize LibreOffice Portable with jPortable (Java) to run your database entirely through a cloud folder (recommended) or from a USB device. In other words, LibreOffice Portable, jPortable, and your split-database folder can all reside in a cloud folder or entirely on a USB device for a completely portable database/office (Windows) environment.

It's also worth noting that the current macro is designed to support a single-user database environment, so concurrent multi-user (server mode) operation is beyond the scope of this discussion.

[JPL]

Bananiel-1944 wrote:

Oops, I realise I am using Libreoffice and not Openoffice. That may be the reason that the code above does not work for me.
I have to check on the difference in syntax (LibreOffice found a syntax error on each (!) line).

There is no difference in the syntax between LibreOffice and OpenOffice. The given code works in both suites.

Did you enclose the code in a Sub before running it ? Like in:

Code: Select all   Expand viewCollapse view

Sub Main
Dim oDatabase As Object, oDataSource As Object, sURL As String, sNewURL As String
   Set oDatabase = ThisDatabaseDocument
   sURL = Join(Split(oDatabase.URL, "%20"), " ")
   Set oDataSource = oDatabase.Datasource
   sNewURL = "jdbc:hsqldb:" & Left(sURL, Len(sURL) - 4) & ";default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false"
   If oDataSource.URL <> sNewURL Then oDataSource.URL = sNewURL      '   To avoid file update if not necessary
End Sub

JPL

[Bananiel-1944]

Bananiel-1944 wrote:

Oops, I realise I am using Libreoffice and not Openoffice. That may be the reason that the code above does not work for me.
I have to check on the difference in syntax (LibreOffice found a syntax error on each (!) line).

There is no difference in the syntax between LibreOffice and OpenOffice. The given code works in both suites.

Did you enclose the code in a Sub before running it ? Like in:

Code: Select all   Expand viewCollapse view

Sub Main
Dim oDatabase As Object, oDataSource As Object, sURL As String, sNewURL As String
   Set oDatabase = ThisDatabaseDocument
   sURL = Join(Split(oDatabase.URL, "%20"), " ")
   Set oDataSource = oDatabase.Datasource
   sNewURL = "jdbc:hsqldb:" & Left(sURL, Len(sURL) - 4) & ";default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false"
   If oDataSource.URL <> sNewURL Then oDataSource.URL = sNewURL      '   To avoid file update if not necessary
End Sub

JPL

Oh dear, I do believe you are right (blush)...

[Bananiel-1944]

In the bottom line of the db window is the text:
JDBC hsqldb:file:/home/name/.../members;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
NB: the new, empty database made according to the recipe in the internet link given above gave in the bottom line the new path name /media/.../mydb (on the USB-stick)

It seems that simply changing the path name as it appears in the bottom line would be the best, but I have no idea how to do that.
I hope someone has an easy solution!

A split database folder is fully portable when using the template with macros enabled. Or when converting an 'existing' database to a split database, you can add the provided macro to enable the same level of portability automation (while skipping the drag&drop steps and the need to transfer personal macros).

You can then move the entire folder to a USB stick, cloud folder, or between Linux, Windows and Macs. Upon opening the macro-enhanced .odb, the macro will instantly configure the necessary database connection path and save it to the .odb file. It's all seamless after initial setup, but each machine/installation must have macros enabled in *Office.

It's probably not an option in your case, but in a Windows environment, you don't even have to enable macros on the host machine (or have *Office installed) if you utilize LibreOffice Portable with jPortable (Java) to run your database entirely through a cloud folder (recommended) or from a USB device. In other words, LibreOffice Portable, jPortable, and your split-database folder can all reside in a cloud folder or entirely on a USB device for a completely portable database/office (Windows) environment.

It's also worth noting that the current macro is designed to support a single-user database environment, so concurrent multi-user (server mode) operation is beyond the scope of this discussion.

Thank you. The macro is larger than needed in my case, as I alreay have a split database with bells & whistles. So I took ample time to study what the template does and cut out the parts I do not need. Now transfer between two linuxes and between windows and Linux goes smoothly (I'm a pessimist, so I add "so far")

The help of you all is greatly appreciated
Bananiel-1944

[Villeroy]

Alternatively you can install my macros suite with a GUI: https://forum.openoffice.org/en/forum/d ... p?id=17008 (import the downloaded .zip with the extension manager).
Start the Basic macro FreeHSQLDB.FreeHSQLDB.Main



If that tool complains about your file not being a database document, restart the office suite with your document and try again. I can't hunt down this problem but a restart always works for me.

[papijo]

My request is slightly different. I have a project (database of garden plants) that has been created using a split HSQLDB database.
How can I transform it as "embedded" in a single *.odb file, e.g. to attach it to a post to this forum, or make it available to other AOO or LO users?
@Willeroy, your Basic macro FreeHSQLDB can do embedded -> split. Can it do split -> embedded?
TIA

[Villeroy]

Don*t do that. The tiny effort to install a split database is more worth than distributing the embedded one. You may have used HSQL2 features that are not availlable in embedded HSQL1.8, so your database may not be backward compatible.
Ship your split database with an installation routine as outlined here: [Tutorial] Splitting an "embedded HSQL database
If you are curious, you can create a new embedded db, open this one side by side with your split one and copy over all the tables, re-build relations, copy over all queries, forms and reports.

[DACM]

...created using a split HSQLDB database.
How can I transform it [into] a single file ... to attach it to a post to this forum, or make it available to other AOO or LO users?

As Villeroy points out, creating a 'portable' split-database is a deliberate process -- which should normally include the specific engine/driver (file) necessary to run your particular database. If your split HSQL database was created using this template -or- converted from an existing 'embedded' database using the portable option (Option 1), then you already have a portable database folder -- including the engine/driver (hsqldb.jar). You can 'move' the entire folder as necessary using drag-&-drop or copy-&-paste. The macro quietly ensures seamless database access, in any location, on any platform (Windows, Mac, Linux). That's what we mean by fully 'portable.'

You can also 'register' the .odb location in *Office to gain seamless access (F4) from Writer, Calc, etc. And it's often best to move your split-database folder into a cloud folder (Dropbox, Google Drive, etc.) to take advantage of the 'file history' features, allowing you to recover previous session files in the event of a disaster. A cloud folder also enables database access from multiple computers/locations (non-concurrently). With or without a cloud folder, you can run your portable database in a portable *Office environment on the Windows or Linux platforms -- taking portability to the next level because nothing is installed on the host machine.

If you'd like to 'distribute' your split-database as a single file, optionally zip (compress) the entire split-database folder into a single file (.zip container file). This is a simple matter of right-clicking the folder icon and selecting the zip-archive option. For example, if you download the above template to a new folder named 'northwind' -- right-click the folder icon to zip the entire 'northwind' folder as follows (Windows method):

Windows > Right click > Send To > Compressed (zipped) folder

Now, the recipient will need to right-click unzip the folder upon receipt, and also ensure that macros are enabled in their *Office installation (Step 1 below).

Although rarely an issue, the recipient should also verify that the global Class Path is empty in *Office (Step 2 below). This setting is empty by default in *Office, but power-users running HSQLDB in server-mode (multi-user environments) generally add a Class Path pointing to a specific HSQLDB engine/driver (hsqldb.jar). And since a global Class Path overrides the engine/driver associated with a split database, your database could be damaged if the global Class Path points to an incompatible version of HSQLDB. But then, the nice thing about using a zipped-folder for database distribution is that the original folder remains intact; you're simply distributing a copy as a single .zip file. This is inherently safe and reliable -- unlike allowing *Office to 'embed' the database within an .odb (whereby the current algorithm moves the database into the .odb while deleting the original).

See the following excerpt:

Instructions for use:

(1) Ensure global macro security in *Office is set to Medium (or Low):


Tools (*Office) > Options (Preferences) > *Office > Security > Macro Security > Medium

(2) Remove any global Class Path to hsqldb.jar that you may have setup manually in *Office:


Tools (*Office) > Options (Preferences) > *Office > Java/Advanced > Class Path > Remove

Note that a 'portable' split database folder created by these methods includes the database engine/driver within the database subfolder. So if we zip-archive the folder for distribution purposes as a single file, the resulting .zp file (containing the entire split-database folder) will be at least 2 MB in size due to the included engine/driver. This ensures database-compatibility in all cases. However, the file-size is too large for direct upload to this particular forum (due to the 128 KB maximum allowed by the forum; I think the LO forum allows larger files). But virtually all email systems allow at least 10 MB attachments. In addition, free cloud folders (Google Drive, Dropbox, etc.) and file services (MediaFire, FileToLink, etc.) allow much larger files, often with direct file links (internet URL) to your .zip file.