Created attachment 141410 [details] test ODB with bad form TEST SYSTEM: Version: 6.1.0.0.alpha0+ Build ID: 110a9e2604e835fff9fcd6053b1d7e5f92b92e8e CPU threads: 4; OS: Linux 4.13; UI render: default; VCL: gtk2; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-04-16_12:05:29 Locale: en-US (en_US.UTF-8); Calc: group Reproduce: Open attached test database. Open existing form for data entry. An error dialog box will open for an SQL error. Close the error dialog. Crash The form was created with the wizard, based on a query and user error then used the same query as the sub_form datasource and that causes the SQL error. Open forms without sql errors isn't causing crashes. Running query defs which generated SQL errors did not crash either. But this form in the db will crash it every time.
Created attachment 141411 [details] crash dump
Confirmed with Version: 6.1.0.0.alpha0+ Build ID: dc823f5fa4a5d2eca56297b9045e5962536c00f9 CPU threads: 2; OS: Linux 4.4; UI render: default; VCL: gtk2; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-04-10_23:32:35 Locale: en-US (en_US.UTF-8); Calc: The error window says: Error code: 1 firebird_sdbc error: *Dynamic SQL Error *SQL error code = -206 *Column unknown *LINK_FROM_EMPLOYEEID *At line 1, column 240 caused by 'isc_dsql_prepare'
This started after https://cgit.freedesktop.org/libreoffice/core/commit/?id=159dd28651788a19848eae56693ad06ed947414d
The error started after: author Lionel Elie Mamane <lionel@mamane.lu> 2018-01-02 22:49:31 +0100 committer Lionel Elie Mamane <lionel@mamane.lu> 2018-01-04 07:50:22 +0100 commit a29d97e6ddab8ec002ba9827bd5fc874117712e0 (patch) tree cbedfb8885b7980a4ea6ebb1347e2170a969b01a parent 1bbadad79d91005dc18a3c1e34de14d02660f6ab (diff) tdf#104986 move named parameters substitution into generic layer Previously, drivers were responsible for making the substitution themselves. In practice they all (Firebird, ODBC and JDBC) used the LibreOffice SQL parser to parse the SQL statement and do the substitution. This had a few negative consequences: * The substitition was applied to _all_ SQL commands, including queries having the "execute SQL directly" bit set. Which means that the SQL was _not_ sent to the DBMS exactly as typed by the user. Even if there was no substitution to be made, since the SQL command was always round-tripped through the parser, thus "normalising" it (which is what led to tdf#104986). * "execute SQL directly" queries "magically" behaved slightly differently depending on whether the LibreOffice SQL parser succeeded in parsing them or not. Bisected with: Adding Cc: to Lionel Elie Mamane
*Bisected with: bibisect-linux64-6.1
I guess we have two problem here. On the one hand, the error pasted in comment 2, which started after https://cgit.freedesktop.org/libreoffice/core/commit/?id=a29d97e6ddab8ec002ba9827bd5fc874117712e0. On the other hand, the crash, which happens, when the error dialog is closed *AFTER* the form dialog is closed as its parent has been destroyed, I guess...
I've reported the crash in a separated issue -> bug 117068
For some reason, the database metadata thinks LibreOffice should not perform parameter name substitution.
The setting is explicitly there, in the ODB's content.xml: <db:driver-settings db:system-driver-settings="" db:base-dn="" db:parameter-name-substitution="false"/> May I ask with which version of LibreOffice taht ODB file was _originally_ created? I think that's where the bug is... The current LibreOffice "just" follows the settings that are hard-coded in the file.
Nope, my previous comment is wrong, current master explicitly disables parameter name substitution in newly created Firebird Embedded, and overrides the setting if it is in the filke. I don't know why. That's the source of the problem.
(In reply to Lionel Elie Mamane from comment #10) > Nope, my previous comment is wrong, current master explicitly disables > parameter name substitution in newly created Firebird Embedded, and > overrides the setting if it is in the file. Correction: it explicitly disables parameter name substitution in newly created Firebird Embedded files, *but* *respects* the setting if it is in the file. Changing the setting to "true" solves this problem *for* *the* *file*: 1) Unzip the .odb 2) edit content.xml 3) replace <db:driver-settings db:parameter-name-substitution="false" /> by <db:driver-settings db:parameter-name-substitution="true" /> 4) repack (rezip) the odb with the new content.xml Remaining question: why is it set incorrectly in new files?
The bug is that the wrong setting is put at *creation* of the .odb file. So the fix will work for *new* ODB file, but already created .odb file will need to have the setting changed. Here are a few ways: * edit content.xml in the ODB's zip structure * change the setting by Macro code * a hacked LibreOffice that shows Advanced Settings for Firebird and change it in Advanced Settings (add the ParameterNameSubstitution *feature* to Firebird's Drivers.xcu)
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d6fce54c82868b82bd6fa190db6047d69bbb3ecf tdf#117053 create database wizard: set a property when it is supported 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.
Hi Lionel, I still reproduce it in Version: 6.1.0.0.alpha0+ Build ID: cb5f6503f593d7c7a719542281b9efd274134f7c CPU threads: 4; OS: Linux 4.13; UI render: default; VCL: gtk3; Locale: ca-ES (ca_ES.UTF-8); Calc: group STR: 1. Ope the file 2. Double click on the form Error: firebird_sdbc error: *Dynamic SQL Error *SQL error code = -206 *Column unknown *LINK_FROM_EMPLOYEEID *At line 1, column 240 caused by 'isc_dsql_prepare'
(In reply to Xisco Faulí from comment #14) > I still reproduce it Please refer to comment 12. The bug happens a file _creation_ time, so tests should happen on _newly_ _created_ filed. Create a new embedded firebird odb, drag and drop the two tables, the query and the form to the new file. You'll see the new file will not have the problem. Users hit by this can "just" change the setting in their existing odb files, e.g. with a macro like: Option Explicit Sub ensureConnection() Dim DBDocUI as Object on error resume next 'XRay ThisComponent DBDocUI = ThisDatabaseDocument.currentController if not DBDocUI.isConnected then DBDocUI.connect end if on error goto 0 End Sub Sub Main ensureConnection() ThisDatabaseDocument.DataSource.Settings.ParameterNameSubstitution = true End Sub or by editing the odb file directly (when not open in LibreOffice) as described in comment 11.
Verified with Version: 6.1.0.0.alpha0+ Build ID: fc5d185bdb0f27bc6014d47338740f741ac05d21 CPU threads: 4; OS: Linux 4.13; UI render: default; VCL: gtk2; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-04-19_00:40:03 Locale: en-US (en_US.UTF-8); Calc: group
(In reply to Lionel Elie Mamane from comment #15) > (In reply to Xisco Faulí from comment #14) > > > I still reproduce it > > Please refer to comment 12. The bug happens a file _creation_ time, so tests > should happen on _newly_ _created_ filed. Create a new embedded firebird > odb, drag and drop the two tables, the query and the form to the new file. > You'll see the new file will not have the problem. > > Users hit by this can "just" change the setting in their existing odb files, > e.g. with a macro like: > > Option Explicit > Sub ensureConnection() > Dim DBDocUI as Object > on error resume next > 'XRay ThisComponent > DBDocUI = ThisDatabaseDocument.currentController > if not DBDocUI.isConnected then > DBDocUI.connect > end if > on error goto 0 > End Sub > > Sub Main > ensureConnection() > ThisDatabaseDocument.DataSource.Settings.ParameterNameSubstitution = true > End Sub > > or by editing the odb file directly (when not open in LibreOffice) as > described in comment 11. Excellent - will capture that macro for the migration How To wiki page. Thanks
*** Bug 117856 has been marked as a duplicate of this bug. ***
When setting ParameterNameSubstitution to true, will everything still work with older LO versions?
(In reply to Gerhard Schaber from comment #19) > When setting ParameterNameSubstitution to true, will everything still work > with older LO versions? Have tested this back to 6.0.5 and yes it works there.
(In reply to Drew Jensen from comment #20) > (In reply to Gerhard Schaber from comment #19) > > When setting ParameterNameSubstitution to true, will everything still work > > with older LO versions? > > Have tested this back to 6.0.5 and yes it works there. Sorry, I test migrated files with the change made, not this issue per se (the migration assistant leaves files that must have this fix applied)
*** Bug 118984 has been marked as a duplicate of this bug. ***
I can confirm that 118984 is fixed, if I run the macro above. What I do not understand is why the flag is not automatically set when a Firebird base file is loaded in a current 6.1.0. What is the use case of leaving ParameterNameSubstitution to false?
I think setting this to true is not only relevant when migrating from HSQLDB. It seems that it is always necessary in order to not break anything.
*** Bug 120470 has been marked as a duplicate of this bug. ***