c_interface.tcl

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

);
</pre></blockquote>

<p>
The strategy is to compile a single SQL statement using
<b>sqlite_compile</b> then invoke <b>sqlite_step</b> multiple times,
once for each row of output, and finally call <b>sqlite_finalize</b>
to clean up after the SQL has finished execution.
</p>

<h4>2.1 Compiling An SQL Statement Into A Virtual Machine</h4>

<p>
The <b>sqlite_compile</b> "compiles" a single SQL statement (specified
by the second parameter) and generates a virtual machine that is able
to execute that statement.  
As with must interface routines, the first parameter must be a pointer
to an sqlite structure that was obtained from a prior call to
<b>sqlite_open</b>.

<p>
A pointer to the virtual machine is stored in a pointer which is passed
in as the 4th parameter.
Space to hold the virtual machine is dynamically allocated.  To avoid
a memory leak, the calling function must invoke
<b>sqlite_finalize</b> on the virtual machine after it has finished
with it.
The 4th parameter may be set to NULL if an error is encountered during
compilation.
</p>

<p>
If any errors are encountered during compilation, an error message is
written into memory obtained from <b>malloc</b> and the 5th parameter
is made to point to that memory.  If the 5th parameter is NULL, then
no error message is generated.  If the 5th parameter is not NULL, then
the calling function should dispose of the memory containing the error
message by calling <b>sqlite_freemem</b>.
</p>

<p>
If the 2nd parameter actually contains two or more statements of SQL,
only the first statement is compiled.  (This is different from the
behavior of <b>sqlite_exec</b> which executes all SQL statements
in its input string.)  The 3rd parameter to <b>sqlite_compile</b>
is made to point to the first character beyond the end of the first
statement of SQL in the input.  If the 2nd parameter contains only
a single SQL statement, then the 3rd parameter will be made to point
to the '\000' terminator at the end of the 2nd parameter.
</p>

<p>
On success, <b>sqlite_compile</b> returns SQLITE_OK.
Otherwise and error code is returned.
</p>

<h4>2.2 Step-By-Step Execution Of An SQL Statement</h4>

<p>
After a virtual machine has been generated using <b>sqlite_compile</b>
it is executed by one or more calls to <b>sqlite_step</b>.  Each
invocation of <b>sqlite_step</b>, except the last one,
returns a single row of the result.
The number of columns in  the result is stored in the integer that
the 2nd parameter points to.
The pointer specified by the 3rd parameter is made to point
to an array of pointers to column values.
The pointer in the 4th parameter is made to point to an array
of pointers to column names and datatypes.
The 2nd through 4th parameters to <b>sqlite_step</b> convey the
same information as the 2nd through 4th parameters of the
<b>callback</b> routine when using
the <b>sqlite_exec</b> interface.  Except, with <b>sqlite_step</b>
the column datatype information is always included in the in the
4th parameter regardless of whether or not the
<a href="pragma.html#pragma_show_datatypes">SHOW_DATATYPES</a> pragma
is on or off.
</p>

<p>
Each invocation of <b>sqlite_step</b> returns an integer code that
indicates what happened during that step.  This code may be
SQLITE_BUSY, SQLITE_ROW, SQLITE_DONE, SQLITE_ERROR, or
SQLITE_MISUSE.
</p>

<p>
If the virtual machine is unable to open the database file because
it is locked by another thread or process, <b>sqlite_step</b>
will return SQLITE_BUSY.  The calling function should do some other
activity, or sleep, for a short amount of time to give the lock a
chance to clear, then invoke <b>sqlite_step</b> again.  This can
be repeated as many times as desired.
</p>

<p>
Whenever another row of result data is available,
<b>sqlite_step</b> will return SQLITE_ROW.  The row data is
stored in an array of pointers to strings and the 2nd parameter
is made to point to this array.
</p>

<p>
When all processing is complete, <b>sqlite_step</b> will return
either SQLITE_DONE or SQLITE_ERROR.  SQLITE_DONE indicates that the
statement completed successfully and SQLITE_ERROR indicates that there
was a run-time error.  (The details of the error are obtained from
<b>sqlite_finalize</b>.)  It is a misuse of the library to attempt
to call <b>sqlite_step</b> again after it has returned SQLITE_DONE
or SQLITE_ERROR.
</p>

