We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

SQL Error in Base

asked 2021-05-08 19:06:49 +0200

jodybingo gravatar image

Hello, Using 7.1.2.2 on W10 with MySql 8.0
I have a search form which is working fine (almost to my liking). The mainform data comes from the filter table and the subform's data comes from SQL I had help on quite some time ago. When I open the SQL through the properties panel it displays fine in design view. When I click on View in SQL it tells me I have an error and I was hoping to understand where LO says the error is (as I noted the form works fine). I also wanted to add an ORDER BY and when I do the form can no longer load the data.
SELECT *
FROM "client"
WHERE (UPPER("first") LIKE UPPER(CONCAT('%',:Var_FN,'%')) OR (:Var_FN IS NULL))
AND (UPPER("last") LIKE UPPER(CONCAT('%',:Var_LN,'%')) OR (:Var_LN IS NULL))
Thanks!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2021-05-08 19:52:49 +0200

Ratslinger gravatar image

updated 2021-05-08 19:58:08 +0200

Hello,

You need to consider that the Query GUI was originally developed for HSQLDB embedded and based mostly upon that syntax. When you place something in there it doesn't like it complains with an error. You could report as a bug but consider all the different databases and syntax. Can't say what is the precise item causing the problem. You can open in SQL (my preferred method always) by right clicking the query name and selecting Edit in SQL View.... I had no problems with this.

I also had no problem (again in SQL view) with adding an Order by clause on to the SQL. The form opened up just fine in the correct sequence. Tried with asc and desc.

Tested on Ubuntu 20.04.x with LO v7.1.3.2 using JDBC. Using a Native connector (direct) is another problem.

edit flag offensive delete link more

Comments

I found out why I am getting the error. LO doesn't like UPPER. It prefers UCASE, but UCASE doesn't work with MySQL. Jody

jodybingo gravatar imagejodybingo ( 2021-05-24 15:58:35 +0200 )edit

@jodybingo,

I have no problem using UPPER in LO with MySQL.

image description

Worked with all upper, lower and mixed case data.

Ratslinger gravatar imageRatslinger ( 2021-05-24 16:56:07 +0200 )edit

hmm. strange.

jodybingo gravatar imagejodybingo ( 2021-05-30 02:30:42 +0200 )edit

@jodybingo,

One difference is you use Win 10. Don't want to load MySQL on my Win 10. Dumped it a couple of years ago as my production DB.

But you can see it works on Linux.

Edit:

Also note that my answer does state I am using JDBC. Don't know your connection type.

Ratslinger gravatar imageRatslinger ( 2021-05-30 02:49:31 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-05-08 19:06:49 +0200

Seen: 21 times

Last updated: May 08