QBSelect LIKE conditions truncate the specified search value

Discuss all problems you have with Servoy here. It might help to mention the Servoy version and Operating System version you are using

QBSelect LIKE conditions truncate the specified search value

Postby rossent1327331687 » Thu Sep 15, 2016 4:00 pm

Hi Servoy,

I just found a serious issue with the QBSelect. Basically the problem is that in the resulting SQL query which is executed against the database server the search values for LIKE conditions are being truncated down to the length of the column.
For example, if a table column length is 5 characters and we need to search for records which contain the string 'ABCD' in this column the query must be "select * from table where column like '%ABCD%'". Here is how the QBSelect will look like:

Code: Select all
    var qry = datasources.db.some_database.some_table.createSelect();
    qry.where.add(qry.columns.some_column.like('%ABCD%'));
    var fs = datasources.db.some_database.some_table.getFoundSet();
    fs.loadRecords(qry);


However the actual query which will be executed in the database is going to be: "select * from table where column like '%ABCD'" - notice the truncated search value which will result in the search operation "starts with ..." instead of "contains ...".

It took me a while to figure out what was causing the odd and incorrect behavior in our application because this used to work before and everything in our code is correct. Only when I started to profile the actual SQL statements which are submitted by Servoy and executed in the database the problem was revealed. I hope that Servoy will fix this nasty bug quickly because it is causing a huge problem for us. Hopefully this helps other users which may be wondering what is causing the incorrect of LIKE queries with QBSelect.

Here is the case which I submitted in case other users are affected by this issue: https://support.servoy.com/browse/SVY-10403
Rossen Totev
Abelisk Software
rossent1327331687
 
Posts: 6
Joined: Mon Jan 23, 2012 5:14 pm

Re: QBSelect LIKE conditions truncate the specified search v

Postby Bernd.N » Sat Sep 17, 2016 11:32 am

As a quick fix, would it be possible to enlarge the column length on your side?
I know that might be a problem as it could affect a lot of columns, but when it creates a huge problem for you, that could be the fastest fix.
Or not to use QBSelect in this cases and use directSQL, when it is possible for you.
Or, finally, you could have a look if LIKE is really needed in your case or could be substituted. Because when the column width is so small, you might know all possible value combinations.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany


Return to Discuss possible Issues and Bugs

Who is online

Users browsing this forum: No registered users and 8 guests

cron