summaryrefslogtreecommitdiffstats
path: root/www/c_interface.html
diff options
context:
space:
mode:
Diffstat (limited to 'www/c_interface.html')
-rw-r--r--www/c_interface.html1241
1 files changed, 1241 insertions, 0 deletions
diff --git a/www/c_interface.html b/www/c_interface.html
new file mode 100644
index 0000000..5f59447
--- /dev/null
+++ b/www/c_interface.html
@@ -0,0 +1,1241 @@
+<!DOCTYPE html>
+<html><head>
+<meta name="viewport" content="width=device-width, initial-scale=1.0">
+<meta http-equiv="content-type" content="text/html; charset=UTF-8">
+<link href="sqlite.css" rel="stylesheet">
+<title>The C language interface to SQLite Version 2</title>
+<!-- path= -->
+</head>
+<body>
+<div class=nosearch>
+<a href="index.html">
+<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
+</a>
+<div><!-- IE hack to prevent disappearing logo --></div>
+<div class="tagline desktoponly">
+Small. Fast. Reliable.<br>Choose any three.
+</div>
+<div class="menu mainmenu">
+<ul>
+<li><a href="index.html">Home</a>
+<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
+<li class='wideonly'><a href='about.html'>About</a>
+<li class='desktoponly'><a href="docs.html">Documentation</a>
+<li class='desktoponly'><a href="download.html">Download</a>
+<li class='wideonly'><a href='copyright.html'>License</a>
+<li class='desktoponly'><a href="support.html">Support</a>
+<li class='desktoponly'><a href="prosupport.html">Purchase</a>
+<li class='search' id='search_menubutton'>
+<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
+</ul>
+</div>
+<div class="menu submenu" id="submenu">
+<ul>
+<li><a href='about.html'>About</a>
+<li><a href='docs.html'>Documentation</a>
+<li><a href='download.html'>Download</a>
+<li><a href='support.html'>Support</a>
+<li><a href='prosupport.html'>Purchase</a>
+</ul>
+</div>
+<div class="searchmenu" id="searchmenu">
+<form method="GET" action="search">
+<select name="s" id="searchtype">
+<option value="d">Search Documentation</option>
+<option value="c">Search Changelog</option>
+</select>
+<input type="text" name="q" id="searchbox" value="">
+<input type="submit" value="Go">
+</form>
+</div>
+</div>
+<script>
+function toggle_div(nm) {
+var w = document.getElementById(nm);
+if( w.style.display=="block" ){
+w.style.display = "none";
+}else{
+w.style.display = "block";
+}
+}
+function toggle_search() {
+var w = document.getElementById("searchmenu");
+if( w.style.display=="block" ){
+w.style.display = "none";
+} else {
+w.style.display = "block";
+setTimeout(function(){
+document.getElementById("searchbox").focus()
+}, 30);
+}
+}
+function div_off(nm){document.getElementById(nm).style.display="none";}
+window.onbeforeunload = function(e){div_off("submenu");}
+/* Disable the Search feature if we are not operating from CGI, since */
+/* Search is accomplished using CGI and will not work without it. */
+if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
+document.getElementById("search_menubutton").style.display = "none";
+}
+/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
+function hideorshow(btn,obj){
+var x = document.getElementById(obj);
+var b = document.getElementById(btn);
+if( x.style.display!='none' ){
+x.style.display = 'none';
+b.innerHTML='show';
+}else{
+x.style.display = '';
+b.innerHTML='hide';
+}
+return false;
+}
+var antiRobot = 0;
+function antiRobotGo(){
+if( antiRobot!=3 ) return;
+antiRobot = 7;
+var j = document.getElementById("mtimelink");
+if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
+}
+function antiRobotDefense(){
+document.body.onmousedown=function(){
+antiRobot |= 2;
+antiRobotGo();
+document.body.onmousedown=null;
+}
+document.body.onmousemove=function(){
+antiRobot |= 2;
+antiRobotGo();
+document.body.onmousemove=null;
+}
+setTimeout(function(){
+antiRobot |= 1;
+antiRobotGo();
+}, 100)
+antiRobotGo();
+}
+antiRobotDefense();
+</script>
+
+
+<p>
+<center><table border="1" cellpadding="10" width="75%">
+<tr><td bgcolor="#ffffbb">
+<b>Editorial Note:</b>
+This document describes SQLite version 2, which was deprecated and
+replaced by SQLite3 in 2004.
+This document is retained as part of the historical record of SQLite.
+Modern programmers should refer to
+more up-to-date documentation on SQLite is available elsewhere
+on this website.
+</table></center>
+<h2>The C language interface to SQLite Version 2</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"></a>
+<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"></a>
+<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"></a>
+<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 zero-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"></a>
+<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&#91;i]&#91;0] == 0
+</pre></blockquote>
+<p>But if the i-th parameter is NULL we will get:</p>
+<blockquote><pre>
+argv&#91;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"></a>
+<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 constraint 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 a
+previously 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 */
+);
+</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&#91;];
+
+const char sqlite_encoding&#91;];
+
+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 existence, 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&#91;0] = "employee_name"<br>
+result&#91;1] = "login"<br>
+result&#91;2] = "host"<br>
+result&#91;3] = "dummy"<br>
+result&#91;4] = "No such user"<br>
+result&#91;5] = 0<br>
+result&#91;6] = "D. Richard Hipp"<br>
+result&#91;7] = "drh"<br>
+result&#91;8] = "zadok"
+</blockquote>
+
+<p>Notice that the "host" value for the "dummy" record is NULL so
+the result&#91;] 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:
+</p>
+
+<blockquote>
+nrow = 0<br>
+ncolumn = 3<br>
+result&#91;0] = "employee_name"<br>
+result&#91;1] = "login"<br>
+result&#91;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 apostrophe 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 apostrophe 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 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"></a>
+<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 <a href="https://sqlite.org/src/file/src/shell.c.in">src/shell.c</a>
+of the source tree.
+Additional information about sqlite is available at
+<a href="cli.html">cli.html</a>.
+See also the sources to the Tcl interface for SQLite in
+the source file
+<a href="https://sqlite.org/src/file/src/tclsqlite.c">src/tclsqlite.c</a>.</p>
+<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/c_interface.in?m=37fc83a59c4d4a606">2020-04-14 16:00:55</a> UTC </small></i></p>
+