Base -HSQL to Firebird migration and disappearing tables/data

[lucky67]

Updated to Version: 6.2.4.2 (x64) Build ID: 2412653d852ce75f65fbfa83fb7e7b669a126d64
First visit to a HSQL database since update I get the migration message, click yes, get a blank error message box.
Click on more get message
SQL Status: invalid map<K, T> key
The tables are still there, the fields (column headings) are still there but all the data in some of the tables has disappeared.
If I click on a query (of one of the blank tables) I get
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 135
*‘2018-12-15’
caused by
‘isc_dsql_prepare’

Thankfully I found if I close (without saving) and exit LO completely when I reopen as long as I click to migrate later data is back and seems intact.

This is a kind of expenses database - so mix of dates, text, currency, boolean.
Some of the tables just contain data for drop down lists (combo boxes) for a form which produces a ‘main’ outgoings table. On the form, out of 5 of these 3 still work/ their source table still contains data.
And it seems random - eg 3 of the drop downs are to categorise the expense so Main (personal), Minor (car) Detail (fuel) - the main and minor tables are now empty but the detail one isn’t - even though all 3 contain similar type of data - text.
Main table is completely empty.
Any idea what the issue is? Obviously I can just keep on using HSQL for now, until it is phased out completely and this database isn’t really important -it is a bit of a learning project for me really.
But I am planning a major overhaul of an important existing split embedded database - I guess I need to do that as Firebird but wasn’t planning on re entering all the data from scratch.

[Ratslinger]

Hello,

Don’t see any signs of dropping HSQLDB anytime soon. There are some problems in data conversion for Firebird but if you overcome these (and a few other minor issues) you get the advantage of a much better DB.

Problems occur (to best of my knowledge) with decimal, numeric and date fields.

The easiest method (my opinion) is to convert as text fields (tables with problems) and after conversion copy over to the original field types.

This post has the information → Firebird Migration: Number [ NUMERIC ] Field Data Corruption

The key will be to know what fields are causing the problems.

My procedure would be to Copy your original Base file (HSQLDB). Working with the copy, convert to Firebird. Now delete the bad tables. Next copy the tables you just deleted from HSQLDB Base to the Firebird Base using the method with text fields. Finally make certain all your relations are in place.

That should do it. May take a bit of time but you will not need to re-enter data.

You may also want to view this post for a bit more on Firebird conversion → Firebird Migration Expectations

Also, even had you saved the erroneous Firebird DB, HSQL was still within the .odb and the process could have been reversed. Your should ALWAYS have a backup regardless - for MANY reasons.

[lucky67]

Ok - thanks For now, for this, I will wait until the issues are ironed out. For the big database I will do as you say where nec… still hold off a week or two.

However like I said two tables I had issues with contained only text (and the id column - but that is the same as the other tables.) I also read somewhere about a 30 character limit - but none of these contained anywhere near that many characters -in fact the longest entry was in one of the tables that still worked. I have just checked and compared the settings for the fields in a working and non working table -they are identical The ID [integer] 10, auto, descriptions [varchar] max length 255 (default value I guess), not auto, data not required. Comparing the working and nonworking lists - both contain all caps, slashes (ie school/college). The non working ones contain spaces (ie Credit Card not Creditcard) -but changing that hasn’t fixed the problem. It is a real puzzle!

And as to having a back up - I do!!! Just had issues

[lucky67]

And as to having a back up - I do!!! Just had issues restoring the split database from the backup once so try not to use it. I learned the hard way. I’ve suffered a hard drive fail but the worst was when I had done hours of work for the end of year of my partners business on my brand new laptop and the next day had it connected up to the server at my work for the first time. My new server profile overwrote my personal one and so all my data was overwritten too. Absolutely unrecoverable - at least with the hard drive fail we managed to recover most of the data…(this was XP, in 2001 so very new and it might be different now but I still refuse to use my windows profile and my documents etc - I have a folder directly on my hard drive - much safer, especially as no-one else uses my laptop ).