Bug 117053 - embedded firebird files created with parameter name substitution disabled
Summary: embedded firebird files created with parameter name substitution disabled
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.0.0.alpha0+
Hardware: All All
: medium normal
Assignee: Lionel Elie Mamane
URL:
Whiteboard: target:6.1.0
Keywords: bibisected, bisected, regression
: 117856 118984 120470 (view as bug list)
Depends on:
Blocks:
 
Reported: 2018-04-16 21:35 UTC by Drew Jensen
Modified: 2018-10-10 07:13 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
test ODB with bad form (14.42 KB, application/vnd.oasis.opendocument.database)
2018-04-16 21:35 UTC, Drew Jensen
Details
crash dump (370.62 KB, application/vnd.tcpdump.pcap)
2018-04-16 21:35 UTC, Drew Jensen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Drew Jensen 2018-04-16 21:35:01 UTC
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.
Comment 1 Drew Jensen 2018-04-16 21:35:44 UTC
Created attachment 141411 [details]
crash dump
Comment 2 MM 2018-04-16 22:57:30 UTC
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'
Comment 3 Xisco Faulí 2018-04-17 08:49:06 UTC Comment hidden (obsolete)
Comment 4 Xisco Faulí 2018-04-17 09:09:55 UTC
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
Comment 5 Xisco Faulí 2018-04-17 09:10:41 UTC
*Bisected with: bibisect-linux64-6.1
Comment 6 Xisco Faulí 2018-04-17 09:15:22 UTC
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...
Comment 7 Xisco Faulí 2018-04-17 17:10:20 UTC
I've reported the crash in a separated issue -> bug 117068
Comment 8 Lionel Elie Mamane 2018-04-18 08:47:09 UTC
For some reason, the database metadata thinks LibreOffice should not perform parameter name substitution.
Comment 9 Lionel Elie Mamane 2018-04-18 08:54:10 UTC
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.
Comment 10 Lionel Elie Mamane 2018-04-18 09:14:57 UTC
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.
Comment 11 Lionel Elie Mamane 2018-04-18 13:38:58 UTC
(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?
Comment 12 Lionel Elie Mamane 2018-04-18 17:07:01 UTC
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)
Comment 13 Commit Notification 2018-04-18 19:15:38 UTC
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.
Comment 14 Xisco Faulí 2018-04-19 09:18:01 UTC
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'
Comment 15 Lionel Elie Mamane 2018-04-19 12:33:40 UTC
(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.
Comment 16 Drew Jensen 2018-04-19 20:57:12 UTC
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
Comment 17 Drew Jensen 2018-04-19 20:59:37 UTC
(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
Comment 18 Xisco Faulí 2018-06-06 18:10:08 UTC
*** Bug 117856 has been marked as a duplicate of this bug. ***
Comment 19 Gerhard Schaber 2018-07-30 14:54:53 UTC
When setting ParameterNameSubstitution to true, will everything still work with older LO versions?
Comment 20 Drew Jensen 2018-07-30 15:01:29 UTC
(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.
Comment 21 Drew Jensen 2018-07-30 15:02:32 UTC
(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)
Comment 22 Xisco Faulí 2018-07-30 17:09:06 UTC
*** Bug 118984 has been marked as a duplicate of this bug. ***
Comment 23 Gerhard Schaber 2018-07-30 17:46:33 UTC
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?
Comment 24 Gerhard Schaber 2018-07-31 07:08:27 UTC
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.
Comment 25 Alex Thurgood 2018-10-10 07:13:50 UTC
*** Bug 120470 has been marked as a duplicate of this bug. ***