What is the proper way to ensure that Tcl variables are treated as the correct type in SQLite?
This active session appears to be about the simplest scenario possible in setting number to 50 but it appears SQLite treats :number as a string, :number+0 and $number as an number.
If, instead, use incr nbr 50 then SQLite treats :nbr as a number.
Is there a way to "declare" the variable in Tcl such that SQLite will treat it as desired; or should every value be cast in the SQL expression?
I'd like to know how to make sure queries will always work as desired rather than just using cast on variables that "fail" to be recognized as desired while testing; and I've yet to recognize some pattern or best practice.
If all variable substitution is not treated as string and cast to a number in the SQL (except when testing reveals an issue), could a future SQLite version result in an instance in which it "just works" changing to the need to cast?
Thank you.
% package require sqlite3
3.43.2
% sqlite3 db
% set number 50
50
% db eval {select min(300, :number)}
300
% db eval {select min(300,:number+0)}
50
% db eval "select min(300,$number)"
50
% db eval {select min(300, cast(:number as integer))}
50
% incr nbr 50
50
% db eval {select min(300,:nbr)}
50
% db eval "select min(300,$nbr)"
50
% db eval {create table numbers (id integer, value integer);
insert into numbers values (1,300);}
% db eval {select min(value,:number) from numbers;}
300
As I recall, if you run the value through expr then it gets a numeric interpretation by SQLite, but you need to be a little careful to stop Tcl from just factoring the value out as a literal when testing:
set number 50
set number [expr {$number}]
db eval {select min(300, :number)}
This tends not to be a big problem when doing more production-oriented code as you put constants in the SQL code and only parameterise values that change (and those will quite possibly have a numeric nature anyway).
If you're really worried about it, put a CAST in your SQL.
At the bytecode level, the expr becomes sending the value through an operation called tryCvtToNumeric which gives values a numeric interpretation if they can have one. Literals default to having no interpretation. Formally, we prefer C code to not depend on the existing interpretations of values, but rather to just ask for the one they want, but the tclsqlite extension (called that to distinguish it from the underlying library) doesn't do that and never has.
It's more important when dealing with BLOB data. That's when you use @var instead of :var...