Experiences migrating from HSQL to Firebird in Libreoffice 6.2.5

English
, , ,
#1

This migration was done with Libreoffice 6.2.5.2 using OpenSuse Leap 15.0 and 15.1 as well as Windows 10.
The existing documentation is quite short and in my opinion incomplete.
The documentation I could find was here:
https://wiki.documentfoundation.org/Documentation/FirebirdMigration

The migration was quite frustrating as there were lots of undocumented changes necessary, so I wrote down most of problems I faced including their solutions which I considered as not specific to my solution here.
But beware there might be suggesstions that may only have been a partial solution for my situation that do not apply to other problems.

The option to run the old and the new database side by side on the same computer will not work (2 screen solution to compare the bahavior before and after the migration). As Libreoffice detects the other running instance and will use the macro language and libraries of the database that was opened first. So in my case I was running the HSQLDB-Database via libreoffice on one computer and the firebird on another side by side. Another help is to run a standalone firebird server to test the viability of SQL-Queries or SQL-functions to narrow down the source of the problems caused by migrating. If the SQL query will not work with the standalone version there is no point trying to run it in libreoffice.

List of issues I faced

  1. Field names, table names and query name length limitation to a maximum 31 characters
    This is documented, but not checked during migration.
    If you miss out a single field, you end up that the table and its data that contains the field that has a name that was longer than 31 characters is missing in the converted database.
    If you have lots of tables, this is not easy to spot.
    The migration assistant should have no problem spotting a problem and reporting it to the user.

  2. Changes in Query Functions that can be used
    The explanation of DATEDIFF is quite short, examples oder links to the firebird documentation are not there.
    But if you want to use the function in a query you have to open the Query in SQL-Edit mode and
    switch on a mode to run thos SQL functions with the menuitem “Run SQL command dicretly” in the EDIT menu.

  3. undocumented changes
    The previously usable function CASE_WHEN had to be replaced with IIF which is not described as
    a function that needs to change.
    So look out for functions in your old code that need replacement and do not believe the error messages. If a function, query or form did work in the database before the migration it was always an incompatibility that was to blame for the error message.

  4. Relating to 2
    In case you use SQL-Function in queries and you want to reuse the calculated column as a condition (WHERE clause) you must generate a subquery:
    Non working code in Firebird
    SELECT “familyname”, “forename”, “birthday”, CURRENT_DATE, DATEDIFF(year, CURRENT_DATE, “birthday”) AS “AgeInYears” FROM MyTable WHERE “AgeInYears” > 50
    will not work without giving a meaningful error message (although it worked perfectly fine in HSQLDB)
    Working code in Firebird
    SELECT * (From SELECT “familyname”, “forename”, “birthday”, CURRENT_DATE, DATEDIFF(year, CURRENT_DATE, “birthday”) AS “AgeInYears” FROM MyTable) WHERE “AgeInYears” > 50
    That does work in firebird 3.x

  5. Forms - Filter will not work with predefined queries
    With HSQLDB there was an easy way to select specific records of tables by applying filters.
    Example (form definition):
    content-type: Query
    Analyse SQL command: yes
    filter: WHERE (member_number = “1001”)
    With firebird that works only if you do switch on “Analyse SQL command” in the forms setting, which at the same time causes some of the SQL-Functions from firebird to stop working.
    The only way I know to manage both running internal SQL-Functions of firebird and using filters
    in forms, is to access the data by SQL-Statements directly, by specifying as “Content Type” “SQL command”.

  6. Weird Problems
    Running SQL-Commands directly via the “Tools” menu of libreoffice leads to very ugly response times (2-5 minutes) but again only if the firebird database is used.
    The same query in the SQL-edit dialog by adding a query from the GUI executes swiftly and without a problem.

    Another funny problem is that the migration assistant cannot be started from the menu, you have one chance after starting and that is it. It would also be nice to mention the database release (version) and which kind of internal database is used somewhere in the menus or otherwise easily accessible.

In the end after 2 days of work I managed to migrate. I have yet to see any advantage of the new database, but that might change when I am getting a bit more familiar with firebird. At the moment the most annoying problem is the requirement to use short names which makes it hard to use self explaining field names. I cannot confirm that there is any gain in speed or usability, but it does run and I hope it is going to be as stable as HSQL.

