LO 7.1 - Parameter queries / mariadb-connector issue [solved]

[MrEgg964]

Hello,

I am currently successfully connecting to my remote mariadb 10.3 server, through LO Base 6.0.7.3 on Ubuntu 18.04. The odb file contains a number of vues, queries (some including variables), forms (some generating queries from code) and reports (using libreoffice-report-builder). The connection to the database is MySQL Native, with the MySQL Connector 1.0.2 extension.

I am now testing Ubuntu 20.04 (LO 7.1) before upgrading my system. I understand that the connector is now integrated into LO, but it doesn’t work just the same as the extension (which can no longer be installed).

I am now running into issues, when running my odb. Connection to the DB works, as I can see the tables and can run simple queries. Queries with variables (eg WHERE PurchaseDate <= :Mois) return an error around the variable :Mois (I do get the dialog box from Base prompting me for the variable, then the following error:

The data content could not be loaded. /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 ‘:Mois GROUP BY “ClientID”) AS “qrycalcClientPackagesub1” WHERE "qrycalcClientPa’ at line 1 /home/buildslave/srouce/libo-core/-connectivity/source/drivers/mysqlc/mysclc_general.cxx:119

What is the new syntax to be used with variables to solve this issue?

Thanks,
Egg

[Ratslinger]

Hello,

Do you have Run SQL command directly turned on? I get the same error with MySQL. That is a pass through.

Re-reading your question you get the dialog. If so should work - MySQL works with this.

May be a bug. Since not being able to use the extension, I have switched to JDBC (testing only - Switched to PostgreSQL).

[MrEgg964]

I have found a solution following this thread : How to unzip ODB file and edit the content.xml file to fix Firebird bug?

MySQL native connection is no longer fully working, switching to MySQL (JDBC) solves the issue after the following tweak.

Here’s what I did, in Ubuntu 20.04:

1. sudo apt install openjdk-11-jdk openjdk-11-jdk-headless
2. sudo apt install libmariadb-java
3. in Base: Tools - Options - LibreOffice - Advanced - Class Path… - Add Archive - mariadb-java-client.jar (found in /usr/share/java)
4. in Base: Edit - Database - Connection Type - MySQL(JDBC) (click next to enter db name and server name)
5. From the thread above, download the odt file, rename it to oxt as instructed, install the extension
6. Run the macro ensureConnection then Main
7. Save odb file

Parameter queries are now finally working fine in LO 7.1.4.2.