Bug 104942 - Conversion HSQLDB to Firebird, Table design issues
Summary: Conversion HSQLDB to Firebird, Table design issues
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.3.0.0.beta2
Hardware: All All
: medium normal
Assignee: Not Assigned
QA Contact:
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-12-27 16:24 UTC by Gerhard Schaber
Modified: 2017-02-06 11:49 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample HSQLDB file (36.58 KB, application/vnd.sun.xml.base)
2016-12-31 18:58 UTC, Gerhard Schaber
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gerhard Schaber 2016-12-27 16:24:39 UTC
The Firebird support is much better now, but there are still some table design related issues.

I am trying to move a HSQLDB based file to a Firebird based file, because I consider Firebird better and faster. I just drag tables from one base document to the other, but have a couple of issues to get the table design over to the Firebird based file.

1. The definition is copied, but not the table data, nor the indexes.
Error code: 1
firebird_sdbc error:
*Attempt to reclose a closed cursor
caused by
'isc_dsql_free_statement: close cursor'

2. All auto increment fields of the source document become normal fields in the Firebird base file.

3. When I then change the auto increment from No to Yes, I get the error:
SQL Status: IM001
Changing autoincrement property of existing column is not supported

4. When I try to create a view from a query (right-click, select Create as View), then I get the following error.
Error code: 1
firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 397
*(
caused by
'isc_dsql_prepare'

This issue is slightlyrelated to bug #103715.
Comment 1 m.a.riosv 2016-12-27 22:37:53 UTC
Please could you attach the HSQLDB file as sample to test?
Comment 2 Gerhard Schaber 2016-12-31 18:58:30 UTC
Created attachment 130063 [details]
Sample HSQLDB file
Comment 3 m.a.riosv 2017-01-01 20:15:40 UTC
Reproducible.
Version: 5.3.0.1 (x64)
Build ID: 3b800451b1d0c48045de03b5b3c7bbbac87f20d9
CPU Threads: 4; OS Version: Windows 6.19; UI Render: default; Layout Engine: new; 
Locale: es-ES (es_ES); Calc: group

Many field types are not avaible in FB, in some table data are copyed, by example with "Lehrgangsgruppen" table.
Comment 4 Gerhard Schaber 2017-01-02 09:16:16 UTC
A related question. Is there a way to access the SQL commands for a Firebird DB, just like the "database\script" file in an HSQLDB ODB file? That would be useful for the future, if one has to migrate a database again.

What I will try as workaround is to change the data types of the HSQLDB not supported by Firebird to supported ones, and then run the commands from the "database\script" file of the ODB file. This way the indexes would also be created.

I noticed that indexes are not even copied when I drag a table from one HSQLDB file to another. So this seems to be a general limitation.
Comment 5 Gerhard Schaber 2017-01-02 11:53:44 UTC
The most annoying two issues is number 1. This happens with every single row that is being copied, and you need to confirm a dialog for every single one.

Even, if I create the table manually in the Firebird file, when I try to copy only the data from the HSQLDB file for the Geschlecht (Gender) table, I get the following error:
SQL Status: IM001
The driver does not support the function 'XParameters::setByte'.

The reason seems to be TINYINT. Well, since I only use that in 3 places, I can change that easily in the source file, but then still there is the issue number 1, which makes is nearly impossible to get data over from an HSQLDB file.
Comment 6 m.a.riosv 2017-01-02 23:36:33 UTC
Few days ago I have reported about to have all field types set up for FB.
https://bugs.documentfoundation.org/show_bug.cgi?id=104734
Comment 7 Gerhard Schaber 2017-01-10 17:47:02 UTC
See also #105240 for an issue with inserting rows into a table with auto generated values.
Comment 8 Gerhard Schaber 2017-02-06 11:49:48 UTC
Same issue with libo-master-2017-02-06_00.00.55_LibreOfficeDev_5.4.0.0.alpha0_Win_x86.msi