When I migrated a database from HSQLBD to Firebird, I had many queries who could not load. I tried different things until I discovered that the problem came from the syntax for joins. The syntax FROM { oj "..." RIGHT OUTER JOIN "...." ON "..."."..." = ...} coming from queries build previously with HSQLBD is not recognised and the query refuses to load On just needs to get rid of "oj" and replaces braces with parenthesis or remove them to get. FROM ( "..." RIGHT OUTER JOIN "...." ON "..."."..." = ...) or even FROM ( "..." RIGHT OUTER JOIN "...." ON "..."."..." = ...) Maybe the conversion tool could take care of correcting the syntax - or else the user should get information about how to correct his queries.
You might be interested in this link: https://wastack.wordpress.com/2018/07/25/database-migration-in-libreoffice-bug-fixes-and-more/
For the FROM { oj ... } but, I tried to apply the regex search FROM\s*\{\s*oj([^}]*)\} replace FROM (\1) it works if there is no embedding similar construct
Migrating to Firbird, transformation of queries with JOIN works well with the graphic editor: FROM { oj ... RIGHT OUTER JOIN …} replaced by FROM ( oj ... RIGHT OUTER JOIN ...) But using the graphic query editor on such queries breaks the syntax Either the additional table is ignored Either the editer reconstruct the old syntax and breaks the query So one has to use exclusively the SQL editor for queries with OUTER JOIN
Created attachment 151588 [details] Test base with 3 tables and queries with INNER JOINS The base contains a persones table and a list table related with a intermediate table for a many-to-many relationship.
I meant: works well with the SQL editer, not the graphic editor...
Uncc myself since I can't help here.
@Silvain: Can you provide us please with the corresponding hsqldb before migration so that we can try and get some debug information ?
I seem to recall that there was a recent-ish change that might have affected the {OJ} parsing.
@Silvain : the "{oj" construct is a leftover from ODBC compliant processing. There used to be a property under Advanced Properties of the database connection which could be toggled on/off. This Advanced Properties dialog appears to have been moved to the Advanced Configuration tool. I would suggest 2 things : - check in your original hsqldb ODB file whether the Advanced Property ODBC compliant strings is ticked - if it is, deactivate it and then try the migration to Firebird ; - check the Advanced Properties of your Firebird database to see whether this option is also present, and try switching it off. If you can't find these properties under the Advanced Properties dialog of the ODB, try setting them via the Advanced Configuration tool (if at all possible). Quite frankly IMO, the whole "removing the Advanced Properties" dialog by the UI/UX group to make the UI easier to use was a completely pointless exercise, and only serves to frustrate database admins.
How to find the advanced configuration tool: Select Tools->Options->Advanced Click on "Expert Configuration" When that dialog opens, enter 'hsqldb' or 'firebird' and click on 'search' The setting you want to verify is 'EnableOuterJoinEscape'.
Dear Silvain Dupertuis, This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INSUFFICIENTDATA due to lack of needed information. For more information about our NEEDINFO policy please read the wiki located here: https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Status/NEEDINFO If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed. Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-NeedInfo-Ping
Created attachment 156098 [details] Example of modifications of a query when migrating to Firebird
[Automated Action] NeedInfo-To-Unconfirmed
@Sylvain : without an original hsqldb ODB file, we are getting nowhere with this. Please provide an embedded HSQLDB filed with which we can test the migration to confirm the problem, and potentially debug the problematic code.
Created attachment 159398 [details] Test database A sample data base (with many tables, relations, queries, forms)
With my present version (6.3.5.2 on Ubuntu 18.04), I no longer see any tool to migrate to Firebird and the migration is no longer suggested at start-up of a base document. As for the problem I had with migration, I did find some workaround for the 3 things that prevented me to migrate. But it is still a lot of work: 1) get rid of all the {oj...} in the syntax of queries like "FROM { oj "Table1" LEFT OUTER JOIN "Table2"...}" - HSQL understand the query without the {oj} but stubbornly adds them when saving the query. 2) replace all the "CONCAT( aaa , bbb )" by the syntax "aaa || bbb" 3) replace the code I had made to output 1 or 0 in a complex query to check if one person is in a list (where the relationship is defined by a intermediate table to have a many to many relationship between lists and persons), so as to display which list a persons belongs to in a form. I had used a code with CONCAT to calculate this 0 or 1, and I can replace it with a CASE syntax (which works in both HSQL and Firebird) The good thing is that for points 2) and 3), the new syntax valid for Firebird works also for HSQL
(In reply to Silvain Dupertuis from comment #16) > With my present version (6.3.5.2 on Ubuntu 18.04), I no longer see any tool > to migrate to Firebird and the migration is no longer suggested at start-up > of a base document. > You have to activate experimental functions. The migration tool has been set to experimental, because it doesn't work without problems and internal Firebird also isn't integrated into GUI as HSQLDB is. You could find tips for the migrating to Firebird here: https://wiki.documentfoundation.org/Documentation/HowTo/MigrateFromHSQLDB
One hint for the problem reported in the bug: The with { oj .... } have created by the GUI and HSQLDB. When I create a query with RIGHT OUTER JOIN in the GUI with Firebird an LO 6.4.3.1 it will work well. No brackets and no "oj" ist added. Code in Firebird differs a little bit to code in HSQLDB. So I don't know if we should call this a bug. The migration tool doesn't solve any of the problems written down in the wiki. It must be solved by the user of the database.
Moving to NEW and changing to enhancement based on comment 18
I have prepared some REGEX transformation commands to solve the problems I have encountered so that I could made most of the adaptations by handling the internal text files of the ZIP odb file. A - In queries and views - and the new queries all work fine in HSQLBD 1) get rid of the additional {of .... } in queries with OUTER JOIN 2) replace the CONCAT(X, CONCAT(' ', Y) form by X || ' ' || Y 3) replace the formulas I had found to display if somebody is in a list in a many-to-many relationships defined by an intermediate table by something of the kind (CASE WHEN ... IS NULL THEN '–' ELSE '✓' END) B - In Table descriptions 4) replace VARCHAR_IGNORECASE(n) by VARCHAR(n) 5) replace TIMESTAMP(0) by TIMESTAMP It seems to me that these transfomrations (except #3) could be handled by the migration tool. If anyone is interested, I can share these REGEX (which still needs checking, as I have tested them on one Database only, but the most complex one I have, and only as a preliminary test before I migrate the actuel Database used.