On 9/3/15, Peter Haworth <pete at lcsql.com> wrote: > > SELECT * FROM myTable WHERE myKey IN (:1) > > If the value I supply to be used as :1 is a single integer, the SELECT > finds the correct rows. If the value is a comma separated list of > integers, e.g 1,2 the SELECT statement does not return any rows and no > error is returned. If I recode the SELECT to specify 1,2 instead of :1, > the correct rows are returned. > > Should the :1 form work when a list is supplied as its value?
No. Variables only work for single values, not lists. > > Similarly with a statement like this. > > SELECT * FROM myTable WHERE myText LIKE :1 > > I've tried various ways of implementing that with the following LIKE clause > and :1 values: > > LIKE :1 - '%abc%' > LIKE :1 - %abc% > LIKE ':1' - %abc% > LIKE '%:1%' - abc > > None of the above return any rows, but if I issue: > > SELECT * FROM myTable WHERE myText LIKE '%abc%' > > ... the correct rows are returned. > > I suspect this is a Livecode problem but wanted to check if what I am > trying to do is syntactically correct before reporting it as a bug. > I concur. This latter seems like a livecode problem. Similar things work in SQLite. See https://www.sqlite.org/src/artifact/0f0ee61?ln=295 for example. The example uses $like instead of :1, but they both work the same. -- D. Richard Hipp drh at sqlite.org
