Variables in statements

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Variables in statements

phaworth
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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Variables in statements

Richard Hipp-3
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 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Variables in statements

James K. Lowden
In reply to this post by phaworth
On Thu, 03 Sep 2015 16:44:50 +0000
Peter Haworth <[hidden email]> 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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users