SQLite Forum

Prepared Statements
The sqlite3_exec() function is just a convenience interface that allows parameter free SQL queries to be executed via a single call. It calls the sqlite3_prepare(), sqlite3_step() and sqlite3_finalize() routines. For each row returned from the query, the callabck function provided to sqlite3_exec() is called (and this function may indicate if it wishes to receive further rows or not).

The purpose of the sqlite3_prepare() function is to "compile" the SQL text into "SQL engine code" - the "SQLite program" - while considering the current schema. This requires substantial effort, which can be avoided if the application knows that it will need to run exactly the same query multiple times and "hangs on" to the prepared statement.

If the difference between two queries is restricted to one or more values, then the effort of preparing several of these queries can be saved by substituting an SQL parameter in place of these values. Instead of sqlite3_prepare() compiling a constant into the SQLite program, it will compile a variable reference instead. The value of each variable can/must be set by using the sqlite3_bind() family of functions, if something other than the default of NULL is desired.

Binding a parameter does not change the text of the query. For applications that suffer from memory loss, the sqlite3_expanded_sql() interface will determine and return the "effective text" of the query, which is neither stored nor used.