| 1 |
<!DOCTYPE html> |
| 2 |
<html><head> |
| 3 |
<meta name="viewport" content="width=device-width, initial-scale=1.0"> |
| 4 |
<meta http-equiv="content-type" content="text/html; charset=UTF-8"> |
| 5 |
<link href="sqlite.css" rel="stylesheet"> |
| 6 |
<title>The C language interface to SQLite Version 2</title> |
| 7 |
<!-- path= --> |
| 8 |
</head> |
| 9 |
<body> |
| 10 |
<div class=nosearch> |
| 11 |
<a href="index.html"> |
| 12 |
<img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0"> |
| 13 |
</a> |
| 14 |
<div><!-- IE hack to prevent disappearing logo --></div> |
| 15 |
<div class="tagline desktoponly"> |
| 16 |
Small. Fast. Reliable.<br>Choose any three. |
| 17 |
</div> |
| 18 |
<div class="menu mainmenu"> |
| 19 |
<ul> |
| 20 |
<li><a href="index.html">Home</a> |
| 21 |
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> |
| 22 |
<li class='wideonly'><a href='about.html'>About</a> |
| 23 |
<li class='desktoponly'><a href="docs.html">Documentation</a> |
| 24 |
<li class='desktoponly'><a href="download.html">Download</a> |
| 25 |
<li class='wideonly'><a href='copyright.html'>License</a> |
| 26 |
<li class='desktoponly'><a href="support.html">Support</a> |
| 27 |
<li class='desktoponly'><a href="prosupport.html">Purchase</a> |
| 28 |
<li class='search' id='search_menubutton'> |
| 29 |
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> |
| 30 |
</ul> |
| 31 |
</div> |
| 32 |
<div class="menu submenu" id="submenu"> |
| 33 |
<ul> |
| 34 |
<li><a href='about.html'>About</a> |
| 35 |
<li><a href='docs.html'>Documentation</a> |
| 36 |
<li><a href='download.html'>Download</a> |
| 37 |
<li><a href='support.html'>Support</a> |
| 38 |
<li><a href='prosupport.html'>Purchase</a> |
| 39 |
</ul> |
| 40 |
</div> |
| 41 |
<div class="searchmenu" id="searchmenu"> |
| 42 |
<form method="GET" action="search"> |
| 43 |
<select name="s" id="searchtype"> |
| 44 |
<option value="d">Search Documentation</option> |
| 45 |
<option value="c">Search Changelog</option> |
| 46 |
</select> |
| 47 |
<input type="text" name="q" id="searchbox" value=""> |
| 48 |
<input type="submit" value="Go"> |
| 49 |
</form> |
| 50 |
</div> |
| 51 |
</div> |
| 52 |
<script> |
| 53 |
function toggle_div(nm) { |
| 54 |
var w = document.getElementById(nm); |
| 55 |
if( w.style.display=="block" ){ |
| 56 |
w.style.display = "none"; |
| 57 |
}else{ |
| 58 |
w.style.display = "block"; |
| 59 |
} |
| 60 |
} |
| 61 |
function toggle_search() { |
| 62 |
var w = document.getElementById("searchmenu"); |
| 63 |
if( w.style.display=="block" ){ |
| 64 |
w.style.display = "none"; |
| 65 |
} else { |
| 66 |
w.style.display = "block"; |
| 67 |
setTimeout(function(){ |
| 68 |
document.getElementById("searchbox").focus() |
| 69 |
}, 30); |
| 70 |
} |
| 71 |
} |
| 72 |
function div_off(nm){document.getElementById(nm).style.display="none";} |
| 73 |
window.onbeforeunload = function(e){div_off("submenu");} |
| 74 |
/* Disable the Search feature if we are not operating from CGI, since */ |
| 75 |
/* Search is accomplished using CGI and will not work without it. */ |
| 76 |
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ |
| 77 |
document.getElementById("search_menubutton").style.display = "none"; |
| 78 |
} |
| 79 |
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ |
| 80 |
function hideorshow(btn,obj){ |
| 81 |
var x = document.getElementById(obj); |
| 82 |
var b = document.getElementById(btn); |
| 83 |
if( x.style.display!='none' ){ |
| 84 |
x.style.display = 'none'; |
| 85 |
b.innerHTML='show'; |
| 86 |
}else{ |
| 87 |
x.style.display = ''; |
| 88 |
b.innerHTML='hide'; |
| 89 |
} |
| 90 |
return false; |
| 91 |
} |
| 92 |
var antiRobot = 0; |
| 93 |
function antiRobotGo(){ |
| 94 |
if( antiRobot!=3 ) return; |
| 95 |
antiRobot = 7; |
| 96 |
var j = document.getElementById("mtimelink"); |
| 97 |
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); |
| 98 |
} |
| 99 |
function antiRobotDefense(){ |
| 100 |
document.body.onmousedown=function(){ |
| 101 |
antiRobot |= 2; |
| 102 |
antiRobotGo(); |
| 103 |
document.body.onmousedown=null; |
| 104 |
} |
| 105 |
document.body.onmousemove=function(){ |
| 106 |
antiRobot |= 2; |
| 107 |
antiRobotGo(); |
| 108 |
document.body.onmousemove=null; |
| 109 |
} |
| 110 |
setTimeout(function(){ |
| 111 |
antiRobot |= 1; |
| 112 |
antiRobotGo(); |
| 113 |
}, 100) |
| 114 |
antiRobotGo(); |
| 115 |
} |
| 116 |
antiRobotDefense(); |
| 117 |
</script> |
| 118 |
|
| 119 |
|
| 120 |
<p> |
| 121 |
<center><table border="1" cellpadding="10" width="75%"> |
| 122 |
<tr><td bgcolor="#ffffbb"> |
| 123 |
<b>Editorial Note:</b> |
| 124 |
This document describes SQLite version 2, which was deprecated and |
| 125 |
replaced by SQLite3 in 2004. |
| 126 |
This document is retained as part of the historical record of SQLite. |
| 127 |
Modern programmers should refer to |
| 128 |
more up-to-date documentation on SQLite is available elsewhere |
| 129 |
on this website. |
| 130 |
</table></center> |
| 131 |
<h2>The C language interface to SQLite Version 2</h2> |
| 132 |
|
| 133 |
<p>The SQLite library is designed to be very easy to use from |
| 134 |
a C or C++ program. This document gives an overview of the C/C++ |
| 135 |
programming interface.</p> |
| 136 |
|
| 137 |
<h3>1.0 The Core API</h3> |
| 138 |
|
| 139 |
<p>The interface to the SQLite library consists of three core functions, |
| 140 |
one opaque data structure, and some constants used as return values. |
| 141 |
The core interface is as follows:</p> |
| 142 |
|
| 143 |
<blockquote><pre> |
| 144 |
typedef struct sqlite sqlite; |
| 145 |
#define SQLITE_OK 0 /* Successful result */ |
| 146 |
|
| 147 |
sqlite *sqlite_open(const char *dbname, int mode, char **errmsg); |
| 148 |
|
| 149 |
void sqlite_close(sqlite *db); |
| 150 |
|
| 151 |
int sqlite_exec( |
| 152 |
sqlite *db, |
| 153 |
char *sql, |
| 154 |
int (*xCallback)(void*,int,char**,char**), |
| 155 |
void *pArg, |
| 156 |
char **errmsg |
| 157 |
); |
| 158 |
</pre></blockquote> |
| 159 |
|
| 160 |
<p> |
| 161 |
The above is all you really need to know in order to use SQLite |
| 162 |
in your C or C++ programs. There are other interface functions |
| 163 |
available (and described below) but we will begin by describing |
| 164 |
the core functions shown above. |
| 165 |
</p> |
| 166 |
|
| 167 |
<a name="sqlite_open"></a> |
| 168 |
<h4>1.1 Opening a database</h4> |
| 169 |
|
| 170 |
<p>Use the <b>sqlite_open</b> function to open an existing SQLite |
| 171 |
database or to create a new SQLite database. The first argument |
| 172 |
is the database name. The second argument is intended to signal |
| 173 |
whether the database is going to be used for reading and writing |
| 174 |
or just for reading. But in the current implementation, the |
| 175 |
second argument to <b>sqlite_open</b> is ignored. |
| 176 |
The third argument is a pointer to a string pointer. |
| 177 |
If the third argument is not NULL and an error occurs |
| 178 |
while trying to open the database, then an error message will be |
| 179 |
written to memory obtained from malloc() and *errmsg will be made |
| 180 |
to point to this error message. The calling function is responsible |
| 181 |
for freeing the memory when it has finished with it.</p> |
| 182 |
|
| 183 |
<p>The name of an SQLite database is the name of a file that will |
| 184 |
contain the database. If the file does not exist, SQLite attempts |
| 185 |
to create and initialize it. If the file is read-only (due to |
| 186 |
permission bits or because it is located on read-only media like |
| 187 |
a CD-ROM) then SQLite opens the database for reading only. The |
| 188 |
entire SQL database is stored in a single file on the disk. But |
| 189 |
additional temporary files may be created during the execution of |
| 190 |
an SQL command in order to store the database rollback journal or |
| 191 |
temporary and intermediate results of a query.</p> |
| 192 |
|
| 193 |
<p>The return value of the <b>sqlite_open</b> function is a |
| 194 |
pointer to an opaque <b>sqlite</b> structure. This pointer will |
| 195 |
be the first argument to all subsequent SQLite function calls that |
| 196 |
deal with the same database. NULL is returned if the open fails |
| 197 |
for any reason.</p> |
| 198 |
|
| 199 |
<a name="sqlite_close"></a> |
| 200 |
<h4>1.2 Closing the database</h4> |
| 201 |
|
| 202 |
<p>To close an SQLite database, call the <b>sqlite_close</b> |
| 203 |
function passing it the sqlite structure pointer that was obtained |
| 204 |
from a prior call to <b>sqlite_open</b>. |
| 205 |
If a transaction is active when the database is closed, the transaction |
| 206 |
is rolled back.</p> |
| 207 |
|
| 208 |
<a name="sqlite_exec"></a> |
| 209 |
<h4>1.3 Executing SQL statements</h4> |
| 210 |
|
| 211 |
<p>The <b>sqlite_exec</b> function is used to process SQL statements |
| 212 |
and queries. This function requires 5 parameters as follows:</p> |
| 213 |
|
| 214 |
<ol> |
| 215 |
<li><p>A pointer to the sqlite structure obtained from a prior call |
| 216 |
to <b>sqlite_open</b>.</p></li> |
| 217 |
<li><p>A zero-terminated string containing the text of one or more |
| 218 |
SQL statements and/or queries to be processed.</p></li> |
| 219 |
<li><p>A pointer to a callback function which is invoked once for each |
| 220 |
row in the result of a query. This argument may be NULL, in which |
| 221 |
case no callbacks will ever be invoked.</p></li> |
| 222 |
<li><p>A pointer that is forwarded to become the first argument |
| 223 |
to the callback function.</p></li> |
| 224 |
<li><p>A pointer to an error string. Error messages are written to space |
| 225 |
obtained from malloc() and the error string is made to point to |
| 226 |
the malloced space. The calling function is responsible for freeing |
| 227 |
this space when it has finished with it. |
| 228 |
This argument may be NULL, in which case error messages are not |
| 229 |
reported back to the calling function.</p></li> |
| 230 |
</ol> |
| 231 |
|
| 232 |
<p> |
| 233 |
The callback function is used to receive the results of a query. A |
| 234 |
prototype for the callback function is as follows:</p> |
| 235 |
|
| 236 |
<blockquote><pre> |
| 237 |
int Callback(void *pArg, int argc, char **argv, char **columnNames){ |
| 238 |
return 0; |
| 239 |
} |
| 240 |
</pre></blockquote> |
| 241 |
|
| 242 |
<a name="callback_row_data"></a> |
| 243 |
<p>The first argument to the callback is just a copy of the fourth argument |
| 244 |
to <b>sqlite_exec</b> This parameter can be used to pass arbitrary |
| 245 |
information through to the callback function from client code. |
| 246 |
The second argument is the number of columns in the query result. |
| 247 |
The third argument is an array of pointers to strings where each string |
| 248 |
is a single column of the result for that record. Note that the |
| 249 |
callback function reports a NULL value in the database as a NULL pointer, |
| 250 |
which is very different from an empty string. If the i-th parameter |
| 251 |
is an empty string, we will get:</p> |
| 252 |
<blockquote><pre> |
| 253 |
argv[i][0] == 0 |
| 254 |
</pre></blockquote> |
| 255 |
<p>But if the i-th parameter is NULL we will get:</p> |
| 256 |
<blockquote><pre> |
| 257 |
argv[i] == 0 |
| 258 |
</pre></blockquote> |
| 259 |
|
| 260 |
<p>The names of the columns are contained in the first <i>argc</i> |
| 261 |
entries of the fourth argument. |
| 262 |
If the <a href="pragma.html">SHOW_DATATYPES</a> pragma |
| 263 |
is on (it is off by default) then |
| 264 |
the second <i>argc</i> entries in the 4th argument are the datatypes |
| 265 |
for the corresponding columns. |
| 266 |
</p> |
| 267 |
|
| 268 |
<p>If the <a href="pragma.html#pragma_empty_result_callbacks"> |
| 269 |
EMPTY_RESULT_CALLBACKS</a> pragma is set to ON and the result of |
| 270 |
a query is an empty set, then the callback is invoked once with the |
| 271 |
third parameter (argv) set to 0. In other words |
| 272 |
<blockquote><pre> |
| 273 |
argv == 0 |
| 274 |
</pre></blockquote> |
| 275 |
The second parameter (argc) |
| 276 |
and the fourth parameter (columnNames) are still valid |
| 277 |
and can be used to determine the number and names of the result |
| 278 |
columns if there had been a result. |
| 279 |
The default behavior is not to invoke the callback at all if the |
| 280 |
result set is empty.</p> |
| 281 |
|
| 282 |
<a name="callback_returns_nonzero"></a> |
| 283 |
<p>The callback function should normally return 0. If the callback |
| 284 |
function returns non-zero, the query is immediately aborted and |
| 285 |
<b>sqlite_exec</b> will return SQLITE_ABORT.</p> |
| 286 |
|
| 287 |
<h4>1.4 Error Codes</h4> |
| 288 |
|
| 289 |
<p> |
| 290 |
The <b>sqlite_exec</b> function normally returns SQLITE_OK. But |
| 291 |
if something goes wrong it can return a different value to indicate |
| 292 |
the type of error. Here is a complete list of the return codes: |
| 293 |
</p> |
| 294 |
|
| 295 |
<blockquote><pre> |
| 296 |
#define SQLITE_OK 0 /* Successful result */ |
| 297 |
#define SQLITE_ERROR 1 /* SQL error or missing database */ |
| 298 |
#define SQLITE_INTERNAL 2 /* An internal logic error in SQLite */ |
| 299 |
#define SQLITE_PERM 3 /* Access permission denied */ |
| 300 |
#define SQLITE_ABORT 4 /* Callback routine requested an abort */ |
| 301 |
#define SQLITE_BUSY 5 /* The database file is locked */ |
| 302 |
#define SQLITE_LOCKED 6 /* A table in the database is locked */ |
| 303 |
#define SQLITE_NOMEM 7 /* A malloc() failed */ |
| 304 |
#define SQLITE_READONLY 8 /* Attempt to write a readonly database */ |
| 305 |
#define SQLITE_INTERRUPT 9 /* Operation terminated by sqlite_interrupt() */ |
| 306 |
#define SQLITE_IOERR 10 /* Some kind of disk I/O error occurred */ |
| 307 |
#define SQLITE_CORRUPT 11 /* The database disk image is malformed */ |
| 308 |
#define SQLITE_NOTFOUND 12 /* (Internal Only) Table or record not found */ |
| 309 |
#define SQLITE_FULL 13 /* Insertion failed because database is full */ |
| 310 |
#define SQLITE_CANTOPEN 14 /* Unable to open the database file */ |
| 311 |
#define SQLITE_PROTOCOL 15 /* Database lock protocol error */ |
| 312 |
#define SQLITE_EMPTY 16 /* (Internal Only) Database table is empty */ |
| 313 |
#define SQLITE_SCHEMA 17 /* The database schema changed */ |
| 314 |
#define SQLITE_TOOBIG 18 /* Too much data for one row of a table */ |
| 315 |
#define SQLITE_CONSTRAINT 19 /* Abort due to constraint violation */ |
| 316 |
#define SQLITE_MISMATCH 20 /* Data type mismatch */ |
| 317 |
#define SQLITE_MISUSE 21 /* Library used incorrectly */ |
| 318 |
#define SQLITE_NOLFS 22 /* Uses OS features not supported on host */ |
| 319 |
#define SQLITE_AUTH 23 /* Authorization denied */ |
| 320 |
#define SQLITE_ROW 100 /* sqlite_step() has another row ready */ |
| 321 |
#define SQLITE_DONE 101 /* sqlite_step() has finished executing */ |
| 322 |
</pre></blockquote> |
| 323 |
|
| 324 |
<p> |
| 325 |
The meanings of these various return values are as follows: |
| 326 |
</p> |
| 327 |
|
| 328 |
<blockquote> |
| 329 |
<dl> |
| 330 |
<dt>SQLITE_OK</dt> |
| 331 |
<dd><p>This value is returned if everything worked and there were no errors. |
| 332 |
</p></dd> |
| 333 |
<dt>SQLITE_INTERNAL</dt> |
| 334 |
<dd><p>This value indicates that an internal consistency check within |
| 335 |
the SQLite library failed. This can only happen if there is a bug in |
| 336 |
the SQLite library. If you ever get an SQLITE_INTERNAL reply from |
| 337 |
an <b>sqlite_exec</b> call, please report the problem on the SQLite |
| 338 |
mailing list. |
| 339 |
</p></dd> |
| 340 |
<dt>SQLITE_ERROR</dt> |
| 341 |
<dd><p>This return value indicates that there was an error in the SQL |
| 342 |
that was passed into the <b>sqlite_exec</b>. |
| 343 |
</p></dd> |
| 344 |
<dt>SQLITE_PERM</dt> |
| 345 |
<dd><p>This return value says that the access permissions on the database |
| 346 |
file are such that the file cannot be opened. |
| 347 |
</p></dd> |
| 348 |
<dt>SQLITE_ABORT</dt> |
| 349 |
<dd><p>This value is returned if the callback function returns non-zero. |
| 350 |
</p></dd> |
| 351 |
<dt>SQLITE_BUSY</dt> |
| 352 |
<dd><p>This return code indicates that another program or thread has |
| 353 |
the database locked. SQLite allows two or more threads to read the |
| 354 |
database at the same time, but only one thread can have the database |
| 355 |
open for writing at the same time. Locking in SQLite is on the |
| 356 |
entire database.</p> |
| 357 |
</p></dd> |
| 358 |
<dt>SQLITE_LOCKED</dt> |
| 359 |
<dd><p>This return code is similar to SQLITE_BUSY in that it indicates |
| 360 |
that the database is locked. But the source of the lock is a recursive |
| 361 |
call to <b>sqlite_exec</b>. This return can only occur if you attempt |
| 362 |
to invoke sqlite_exec from within a callback routine of a query |
| 363 |
from a prior invocation of sqlite_exec. Recursive calls to |
| 364 |
sqlite_exec are allowed as long as they do |
| 365 |
not attempt to write the same table. |
| 366 |
</p></dd> |
| 367 |
<dt>SQLITE_NOMEM</dt> |
| 368 |
<dd><p>This value is returned if a call to <b>malloc</b> fails. |
| 369 |
</p></dd> |
| 370 |
<dt>SQLITE_READONLY</dt> |
| 371 |
<dd><p>This return code indicates that an attempt was made to write to |
| 372 |
a database file that is opened for reading only. |
| 373 |
</p></dd> |
| 374 |
<dt>SQLITE_INTERRUPT</dt> |
| 375 |
<dd><p>This value is returned if a call to <b>sqlite_interrupt</b> |
| 376 |
interrupts a database operation in progress. |
| 377 |
</p></dd> |
| 378 |
<dt>SQLITE_IOERR</dt> |
| 379 |
<dd><p>This value is returned if the operating system informs SQLite |
| 380 |
that it is unable to perform some disk I/O operation. This could mean |
| 381 |
that there is no more space left on the disk. |
| 382 |
</p></dd> |
| 383 |
<dt>SQLITE_CORRUPT</dt> |
| 384 |
<dd><p>This value is returned if SQLite detects that the database it is |
| 385 |
working on has become corrupted. Corruption might occur due to a rogue |
| 386 |
process writing to the database file or it might happen due to a |
| 387 |
previously undetected logic error in SQLite. This value is also |
| 388 |
returned if a disk I/O error occurs in such a way that SQLite is forced |
| 389 |
to leave the database file in a corrupted state. The latter should only |
| 390 |
happen due to a hardware or operating system malfunction. |
| 391 |
</p></dd> |
| 392 |
<dt>SQLITE_FULL</dt> |
| 393 |
<dd><p>This value is returned if an insertion failed because there is |
| 394 |
no space left on the disk, or the database is too big to hold any |
| 395 |
more information. The latter case should only occur for databases |
| 396 |
that are larger than 2GB in size. |
| 397 |
</p></dd> |
| 398 |
<dt>SQLITE_CANTOPEN</dt> |
| 399 |
<dd><p>This value is returned if the database file could not be opened |
| 400 |
for some reason. |
| 401 |
</p></dd> |
| 402 |
<dt>SQLITE_PROTOCOL</dt> |
| 403 |
<dd><p>This value is returned if some other process is messing with |
| 404 |
file locks and has violated the file locking protocol that SQLite uses |
| 405 |
on its rollback journal files. |
| 406 |
</p></dd> |
| 407 |
<dt>SQLITE_SCHEMA</dt> |
| 408 |
<dd><p>When the database is first opened, SQLite reads the database schema |
| 409 |
into memory and uses that schema to parse new SQL statements. If another |
| 410 |
process changes the schema, the command currently being processed will |
| 411 |
abort because the virtual machine code generated assumed the old |
| 412 |
schema. This is the return code for such cases. Retrying the |
| 413 |
command usually will clear the problem. |
| 414 |
</p></dd> |
| 415 |
<dt>SQLITE_TOOBIG</dt> |
| 416 |
<dd><p>SQLite will not store more than about 1 megabyte of data in a single |
| 417 |
row of a single table. If you attempt to store more than 1 megabyte |
| 418 |
in a single row, this is the return code you get. |
| 419 |
</p></dd> |
| 420 |
<dt>SQLITE_CONSTRAINT</dt> |
| 421 |
<dd><p>This constant is returned if the SQL statement would have violated |
| 422 |
a database constraint. |
| 423 |
</p></dd> |
| 424 |
<dt>SQLITE_MISMATCH</dt> |
| 425 |
<dd><p>This error occurs when there is an attempt to insert non-integer |
| 426 |
data into a column labeled INTEGER PRIMARY KEY. For most columns, SQLite |
| 427 |
ignores the data type and allows any kind of data to be stored. But |
| 428 |
an INTEGER PRIMARY KEY column is only allowed to store integer data. |
| 429 |
</p></dd> |
| 430 |
<dt>SQLITE_MISUSE</dt> |
| 431 |
<dd><p>This error might occur if one or more of the SQLite API routines |
| 432 |
is used incorrectly. Examples of incorrect usage include calling |
| 433 |
<b>sqlite_exec</b> after the database has been closed using |
| 434 |
<b>sqlite_close</b> or |
| 435 |
calling <b>sqlite_exec</b> with the same |
| 436 |
database pointer simultaneously from two separate threads. |
| 437 |
</p></dd> |
| 438 |
<dt>SQLITE_NOLFS</dt> |
| 439 |
<dd><p>This error means that you have attempted to create or access a |
| 440 |
database file that is larger than 2GB on a legacy Unix machine that |
| 441 |
lacks large file support. |
| 442 |
</p></dd> |
| 443 |
<dt>SQLITE_AUTH</dt> |
| 444 |
<dd><p>This error indicates that the authorizer callback |
| 445 |
has disallowed the SQL you are attempting to execute. |
| 446 |
</p></dd> |
| 447 |
<dt>SQLITE_ROW</dt> |
| 448 |
<dd><p>This is one of the return codes from the |
| 449 |
<b>sqlite_step</b> routine which is part of the non-callback API. |
| 450 |
It indicates that another row of result data is available. |
| 451 |
</p></dd> |
| 452 |
<dt>SQLITE_DONE</dt> |
| 453 |
<dd><p>This is one of the return codes from the |
| 454 |
<b>sqlite_step</b> routine which is part of the non-callback API. |
| 455 |
It indicates that the SQL statement has been completely executed and |
| 456 |
the <b>sqlite_finalize</b> routine is ready to be called. |
| 457 |
</p></dd> |
| 458 |
</dl> |
| 459 |
</blockquote> |
| 460 |
|
| 461 |
<h3>2.0 Accessing Data Without Using A Callback Function</h3> |
| 462 |
|
| 463 |
<p> |
| 464 |
The <b>sqlite_exec</b> routine described above used to be the only |
| 465 |
way to retrieve data from an SQLite database. But many programmers found |
| 466 |
it inconvenient to use a callback function to obtain results. So beginning |
| 467 |
with SQLite version 2.7.7, a second access interface is available that |
| 468 |
does not use callbacks. |
| 469 |
</p> |
| 470 |
|
| 471 |
<p> |
| 472 |
The new interface uses three separate functions to replace the single |
| 473 |
<b>sqlite_exec</b> function. |
| 474 |
</p> |
| 475 |
|
| 476 |
<blockquote><pre> |
| 477 |
typedef struct sqlite_vm sqlite_vm; |
| 478 |
|
| 479 |
int sqlite_compile( |
| 480 |
sqlite *db, /* The open database */ |
| 481 |
const char *zSql, /* SQL statement to be compiled */ |
| 482 |
const char **pzTail, /* OUT: uncompiled tail of zSql */ |
| 483 |
sqlite_vm **ppVm, /* OUT: the virtual machine to execute zSql */ |
| 484 |
char **pzErrmsg /* OUT: Error message. */ |
| 485 |
); |
| 486 |
|
| 487 |
int sqlite_step( |
| 488 |
sqlite_vm *pVm, /* The virtual machine to execute */ |
| 489 |
int *pN, /* OUT: Number of columns in result */ |
| 490 |
const char ***pazValue, /* OUT: Column data */ |
| 491 |
const char ***pazColName /* OUT: Column names and datatypes */ |
| 492 |
); |
| 493 |
|
| 494 |
int sqlite_finalize( |
| 495 |
sqlite_vm *pVm, /* The virtual machine to be finalized */ |
| 496 |
char **pzErrMsg /* OUT: Error message */ |
| 497 |
); |
| 498 |
</pre></blockquote> |
| 499 |
|
| 500 |
<p> |
| 501 |
The strategy is to compile a single SQL statement using |
| 502 |
<b>sqlite_compile</b> then invoke <b>sqlite_step</b> multiple times, |
| 503 |
once for each row of output, and finally call <b>sqlite_finalize</b> |
| 504 |
to clean up after the SQL has finished execution. |
| 505 |
</p> |
| 506 |
|
| 507 |
<h4>2.1 Compiling An SQL Statement Into A Virtual Machine</h4> |
| 508 |
|
| 509 |
<p> |
| 510 |
The <b>sqlite_compile</b> "compiles" a single SQL statement (specified |
| 511 |
by the second parameter) and generates a virtual machine that is able |
| 512 |
to execute that statement. |
| 513 |
As with most interface routines, the first parameter must be a pointer |
| 514 |
to an sqlite structure that was obtained from a prior call to |
| 515 |
<b>sqlite_open</b>. |
| 516 |
|
| 517 |
<p> |
| 518 |
A pointer to the virtual machine is stored in a pointer which is passed |
| 519 |
in as the 4th parameter. |
| 520 |
Space to hold the virtual machine is dynamically allocated. To avoid |
| 521 |
a memory leak, the calling function must invoke |
| 522 |
<b>sqlite_finalize</b> on the virtual machine after it has finished |
| 523 |
with it. |
| 524 |
The 4th parameter may be set to NULL if an error is encountered during |
| 525 |
compilation. |
| 526 |
</p> |
| 527 |
|
| 528 |
<p> |
| 529 |
If any errors are encountered during compilation, an error message is |
| 530 |
written into memory obtained from <b>malloc</b> and the 5th parameter |
| 531 |
is made to point to that memory. If the 5th parameter is NULL, then |
| 532 |
no error message is generated. If the 5th parameter is not NULL, then |
| 533 |
the calling function should dispose of the memory containing the error |
| 534 |
message by calling <b>sqlite_freemem</b>. |
| 535 |
</p> |
| 536 |
|
| 537 |
<p> |
| 538 |
If the 2nd parameter actually contains two or more statements of SQL, |
| 539 |
only the first statement is compiled. (This is different from the |
| 540 |
behavior of <b>sqlite_exec</b> which executes all SQL statements |
| 541 |
in its input string.) The 3rd parameter to <b>sqlite_compile</b> |
| 542 |
is made to point to the first character beyond the end of the first |
| 543 |
statement of SQL in the input. If the 2nd parameter contains only |
| 544 |
a single SQL statement, then the 3rd parameter will be made to point |
| 545 |
to the '\000' terminator at the end of the 2nd parameter. |
| 546 |
</p> |
| 547 |
|
| 548 |
<p> |
| 549 |
On success, <b>sqlite_compile</b> returns SQLITE_OK. |
| 550 |
Otherwise an error code is returned. |
| 551 |
</p> |
| 552 |
|
| 553 |
<h4>2.2 Step-By-Step Execution Of An SQL Statement</h4> |
| 554 |
|
| 555 |
<p> |
| 556 |
After a virtual machine has been generated using <b>sqlite_compile</b> |
| 557 |
it is executed by one or more calls to <b>sqlite_step</b>. Each |
| 558 |
invocation of <b>sqlite_step</b>, except the last one, |
| 559 |
returns a single row of the result. |
| 560 |
The number of columns in the result is stored in the integer that |
| 561 |
the 2nd parameter points to. |
| 562 |
The pointer specified by the 3rd parameter is made to point |
| 563 |
to an array of pointers to column values. |
| 564 |
The pointer in the 4th parameter is made to point to an array |
| 565 |
of pointers to column names and datatypes. |
| 566 |
The 2nd through 4th parameters to <b>sqlite_step</b> convey the |
| 567 |
same information as the 2nd through 4th parameters of the |
| 568 |
<b>callback</b> routine when using |
| 569 |
the <b>sqlite_exec</b> interface. Except, with <b>sqlite_step</b> |
| 570 |
the column datatype information is always included in the |
| 571 |
4th parameter regardless of whether or not the |
| 572 |
<a href="pragma.html">SHOW_DATATYPES</a> pragma |
| 573 |
is on or off. |
| 574 |
</p> |
| 575 |
|
| 576 |
<p> |
| 577 |
Each invocation of <b>sqlite_step</b> returns an integer code that |
| 578 |
indicates what happened during that step. This code may be |
| 579 |
SQLITE_BUSY, SQLITE_ROW, SQLITE_DONE, SQLITE_ERROR, or |
| 580 |
SQLITE_MISUSE. |
| 581 |
</p> |
| 582 |
|
| 583 |
<p> |
| 584 |
If the virtual machine is unable to open the database file because |
| 585 |
it is locked by another thread or process, <b>sqlite_step</b> |
| 586 |
will return SQLITE_BUSY. The calling function should do some other |
| 587 |
activity, or sleep, for a short amount of time to give the lock a |
| 588 |
chance to clear, then invoke <b>sqlite_step</b> again. This can |
| 589 |
be repeated as many times as desired. |
| 590 |
</p> |
| 591 |
|
| 592 |
<p> |
| 593 |
Whenever another row of result data is available, |
| 594 |
<b>sqlite_step</b> will return SQLITE_ROW. The row data is |
| 595 |
stored in an array of pointers to strings and the 2nd parameter |
| 596 |
is made to point to this array. |
| 597 |
</p> |
| 598 |
|
| 599 |
<p> |
| 600 |
When all processing is complete, <b>sqlite_step</b> will return |
| 601 |
either SQLITE_DONE or SQLITE_ERROR. SQLITE_DONE indicates that the |
| 602 |
statement completed successfully and SQLITE_ERROR indicates that there |
| 603 |
was a run-time error. (The details of the error are obtained from |
| 604 |
<b>sqlite_finalize</b>.) It is a misuse of the library to attempt |
| 605 |
to call <b>sqlite_step</b> again after it has returned SQLITE_DONE |
| 606 |
or SQLITE_ERROR. |
| 607 |
</p> |
| 608 |
|
| 609 |
<p> |
| 610 |
When <b>sqlite_step</b> returns SQLITE_DONE or SQLITE_ERROR, |
| 611 |
the *pN and *pazColName values are set to the number of columns |
| 612 |
in the result set and to the names of the columns, just as they |
| 613 |
are for an SQLITE_ROW return. This allows the calling code to |
| 614 |
find the number of result columns and the column names and datatypes |
| 615 |
even if the result set is empty. The *pazValue parameter is always |
| 616 |
set to NULL when the return code is SQLITE_DONE or SQLITE_ERROR. |
| 617 |
If the SQL being executed is a statement that does not |
| 618 |
return a result (such as an INSERT or an UPDATE) then *pN will |
| 619 |
be set to zero and *pazColName will be set to NULL. |
| 620 |
</p> |
| 621 |
|
| 622 |
<p> |
| 623 |
If you abuse the library by trying to call <b>sqlite_step</b> |
| 624 |
inappropriately it will attempt return SQLITE_MISUSE. |
| 625 |
This can happen if you call sqlite_step() on the same virtual machine |
| 626 |
at the same |
| 627 |
time from two or more threads or if you call sqlite_step() |
| 628 |
again after it returned SQLITE_DONE or SQLITE_ERROR or if you |
| 629 |
pass in an invalid virtual machine pointer to sqlite_step(). |
| 630 |
You should not depend on the SQLITE_MISUSE return code to indicate |
| 631 |
an error. It is possible that a misuse of the interface will go |
| 632 |
undetected and result in a program crash. The SQLITE_MISUSE is |
| 633 |
intended as a debugging aid only - to help you detect incorrect |
| 634 |
usage prior to a mishap. The misuse detection logic is not guaranteed |
| 635 |
to work in every case. |
| 636 |
</p> |
| 637 |
|
| 638 |
<h4>2.3 Deleting A Virtual Machine</h4> |
| 639 |
|
| 640 |
<p> |
| 641 |
Every virtual machine that <b>sqlite_compile</b> creates should |
| 642 |
eventually be handed to <b>sqlite_finalize</b>. The sqlite_finalize() |
| 643 |
procedure deallocates the memory and other resources that the virtual |
| 644 |
machine uses. Failure to call sqlite_finalize() will result in |
| 645 |
resource leaks in your program. |
| 646 |
</p> |
| 647 |
|
| 648 |
<p> |
| 649 |
The <b>sqlite_finalize</b> routine also returns the result code |
| 650 |
that indicates success or failure of the SQL operation that the |
| 651 |
virtual machine carried out. |
| 652 |
The value returned by sqlite_finalize() will be the same as would |
| 653 |
have been returned had the same SQL been executed by <b>sqlite_exec</b>. |
| 654 |
The error message returned will also be the same. |
| 655 |
</p> |
| 656 |
|
| 657 |
<p> |
| 658 |
It is acceptable to call <b>sqlite_finalize</b> on a virtual machine |
| 659 |
before <b>sqlite_step</b> has returned SQLITE_DONE. Doing so has |
| 660 |
the effect of interrupting the operation in progress. Partially completed |
| 661 |
changes will be rolled back and the database will be restored to its |
| 662 |
original state (unless an alternative recovery algorithm is selected using |
| 663 |
an ON CONFLICT clause in the SQL being executed.) The effect is the |
| 664 |
same as if a callback function of <b>sqlite_exec</b> had returned |
| 665 |
non-zero. |
| 666 |
</p> |
| 667 |
|
| 668 |
<p> |
| 669 |
It is also acceptable to call <b>sqlite_finalize</b> on a virtual machine |
| 670 |
that has never been passed to <b>sqlite_step</b> even once. |
| 671 |
</p> |
| 672 |
|
| 673 |
<h3>3.0 The Extended API</h3> |
| 674 |
|
| 675 |
<p>Only the three core routines described in section 1.0 are required to use |
| 676 |
SQLite. But there are many other functions that provide |
| 677 |
useful interfaces. These extended routines are as follows: |
| 678 |
</p> |
| 679 |
|
| 680 |
<blockquote><pre> |
| 681 |
int sqlite_last_insert_rowid(sqlite*); |
| 682 |
|
| 683 |
int sqlite_changes(sqlite*); |
| 684 |
|
| 685 |
int sqlite_get_table( |
| 686 |
sqlite*, |
| 687 |
char *sql, |
| 688 |
char ***result, |
| 689 |
int *nrow, |
| 690 |
int *ncolumn, |
| 691 |
char **errmsg |
| 692 |
); |
| 693 |
|
| 694 |
void sqlite_free_table(char**); |
| 695 |
|
| 696 |
void sqlite_interrupt(sqlite*); |
| 697 |
|
| 698 |
int sqlite_complete(const char *sql); |
| 699 |
|
| 700 |
void sqlite_busy_handler(sqlite*, int (*)(void*,const char*,int), void*); |
| 701 |
|
| 702 |
void sqlite_busy_timeout(sqlite*, int ms); |
| 703 |
|
| 704 |
const char sqlite_version[]; |
| 705 |
|
| 706 |
const char sqlite_encoding[]; |
| 707 |
|
| 708 |
int sqlite_exec_printf( |
| 709 |
sqlite*, |
| 710 |
char *sql, |
| 711 |
int (*)(void*,int,char**,char**), |
| 712 |
void*, |
| 713 |
char **errmsg, |
| 714 |
... |
| 715 |
); |
| 716 |
|
| 717 |
int sqlite_exec_vprintf( |
| 718 |
sqlite*, |
| 719 |
char *sql, |
| 720 |
int (*)(void*,int,char**,char**), |
| 721 |
void*, |
| 722 |
char **errmsg, |
| 723 |
va_list |
| 724 |
); |
| 725 |
|
| 726 |
int sqlite_get_table_printf( |
| 727 |
sqlite*, |
| 728 |
char *sql, |
| 729 |
char ***result, |
| 730 |
int *nrow, |
| 731 |
int *ncolumn, |
| 732 |
char **errmsg, |
| 733 |
... |
| 734 |
); |
| 735 |
|
| 736 |
int sqlite_get_table_vprintf( |
| 737 |
sqlite*, |
| 738 |
char *sql, |
| 739 |
char ***result, |
| 740 |
int *nrow, |
| 741 |
int *ncolumn, |
| 742 |
char **errmsg, |
| 743 |
va_list |
| 744 |
); |
| 745 |
|
| 746 |
char *sqlite_mprintf(const char *zFormat, ...); |
| 747 |
|
| 748 |
char *sqlite_vmprintf(const char *zFormat, va_list); |
| 749 |
|
| 750 |
void sqlite_freemem(char*); |
| 751 |
|
| 752 |
void sqlite_progress_handler(sqlite*, int, int (*)(void*), void*); |
| 753 |
|
| 754 |
</pre></blockquote> |
| 755 |
|
| 756 |
<p>All of the above definitions are included in the "sqlite.h" |
| 757 |
header file that comes in the source tree.</p> |
| 758 |
|
| 759 |
<h4>3.1 The ROWID of the most recent insert</h4> |
| 760 |
|
| 761 |
<p>Every row of an SQLite table has a unique integer key. If the |
| 762 |
table has a column labeled INTEGER PRIMARY KEY, then that column |
| 763 |
serves as the key. If there is no INTEGER PRIMARY KEY column then |
| 764 |
the key is a unique integer. The key for a row can be accessed in |
| 765 |
a SELECT statement or used in a WHERE or ORDER BY clause using any |
| 766 |
of the names "ROWID", "OID", or "_ROWID_".</p> |
| 767 |
|
| 768 |
<p>When you do an insert into a table that does not have an INTEGER PRIMARY |
| 769 |
KEY column, or if the table does have an INTEGER PRIMARY KEY but the value |
| 770 |
for that column is not specified in the VALUES clause of the insert, then |
| 771 |
the key is automatically generated. You can find the value of the key |
| 772 |
for the most recent INSERT statement using the |
| 773 |
<b>sqlite_last_insert_rowid</b> API function.</p> |
| 774 |
|
| 775 |
<h4>3.2 The number of rows that changed</h4> |
| 776 |
|
| 777 |
<p>The <b>sqlite_changes</b> API function returns the number of rows |
| 778 |
that have been inserted, deleted, or modified since the database was |
| 779 |
last quiescent. A "quiescent" database is one in which there are |
| 780 |
no outstanding calls to <b>sqlite_exec</b> and no VMs created by |
| 781 |
<b>sqlite_compile</b> that have not been finalized by <b>sqlite_finalize</b>. |
| 782 |
In common usage, <b>sqlite_changes</b> returns the number |
| 783 |
of rows inserted, deleted, or modified by the most recent <b>sqlite_exec</b> |
| 784 |
call or since the most recent <b>sqlite_compile</b>. But if you have |
| 785 |
nested calls to <b>sqlite_exec</b> (that is, if the callback routine |
| 786 |
of one <b>sqlite_exec</b> invokes another <b>sqlite_exec</b>) or if |
| 787 |
you invoke <b>sqlite_compile</b> to create a new VM while there is |
| 788 |
still another VM in existence, then |
| 789 |
the meaning of the number returned by <b>sqlite_changes</b> is more |
| 790 |
complex. |
| 791 |
The number reported includes any changes |
| 792 |
that were later undone by a ROLLBACK or ABORT. But rows that are |
| 793 |
deleted because of a DROP TABLE are <em>not</em> counted.</p> |
| 794 |
|
| 795 |
<p>SQLite implements the command "<b>DELETE FROM table</b>" (without |
| 796 |
a WHERE clause) by dropping the table then recreating it. |
| 797 |
This is much faster than deleting the elements of the table individually. |
| 798 |
But it also means that the value returned from <b>sqlite_changes</b> |
| 799 |
will be zero regardless of the number of elements that were originally |
| 800 |
in the table. If an accurate count of the number of elements deleted |
| 801 |
is necessary, use "<b>DELETE FROM table WHERE 1</b>" instead.</p> |
| 802 |
|
| 803 |
<h4>3.3 Querying into memory obtained from malloc()</h4> |
| 804 |
|
| 805 |
<p>The <b>sqlite_get_table</b> function is a wrapper around |
| 806 |
<b>sqlite_exec</b> that collects all the information from successive |
| 807 |
callbacks and writes it into memory obtained from malloc(). This |
| 808 |
is a convenience function that allows the application to get the |
| 809 |
entire result of a database query with a single function call.</p> |
| 810 |
|
| 811 |
<p>The main result from <b>sqlite_get_table</b> is an array of pointers |
| 812 |
to strings. There is one element in this array for each column of |
| 813 |
each row in the result. NULL results are represented by a NULL |
| 814 |
pointer. In addition to the regular data, there is an added row at the |
| 815 |
beginning of the array that contains the name of each column of the |
| 816 |
result.</p> |
| 817 |
|
| 818 |
<p>As an example, consider the following query:</p> |
| 819 |
|
| 820 |
<blockquote> |
| 821 |
SELECT employee_name, login, host FROM users WHERE login LIKE 'd%'; |
| 822 |
</blockquote> |
| 823 |
|
| 824 |
<p>This query will return the name, login and host computer name |
| 825 |
for every employee whose login begins with the letter "d". If this |
| 826 |
query is submitted to <b>sqlite_get_table</b> the result might |
| 827 |
look like this:</p> |
| 828 |
|
| 829 |
<blockquote> |
| 830 |
nrow = 2<br> |
| 831 |
ncolumn = 3<br> |
| 832 |
result[0] = "employee_name"<br> |
| 833 |
result[1] = "login"<br> |
| 834 |
result[2] = "host"<br> |
| 835 |
result[3] = "dummy"<br> |
| 836 |
result[4] = "No such user"<br> |
| 837 |
result[5] = 0<br> |
| 838 |
result[6] = "D. Richard Hipp"<br> |
| 839 |
result[7] = "drh"<br> |
| 840 |
result[8] = "zadok" |
| 841 |
</blockquote> |
| 842 |
|
| 843 |
<p>Notice that the "host" value for the "dummy" record is NULL so |
| 844 |
the result[] array contains a NULL pointer at that slot.</p> |
| 845 |
|
| 846 |
<p>If the result set of a query is empty, then by default |
| 847 |
<b>sqlite_get_table</b> will set nrow to 0 and leave its |
| 848 |
result parameter is set to NULL. But if the EMPTY_RESULT_CALLBACKS |
| 849 |
pragma is ON then the result parameter is initialized to the names |
| 850 |
of the columns only. For example, consider this query which has |
| 851 |
an empty result set:</p> |
| 852 |
|
| 853 |
<blockquote> |
| 854 |
SELECT employee_name, login, host FROM users WHERE employee_name IS NULL; |
| 855 |
</blockquote> |
| 856 |
|
| 857 |
<p> |
| 858 |
The default behavior gives this results: |
| 859 |
</p> |
| 860 |
|
| 861 |
<blockquote> |
| 862 |
nrow = 0<br> |
| 863 |
ncolumn = 0<br> |
| 864 |
result = 0<br> |
| 865 |
</blockquote> |
| 866 |
|
| 867 |
<p> |
| 868 |
But if the EMPTY_RESULT_CALLBACKS pragma is ON, then the following |
| 869 |
is returned: |
| 870 |
</p> |
| 871 |
|
| 872 |
<blockquote> |
| 873 |
nrow = 0<br> |
| 874 |
ncolumn = 3<br> |
| 875 |
result[0] = "employee_name"<br> |
| 876 |
result[1] = "login"<br> |
| 877 |
result[2] = "host"<br> |
| 878 |
</blockquote> |
| 879 |
|
| 880 |
<p>Memory to hold the information returned by <b>sqlite_get_table</b> |
| 881 |
is obtained from malloc(). But the calling function should not try |
| 882 |
to free this information directly. Instead, pass the complete table |
| 883 |
to <b>sqlite_free_table</b> when the table is no longer needed. |
| 884 |
It is safe to call <b>sqlite_free_table</b> with a NULL pointer such |
| 885 |
as would be returned if the result set is empty.</p> |
| 886 |
|
| 887 |
<p>The <b>sqlite_get_table</b> routine returns the same integer |
| 888 |
result code as <b>sqlite_exec</b>.</p> |
| 889 |
|
| 890 |
<h4>3.4 Interrupting an SQLite operation</h4> |
| 891 |
|
| 892 |
<p>The <b>sqlite_interrupt</b> function can be called from a |
| 893 |
different thread or from a signal handler to cause the current database |
| 894 |
operation to exit at its first opportunity. When this happens, |
| 895 |
the <b>sqlite_exec</b> routine (or the equivalent) that started |
| 896 |
the database operation will return SQLITE_INTERRUPT.</p> |
| 897 |
|
| 898 |
<h4>3.5 Testing for a complete SQL statement</h4> |
| 899 |
|
| 900 |
<p>The next interface routine to SQLite is a convenience function used |
| 901 |
to test whether or not a string forms a complete SQL statement. |
| 902 |
If the <b>sqlite_complete</b> function returns true when its input |
| 903 |
is a string, then the argument forms a complete SQL statement. |
| 904 |
There are no guarantees that the syntax of that statement is correct, |
| 905 |
but we at least know the statement is complete. If <b>sqlite_complete</b> |
| 906 |
returns false, then more text is required to complete the SQL statement.</p> |
| 907 |
|
| 908 |
<p>For the purpose of the <b>sqlite_complete</b> function, an SQL |
| 909 |
statement is complete if it ends in a semicolon.</p> |
| 910 |
|
| 911 |
<p>The <b>sqlite</b> command-line utility uses the <b>sqlite_complete</b> |
| 912 |
function to know when it needs to call <b>sqlite_exec</b>. After each |
| 913 |
line of input is received, <b>sqlite</b> calls <b>sqlite_complete</b> |
| 914 |
on all input in its buffer. If <b>sqlite_complete</b> returns true, |
| 915 |
then <b>sqlite_exec</b> is called and the input buffer is reset. If |
| 916 |
<b>sqlite_complete</b> returns false, then the prompt is changed to |
| 917 |
the continuation prompt and another line of text is read and added to |
| 918 |
the input buffer.</p> |
| 919 |
|
| 920 |
<h4>3.6 Library version string</h4> |
| 921 |
|
| 922 |
<p>The SQLite library exports the string constant named |
| 923 |
<b>sqlite_version</b> which contains the version number of the |
| 924 |
library. The header file contains a macro SQLITE_VERSION |
| 925 |
with the same information. If desired, a program can compare |
| 926 |
the SQLITE_VERSION macro against the <b>sqlite_version</b> |
| 927 |
string constant to verify that the version number of the |
| 928 |
header file and the library match.</p> |
| 929 |
|
| 930 |
<h4>3.7 Library character encoding</h4> |
| 931 |
|
| 932 |
<p>By default, SQLite assumes that all data uses a fixed-size |
| 933 |
8-bit character (iso8859). But if you give the --enable-utf8 option |
| 934 |
to the configure script, then the library assumes UTF-8 variable |
| 935 |
sized characters. This makes a difference for the LIKE and GLOB |
| 936 |
operators and the LENGTH() and SUBSTR() functions. The static |
| 937 |
string <b>sqlite_encoding</b> will be set to either "UTF-8" or |
| 938 |
"iso8859" to indicate how the library was compiled. In addition, |
| 939 |
the <b>sqlite.h</b> header file will define one of the |
| 940 |
macros <b>SQLITE_UTF8</b> or <b>SQLITE_ISO8859</b>, as appropriate.</p> |
| 941 |
|
| 942 |
<p>Note that the character encoding mechanism used by SQLite cannot |
| 943 |
be changed at run-time. This is a compile-time option only. The |
| 944 |
<b>sqlite_encoding</b> character string just tells you how the library |
| 945 |
was compiled.</p> |
| 946 |
|
| 947 |
<h4>3.8 Changing the library's response to locked files</h4> |
| 948 |
|
| 949 |
<p>The <b>sqlite_busy_handler</b> procedure can be used to register |
| 950 |
a busy callback with an open SQLite database. The busy callback will |
| 951 |
be invoked whenever SQLite tries to access a database that is locked. |
| 952 |
The callback will typically do some other useful work, or perhaps sleep, |
| 953 |
in order to give the lock a chance to clear. If the callback returns |
| 954 |
non-zero, then SQLite tries again to access the database and the cycle |
| 955 |
repeats. If the callback returns zero, then SQLite aborts the current |
| 956 |
operation and returns SQLITE_BUSY.</p> |
| 957 |
|
| 958 |
<p>The arguments to <b>sqlite_busy_handler</b> are the opaque |
| 959 |
structure returned from <b>sqlite_open</b>, a pointer to the busy |
| 960 |
callback function, and a generic pointer that will be passed as |
| 961 |
the first argument to the busy callback. When SQLite invokes the |
| 962 |
busy callback, it sends it three arguments: the generic pointer |
| 963 |
that was passed in as the third argument to <b>sqlite_busy_handler</b>, |
| 964 |
the name of the database table or index that the library is trying |
| 965 |
to access, and the number of times that the library has attempted to |
| 966 |
access the database table or index.</p> |
| 967 |
|
| 968 |
<p>For the common case where we want the busy callback to sleep, |
| 969 |
the SQLite library provides a convenience routine <b>sqlite_busy_timeout</b>. |
| 970 |
The first argument to <b>sqlite_busy_timeout</b> is a pointer to |
| 971 |
an open SQLite database and the second argument is a number of milliseconds. |
| 972 |
After <b>sqlite_busy_timeout</b> has been executed, the SQLite library |
| 973 |
will wait for the lock to clear for at least the number of milliseconds |
| 974 |
specified before it returns SQLITE_BUSY. Specifying zero milliseconds for |
| 975 |
the timeout restores the default behavior.</p> |
| 976 |
|
| 977 |
<h4>3.9 Using the <tt>_printf()</tt> wrapper functions</h4> |
| 978 |
|
| 979 |
<p>The four utility functions</p> |
| 980 |
|
| 981 |
<p> |
| 982 |
<ul> |
| 983 |
<li><b>sqlite_exec_printf()</b></li> |
| 984 |
<li><b>sqlite_exec_vprintf()</b></li> |
| 985 |
<li><b>sqlite_get_table_printf()</b></li> |
| 986 |
<li><b>sqlite_get_table_vprintf()</b></li> |
| 987 |
</ul> |
| 988 |
</p> |
| 989 |
|
| 990 |
<p>implement the same query functionality as <b>sqlite_exec</b> |
| 991 |
and <b>sqlite_get_table</b>. But instead of taking a complete |
| 992 |
SQL statement as their second argument, the four <b>_printf</b> |
| 993 |
routines take a printf-style format string. The SQL statement to |
| 994 |
be executed is generated from this format string and from whatever |
| 995 |
additional arguments are attached to the end of the function call.</p> |
| 996 |
|
| 997 |
<p>There are two advantages to using the SQLite printf |
| 998 |
functions instead of <b>sprintf</b>. First of all, with the |
| 999 |
SQLite printf routines, there is never a danger of overflowing a |
| 1000 |
static buffer as there is with <b>sprintf</b>. The SQLite |
| 1001 |
printf routines automatically allocate (and later free) |
| 1002 |
as much memory as is |
| 1003 |
necessary to hold the SQL statements generated.</p> |
| 1004 |
|
| 1005 |
<p>The second advantage the SQLite printf routines have over |
| 1006 |
<b>sprintf</b> are two new formatting options specifically designed |
| 1007 |
to support string literals in SQL. Within the format string, |
| 1008 |
the %q formatting option works very much like %s in that it |
| 1009 |
reads a null-terminated string from the argument list and inserts |
| 1010 |
it into the result. But %q translates the inserted string by |
| 1011 |
making two copies of every single-quote (') character in the |
| 1012 |
substituted string. This has the effect of escaping the end-of-string |
| 1013 |
meaning of single-quote within a string literal. The %Q formatting |
| 1014 |
option works similar; it translates the single-quotes like %q and |
| 1015 |
additionally encloses the resulting string in single-quotes. |
| 1016 |
If the argument for the %Q formatting options is a NULL pointer, |
| 1017 |
the resulting string is NULL without single quotes. |
| 1018 |
</p> |
| 1019 |
|
| 1020 |
<p>Consider an example. Suppose you are trying to insert a string |
| 1021 |
value into a database table where the string value was obtained from |
| 1022 |
user input. Suppose the string to be inserted is stored in a variable |
| 1023 |
named zString. The code to do the insertion might look like this:</p> |
| 1024 |
|
| 1025 |
<blockquote><pre> |
| 1026 |
sqlite_exec_printf(db, |
| 1027 |
"INSERT INTO table1 VALUES('%s')", |
| 1028 |
0, 0, 0, zString); |
| 1029 |
</pre></blockquote> |
| 1030 |
|
| 1031 |
<p>If the zString variable holds text like "Hello", then this statement |
| 1032 |
will work just fine. But suppose the user enters a string like |
| 1033 |
"Hi y'all!". The SQL statement generated reads as follows: |
| 1034 |
|
| 1035 |
<blockquote><pre> |
| 1036 |
INSERT INTO table1 VALUES('Hi y'all') |
| 1037 |
</pre></blockquote> |
| 1038 |
|
| 1039 |
<p>This is not valid SQL because of the apostrophe in the word "y'all". |
| 1040 |
But if the %q formatting option is used instead of %s, like this:</p> |
| 1041 |
|
| 1042 |
<blockquote><pre> |
| 1043 |
sqlite_exec_printf(db, |
| 1044 |
"INSERT INTO table1 VALUES('%q')", |
| 1045 |
0, 0, 0, zString); |
| 1046 |
</pre></blockquote> |
| 1047 |
|
| 1048 |
<p>Then the generated SQL will look like the following:</p> |
| 1049 |
|
| 1050 |
<blockquote><pre> |
| 1051 |
INSERT INTO table1 VALUES('Hi y''all') |
| 1052 |
</pre></blockquote> |
| 1053 |
|
| 1054 |
<p>Here the apostrophe has been escaped and the SQL statement is well-formed. |
| 1055 |
When generating SQL on-the-fly from data that might contain a |
| 1056 |
single-quote character ('), it is always a good idea to use the |
| 1057 |
SQLite printf routines and the %q formatting option instead of <b>sprintf</b>. |
| 1058 |
</p> |
| 1059 |
|
| 1060 |
<p>If the %Q formatting option is used instead of %q, like this:</p> |
| 1061 |
|
| 1062 |
<blockquote><pre> |
| 1063 |
sqlite_exec_printf(db, |
| 1064 |
"INSERT INTO table1 VALUES(%Q)", |
| 1065 |
0, 0, 0, zString); |
| 1066 |
</pre></blockquote> |
| 1067 |
|
| 1068 |
<p>Then the generated SQL will look like the following:</p> |
| 1069 |
|
| 1070 |
<blockquote><pre> |
| 1071 |
INSERT INTO table1 VALUES('Hi y''all') |
| 1072 |
</pre></blockquote> |
| 1073 |
|
| 1074 |
<p>If the value of the zString variable is NULL, the generated SQL |
| 1075 |
will look like the following:</p> |
| 1076 |
|
| 1077 |
<blockquote><pre> |
| 1078 |
INSERT INTO table1 VALUES(NULL) |
| 1079 |
</pre></blockquote> |
| 1080 |
|
| 1081 |
<p>All of the _printf() routines above are built around the following |
| 1082 |
two functions:</p> |
| 1083 |
|
| 1084 |
<blockquote><pre> |
| 1085 |
char *sqlite_mprintf(const char *zFormat, ...); |
| 1086 |
char *sqlite_vmprintf(const char *zFormat, va_list); |
| 1087 |
</pre></blockquote> |
| 1088 |
|
| 1089 |
<p>The <b>sqlite_mprintf()</b> routine works like the standard library |
| 1090 |
<b>sprintf()</b> except that it writes its results into memory obtained |
| 1091 |
from malloc() and returns a pointer to the malloced buffer. |
| 1092 |
<b>sqlite_mprintf()</b> also understands the %q and %Q extensions described |
| 1093 |
above. The <b>sqlite_vmprintf()</b> is a varargs version of the same |
| 1094 |
routine. The string pointer that these routines return should be freed |
| 1095 |
by passing it to <b>sqlite_freemem()</b>. |
| 1096 |
</p> |
| 1097 |
|
| 1098 |
<h4>3.10 Performing background jobs during large queries</h3> |
| 1099 |
|
| 1100 |
<p>The <b>sqlite_progress_handler()</b> routine can be used to register a |
| 1101 |
callback routine with an SQLite database to be invoked periodically during long |
| 1102 |
running calls to <b>sqlite_exec()</b>, <b>sqlite_step()</b> and the various |
| 1103 |
wrapper functions. |
| 1104 |
</p> |
| 1105 |
|
| 1106 |
<p>The callback is invoked every N virtual machine operations, where N is |
| 1107 |
supplied as the second argument to <b>sqlite_progress_handler()</b>. The third |
| 1108 |
and fourth arguments to <b>sqlite_progress_handler()</b> are a pointer to the |
| 1109 |
routine to be invoked and a void pointer to be passed as the first argument to |
| 1110 |
it. |
| 1111 |
</p> |
| 1112 |
|
| 1113 |
<p>The time taken to execute each virtual machine operation can vary based on |
| 1114 |
many factors. A typical value for a 1 GHz PC is between half and three million |
| 1115 |
per second but may be much higher or lower, depending on the query. As such it |
| 1116 |
is difficult to schedule background operations based on virtual machine |
| 1117 |
operations. Instead, it is recommended that a callback be scheduled relatively |
| 1118 |
frequently (say every 1000 instructions) and external timer routines used to |
| 1119 |
determine whether or not background jobs need to be run. |
| 1120 |
</p> |
| 1121 |
|
| 1122 |
<a name="cfunc"></a> |
| 1123 |
<h3>4.0 Adding New SQL Functions</h3> |
| 1124 |
|
| 1125 |
<p>Beginning with version 2.4.0, SQLite allows the SQL language to be |
| 1126 |
extended with new functions implemented as C code. The following interface |
| 1127 |
is used: |
| 1128 |
</p> |
| 1129 |
|
| 1130 |
<blockquote><pre> |
| 1131 |
typedef struct sqlite_func sqlite_func; |
| 1132 |
|
| 1133 |
int sqlite_create_function( |
| 1134 |
sqlite *db, |
| 1135 |
const char *zName, |
| 1136 |
int nArg, |
| 1137 |
void (*xFunc)(sqlite_func*,int,const char**), |
| 1138 |
void *pUserData |
| 1139 |
); |
| 1140 |
int sqlite_create_aggregate( |
| 1141 |
sqlite *db, |
| 1142 |
const char *zName, |
| 1143 |
int nArg, |
| 1144 |
void (*xStep)(sqlite_func*,int,const char**), |
| 1145 |
void (*xFinalize)(sqlite_func*), |
| 1146 |
void *pUserData |
| 1147 |
); |
| 1148 |
|
| 1149 |
char *sqlite_set_result_string(sqlite_func*,const char*,int); |
| 1150 |
void sqlite_set_result_int(sqlite_func*,int); |
| 1151 |
void sqlite_set_result_double(sqlite_func*,double); |
| 1152 |
void sqlite_set_result_error(sqlite_func*,const char*,int); |
| 1153 |
|
| 1154 |
void *sqlite_user_data(sqlite_func*); |
| 1155 |
void *sqlite_aggregate_context(sqlite_func*, int nBytes); |
| 1156 |
int sqlite_aggregate_count(sqlite_func*); |
| 1157 |
</pre></blockquote> |
| 1158 |
|
| 1159 |
<p> |
| 1160 |
The <b>sqlite_create_function()</b> interface is used to create |
| 1161 |
regular functions and <b>sqlite_create_aggregate()</b> is used to |
| 1162 |
create new aggregate functions. In both cases, the <b>db</b> |
| 1163 |
parameter is an open SQLite database on which the functions should |
| 1164 |
be registered, <b>zName</b> is the name of the new function, |
| 1165 |
<b>nArg</b> is the number of arguments, and <b>pUserData</b> is |
| 1166 |
a pointer which is passed through unchanged to the C implementation |
| 1167 |
of the function. Both routines return 0 on success and non-zero |
| 1168 |
if there are any errors. |
| 1169 |
</p> |
| 1170 |
|
| 1171 |
<p> |
| 1172 |
The length of a function name may not exceed 255 characters. |
| 1173 |
Any attempt to create a function whose name exceeds 255 characters |
| 1174 |
in length will result in an error. |
| 1175 |
</p> |
| 1176 |
|
| 1177 |
<p> |
| 1178 |
For regular functions, the <b>xFunc</b> callback is invoked once |
| 1179 |
for each function call. The implementation of xFunc should call |
| 1180 |
one of the <b>sqlite_set_result_...</b> interfaces to return its |
| 1181 |
result. The <b>sqlite_user_data()</b> routine can be used to |
| 1182 |
retrieve the <b>pUserData</b> pointer that was passed in when the |
| 1183 |
function was registered. |
| 1184 |
</p> |
| 1185 |
|
| 1186 |
<p> |
| 1187 |
For aggregate functions, the <b>xStep</b> callback is invoked once |
| 1188 |
for each row in the result and then <b>xFinalize</b> is invoked at the |
| 1189 |
end to compute a final answer. The xStep routine can use the |
| 1190 |
<b>sqlite_aggregate_context()</b> interface to allocate memory that |
| 1191 |
will be unique to that particular instance of the SQL function. |
| 1192 |
This memory will be automatically deleted after xFinalize is called. |
| 1193 |
The <b>sqlite_aggregate_count()</b> routine can be used to find out |
| 1194 |
how many rows of data were passed to the aggregate. The xFinalize |
| 1195 |
callback should invoke one of the <b>sqlite_set_result_...</b> |
| 1196 |
interfaces to set the final result of the aggregate. |
| 1197 |
</p> |
| 1198 |
|
| 1199 |
<p> |
| 1200 |
SQLite now implements all of its built-in functions using this |
| 1201 |
interface. For additional information and examples on how to create |
| 1202 |
new SQL functions, review the SQLite source code in the file |
| 1203 |
<b>func.c</b>. |
| 1204 |
</p> |
| 1205 |
|
| 1206 |
<h3>5.0 Multi-Threading And SQLite</h3> |
| 1207 |
|
| 1208 |
<p> |
| 1209 |
If SQLite is compiled with the THREADSAFE preprocessor macro set to 1, |
| 1210 |
then it is safe to use SQLite from two or more threads of the same process |
| 1211 |
at the same time. But each thread should have its own <b>sqlite*</b> |
| 1212 |
pointer returned from <b>sqlite_open</b>. It is never safe for two |
| 1213 |
or more threads to access the same <b>sqlite*</b> pointer at the same time. |
| 1214 |
</p> |
| 1215 |
|
| 1216 |
<p> |
| 1217 |
In precompiled SQLite libraries available on the website, the Unix |
| 1218 |
versions are compiled with THREADSAFE turned off but the Windows |
| 1219 |
versions are compiled with THREADSAFE turned on. If you need something |
| 1220 |
different than this you will have to recompile. |
| 1221 |
</p> |
| 1222 |
|
| 1223 |
<p> |
| 1224 |
Under Unix, an <b>sqlite*</b> pointer should not be carried across a |
| 1225 |
<b>fork()</b> system call into the child process. The child process |
| 1226 |
should open its own copy of the database after the <b>fork()</b>. |
| 1227 |
</p> |
| 1228 |
|
| 1229 |
<h3>6.0 Usage Examples</h3> |
| 1230 |
|
| 1231 |
<p>For examples of how the SQLite C/C++ interface can be used, |
| 1232 |
refer to the source code for the <b>sqlite</b> program in the |
| 1233 |
file <a href="https://sqlite.org/src/file/src/shell.c.in">src/shell.c</a> |
| 1234 |
of the source tree. |
| 1235 |
Additional information about sqlite is available at |
| 1236 |
<a href="cli.html">cli.html</a>. |
| 1237 |
See also the sources to the Tcl interface for SQLite in |
| 1238 |
the source file |
| 1239 |
<a href="https://sqlite.org/src/file/src/tclsqlite.c">src/tclsqlite.c</a>.</p> |
| 1240 |
<p align="center"><small><i>This page was last updated on 2025-07-12 15:11:36Z </small></i></p> |
| 1241 |
|