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>C/C++ Interface For SQLite Version 3 (old)</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>
123
<font color="#7f001f"><p>
125
This document was written in 2004 as a guide to helping programmers
126
move from using SQLite version 2 to SQLite version 3. The information
127
in this document is still essentially correct, however there have been
128
many changes and enhancements over the years. We recommend that the
129
following documents be used instead:
131
<li><a href="cintro.html">An Introduction To The SQLite C/C++ Interface</a></li>
132
<li><a href="c3ref/intro.html">SQLite C/C++ Reference Guide</a></li>
137
<h2>C/C++ Interface For SQLite Version 3</h2>
139
<h3>1.0 Overview</h3>
142
SQLite version 3.0 is a new version of SQLite, derived from
143
the SQLite 2.8.13 code base, but with an incompatible file format
145
SQLite version 3.0 was created to answer demand for the following features:
149
<li>Support for UTF-16.</li>
150
<li>User-definable text collating sequences.</li>
151
<li>The ability to store BLOBs in indexed columns.</li>
155
It was necessary to move to version 3.0 to implement these features because
156
each requires incompatible changes to the database file format. Other
157
incompatible changes, such as a cleanup of the API, were introduced at the
158
same time under the theory that it is best to get your incompatible changes
159
out of the way all at once.
163
The API for version 3.0 is similar to the version 2.X API,
164
but with some important changes. Most noticeably, the "<tt>sqlite_</tt>"
165
prefix that occurs on the beginning of all API functions and data
166
structures are changed to "<tt>sqlite3_</tt>".
167
This avoids confusion between the two APIs and allows linking against both
168
SQLite 2.X and SQLite 3.0 at the same time.
172
There is no agreement on what the C datatype for a UTF-16
173
string should be. Therefore, SQLite uses a generic type of void*
174
to refer to UTF-16 strings. Client software can cast the void*
175
to whatever datatype is appropriate for their system.
178
<h3>2.0 C/C++ Interface</h3>
181
The API for SQLite 3.0 includes 83 separate functions in addition
182
to several data structures and #defines. (A complete
183
<a href="c3ref/intro.html">API reference</a> is provided as a separate
185
Fortunately, the interface is not nearly as complex as its size implies.
186
Simple programs can still make do with only 3 functions:
187
<a href="c3ref/open.html">sqlite3_open()</a>, <a href="c3ref/exec.html">sqlite3_exec()</a>, and <a href="c3ref/close.html">sqlite3_close()</a>.
188
More control over the execution of the database engine is provided
189
using <a href="c3ref/prepare.html">sqlite3_prepare_v2()</a>
190
to compile an SQLite statement into byte code and
191
<a href="c3ref/step.html">sqlite3_step()</a> to execute that bytecode.
192
A family of routines with names beginning with
193
<a href="c3ref/column_blob.html">sqlite3_column_</a>
194
is used to extract information about the result set of a query.
195
Many interface functions come in pairs, with both a UTF-8 and
196
UTF-16 version. And there is a collection of routines
197
used to implement user-defined SQL functions and user-defined
198
text collating sequences.
202
<h4>2.1 Opening and closing a database</h4>
205
typedef struct sqlite3 sqlite3;
206
int sqlite3_open(const char*, sqlite3**);
207
int sqlite3_open16(const void*, sqlite3**);
208
int sqlite3_close(sqlite3*);
209
const char *sqlite3_errmsg(sqlite3*);
210
const void *sqlite3_errmsg16(sqlite3*);
211
int sqlite3_errcode(sqlite3*);
215
The sqlite3_open() routine returns an integer error code rather than
216
a pointer to the sqlite3 structure as the version 2 interface did.
217
The difference between sqlite3_open()
218
and sqlite3_open16() is that sqlite3_open16() takes UTF-16 (in host native
219
byte order) for the name of the database file. If a new database file
220
needs to be created, then sqlite3_open16() sets the internal text
221
representation to UTF-16 whereas sqlite3_open() sets the text
222
representation to UTF-8.
226
The opening and/or creating of the database file is deferred until the
227
file is actually needed. This allows options and parameters, such
228
as the native text representation and default page size, to be
229
set using PRAGMA statements.
233
The sqlite3_errcode() routine returns a result code for the most
234
recent major API call. sqlite3_errmsg() returns an English-language
235
text error message for the most recent error. The error message is
236
represented in UTF-8 and will be ephemeral - it could disappear on
237
the next call to any SQLite API function. sqlite3_errmsg16() works like
238
sqlite3_errmsg() except that it returns the error message represented
239
as UTF-16 in host native byte order.
243
The error codes for SQLite version 3 are unchanged from version 2.
248
#define SQLITE_OK 0 /* Successful result */
249
#define SQLITE_ERROR 1 /* SQL error or missing database */
250
#define SQLITE_INTERNAL 2 /* An internal logic error in SQLite */
251
#define SQLITE_PERM 3 /* Access permission denied */
252
#define SQLITE_ABORT 4 /* Callback routine requested an abort */
253
#define SQLITE_BUSY 5 /* The database file is locked */
254
#define SQLITE_LOCKED 6 /* A table in the database is locked */
255
#define SQLITE_NOMEM 7 /* A malloc() failed */
256
#define SQLITE_READONLY 8 /* Attempt to write a readonly database */
257
#define SQLITE_INTERRUPT 9 /* Operation terminated by sqlite_interrupt() */
258
#define SQLITE_IOERR 10 /* Some kind of disk I/O error occurred */
259
#define SQLITE_CORRUPT 11 /* The database disk image is malformed */
260
#define SQLITE_NOTFOUND 12 /* (Internal Only) Table or record not found */
261
#define SQLITE_FULL 13 /* Insertion failed because database is full */
262
#define SQLITE_CANTOPEN 14 /* Unable to open the database file */
263
#define SQLITE_PROTOCOL 15 /* Database lock protocol error */
264
#define SQLITE_EMPTY 16 /* (Internal Only) Database table is empty */
265
#define SQLITE_SCHEMA 17 /* The database schema changed */
266
#define SQLITE_TOOBIG 18 /* Too much data for one row of a table */
267
#define SQLITE_CONSTRAINT 19 /* Abort due to contraint violation */
268
#define SQLITE_MISMATCH 20 /* Data type mismatch */
269
#define SQLITE_MISUSE 21 /* Library used incorrectly */
270
#define SQLITE_NOLFS 22 /* Uses OS features not supported on host */
271
#define SQLITE_AUTH 23 /* Authorization denied */
272
#define SQLITE_ROW 100 /* sqlite_step() has another row ready */
273
#define SQLITE_DONE 101 /* sqlite_step() has finished executing */
276
<h4>2.2 Executing SQL statements</h4>
279
typedef int (*sqlite_callback)(void*,int,char**, char**);
280
int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);
284
The <a href="c3ref/exec.html">sqlite3_exec()</a> function works much as it did in SQLite version 2.
285
Zero or more SQL statements specified in the second parameter are compiled
286
and executed. Query results are returned to a callback routine.
290
In SQLite version 3, the sqlite3_exec routine is just a wrapper around
291
calls to the prepared statement interface.
295
typedef struct sqlite3_stmt sqlite3_stmt;
296
int sqlite3_prepare(sqlite3*, const char*, int, sqlite3_stmt**, const char**);
297
int sqlite3_prepare16(sqlite3*, const void*, int, sqlite3_stmt**, const void**);
298
int sqlite3_finalize(sqlite3_stmt*);
299
int sqlite3_reset(sqlite3_stmt*);
303
The sqlite3_prepare interface compiles a single SQL statement into byte code
304
for later execution. This interface is now the preferred way of accessing
309
The SQL statement is a UTF-8 string for sqlite3_prepare().
310
The sqlite3_prepare16() works the same way except
311
that it expects a UTF-16 string as SQL input.
312
Only the first SQL statement in the input string is compiled.
313
The fifth parameter is filled in with a pointer to the next (uncompiled)
314
SQLite statement in the input string, if any.
315
The sqlite3_finalize() routine deallocates a prepared SQL statement.
316
All prepared statements must be finalized before the database can be
318
The sqlite3_reset() routine resets a prepared SQL statement so that it
319
can be executed again.
323
The SQL statement may contain tokens of the form "?" or "?nnn" or ":aaa"
324
where "nnn" is an integer and "aaa" is an identifier.
325
Such tokens represent unspecified literal values (or "wildcards")
326
to be filled in later by the
327
<a href="c3ref/bind_blob.html">sqlite3_bind</a> interface.
328
Each wildcard has an associated number which is its sequence in the
329
statement or the "nnn" in the case of a "?nnn" form.
330
It is allowed for the same wildcard
331
to occur more than once in the same SQL statement, in which case
332
all instance of that wildcard will be filled in with the same value.
333
Unbound wildcards have a value of NULL.
337
int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
338
int sqlite3_bind_double(sqlite3_stmt*, int, double);
339
int sqlite3_bind_int(sqlite3_stmt*, int, int);
340
int sqlite3_bind_int64(sqlite3_stmt*, int, long long int);
341
int sqlite3_bind_null(sqlite3_stmt*, int);
342
int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
343
int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
344
int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
348
There is an assortment of sqlite3_bind routines used to assign values
349
to wildcards in a prepared SQL statement. Unbound wildcards
350
are interpreted as NULLs. Bindings are not reset by sqlite3_reset().
351
But wildcards can be rebound to new values after an sqlite3_reset().
355
After an SQL statement has been prepared (and optionally bound), it
360
int sqlite3_step(sqlite3_stmt*);
364
The sqlite3_step() routine return SQLITE_ROW if it is returning a single
365
row of the result set, or SQLITE_DONE if execution has completed, either
366
normally or due to an error. It might also return SQLITE_BUSY if it is
367
unable to open the database file. If the return value is SQLITE_ROW, then
368
the following routines can be used to extract information about that row
373
const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
374
int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
375
int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
376
int sqlite3_column_count(sqlite3_stmt*);
377
const char *sqlite3_column_decltype(sqlite3_stmt *, int iCol);
378
const void *sqlite3_column_decltype16(sqlite3_stmt *, int iCol);
379
double sqlite3_column_double(sqlite3_stmt*, int iCol);
380
int sqlite3_column_int(sqlite3_stmt*, int iCol);
381
long long int sqlite3_column_int64(sqlite3_stmt*, int iCol);
382
const char *sqlite3_column_name(sqlite3_stmt*, int iCol);
383
const void *sqlite3_column_name16(sqlite3_stmt*, int iCol);
384
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
385
const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
386
int sqlite3_column_type(sqlite3_stmt*, int iCol);
390
The <a href="c3ref/column_count.html">sqlite3_column_count()</a>
391
function returns the number of columns in
392
the results set. sqlite3_column_count() can be called at any time after
393
<a href="c3ref/prepare.html">sqlite3_prepare_v2()</a>. <a href="c3ref/data_count.html">sqlite3_data_count()</a> works similarly to
394
<a href="c3ref/column_count.html">sqlite3_column_count()</a> except that it only works following <a href="c3ref/step.html">sqlite3_step()</a>.
395
If the previous call to <a href="c3ref/step.html">sqlite3_step()</a> returned SQLITE_DONE or an error code,
396
then <a href="c3ref/data_count.html">sqlite3_data_count()</a> will return 0 whereas <a href="c3ref/column_count.html">sqlite3_column_count()</a> will
397
continue to return the number of columns in the result set.
400
<p>Returned data is examined using the other
401
<a href="c3ref/column_blob.html">sqlite3_column_***()</a> functions,
402
all of which take a column number as their second parameter. Columns are
403
zero-indexed from left to right. Note that this is different to parameters,
404
which are indexed starting at one.
408
The <a href="c3ref/column_blob.html">sqlite3_column_type()</a> function returns the
409
datatype for the value in the Nth column. The return value is one
414
#define SQLITE_INTEGER 1
415
#define SQLITE_FLOAT 2
416
#define SQLITE_TEXT 3
417
#define SQLITE_BLOB 4
418
#define SQLITE_NULL 5
422
The sqlite3_column_decltype() routine returns text which is the
423
declared type of the column in the CREATE TABLE statement. For an
424
expression, the return type is an empty string. sqlite3_column_name()
425
returns the name of the Nth column. sqlite3_column_bytes() returns
426
the number of bytes in a column that has type BLOB or the number of bytes
427
in a TEXT string with UTF-8 encoding. sqlite3_column_bytes16() returns
428
the same value for BLOBs but for TEXT strings returns the number of bytes
429
in a UTF-16 encoding.
430
sqlite3_column_blob() return BLOB data.
431
sqlite3_column_text() return TEXT data as UTF-8.
432
sqlite3_column_text16() return TEXT data as UTF-16.
433
sqlite3_column_int() return INTEGER data in the host machines native
435
sqlite3_column_int64() returns 64-bit INTEGER data.
436
Finally, sqlite3_column_double() return floating point data.
440
It is not necessary to retrieve data in the format specify by
441
sqlite3_column_type(). If a different format is requested, the data
442
is converted automatically.
446
Data format conversions can invalidate the pointer returned by
447
prior calls to sqlite3_column_blob(), sqlite3_column_text(), and/or
448
sqlite3_column_text16(). Pointers might be invalided in the following
453
The initial content is a BLOB and sqlite3_column_text()
454
or sqlite3_column_text16()
455
is called. A zero-terminator might need to be added to the string.
458
The initial content is UTF-8 text and sqlite3_column_bytes16() or
459
sqlite3_column_text16() is called. The content must be converted to UTF-16.
462
The initial content is UTF-16 text and sqlite3_column_bytes() or
463
sqlite3_column_text() is called. The content must be converted to UTF-8.
467
Note that conversions between UTF-16be and UTF-16le
468
are always done in place and do
469
not invalidate a prior pointer, though of course the content of the buffer
470
that the prior pointer points to will have been modified. Other kinds
471
of conversion are done in place when it is possible, but sometime it is
472
not possible and in those cases prior pointers are invalidated.
476
The safest and easiest to remember policy is this: assume that any
479
<li>sqlite3_column_blob(),</li>
480
<li>sqlite3_column_text(), or</li>
481
<li>sqlite3_column_text16()</li>
483
is invalided by subsequent calls to
485
<li>sqlite3_column_bytes(),</li>
486
<li>sqlite3_column_bytes16(),</li>
487
<li>sqlite3_column_text(), or</li>
488
<li>sqlite3_column_text16().</li>
490
This means that you should always call sqlite3_column_bytes() or
491
sqlite3_column_bytes16() <u>before</u> calling sqlite3_column_blob(),
492
sqlite3_column_text(), or sqlite3_column_text16().
495
<h4>2.3 User-defined functions</h4>
498
User defined functions can be created using the following routine:
502
typedef struct sqlite3_value sqlite3_value;
503
int sqlite3_create_function(
505
const char *zFunctionName,
509
void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
510
void (*xStep)(sqlite3_context*,int,sqlite3_value**),
511
void (*xFinal)(sqlite3_context*)
513
int sqlite3_create_function16(
515
const void *zFunctionName,
519
void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
520
void (*xStep)(sqlite3_context*,int,sqlite3_value**),
521
void (*xFinal)(sqlite3_context*)
523
#define SQLITE_UTF8 1
524
#define SQLITE_UTF16 2
525
#define SQLITE_UTF16BE 3
526
#define SQLITE_UTF16LE 4
531
The nArg parameter specifies the number of arguments to the function.
532
A value of 0 indicates that any number of arguments is allowed. The
533
eTextRep parameter specifies what representation text values are expected
534
to be in for arguments to this function. The value of this parameter should
535
be one of the parameters defined above. SQLite version 3 allows multiple
536
implementations of the same function using different text representations.
537
The database engine chooses the function that minimization the number
538
of text conversions required.
542
Normal functions specify only xFunc and leave xStep and xFinal set to NULL.
543
Aggregate functions specify xStep and xFinal and leave xFunc set to NULL.
544
There is no separate sqlite3_create_aggregate() API.
548
The function name is specified in UTF-8. A separate sqlite3_create_function16()
549
API works the same as sqlite_create_function()
550
except that the function name is specified in UTF-16 host byte order.
554
Notice that the parameters to functions are now pointers to sqlite3_value
555
structures instead of pointers to strings as in SQLite version 2.X.
556
The following routines are used to extract useful information from these
561
const void *sqlite3_value_blob(sqlite3_value*);
562
int sqlite3_value_bytes(sqlite3_value*);
563
int sqlite3_value_bytes16(sqlite3_value*);
564
double sqlite3_value_double(sqlite3_value*);
565
int sqlite3_value_int(sqlite3_value*);
566
long long int sqlite3_value_int64(sqlite3_value*);
567
const unsigned char *sqlite3_value_text(sqlite3_value*);
568
const void *sqlite3_value_text16(sqlite3_value*);
569
int sqlite3_value_type(sqlite3_value*);
573
Function implementations use the following APIs to acquire context and
578
void *sqlite3_aggregate_context(sqlite3_context*, int nbyte);
579
void *sqlite3_user_data(sqlite3_context*);
580
void sqlite3_result_blob(sqlite3_context*, const void*, int n, void(*)(void*));
581
void sqlite3_result_double(sqlite3_context*, double);
582
void sqlite3_result_error(sqlite3_context*, const char*, int);
583
void sqlite3_result_error16(sqlite3_context*, const void*, int);
584
void sqlite3_result_int(sqlite3_context*, int);
585
void sqlite3_result_int64(sqlite3_context*, long long int);
586
void sqlite3_result_null(sqlite3_context*);
587
void sqlite3_result_text(sqlite3_context*, const char*, int n, void(*)(void*));
588
void sqlite3_result_text16(sqlite3_context*, const void*, int n, void(*)(void*));
589
void sqlite3_result_value(sqlite3_context*, sqlite3_value*);
590
void *sqlite3_get_auxdata(sqlite3_context*, int);
591
void sqlite3_set_auxdata(sqlite3_context*, int, void*, void (*)(void*));
594
<h4>2.4 User-defined collating sequences</h4>
597
The following routines are used to implement user-defined
602
sqlite3_create_collation(sqlite3*, const char *zName, int eTextRep, void*,
603
int(*xCompare)(void*,int,const void*,int,const void*));
604
sqlite3_create_collation16(sqlite3*, const void *zName, int eTextRep, void*,
605
int(*xCompare)(void*,int,const void*,int,const void*));
606
sqlite3_collation_needed(sqlite3*, void*,
607
void(*)(void*,sqlite3*,int eTextRep,const char*));
608
sqlite3_collation_needed16(sqlite3*, void*,
609
void(*)(void*,sqlite3*,int eTextRep,const void*));
613
The sqlite3_create_collation() function specifies a collating sequence name
614
and a comparison function to implement that collating sequence. The
615
comparison function is only used for comparing text values. The eTextRep
616
parameter is one of SQLITE_UTF8, SQLITE_UTF16LE, SQLITE_UTF16BE, or
617
SQLITE_ANY to specify which text representation the comparison function works
618
with. Separate comparison functions can exist for the same collating
619
sequence for each of the UTF-8, UTF-16LE and UTF-16BE text representations.
620
The sqlite3_create_collation16() works like sqlite3_create_collation() except
621
that the collation name is specified in UTF-16 host byte order instead of
626
The sqlite3_collation_needed() routine registers a callback which the
627
database engine will invoke if it encounters an unknown collating sequence.
628
The callback can lookup an appropriate comparison function and invoke
629
sqlite_3_create_collation() as needed. The fourth parameter to the callback
630
is the name of the collating sequence in UTF-8. For sqlite3_collation_need16()
631
the callback sends the collating sequence name in UTF-16 host byte order.
added
removed
capi3.html