I also took a look at the initial discussion why there was a change to firebird (starting here Base: HSQLDB to Firebird migration for existing databases). There is also a nice comparison between HSQL and Firebird (https://db-engines.com/en/system/Firebird%3BHyperSQL).

To sum it up: I have not found an official reasoning why the change was necessary and lot of speculation about possible reasons. As it is done already and so much work was invested to manage the transition I would as well argue against changing back.

I would still improve the migration assistant before phasing out the HSQL-support as well as improve the migration documentation at Migrating HSQLDB Base files to Firebird Base files - The Document Foundation Wiki which is too short and incomplete (in my opinion).

kind regards
Franz

from
Munich, Germany

Massive operations in LibO - hard?
#2

Hello,

It appears you missed this post → Migrate from HSQLDB

Also, one of the main reasons to move away from HSQLDB is elimination of JAVA. Firebird is C++.

#3

Thank you for telling me, I did not know about that link. On Linux and windows 10 there was no link offered for additional information, contrary to the shown dialog box.

The Bug “Migration bug for subforms and parameter queries” relating to subforms did not come up in my migration project. Alas, another important issue came up: Installing the firebird server on the same linux maschine as the one where you want to use libreoffice results in an error that certain files cannot be written to. That problem did go away after uninstalling firebird server package (on OpenSuse).

Regarding the reasons: I read in a few posts about a problem Java would have, but I have not seen any explanation what kind of problem that might be, that is critical to libreoffice.The only arguments I can think of is speed and possible license problems, but I could not measure any performance gains after migrating to firebird. But I did find a few deteriorations (executing SQL directly is way slower).

#4

Anyway as the decision was made, there is no point to go back on that. The idea is to make the best out of that decision and give a user experience that encourage the usage of libreoffice base. A good starting point would be to improve the migration documentation and the wizard.

#5

Frankly have not seen many of the problems you seem to experience. Have had LO Firebird embedded & Firebird server on same systems (Mint 18.3 & Ubuntu 18.04 ) with Mint being for over a year. See no problems in speed regarding SQL direct mode. See much more functionality available with SQL. Did spend time with the Firebird manual regarding SQL. There are still issues but can deal with most.

Overall, after some conversion problems, most notably dealing with numeric & decimal data, I don’t have many complaints. My main database remains PostgreSQL.

#6

Some additional experiences with incompatibilities while migrating from HSQLDB to Firebird:

  1. INSERT INTO ( SELECT * FROM ) does work with HSQLDB but not work with firebird leaving away the breackets () will make it work in firebird.
    HSQLDB: INSERT INTO

    ( SELECT * FROM ) - will result in an error message in Firebird
    Firebird: INSERT INTO
    SELECT * FROM
    This is a minor inconsistency but one that was not described in the documentation and it was not identified as a problem in the migration process.

  2. Forms: A form that was converted from HSQLDB to Firebird and that did contain a logic to switch the field between “ReadOnly” and a mode in which a modification is allowed, needs to set the field to read only (field.ReadOnly=TRUE) before allowing modification (field.ReadOnly=FALSE), just setting the field to ReadOnly=FALSE will be ignored in those migrated forms. The alternative action is to recreate the form after the migration.

  3. Trying to find the number of records of a query that has zero or no record at all previously (HSQLDB) worked like that: DIM SQLCheckMemberDataAvailable AS STRING
    DIM index AS INTEGER
    SQLCheckMemberDataAvailable = “SELECT * FROM ““Members”” WHERE " & _
    " ““membernumber””=’”+txtMember+"’ "
    indexvalue = -1
    oStatement = oConnection.CreateStatement()
    oResult = oStatement.executeQuery(SQLCheckMemberDataAvailable)
    IF (oResult.NEXT() = FALSE) THEN
    REM zero records
    END IF
    needs to be replaced with
    SQLCheckMemberDataAvailable = “SELECT COUNT(*) FROM ““Members”” WHERE " & _
    " ““Members””.”“membernumber”" = ‘" & txtMember & "’; "
    oStatement = oConnection.CreateStatement()
    oResult = oStatement.executeQuery(SQLCheckMemberDataAvailable)

    oResult.Next()
    number_of_records_found = oResult.getInt(1)

    4. Systems:
    OpenSuse Leap 15.0 and 15.1
    Windows 7
    Windows 10

    Libreoffice 6.2.6.2 (Leap 15.0)
    Libreoffice 6.2.6.5 (Leap 15.1)
    and LibreOffice 6.3.x unter Windows

    In general there are differences that affect also libreoffice code that is not directly connected to the database. The best is to compare with an emedded HSQLDB database. Any difference must be due to differences in the used database.
    At least this approach helped me.

#7

Hello,

Comment #1 - This is shown in the Firebird documentation without parentheses. The Wiki page mentions its limitation to cover all aspects of SQL and to also refer to the Firebird Documentation.

Comment #2 - This you posted in a separate question. A comment was attached stating that testing could not duplicate the problem and asked for a sample. No response was ever provided.

Comment #3 (or 1 under 2) - There are differences between HSQLDB and most other databases. Firebird itself only provides for a forward moving cursor. The solution for your example:

oResult.next
If Not oResult.isFirst() Then
    MsgBox "No Record Found for Value Entered"
    Exit Sub
End If

In HSQLDB I much prefer the bCursorTest method in this post - How to display SQL query result

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