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.
Please could you attach the HSQLDB file as sample to test?
Created attachment 130063 [details] Sample HSQLDB file
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.
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.
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.
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
See also #105240 for an issue with inserting rows into a table with auto generated values.
Same issue with libo-master-2017-02-06_00.00.55_LibreOfficeDev_5.4.0.0.alpha0_Win_x86.msi
https://cgit.freedesktop.org/libreoffice/core/commit/?id=b3ea7ed747991696b9f02ede14c57f333c28a41b may help here
Julien Nabet committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f31ccd18f1b142f8c851e7e219f8bc18b157bc52 Related tdf#104942: implement setByte on firebird It will be available in 6.1.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
As far as I could see, copying the tables from the HSQLDB .odb database in Comment #2 into a freshly created empty Firebird .odb database works now in master. Resolve as fixed? I will attach the resulting .odb. I do se warnings like: warn:connectivity.firebird:1905:1905:connectivity/source/drivers/firebird/PreparedStatement.cxx:269: firebird_sdbc error: *Attempt to reclose a closed cursor caused by 'isc_dsql_free_statement: close cursor' but they are indeed "just" warnings. Should one still be concerned?
Created attachment 138798 [details] tested.odb manually "converted" to Firebird by drag-and-dropping the tables
(In reply to Tor Lillqvist from comment #11) > As far as I could see, copying the tables from the HSQLDB .odb database in > Comment #2 into a freshly created empty Firebird .odb database works now in > master. Resolve as fixed? I will attach the resulting .odb. > > I do se warnings like: > warn:connectivity.firebird:1905:1905:connectivity/source/drivers/firebird/ > PreparedStatement.cxx:269: firebird_sdbc error: > *Attempt to reclose a closed cursor > caused by > 'isc_dsql_free_statement: close cursor' > > but they are indeed "just" warnings. Should one still be concerned? They seem to be a sign of incorrect resource management by the Firebird SDBC driver, so I wouldn't be unhappy to have them understood and fixed. If a cursor is closed "too early", then it might be closed when still needed.
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-6-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d4469196a2072d2d3fa2e433d02552facaa269f2&h=libreoffice-6-0 Related tdf#104942: implement setByte on firebird It will be available in 6.0.0.2. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
I think it would be a good idea to split this bug up into separate individual easily verifiable bugs, to the extent there are issues left. How many of the four items in the initial comment are still present?
Hello, I can test as soon as there is a new nightly build where these and related fixes are included. I am having other Firebird unrelated troubles with with 6.1.
1. and 2. work. 3. still does not work. It tries to create an additional primary key. But explicitly setting the previous primary key as primary key again does also not help. 4. Does not work either. But it says "No table format could be found", now. Tested with: Version: 6.1.0.0.alpha0+ Build ID: dbf83d315acc454b576355f2e5bd8412586827ac CPU threads: 8; OS: Windows 6.1; UI render: default; TinderBox: Win-x86@42, Branch:master, Time: 2018-01-07_23:57:11 Locale: de-AT (de_AT); Calc: group threaded I am glad that the major two issues have been resolved. Thanks.
(In reply to Gerhard Schaber from comment #17) >... > 4. Does not work either. But it says "No table format could be found", now. On pc Debian x86-64 with master sources updated today, I indeed reproduce the error message but if you select Refresh tables, you'll see that the view has been created.
I forgot to say that if I create a view with "Create View" wizard, the view doesn't appear automatically too. In comparison, with hsqldb embedded, the view appears automatically (so with using "Refresh Tables") in both cases, so no error message of course with using "Create as View" on a query.
1. 2. are solved. 3. is impossible with Firebird. Firebird does not allow to change an existing column to autoincremented. 4.: I think it would be nice to create a new, clean bug tracker for this, considering that the initial error message has been changed since the creation of this tracker. I'm not sure what is the correct status in that case, so I just move it to resolved / fixed.