Documentation Source Text

  }
  hd_puts {</tr></table>}
}

hd_open_aux c3ref/intro.html
hd_header Introduction
hd_enable_main 0
hd_keywords {capi3ref}
</tcl>

<p>These pages defined the C-language interface to SQLite.
These pages are intended as a reference to what SQLite
is suppose to do.  This is not a tutorial.  These
pages are designed to be precise, not easy to read.</p>

<title>An Introduction To The SQLite C/C++ Interface</title>
<tcl>
proc CODE {text} {
  hd_puts "<blockquote><pre>"
  hd_puts $text
  hd_puts "</pre></blockquote>"
}
proc PARAGRAPH {text} {
  hd_resolve <p>$text</p>\n
}
set level(0) 0
set level(1) 0
proc HEADING {n name {tag {}}} {
  if {$tag!=""} {
    hd_fragment $tag
  }
  global level
  incr level($n)
  for {set i [expr {$n+1}]} {$i<10} {incr i} {
    set level($i) 0
  }
  if {$n==0} {
    set num {}
  } elseif {$n==1} {
    set num $level(1).0
  } else {
    set num $level(1)
    for {set i 2} {$i<=$n} {incr i} {
      append num .$level($i)
    }
  }
  incr n 1
  hd_puts "<h$n>$num $name</h$n>"
}

hd_keywords {Introduction To The SQLite C/C++ Interface}
HEADING 0 {An Introduction To The SQLite C/C++ Interface}
</tcl>

<p>
  This article provides an overview and roadmap to the C/C++ interface
  to SQLite.
</p>

<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],
  provides detailed
  specifications for all of the various C/C++ APIs for SQLite.  Once you
  understand the basic principles of operation for SQLite you should
  begin using [capi3ref | that document] as your reference
  guide.  This article is intended as introduction only and is neither a
  complete or authoritative reference for the SQLite API.
</p>

<tcl>HEADING 1 {Core Objects And Interfaces}</tcl>

<p>
  The principal function of an SQL database engine is to evaluate statements
  of SQL.  In order to accomplish this in SQLite, one needs to know about
  two objects:
</p>

<p><ul>
  <li> The [sqlite3] database connection object </li>
  <li> The [sqlite3_stmt] prepared statement object </li>
</ul></p>

<p>
  Strictly speaking, the [prepared statement] object is not required since
  you can use one of the convenience wrapper interfaces, [sqlite3_exec] or
  [sqlite3_get_table], which encapsulate and hide the prepared statement.
  But for a full understand of how SQLite works, one really does need to
  know about prepared statements so they will be included in this discussion.
</p>

<p>
  The [database connection] and [prepared statement] objects are controlled
  by a small set of C/C++ interface routine listed below.
</p>

<p><ul>
  <li> [sqlite3_open()] </li>
  <li> [sqlite3_prepare()] </li>
  <li> [sqlite3_step()] </li>
  <li> [sqlite3_column_int | sqlite3_column()] </li>
  <li> [sqlite3_finalize()] </li>
  <li> [sqlite3_close()] </li>
</ul></p>

<p>
  To the list above we add a second tier of core interfaces as follows:
</p>

<p></ul>
  <li> [sqlite3_bind_int | sqlite3_bind()] </li>
  <li> [sqlite3_reset()] </li>
</ul></p>

<p>
  The 8 C/C++ interface routines and 2 objects listed above form the core
  functionality of SQLite.  If you understand what these APIs and objects
  do, you will have a good foundation for using SQLite.
</p>

<p>
  Note, however, that the list of 8 routines above is a
  simplification.  There are frequently multiple versions of each of
  the routines shown.  For example, the list above shows a single routine
  named [sqlite3_open()] when in fact there are three separate routines
  that accomplish the same thing in slightly different ways:
  [sqlite3_open()], [sqlite3_open16()] and [sqlite3_open_v2()].
  The list mentions [sqlite3_column_int | sqlite3_column()] and
  and [sqlite3_bind_int | sqlite3_bind()] when in fact no such routines
  exist.  Those entries in the list are place holders for entire families
  of routines to be used for various datatypes
  ([sqlite3_column_blob()], [sqlite3_column_text()], [sqlite3_column_int()],
  and so forth.)
  Do not be intimidated by this.  Focus for now on the 8 core routines
  listed above and later refer to the [capi3ref | C/C++ Interface Reference] 
  for details on the available variations of each interface.
</p>

<p>
  Here is what the core interfaces do:
</p>

<table border="0" cellspacing="15">

