Documentation Source Text

hd_open_aux c3ref/intro.html
hd_header Introduction
hd_enable_main 0
hd_keywords *capi3ref {C-language Interface}
</tcl>

<p>These pages define the C-language interface to SQLite.</p>

<p>This is not a tutorial.  These
pages are designed to be precise, not easy to read.
For a tutorial introduction see
[quickstart | SQLite In 3 Minutes Or Less] and/or
the [cintro | Introduction To The SQLite C/C++ Interface].
</p>

  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()]
  when in fact no such routine exists.
  The "sqlite3_column()" shown in the list is place holders for
  an entire family of routines to be used for extracting column
  data in various datatypes.
</p>

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

<tcl>HEADING 1 {Binding Parameters and Reusing Prepared Statements}</tcl>

<p>
  In prior discussion, it was assumed that each SQL statement is prepared
  once, evaluated, then destroyed.  However, the SQLite allows the same
  [prepared statement] to be evaluated multiple times.  This is accomplished
  using the following routines:
</p>

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

  There is no arbitrary limit to the number of outstanding
  [prepared statements].
</p>

<tcl>HEADING 1 {Extending SQLite}</tcl>

<p>
  SQLite includes interfaces that can be used to extend its functionality.
  Such routines include:
</p>

<p><ul>
  <li> [sqlite3_create_collation()] </li>
  <li> [sqlite3_create_function()] </li>
  <li> [sqlite3_create_module()] </li>
</ul></p>

<p>
  The [sqlite3_create_collation()] interface is used to create new
  collating sequences for sorting text.
  The [sqlite3_create_module()] interface is used to register new
  virtual table implementations.
</p>

<p>
  The [sqlite3_create_function()] interface creates new SQL functions - 
  either scalar or aggregate.  The new function implementation typically
  makes use of the following additional interfaces:

<tcl>HEADING 1 {Other Interfaces}</tcl>

<p>
  This article only mentions the foundational SQLite interfaces.
  The SQLite library includes many other APIs implementing useful
  features that are not described here.  
  A [capi3ref_funclist | complete list of functions] that form the SQLite
  application programming interface is found at the
  [capi3ref | C/C++ Interface Specification].
  Refer to that document for complete and authoritative information about
  all SQLite interfaces.
</p>

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
on the last line of the script.</p>

<blockquote><pre>
01  #!/usr/bin/tclsh
02  if {$argc!=2} {
03    puts stderr "Usage: %s DATABASE SQL-STATEMENT"
04    exit 1

05  }
06  load /usr/lib/tclsqlite3.so Sqlite3
07  <b>sqlite3</b> db &#91;lindex $argv 0]
08  <b>db</b> eval &#91;lindex $argv 1] x {
09    foreach v $x(*) {
10      puts "$v = $x($v)"

11    }
12    puts ""

13  }
14  <b>db</b> close
</pre></blockquote>
</li>

<li><p>Below is a simple C program that demonstrates how to use
the [capi3ref | 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 28 that executes SQL
commands against the database, and [sqlite3_close()] on line 33
that closes the database connection.</p>

<p>See also the [cintro | Introduction To The SQLite C/C++ Interface] for
an introductory overview and roadmap to the dozens of SQLite interface
functions.</p>

<blockquote><pre>
01  #include &lt;stdio.h&gt;
02  #include &lt;sqlite3.h&gt;
03  
04  static int callback(void *NotUsed, int argc, char **argv, char **azColName){
05    int i;
06    for(i=0; i&lt;argc; i++){
07      printf("%s = %s\n", azColName&#91;i], argv&#91;i] ? argv&#91;i] : "NULL");

08    }
09    printf("\n");
10    return 0;

11  }
12  
13  int main(int argc, char **argv){
14    <b>sqlite3</b> *db;
15    char *zErrMsg = 0;
16    int rc;
17  
18    if( argc!=3 ){
19      fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv&#91;0]);
20      exit(1);

21    }
22    rc = <b>sqlite3_open</b>(argv&#91;1], &db);
23    if( rc ){
24      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
25      <b>sqlite3_close</b>(db);
26      exit(1);

27    }
28    rc = <b>sqlite3_exec</b>(db, argv&#91;2], callback, 0, &zErrMsg);
29    if( rc!=SQLITE_OK ){
30      fprintf(stderr, "SQL error: %s\n", zErrMsg);
31      <b>sqlite3_free</b>(zErrMsg);

32    }
33    <b>sqlite3_close</b>(db);
34    return 0;

35  }
</pre></blockquote>
</li>
</ul>