Fredrik Karlsson wrote:
> package require sqlite3
> sqlite3 db :memory:
> db eval {create table a (id INTEGER);}
> db eval {insert into a values (1);}
> db eval {insert into a values (2);}
> db eval {select * from a where id in (1,3);} vals {parray vals}
> vals(*) = id
> vals(id) = 1
> set alist [list 1 3]
> 1 3
> db eval {select * from a where id in $alist;} vals {parray vals}
> near "$alist": syntax error
> --------------
This implies that the manner in which $alist gets expanded should be
sensitive to the SQL context in which the expansion happens (and also,
for the purposes of backward compatibility, to the value of the
variable iteslf).
Unless I'm mistaken that would require pushing the expansion logic down
much further into sqlite, and probably would still fail in a number of
cases.
So I doubt you'll get much traction there, especially since this can be
pretty easily done from your application.
Here's an option off the top of my head:
proc qSqlList {alistname} {
set magic_array_name _qSql_${alistname}_arr
upvar $alistname alist $magic_array_name alist_arr
#assert {[string is list $alist]} ;# or whatever your infra provides
array unset alist_arr
set i 0
set out [list]
foreach item $alist {
set alist_arr($i) $item
lappend out \$${magic_array_name}($i)
incr i
}
return ([join $out ,])
}
So your call becomes:
db eval "select * from a where id in [qSqlList alist]" vals {parray vals}
SQLite does the expansion on the underlying list values with the proper
sqlite3_bind_* calls etc.
The proc isn't 100% ideal because:
1. it creates this magic array in the caller's scope (not the prettiest
thing in the world), and
2. for that reason it disallows dumb copies of the return value to float
around. You need to make the sqlite call close to where you do the
quoting.
Still, it might be good enough for your purposes. Or maybe you can
expand on the idea, wrap it up into a cleaner interface, and go from there.
Eric
--
Eric A. Smith
Money is the root of all wealth.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
