This question arose out of wondering how to limit the number of SQLite db eval {...}
statements to accomplish a task. I’m not sure it really matters how many there are but it has been asked on the SQLite forum a few times (one example here) whether or not it is possible to use values from a returning clause in a subsequent query within a CTE. A similar thought appears to apply to multiple SQL statements withing a single eval
I happened to see someone in a separate post add a {}
at the end of the db eval {...} {}
similar to opening all dictionary variables with dict with {}
.
The active Tcl session, below, is a very simple example showing three attempts to update a table and use the result of an earlier column given a variable name. The first shows that it won’t work without the suffixed {}
. The second shows the way I’ve been doing this sort of thing by using more than one db eval
and passing the value first to a Tcl variable. The third shows that it appears to work in one db eval
if the empty script is added.
My question is, Would you please explain how that empty script works? I thought it runs for each row returned by a query; but what happens when there are multiple queries?
And, is this a bad practice that may bring unwanted variables into scope/context, possibly overwriting variables explicitly declared in Tcl like dict with
can, and/or end up being less efficent than multiple db eval
statments; that is, if multiple db eval
statements are less efficient than mulitple queries within one statement.
Thank you for considering my question.
% package require sqlite3
3.45.3
% sqlite3 db ":memory:"
% db eval {create table t1 (id integer, value text);
insert into t1 values (1,'a'),(2,'b'),(3,'c'),(4,'d');
select * from t1;}
1 a 2 b 3 c 4 d
% db eval {
update t1 set value = 'B' where id = 2 returning id as changed_id;
select * from t1 where id = :changed_id }
2
% puts $changed_id
cant read "changed_id": no such variable
% set changed_id [db eval { update t1 set value = 'C' where id = 3 returning id}]
3
% lassign [db eval {select id, value from t1 where id = :changed_id;}] final_id final_value
% puts "$final_id $final_value"
3 C
% unset changed_id
% unset final_id
% unset final_value
% db eval {
update t1 set value = 'D' where id = 4 returning id as changed_id;
select id as final_id, value as final_value from t1 where id = :changed_id } {}
% puts "$changed_id $final_id $final_value"
4 4 D