[Solved] Problem with making split database fully portable
-
- Posts: 6
- Joined: Sun Nov 02, 2014 10:20 pm
[Solved] Problem with making split database fully portable
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 (viewtopic.php?f=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
Using the instructions in this forum (viewtopic.php?f=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
Last edited by Bananiel-1944 on Tue Nov 04, 2014 3:02 pm, edited 1 time in total.
Libreoffice 4.2.7.2
Linux Fedora20
Linux Fedora20
Re: Problem with making split database fully portable
Edit->Database->PropertiesBananiel-1944 wrote:but I have no idea how to do that.
R
- MMove 1.0.6
- Extension for easy, exact positioning of shapes, pictures, controls, frames ...
- my current system
- Windows 10 AOO, LOLinux Mint AOO, LO
Re: Problem with making split database fully portable
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.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.
Code: Select all
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
If the database is moved again, the URL will be re-adapted.
Hoping this helps.
JPL
Kubuntu 22.04 / LibO 24.2
Access2Base (LibO).
BaseDocumenter extension (LibO)
ScriptForge (LibO)
Documentation on https://help.libreoffice.org/latest/en- ... bPAR=BASIC
Access2Base (LibO).
BaseDocumenter extension (LibO)
ScriptForge (LibO)
Documentation on https://help.libreoffice.org/latest/en- ... bPAR=BASIC
-
- Posts: 6
- Joined: Sun Nov 02, 2014 10:20 pm
Re: Problem with making split database fully portable
Thanks a lot. Things are soooo easy if you know where to look!F3K Total wrote:Edit->Database->PropertiesBananiel-1944 wrote:but I have no idea how to do that.
R
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
Libreoffice 4.2.7.2
Linux Fedora20
Linux Fedora20
Re: Problem with making split database fully portable
Is it a good idea to run a database on a stick?
Bananiel-1944 wrote:I tried to put my split-database completely on a USB-stick.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 6
- Joined: Sun Nov 02, 2014 10:20 pm
Re: Problem with making split database fully portable
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.Villeroy wrote:Is it a good idea to run a database on a stick?Bananiel-1944 wrote:I tried to put my split-database completely on a USB-stick.
B
Libreoffice 4.2.7.2
Linux Fedora20
Linux Fedora20
-
- Posts: 6
- Joined: Sun Nov 02, 2014 10:20 pm
Re: Problem with making split database fully portable
Oops, I realise I am using Libreoffice and not Openoffice. That may be the reason that the code above does not work for me.JPL wrote: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.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.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.Code: Select all
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
If the database is moved again, the URL will be re-adapted.
Hoping this helps.
JPL
I have to check on the difference in syntax (LibreOffice found a syntax error on each (!) line).
B
Libreoffice 4.2.7.2
Linux Fedora20
Linux Fedora20
Re: Problem with making split database fully portable
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).Bananiel-1944 wrote: 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!
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.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Re: Problem with making split database fully portable
There is no difference in the syntax between LibreOffice and OpenOffice. The given code works in both suites.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).
Did you enclose the code in a Sub before running it ? Like in:
Code: Select all
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
Kubuntu 22.04 / LibO 24.2
Access2Base (LibO).
BaseDocumenter extension (LibO)
ScriptForge (LibO)
Documentation on https://help.libreoffice.org/latest/en- ... bPAR=BASIC
Access2Base (LibO).
BaseDocumenter extension (LibO)
ScriptForge (LibO)
Documentation on https://help.libreoffice.org/latest/en- ... bPAR=BASIC
-
- Posts: 6
- Joined: Sun Nov 02, 2014 10:20 pm
Re: Problem with making split database fully portable
Oh dear, I do believe you are right (blush)...JPL wrote:There is no difference in the syntax between LibreOffice and OpenOffice. The given code works in both suites.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).
Did you enclose the code in a Sub before running it ? Like in:JPLCode: Select all
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
Libreoffice 4.2.7.2
Linux Fedora20
Linux Fedora20
-
- Posts: 6
- Joined: Sun Nov 02, 2014 10:20 pm
Re: Problem with making split database fully portable
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")DACM wrote: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).Bananiel-1944 wrote: 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!
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.
The help of you all is greatly appreciated
Bananiel-1944
Libreoffice 4.2.7.2
Linux Fedora20
Linux Fedora20
Re: (Solved) Problem with making split database fully portab
Alternatively you can install my macros suite with a GUI: download/file.php?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.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Problem with making split database fully portab
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
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
LO: LibreOffice 6.4.0.3 (x64) on Windows 10 64bits. Split database HSQL 2.3.4.
Re: [Solved] Problem with making split database fully portab
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.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
[Solved] Problem with making split database fully portable
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.'papijo wrote:...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?
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):
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:
- Portable 'split' HSQL database template wrote:
Instructions for use:
(1) Ensure global macro security in *Office is set to Medium (or Low):
(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
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria