SQLite Forum

Novice question concerning tcl sqlite and eval options and reusing prepared statements

Novice question concerning tcl sqlite and eval options and reusing prepared statements

(1) By Gary (1codedebugger) on 2021-05-16 20:32:28 [link] [source]

I've used only the C API and CLI in the past and am trying to use the Tcl API now and am a bit confused about the concept of reusing prepared statements in Tcl API.

In the documentation, the form of eval is:
dbcmd  eval  ?options?  sql   ?array-name?  ?script?

The only option I saw was -withoutnulls.  If there are others, would you please tell me where their documentation can be located?

Regarding the caching of prepared statements, how is a prepared statement reset and new values of arguments/parameters bound?  I understand that regular Tcl substitution will handle the binding but I don't understand how using eval again will reuse a query previously run using eval as opposed to just creating a new one altogether; because eval sort of does it all--prepares, binds(or already bound by Tcl substitution before passed to the command), executes, and steps.

Also, is there any reason to use the TDBC extension?  I was reading over NadKarni's book on Tcl and see that in TDBC the prepared statement appears to be stored in a variable, if there are variable parameters then they are passed at execution of the statement which is coded explicitly.  Other than perhaps that, I don't see what it adds over the methods documented on the SQLite Tcl API documentation.

Thank you.

(2) By Gerry Snyder (GSnyder) on 2021-05-16 21:08:47 in reply to 1 [link] [source]

A partial response:

Nothing special is needed to re-use prepared statements. And no need to reset. By default 10 statements will be prepared and cached, and it is all automatic.

I had done a lot of stuff with Tcl and SQLite before TDBC came along, and I have not used it. If I did, it would probably be to be able to use the same code for SQLite and other DB systems. But it does look pretty neat.

Hope this helps, at least a little,

Gerry Snyder

(3.1) By Gary (1codedebugger) on 2021-05-17 00:16:39 edited from 3.0 in reply to 2 [link] [source]

Thank you. May I ask a specific follow-up please?

Is the proper method of preparing a statement that of passing the SQL in curly braces with the $var inside such that the variable substitution does not take place before it is passed to the eval command? Does that treat the $var something like a '?' in the C API and generate an equivalent prepared statement that will be used again when $var is a different value?

Or are prepared statements cached only after substitution such that there is not an equivalent in Tcl SQLite of a prepared statement with ?'s that can be reset and new values set through binding, whether automatic or manually coded?

For instance, regarding the SQLite documentation example of db1 eval {INSERT INTO t1 VALUES(5,$bigstring)} would there be a prepared statement with a '?" for $bigstring, such that, if the same statement was performed again when $bigstring was a different value, a new statement would not be prepared but the cached one would be reset and a new value bound?

I don't mean to be tedious or annoying, but am just not understanding how the way the Tcl expression is constructed impacts the way SQLite prepares statements for re-use.

Thank you.

(4.1) By Gerry Snyder (GSnyder) on 2021-05-19 23:42:15 edited from 4.0 in reply to 3.1 [source]


Your first answer is correct. The prepared statement is stored with the space for the open variables to be filled in.

If you need to be really tricky, you can put the statement in quotation marks, and variables with dollar sign will be filled in immediately (by Tcl), and those with colon will not be filled in until actually executed.

Of course, unless you execute a statement a skazillion times, you won't see any difference in the timing. Tcl is slow, but not that slow. Disk access time dominates all.


(5) By Larry Brasfield (larrybr) on 2021-05-19 23:47:24 in reply to 4.0 [link] [source]

That "really tricky" method may open a pathway for an SQL injection attack. When the "variables" appear within a brace-delimited query or DDL, '$'-prefixed identifiers become SQL parameters and are substituted with like-named TCL variable values via sqlite3_bind_*() calls by the SQLite TCL implementation of its eval subcommand. This is safer and (as Gerry notes) likely to be faster also.