<p>
When <b>sqlite_step</b> returns SQLITE_DONE or SQLITE_ERROR,
the *pN and *pazColName values are set to the number of columns
in the result set and to the names of the columns, just as they
are for an SQLITE_ROW return.  This allows the calling code to
find the number of result columns and the column names and datatypes
even if the result set is empty.  The *pazValue parameter is always
set to NULL when the return codes is SQLITE_DONE or SQLITE_ERROR.
If the SQL being executed is a statement that does not
return a result (such as an INSERT or an UPDATE) then *pN will
be set to zero and *pazColName will be set to NULL.
</p>

<p>
If you abuse the library by trying to call <b>sqlite_step</b>
inappropriately it will attempt return SQLITE_MISUSE.
This can happen if you call sqlite_step() on the same virtual machine
at the same
time from two or more threads or if you call sqlite_step()
again after it returned SQLITE_DONE or SQLITE_ERROR or if you
pass in an invalid virtual machine pointer to sqlite_step().
You should not depend on the SQLITE_MISUSE return code to indicate
an error.  It is possible that a misuse of the interface will go
undetected and result in a program crash.  The SQLITE_MISUSE is
intended as a debugging aid only - to help you detect incorrect
usage prior to a mishap.  The misuse detection logic is not guaranteed
to work in every case.
</p>

<h4>2.3 Deleting A Virtual Machine</h4>

<p>
Every virtual machine that <b>sqlite_compile</b> creates should
eventually be handed to <b>sqlite_finalize</b>.  The sqlite_finalize()
procedure deallocates the memory and other resources that the virtual
machine uses.  Failure to call sqlite_finalize() will result in 
resource leaks in your program.
</p>

<p>
The <b>sqlite_finalize</b> routine also returns the result code
that indicates success or failure of the SQL operation that the
virtual machine carried out.
The value returned by sqlite_finalize() will be the same as would
have been returned had the same SQL been executed by <b>sqlite_exec</b>.
The error message returned will also be the same.
</p>

<p>
It is acceptable to call <b>sqlite_finalize</b> on a virtual machine
before <b>sqlite_step</b> has returned SQLITE_DONE.  Doing so has
the effect of interrupting the operation in progress.  Partially completed
changes will be rolled back and the database will be restored to its
original state (unless an alternative recovery algorithm is selected using
an ON CONFLICT clause in the SQL being executed.)  The effect is the
same as if a callback function of <b>sqlite_exec</b> had returned
non-zero.
</p>

<p>
It is also acceptable to call <b>sqlite_finalize</b> on a virtual machine
that has never been passed to <b>sqlite_step</b> even once.
</p>

<h3>3.0 The Extended API</h3>

<p>Only the three core routines described in section 1.0 are required to use
SQLite.  But there are many other functions that provide 
useful interfaces.  These extended routines are as follows:
</p>

<blockquote><pre>
int sqlite_last_insert_rowid(sqlite*);

int sqlite_changes(sqlite*);

int sqlite_get_table(
  sqlite*,
  char *sql,
  char ***result,
  int *nrow,
  int *ncolumn,
  char **errmsg
);

void sqlite_free_table(char**);

void sqlite_interrupt(sqlite*);

int sqlite_complete(const char *sql);

void sqlite_busy_handler(sqlite*, int (*)(void*,const char*,int), void*);

void sqlite_busy_timeout(sqlite*, int ms);

const char sqlite_version[];

const char sqlite_encoding[];

int sqlite_exec_printf(
  sqlite*,
  char *sql,
  int (*)(void*,int,char**,char**),
  void*,
  char **errmsg,
  ...
);

int sqlite_exec_vprintf(
  sqlite*,
  char *sql,
  int (*)(void*,int,char**,char**),
  void*,
  char **errmsg,
  va_list
);

int sqlite_get_table_printf(
  sqlite*,
  char *sql,
  char ***result,
  int *nrow,
  int *ncolumn,
  char **errmsg,
  ...
);

int sqlite_get_table_vprintf(
  sqlite*,
  char *sql,
  char ***result,
  int *nrow,
  int *ncolumn,
  char **errmsg,
  va_list
);

char *sqlite_mprintf(const char *zFormat, ...);

char *sqlite_vmprintf(const char *zFormat, va_list);

void sqlite_freemem(char*);

void sqlite_progress_handler(sqlite*, int, int (*)(void*), void*);

</pre></blockquote>

<p>All of the above definitions are included in the "sqlite.h"
header file that comes in the source tree.</p>

<h4>3.1 The ROWID of the most recent insert</h4>

