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