API

The sqlite3 API

The API for sqlite3 is based on the C language and has very nice reference documentation including a wiki. Although the base API is based on C, there are wrappers for many other languages. The examples here focus on C++.

As stated in http://www.sqlite.org/capi3ref.html#sqlite3_stmt, the life of a statement object goes something like this:

  1. Create the object using sqlite3_prepare_v2() or a related function.
  2. Bind values to host parameters using sqlite3_bind_* interfaces.
  3. Run the SQL by calling sqlite3_step() one or more times.
  4. Reset the statement using sqlite3_reset() then go back to step 2.
    Do step 4 zero or more times.
  5. Destroy the object using sqlite3_finalize().

Examples

I've put up some code with a few examples:

api_1 a C++ example based on http://www.sqlite.org/quickstart.html and uses sqlite3_exec. Use test_api_1_1.sh to test the api_1 program.

api_2 is based on http://www.sqlite.org/quickstart.html, but follows the 5 steps above. It uses sqlite3_prepare_v2 and sqlite3_step instead of sqlite3_exec. Due to the use of sqlite3_exec, a "callback" routine "process_row" is used to handle each result returned from the SQL statement. The callback simply prints out each value using sqlite3_column_text (regardless of any true type for the column). Use test_api_2_1.sh to test the api_2 program.

api_3 is similar to api_2 in design, although the SQL is different. This example demonstrates the use of sqlite3_bind_text to bind a value to the prepared SQL statement. If both the api_1 and api_2 test scripts have been run, then use test_api_3_1.sh to test the api_3 program.