CONTAINS being used instead of LIKE in places it shouldn't be used in database manager queries

Wappler Version : 5.5.2
Operating System : Mac OS 12.6.1
Server Model: PHP
Database Type: MySQL
Hosting Type: Docker

What do you think should happen?

Selecting “contains” from the dropdown for query conditions uses “like” in the query and in the “operation”: “like” setting

What actually happens?

It’s using CONTAINS in the query and “operation”: “contains” which causes an error and you have to manually change them in code view.

How to reproduce

Create a query and select “contains” from the condition dropdown.

1 Like

I think most of them are broken. begins with, doesn’t begin with, contains, doesn’t contain, is empty, and is not empty all use LIKE for me and throw an error if I try using them. I’m using 5.5.2 and MariaDB + PHP.

1 Like

I’ve had similar issues with ‘CONTAINS’ not behaving as it should, when replaced with ‘LIKE’ it also sometimes reverts by itself to ‘CONTAINS’. I am also using MariaDB.

Contains is just the name of our internal operation.

Why do you think it is broken @alpine are you getting any errors?

@TMR It reverts to CONTAINS on each save so if you change the query, you have to go back to code view and change the CONTAINS to LIKE and “operation”:“contains” to “operation”:“like”

So is there any error message you are getting using the Contains operator in the query builder?

Yes, that’s how I knew to go looking in code view.

code: "42000", file: "/var/www/html/dmxConnectLib/lib/db/Connection.php", line: 112,…}

code
:
“42000”
file
:
“/var/www/html/dmxConnectLib/lib/db/Connection.php”
line
:
112
message
:
“SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘contains CONCAT(’%’, ?, ‘%’) ESCAPE ‘!’))) ) AS dt’ at line 1”
trace
:
#0 /var/www/html/dmxConnectLib/lib/db/Connection.php(112): PDO->prepare(‘SELECT COUNT()…’)\n#1 /var/www/html/dmxConnectLib/modules/dbconnector.php(240): lib\db\Connection->execute('SELECT COUNT()…’, Array)\n#2 /var/www/html/dmxConnectLib/lib/App.php(204): modules\dbconnector->paged(Object(stdClass), ‘qCases’, Array)\n#3 /var/www/html/dmxConnectLib/lib/App.php(144): lib\App->execSteps(Object(stdClass))\n#4 /var/www/html/dmxConnectLib/modules/core.php(102): lib\App->exec(Object(stdClass), true)\n#5 /var/www/html/dmxConnectLib/lib/App.php(204): modules\core->condition(Object(stdClass), ‘’, NULL)\n#6 /var/www/html/dmxConnectLib/lib/App.php(144): lib\App->execSteps(Object(stdClass))\n#7 /var/www/html/dmxConnectLib/lib/App.php(133): lib\App->exec(Object(stdClass), false)\n#8 /var/www/html/dmxConnectLib/lib/App.php(111): lib\App->exec(Object(stdClass))\n#9 /var/www/html/dmxConnect/api/cases/queryCases.php(8): lib\App->define(Object(stdClass))\n#10 {main}”

@Teodor, Upon further investigation, it appears that CONTAINS in the query works as well as LIKE, it’s the “operation”: “contains” that causes the error. If I leave CONTAINS in the query and change “operation”: “contains” to “operation”: “like” then the error is gone and the query works

I see that its a visual thing now, it does set it to “begins with” in the code but it’s still not usable for me. Whenever I add a “begins with” condition it breaks everything else and this appears at the bottom of the API return page instead of the data after debug is turned on:

"getInfo":{"options":{"connection":"uoserach","sql":{"type":"select","distinct":false,"columns":[{"table":"courseinfo","column":"*","field":"*"}],"table":{"name":"courseinfo"},"joins":[],"wheres":{"condition":"OR","rules":[{"condition":"AND","rules":[{"id":"courseinfo.course_code","field":"courseinfo.course_code","type":"string","operator":"begins_with","value":"1","data":{"table":"courseinfo","column":"course_code","type":"text","columnObj":{"type":"text","maxLength":65535,"primary":false,"nullable":false,"name":"course_code"}},"operation":"starts with"}],"conditional":null}],"conditional":null,"valid":true},"orders":[],"params":[{"operator":"begins_with","type":"expression","name":":P1","value":"1","test":""}],"query":"SELECT *\nFROM courseinfo\nWHERE course_code STARTS WITH :P1 \/* 1 *\/","sort":null,"dir":null},"test":true},"query":"SELECT * FROM `courseinfo` WHERE ((`course_code` starts with CONCAT(?, '%') ESCAPE '!'))","params":[{"id":"courseinfo.course_code","field":"courseinfo.course_code","type":"string","operator":"begins_with","value":"1","data":{"table":"courseinfo","column":"course_code","type":"text","columnObj":{"type":"text","maxLength":65535,"primary":false,"nullable":false,"name":"course_code"}},"operation":"starts with"}]}

Query builder:

for info, i have tested this under node and everything works as it should so if there is a bug it seems PHP or database type specific

We found the issue, it was indeed a bad mapping of contains that we added in Wappler 5.5.2 and it gives errors in the classic server models like PHP, ASP and ASP.NET. In NodeJS it is all fine.

It will be fixed in the next update - probably tomorrow.

1 Like

Fixed in Wappler 5.5.3

This topic was automatically closed after 46 hours. New replies are no longer allowed.