Thanks for the info. Could I get round the single value limitation with something like:
IN ((SELECT 'abc','def' FROM myTable)) Also, in the LIKE example, looks like I don't need single quote delimiters around the pattern? > > Message: 4 > Date: Thu, 3 Sep 2015 12:54:46 -0400 > From: Richard Hipp <drh at sqlite.org> > To: General Discussion of SQLite Database > <sqlite-users at mailinglists.sqlite.org> > Subject: Re: [sqlite] Variables in statements > Message-ID: > <CALwJ= > Mwy6Z07bWxUNACk6uAv2d6NmM8s4ETGcNbULw3jXZHH3g at mail.gmail.com> > Content-Type: text/plain; charset=UTF-8 > > 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 > > > < <sqlite-users at mailinglists.sqlite.org>
