1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>An Introduction To The SQLite C/C++ Interface</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
An Introduction To The SQLite C/C++ Interface
</div>
<div class="fancy_toc">
<a onclick="toggle_toc()">
<span class="fancy_toc_mark" id="toc_mk">►</span>
Table Of Contents
</a>
<div id="toc_sub"><div class="fancy-toc1"><a href="#summary">1. Summary</a></div>
<div class="fancy-toc1"><a href="#introduction">2. Introduction</a></div>
<div class="fancy-toc1"><a href="#core_objects_and_interfaces">3. Core Objects And Interfaces</a></div>
<div class="fancy-toc1"><a href="#typical_usage_of_core_routines_and_objects">4. Typical Usage Of Core Routines And Objects</a></div>
<div class="fancy-toc1"><a href="#convenience_wrappers_around_core_routines">5. Convenience Wrappers Around Core Routines</a></div>
<div class="fancy-toc1"><a href="#binding_parameters_and_reusing_prepared_statements">6. Binding Parameters and Reusing Prepared Statements</a></div>
<div class="fancy-toc1"><a href="#configuring_sqlite">7. Configuring SQLite</a></div>
<div class="fancy-toc1"><a href="#extending_sqlite">8. Extending SQLite</a></div>
<div class="fancy-toc1"><a href="#other_interfaces">9. Other Interfaces</a></div>
</div>
</div>
<script>
function toggle_toc(){
var sub = document.getElementById("toc_sub")
var mk = document.getElementById("toc_mk")
if( sub.style.display!="block" ){
sub.style.display = "block";
mk.innerHTML = "▼";
} else {
sub.style.display = "none";
mk.innerHTML = "►";
}
}
</script>
</div>
<h1 id="summary"><span>1. </span>Summary</h1>
<p>The following two objects and eight methods comprise the essential
elements of the SQLite interface:
</p><ul>
<li><p><b><a href="c3ref/sqlite3.html">sqlite3</a></b> →
The database connection object. Created by
<a href="c3ref/open.html">sqlite3_open()</a> and destroyed by <a href="c3ref/close.html">sqlite3_close()</a>.
</p></li><li><p><b><a href="c3ref/stmt.html">sqlite3_stmt</a></b> →
The prepared statement object. Created by
<a href="c3ref/prepare.html">sqlite3_prepare()</a> and destroyed by <a href="c3ref/finalize.html">sqlite3_finalize()</a>.
</p></li><li><p><b><a href="c3ref/open.html">sqlite3_open()</a></b> →
Open a connection to a new or existing SQLite database.
The constructor for <a href="c3ref/sqlite3.html">sqlite3</a>.
</p></li><li><p><b><a href="c3ref/prepare.html">sqlite3_prepare()</a></b> →
Compile SQL text into
byte-code that will do the work of querying or updating the database.
The constructor for <a href="c3ref/stmt.html">sqlite3_stmt</a>.
</p></li><li><p><b><a href="c3ref/bind_blob.html">sqlite3_bind()</a></b> →
Store application data into
<a href="lang_expr.html#varparam">parameters</a> of the original SQL.
</p></li><li><p><b><a href="c3ref/step.html">sqlite3_step()</a></b> →
Advance an <a href="c3ref/stmt.html">sqlite3_stmt</a> to the next result row or to completion.
</p></li><li><p><b><a href="c3ref/column_blob.html">sqlite3_column()</a></b> →
Column values in the current result row for an <a href="c3ref/stmt.html">sqlite3_stmt</a>.
</p></li><li><p><b><a href="c3ref/finalize.html">sqlite3_finalize()</a></b> →
Destructor for <a href="c3ref/stmt.html">sqlite3_stmt</a>.
</p></li><li><p><b><a href="c3ref/close.html">sqlite3_close()</a></b> →
Destructor for <a href="c3ref/sqlite3.html">sqlite3</a>.
</p></li><li><p><b><a href="c3ref/exec.html">sqlite3_exec()</a></b> →
A wrapper function that does <a href="c3ref/prepare.html">sqlite3_prepare()</a>, <a href="c3ref/step.html">sqlite3_step()</a>,
<a href="c3ref/column_blob.html">sqlite3_column()</a>, and <a href="c3ref/finalize.html">sqlite3_finalize()</a> for
a string of one or more SQL statements.
</p></li></ul>
<h1 id="introduction"><span>2. </span>Introduction</h1>
<p>
SQLite has more than 225 APIs.
However, most of the APIs are optional and very specialized
and can be ignored by beginners.
The core API is small, simple, and easy to learn.
This article summarizes the core API.
</p>
<p>
A separate document, <a href="c3ref/intro.html">The SQLite C/C++ Interface</a>,
provides detailed
specifications for all C/C++ APIs for SQLite. Once
the reader
understands the basic principles of operation for SQLite,
<a href="c3ref/intro.html">that document</a> should be used as a reference
guide. This article is intended as introduction only and is neither a
complete nor authoritative reference for the SQLite API.
</p>
<h1 id="core_objects_and_interfaces"><span>3. </span>Core Objects And Interfaces</h1>
<p>
The principal task of an SQL database engine is to evaluate SQL statements
of SQL. To accomplish this, the developer needs two objects:
</p>
<p></p><ul>
<li> The <a href="c3ref/sqlite3.html">database connection</a> object: sqlite3 </li>
<li> The <a href="c3ref/stmt.html">prepared statement</a> object: sqlite3_stmt </li>
</ul>
<p>
Strictly speaking, the <a href="c3ref/stmt.html">prepared statement</a> object is not required since
the convenience wrapper interfaces, <a href="c3ref/exec.html">sqlite3_exec</a> or
<a href="c3ref/free_table.html">sqlite3_get_table</a>, can be used and these convenience wrappers
encapsulate and hide the <a href="c3ref/stmt.html">prepared statement</a> object.
Nevertheless, an understanding of
<a href="c3ref/stmt.html">prepared statements</a> is needed to make full use of SQLite.
</p>
<p>
The <a href="c3ref/sqlite3.html">database connection</a> and <a href="c3ref/stmt.html">prepared statement</a> objects are controlled
by a small set of C/C++ interface routine listed below.
</p>
<p></p><ul>
<li> <a href="c3ref/open.html">sqlite3_open()</a> </li>
<li> <a href="c3ref/prepare.html">sqlite3_prepare()</a> </li>
<li> <a href="c3ref/step.html">sqlite3_step()</a> </li>
<li> <a href="c3ref/column_blob.html">sqlite3_column()</a> </li>
<li> <a href="c3ref/finalize.html">sqlite3_finalize()</a> </li>
<li> <a href="c3ref/close.html">sqlite3_close()</a> </li>
</ul>
<p>
Note that the list of routines above is conceptual rather than actual.
Many of these routines come in multiple versions.
For example, the list above shows a single routine
named <a href="c3ref/open.html">sqlite3_open()</a> when in fact there are three separate routines
that accomplish the same thing in slightly different ways:
<a href="c3ref/open.html">sqlite3_open()</a>, <a href="c3ref/open.html">sqlite3_open16()</a> and <a href="c3ref/open.html">sqlite3_open_v2()</a>.
The list mentions <a href="c3ref/column_blob.html">sqlite3_column()</a>
when in fact no such routine exists.
The "sqlite3_column()" shown in the list is a placeholder for
an entire family of routines that extract column
data in various datatypes.
</p>
<p>
Here is a summary of what the core interfaces do:
</p>
<ul>
<li><p><b><a href="c3ref/open.html">sqlite3_open()</a></b>
</p><p>
This routine
opens a connection to an SQLite database file and returns a
<a href="c3ref/sqlite3.html">database connection</a> object. This is often the first SQLite API
call that an application makes and is a prerequisite for most other
SQLite APIs. Many SQLite interfaces require a pointer to
the <a href="c3ref/sqlite3.html">database connection</a> object as their first parameter and can
be thought of as methods on the <a href="c3ref/sqlite3.html">database connection</a> object.
This routine is the constructor for the <a href="c3ref/sqlite3.html">database connection</a> object.
</p>
</li><li><p><b><a href="c3ref/prepare.html">sqlite3_prepare()</a></b>
</p><p>
This routine
converts SQL text into a <a href="c3ref/stmt.html">prepared statement</a> object and returns a pointer
to that object. This interface requires a <a href="c3ref/sqlite3.html">database connection</a> pointer
created by a prior call to <a href="c3ref/open.html">sqlite3_open()</a> and a text string containing
the SQL statement to be prepared. This API does not actually evaluate
the SQL statement. It merely prepares the SQL statement for evaluation.
</p><p>Think of each SQL statement as a small computer program. The purpose
of <a href="c3ref/prepare.html">sqlite3_prepare()</a> is to compile that program into object code.
The <a href="c3ref/stmt.html">prepared statement</a> is the object code. The <a href="c3ref/step.html">sqlite3_step()</a> interface
then runs the object code to get a result.
</p><p>New applications should always invoke <a href="c3ref/prepare.html">sqlite3_prepare_v2()</a> instead
of <a href="c3ref/prepare.html">sqlite3_prepare()</a>. The older <a href="c3ref/prepare.html">sqlite3_prepare()</a> is retained for
backwards compatibility. But <a href="c3ref/prepare.html">sqlite3_prepare_v2()</a> provides a much
better interface.</p>
</li><li><p><b><a href="c3ref/step.html">sqlite3_step()</a></b>
</p><p>
This routine is used to evaluate a <a href="c3ref/stmt.html">prepared statement</a> that has been
previously created by the <a href="c3ref/prepare.html">sqlite3_prepare()</a> interface. The statement
is evaluated up to the point where the first row of results are available.
To advance to the second row of results, invoke <a href="c3ref/step.html">sqlite3_step()</a> again.
Continue invoking <a href="c3ref/step.html">sqlite3_step()</a> until the statement is complete.
Statements that do not return results (ex: INSERT, UPDATE, or DELETE
statements) run to completion on a single call to <a href="c3ref/step.html">sqlite3_step()</a>.
</p></li><li><p><b><a href="c3ref/column_blob.html">sqlite3_column()</a></b>
</p><p>
This routine returns a single column from the current row of a result
set for a <a href="c3ref/stmt.html">prepared statement</a> that is being evaluated by <a href="c3ref/step.html">sqlite3_step()</a>.
Each time <a href="c3ref/step.html">sqlite3_step()</a> stops with a new result set row, this routine
can be called multiple times to find the values of all columns in that row.
</p><p>As noted above, there really is no such thing as a "sqlite3_column()"
function in the SQLite API. Instead, what we here call "sqlite3_column()"
is a place-holder for an entire family of functions that return
a value from the result set in various data types. There are also routines
in this family that return the size of the result (if it is a string or
BLOB) and the number of columns in the result set.
</p><p></p><ul>
<li> <a href="c3ref/column_blob.html">sqlite3_column_blob()</a> </li>
<li> <a href="c3ref/column_blob.html">sqlite3_column_bytes()</a> </li>
<li> <a href="c3ref/column_blob.html">sqlite3_column_bytes16()</a> </li>
<li> <a href="c3ref/column_count.html">sqlite3_column_count()</a> </li>
<li> <a href="c3ref/column_blob.html">sqlite3_column_double()</a> </li>
<li> <a href="c3ref/column_blob.html">sqlite3_column_int()</a> </li>
<li> <a href="c3ref/column_blob.html">sqlite3_column_int64()</a> </li>
<li> <a href="c3ref/column_blob.html">sqlite3_column_text()</a> </li>
<li> <a href="c3ref/column_blob.html">sqlite3_column_text16()</a> </li>
<li> <a href="c3ref/column_blob.html">sqlite3_column_type()</a> </li>
<li> <a href="c3ref/column_blob.html">sqlite3_column_value()</a> </li>
</ul>
</li><li><p><b><a href="c3ref/finalize.html">sqlite3_finalize()</a></b>
</p><p>
This routine destroys a <a href="c3ref/stmt.html">prepared statement</a> created by a prior call
to <a href="c3ref/prepare.html">sqlite3_prepare()</a>. Every prepared statement must be destroyed using
a call to this routine in order to avoid memory leaks.
</p></li><li><p><b><a href="c3ref/close.html">sqlite3_close()</a></b>
</p><p>
This routine closes a <a href="c3ref/sqlite3.html">database connection</a> previously opened by a call
to <a href="c3ref/open.html">sqlite3_open()</a>. All <a href="c3ref/stmt.html">prepared statements</a> associated with the
connection should be <a href="c3ref/finalize.html">finalized</a> prior to closing the
connection.
</p></li></ul>
<h1 id="typical_usage_of_core_routines_and_objects"><span>4. </span>Typical Usage Of Core Routines And Objects</h1>
<p>
An application will typically use
<a href="c3ref/open.html">sqlite3_open()</a> to create a single <a href="c3ref/sqlite3.html">database connection</a>
during initialization.
Note that <a href="c3ref/open.html">sqlite3_open()</a> can be used to either open existing database
files or to create and open new database files.
While many applications use only a single <a href="c3ref/sqlite3.html">database connection</a>, there is
no reason why an application cannot call <a href="c3ref/open.html">sqlite3_open()</a> multiple times
in order to open multiple <a href="c3ref/sqlite3.html">database connections</a> - either to the same
database or to different databases. Sometimes a multi-threaded application
will create separate <a href="c3ref/sqlite3.html">database connections</a> for each thread.
Note that a single <a href="c3ref/sqlite3.html">database connection</a> can access two or more
databases using the <a href="lang_attach.html">ATTACH</a> SQL command, so it is not necessary to
have a separate database connection for each database file.
</p>
<p>
Many applications destroy their <a href="c3ref/sqlite3.html">database connections</a> using calls to
<a href="c3ref/close.html">sqlite3_close()</a> at shutdown. Or, for example, an application that
uses SQLite as its <a href="appfileformat.html">application file format</a> might
open <a href="c3ref/sqlite3.html">database connections</a> in response to a File/Open menu action
and then destroy the corresponding <a href="c3ref/sqlite3.html">database connection</a> in response
to the File/Close menu.
</p>
<p>
To run an SQL statement, the application follows these steps:
</p>
<p></p><ol>
<li> Create a <a href="c3ref/stmt.html">prepared statement</a> using <a href="c3ref/prepare.html">sqlite3_prepare()</a>. </li>
<li> Evaluate the <a href="c3ref/stmt.html">prepared statement</a> by calling <a href="c3ref/step.html">sqlite3_step()</a> one
or more times. </li>
<li> For queries, extract results by calling
<a href="c3ref/column_blob.html">sqlite3_column()</a> in between
two calls to <a href="c3ref/step.html">sqlite3_step()</a>. </li>
<li> Destroy the <a href="c3ref/stmt.html">prepared statement</a> using <a href="c3ref/finalize.html">sqlite3_finalize()</a>. </li>
</ol>
<p>
The foregoing is all one really needs to know in order to use SQLite
effectively. All the rest is optimization and detail.
</p>
<h1 id="convenience_wrappers_around_core_routines"><span>5. </span>Convenience Wrappers Around Core Routines</h1>
<p>
The <a href="c3ref/exec.html">sqlite3_exec()</a> interface is a convenience wrapper that carries out
all four of the above steps with a single function call. A callback
function passed into <a href="c3ref/exec.html">sqlite3_exec()</a> is used to process each row of
the result set. The <a href="c3ref/free_table.html">sqlite3_get_table()</a> is another convenience wrapper
that does all four of the above steps. The <a href="c3ref/free_table.html">sqlite3_get_table()</a> interface
differs from <a href="c3ref/exec.html">sqlite3_exec()</a> in that it stores the results of queries
in heap memory rather than invoking a callback.
</p>
<p>
It is important to realize that neither <a href="c3ref/exec.html">sqlite3_exec()</a> nor
<a href="c3ref/free_table.html">sqlite3_get_table()</a> do anything that cannot be accomplished using
the core routines. In fact, these wrappers are implemented purely in
terms of the core routines.
</p>
<h1 id="binding_parameters_and_reusing_prepared_statements"><span>6. </span>Binding Parameters and Reusing Prepared Statements</h1>
<p>
In prior discussion, it was assumed that each SQL statement is prepared
once, evaluated, then destroyed. However, SQLite allows the same
<a href="c3ref/stmt.html">prepared statement</a> to be evaluated multiple times. This is accomplished
using the following routines:
</p>
<p></p><ul>
<li> <a href="c3ref/reset.html">sqlite3_reset()</a> </li>
<li> <a href="c3ref/bind_blob.html">sqlite3_bind()</a> </li>
</ul>
<p>
After a <a href="c3ref/stmt.html">prepared statement</a> has been evaluated by one or more calls to
<a href="c3ref/step.html">sqlite3_step()</a>, it can be reset in order to be evaluated again by a
call to <a href="c3ref/reset.html">sqlite3_reset()</a>.
Think of <a href="c3ref/reset.html">sqlite3_reset()</a> as rewinding the <a href="c3ref/stmt.html">prepared statement</a> program
back to the beginning.
Using <a href="c3ref/reset.html">sqlite3_reset()</a> on an existing <a href="c3ref/stmt.html">prepared statement</a> rather than
creating a new <a href="c3ref/stmt.html">prepared statement</a> avoids unnecessary calls to
<a href="c3ref/prepare.html">sqlite3_prepare()</a>.
For many SQL statements, the time needed
to run <a href="c3ref/prepare.html">sqlite3_prepare()</a> equals or exceeds the time needed by
<a href="c3ref/step.html">sqlite3_step()</a>. So avoiding calls to <a href="c3ref/prepare.html">sqlite3_prepare()</a> can give
a significant performance improvement.
</p>
<p>
It is not commonly useful to evaluate the <em>exact</em> same SQL
statement more than once. More often, one wants to evaluate similar
statements. For example, you might want to evaluate an INSERT statement
multiple times with different values. Or you might want to evaluate
the same query multiple times using a different key in the WHERE clause.
To accommodate
this, SQLite allows SQL statements to contain <a href="lang_expr.html#varparam">parameters</a>
which are "bound" to values prior to being evaluated. These values can
later be changed and the same <a href="c3ref/stmt.html">prepared statement</a> can be evaluated
a second time using the new values.
</p>
<p>
SQLite allows a <a href="lang_expr.html#varparam">parameter</a> wherever a string literal,
blob literal, numeric constant, or NULL is allowed
in queries or data modification statements. (DQL or DML)
(Parameters may not be used for column or table names,
or as values for constraints or default values. (DDL))
A <a href="lang_expr.html#varparam">parameter</a> takes one of the following forms:
</p>
<p></p><ul>
<li> <b>?</b> </li>
<li> <b>?</b><i>NNN</i> </li>
<li> <b>:</b><i>AAA</i> </li>
<li> <b>$</b><i>AAA</i> </li>
<li> <b>@</b><i>AAA</i> </li>
</ul>
<p>
In the examples above, <i>NNN</i> is an integer value and
<i>AAA</i> is an identifier.
A parameter initially has a value of NULL.
Prior to calling <a href="c3ref/step.html">sqlite3_step()</a> for the first time or immediately
after <a href="c3ref/reset.html">sqlite3_reset()</a>, the application can invoke the
<a href="c3ref/bind_blob.html">sqlite3_bind()</a> interfaces to attach values
to the parameters. Each call to <a href="c3ref/bind_blob.html">sqlite3_bind()</a>
overrides prior bindings on the same parameter.
</p>
<p>
An application is allowed to prepare multiple SQL statements in advance
and evaluate them as needed.
There is no arbitrary limit to the number of outstanding
<a href="c3ref/stmt.html">prepared statements</a>.
Some applications call <a href="c3ref/prepare.html">sqlite3_prepare()</a> multiple times at start-up to
create all of the <a href="c3ref/stmt.html">prepared statements</a> they will ever need. Other
applications keep a cache of the most recently used <a href="c3ref/stmt.html">prepared statements</a>
and then reuse <a href="c3ref/stmt.html">prepared statements</a> out of the cache when available.
Another approach is to only reuse <a href="c3ref/stmt.html">prepared statements</a> when they are
inside of a loop.
</p>
<h1 id="configuring_sqlite"><span>7. </span>Configuring SQLite</h1>
<p>
The default configuration for SQLite works great for most applications.
But sometimes developers want to tweak the setup to try to squeeze out
a little more performance, or take advantage of some obscure feature.
</p><p>
The <a href="c3ref/config.html">sqlite3_config()</a> interface is used to make global, process-wide
configuration changes for SQLite. The <a href="c3ref/config.html">sqlite3_config()</a> interface must
be called before any <a href="c3ref/sqlite3.html">database connections</a> are created. The
<a href="c3ref/config.html">sqlite3_config()</a> interface allows the programmer to do things like:
</p><ul>
<li>Adjust how SQLite does <a href="malloc.html">memory allocation</a>, including setting up
alternative memory allocators appropriate for safety-critical
real-time embedded systems and application-defined memory allocators.
</li><li>Set up a process-wide <a href="errlog.html">error log</a>.
</li><li>Specify an application-defined page cache.
</li><li>Adjust the use of mutexes so that they are appropriate for various
<a href="threadsafe.html">threading models</a>, or substitute an
application-defined mutex system.
</li></ul>
<p>
After process-wide configuration is complete and <a href="c3ref/sqlite3.html">database connections</a>
have been created, individual database connections can be configured using
calls to <a href="c3ref/limit.html">sqlite3_limit()</a> and <a href="c3ref/db_config.html">sqlite3_db_config()</a>.
</p><h1 id="extending_sqlite"><span>8. </span>Extending SQLite</h1>
<p>
SQLite includes interfaces that can be used to extend its functionality.
Such routines include:
</p>
<p></p><ul>
<li> <a href="c3ref/create_collation.html">sqlite3_create_collation()</a> </li>
<li> <a href="c3ref/create_function.html">sqlite3_create_function()</a> </li>
<li> <a href="c3ref/create_module.html">sqlite3_create_module()</a> </li>
<li> <a href="c3ref/vfs_find.html">sqlite3_vfs_register()</a> </li>
</ul>
<p>
The <a href="c3ref/create_collation.html">sqlite3_create_collation()</a> interface is used to create new
<a href="datatype3.html#collation">collating sequences</a> for sorting text.
The <a href="c3ref/create_module.html">sqlite3_create_module()</a> interface is used to register new
<a href="vtab.html">virtual table</a> implementations.
The <a href="c3ref/vfs_find.html">sqlite3_vfs_register()</a> interface creates new <a href="vfs.html">VFSes</a>.
</p>
<p>
The <a href="c3ref/create_function.html">sqlite3_create_function()</a> interface creates new SQL functions -
either scalar or aggregate. The new function implementation typically
makes use of the following additional interfaces:
</p>
<p></p><ul>
<li> <a href="c3ref/aggregate_context.html">sqlite3_aggregate_context()</a> </li>
<li> <a href="c3ref/result_blob.html">sqlite3_result()</a> </li>
<li> <a href="c3ref/user_data.html">sqlite3_user_data()</a> </li>
<li> <a href="c3ref/value_blob.html">sqlite3_value()</a> </li>
</ul>
<p>
All of the built-in SQL functions of SQLite are created using exactly
these same interfaces. Refer to the SQLite source code, and in particular
the
<a href="https://www.sqlite.org/src/doc/trunk/src/date.c">date.c</a> and
<a href="https://www.sqlite.org/src/doc/trunk/src/func.c">func.c</a> source files
for examples.
</p>
<p>
Shared libraries or DLLs can be used as <a href="loadext.html">loadable extensions</a> to SQLite.
</p><h1 id="other_interfaces"><span>9. </span>Other Interfaces</h1>
<p>
This article only mentions the most important and most commonly
used SQLite interfaces.
The SQLite library includes many other APIs implementing useful
features that are not described here.
A <a href="c3ref/funclist.html">complete list of functions</a> that form the SQLite
application programming interface is found at the
<a href="c3ref/intro.html">C/C++ Interface Specification</a>.
Refer to that document for complete and authoritative information about
all SQLite interfaces.
</p>
|
