SQLite and bind variables

Usually, an application that stores its data in a database issues a lot of similar SQL statements:

char sql_buf[100];

int a=get_some_value(5)
sprintf(sql_buf, "insert into foo values (%d)", a);
sql_stmt(sql_buf);

a=get_some_other_value(99);
sprintf(sql_buf, "insert into foo values (%d)", a);
sql_stmt(sql_buf);

a=42;
sprintf(sql_buf, "insert into foo values (%d)", a);
sql_stmt(sql_buf);

In this example, three sql statements are executed. The problem is, each statement has to be parsed once, although they're similar. Bind variables are used to reduce the parsing overhead. This is done by parsing the statement once with a place holder where the statement's variable part is. Usually, those place holders are indicated with a question mark (?) or a colon followed by an identifier (:bar). SQLite happens to understand both of them. With bind variables, the example above becomes:

parsed_sql_stmt stmt = parse_sql(insert into foo values (?));

int a=get_some_value(5)
exec_stmt(stmt, a);

a=get_some_other_value(99);
exec_stmt(stmt, a);

a=42;
exec_stmt(stmt, a);

The parsing has been reduced to one parse.

It must be pointed out that the place holders can only go into the value(?,?...) or the where x=? and y=? part of the SQL statement. It is not possible to do, for example, this: insert into ? values (4,5).

SQLite uses sqlite3_prepare to parse a statement, sqlite3_bind_* to bind a value to a place holder and sqlite3_step to execute a statement. Additionally, after executing a statement, it must be reset with sqlite3_reset.

Here's a small demonstration of bind variables in SQLite.