[Solved] Query with user input not working

Creating tables and queries
Post Reply
oneindelijk
Posts: 7
Joined: Wed Sep 03, 2014 9:31 am

[Solved] Query with user input not working

Post 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 ?
Last edited by floris v on Mon Jul 25, 2016 6:44 pm, edited 2 times in total.
Reason: Added green check mark, floris v, moderator
OpenOffice 5.1 on Ubuntu 16.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query with user input not working

Post by Villeroy »

Works for me with a recent version of OpenOffice or LibreOfficen using the built-in HSQL database engine.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Query with user input not working

Post 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.
oneindelijk
Posts: 7
Joined: Wed Sep 03, 2014 9:31 am

Re: Query with user input not working

Post by oneindelijk »

The Backend Is MySQL 5.7 ...
OpenOffice 5.1 on Ubuntu 16.04
oneindelijk
Posts: 7
Joined: Wed Sep 03, 2014 9:31 am

Re: Query with user input not working

Post by oneindelijk »

I've tried this and it works !:

SELECT `idProject`, `ProjectName` FROM `TeslaHQ`.`Projecten` AS `Projecten` Where `ProjectName` like concat('%',:test,'%')
OpenOffice 5.1 on Ubuntu 16.04
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Query with user input not working

Post 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.
Post Reply