Sam Thursfield <sss...@...> writes:
>
> Hello everyone,
> I have a question which I'm hoping someone who knows a bit about
> SQlite's internals can answer easily.
>
> I have a query such as this:
>
> SELECT * FROM local INNER JOIN foreign ON local.foreign_id =
> foreign.id WHERE foreign.id IN (4, 3, 6, 7) ORDER BY local.name;
>
> Here the list of numbers in the WHERE clause is subject to change. How
> possible do you think it is to have this as a prepared query?
>
> SELECT * ... WHERE foreign.id IN ($list) ...;
>
> Bearing in mind:
> - the length of the list is completely variable.
> - binding text results in SELECT * ... WHERE foreign.id IN ("4. 3, 6,
> 7") ...; which of course doesn't work.
> - binding seems to set specific values in the VM, so my instinct says
> this won't be possible because the query will change too much based on
> the list of ids - it will need too much recompiling each time.
>
> However, I thought I would throw this problem out to people who knew
> sqlite in more depth than me to see if there's an easy answer that I'm
> missing.
>
> Thanks in advance for any help!
> Sam
If your list values are relatively small you could use a set
of bits for your IN clause and use
bitwise: WHERE ((1<<foreign.id) AND ?1) != 0 instead, where ?1
encodes the required elements as bits set to 1.
But haven't had time to try this out, and it's probably limited
to 0 <= foreign.id < 64 since SQLite uses 8-byte INT arithmetic.
Regards,
Mike
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
