On Wed, Feb 9, 2011 at 6:15 PM, Eric Smith <eas....@gmail.com> wrote:
> 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.
Hi Eric,
Thank you for that! I could definitelly wrap that proc up to at least
get the array to be in a separate, hidden, namespace or something like
that.
Thanks!
/Fredrik
--
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
