We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Experiences migrating from HSQL to Firebird in Libreoffice 6.2.5

asked 2019-08-27 23:29:31 +0200

fgotsis gravatar image

This migration was done with Libreoffice 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/D...

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 ...
edit retag flag offensive close merge delete



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++.

Ratslinger gravatar imageRatslinger ( 2019-08-28 01:40:58 +0200 )edit

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 ...(more)

fgotsis gravatar imagefgotsis ( 2019-08-28 03:01:56 +0200 )edit

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.

fgotsis gravatar imagefgotsis ( 2019-08-28 03:05:11 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2019-08-28 03:50:12 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-09-22 16:37:16 +0200

fgotsis gravatar image

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

  1. INSERT INTO ( SELECT * FROM <anothertable>) does work with HSQLDB but not work with firebird leaving away the breackets () will make it work in firebird. HSQLDB: INSERT INTO
    ( SELECT * FROM <anothertable>) - will result in an error message in Firebird Firebird: INSERT INTO SELECT * FROM <anothertable> 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.

    1. 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)

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

    Libreoffice (Leap 15.0) Libreoffice (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.

edit flag offensive delete link more



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:

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

Ratslinger gravatar imageRatslinger ( 2019-09-22 21:12:02 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-08-27 23:29:31 +0200

Seen: 553 times

Last updated: Sep 22 '19