sqlitetcl

SQLite Tcl Interface: Using db eval {...query(ies)...} followed by an empty script {}?

This question arose out of wondering how to limit the number of SQLite dbcmd 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 within a single eval

I happened to see someone in a separate post add an empty script {} at the end of the dbcmd 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 dbcmd eval and passing the value first to a Tcl variable. The third shows that it appears to work in one dbcmd eval if the empty script is added.

My initial question was, Would you please explain how that empty script works ... when there are multiple queries? However, ...

Added later: As it is documented in the Tcl SQlite Interface for eval, it appears the script is run for every row returned by each query in the eval. All the columns in the result rows are written (and overwritten at each run of the script) to Tcl variables of the same name. I was concerned that, when using a WITH with many CTEs, all those intermediate columns would be expanded into Tcl; but that appears inaccurate and only the final results are written. Thus, in a case in which there are multiple queries in one eval statement and each returns only a single row of a small number of columns that the subsequent queries may need, perhaps it is not so bad although possibly confusing.

Is this a bad practice for any other reason? Is it worth spending time on in it because it would be more efficient than using multiple dbcmd eval statements?

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

This first example shows that the RETURING column is not brought into Tcl and is not recognized in the SELECT.

% 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

This second example is how I've been handling similar instances using multiple eval statements.

% 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

This third example uses a single eval and the empty script and all the results columns are brought into Tcl and the SELECT recognizes the value of the RETURNING clause.

% 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
Solution

  • The tclsqlite extension package is really quite simple. It:

    1. binds the variables (with the help of a trivial parser),
    2. calls into the SQLite core to run the SQL for the first step (preparing the statement first),
    3. maps the result set back into Tcl (directly to Tcl variables or to a Tcl array if one is supplied),
    4. evaluates the callback body,
    5. calls SQLite to get the next step, jumping back to step 3 if there is another row, and
    6. cleans up the temporary resource handles.

    What the result set is and where it is issued from within the SQL you provide depends entirely on the SQLite engine. If you provide an empty callback body, it still gets executed for every step, but you only end up seeing the changes due to the last of those steps (unless you use variable traces).

    If the SQL doesn't have a result set (perhaps because it is a simple UPDATE) steps 3 to 5 get skipped. There's literally nothing to do in that case! This is a consequence of the natural way to implement such a processing loop.