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 <[hidden email]>
> To: General Discussion of SQLite Database
> <[hidden email]>
> Subject: Re: [sqlite] Variables in statements
> [hidden email]>
> Content-Type: text/plain; charset=UTF-8
> On 9/3/15, Peter Haworth <[hidden email]> 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
> > 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
> [hidden email] >
> < <[hidden email]>
On 2015-09-04 06:09 PM, Peter Haworth wrote:
> Thanks for the info.
> Could I get round the single value limitation with something like:
> IN ((SELECT 'abc','def' FROM myTable))
Maybe what you intend is more like this:
IN (SELECT 'abc' UNION ALL SELECT 'def' UNION ALL SELECT 'ghi'... etc.)
IN (SELECT A FROM B)
- assuming table B contains a column A that already contains all the values you need to include in the IN.
> Also, in the LIKE example, looks like I don't need single quote delimiters
> around the pattern?
The quote delimiters enables passing the value within an SQLite
statement, it is not significant to the type of value or in any way
significant to the LIKE parsing.
You could as easily use an identifier or parameter sans quotes, but if
you wish to compare like-ness by giving a string directly in an SQL
statement, it has to be contained in single quotes. That is purely
because of string-passing semantics in general. This means the following
examples all do the same thing:
"SELECT A, 'abc%' FROM B WHERE C LIKE 'abc%';"
"SELECT A, :1 FROM B WHERE C LIKE :1;" passing the value: abc%
"SELECT A, D FROM B WHERE C LIKE D;" assuming this is a sub-select
within another query of which there is a column D that contains a value
(I may have misinterpreted what you meant, if so, apologies - feel free
to ask again)