Wildcard search in a parameter input query not working in LO Base with Mariadb? Help!

[rherbert]

I set up a reference library with LO Base in an Embedded HSQLDB database. In my queries I used

SELECT `Reference_ID`, `Year`, `Title`,`Citation_Info`, `File_name`, `Link`
 FROM `Library`.`tbl_reference` `tbl_reference`
 WHERE `Title`
 LIKE ( '%' || :Please_Enter_a_word_or_phrase || '%' )
 ORDER BY `Year` DESC

This worked well with Titles entered into Text[VARCHAR_IGNORECASE], Text[VARCHAR], and Memo[LONGVARCHAR].

I input the database into mariadb using LO Base so that I could set up a Master-Master Replication. The copying and pasting of tables, forms and queries went well with some work to reset the datasource.

However, the → LIKE ( ‘%’ || :Please_Enter_a_word_or_phrase || ‘%’ ) ← does not seem to be recognized such a parameter is asked for but the word/phrase entered is not recognized and the query results in a blank table. I had to switch to entering into Memo[MEDIUMTEXT] and Memo[LONGTEXT] to accommodate Mariadb.

When the query is run through a Form to visualize results: “Message: The cursor points to before the first or after the last row.”

What is wrong … or what will work to replace the LIKE statement?

[Wanderer]

Use Concat instead of the pipes.

LIKE CONCAT( '%', :word_or_phrase, '%' )

By default, MariaDB treats || as a synonym for the OR logical operator.

is quoted from this link:
https://database.guide/2-ways-to-concatenate-strings-and-numbers-in-mariadb/

[rherbert]

Thank you!!! I have searched for days trying to resolve this. Thank you!

However, an odd problem cropped up. The query is working fine in a form that is used to visualize search results.

But when I go into edit the query I can not execute, save or switch design view to view/edit SQL. The following error occurs.

At this point this is an inconvenience since the query is working in my forms, but I will need to edit my query sooner or later.

Any suggestions? I am using LibreOffice version 7.4.3.2 with OpenSuse Tumbleweed and my Mariadb is server version 10.10.2

Thank you.

[Wanderer]

@rherbert

Any suggestions

Actually I never use this Editor, but open my query im SQL-Mode. Never had problems there. But one point to note: The use of :param is not possible when you use “direct-SQL” because then the parameter is not replaced by Base.

[rherbert]

I had not thought about selecting “Edit in SQL View” instead of “Edit”. This definitely gets me access to work with the Query SQL. But, the error when trying to use the other “Edit” selection remains.
Thanks for the workaround!

[iplaw67]

That error would indicate that the parser is incapable of analyzing the parameters being passed to it.

https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sdb_1_1XSingleSelectQueryAnalyzer.html#a122da31c61b65134873d903ff2139592

Whether it should be able to, and therefore a bug, would need to be determined.

[RobertG]

@rherbert : Switch “Design view → off”.
Code like this for a little example will run with MariaDB and direct connection:

SELECT `ID`, `Forename`, `Surname` 
FROM `libretest`.`Person` AS `Person` 
WHERE `Forename` LIKE CONCAT( '%', :please_enter_word_or_phrase, '%' )

Tried to enter this code through GUI and get many errors.
Have set the right code in SQL. Works.
Switched back to Design mode on and and it looks like your code but gives an error.
Couldn’t switch back to Design mode off. Have to delete
LIKE CONCAT( '%', :please_enter_word_or_phrase, '%' )

This is a bug of the GUI-editor.

[rherbert]

Thanks @RobertG. I have the SQL working for my queries working without pipes and accessing them through “Edit in SQL View”. However, a similar problem occurs in reverse. I discovered the reverse by restructuring a few tables and relationships. When I then went in to adjust my queries accordingly by selecting “Edit”, I received the error below and could not enter Design Mode. In this regard, the bug may have more to it.
Richard

[RobertG]

@rherbert : This is the right warning: GUI couldn’t parse the code, so it opens in SQL mode. There are lot of different codes and GUI should know most of the Code for internal HSQLDB and Firebird. So no wonder there is code, which isn’t known for the GUI.

Problem I saw: You could switch back from SQL-view to design view and then the error appears, which you had posted at the start of this thread. There should appear the same message as you get: “SQL command could not be parsed”.