I have created a database in Base 7.0 using the Firebird engine. Now I need to share the DB with an organization which is still on LO 6.1, where attempting to access the data will give an error that the Firebird SDBC driver cannot be found.
Is there any way to convert a Firebird DB to HSQLDB if that kind of backward compatibility is needed?
Hello,
Converting one database to another requires either a good deal of coding (ie: HSQLDB embedded to Firebird embedded process) or a good deal of manual intervention in copying items from one location to another. The Base file itself is not a major problem but the database can be very troublesome.
Why not simply have the user install the appropriate driver.
On Linux I get this through Synaptic package manager - libreoffice-sdbc-firebird
Why not simply have the user install the appropriate driver—because the user is in a typical locked-down corporate environment, where installing a driver is not a 5-minute operation, but a project that takes several months at best. Most likely, I will have converted the database by hand before the SBDC driver rollout project even gets management approval. Almost any database can be recreated (there is, or has been, even support for automating HSQL→Firebird) using another engine, unless you are making heavy use of features not found in the target DB engine—I am just interested in how I can speed up the process, rather than doing everything by hand.
Here is a semi-manual process that worked for me:
Create a new database using the HSQLDB database engine, and open both databases for editing, side by side.
Copy the tables over one by one using Ctrl+C/Ctrl+V. Be sure to copy definiton and data, and select all columns.
Caveat here: if one of your Firebird tables has a CLOB column, copying content will fail. When copying the table to the destination, first select Memo [LONGVARCHAR] as the column type in HSQLDB where Firebird has CLOB. This will create the definition, but no content, as LO atempts a setClob operation, which is not supported for HSQLDB. As a workaround, create an empty Calc sheet, copy the table there, select all cells and copy the data into the newly created table (use first row as column names, should be the default).
Recreate relationships between tables if needed.
Now copy over the queries in the same manner. If you have queries depending on each other, you may need to copy them in the correct order: first those which depend only on tables, then those which depend only on queries you have already copied.
Caveat here: if your queries use functions which are specific to one database engine, you will need to modify these queries. The LO wiki has a list of semantically equivalent functions which have different names in both DB engines. You’ll have to try out each query to ensure it still runs on the target database engine and returns the same results. (Some of the queries in my DB did not work after copying, but I did not investigate further as they are essentially abandonware.)
Next, copy over the forms as before. If you use SQL statements anywhere in your forms, and they include engine-specific functions, modify them as above.
Now copy over the reports. (Haven’t tried this, but should be very similar to forms.)
If your database includes macros, you will have to copy them over as well. (Haven’t tried this.)