Dynamic SQL WHERE and LIKE search

Classic Servoy
1

Dear Servoyians,

I’m looking for a way to do a dynamic SQL search at the WHERE and LIKE option.
In this case I like to select te columnname in a combobox and have this as WHERE option, and same for the LIKE search criteria…

This is how I tought it could be, but does not work…

var sqlWhere = globals.SearchFieldOption ;
var sqlLike = globals.SearchLetterOption ;
var maxReturedRows = 200;
var query = "SELECT"+ 
" contactid "+
" FROM contact "+
" WHERE sqlWhere " +
" LIKE sqlLike ";

Hope this make sense…?

2

It does make sense in general, but the String concat is wrong. This

var query = “SELECT”+
" contactid "+
" FROM contact "+
" WHERE sqlWhere " +
" LIKE sqlLike ";

will lead to this query:

SELECT contactid from contact where sqlWhere like sqlLike

What you want is this:

var query = 'SELECT contactid FROM contact WHERE ' + sqlWhere + ' LIKE \'%' + sqlLike + '%\''

Note that I put %% around your sqlLike. I don’t know if you have done that already, but with a LIKE you have to use either a % at the beginning, the end or both, depending on what you want to find. When searching for Strings (using LIKE you can only search for strings), you also have to make sure that your criteria is nested in '.

My query will lead to

SELECT contactid FROM contact WHERE afield LIKE '%avalue%'

and that should work. It will find all occurences of afield that contain avalue.

Patrick

3

patrick:

var query = 'SELECT contactid FROM contact WHERE ' + sqlWhere + ' LIKE \'%' + sqlLike + '%\''

Cool! :D Works great! Thanks Patrick!