Firebird Migration Expectations

[JohnD]

Is there any documentation about how to migrate to Firebird from the internal HSQLDB and what to expect after the migration - i.e. are just tables migrated? How about Queries? Forms? Reports?

My initial try with 6.1.0.3 kind of indicates that my tables will probably be ok. However, queries and forms (very simple ones) result in error messages that appear to indicate that after conversion the table data cannot be loaded.

My databases are pretty simple, I am not an experienced programmer and am looking for a “consumer level” conversion process. Or, assurance that I can stay with the current configuration for the foreseeable future.

.

[EasyTrieve]

At this point given that you’re going to have to move your data anyway, I think one might consider moving your HSQLDB data out of LO altogether, and into a more stable and capable database like for example MariaDB, and then connect to the data and use LO as a front end. I think there are a number of advantages to doing this, including system molecularity and the ability to use 3rd party tools like HeidiSQL, Jaspersoft Studio reports, etc. Just food for thought.

[JohnD]

Looks like migration problems of corrupt numeric/decimal values still exists in version 6.2.0.
Is it safe to stay with HSQLDB until this migration problem is resolved?

[Ratslinger]

Hello,

The migration you speak of only deals with the tables themselves. It does nothing with forms or queries. Once you convert the tables and save the .odb, the HSQL tables are still retained within the zipped .odb. When using this conversion be careful to verify table data as decimal or numeric fields can be converted incorrectly.

For form problem, the only specific one deals with forms which have subforms. This is discussed in Bug #117053 and can be fixed with the provided macro in Comment #15 or a direct file patch mentioned in Comment #11.

You may have other form problems either not reported or they may be based on SQL (Queries). Personally haven’t seen any other problems.

Firebird SQL has some differences. Documentation can be found here → Firebird Documentation. The v2.5 Language Reference is the latest and for 3.0 you need to check release notes for changes. One item recently found does concern numeric/decimal fields. See this post → HSQLDB SQL in Firebird.

Here are the differences I have found thus far in HSQL vs Firebird SQL:

Access portion of date field:

HSQL     - YEAR(MYDATE)  this also applies to MONTH & DAY
Firebird - EXTRACT(YEAR FROM MYDATE)

DATEDIFF (note "Unit" surrounded by apostrophe):

HSQL     - DATEDIFF( 'year', "DATE1", CURRENT_TIMESTAMP )
Firebird - DATEDIFF( year, "DATE1", CURRENT_TIMESTAMP )

SUBSTRING:

HSQL     - SUBSTRING ("ITEM", 1, 3)
Firebird - SUBSTRING ("ITEM" from 1 for 3)

BOOLEAN(new in Firebird 3):

HSQL     - "CheckBox" = 'TRUE' ( or 'FALSE')
Firebird - "CheckBox"   (this is same as IS TRUE)  (also: IS FALSE or IS UNKNOWN or IS TRUE)

LOCATE:

HSQL     - LOCATE( SPACE( 1 ), "ITEM" )
Firebird - POSITION( ' ', "ITEM" )

LENGTH:

HSQL     - LENGTH("FIELD")
Firebird - CHAR_LENGTH("FIELD")   (also: CHARACTER_LENGTH or OCTET_LENGTH or BIT_LENGTH)

Comments:

HSQL     - accepts --Line style; //Java style line; /* C style Block */
Firebird - accepts --Line style; /* C style Block */

The following work in either:

MAX; ORDER BY; UPPER; LOWER; IS NULL; NULL; LIKE; COLESCE; SUM;
BETWEEN; HAVING; COUNT; parameters like :EnterDate;+, -, /, *; || (string concatonation);

This list is fairly general and variations are probable. Again, refer to the documentation.

You will need to go through you Queries and fix where appropriate.

This does not include the items now available in Firebird which were not in HSQL such as SQL easier date math, Windowing Functions, and calculated fields.

[JohnD]

Thanks Ratslinger for the valuable information.
I kind of thought this conversion was not going to be easy. I do appreciate the hard work of the developers but I guess I will stick with the old HSQLDB for a while.
Yes, I did see a number of errors in the conversion of decimal or numeric fields. Couldn’t identify any specific pattern but the large number of errors makes it impractical to manually correct.

[Ninpodeshi]

Ratslinger,

Does this mean we need to reconstruct the forms and queries, or is there a way to migrate them too?

I allowed a db to convert to Firebird, and now I cannot interact with it through the queries I made. (Different from the one you just helped me to recover.) With the db you just helped me to recover, I think now is a good time to reconstruct it in Firebird before giving it to others to work with, if that’s what I need to do.

Thanks.

[Ratslinger]

@Ninpodeshi,

If using the automated conversion, forms, queries and reports remain and there is nothing to migrate. Often the problem with queries is that the syntax has changed (see my answer). Some modification may be needed to fix SQL. See also:

Migrating HSQLDB Base files to Firebird Base files

and

Migrate from HSQLDB

[Ninpodeshi]

Thank you for the clarification.

I’m not that familiar with SQL yet and I want to make a lot of minor tweaks in the query anyway, so I’ll use this as a learning experience to remake the query screens.

Thanks again.