c_interface.tcl

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

</p>

<blockquote>
nrow = 0<br>
ncolumn = 3<br>
result[0] = "employee_name"<br>
result[1] = "login"<br>
result[2] = "host"<br>
</blockquote>

<p>Memory to hold the information returned by <b>sqlite_get_table</b>
is obtained from malloc().  But the calling function should not try
to free this information directly.  Instead, pass the complete table
to <b>sqlite_free_table</b> when the table is no longer needed.
It is safe to call <b>sqlite_free_table</b> with a NULL pointer such
as would be returned if the result set is empty.</p>

<p>The <b>sqlite_get_table</b> routine returns the same integer
result code as <b>sqlite_exec</b>.</p>

<h4>3.4 Interrupting an SQLite operation</h4>

<p>The <b>sqlite_interrupt</b> function can be called from a
different thread or from a signal handler to cause the current database
operation to exit at its first opportunity.  When this happens,
the <b>sqlite_exec</b> routine (or the equivalent) that started
the database operation will return SQLITE_INTERRUPT.</p>

<h4>3.5 Testing for a complete SQL statement</h4>

<p>The next interface routine to SQLite is a convenience function used
to test whether or not a string forms a complete SQL statement.
If the <b>sqlite_complete</b> function returns true when its input
is a string, then the argument forms a complete SQL statement.
There are no guarantees that the syntax of that statement is correct,
but we at least know the statement is complete.  If <b>sqlite_complete</b>
returns false, then more text is required to complete the SQL statement.</p>

<p>For the purpose of the <b>sqlite_complete</b> function, an SQL
statement is complete if it ends in a semicolon.</p>

<p>The <b>sqlite</b> command-line utility uses the <b>sqlite_complete</b>
function to know when it needs to call <b>sqlite_exec</b>.  After each
line of input is received, <b>sqlite</b> calls <b>sqlite_complete</b>
on all input in its buffer.  If <b>sqlite_complete</b> returns true, 
then <b>sqlite_exec</b> is called and the input buffer is reset.  If
<b>sqlite_complete</b> returns false, then the prompt is changed to
the continuation prompt and another line of text is read and added to
the input buffer.</p>

<h4>3.6 Library version string</h4>

<p>The SQLite library exports the string constant named
<b>sqlite_version</b> which contains the version number of the
library.  The header file contains a macro SQLITE_VERSION
with the same information.  If desired, a program can compare
the SQLITE_VERSION macro against the <b>sqlite_version</b>
string constant to verify that the version number of the
header file and the library match.</p> 

<h4>3.7 Library character encoding</h4>

<p>By default, SQLite assumes that all data uses a fixed-size
8-bit character (iso8859).  But if you give the --enable-utf8 option
to the configure script, then the library assumes UTF-8 variable
sized characters.  This makes a difference for the LIKE and GLOB
operators and the LENGTH() and SUBSTR() functions.  The static
string <b>sqlite_encoding</b> will be set to either "UTF-8" or
"iso8859" to indicate how the library was compiled.  In addition,
the <b>sqlite.h</b> header file will define one of the
macros <b>SQLITE_UTF8</b> or <b>SQLITE_ISO8859</b>, as appropriate.</p>

<p>Note that the character encoding mechanism used by SQLite cannot
be changed at run-time.  This is a compile-time option only.  The
<b>sqlite_encoding</b> character string just tells you how the library
was compiled.</p>

<h4>3.8 Changing the library's response to locked files</h4>

<p>The <b>sqlite_busy_handler</b> procedure can be used to register
a busy callback with an open SQLite database.  The busy callback will
be invoked whenever SQLite tries to access a database that is locked.
The callback will typically do some other useful work, or perhaps sleep,
in order to give the lock a chance to clear.  If the callback returns
non-zero, then SQLite tries again to access the database and the cycle
repeats.  If the callback returns zero, then SQLite aborts the current
operation and returns SQLITE_BUSY.</p>

<p>The arguments to <b>sqlite_busy_handler</b> are the opaque
structure returned from <b>sqlite_open</b>, a pointer to the busy
callback function, and a generic pointer that will be passed as
the first argument to the busy callback.  When SQLite invokes the
busy callback, it sends it three arguments:  the generic pointer
that was passed in as the third argument to <b>sqlite_busy_handler</b>,
the name of the database table or index that the library is trying
to access, and the number of times that the library has attempted to
access the database table or index.</p>

<p>For the common case where we want the busy callback to sleep,
the SQLite library provides a convenience routine <b>sqlite_busy_timeout</b>.
The first argument to <b>sqlite_busy_timeout</b> is a pointer to
an open SQLite database and the second argument is a number of milliseconds.
After <b>sqlite_busy_timeout</b> has been executed, the SQLite library
will wait for the lock to clear for at least the number of milliseconds 
specified before it returns SQLITE_BUSY.  Specifying zero milliseconds for
the timeout restores the default behavior.</p>

