LibreOffice Base convert an HSQLDB to Firebird?

[tommasz]

I’ve been trying to do this with a small database I have without success over multiple versions. I had hoped that 6.2.4.2 (on Mac OS X 10.14.5) would finally solve this issue but it persists. Forms and reports appear unaffected. Is there another way to do this?

Symptoms: First sign is a blank dialog box with an “OK” and “More” buttons. Clicking “More” shows an Error: “SQL Status: map::at: key not found”. The single database table is now empty.

A query against that table returns:

The data content could not be loaded.    

 firebird_sdbc error:
 *Dynamic SQL Error
 *SQL error code = -104
 *Token unknown - line 1, column 612
 *'dd'
 caused by
 'isc_dsql_prepare'

[Hrbrgr]

On the German Community it is recommended to work with HSQLDB for the productive use. This refers to the integrated DB. Firebird is still considered experimental.
Some users are interested in Firebird and its evolution.
Firebird error messages can be viewed on Bugzilla. It should also be noted that some syntax has changed.

[lonk]

Dear @Hrbrgr,

Is there any tool for converting HSQLDB to Firebird automatically ?

I am using Fedora 31 Workstation KDE Desktop Spins and LibreOffice 6.2.8.2-2.

[Ratslinger]

Hello,

There is no reason, from my experience, not to use Firebird embedded. Although there are some initial hurdles, once overcome it is much better that the ancient HSQLDB embedded database in Base. SQL alone is worth the effort.

As with changing from one database to any other, typically you will find some differences in SQL. It appears your error is related to the DATEDIFF function. In HSQLDB dd is surrounded with apostrophes. In Firebird it is not. For more of this, please see my answer here → Firebird Migration Expectations.

The post contains many of the differences in SQL. It also contains a link to the Firebird documentation which is valuable in converting.

If you continue to have problems, please include the actual SQL statement which is causing the problem.

[tommasz]

Fixing the DATEDIFF function doesn’t do anything to deal with the fact that the conversion deletes all my table data.

[Ratslinger]

Please provide a sample of the Base file without personal or confidential information. The error you have presented is relevant to SQL. Is this possibly a table view and not actually a table?

[tommasz]

There is an issue with the SQL syntax of the DATEDIFF function in the query, the dd should not be enclosed in single quotes. This is separate from the empty table.

[Ratslinger]

There is certainly some reason the converted table is empty - another error? Cannot help further without a sample.

Have not experienced any conversion myself which creates an empty table without errors. Not to say this isn’t happening to you but do need example file posted.

[tommasz]

It does generate an error. From the original post:

Symptoms: First sign is a blank dialog box with an “OK” and “More” buttons. Clicking “More” shows an Error:

"SQL Status: map::at: key not found". 

The single database table is now empty.

[Ratslinger]

My apologies for not reading the question correctly. Must be something with the data. Still need sample.

[tommasz]

I’m not willing to post the file. I’ll stick with HSQLDB for now and keep seeing how newer versions perform.

[Ratslinger]

Have done some further searching and found a bug report - #123368 which specifies your error. It is caused by a space in the table name. Eliminating the space fixes the conversion problem.