I’m learning how to write queries in Libreoffice. I’m quite familiar with MS SQL but not LibreOffice with a MySQL back end. I created a very simple query to see how to get the prompt for the car_id to appear before running the query. I put :car_id into the Criterion field as seen in the image. When I run the query, I get the error as seen in the image. Any help would be appreciated.
I am running MySQL 8.0 and LibreOffice 6.4.3.2. If I create the same table in a test database running locally, the query works just fine with the prompt. I think I am fighting a syntax problem with the MySQL Back end. Is that correct?
Hello,
The first thing I don’t seem to see is a field set to be the primary key. Would think this is field car_id.
Using Mint 19.3 Cinnamon with MySQL v8.0.19 and LO v 6.4.2.2 tested with approximate set-up (set primary key). Prompt first:
Then after entering 2:
Also should note this is a JDBC connection with:
mysql-connector-java-8.0.12.jar
java-11-openjdk-amd64Thank you for the quick response. My primary key is actually on a different field called unique_id as the car_id very well may have the same car number from time to time. I’ll keep playing with this to see wha is going on. I’m using the Native MySQL for the connector type. Would that impact how SQL queries are written in LO to make queries into the MySQL backend database?
The connector is always a possibility. Tend to avoid MySQL native since it was changed to MariaDB connector in the not that distant past. Gave me problems in various areas (especially in my macros) and eventually changed to JDBC and then moved to a different DB.
Edit:
Just moved to my Ubuntu 20.04 system with MySQL 8.0.20 and tested with MySQL native connector & worked. This also worked with MySQL v5.7 and a native connection.
Not certain where your problem lies.
In writing the simplest of queries, all of them work except when I try to put the :parameter in the Criterion field in order to get a prompt for an input from the user. At that point, it always throws that SQL syntax error. This is starting to get quite frustrating to spend hours and hours on what appears to be a trivial matter. I do appreciate your response to this thread. I feel like there has to be some tick mark that needs to be checked or unchecked or perhaps some setting on the MySQL database itself that needs to be configured to allow this to work seamlessly.
Did some more researching. A bit of testing shows this may a problem from the past. Don’t understand why you have this problem in your LO version - should be OK there. Anyway the fix is in this post → Form got broken with Libreoffice Base version 6.1.5.2 and later [MySQL(ODBC)]
If this is a problem for you to do, there is an easier method in this post → How to unzip ODB file and edit the content.xml file to fix Firebird bug?. It states Firebird but the setting is the same.
Always be on the safe side. Before any modifications make a copy of the Base file!
It’s working now! Thanks a million for the help. I followed the instructions on the link (How to unzip ODB file and edit the content.xml file to fix Firebird bug?). I ran the one macro called “ensureConnection” that detects the datasource and then I ran the second macro called “Main”. By running both macros, this resolved the problem. Again, thank you for help.