set rcsid {$Id: capi3.tcl,v 1.9 2005/03/11 04:39:58 drh Exp $}source common.tclheader {C/C++ Interface For SQLite Version 3}proc AddHyperlinks {txt} {  regsub -all {([^:alnum:>])(sqlite3_\w+)(\([^\)]*\))} $txt \      {\1<a href="capi3ref.html#\2">\2</a>\3} t2  puts $t2}AddHyperlinks {<h2>C/C++ Interface For SQLite Version 3</h2><h3>1.0 Overview</h3><p>SQLite version 3.0 is a new version of SQLite, derived fromthe SQLite 2.8.13 code base, but with an incompatible file formatand API.SQLite version 3.0 was created to answer demand for the following features:</p><ul><li>Support for UTF-16.</li><li>User-definable text collating sequences.</li><li>The ability to store BLOBs in indexed columns.</li></ul><p>It was necessary to move to version 3.0 to implement these features becauseeach requires incompatible changes to the database file format.  Otherincompatible changes, such as a cleanup of the API, were introduced at thesame time under the theory that it is best to get your incompatible changesout of the way all at once.  </p><p>The API for version 3.0 is similar to the version 2.X API,but with some important changes.  Most noticeably, the "<tt>sqlite_</tt>"prefix that occurs on the beginning of all API functions and datastructures are changed to "<tt>sqlite3_</tt>".  This avoids confusion between the two APIs and allows linking against bothSQLite 2.X and SQLite 3.0 at the same time.</p><p>There is no agreement on what the C datatype for a UTF-16string should be.  Therefore, SQLite uses a generic type of void*to refer to UTF-16 strings.  Client software can cast the void* to whatever datatype is appropriate for their system.</p><h3>2.0 C/C++ Interface</h3><p>The API for SQLite 3.0 includes 83 separate functions in additionto several data structures and #defines.  (A complete<a href="capi3ref.html">API reference</a> is provided as a separate document.)Fortunately, the interface is not nearly as complex as its size implies.Simple programs can still make do with only 3 functions:<a href="capi3ref.html#sqlite3_open">sqlite3_open()</a>,<a href="capi3ref.html#sqlite3_exec">sqlite3_exec()</a>, and<a href="capi3ref.html#sqlite3_close">sqlite3_close()</a>.More control over the execution of the database engine is providedusing<a href="capi3ref.html#sqlite3_prepare">sqlite3_prepare()</a>to compile an SQLite statement into byte code and<a href="capi3ref.html#sqlite3_prepare">sqlite3_step()</a>to execute that bytecode.A family of routines with names beginning with <a href="capi3ref.html#sqlite3_column_blob">sqlite3_column_</a>is used to extract information about the result set of a query.Many interface functions come in pairs, with both a UTF-8 andUTF-16 version.  And there is a collection of routinesused to implement user-defined SQL functions and user-definedtext collating sequences.</p><h4>2.1 Opening and closing a database</h4><blockquote><pre>   typedef struct sqlite3 sqlite3;   int sqlite3_open(const char*, sqlite3**);   int sqlite3_open16(const void*, sqlite3**);   int sqlite3_close(sqlite3*);   const char *sqlite3_errmsg(sqlite3*);   const void *sqlite3_errmsg16(sqlite3*);   int sqlite3_errcode(sqlite3*);</pre></blockquote><p>The sqlite3_open() routine returns an integer error code rather thana pointer to the sqlite3 structure as the version 2 interface did.The difference between sqlite3_open()and sqlite3_open16() is that sqlite3_open16() takes UTF-16 (in host nativebyte order) for the name of the database file.  If a new database fileneeds to be created, then sqlite3_open16() sets the internal textrepresentation to UTF-16 whereas sqlite3_open() sets the textrepresentation to UTF-8.</p><p>The opening and/or creating of the database file is deferred until thefile is actually needed.  This allows options and parameters, suchas the native text representation and default page size, to beset using PRAGMA statements.</p><p>The sqlite3_errcode() routine returns a result code for the mostrecent major API call.  sqlite3_errmsg() returns an English-languagetext error message for the most recent error.  The error message isrepresented in UTF-8 and will be ephemeral - it could disappear onthe next call to any SQLite API function.  sqlite3_errmsg16() works likesqlite3_errmsg() except that it returns the error message representedas UTF-16 in host native byte order.</p><p>The error codes for SQLite version 3 are unchanged from version 2.They are as follows:</p><blockquote><pre>#define SQLITE_OK           0   /* Successful result */#define SQLITE_ERROR        1   /* SQL error or missing database */#define SQLITE_INTERNAL     2   /* An internal logic error in SQLite */#define SQLITE_PERM         3   /* Access permission denied */#define SQLITE_ABORT        4   /* Callback routine requested an abort */#define SQLITE_BUSY         5   /* The database file is locked */#define SQLITE_LOCKED       6   /* A table in the database is locked */#define SQLITE_NOMEM        7   /* A malloc() failed */#define SQLITE_READONLY     8   /* Attempt to write a readonly database */#define SQLITE_INTERRUPT    9   /* Operation terminated by sqlite_interrupt() */#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */#define SQLITE_CORRUPT     11   /* The database disk image is malformed */#define SQLITE_NOTFOUND    12   /* (Internal Only) Table or record not found */#define SQLITE_FULL        13   /* Insertion failed because database is full */#define SQLITE_CANTOPEN    14   /* Unable to open the database file */#define SQLITE_PROTOCOL    15   /* Database lock protocol error */#define SQLITE_EMPTY       16   /* (Internal Only) Database table is empty */#define SQLITE_SCHEMA      17   /* The database schema changed */#define SQLITE_TOOBIG      18   /* Too much data for one row of a table */#define SQLITE_CONSTRAINT  19   /* Abort due to contraint violation */#define SQLITE_MISMATCH    20   /* Data type mismatch */#define SQLITE_MISUSE      21   /* Library used incorrectly */#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */#define SQLITE_AUTH        23   /* Authorization denied */#define SQLITE_ROW         100  /* sqlite_step() has another row ready */#define SQLITE_DONE        101  /* sqlite_step() has finished executing */</pre></blockquote><h4>2.2 Executing SQL statements</h4><blockquote><pre>   typedef int (*sqlite_callback)(void*,int,char**, char**);   int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);</pre></blockquote><p>The sqlite3_exec function works much as it did in SQLite version 2.Zero or more SQL statements specified in the second parameter are compiledand executed.  Query results are returned to a callback routine.See the <a href="capi3ref.html#sqlite3_exec">API reference</a> for additionalinformation.</p><p>In SQLite version 3, the sqlite3_exec routine is just a wrapper aroundcalls to the prepared statement interface.</p><blockquote><pre>   typedef struct sqlite3_stmt sqlite3_stmt;   int sqlite3_prepare(sqlite3*, const char*, int, sqlite3_stmt**, const char**);   int sqlite3_prepare16(sqlite3*, const void*, int, sqlite3_stmt**, const void**);   int sqlite3_finalize(sqlite3_stmt*);   int sqlite3_reset(sqlite3_stmt*);</pre></blockquote><p>The sqlite3_prepare interface compiles a single SQL statement into byte codefor later execution.  This interface is now the preferred way of accessingthe database.</p><p>The SQL statement is a UTF-8 string for sqlite3_prepare().The sqlite3_prepare16() works the same way exceptthat it expects a UTF-16 string as SQL input.Only the first SQL statement in the input string is compiled.The fourth parameter is filled in with a pointer to the next (uncompiled)SQLite statement in the input string, if any.The sqlite3_finalize() routine deallocates a prepared SQL statement.All prepared statements must be finalized before the database can beclosed.The sqlite3_reset() routine resets a prepared SQL statement so that itcan be executed again.</p><p>The SQL statement may contain tokens of the form "?" or "?nnn" or ":aaa"where "nnn" is an integer and "aaa" is an identifier.Such tokens represent unspecified literal values (or "wildcards")to be filled in later by the <a href="capi3ref.html#sqlite3_bind_blob">sqlite3_bind</a> interface.Each wildcard has an associated number which is its sequence in thestatement or the "nnn" in the case of a "?nnn" form. It is allowed for the same wildcardto occur more than once in the same SQL statement, in which caseall instance of that wildcard will be filled in with the same value.Unbound wildcards have a value of NULL.</p><blockquote><pre>   int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));   int sqlite3_bind_double(sqlite3_stmt*, int, double);   int sqlite3_bind_int(sqlite3_stmt*, int, int);   int sqlite3_bind_int64(sqlite3_stmt*, int, long long int);   int sqlite3_bind_null(sqlite3_stmt*, int);   int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));   int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*));   int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);</pre></blockquote><p>There is an assortment of sqlite3_bind routines used to assign valuesto wildcards in a prepared SQL statement.  Unbound wildcardsare interpreted as NULLs.  Bindings are not reset by sqlite3_reset().But wildcards can be rebound to new values after an sqlite3_reset().</p><p>