Prepared Statements
Once a database connection is established, we can start to execute SQL
commands. This is normally done by preparing and stepping through
statements. Statements are held in
sqlite3_stmt data structures.
Statement Life Cycle
The life cycle of a prepared statement is a bit complex. Unlike database connections, which are typically opened, used for some period of time, and then closed, a statement can be in a number of different states. A statement might be prepared, but not run, or it might be in the middle of processing. Once a statement has run to completion, it can be reset and re-executed multiple times before eventually being finalized and released.
The life cycle of a typical sqlite3_stmt looks something like this
(in pseudo-code):
/* create a statement from an SQL string */
sqlite3_stmt *stmt = NULL;
sqlite3_prepare_v2( db, sql_str, sql_str_len, &stmt, NULL );
/* use the statement as many times as required */
while( ... )
{
/* bind any parameter values */
sqlite3_bind_xxx( stmt, param_idx, param_value... );
...
/* execute statement and step over each row of the result set */
while ( sqlite3_step( stmt ) == SQLITE_ROW )
{
/* extract column values from the current result row */
col_val = sqlite3_column_xxx( stmt, col_index );
...
}
/* reset the statement so it may be used again */
sqlite3_reset( stmt );
sqlite3_clear_bindings( stmt ); /* optional */
}
/* destroy and release the statement */
sqlite3_finalize( stmt );
stmt = NULL;The prepare process converts an SQL ...