Confusion about "Standalone Form"

English
#1

“Standalone Form”. I’ve searched the site for explanations about this, but it’s still confusing to me and the more I read about it, the more questions it raises.

1 - Is it really possible to take a Base document (an .ODB database) and make it an independent application that no longer needs LibreOffice to function?

2 - Or does this application remain linked in any way to LibreOffice via Writer or Calc or Base? …

3 - If the use of Base can be abandoned, are there any advantages or what are the disadvantages.

This subject is very confusing for me, could someone enlighten me on the subject or on the notion of “Standalone” when we talk about a conversion into “Standalone” of a database created with Base?

Days calculation in a Base form
#2

The attached Writer document is a stand-alone form document with a form linked to the Bibliography database that is shipped with the office suite. A stand-alone form can be opened without its database document. However, a registered database document is required anyway.
biblio_form.odt (28.9 KB)

#3

OK, I found a video on the net from “TheFrugalComputerGuy” which explains very well how to make each form in a database a “Standalone Form” and I understand better what you mentioned about a registered database document is required.

What I also understand is that only the SQL language is supported in this type of document. So, in the case of my DB which still contains a lot of Basic macros, trying this kind of thing turns out to be completely useless.

In fact, are there any disadvantages to using an integrated database like Base. Why do many people seem inclined to make their DB “Standalone Forms”?

#4

In a corporate environment, you load stand-alone input forms just like ordinary office documents, possibly by means of desktop links. This is by far easier than programming switch boards and open-form-buttons.
An end user does not need/want to see any Base window which is more like a development environment. Unfortunately, there is no way to create fully featured Base reports as stand-alone documents, which is why I prefer Calc reports with linked database ranges and pivot tables.

#5

OK! I better understand the usefulness and limitations of stand-alone forms.

So, as I have read a few times in various Threads and searches on the net, it is for all these reasons that people will use engines like PostgreSQL, Firebird or others of the same kind to run a database containing macros and even the slightest bit sophisticated reporting, is that correct?

#6

Base can be used with Firebird, PostgreSQL, MySQL, SQLite, MS Access, HSQL, H2 and any other type of database providing a JDBC or ODBC driver. The embedded HSQL and Firebird databases are just a convenience feature, providing a database in a single file. Such a convenience package is very well suited for educational purpose and for demos, but in the end you want to connect your Base document to some external database backend, possibly some database of a 3rd-party business administration or scientific project software. This way you get seamless integration of your existing database with this office suite.
Creating a new database is a tedious development process, requiring a lot of conceptual knowledge and experience. Base does not help very much in this field. However, it is very well suited as an adapter between existing databases and office documents.

#7

This information that you are giving me is really very relevant. You demystify a lot of things that were very confusing to me.
.
For a person like me who uses a database for personal needs only, I can be comfortable using it as part of the LibreOffice suite with Base.
.
What could force or convince me to connect my Base document to an external database like PostgreSQL for example? If it brings me no really justifiable benefits, there would be very little point in doing it, right?

#8

You started with “standalone forms”. There only the form is outside the .odb hiding a lot of the buttons of LibreOffice for users who shall not see them or for click-savers which dont like to select a form after the .odb was opened.
.
It is not related to embedded vs split datatabase, where your data is embedded in the .odb or in an external file or database.
.
Strongest reason for split db is better security of your data, as the necessary unpacking and re-embedding of data can fail, especcially when the computer or LO crashes, or when you don’t give the system time to save before power us switched of.
.
The main reason to use something like postgres or MariaDB us the possibillity to have multiple users of the same database at the same time or to move the database to another place (server/cloud).
.
If you use/rent webspace you often have a MariaDB accessible there, wich you can connect to your office-suite via Base.

#9

I recommend stand-alone HSQL as a database for one user on a personal computer with Base as the only frontend. You can design the whole thing as embedded HSQL with embedded forms, and when the database structure (tables & relations), the queries, forms and reports work well, it is very easy to extract the embedded HSQL database into a dedicated directory. For a single user, it should be less relevant if your forms are embedded or not.

#10

Thank you @Villeroy and @Wanderer,
.
Many things have been clarified. Being the only user of a personal database, for the moment, I do not see the point of using an environment other than Base.
.
I have already made sure that the database is backed up automatically as soon as it is opened so that I have more than 10 possible Backups. So if there was a problem the impact on data loss would be minimized.
.
However, what Villeroy recommends with stand-alone HSQL arouses my curiosity. I will have to take the time to read the information linked to the two links that are proposed in order to understand what this means and implies.
.
Once again thank you for this rich discussion.

#11

When you open a Base file with an embedded database, the embedded database will be installed to a temporary directory as soon as you access the database by opening a form or some other object. When you close a table, query or form with modified data, the whole database is wrapped back into the Base document. As the database grows, it becomes more likely that something ugly (shut down, crash, power outage) happens to that database while being extracted or packed into the document.

Like any other office document, a Base document is just a zip archive. Open a Base document with a zip tool, and you see the actual database in a folder named “database”. A HSQLDB consists of 3 files at least. The properties file is plain text describing the HSQL version. The script file describes the database structure in SQL language, and the data file contains the actual data in binary format.

HSQL is an independent software product written in Java. It is designed to support Java programmers with some relational database. It can run in server mode for multi-user access, feeding web pages with data and/or serving multiple LibreOffice users on a network. HSQL can also run in single-user mode like the one that is embedded in a Base document.

#12

@Villeroy,
.
Forgive my ignorance, but I never realized that a Base document was a simple archive. I opened my document with WinRar and was able to see its contents with all the files and folders embedded in it. Rather interesting, on the other hand, certain files from other types of programs seem encrypted because viewing the file presents a window requiring an interpreter in order to understand the information appearing on the screen.
.
That said, I understand that HSQL is a more secure environment when it comes to data protection. Which is far from trivial.
.
However, my DB still contains a certain number of Basic macros which enable certain functionalities. Once the DB is converted (I’m not sure the word is right) is it possible to modify its structure both in terms of forms or macros without having to reuse Base or is this done in conjunction with Base? More precisely, let’s say that after 6 months I have entered a lot of data and that I decide to make significant modifications to the forms, queries and macros. Can this be done in conjunction with HSQL? This is a bit unclear to me.

#13

Simply try out with a copy of your embedded HSQL.

  • Create a directory where you have write access and copy your Base document to it.
  • Download and install my extraction macro.
  • Open your document and run Tools>Macros>pyDBA>ExtractHSQL>Main
  • Test the database. If it does not work, close it without saving. If you saved the file already, there is also a macro to revert to the embedded database. And of course, you still have another copy or the file.

In order to upgrade to a more recent version of HSQL:

  • Download HSQL 2.4.1 which is the last version able to convert the very old HSQL 1.8. database.
  • Create a subdirectory “driver” in your new directory and extract hsqldb/lib/hsqldb.jar to the driver directory.
  • Restart the office suite in order to be sure that any connection to the extracted database is closed.
  • Open the document and run the same macro again. Tools>Macros>pyDBA>ExtractHSQL>Main

The database backend (tables, indices, relations) with its database driver is completely independent of any frontend. The backend driver (the software which actually runs the database) is not aware if some request comes from a web page, an office suite, a development suite, a script or anything else. You operate a 3rd-party database software through an office suite.

1 Like
#14

Thank you @Villeroy for taking the time to explain all this to me.
.
I’m going to take the plunge and try the procedure you suggest. I want to know what it’s like to use my DB in a different environment than Base.
.
If I encounter any difficulties, I will contact you again via this forum. But I think it should go well, everything is well explained.
.
Many thanks!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.