<p>Every row of an SQLite table has a unique integer key.  If the
table has a column labeled INTEGER PRIMARY KEY, then that column
serves as the key.  If there is no INTEGER PRIMARY KEY column then
the key is a unique integer.  The key for a row can be accessed in
a SELECT statement or used in a WHERE or ORDER BY clause using any
of the names "ROWID", "OID", or "_ROWID_".</p>

<p>When you do an insert into a table that does not have an INTEGER PRIMARY
KEY column, or if the table does have an INTEGER PRIMARY KEY but the value
for that column is not specified in the VALUES clause of the insert, then
the key is automatically generated.  You can find the value of the key
for the most recent INSERT statement using the
<b>sqlite_last_insert_rowid</b> API function.</p>

<h4>3.2 The number of rows that changed</h4>

<p>The <b>sqlite_changes</b> API function returns the number of rows
that have been inserted, deleted, or modified since the database was
last quiescent.  A "quiescent" database is one in which there are
no outstanding calls to <b>sqlite_exec</b> and no VMs created by
<b>sqlite_compile</b> that have not been finalized by <b>sqlite_finalize</b>.
In common usage, <b>sqlite_changes</b> returns the number
of rows inserted, deleted, or modified by the most recent <b>sqlite_exec</b>
call or since the most recent <b>sqlite_compile</b>.  But if you have
nested calls to <b>sqlite_exec</b> (that is, if the callback routine
of one <b>sqlite_exec</b> invokes another <b>sqlite_exec</b>) or if
you invoke <b>sqlite_compile</b> to create a new VM while there is
still another VM in existance, then
the meaning of the number returned by <b>sqlite_changes</b> is more
complex.
The number reported includes any changes
that were later undone by a ROLLBACK or ABORT.  But rows that are
deleted because of a DROP TABLE are <em>not</em> counted.</p>

<p>SQLite implements the command "<b>DELETE FROM table</b>" (without
a WHERE clause) by dropping the table then recreating it.  
This is much faster than deleting the elements of the table individually.
But it also means that the value returned from <b>sqlite_changes</b>
will be zero regardless of the number of elements that were originally
in the table.  If an accurate count of the number of elements deleted
is necessary, use "<b>DELETE FROM table WHERE 1</b>" instead.</p>

<h4>3.3 Querying into memory obtained from malloc()</h4>

<p>The <b>sqlite_get_table</b> function is a wrapper around
<b>sqlite_exec</b> that collects all the information from successive
callbacks and writes it into memory obtained from malloc().  This
is a convenience function that allows the application to get the
entire result of a database query with a single function call.</p>

<p>The main result from <b>sqlite_get_table</b> is an array of pointers
to strings.  There is one element in this array for each column of
each row in the result.  NULL results are represented by a NULL
pointer. In addition to the regular data, there is an added row at the 
beginning of the array that contains the name of each column of the
result.</p>

<p>As an example, consider the following query:</p>

<blockquote>
SELECT employee_name, login, host FROM users WHERE login LIKE 'd%';
</blockquote>

<p>This query will return the name, login and host computer name
for every employee whose login begins with the letter "d".  If this
query is submitted to <b>sqlite_get_table</b> the result might
look like this:</p>

<blockquote>
nrow = 2<br>
ncolumn = 3<br>
result[0] = "employee_name"<br>
result[1] = "login"<br>
result[2] = "host"<br>
result[3] = "dummy"<br>
result[4] = "No such user"<br>
result[5] = 0<br>
result[6] = "D. Richard Hipp"<br>
result[7] = "drh"<br>
result[8] = "zadok"
</blockquote>

<p>Notice that the "host" value for the "dummy" record is NULL so
the result[] array contains a NULL pointer at that slot.</p>

<p>If the result set of a query is empty, then by default
<b>sqlite_get_table</b> will set nrow to 0 and leave its
result parameter is set to NULL.  But if the EMPTY_RESULT_CALLBACKS
pragma is ON then the result parameter is initialized to the names
of the columns only.  For example, consider this query which has
an empty result set:</p>

<blockquote>
SELECT employee_name, login, host FROM users WHERE employee_name IS NULL;
</blockquote>

<p>
The default behavior gives this results:
</p>

<blockquote>
nrow = 0<br>
ncolumn = 0<br>
result = 0<br>
</blockquote>

<p>
But if the EMPTY_RESULT_CALLBACKS pragma is ON, then the following
is returned: