c_interface.tcl

sqlite-3.4.1,嵌入式数据库.是一个功能强大的开源数据库,给学习和研发以及小型公司的发展带来了全所未有的好处.

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.43 2004/11/19 11:59:24 danielk1977 Exp $}
source common.tcl
header {The C language interface to the SQLite library}
puts {
<h2>The C language interface to the SQLite library</h2>

<p>The SQLite library is designed to be very easy to use from
a C or C++ program.  This document gives an overview of the C/C++
programming interface.</p>

<h3>1.0 The Core API</h3>

<p>The interface to the SQLite library consists of three core functions,
one opaque data structure, and some constants used as return values.
The core interface is as follows:</p>

<blockquote><pre>
typedef struct sqlite sqlite;
#define SQLITE_OK           0   /* Successful result */

sqlite *sqlite_open(const char *dbname, int mode, char **errmsg);

void sqlite_close(sqlite *db);

int sqlite_exec(
  sqlite *db,
  char *sql,
  int (*xCallback)(void*,int,char**,char**),
  void *pArg,
  char **errmsg
);
</pre></blockquote>

<p>
The above is all you really need to know in order to use SQLite
in your C or C++ programs.  There are other interface functions
available (and described below) but we will begin by describing
the core functions shown above.
</p>

<a name="sqlite_open">
<h4>1.1 Opening a database</h4>

<p>Use the <b>sqlite_open</b> function to open an existing SQLite
database or to create a new SQLite database.  The first argument
is the database name.  The second argument is intended to signal
whether the database is going to be used for reading and writing
or just for reading.  But in the current implementation, the
second argument to <b>sqlite_open</b> is ignored.
The third argument is a pointer to a string pointer.
If the third argument is not NULL and an error occurs
while trying to open the database, then an error message will be
written to memory obtained from malloc() and *errmsg will be made
to point to this error message.  The calling function is responsible
for freeing the memory when it has finished with it.</p>

<p>The name of an SQLite database is the name of a file that will
contain the database.  If the file does not exist, SQLite attempts
to create and initialize it.  If the file is read-only (due to
permission bits or because it is located on read-only media like
a CD-ROM) then SQLite opens the database for reading only.  The
entire SQL database is stored in a single file on the disk.  But
additional temporary files may be created during the execution of
an SQL command in order to store the database rollback journal or
temporary and intermediate results of a query.</p>

<p>The return value of the <b>sqlite_open</b> function is a
pointer to an opaque <b>sqlite</b> structure.  This pointer will
be the first argument to all subsequent SQLite function calls that
deal with the same database.  NULL is returned if the open fails
for any reason.</p>

<a name="sqlite_close">
<h4>1.2 Closing the database</h4>

<p>To close an SQLite database, call the <b>sqlite_close</b>
function passing it the sqlite structure pointer that was obtained
from a prior call to <b>sqlite_open</b>.
If a transaction is active when the database is closed, the transaction
is rolled back.</p>

<a name="sqlite_exec">
<h4>1.3 Executing SQL statements</h4>

<p>The <b>sqlite_exec</b> function is used to process SQL statements
and queries.  This function requires 5 parameters as follows:</p>

<ol>
<li><p>A pointer to the sqlite structure obtained from a prior call
       to <b>sqlite_open</b>.</p></li>
<li><p>A null-terminated string containing the text of one or more
       SQL statements and/or queries to be processed.</p></li>
<li><p>A pointer to a callback function which is invoked once for each
       row in the result of a query.  This argument may be NULL, in which
       case no callbacks will ever be invoked.</p></li>
<li><p>A pointer that is forwarded to become the first argument
       to the callback function.</p></li>
<li><p>A pointer to an error string.  Error messages are written to space
       obtained from malloc() and the error string is made to point to
       the malloced space.  The calling function is responsible for freeing
       this space when it has finished with it.
       This argument may be NULL, in which case error messages are not
       reported back to the calling function.</p></li>
</ol>

<p>
The callback function is used to receive the results of a query.  A
prototype for the callback function is as follows:</p>

<blockquote><pre>
int Callback(void *pArg, int argc, char **argv, char **columnNames){
  return 0;
}
</pre></blockquote>

<a name="callback_row_data">
<p>The first argument to the callback is just a copy of the fourth argument
to <b>sqlite_exec</b>  This parameter can be used to pass arbitrary
information through to the callback function from client code.
The second argument is the number of columns in the query result.
The third argument is an array of pointers to strings where each string
is a single column of the result for that record.  Note that the
callback function reports a NULL value in the database as a NULL pointer,
which is very different from an empty string.  If the i-th parameter
is an empty string, we will get:</p>
<blockquote><pre>
argv[i][0] == 0
</pre></blockquote>
<p>But if the i-th parameter is NULL we will get:</p>
<blockquote><pre>
argv[i] == 0
</pre></blockquote>

<p>The names of the columns are contained in first <i>argc</i>
entries of the fourth argument.
If the <a href="pragma.html#pragma_show_datatypes">SHOW_DATATYPES</a> pragma
is on (it is off by default) then
the second <i>argc</i> entries in the 4th argument are the datatypes
for the corresponding columns.
</p>

<p>If the <a href="pragma.html#pragma_empty_result_callbacks">
EMPTY_RESULT_CALLBACKS</a> pragma is set to ON and the result of
a query is an empty set, then the callback is invoked once with the
third parameter (argv) set to 0.  In other words
<blockquote><pre>
argv == 0
</pre></blockquote>
The second parameter (argc)
and the fourth parameter (columnNames) are still valid
and can be used to determine the number and names of the result
columns if there had been a result.
The default behavior is not to invoke the callback at all if the
result set is empty.</p>

<a name="callback_returns_nonzero">
<p>The callback function should normally return 0.  If the callback
function returns non-zero, the query is immediately aborted and 
<b>sqlite_exec</b> will return SQLITE_ABORT.</p>

<h4>1.4 Error Codes</h4>

<p>
The <b>sqlite_exec</b> function normally returns SQLITE_OK.  But
if something goes wrong it can return a different value to indicate
the type of error.  Here is a complete list of the return codes:
</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>

<p>
The meanings of these various return values are as follows:
</p>

<blockquote>
<dl>
<dt>SQLITE_OK</dt>
<dd><p>This value is returned if everything worked and there were no errors.
</p></dd>
<dt>SQLITE_INTERNAL</dt>
<dd><p>This value indicates that an internal consistency check within
the SQLite library failed.  This can only happen if there is a bug in
the SQLite library.  If you ever get an SQLITE_INTERNAL reply from
an <b>sqlite_exec</b> call, please report the problem on the SQLite
mailing list.
</p></dd>
<dt>SQLITE_ERROR</dt>
<dd><p>This return value indicates that there was an error in the SQL
that was passed into the <b>sqlite_exec</b>.
</p></dd>
<dt>SQLITE_PERM</dt>
<dd><p>This return value says that the access permissions on the database
file are such that the file cannot be opened.
</p></dd>
<dt>SQLITE_ABORT</dt>
<dd><p>This value is returned if the callback function returns non-zero.
</p></dd>
<dt>SQLITE_BUSY</dt>
<dd><p>This return code indicates that another program or thread has
the database locked.  SQLite allows two or more threads to read the
database at the same time, but only one thread can have the database
open for writing at the same time.  Locking in SQLite is on the
entire database.</p>
</p></dd>
<dt>SQLITE_LOCKED</dt>
<dd><p>This return code is similar to SQLITE_BUSY in that it indicates
that the database is locked.  But the source of the lock is a recursive
call to <b>sqlite_exec</b>.  This return can only occur if you attempt
to invoke sqlite_exec from within a callback routine of a query
from a prior invocation of sqlite_exec.  Recursive calls to
sqlite_exec are allowed as long as they do
not attempt to write the same table.
</p></dd>
<dt>SQLITE_NOMEM</dt>
<dd><p>This value is returned if a call to <b>malloc</b> fails.
</p></dd>
<dt>SQLITE_READONLY</dt>
<dd><p>This return code indicates that an attempt was made to write to
a database file that is opened for reading only.
</p></dd>
<dt>SQLITE_INTERRUPT</dt>
<dd><p>This value is returned if a call to <b>sqlite_interrupt</b>
interrupts a database operation in progress.
</p></dd>
<dt>SQLITE_IOERR</dt>
<dd><p>This value is returned if the operating system informs SQLite
that it is unable to perform some disk I/O operation.  This could mean
that there is no more space left on the disk.
</p></dd>
<dt>SQLITE_CORRUPT</dt>
<dd><p>This value is returned if SQLite detects that the database it is
working on has become corrupted.  Corruption might occur due to a rogue
process writing to the database file or it might happen due to an 
perviously undetected logic error in of SQLite. This value is also
returned if a disk I/O error occurs in such a way that SQLite is forced
to leave the database file in a corrupted state.  The latter should only
happen due to a hardware or operating system malfunction.
</p></dd>
<dt>SQLITE_FULL</dt>
<dd><p>This value is returned if an insertion failed because there is
no space left on the disk, or the database is too big to hold any
more information.  The latter case should only occur for databases
that are larger than 2GB in size.
</p></dd>
<dt>SQLITE_CANTOPEN</dt>
<dd><p>This value is returned if the database file could not be opened
for some reason.
</p></dd>
<dt>SQLITE_PROTOCOL</dt>
<dd><p>This value is returned if some other process is messing with
file locks and has violated the file locking protocol that SQLite uses
on its rollback journal files.
</p></dd>
<dt>SQLITE_SCHEMA</dt>
<dd><p>When the database first opened, SQLite reads the database schema
into memory and uses that schema to parse new SQL statements.  If another
process changes the schema, the command currently being processed will
abort because the virtual machine code generated assumed the old
schema.  This is the return code for such cases.  Retrying the
command usually will clear the problem.
</p></dd>
<dt>SQLITE_TOOBIG</dt>
<dd><p>SQLite will not store more than about 1 megabyte of data in a single
row of a single table.  If you attempt to store more than 1 megabyte
in a single row, this is the return code you get.
</p></dd>
<dt>SQLITE_CONSTRAINT</dt>
<dd><p>This constant is returned if the SQL statement would have violated
a database constraint.
</p></dd>
<dt>SQLITE_MISMATCH</dt>
<dd><p>This error occurs when there is an attempt to insert non-integer
data into a column labeled INTEGER PRIMARY KEY.  For most columns, SQLite
ignores the data type and allows any kind of data to be stored.  But
an INTEGER PRIMARY KEY column is only allowed to store integer data.
</p></dd>
<dt>SQLITE_MISUSE</dt>
<dd><p>This error might occur if one or more of the SQLite API routines
is used incorrectly.  Examples of incorrect usage include calling
<b>sqlite_exec</b> after the database has been closed using
<b>sqlite_close</b> or 
calling <b>sqlite_exec</b> with the same
database pointer simultaneously from two separate threads.
</p></dd>
<dt>SQLITE_NOLFS</dt>
<dd><p>This error means that you have attempts to create or access a file
database file that is larger that 2GB on a legacy Unix machine that
lacks large file support.
</p></dd>
<dt>SQLITE_AUTH</dt>
<dd><p>This error indicates that the authorizer callback
has disallowed the SQL you are attempting to execute.
</p></dd>
<dt>SQLITE_ROW</dt>
<dd><p>This is one of the return codes from the
<b>sqlite_step</b> routine which is part of the non-callback API.
It indicates that another row of result data is available.
</p></dd>
<dt>SQLITE_DONE</dt>
<dd><p>This is one of the return codes from the
<b>sqlite_step</b> routine which is part of the non-callback API.
It indicates that the SQL statement has been completely executed and
the <b>sqlite_finalize</b> routine is ready to be called.
</p></dd>
</dl>
</blockquote>

<h3>2.0 Accessing Data Without Using A Callback Function</h3>

<p>
The <b>sqlite_exec</b> routine described above used to be the only
way to retrieve data from an SQLite database.  But many programmers found
it inconvenient to use a callback function to obtain results.  So beginning
with SQLite version 2.7.7, a second access interface is available that
does not use callbacks.
</p>

<p>
The new interface uses three separate functions to replace the single
<b>sqlite_exec</b> function.
</p>

<blockquote><pre>
typedef struct sqlite_vm sqlite_vm;

int sqlite_compile(
  sqlite *db,              /* The open database */
  const char *zSql,        /* SQL statement to be compiled */
  const char **pzTail,     /* OUT: uncompiled tail of zSql */
  sqlite_vm **ppVm,        /* OUT: the virtual machine to execute zSql */
  char **pzErrmsg          /* OUT: Error message. */
);

int sqlite_step(
  sqlite_vm *pVm,          /* The virtual machine to execute */
  int *pN,                 /* OUT: Number of columns in result */
  const char ***pazValue,  /* OUT: Column data */
  const char ***pazColName /* OUT: Column names and datatypes */
);

int sqlite_finalize(
  sqlite_vm *pVm,          /* The virtual machine to be finalized */
  char **pzErrMsg          /* OUT: Error message */