Help regarding LibOfficeBase/Firebird

users
#1

Good evening,
and a successful new year 2019 to all of you!
And thanks for the gone interesting years with LibreOffice!
(even if not always quite satisfying)

My LibreOffice version on Windows10 now is 6.1.4.2 and I hope that LO-Base (finally) can meet my needs and intentions.

I have some old database-projects that I (very nicely) have built on MSAccess2003 and want now to rebuild them onto LO-Base.
One of them has three tables (a main form and two subforms with about a hundred records by 15-25 cols each) that I now have successfully ‘transformed’ via MSExcel to LO-Calc files.
My goal now is to get these 400 records to three LOBase tables with a maximum of ease (the new registered database has been created – but no tables yet!).

Now some questions to a LO-Base expert out there somewhere (I think the answers can be of interest to other LOBase-dummies like me!)
1 >>I have noticed that the embedded engine HSQLDB has been replaced with Firebird
    >>can I still work with LOBase as a frontend the same way as before? If not, why not?
    >>which are the main differences to be considered (e.g. regarding form- and report properties like with list-/comboboxes, reports etc)
                with Firebird vs HSQLDB
    >>where can I find fresh instructions (or tutorial) for LOBase (and if available for LOBase/Firebird)
2 >>as said the MSAccess table’s data are now saved as LOCalc-files.
I have learned how to export a LOBase table to a LOCalc file (registered) – but I can not find how to do the reverse:
    >>how do I import a LOCalc file’s data to a (registered) LOBase table, or in other words, get the data (with or not it’s structures) from a LOCalc file into                 a LOBase table?
3 >>        (Brian please, once more) what is the formula to calculate (now in a LOBase/Firebird form or query) a person's age out of her birth date?

All advice is very much appreciated!
Pertti Rönnberg /Finland, 79yrs

#2

Hi Pertti,

I have some old database-projects that I (very nicely) have built on
MSAccess2003 and want now to rebuild them onto LO-Base.
One of them has three tables (a main form and two subforms with about a
hundred records by 15-25 cols each) that I now have successfully
‘transformed’ via MSExcel to LO-Calc files.
My goal now is to get these 400 records to three LOBase tables with a
maximum of ease (the new registered database has been created – but no
tables yet!).

You don't need to register a database if you want to use the database
standalone, for example without using form-letters.

Now some questions to a LO-Base expert out there somewhere (I think the
answers can be of interest to other LOBase-dummies like me!)
1 >>I have noticed that the embedded engine HSQLDB has been replaced
with Firebird
    >>can I still work with LOBase as a frontend the same way as before?

There is no problem to create a new database as internal HSQLDB or
internal Firebird database. HSQLDB will be replaced in the future, but
there is a migration wizard that should do most of work here.

I would start with a Firebird database at this moment, because a
Firebird database will be the standard in future, offers some
interisting functions and doesn't need so much memory than a
HSQLDB-database.

If not, why not?
    >>which are the main differences to be considered (e.g. regarding
form- and report properties like with list-/comboboxes, reports etc)

Forms and reports will work the same way with both engines. I have just
migrated an example-database for the Base-Handbook. Works the same way,
but I had to change some queries before converting, because HSQLDB uses
some functions with different labels. For example: Firebird can't use
IFNULL, but HSQLDB and Firebird both work with COALESCE, which will work
the same way.

with Firebird vs HSQLDB
    >>where can I find fresh instructions (or tutorial) for LOBase (and
if available for LOBase/Firebird)

I have written a Base-Handbook in German. At this moment it will be
translated into English.
The German developement-version for LO 6.2 you could find here:
http://robert.familiegrosskopf.de/index.php
Older translations in English I have uploaded here:
http://robert.familiegrosskopf.de/lo_hb_en/
but the translation for each chapter hasn't been finished ...

2 >>as said the MSAccess table’s data are now saved as LOCalc-files.
I have learned how to export a LOBase table to a LOCalc file
(registered) – but I can not find how to do the reverse:
    >>how do I import a LOCalc file’s data to a (registered) LOBase
table, or in other words, get the data (with or not it’s structures)
from a LOCalc file into                 a LOBase table?

You could copy the content of one table and paste it in Base. The
table-wizard will help you to get a table with the content. Note: Tables
must have a primary-key. If you have 3 tables, which have been
connected, you will have a primary-key. It is a little bit hidden in the
wizard where to mark the field for the primary-key. The screenshots in
the Handbook will help here.

3 >>        (Brian please, once more) what is the formula to calculate
(now in a LOBase/Firebird form or query) a person's age out of her birth
date?

DATEDIFF('year', "birthdate", CURRENT_DATE)
will be the version for HSQLDB.
DATEDIFF(year, "birthdate", CURRENT_DATE)
will be the version for Firebird.
But note: This isn't the right age for every person. Will be only right
at the last day of December. So the code must be changed. This is also
written down in the Handbook, chapter 8 (Tasks).

Regards

Robert

#3

Hi Pertti,

Now some questions to a LO-Base expert out there somewhere (I think the
answers can be of interest to other LOBase-dummies like me!)
1 >>I have noticed that the embedded engine HSQLDB has been replaced
with Firebird

Firebird support is still "experimental" for the moment as there are a
number of as yet unresolved bugs still needing to be fixed before it can
be considered "operational".

>>can I still work with LOBase as a frontend the same way as before?
If not, why not?

Yes, the aim is for the frontend to work in the same way, irrespective
of the backend database engine that is supported.

>>which are the main differences to be considered (e.g. regarding
form- and report properties like with list-/comboboxes, reports etc)
                with Firebird vs HSQLDB

The main differences are those in the names of functions, correspondence
of function definitions, data types, and user-defined functions.

>>where can I find fresh instructions (or tutorial) for LOBase (and
if available for LOBase/Firebird)

Drew Jensen has drawn up a comparison of the functions on the
LibreOffice wiki, and of course, as Robert has mentioned, there is his
Base Handbook.

>>how do I import a LOCalc file’s data to a (registered) LOBase
table, or in other words, get the data (with or not it’s structures)
from a LOCalc file into                 a LOBase table?

Open the Calc file.
Open your ODB file, and click on the Tables icon.
Select all of the cells in the cell range you want to import.
With the selection still active, drag and drop the selected cells onto
the table into which you want to import the data.
The Calc to Base import wizard should start.
Use the wizard to define how you want to import the data (data
definitions, definitions plus data, append data only, etc) and check
that the data types/data you are importing correspond to what you want.
As Robert has said, you need a unique primary key field to be present
either in your table définition, or already in your Calc data.

Alex