Documentation Source Text

<p>
  Early versions of SQLite were very easy to learn since they only
  supported 5 C/C++ interfaces.  But as SQLite has grown in capability
  over the years, new C/C++ interfaces have been added so that now there
  are over 150 distinct APIs.  This can be overwhelming to a programmer
  new to SQLite.
  Fortunately, most of the C/C++ interfaces in SQLite are very specialized
  and never need to be used in most applications.  Despite having so many
  entry points, the core API is still relatively simple and easy to code to.
  This article aims to provide all of the background information needed to
  easily understand how SQLite works.
</p>

<p>
  A separate document, [capi3ref | The SQLite C/C++ Interface],
................................................................................
  <li> The [database connection] object: sqlite3 </li>
  <li> The [prepared statement] object: sqlite3_stmt </li>
</ul></p>

<p>
  Strictly speaking, the [prepared statement] object is not required since
  the convenience wrapper interfaces, [sqlite3_exec] or
  [sqlite3_get_table], that encapsulate and hide the prepared statement
  can be used instead.  Nevertheless, and understanding of
  [prepared statements] is needed to make full use of SQLite.
</p>

<p>
  The [database connection] and [prepared statement] objects are controlled
  by a small set of C/C++ interface routine listed below.
................................................................................
  <li> [sqlite3_column_int | sqlite3_column()] </li>
  <li> [sqlite3_finalize()] </li>
  <li> [sqlite3_close()] </li>
</ul></p>

<p>
  The six C/C++ interface routines and two objects listed above form the core
  functionality of SQLite.  The developer who understands
  what these APIs and objects
  do will have a good foundation for using SQLite.
</p>

<p>
  Note that the list of routines is conceptual rather than actual.
  Many of these routines come in multiple versions.
  For example, the list above shows a single routine
  named [sqlite3_open()] when in fact there are three separate routines
................................................................................

<tr><td valign="top" align="right">[sqlite3_open()]</td>
<td valign="top">
  This routine 
  opens a connection to an SQLite database file and return a
  [database connection] object.  This is often the first SQLite API
  call that an application makes and is a prerequisite for most other
  SQLite APIs.



</td>

<tr><td valign="top" align="right">[sqlite3_prepare()]</td>
<td valign="top">
  This routine
  converts SQL text into a [prepared statement] object and return a pointer
  to that object.  This interface requires a [database connection] pointer
................................................................................
<p>
  The [sqlite3_exec()] interface is a convenience wrapper that carries out
  all four of the above steps with a single function call.  A callback
  function passed into [sqlite3_exec()] is used to process each row of
  the result set.  The [sqlite3_get_table()] is another convenience wrapper
  that does all four of the above steps.  The [sqlite3_get_table()] interface
  differs from [sqlite3_get_table()] in that it stores the results of queries
  in help memory rather than invoking a callback.
</p>

<p>
  It is important to realize that neither [sqlite3_exec()] nor
  [sqlite3_get_table()] do anything that cannot be accomplished using
  the core routines.  In fact, these wrappers are implemented purely in
  terms of the core routines.
................................................................................
  <li> [sqlite3_reset()] </li>
  <li> [sqlite3_bind_int | sqlite3_bind()] </li>
</ul></p>

<p>
  After a [prepared statement] has been evaluated by one or more calls to
  [sqlite3_step()], it can be reset in order to be evaluted again by a
  single call to [sqlite3_reset()].
  Using [sqlite3_reset()] on an existing [prepared statement] rather
  creating a new [prepared statement] avoids unnecessary calls to
  [sqlite3_prepare()].
  In many SQL statements, the time needed
  to run [sqlite3_prepare()] equals or exceeds the time needed by
  [sqlite3_step()].  So avoiding calls to [sqlite3_prepare()] can result
  in a significant performance improvement.

<p>
  Early versions of SQLite were very easy to learn since they only
  supported 5 C/C++ interfaces.  But as SQLite has grown in capability
  over the years, new C/C++ interfaces have been added so that now there
  are over 150 distinct APIs.  This can be overwhelming to a programmer
  new to SQLite.
  Fortunately, most of the C/C++ interfaces in SQLite are very specialized
  and never need to be used.  Despite having so many
  entry points, the core API is still relatively simple and easy to code to.
  This article aims to provide all of the background information needed to
  easily understand how SQLite works.
</p>

<p>
  A separate document, [capi3ref | The SQLite C/C++ Interface],
................................................................................
  <li> The [database connection] object: sqlite3 </li>
  <li> The [prepared statement] object: sqlite3_stmt </li>
</ul></p>

<p>
  Strictly speaking, the [prepared statement] object is not required since
  the convenience wrapper interfaces, [sqlite3_exec] or
  [sqlite3_get_table], that encapsulate and hide the [prepared statement]
  can be used instead.  Nevertheless, and understanding of
  [prepared statements] is needed to make full use of SQLite.
</p>

<p>
  The [database connection] and [prepared statement] objects are controlled
  by a small set of C/C++ interface routine listed below.
................................................................................
  <li> [sqlite3_column_int | sqlite3_column()] </li>
  <li> [sqlite3_finalize()] </li>
  <li> [sqlite3_close()] </li>
</ul></p>

<p>
  The six C/C++ interface routines and two objects listed above form the core
  functionality of SQLite.  The developer who understands them

  will have a good foundation for using SQLite.
</p>

<p>
  Note that the list of routines is conceptual rather than actual.
  Many of these routines come in multiple versions.
  For example, the list above shows a single routine
  named [sqlite3_open()] when in fact there are three separate routines
................................................................................

<tr><td valign="top" align="right">[sqlite3_open()]</td>
<td valign="top">
  This routine 
  opens a connection to an SQLite database file and return a
  [database connection] object.  This is often the first SQLite API
  call that an application makes and is a prerequisite for most other
  SQLite APIs.  Many SQLite interfaces require a pointer to
  the [database connection] object as their first parameter and can
  be thought of as methods on the [database connection] object.
  This routine is the constructor for the [database connection] object.
</td>

<tr><td valign="top" align="right">[sqlite3_prepare()]</td>
<td valign="top">
  This routine
  converts SQL text into a [prepared statement] object and return a pointer
  to that object.  This interface requires a [database connection] pointer
................................................................................
<p>
  The [sqlite3_exec()] interface is a convenience wrapper that carries out
  all four of the above steps with a single function call.  A callback
  function passed into [sqlite3_exec()] is used to process each row of
  the result set.  The [sqlite3_get_table()] is another convenience wrapper
  that does all four of the above steps.  The [sqlite3_get_table()] interface
  differs from [sqlite3_get_table()] in that it stores the results of queries
  in heap memory rather than invoking a callback.
</p>

<p>
  It is important to realize that neither [sqlite3_exec()] nor
  [sqlite3_get_table()] do anything that cannot be accomplished using
  the core routines.  In fact, these wrappers are implemented purely in
  terms of the core routines.
................................................................................
  <li> [sqlite3_reset()] </li>
  <li> [sqlite3_bind_int | sqlite3_bind()] </li>
</ul></p>

<p>
  After a [prepared statement] has been evaluated by one or more calls to
  [sqlite3_step()], it can be reset in order to be evaluted again by a
  call to [sqlite3_reset()].
  Using [sqlite3_reset()] on an existing [prepared statement] rather
  creating a new [prepared statement] avoids unnecessary calls to
  [sqlite3_prepare()].
  In many SQL statements, the time needed
  to run [sqlite3_prepare()] equals or exceeds the time needed by
  [sqlite3_step()].  So avoiding calls to [sqlite3_prepare()] can result
  in a significant performance improvement.