Thank you @RobertG , your SQL query works as well. It is interesting that two different SQL sentences can have the same output.
Regarding using Firebird I guess as I am successfully using it I may as well contnue to use it.
Thanks again.
Appears to be unnecessary as it works without this in my example. Selecting all from all table can become burdensome if the other tables contain more data than presented here.
There is only one table in your code, which is written in the direct SELECT: “TblAppraisee”. There are two subselects which couldn’t be shown as output of the query. The primary key of TblAppraisee is shown as output of the query.
You could also add subselects and correlated subselscts, which are shown as output. The main SELECT won’t change the possibilty to be editable, because it only interprets … FROM “TblAppraisee” WHERE … and expects only the primary key of “TblAppraisee”. Only the fields of this table will be editable in the query.
From the post that is all that is wanted:
https://ask.libreoffice.org/uploads/short-url/1aku0FLh18y7SXNaiucswNsIMhr.odb
This is based on a parameter query. Notice that the filtered record set is editable.
HSQL works way better than Thunderbird and it is very easy to convert the embedded database into a real one with a recent driver version.
Embedded databases are risky. Sooner or later the existence of the database depends on your backup strategy.
It rather begs the question as to why Firebird was chosen as the successor to HSQLDB?
Because it does not depend on Java which is in the hands of “the enemy” (Oracle) with unclear legel implications and weird legal proceedings about US copyright. TB has been chosen as a successor although there are serious differences which make it very unlikely that Base can be compatible with TB without breaking HSQL compatibility. Mieanwhile it looks as if TB is going to be dropped.
OK, makes sense to avoid non-open source software.
Any Linux distribution comes with free Java runtimes (free by any means).
For anybody else there is https://adoptopenjdk.net/
I have dozends of embedded HSQL databases. LibreOffice can not convert a single one to embedded TB
All very complicated to a simple user like myself, which one to choose seems very complex.
Is there an open source or low cost backend database that you would recommend, and I could use LO Base as the front end? I use Win10.
Thank you.
Depends on your requirements. Since 12 years I use Base with HSQL for a 100% desktop database on a local network with zero problems. First I got it up and working as an embedded HSQL on my laptop. Then I extracted the database, moved it with some configuration files and scripts to a server machine and distributed the connected Base file and document to 6 client PCs for simultanious access. It is still an amateurish solution but users are happy because it saves a lot of paper work and keeps track of dozends of little things that need to be done by someone on a regular basis. As a little extra it includes a simple cash register that used to be a stupid spreadsheet (rarely used but nevertheless necessary).
Another important database document is connected to the database of our business software.
I guess that more than 90% of Base documents are connected to spreadsheets because that is what everybody uses as address list for serial letters and label printing. The initial concept of database connectivity in OpenOfice.org 1.x (2000-2005) was connectivity to a wide variety of existing databases. A bridge between databases and office doucments. And IMHO this is still the best way to utilize this component. Connect office documents to your existing dBase, MySQL, MS SQL, Oracle DB or whatever.
If you need some DB for your web page, Base is useless anyway but you may be able to use it as an additional bridge to your online database.
No, Firebird never will be dropped. Too many people have started to work with Firebird and there are many fixed bugs in the last months.
The most problems appear by using the wizard for migrate a HSQLDB to Firebird. Could be we decide to set Firebird from experimental to “normal use” with LO 7.4.
OK, everybody can connect a Base document to whatever preferred database engine. HSQL with Base simply works fairly well since 2008.
Is there any repository of example databases with embedded Thunderbird? I mean non-trivial ones with useful forms that are not created by a stupid “wizard”.
Have a look here: German Base Handbook - Example databases
I have duplicated all databases of the examples of German Base Handbook. Used the wirzard for migration to Firebird and now there are versions for HSQLDB and Firebird, which all work the same. I have only tested a little bit.
At this moment I’m creating a database which I won’t create with internal HSQLDB, because I need a function like LIST. And the person will use it first as internal database. So I have described the way from internal Firebird to external Firebird file and then to Firebird server while creating this database. Also German: Homepage Robert, see “Bearbeitungsversionen” and there “Base_Gesamtband …” or the chapter “… DatenbankErstellen…”.
Apart from the 2000 lines of StarBasic code, the forms in the rental database are rather trivial. I’ll wait until 7.4 and see if it can handle any of my forms.
Did much of that years ago. Appears to be little if any interest in it.
.
Base: HSQLDB to Firebird migration for existing databases
.
and this (nice for sharing a DB from server):
.
How can I create a portable split embedded/local firebird database in the manner that is done with HSQLDB?
.
Even have code to extract the FB DB from embedded to fix the ever growing size problem and then other different items.
.
Encouraged its’ use for a couple of years. Then it went to the back burner - again. Had some hope years ago but that is gone. Way too much is needed to get this to be what it was intended to be.
Apologies for the delay - I have had to wait 7 hours. As a new user I have a cap on the number of replies per day I can do.
Thank you @RobertG and @Villeroy for your views. As with many other types of software there are pros and cons for each. I use databases for even simpler purposes than Villeroy, for example keeping a list of people I appraise and relating to this table each appraisal I do for them. Perhaps for records as simple as these I could get away with a spreadsheet. But I like tinkering (in a very amateurish way) with software and learn a little bit about SQL and macro Basic along the way. I’m over 60 and it keeps my mind ticking over!
I’m using the embedded HSQLDB especially, when I need to share the database by mail or stick, as it can be easily transfered and others need nothing more than Open- or LibreOffice.
For LibreOffice we can do the same with firebird embedded. Both can also be run as a server-database (search Split-DB in AOO or LO-forum).
I use SQLite over odbc-driver on several laptops, where people work on their part of our database and may not always have online connection. Main other reasons are support through own CLI or programs like SQLite-Studio and direct access by python. So I will always find a way to access this data for integration in something bigger, and the data is (like embedded) in one easily localised file, for transfer and backup. Disadvantage: No graphical relations(?) in Base.
For bigger tasks there is MariaDB (replacing MySQL) as OpenSource, but even Postgres is available. As I can use MariaDB hosted locally on my computer, integrated in my Synology-NAS and on the web it is a logical choice for me, if there are no special needs calling for special solutions like NoSQL etc.
All this databases are free to use on your own computer and open-source…
Thank you Wanderer, very helpful I will look into splitting Firebird.