I have these variables in Tcl.
set insert {1 2 3 4 5}
set select {3 5}
I want SQL like this to be executed in SQLite.
insert into tab (col) values (1), (2), (3), (4), (5);
select * from tab where col in (3, 5);
How should change the statements in the below group so they can take
the parameters in the above group?
The best I know is to separate the SQL into several statements.
foreach insertI $insert {db eval {
insert into safe (col) values ($insertI);
}}
foreach selectI $select {db eval {
select * from safe where col = $selectI;
}}
But that is more complicated and could be slower.
Another option is to write the SQL before giving the command to SQLite.
In the interest of time, I have written only an unsafe example.
proc map {lambda list} {
set result {}
foreach item $list {
lappend result [apply $lambda $item]
}
return $result
}
set unsafeInsert [join [map {x {return "($x)"}} $insert] ", "]
set unsafeSelect "([join $select ", "])"
db eval "
create temporary table unsafe (col);
insert into unsafe (col) values $unsafeInsert;
select * from unsafe where col in $unsafeSelect;
"
I don't like either of these options, even if I safely escape everything
in the second example. Does a better way already exist?
Attached is a coherent Tcl program containing all of the above examples.
Cordially,
Ramarro
#!/usr/bin/env tclsh
package require sqlite3
proc map {lambda list} {
set result {}
foreach item $list {
lappend result [apply $lambda $item]
}
return $result
}
set insert {1 2 3 4 5}
set select {3 5}
# This version could be unsafe if I escaped everything properly.
# I am hoping that somebody else has already written that.
set unsafeInsert [join [map {x {return "($x)"}} $insert] ", "]
set unsafeSelect "([join $select ", "])"
sqlite3 db
db eval "
create temporary table unsafe (col);
insert into unsafe (col) values $unsafeInsert;
select * from unsafe where col in $unsafeSelect;
" {
puts "unsafe:$col"
}
# Here is a safe way that is less convenient for me and that could be slower.
db eval {create temporary table safe (col);}
foreach insertI $insert {db eval {insert into safe (col) values ($insertI);}}
foreach selectI $select {db eval {select * from safe where col = $selectI;} {
puts "safe:$col"
}}
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
