1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>The C language interface to SQLite Version 2</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
122
<h2>The C language interface to SQLite Version 2</h2>
124
<p>The SQLite library is designed to be very easy to use from
125
a C or C++ program. This document gives an overview of the C/C++
126
programming interface.</p>
128
<h3>1.0 The Core API</h3>
130
<p>The interface to the SQLite library consists of three core functions,
131
one opaque data structure, and some constants used as return values.
132
The core interface is as follows:</p>
135
typedef struct sqlite sqlite;
136
#define SQLITE_OK 0 /* Successful result */
138
sqlite *sqlite_open(const char *dbname, int mode, char **errmsg);
140
void sqlite_close(sqlite *db);
145
int (*xCallback)(void*,int,char**,char**),
152
The above is all you really need to know in order to use SQLite
153
in your C or C++ programs. There are other interface functions
154
available (and described below) but we will begin by describing
155
the core functions shown above.
158
<a name="sqlite_open"></a>
159
<h4>1.1 Opening a database</h4>
161
<p>Use the <b>sqlite_open</b> function to open an existing SQLite
162
database or to create a new SQLite database. The first argument
163
is the database name. The second argument is intended to signal
164
whether the database is going to be used for reading and writing
165
or just for reading. But in the current implementation, the
166
second argument to <b>sqlite_open</b> is ignored.
167
The third argument is a pointer to a string pointer.
168
If the third argument is not NULL and an error occurs
169
while trying to open the database, then an error message will be
170
written to memory obtained from malloc() and *errmsg will be made
171
to point to this error message. The calling function is responsible
172
for freeing the memory when it has finished with it.</p>
174
<p>The name of an SQLite database is the name of a file that will
175
contain the database. If the file does not exist, SQLite attempts
176
to create and initialize it. If the file is read-only (due to
177
permission bits or because it is located on read-only media like
178
a CD-ROM) then SQLite opens the database for reading only. The
179
entire SQL database is stored in a single file on the disk. But
180
additional temporary files may be created during the execution of
181
an SQL command in order to store the database rollback journal or
182
temporary and intermediate results of a query.</p>
184
<p>The return value of the <b>sqlite_open</b> function is a
185
pointer to an opaque <b>sqlite</b> structure. This pointer will
186
be the first argument to all subsequent SQLite function calls that
187
deal with the same database. NULL is returned if the open fails
190
<a name="sqlite_close"></a>
191
<h4>1.2 Closing the database</h4>
193
<p>To close an SQLite database, call the <b>sqlite_close</b>
194
function passing it the sqlite structure pointer that was obtained
195
from a prior call to <b>sqlite_open</b>.
196
If a transaction is active when the database is closed, the transaction
199
<a name="sqlite_exec"></a>
200
<h4>1.3 Executing SQL statements</h4>
202
<p>The <b>sqlite_exec</b> function is used to process SQL statements
203
and queries. This function requires 5 parameters as follows:</p>
206
<li><p>A pointer to the sqlite structure obtained from a prior call
207
to <b>sqlite_open</b>.</p></li>
208
<li><p>A null-terminated string containing the text of one or more
209
SQL statements and/or queries to be processed.</p></li>
210
<li><p>A pointer to a callback function which is invoked once for each
211
row in the result of a query. This argument may be NULL, in which
212
case no callbacks will ever be invoked.</p></li>
213
<li><p>A pointer that is forwarded to become the first argument
214
to the callback function.</p></li>
215
<li><p>A pointer to an error string. Error messages are written to space
216
obtained from malloc() and the error string is made to point to
217
the malloced space. The calling function is responsible for freeing
218
this space when it has finished with it.
219
This argument may be NULL, in which case error messages are not
220
reported back to the calling function.</p></li>
224
The callback function is used to receive the results of a query. A
225
prototype for the callback function is as follows:</p>
228
int Callback(void *pArg, int argc, char **argv, char **columnNames){
233
<a name="callback_row_data"></a>
234
<p>The first argument to the callback is just a copy of the fourth argument
235
to <b>sqlite_exec</b> This parameter can be used to pass arbitrary
236
information through to the callback function from client code.
237
The second argument is the number of columns in the query result.
238
The third argument is an array of pointers to strings where each string
239
is a single column of the result for that record. Note that the
240
callback function reports a NULL value in the database as a NULL pointer,
241
which is very different from an empty string. If the i-th parameter
242
is an empty string, we will get:</p>
244
argv[i][0] == 0
246
<p>But if the i-th parameter is NULL we will get:</p>
251
<p>The names of the columns are contained in first <i>argc</i>
252
entries of the fourth argument.
253
If the <a href="pragma.html#pragma_show_datatypes">SHOW_DATATYPES</a> pragma
254
is on (it is off by default) then
255
the second <i>argc</i> entries in the 4th argument are the datatypes
256
for the corresponding columns.
259
<p>If the <a href="pragma.html#pragma_empty_result_callbacks">
260
EMPTY_RESULT_CALLBACKS</a> pragma is set to ON and the result of
261
a query is an empty set, then the callback is invoked once with the
262
third parameter (argv) set to 0. In other words
266
The second parameter (argc)
267
and the fourth parameter (columnNames) are still valid
268
and can be used to determine the number and names of the result
269
columns if there had been a result.
270
The default behavior is not to invoke the callback at all if the
271
result set is empty.</p>
273
<a name="callback_returns_nonzero"></a>
274
<p>The callback function should normally return 0. If the callback
275
function returns non-zero, the query is immediately aborted and
276
<b>sqlite_exec</b> will return SQLITE_ABORT.</p>
278
<h4>1.4 Error Codes</h4>
281
The <b>sqlite_exec</b> function normally returns SQLITE_OK. But
282
if something goes wrong it can return a different value to indicate
283
the type of error. Here is a complete list of the return codes:
287
#define SQLITE_OK 0 /* Successful result */
288
#define SQLITE_ERROR 1 /* SQL error or missing database */
289
#define SQLITE_INTERNAL 2 /* An internal logic error in SQLite */
290
#define SQLITE_PERM 3 /* Access permission denied */
291
#define SQLITE_ABORT 4 /* Callback routine requested an abort */
292
#define SQLITE_BUSY 5 /* The database file is locked */
293
#define SQLITE_LOCKED 6 /* A table in the database is locked */
294
#define SQLITE_NOMEM 7 /* A malloc() failed */
295
#define SQLITE_READONLY 8 /* Attempt to write a readonly database */
296
#define SQLITE_INTERRUPT 9 /* Operation terminated by sqlite_interrupt() */
297
#define SQLITE_IOERR 10 /* Some kind of disk I/O error occurred */
298
#define SQLITE_CORRUPT 11 /* The database disk image is malformed */
299
#define SQLITE_NOTFOUND 12 /* (Internal Only) Table or record not found */
300
#define SQLITE_FULL 13 /* Insertion failed because database is full */
301
#define SQLITE_CANTOPEN 14 /* Unable to open the database file */
302
#define SQLITE_PROTOCOL 15 /* Database lock protocol error */
303
#define SQLITE_EMPTY 16 /* (Internal Only) Database table is empty */
304
#define SQLITE_SCHEMA 17 /* The database schema changed */
305
#define SQLITE_TOOBIG 18 /* Too much data for one row of a table */
306
#define SQLITE_CONSTRAINT 19 /* Abort due to constraint violation */
307
#define SQLITE_MISMATCH 20 /* Data type mismatch */
308
#define SQLITE_MISUSE 21 /* Library used incorrectly */
309
#define SQLITE_NOLFS 22 /* Uses OS features not supported on host */
310
#define SQLITE_AUTH 23 /* Authorization denied */
311
#define SQLITE_ROW 100 /* sqlite_step() has another row ready */
312
#define SQLITE_DONE 101 /* sqlite_step() has finished executing */
316
The meanings of these various return values are as follows:
322
<dd><p>This value is returned if everything worked and there were no errors.
324
<dt>SQLITE_INTERNAL</dt>
325
<dd><p>This value indicates that an internal consistency check within
326
the SQLite library failed. This can only happen if there is a bug in
327
the SQLite library. If you ever get an SQLITE_INTERNAL reply from
328
an <b>sqlite_exec</b> call, please report the problem on the SQLite
331
<dt>SQLITE_ERROR</dt>
332
<dd><p>This return value indicates that there was an error in the SQL
333
that was passed into the <b>sqlite_exec</b>.
336
<dd><p>This return value says that the access permissions on the database
337
file are such that the file cannot be opened.
339
<dt>SQLITE_ABORT</dt>
340
<dd><p>This value is returned if the callback function returns non-zero.
343
<dd><p>This return code indicates that another program or thread has
344
the database locked. SQLite allows two or more threads to read the
345
database at the same time, but only one thread can have the database
346
open for writing at the same time. Locking in SQLite is on the
349
<dt>SQLITE_LOCKED</dt>
350
<dd><p>This return code is similar to SQLITE_BUSY in that it indicates
351
that the database is locked. But the source of the lock is a recursive
352
call to <b>sqlite_exec</b>. This return can only occur if you attempt
353
to invoke sqlite_exec from within a callback routine of a query
354
from a prior invocation of sqlite_exec. Recursive calls to
355
sqlite_exec are allowed as long as they do
356
not attempt to write the same table.
358
<dt>SQLITE_NOMEM</dt>
359
<dd><p>This value is returned if a call to <b>malloc</b> fails.
361
<dt>SQLITE_READONLY</dt>
362
<dd><p>This return code indicates that an attempt was made to write to
363
a database file that is opened for reading only.
365
<dt>SQLITE_INTERRUPT</dt>
366
<dd><p>This value is returned if a call to <b>sqlite_interrupt</b>
367
interrupts a database operation in progress.
369
<dt>SQLITE_IOERR</dt>
370
<dd><p>This value is returned if the operating system informs SQLite
371
that it is unable to perform some disk I/O operation. This could mean
372
that there is no more space left on the disk.
374
<dt>SQLITE_CORRUPT</dt>
375
<dd><p>This value is returned if SQLite detects that the database it is
376
working on has become corrupted. Corruption might occur due to a rogue
377
process writing to the database file or it might happen due to a
378
previously undetected logic error in of SQLite. This value is also
379
returned if a disk I/O error occurs in such a way that SQLite is forced
380
to leave the database file in a corrupted state. The latter should only
381
happen due to a hardware or operating system malfunction.
384
<dd><p>This value is returned if an insertion failed because there is
385
no space left on the disk, or the database is too big to hold any
386
more information. The latter case should only occur for databases
387
that are larger than 2GB in size.
389
<dt>SQLITE_CANTOPEN</dt>
390
<dd><p>This value is returned if the database file could not be opened
393
<dt>SQLITE_PROTOCOL</dt>
394
<dd><p>This value is returned if some other process is messing with
395
file locks and has violated the file locking protocol that SQLite uses
396
on its rollback journal files.
398
<dt>SQLITE_SCHEMA</dt>
399
<dd><p>When the database first opened, SQLite reads the database schema
400
into memory and uses that schema to parse new SQL statements. If another
401
process changes the schema, the command currently being processed will
402
abort because the virtual machine code generated assumed the old
403
schema. This is the return code for such cases. Retrying the
404
command usually will clear the problem.
406
<dt>SQLITE_TOOBIG</dt>
407
<dd><p>SQLite will not store more than about 1 megabyte of data in a single
408
row of a single table. If you attempt to store more than 1 megabyte
409
in a single row, this is the return code you get.
411
<dt>SQLITE_CONSTRAINT</dt>
412
<dd><p>This constant is returned if the SQL statement would have violated
413
a database constraint.
415
<dt>SQLITE_MISMATCH</dt>
416
<dd><p>This error occurs when there is an attempt to insert non-integer
417
data into a column labeled INTEGER PRIMARY KEY. For most columns, SQLite
418
ignores the data type and allows any kind of data to be stored. But
419
an INTEGER PRIMARY KEY column is only allowed to store integer data.
421
<dt>SQLITE_MISUSE</dt>
422
<dd><p>This error might occur if one or more of the SQLite API routines
423
is used incorrectly. Examples of incorrect usage include calling
424
<b>sqlite_exec</b> after the database has been closed using
425
<b>sqlite_close</b> or
426
calling <b>sqlite_exec</b> with the same
427
database pointer simultaneously from two separate threads.
429
<dt>SQLITE_NOLFS</dt>
430
<dd><p>This error means that you have attempts to create or access a file
431
database file that is larger that 2GB on a legacy Unix machine that
432
lacks large file support.
435
<dd><p>This error indicates that the authorizer callback
436
has disallowed the SQL you are attempting to execute.
439
<dd><p>This is one of the return codes from the
440
<b>sqlite_step</b> routine which is part of the non-callback API.
441
It indicates that another row of result data is available.
444
<dd><p>This is one of the return codes from the
445
<b>sqlite_step</b> routine which is part of the non-callback API.
446
It indicates that the SQL statement has been completely executed and
447
the <b>sqlite_finalize</b> routine is ready to be called.
452
<h3>2.0 Accessing Data Without Using A Callback Function</h3>
455
The <b>sqlite_exec</b> routine described above used to be the only
456
way to retrieve data from an SQLite database. But many programmers found
457
it inconvenient to use a callback function to obtain results. So beginning
458
with SQLite version 2.7.7, a second access interface is available that
459
does not use callbacks.
463
The new interface uses three separate functions to replace the single
464
<b>sqlite_exec</b> function.
468
typedef struct sqlite_vm sqlite_vm;
471
sqlite *db, /* The open database */
472
const char *zSql, /* SQL statement to be compiled */
473
const char **pzTail, /* OUT: uncompiled tail of zSql */
474
sqlite_vm **ppVm, /* OUT: the virtual machine to execute zSql */
475
char **pzErrmsg /* OUT: Error message. */
479
sqlite_vm *pVm, /* The virtual machine to execute */
480
int *pN, /* OUT: Number of columns in result */
481
const char ***pazValue, /* OUT: Column data */
482
const char ***pazColName /* OUT: Column names and datatypes */
486
sqlite_vm *pVm, /* The virtual machine to be finalized */
487
char **pzErrMsg /* OUT: Error message */
492
The strategy is to compile a single SQL statement using
493
<b>sqlite_compile</b> then invoke <b>sqlite_step</b> multiple times,
494
once for each row of output, and finally call <b>sqlite_finalize</b>
495
to clean up after the SQL has finished execution.
498
<h4>2.1 Compiling An SQL Statement Into A Virtual Machine</h4>
501
The <b>sqlite_compile</b> "compiles" a single SQL statement (specified
502
by the second parameter) and generates a virtual machine that is able
503
to execute that statement.
504
As with must interface routines, the first parameter must be a pointer
505
to an sqlite structure that was obtained from a prior call to
509
A pointer to the virtual machine is stored in a pointer which is passed
510
in as the 4th parameter.
511
Space to hold the virtual machine is dynamically allocated. To avoid
512
a memory leak, the calling function must invoke
513
<b>sqlite_finalize</b> on the virtual machine after it has finished
515
The 4th parameter may be set to NULL if an error is encountered during
520
If any errors are encountered during compilation, an error message is
521
written into memory obtained from <b>malloc</b> and the 5th parameter
522
is made to point to that memory. If the 5th parameter is NULL, then
523
no error message is generated. If the 5th parameter is not NULL, then
524
the calling function should dispose of the memory containing the error
525
message by calling <b>sqlite_freemem</b>.
529
If the 2nd parameter actually contains two or more statements of SQL,
530
only the first statement is compiled. (This is different from the
531
behavior of <b>sqlite_exec</b> which executes all SQL statements
532
in its input string.) The 3rd parameter to <b>sqlite_compile</b>
533
is made to point to the first character beyond the end of the first
534
statement of SQL in the input. If the 2nd parameter contains only
535
a single SQL statement, then the 3rd parameter will be made to point
536
to the '\000' terminator at the end of the 2nd parameter.
540
On success, <b>sqlite_compile</b> returns SQLITE_OK.
541
Otherwise and error code is returned.
544
<h4>2.2 Step-By-Step Execution Of An SQL Statement</h4>
547
After a virtual machine has been generated using <b>sqlite_compile</b>
548
it is executed by one or more calls to <b>sqlite_step</b>. Each
549
invocation of <b>sqlite_step</b>, except the last one,
550
returns a single row of the result.
551
The number of columns in the result is stored in the integer that
552
the 2nd parameter points to.
553
The pointer specified by the 3rd parameter is made to point
554
to an array of pointers to column values.
555
The pointer in the 4th parameter is made to point to an array
556
of pointers to column names and datatypes.
557
The 2nd through 4th parameters to <b>sqlite_step</b> convey the
558
same information as the 2nd through 4th parameters of the
559
<b>callback</b> routine when using
560
the <b>sqlite_exec</b> interface. Except, with <b>sqlite_step</b>
561
the column datatype information is always included in the in the
562
4th parameter regardless of whether or not the
563
<a href="pragma.html#pragma_show_datatypes">SHOW_DATATYPES</a> pragma
568
Each invocation of <b>sqlite_step</b> returns an integer code that
569
indicates what happened during that step. This code may be
570
SQLITE_BUSY, SQLITE_ROW, SQLITE_DONE, SQLITE_ERROR, or
575
If the virtual machine is unable to open the database file because
576
it is locked by another thread or process, <b>sqlite_step</b>
577
will return SQLITE_BUSY. The calling function should do some other
578
activity, or sleep, for a short amount of time to give the lock a
579
chance to clear, then invoke <b>sqlite_step</b> again. This can
580
be repeated as many times as desired.
584
Whenever another row of result data is available,
585
<b>sqlite_step</b> will return SQLITE_ROW. The row data is
586
stored in an array of pointers to strings and the 2nd parameter
587
is made to point to this array.
591
When all processing is complete, <b>sqlite_step</b> will return
592
either SQLITE_DONE or SQLITE_ERROR. SQLITE_DONE indicates that the
593
statement completed successfully and SQLITE_ERROR indicates that there
594
was a run-time error. (The details of the error are obtained from
595
<b>sqlite_finalize</b>.) It is a misuse of the library to attempt
596
to call <b>sqlite_step</b> again after it has returned SQLITE_DONE
601
When <b>sqlite_step</b> returns SQLITE_DONE or SQLITE_ERROR,
602
the *pN and *pazColName values are set to the number of columns
603
in the result set and to the names of the columns, just as they
604
are for an SQLITE_ROW return. This allows the calling code to
605
find the number of result columns and the column names and datatypes
606
even if the result set is empty. The *pazValue parameter is always
607
set to NULL when the return codes is SQLITE_DONE or SQLITE_ERROR.
608
If the SQL being executed is a statement that does not
609
return a result (such as an INSERT or an UPDATE) then *pN will
610
be set to zero and *pazColName will be set to NULL.
614
If you abuse the library by trying to call <b>sqlite_step</b>
615
inappropriately it will attempt return SQLITE_MISUSE.
616
This can happen if you call sqlite_step() on the same virtual machine
618
time from two or more threads or if you call sqlite_step()
619
again after it returned SQLITE_DONE or SQLITE_ERROR or if you
620
pass in an invalid virtual machine pointer to sqlite_step().
621
You should not depend on the SQLITE_MISUSE return code to indicate
622
an error. It is possible that a misuse of the interface will go
623
undetected and result in a program crash. The SQLITE_MISUSE is
624
intended as a debugging aid only - to help you detect incorrect
625
usage prior to a mishap. The misuse detection logic is not guaranteed
626
to work in every case.
629
<h4>2.3 Deleting A Virtual Machine</h4>
632
Every virtual machine that <b>sqlite_compile</b> creates should
633
eventually be handed to <b>sqlite_finalize</b>. The sqlite_finalize()
634
procedure deallocates the memory and other resources that the virtual
635
machine uses. Failure to call sqlite_finalize() will result in
636
resource leaks in your program.
640
The <b>sqlite_finalize</b> routine also returns the result code
641
that indicates success or failure of the SQL operation that the
642
virtual machine carried out.
643
The value returned by sqlite_finalize() will be the same as would
644
have been returned had the same SQL been executed by <b>sqlite_exec</b>.
645
The error message returned will also be the same.
649
It is acceptable to call <b>sqlite_finalize</b> on a virtual machine
650
before <b>sqlite_step</b> has returned SQLITE_DONE. Doing so has
651
the effect of interrupting the operation in progress. Partially completed
652
changes will be rolled back and the database will be restored to its
653
original state (unless an alternative recovery algorithm is selected using
654
an ON CONFLICT clause in the SQL being executed.) The effect is the
655
same as if a callback function of <b>sqlite_exec</b> had returned
660
It is also acceptable to call <b>sqlite_finalize</b> on a virtual machine
661
that has never been passed to <b>sqlite_step</b> even once.
664
<h3>3.0 The Extended API</h3>
666
<p>Only the three core routines described in section 1.0 are required to use
667
SQLite. But there are many other functions that provide
668
useful interfaces. These extended routines are as follows:
672
int sqlite_last_insert_rowid(sqlite*);
674
int sqlite_changes(sqlite*);
676
int sqlite_get_table(
685
void sqlite_free_table(char**);
687
void sqlite_interrupt(sqlite*);
689
int sqlite_complete(const char *sql);
691
void sqlite_busy_handler(sqlite*, int (*)(void*,const char*,int), void*);
693
void sqlite_busy_timeout(sqlite*, int ms);
695
const char sqlite_version[];
697
const char sqlite_encoding[];
699
int sqlite_exec_printf(
702
int (*)(void*,int,char**,char**),
708
int sqlite_exec_vprintf(
711
int (*)(void*,int,char**,char**),
717
int sqlite_get_table_printf(
727
int sqlite_get_table_vprintf(
737
char *sqlite_mprintf(const char *zFormat, ...);
739
char *sqlite_vmprintf(const char *zFormat, va_list);
741
void sqlite_freemem(char*);
743
void sqlite_progress_handler(sqlite*, int, int (*)(void*), void*);
747
<p>All of the above definitions are included in the "sqlite.h"
748
header file that comes in the source tree.</p>
750
<h4>3.1 The ROWID of the most recent insert</h4>
752
<p>Every row of an SQLite table has a unique integer key. If the
753
table has a column labeled INTEGER PRIMARY KEY, then that column
754
serves as the key. If there is no INTEGER PRIMARY KEY column then
755
the key is a unique integer. The key for a row can be accessed in
756
a SELECT statement or used in a WHERE or ORDER BY clause using any
757
of the names "ROWID", "OID", or "_ROWID_".</p>
759
<p>When you do an insert into a table that does not have an INTEGER PRIMARY
760
KEY column, or if the table does have an INTEGER PRIMARY KEY but the value
761
for that column is not specified in the VALUES clause of the insert, then
762
the key is automatically generated. You can find the value of the key
763
for the most recent INSERT statement using the
764
<b>sqlite_last_insert_rowid</b> API function.</p>
766
<h4>3.2 The number of rows that changed</h4>
768
<p>The <b>sqlite_changes</b> API function returns the number of rows
769
that have been inserted, deleted, or modified since the database was
770
last quiescent. A "quiescent" database is one in which there are
771
no outstanding calls to <b>sqlite_exec</b> and no VMs created by
772
<b>sqlite_compile</b> that have not been finalized by <b>sqlite_finalize</b>.
773
In common usage, <b>sqlite_changes</b> returns the number
774
of rows inserted, deleted, or modified by the most recent <b>sqlite_exec</b>
775
call or since the most recent <b>sqlite_compile</b>. But if you have
776
nested calls to <b>sqlite_exec</b> (that is, if the callback routine
777
of one <b>sqlite_exec</b> invokes another <b>sqlite_exec</b>) or if
778
you invoke <b>sqlite_compile</b> to create a new VM while there is
779
still another VM in existence, then
780
the meaning of the number returned by <b>sqlite_changes</b> is more
782
The number reported includes any changes
783
that were later undone by a ROLLBACK or ABORT. But rows that are
784
deleted because of a DROP TABLE are <em>not</em> counted.</p>
786
<p>SQLite implements the command "<b>DELETE FROM table</b>" (without
787
a WHERE clause) by dropping the table then recreating it.
788
This is much faster than deleting the elements of the table individually.
789
But it also means that the value returned from <b>sqlite_changes</b>
790
will be zero regardless of the number of elements that were originally
791
in the table. If an accurate count of the number of elements deleted
792
is necessary, use "<b>DELETE FROM table WHERE 1</b>" instead.</p>
794
<h4>3.3 Querying into memory obtained from malloc()</h4>
796
<p>The <b>sqlite_get_table</b> function is a wrapper around
797
<b>sqlite_exec</b> that collects all the information from successive
798
callbacks and writes it into memory obtained from malloc(). This
799
is a convenience function that allows the application to get the
800
entire result of a database query with a single function call.</p>
802
<p>The main result from <b>sqlite_get_table</b> is an array of pointers
803
to strings. There is one element in this array for each column of
804
each row in the result. NULL results are represented by a NULL
805
pointer. In addition to the regular data, there is an added row at the
806
beginning of the array that contains the name of each column of the
809
<p>As an example, consider the following query:</p>
812
SELECT employee_name, login, host FROM users WHERE login LIKE 'd%';
815
<p>This query will return the name, login and host computer name
816
for every employee whose login begins with the letter "d". If this
817
query is submitted to <b>sqlite_get_table</b> the result might
823
result[0] = "employee_name"<br>
824
result[1] = "login"<br>
825
result[2] = "host"<br>
826
result[3] = "dummy"<br>
827
result[4] = "No such user"<br>
828
result[5] = 0<br>
829
result[6] = "D. Richard Hipp"<br>
830
result[7] = "drh"<br>
831
result[8] = "zadok"
834
<p>Notice that the "host" value for the "dummy" record is NULL so
835
the result[] array contains a NULL pointer at that slot.</p>
837
<p>If the result set of a query is empty, then by default
838
<b>sqlite_get_table</b> will set nrow to 0 and leave its
839
result parameter is set to NULL. But if the EMPTY_RESULT_CALLBACKS
840
pragma is ON then the result parameter is initialized to the names
841
of the columns only. For example, consider this query which has
842
an empty result set:</p>
845
SELECT employee_name, login, host FROM users WHERE employee_name IS NULL;
849
The default behavior gives this results:
859
But if the EMPTY_RESULT_CALLBACKS pragma is ON, then the following
866
result[0] = "employee_name"<br>
867
result[1] = "login"<br>
868
result[2] = "host"<br>
871
<p>Memory to hold the information returned by <b>sqlite_get_table</b>
872
is obtained from malloc(). But the calling function should not try
873
to free this information directly. Instead, pass the complete table
874
to <b>sqlite_free_table</b> when the table is no longer needed.
875
It is safe to call <b>sqlite_free_table</b> with a NULL pointer such
876
as would be returned if the result set is empty.</p>
878
<p>The <b>sqlite_get_table</b> routine returns the same integer
879
result code as <b>sqlite_exec</b>.</p>
881
<h4>3.4 Interrupting an SQLite operation</h4>
883
<p>The <b>sqlite_interrupt</b> function can be called from a
884
different thread or from a signal handler to cause the current database
885
operation to exit at its first opportunity. When this happens,
886
the <b>sqlite_exec</b> routine (or the equivalent) that started
887
the database operation will return SQLITE_INTERRUPT.</p>
889
<h4>3.5 Testing for a complete SQL statement</h4>
891
<p>The next interface routine to SQLite is a convenience function used
892
to test whether or not a string forms a complete SQL statement.
893
If the <b>sqlite_complete</b> function returns true when its input
894
is a string, then the argument forms a complete SQL statement.
895
There are no guarantees that the syntax of that statement is correct,
896
but we at least know the statement is complete. If <b>sqlite_complete</b>
897
returns false, then more text is required to complete the SQL statement.</p>
899
<p>For the purpose of the <b>sqlite_complete</b> function, an SQL
900
statement is complete if it ends in a semicolon.</p>
902
<p>The <b>sqlite</b> command-line utility uses the <b>sqlite_complete</b>
903
function to know when it needs to call <b>sqlite_exec</b>. After each
904
line of input is received, <b>sqlite</b> calls <b>sqlite_complete</b>
905
on all input in its buffer. If <b>sqlite_complete</b> returns true,
906
then <b>sqlite_exec</b> is called and the input buffer is reset. If
907
<b>sqlite_complete</b> returns false, then the prompt is changed to
908
the continuation prompt and another line of text is read and added to
909
the input buffer.</p>
911
<h4>3.6 Library version string</h4>
913
<p>The SQLite library exports the string constant named
914
<b>sqlite_version</b> which contains the version number of the
915
library. The header file contains a macro SQLITE_VERSION
916
with the same information. If desired, a program can compare
917
the SQLITE_VERSION macro against the <b>sqlite_version</b>
918
string constant to verify that the version number of the
919
header file and the library match.</p>
921
<h4>3.7 Library character encoding</h4>
923
<p>By default, SQLite assumes that all data uses a fixed-size
924
8-bit character (iso8859). But if you give the --enable-utf8 option
925
to the configure script, then the library assumes UTF-8 variable
926
sized characters. This makes a difference for the LIKE and GLOB
927
operators and the LENGTH() and SUBSTR() functions. The static
928
string <b>sqlite_encoding</b> will be set to either "UTF-8" or
929
"iso8859" to indicate how the library was compiled. In addition,
930
the <b>sqlite.h</b> header file will define one of the
931
macros <b>SQLITE_UTF8</b> or <b>SQLITE_ISO8859</b>, as appropriate.</p>
933
<p>Note that the character encoding mechanism used by SQLite cannot
934
be changed at run-time. This is a compile-time option only. The
935
<b>sqlite_encoding</b> character string just tells you how the library
938
<h4>3.8 Changing the library's response to locked files</h4>
940
<p>The <b>sqlite_busy_handler</b> procedure can be used to register
941
a busy callback with an open SQLite database. The busy callback will
942
be invoked whenever SQLite tries to access a database that is locked.
943
The callback will typically do some other useful work, or perhaps sleep,
944
in order to give the lock a chance to clear. If the callback returns
945
non-zero, then SQLite tries again to access the database and the cycle
946
repeats. If the callback returns zero, then SQLite aborts the current
947
operation and returns SQLITE_BUSY.</p>
949
<p>The arguments to <b>sqlite_busy_handler</b> are the opaque
950
structure returned from <b>sqlite_open</b>, a pointer to the busy
951
callback function, and a generic pointer that will be passed as
952
the first argument to the busy callback. When SQLite invokes the
953
busy callback, it sends it three arguments: the generic pointer
954
that was passed in as the third argument to <b>sqlite_busy_handler</b>,
955
the name of the database table or index that the library is trying
956
to access, and the number of times that the library has attempted to
957
access the database table or index.</p>
959
<p>For the common case where we want the busy callback to sleep,
960
the SQLite library provides a convenience routine <b>sqlite_busy_timeout</b>.
961
The first argument to <b>sqlite_busy_timeout</b> is a pointer to
962
an open SQLite database and the second argument is a number of milliseconds.
963
After <b>sqlite_busy_timeout</b> has been executed, the SQLite library
964
will wait for the lock to clear for at least the number of milliseconds
965
specified before it returns SQLITE_BUSY. Specifying zero milliseconds for
966
the timeout restores the default behavior.</p>
968
<h4>3.9 Using the <tt>_printf()</tt> wrapper functions</h4>
970
<p>The four utility functions</p>
974
<li><b>sqlite_exec_printf()</b></li>
975
<li><b>sqlite_exec_vprintf()</b></li>
976
<li><b>sqlite_get_table_printf()</b></li>
977
<li><b>sqlite_get_table_vprintf()</b></li>
981
<p>implement the same query functionality as <b>sqlite_exec</b>
982
and <b>sqlite_get_table</b>. But instead of taking a complete
983
SQL statement as their second argument, the four <b>_printf</b>
984
routines take a printf-style format string. The SQL statement to
985
be executed is generated from this format string and from whatever
986
additional arguments are attached to the end of the function call.</p>
988
<p>There are two advantages to using the SQLite printf
989
functions instead of <b>sprintf</b>. First of all, with the
990
SQLite printf routines, there is never a danger of overflowing a
991
static buffer as there is with <b>sprintf</b>. The SQLite
992
printf routines automatically allocate (and later frees)
994
necessary to hold the SQL statements generated.</p>
996
<p>The second advantage the SQLite printf routines have over
997
<b>sprintf</b> are two new formatting options specifically designed
998
to support string literals in SQL. Within the format string,
999
the %q formatting option works very much like %s in that it
1000
reads a null-terminated string from the argument list and inserts
1001
it into the result. But %q translates the inserted string by
1002
making two copies of every single-quote (') character in the
1003
substituted string. This has the effect of escaping the end-of-string
1004
meaning of single-quote within a string literal. The %Q formatting
1005
option works similar; it translates the single-quotes like %q and
1006
additionally encloses the resulting string in single-quotes.
1007
If the argument for the %Q formatting options is a NULL pointer,
1008
the resulting string is NULL without single quotes.
1011
<p>Consider an example. Suppose you are trying to insert a string
1012
value into a database table where the string value was obtained from
1013
user input. Suppose the string to be inserted is stored in a variable
1014
named zString. The code to do the insertion might look like this:</p>
1017
sqlite_exec_printf(db,
1018
"INSERT INTO table1 VALUES('%s')",
1022
<p>If the zString variable holds text like "Hello", then this statement
1023
will work just fine. But suppose the user enters a string like
1024
"Hi y'all!". The SQL statement generated reads as follows:
1027
INSERT INTO table1 VALUES('Hi y'all')
1030
<p>This is not valid SQL because of the apostrophe in the word "y'all".
1031
But if the %q formatting option is used instead of %s, like this:</p>
1034
sqlite_exec_printf(db,
1035
"INSERT INTO table1 VALUES('%q')",
1039
<p>Then the generated SQL will look like the following:</p>
1042
INSERT INTO table1 VALUES('Hi y''all')
1045
<p>Here the apostrophe has been escaped and the SQL statement is well-formed.
1046
When generating SQL on-the-fly from data that might contain a
1047
single-quote character ('), it is always a good idea to use the
1048
SQLite printf routines and the %q formatting option instead of <b>sprintf</b>.
1051
<p>If the %Q formatting option is used instead of %q, like this:</p>
1054
sqlite_exec_printf(db,
1055
"INSERT INTO table1 VALUES(%Q)",
1059
<p>Then the generated SQL will look like the following:</p>
1062
INSERT INTO table1 VALUES('Hi y''all')
1065
<p>If the value of the zString variable is NULL, the generated SQL
1066
will look like the following:</p>
1069
INSERT INTO table1 VALUES(NULL)
1072
<p>All of the _printf() routines above are built around the following
1076
char *sqlite_mprintf(const char *zFormat, ...);
1077
char *sqlite_vmprintf(const char *zFormat, va_list);
1080
<p>The <b>sqlite_mprintf()</b> routine works like the standard library
1081
<b>sprintf()</b> except that it writes its results into memory obtained
1082
from malloc() and returns a pointer to the malloced buffer.
1083
<b>sqlite_mprintf()</b> also understands the %q and %Q extensions described
1084
above. The <b>sqlite_vmprintf()</b> is a varargs version of the same
1085
routine. The string pointer that these routines return should be freed
1086
by passing it to <b>sqlite_freemem()</b>.
1089
<h4>3.10 Performing background jobs during large queries</h3>
1091
<p>The <b>sqlite_progress_handler()</b> routine can be used to register a
1092
callback routine with an SQLite database to be invoked periodically during long
1093
running calls to <b>sqlite_exec()</b>, <b>sqlite_step()</b> and the various
1097
<p>The callback is invoked every N virtual machine operations, where N is
1098
supplied as the second argument to <b>sqlite_progress_handler()</b>. The third
1099
and fourth arguments to <b>sqlite_progress_handler()</b> are a pointer to the
1100
routine to be invoked and a void pointer to be passed as the first argument to
1104
<p>The time taken to execute each virtual machine operation can vary based on
1105
many factors. A typical value for a 1 GHz PC is between half and three million
1106
per second but may be much higher or lower, depending on the query. As such it
1107
is difficult to schedule background operations based on virtual machine
1108
operations. Instead, it is recommended that a callback be scheduled relatively
1109
frequently (say every 1000 instructions) and external timer routines used to
1110
determine whether or not background jobs need to be run.
1113
<a name="cfunc"></a>
1114
<h3>4.0 Adding New SQL Functions</h3>
1116
<p>Beginning with version 2.4.0, SQLite allows the SQL language to be
1117
extended with new functions implemented as C code. The following interface
1122
typedef struct sqlite_func sqlite_func;
1124
int sqlite_create_function(
1128
void (*xFunc)(sqlite_func*,int,const char**),
1131
int sqlite_create_aggregate(
1135
void (*xStep)(sqlite_func*,int,const char**),
1136
void (*xFinalize)(sqlite_func*),
1140
char *sqlite_set_result_string(sqlite_func*,const char*,int);
1141
void sqlite_set_result_int(sqlite_func*,int);
1142
void sqlite_set_result_double(sqlite_func*,double);
1143
void sqlite_set_result_error(sqlite_func*,const char*,int);
1145
void *sqlite_user_data(sqlite_func*);
1146
void *sqlite_aggregate_context(sqlite_func*, int nBytes);
1147
int sqlite_aggregate_count(sqlite_func*);
1151
The <b>sqlite_create_function()</b> interface is used to create
1152
regular functions and <b>sqlite_create_aggregate()</b> is used to
1153
create new aggregate functions. In both cases, the <b>db</b>
1154
parameter is an open SQLite database on which the functions should
1155
be registered, <b>zName</b> is the name of the new function,
1156
<b>nArg</b> is the number of arguments, and <b>pUserData</b> is
1157
a pointer which is passed through unchanged to the C implementation
1158
of the function. Both routines return 0 on success and non-zero
1159
if there are any errors.
1163
The length of a function name may not exceed 255 characters.
1164
Any attempt to create a function whose name exceeds 255 characters
1165
in length will result in an error.
1169
For regular functions, the <b>xFunc</b> callback is invoked once
1170
for each function call. The implementation of xFunc should call
1171
one of the <b>sqlite_set_result_...</b> interfaces to return its
1172
result. The <b>sqlite_user_data()</b> routine can be used to
1173
retrieve the <b>pUserData</b> pointer that was passed in when the
1174
function was registered.
1178
For aggregate functions, the <b>xStep</b> callback is invoked once
1179
for each row in the result and then <b>xFinalize</b> is invoked at the
1180
end to compute a final answer. The xStep routine can use the
1181
<b>sqlite_aggregate_context()</b> interface to allocate memory that
1182
will be unique to that particular instance of the SQL function.
1183
This memory will be automatically deleted after xFinalize is called.
1184
The <b>sqlite_aggregate_count()</b> routine can be used to find out
1185
how many rows of data were passed to the aggregate. The xFinalize
1186
callback should invoke one of the <b>sqlite_set_result_...</b>
1187
interfaces to set the final result of the aggregate.
1191
SQLite now implements all of its built-in functions using this
1192
interface. For additional information and examples on how to create
1193
new SQL functions, review the SQLite source code in the file
1197
<h3>5.0 Multi-Threading And SQLite</h3>
1200
If SQLite is compiled with the THREADSAFE preprocessor macro set to 1,
1201
then it is safe to use SQLite from two or more threads of the same process
1202
at the same time. But each thread should have its own <b>sqlite*</b>
1203
pointer returned from <b>sqlite_open</b>. It is never safe for two
1204
or more threads to access the same <b>sqlite*</b> pointer at the same time.
1208
In precompiled SQLite libraries available on the website, the Unix
1209
versions are compiled with THREADSAFE turned off but the Windows
1210
versions are compiled with THREADSAFE turned on. If you need something
1211
different that this you will have to recompile.
1215
Under Unix, an <b>sqlite*</b> pointer should not be carried across a
1216
<b>fork()</b> system call into the child process. The child process
1217
should open its own copy of the database after the <b>fork()</b>.
1220
<h3>6.0 Usage Examples</h3>
1222
<p>For examples of how the SQLite C/C++ interface can be used,
1223
refer to the source code for the <b>sqlite</b> program in the
1224
file <b>src/shell.c</b> of the source tree.
1225
Additional information about sqlite is available at
1226
<a href="sqlite.html">sqlite.html</a>.
1227
See also the sources to the Tcl interface for SQLite in
1228
the source file <b>src/tclsqlite.c</b>.</p>
added
removed
c_interface.html
