[sqlite] Variables in statements
On Thu, 03 Sep 2015 16:44:50 + Peter Haworth wrote: > The statement I'm using is: > > 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 you think about the SQLite C interface that Livecode must be using, you'll see why lists don't work in this context. The data in your list are conveyed to SQLite as a pointer to the list data. The caller indicates how that pointer is to interpreted through the bind function used. (http://www.sqlite.org/c3ref/bind_blob.html) Livecode must bind the parameter as one of those scalar types, probably integer. SQLite then interprets the data as (say) an integer whose bytes are the ASCII encoding of the characters '1' ',' '2' ',' [...] which is probably a very big number, and one that does not match any myKey values. Hence no row returns and no error produced. > I suspect this is a Livecode problem If Livecode has a way to prevent passing a "list" type as an integer parameter then, yes, I'd say so. If not, they'd have to call it a pibcak problem and say, "well, don't do that". :-) --jkl
[sqlite] Variables in statements
I use a high level language to write my db applications (Livecode). It permits the use of replacement opertaors in sql statements, e.g. "SELECT * FROM myTable WHERE myKey=:1". I guess that's a standard way of doing things in SQLite. I'm having some issues with this and not sure whether it's a SQLite or Livecode problem. The statement I'm using is: 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? 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. Thanks
[sqlite] Variables in statements
On 9/3/15, Peter Haworth 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
