migrate splitted HSQLDB to firebird

English
, , , ,
#1

The migration is well documented for embedded databases link to wiki.docs, the do you want to migrate question pops up once the box ‘experimental’ has been ticket ( in the tools>options>advanced frame, the one showing the installed JRE )

I couldn’t find an identical procedure for splitted HSQLDB databases.

The question can also be reframed as: how to convert a splitted database into an embedded database. In an embedded database containing one table, I copied the four splitted files, data-script-properties-backup, to the appropriate datafolder with the .odb opened with an archievemanager.
This bypass action is noted by LO 6.4.6.2 (LinuxMint 20) and recovery leads to an empty page.

How to split a firebird embedded database
#2

STEPS TO TRY…

  1. Create an EMPTY Firebird database
    (using the database Wizard) and SAVE
    in an appropriate location. (perhaps
    the same folder as your SPLIT
    HSQLDB).
  2. Keep your newly-created FB database
    open (main DB window visible)
  3. Open your SPLIT HSQLDB in an
    adjacent window
  4. Drag each table from your Split
    HSQLDB table list to the table list
    of your new FB database. Make sure
    to select ALL the columns you want
    imported when the Wizard asks you
    during the import process for each
    table
  5. SAVE the Firebird database each time
    you import a table.
  6. Drag all QUERIES, FORMS and REPORTS
    to their respective windows in a
    similar manner.
  7. Post COMMENTS here is you have any
    problems.
  8. NOTE: Some QUERIES that work in
    HSQLDB v2 may not work in Firebird,
    and likewise, there may be some
    problems with FORMS and REPORTS

UPDATE 28 September 2020

After much trial-and-error I have found a way to import a second HSQLDB ‘split’ database using the method listed below (the particular DB I tried with has a PK integer column, date, currency, and text columns). I have so far only tried the method on this one HSQLDB ‘split’ Base file.

STEPS TO IMPORT HSQLDB ‘split’ tables only:

  1. Create a new ‘blank’ Firebird Embedded (FBE) Base file in the usual manner and keep it open.
  2. Open your HSQLDB ‘split’ (HSQLS) Base file and do a clipboard-COPY of one of the TABLES you want to ‘move’ to your new empty FBE Base file.
  3. PASTE the clipboard contents into the TABLES area of the new FBE file, and choose the DEFINITION ONLY option for the paste-table-creation process - don’t try to paste the table data rows. The import wizard should then show a dialog with the names of all the columns in your original HSQLS file and make sure all of them are chosen.
  4. When you OK this, you should see the name of the newly-created table appear in the table-list (which should match the table-name in your HSQLS base file.
  5. Now open a new Calc sheet and repeat the paste operation into the sheet (your clipboard should still be loaded from the previous copy-paste operation). And note, the column names should appear in the first row. (This process might take minutes to complete depending on how many rows are in your table.)
  6. SAVE the populated Calc sheet after the process is complete. You may delete it after the steps below have been successfully completed.
  7. Also, check your Calc Sheet to make sure it contains the correct number of rows.
  8. Now, click on the on the upper left row and column ‘square’ of the sheet to select all its rows and columns, and COPY all rows and columns to the clipboard.
  9. Switch to your newly created empty Firebird embedded Base file (or open it if it’s not already open), select the appropriate table name in the tables list, and then do a PASTE.
  10. Make sure you choose the APPEND DATA option and TICK ‘use the first row as the column names’.
  11. After a variable period of time, the paste operation should complete and you should be able to see all the rows and columns of your original table imported successfully.
  12. Save your FBE file after a check if everything looks OK. (Close the file without saving if things go awry)
  13. Repeat the above steps for any other tables needing to be imported.

This process is not exactly straightforward, but maybe it is a bit simpler than the process you have developed. Post with any feedback you feel is relevant.

#3

step 4 is the difficult one: with LO6 no connection to the tables can be established: “error in script file line:1 Unexpected token UNIQUE, requires COLLATION in statement [SET DATABASE UNIQUE]”.
As a workaround, with the cloned old OS and LO5, I created an empty embedded HSQLDB database, opened the splitted db and dragged each table to the embedded database.

Then opened the embedded database in LO6 and allowed to migrate to firebird. From the splitted database dragged the FORMS > OK, attempt to drag QUERIES failed, logical, no access to tables in splitted database.
Will return to LO5 and drag, beside the tables, also the QUERIES to the embedded HSQLDB, more efficient to do that in advance, so preliminary conclusion:
Drag and drop all objects from a splitted HSQLDB to an embedded HSQLDB and then open that with LO6.
Will return with final experience, thanks for the tip to drag and drop the objects to an embedded HSQLDB.

#4

I have no ready explanation for what you are experiencing (sorry). The procedure listed works OK for me using LO v6.4.6.2 (on MacOS) and I don’t get any of your error messages. Would you be able to post your Base file ‘sanitized’ to remove all confidential data?
Maybe one or more of the column types in your HSQL ‘split’ DB is causing a problem with FB importer wizard (or even FB itself). A list of columns in your HSQL ‘split’ DB (with column TYPE and LENGTH) might reveal something. Anyone else with ideas? Look forward to hearing back from you. (PS: Are you saving and CLOSING and then re-starting your FB DB every time you import a table?)

#5

See procedure in my ANSWER above that seems to work with tables that don’t import with a simple drag and drop method. I have only tried it with the one table, so your feedback will be useful.

#6

see the second answer

So successful in the end, sorry that I cannot vote you up, because you supplied the crucial step from splitted to embedded

#7

No worries. Glad you sorted it in the end.

#8

Forgotten about that, but the database contained VIEWS and objectnames > 30 characters. What I did, and worked for me is:

  1. with LO5 opened the HSQLDB splitted database
  2. removed all VIEWS, and TABLES with column names>30 and QUERIES and FORMS based on VIEWS. So all objects could be opened without errors (kind of sanatized db)
  3. created an empty embedded HSQLDB db.
  4. opened the splitted and embedded database side by side,
  5. dragged and dropped all objects to the embedded db
  6. copied all macros to the embedded and saved
  7. opened the embedded database with LO6 > do you want to migrate > yes >> result OK after a min.

Had to do some minor SQL editing in queries and forms, and
experienced difficulty with forms including a subform (one to many): error=-206. Repaired that with broken forms.

So successful in the end, sorry, cannot vote you up

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