Page 1 of 1
[Solved] Query with user input not working
Posted: Sat Jul 23, 2016 1:07 am
by oneindelijk
If I try this I get all records from my table:
Code: Select all
WHERE `ProjectName` LIKE '%' || :Project_Name || '%'
When I hardcode the value that I would enter in the popup asking for the value of Project_Name like so:
Code: Select all
WHERE `ProjectName` LIKE '%Design%'
The Query (of course,) works
What am I doing wrong ?
Re: Query with user input not working
Posted: Sat Jul 23, 2016 1:22 am
by Villeroy
Works for me with a recent version of OpenOffice or LibreOfficen using the built-in HSQL database engine.
Re: Query with user input not working
Posted: Sat Jul 23, 2016 2:13 am
by Sliderule
oneindelijk:
You have not told us which database back-end you are connected to for your Query. You did indicate that the field name is: `ProjectName`, therefore, the concatenation syntax that is used by your database back-end should be used.
For example, while there is a SQL Standard, and, both HSQL and H2 follow that standard ( and use || for concatenation ). But, some databases, such as MySQL, Microsoft Access, etc have elected to create their own rules, and, not follow the industry standards.
If you want some help, please be sure to let us know what database back-end you are using.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Re: Query with user input not working
Posted: Mon Jul 25, 2016 4:31 pm
by oneindelijk
The Backend Is MySQL 5.7 ...
Re: Query with user input not working
Posted: Mon Jul 25, 2016 4:36 pm
by oneindelijk
I've tried this and it works !:
SELECT `idProject`, `ProjectName` FROM `TeslaHQ`.`Projecten` AS `Projecten` Where `ProjectName` like concat('%',:test,'%')
Re: [Solved] Query with user input not working
Posted: Mon Jul 25, 2016 4:44 pm
by Sliderule
MySQL does not support ( allow ) the SQL standard of
|| for string concatenation. However, it does support a function
CONCAT, and, no reasonable limit to the number of parameters . . . so . . . you can include 2 or 20 strings for concatenation. Since, MySQL wants text strings surrounded by single quotes, and, table, column, view names surrounded by
` :
Code: Select all
Where `MY_STRING_COLUMN` LIKE CONCAT('%', :Enter_Text_Search_String, '%')
Sliderule
Thanks to add
[Solved] in your
1st post Subject (edit button top right) if this issue has been resolved.