Bug 141132 - SQL Query to MARIADB fails (conn=1411) Could not set parameter at position..... in a formular
Summary: SQL Query to MARIADB fails (conn=1411) Could not set parameter at position....
Status: RESOLVED DUPLICATE of bug 137893
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
7.1.2.2 release
Hardware: x86-64 (AMD64) Linux (All)
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-03-20 19:05 UTC by Herbert
Modified: 2022-05-23 10:41 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Attachment of example testcase set with HSQLDB in libreoffice (40.00 KB, application/vnd.oasis.opendocument.database)
2021-04-13 09:57 UTC, Herbert
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Herbert 2021-03-20 19:05:32 UTC
Description:
A query to 2 mysql databases ( here : EIN2021 and adr01) breaks down when this is set as a SubForm.

error message is:

Full Error Message as  example (german):
Die Dateninhalte konnten nicht geladen werden. /home/abuild/rpmbuild/BUILD/libreoffice-7.1.1.2/connectivity/source/commontools/dbtools.cxx:751


SQL-Status: 42000
Fehlercode: -1

(conn=1411) Could not set parameter at position 1 (values was 565)
Query - conn:1411(M)  - "SELECT `adr01`.`KDNR`, `adr01`.`KDNR`, `adr01`.`NAME`, `adr01`.`VORNAME`, `EIN2021`.`KDNR` FROM `KKS`.`EIN2021` `EIN2021`, `KKS`.`adr01` `adr01` WHERE `EIN2021`.`KDNR` = `adr01`.`KDNR`" /home/abuild/rpmbuild/BUILD/libreoffice-7.1.1.2/connectivity/source/drivers/jdbc/Object.cxx:175

Steps to Reproduce:
1.A Form ist edited. A Subform created and the  the SQL Query is inserted 
2.when editmode for the formular  is closed, the errormessage comes up 
with this titel: 
"Die Dateninhalte konnten nicht geladen werden. /home/abuild/rpmbuild/BUILD/libreoffice-7.1.1.2/connectivity/source/commontools/dbtools.cxx:751"
3. When back to form and everything is written, the tablef-field remains empty.


Actual Results:
No query is possible in this environment.
MARIADB 

Expected Results:
SubForms could be usefull to create relational data connections.


Reproducible: Always


User Profile Reset: No



Additional Info:
It should show a form with relational subfoms linked by sql queries.
A KDNR in an accounting should be linked with KDNR in an adress database
Comment 1 Alex Thurgood 2021-03-24 16:52:55 UTC
From the linked error message pointing to the Object.cxx file, it appears that you are using a JDBC connection.

Does the same problem also manifest itself with the built-in native mysql connector driver ?

I wouldn't be surprised if this has never worked. I seem to recall that it was never possible to use a subform to query a separate database schema (but I would like to hope that this is only a regression, instead of a missing feature).
Comment 2 Alex Thurgood 2021-03-24 16:56:11 UTC
Indeed, seems to me that this is a duplicate of bug 137893
Comment 3 Robert Großkopf 2021-03-25 16:26:20 UTC
Could be it is a problem with parameters.

If this is a part of the content.xml in the Base file:
<db:driver-settings db:system-driver-settings="" db:base-dn=""
db:parameter-name-substitution="false"/>

You have to change it to
<db:driver-settings db:system-driver-settings="" db:base-dn=""/>

You could also try it by running the macro:

SUB FB_Parameter
DIM oSettings AS OBJECT
oSettings = ThisComponent.DataSource.Settings
oSettings.ParameterNameSubstitution = True
END SUB

This will change it also. The possibility to change this in additional properties for the database has been gone for MySQL/MariaDB
Comment 4 Herbert 2021-04-13 09:53:49 UTC
Version: 7.1.2.2 / LibreOffice Community
Build ID: 8a45595d069ef5570103caea1b71cc9d82b2aae4
CPU threads: 8; OS: Linux 5.11; UI render: default; VCL: kf5
Locale: de-DE (de_DE.UTF-8); UI: de-DE
Calc: threaded

System is : SuSE-Linus tubleweed and Mariadb
mariadb-admin  Ver 9.1 Distrib 10.5.9-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version          10.5.9-MariaDB
Protocol version        10
Connection              Localhost via UNIX socket


I created an HSQL Database  for Test : see attachment
then I created and copied the database to
1. mariadb - jdbc connection
2. mysql - direct connection using mariadb as well

Errors appeared in both mariadb versions while creating a form and setting a subform with the help of the database-assistent :

Errormessage:
"Die Dateninhalte konn:en nicht geladen werden. /home/buildslave/source/libo-core/connectivity/source/commontools/dbtools.cxx:751

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':link_from_accNo )' at line 1 /home/buildslave/source/libo-core/connectivity/source/drivers/mysqlc/mysqlc_general.cxx:119"

The errors are mostly always wih the remark: ":link_from_accNo" 
It seems to be an uncompatibility of mariadb with libreoffice-jdbc or mysql direct connection.
As well errors appeard in case a field is not configured with a standard value.

With native HSQLDB it works fine. See the example database here attached.
Comment 5 Herbert 2021-04-13 09:57:01 UTC
Created attachment 171155 [details]
Attachment of example testcase set with HSQLDB in libreoffice

forgotten to attache the testcase
Comment 6 Alex Thurgood 2021-04-13 17:16:33 UTC

*** This bug has been marked as a duplicate of bug 137893 ***
Comment 7 Alex Thurgood 2021-04-13 17:22:38 UTC
For the record, see also:

https://bz.apache.org/ooo/show_bug.cgi?id=42464

and:

https://bz.apache.org/ooo/show_bug.cgi?id=8949


to get an idea of just how old this is.