Ask Your Question
0

LibreOffice Base convert an HSQLDB to Firebird? [closed]

asked 2019-05-29 14:34:22 +0100

tommasz gravatar image

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'
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by tommasz
close date 2019-05-31 22:21:50.771529

Comments

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.

ebot gravatar imageebot ( 2019-05-29 15:20:31 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-05-29 18:14:43 +0100

Ratslinger gravatar image

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.

edit flag offensive delete link more

Comments

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

tommasz gravatar imagetommasz ( 2019-05-31 15:24:23 +0100 )edit

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?

Ratslinger gravatar imageRatslinger ( 2019-05-31 17:49:27 +0100 )edit

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.

tommasz gravatar imagetommasz ( 2019-05-31 18:13:08 +0100 )edit

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.

Ratslinger gravatar imageRatslinger ( 2019-05-31 19:00:28 +0100 )edit

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.

tommasz gravatar imagetommasz ( 2019-05-31 20:45:36 +0100 )edit

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

Ratslinger gravatar imageRatslinger ( 2019-05-31 20:54:54 +0100 )edit

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

tommasz gravatar imagetommasz ( 2019-05-31 22:21:02 +0100 )edit

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.

Ratslinger gravatar imageRatslinger ( 2019-05-31 23:04:11 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2019-05-29 14:34:22 +0100

Seen: 265 times

Last updated: May 29