SQLite IN operator on a Tcl list

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

SQLite IN operator on a Tcl list

Adam Jensen
Would it be very challenging to tweak the SQLite/Tcl code such that
something like this (below) would work?

-------------------------------------------------------
#!/usr/bin/env tclsh8.6

package require sqlite3
sqlite3 db "test.db"

db eval { CREATE TABLE ex_table (
    ex_id INTEGER NOT NULL,
    ex_name TEXT NOT NULL,
        PRIMARY KEY (ex_id) ) }

db eval { INSERT INTO ex_table (ex_id, ex_name)
    VALUES (1,"alice"),(2,"bob"),(3,"fred"),(4,"jan") }

#####vvvvv THIS CURRENTLY DOES NOT WORK vvvvv#####

set my_id_list [list 1 2 4]

set my_name_list [db eval { SELECT ex_name
    FROM ex_table WHERE ex_id IN $my_id_list }]

puts $my_name_list

#####^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^#####

db close
file delete "test.db"
-------------------------------------------------------
_______________________________________________
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: SQLite IN operator on a Tcl list

Gerry Snyder-4
This gets asked once in a while, and the answer is "No." The SQL statements
used in Tcl get compiled, and the compiled statement has to know how many
values are being looked at by IN.

At least up til now. Perhaps having row values will change this. We should
know soon.

Gerry Snyder

On Sun, Sep 25, 2016 at 10:21 AM, Adam Jensen <[hidden email]> wrote:

> Would it be very challenging to tweak the SQLite/Tcl code such that
> something like this (below) would work?
>
> -------------------------------------------------------
> #!/usr/bin/env tclsh8.6
>
> package require sqlite3
> sqlite3 db "test.db"
>
> db eval { CREATE TABLE ex_table (
>     ex_id INTEGER NOT NULL,
>     ex_name TEXT NOT NULL,
>         PRIMARY KEY (ex_id) ) }
>
> db eval { INSERT INTO ex_table (ex_id, ex_name)
>     VALUES (1,"alice"),(2,"bob"),(3,"fred"),(4,"jan") }
>
> #####vvvvv THIS CURRENTLY DOES NOT WORK vvvvv#####
>
> set my_id_list [list 1 2 4]
>
> set my_name_list [db eval { SELECT ex_name
>     FROM ex_table WHERE ex_id IN $my_id_list }]
>
> puts $my_name_list
>
> #####^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^#####
>
> db close
> file delete "test.db"
> -------------------------------------------------------
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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: SQLite IN operator on a Tcl list

Dominique Devienne
On Sun, Sep 25, 2016 at 8:54 PM, Gerry Snyder <[hidden email]>
wrote:

> [...] the compiled statement has to know how many values are being looked
> at by IN.
>
> At least up til now. Perhaps having row values will change this. We
> should know soon.


Only if you can bind them :)

And the SQLite-TCL could also use the carray extension,
but that would be a security hole I suppose. (unless the TCL runtime can
lookup
the list variable's name dynamically, in which case it wouldn't be the list
address
but a "safe surrogate" in that case. That would probably work. Probably no
one
thought of doing that yet, since eponymous vtables are relatively new)

Or [db eval  ...] recognizes it's used with a variable in a position that
needs
to be runtime-expanded textually, and thus cannot be prepared+bind, but
that would require knowing the context of bind placeholders, which is not
part
of the SQLite API, since there's no parse AST per-se for statements, part of
the public API. Also on my wish list that clashes with the "lite" part. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users