<tr><td valign="top">[sqlite3_open()]</td>
<td valign="top">
  Open 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">[sqlite3_prepare()]</td>
<td valign="top">
  Convert SQL text into a [prepared statement] object and return a pointer
  to that object.  This interface requires a [database connection] pointer
  created by a prior call to [sqlite3_open()] and a text string containing
  the SQL statement to be prepared.  This API does not actually evaluate
  the SQL statement.  It merely prepares the SQL statement for evaluation.
</td>

<tr><td valign="top">[sqlite3_step()]</td>
<td valign="top">
  This routine is used to evaluate a [prepared statement] that has been
  previously created by the [sqlite3_prepare()] interface.  The statement
  is evaluated up to the point where the first row of results are available.
  To advance to the second row of results, invoke [sqlite3_step()] again.
  Continue invoking [sqlite3_step()] until the statement is complete.
  Statements that do not return results (ex: INSERT, UPDATE, or DELETE
  statements) run to completion on a single call to [sqlite3_step()].
</td>


<tr><td valign="top">[sqlite3_column_int | sqlite3_column()]</td>
<td valign="top">
  This routine returns a single column from the current row of a result
  set for a [prepared statement] that is being evaluated by [sqlite3_step()].
  Each time [sqlite3_step()] stops with a new result set row, this routine
  can be called multiple times to find the values of all columns in that row.
  As noted above, there really is no such thing as a "sqlite3_column()"
  function in the SQLite API.  Instead, what we here call "sqlite3_column()"
  is really a place-holder for an entire family of functions that return
  a value from the result set in various data types.  There are also routines
  in this family that return the size of the result (if it is a string or
  BLOB) and the number of columns in the result set.  

  <p><ul>
    <li> [sqlite3_column_blob()] </li>
    <li> [sqlite3_column_bytes()] </li>
    <li> [sqlite3_column_bytes16()] </li>
    <li> [sqlite3_column_count()] </li>
    <li> [sqlite3_column_double()] </li>
    <li> [sqlite3_column_int()] </li>
    <li> [sqlite3_column_int64()] </li>
    <li> [sqlite3_column_text()] </li>
    <li> [sqlite3_column_text16()] </li>
    <li> [sqlite3_column_type()] </li>
    <li> [sqlite3_column_value()] </li>
  </ul></p>
</td>

</table>

new database.</p></li>
<li><p>Additional documentation is available <a href="sqlite.html">here</a></li>
</ul>

<h2>Write Programs That Use SQLite</h2>

<ul>
<li><p>Below is a simple 
<a href="http://www.tcl.tk">TCL program</a> that demonstrates how to use
the TCL interface to SQLite.  The program executes the SQL statements
given as the second argument on the database defined by the first
argument.  The commands to watch for are the <b>sqlite3</b> command
on line 7 which opens an SQLite database and creates
a new TCL command named "<b>db</b>" to access that database, the
invocation of the <b>db</b> command on line 8 to execute
SQL commands against the database, and the closing of the database connection

</pre></blockquote>
</li>

<li><p>Below is a simple C program that demonstrates how to use
the C/C++ interface to SQLite.  The name of a database is given by
the first argument and the second argument is one or more SQL statements
to execute against the database.  The function calls to pay attention
to here are the call to [sqlite3_open()] on line 22 which opens
the database, [sqlite3_exec()] on line 27 that executes SQL
commands against the database, and [sqlite3_close()] on line 31
that closes the database connection.</p>

<blockquote><pre>
#include &lt;stdio.h&gt;
#include &lt;sqlite3.h&gt;

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
  int i;
  for(i=0; i&lt;argc; i++){
    printf("%s = %s\n", azColName&#91;i], argv&#91;i] ? argv&#91;i] : "NULL");
  }
  printf("\n");
  return 0;
}

int main(int argc, char **argv){
  <b>sqlite3</b> *db;
  char *zErrMsg = 0;
  int rc;

  if( argc!=3 ){
    fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv&#91;0]);
    exit(1);
  }
  rc = <b>sqlite3_open</b>(argv&#91;1], &db);
  if( rc ){
    fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
    <b>sqlite3_close</b>(db);
    exit(1);
  }
  rc = <b>sqlite3_exec</b>(db, argv&#91;2], callback, 0, &zErrMsg);
  if( rc!=SQLITE_OK ){
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
    <b>sqlite3_free</b>(zErrMsg);
  }
  <b>sqlite3_close</b>(db);
  return 0;
}
</pre></blockquote>
</li>
</ul>