Ask Your Question
0

Firebird Migration Expectations

asked 2018-08-29 05:33:42 +0200

JohnD gravatar image

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.

.

edit retag flag offensive close merge delete

Comments

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.

EasyTrieve gravatar imageEasyTrieve ( 2018-10-02 19:35:24 +0200 )edit

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?

JohnD gravatar imageJohnD ( 2019-02-14 01:42:57 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2018-08-29 07:06:04 +0200

Ratslinger gravatar image

updated 2018-08-29 07:22:14 +0200

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.

edit flag offensive delete link more

Comments

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.

JohnD gravatar imageJohnD ( 2018-08-30 02:33:59 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-08-29 05:33:42 +0200

Seen: 467 times

Last updated: Aug 29 '18