<h4>3.9 Using the <tt>_printf()</tt> wrapper functions</h4>

<p>The four utility functions</p>

<p>
<ul>
<li><b>sqlite_exec_printf()</b></li>
<li><b>sqlite_exec_vprintf()</b></li>
<li><b>sqlite_get_table_printf()</b></li>
<li><b>sqlite_get_table_vprintf()</b></li>
</ul>
</p>

<p>implement the same query functionality as <b>sqlite_exec</b>
and <b>sqlite_get_table</b>.  But instead of taking a complete
SQL statement as their second argument, the four <b>_printf</b>
routines take a printf-style format string.  The SQL statement to
be executed is generated from this format string and from whatever
additional arguments are attached to the end of the function call.</p>

<p>There are two advantages to using the SQLite printf
functions instead of <b>sprintf</b>.  First of all, with the
SQLite printf routines, there is never a danger of overflowing a
static buffer as there is with <b>sprintf</b>.  The SQLite
printf routines automatically allocate (and later frees)
as much memory as is 
necessary to hold the SQL statements generated.</p>

<p>The second advantage the SQLite printf routines have over
<b>sprintf</b> are two new formatting options specifically designed
to support string literals in SQL.  Within the format string,
the %q formatting option works very much like %s in that it
reads a null-terminated string from the argument list and inserts
it into the result.  But %q translates the inserted string by
making two copies of every single-quote (') character in the
substituted string.  This has the effect of escaping the end-of-string
meaning of single-quote within a string literal. The %Q formatting
option works similar; it translates the single-quotes like %q and
additionally encloses the resulting string in single-quotes.
If the argument for the %Q formatting options is a NULL pointer,
the resulting string is NULL without single quotes.
</p>

<p>Consider an example.  Suppose you are trying to insert a string
value into a database table where the string value was obtained from
user input.  Suppose the string to be inserted is stored in a variable
named zString.  The code to do the insertion might look like this:</p>

<blockquote><pre>
sqlite_exec_printf(db,
  "INSERT INTO table1 VALUES('%s')",
  0, 0, 0, zString);
</pre></blockquote>

<p>If the zString variable holds text like "Hello", then this statement
will work just fine.  But suppose the user enters a string like 
"Hi y'all!".  The SQL statement generated reads as follows:

<blockquote><pre>
INSERT INTO table1 VALUES('Hi y'all')
</pre></blockquote>

<p>This is not valid SQL because of the apostrophy in the word "y'all".
But if the %q formatting option is used instead of %s, like this:</p>

<blockquote><pre>
sqlite_exec_printf(db,
  "INSERT INTO table1 VALUES('%q')",
  0, 0, 0, zString);
</pre></blockquote>

<p>Then the generated SQL will look like the following:</p>

<blockquote><pre>
INSERT INTO table1 VALUES('Hi y''all')
</pre></blockquote>

<p>Here the apostrophy has been escaped and the SQL statement is well-formed.
When generating SQL on-the-fly from data that might contain a
single-quote character ('), it is always a good idea to use the
SQLite printf routines and the %q formatting option instead of <b>sprintf</b>.
</p>

<p>If the %Q formatting option is used instead of %q, like this:</p>

<blockquote><pre>
sqlite_exec_printf(db,
  "INSERT INTO table1 VALUES(%Q)",
  0, 0, 0, zString);
</pre></blockquote>

<p>Then the generated SQL will look like the following:</p>

<blockquote><pre>
INSERT INTO table1 VALUES('Hi y''all')
</pre></blockquote>

<p>If the value of the zString variable is NULL, the generated SQL
will look like the following:</p>

<blockquote><pre>
INSERT INTO table1 VALUES(NULL)
</pre></blockquote>

<p>All of the _printf() routines above are built around the following
two functions:</p>

<blockquote><pre>
char *sqlite_mprintf(const char *zFormat, ...);
char *sqlite_vmprintf(const char *zFormat, va_list);
</pre></blockquote>

<p>The <b>sqlite_mprintf()</b> routine works like the the standard library
<b>sprintf()</b> except that it writes its results into memory obtained
from malloc() and returns a pointer to the malloced buffer.  
<b>sqlite_mprintf()</b> also understands the %q and %Q extensions described
above.  The <b>sqlite_vmprintf()</b> is a varargs version of the same
routine.  The string pointer that these routines return should be freed
by passing it to <b>sqlite_freemem()</b>.
</p>

<h4>3.10 Performing background jobs during large queries</h3>

<p>The <b>sqlite_progress_handler()</b> routine can be used to register a
callback routine with an SQLite database to be invoked periodically during long
running calls to <b>sqlite_exec()</b>, <b>sqlite_step()</b> and the various
wrapper functions.
</p>

<p>The callback is invoked every N virtual machine operations, where N is
supplied as the second argument to <b>sqlite_progress_handler()</b>. The third
and fourth arguments to <b>sqlite_progress_handler()</b> are a pointer to the
routine to be invoked and a void pointer to be passed as the first argument to
it.
</p>

<p>The time taken to execute each virtual machine operation can vary based on
many factors.  A typical value for a 1 GHz PC is between half and three million
per second but may be much higher or lower, depending on the query.  As such it
is difficult to schedule background operations based on virtual machine
operations. Instead, it is recommended that a callback be scheduled relatively
frequently (say every 1000 instructions) and external timer routines used to
determine whether or not background jobs need to be run.  
</p>

<a name="cfunc">
<h3>4.0 Adding New SQL Functions</h3>

<p>Beginning with version 2.4.0, SQLite allows the SQL language to be
extended with new functions implemented as C code.  The following interface
is used:
</p>

<blockquote><pre>
typedef struct sqlite_func sqlite_func;

int sqlite_create_function(
  sqlite *db,
  const char *zName,
  int nArg,
  void (*xFunc)(sqlite_func*,int,const char**),
  void *pUserData
);
int sqlite_create_aggregate(
  sqlite *db,
  const char *zName,
  int nArg,
  void (*xStep)(sqlite_func*,int,const char**),
  void (*xFinalize)(sqlite_func*),
  void *pUserData
);

char *sqlite_set_result_string(sqlite_func*,const char*,int);
void sqlite_set_result_int(sqlite_func*,int);
void sqlite_set_result_double(sqlite_func*,double);
void sqlite_set_result_error(sqlite_func*,const char*,int);

void *sqlite_user_data(sqlite_func*);
void *sqlite_aggregate_context(sqlite_func*, int nBytes);
int sqlite_aggregate_count(sqlite_func*);
</pre></blockquote>

<p>
The <b>sqlite_create_function()</b> interface is used to create 
regular functions and <b>sqlite_create_aggregate()</b> is used to
create new aggregate functions.  In both cases, the <b>db</b>
parameter is an open SQLite database on which the functions should
be registered, <b>zName</b> is the name of the new function,
<b>nArg</b> is the number of arguments, and <b>pUserData</b> is
a pointer which is passed through unchanged to the C implementation
of the function.  Both routines return 0 on success and non-zero
if there are any errors.
</p>

<p>
The length of a function name may not exceed 255 characters.
Any attempt to create a function whose name exceeds 255 characters
in length will result in an error.
</p>

<p>
For regular functions, the <b>xFunc</b> callback is invoked once
for each function call.  The implementation of xFunc should call
one of the <b>sqlite_set_result_...</b> interfaces to return its
result.  The <b>sqlite_user_data()</b> routine can be used to
retrieve the <b>pUserData</b> pointer that was passed in when the
function was registered.
</p>

<p>
For aggregate functions, the <b>xStep</b> callback is invoked once
for each row in the result and then <b>xFinalize</b> is invoked at the
end to compute a final answer.  The xStep routine can use the
<b>sqlite_aggregate_context()</b> interface to allocate memory that
will be unique to that particular instance of the SQL function.
This memory will be automatically deleted after xFinalize is called.
The <b>sqlite_aggregate_count()</b> routine can be used to find out
how many rows of data were passed to the aggregate.  The xFinalize
callback should invoke one of the <b>sqlite_set_result_...</b>
interfaces to set the final result of the aggregate.
</p>

<p>
SQLite now implements all of its built-in functions using this
interface.  For additional information and examples on how to create
new SQL functions, review the SQLite source code in the file
<b>func.c</b>.
</p>

<h3>5.0 Multi-Threading And SQLite</h3>

<p>
If SQLite is compiled with the THREADSAFE preprocessor macro set to 1,
then it is safe to use SQLite from two or more threads of the same process
at the same time.  But each thread should have its own <b>sqlite*</b>
pointer returned from <b>sqlite_open</b>.  It is never safe for two
or more threads to access the same <b>sqlite*</b> pointer at the same time.
</p>

<p>
In precompiled SQLite libraries available on the website, the Unix
versions are compiled with THREADSAFE turned off but the windows
versions are compiled with THREADSAFE turned on.  If you need something
different that this you will have to recompile.
</p>

<p>
Under Unix, an <b>sqlite*</b> pointer should not be carried across a
<b>fork()</b> system call into the child process.  The child process
should open its own copy of the database after the <b>fork()</b>.
</p>

<h3>6.0 Usage Examples</h3>

<p>For examples of how the SQLite C/C++ interface can be used,
refer to the source code for the <b>sqlite</b> program in the
file <b>src/shell.c</b> of the source tree.
Additional information about sqlite is available at
<a href="sqlite.html">sqlite.html</a>.
See also the sources to the Tcl interface for SQLite in
the source file <b>src/tclsqlite.c</b>.</p>
}
footer $rcsid