Ask Your Question
0

migrate splitted HSQLDB to firebird

asked 2020-09-26 14:05:35 +0100

parsely gravatar image

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2020-09-29 00:18:10 +0100

parsely gravatar image

updated 2020-09-29 09:03:02 +0100

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

edit flag offensive delete link more
0

answered 2020-09-27 00:00:34 +0100

frofa gravatar image

updated 2020-09-28 10:04:43 +0100

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 ...
(more)
edit flag offensive delete link more

Comments

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

parsely gravatar imageparsely ( 2020-09-27 11:58:13 +0100 )edit

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?)

frofa gravatar imagefrofa ( 2020-09-28 00:27:31 +0100 )edit

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.

frofa gravatar imagefrofa ( 2020-09-28 08:51:51 +0100 )edit

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

parsely gravatar imageparsely ( 2020-09-28 23:57:41 +0100 )edit

No worries. Glad you sorted it in the end.

frofa gravatar imagefrofa ( 2020-09-29 01:26:52 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-09-26 14:05:35 +0100

Seen: 117 times

Last updated: Sep 29 '20