Documentation Source Text

Check-in [85e3a73968]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Additional work on the C/C++ interface roadmap.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 85e3a7396830bb33147eb8dfac10ea798a4283a5
User & Date: drh 2008-05-10 15:45:30.000
Context
2008-05-10
16:22
Better hyperlinks. (check-in: 32b6f9cdf7 user: drh tags: trunk)
15:45
Additional work on the C/C++ interface roadmap. (check-in: 85e3a73968 user: drh tags: trunk)
2008-05-09
17:46
Begin working on an article that contains a introduction and roadmap for the C/C++ interface. (check-in: 3cd85c12aa user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/capi3ref.in.
334
335
336
337
338
339
340

341
342
343
344
345
346
347
    if {[regexp {^sqlite} $kw]} {
      lappend funclist [list $k $kw]
    }
  }
}
hd_open_aux c3ref/funclist.html
hd_header {List Of SQLite Functions}

hd_enable_main 0
hd_puts {<a href="intro.html"><h2>SQLite C Interface</h2></a>}
hd_enable_main 1
hd_puts {<h2>Functions:</h2>}
set funclist [lsort -index 1 $funclist]
output_list 3 $funclist
hd_enable_main 0







>







334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
    if {[regexp {^sqlite} $kw]} {
      lappend funclist [list $k $kw]
    }
  }
}
hd_open_aux c3ref/funclist.html
hd_header {List Of SQLite Functions}
hd_keywords {capi3ref_funclist}
hd_enable_main 0
hd_puts {<a href="intro.html"><h2>SQLite C Interface</h2></a>}
hd_enable_main 1
hd_puts {<h2>Functions:</h2>}
set funclist [lsort -index 1 $funclist]
output_list 3 $funclist
hd_enable_main 0
Changes to pages/cintro.in.
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
  This article aims to provide all of the background information needed to
  easily understand how SQLite works.
</p>

<p>
  A separate document, [capi3ref | The SQLite C/C++ Interface],
  provides detailed
  specifications for all of the various C/C++ APIs for SQLite.  Once you

  understand the basic principles of operation for SQLite you should
  begin using [capi3ref | that document] as your reference
  guide.  This article is intended as introduction only and is neither a
  complete or authoritative reference for the SQLite API.
</p>

<tcl>HEADING 1 {Core Objects And Interfaces}</tcl>

<p>
  The principal function of an SQL database engine is to evaluate statements
  of SQL.  In order to accomplish this in SQLite, one needs to know about
  two objects:
</p>

<p><ul>
  <li> The [sqlite3] database connection object </li>
  <li> The [sqlite3_stmt] prepared statement object </li>
</ul></p>

<p>
  Strictly speaking, the [prepared statement] object is not required since
  you can use one of the convenience wrapper interfaces, [sqlite3_exec] or
  [sqlite3_get_table], which encapsulate and hide the prepared statement.
  But for a full understand of how SQLite works, one really does need to
  know about prepared statements so they will be included in this discussion.
</p>

<p>
  The [database connection] and [prepared statement] objects are controlled
  by a small set of C/C++ interface routine listed below.
</p>

<p><ul>
  <li> [sqlite3_open()] </li>
  <li> [sqlite3_prepare()] </li>
  <li> [sqlite3_step()] </li>
  <li> [sqlite3_column_int | sqlite3_column()] </li>
  <li> [sqlite3_finalize()] </li>
  <li> [sqlite3_close()] </li>
</ul></p>

<p>
  To the list above we add a second tier of core interfaces as follows:
</p>

<p></ul>
  <li> [sqlite3_bind_int | sqlite3_bind()] </li>
  <li> [sqlite3_reset()] </li>
</ul></p>

<p>
  The 8 C/C++ interface routines and 2 objects listed above form the core
  functionality of SQLite.  If you understand what these APIs and objects
  do, you will have a good foundation for using SQLite.
</p>

<p>
  Note, however, that the list of 8 routines above is a
  simplification.  There are frequently multiple versions of each of
  the routines shown.  For example, the list above shows a single routine
  named [sqlite3_open()] when in fact there are three separate routines
  that accomplish the same thing in slightly different ways:
  [sqlite3_open()], [sqlite3_open16()] and [sqlite3_open_v2()].
  The list mentions [sqlite3_column_int | sqlite3_column()] and
  and [sqlite3_bind_int | sqlite3_bind()] when in fact no such routines
  exist.  Those entries in the list are place holders for entire families
  of routines to be used for various datatypes
  ([sqlite3_column_blob()], [sqlite3_column_text()], [sqlite3_column_int()],
  and so forth.)
  Do not be intimidated by this.  Focus for now on the 8 core routines
  listed above and later refer to the [capi3ref | C/C++ Interface Reference] 
  for details on the available variations of each interface.
</p>

<p>
  Here is what the core interfaces do:
</p>

<table border="0" cellspacing="15">

<tr><td valign="top">[sqlite3_open()]</td>
<td valign="top">

  Open a connection to an SQLite database file and return a
  [database connection] object.  This is often the first SQLite API
  call that an application makes and is a prerequisite for most other
  SQLite APIs.
</td>

<tr><td valign="top">[sqlite3_prepare()]</td>
<td valign="top">

  Convert SQL text into a [prepared statement] object and return a pointer
  to that object.  This interface requires a [database connection] pointer
  created by a prior call to [sqlite3_open()] 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.




</td>

<tr><td valign="top">[sqlite3_step()]</td>
<td valign="top">
  This routine is used to evaluate a [prepared statement] that has been
  previously created by the [sqlite3_prepare()] 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 [sqlite3_step()] again.
  Continue invoking [sqlite3_step()] 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 [sqlite3_step()].
</td>


<tr><td valign="top">[sqlite3_column_int | sqlite3_column()]</td>
<td valign="top">
  This routine returns a single column from the current row of a result
  set for a [prepared statement] that is being evaluated by [sqlite3_step()].
  Each time [sqlite3_step()] stops with a new result set row, this routine
  can be called multiple times to find the values of all columns in that row.
  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()"







|
>
|
|








|
|



|
|




|
|
|
|

















<
<
|
<
<
<
<
|
<
<
|
|



|
|
|



|
|
|
|
<
<
<
<
|



|




|

>
|





|

>
|




>
>
>
>


|











|







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
  This article aims to provide all of the background information needed to
  easily understand how SQLite works.
</p>

<p>
  A separate document, [capi3ref | The SQLite C/C++ Interface],
  provides detailed
  specifications for all of the various C/C++ APIs for SQLite.  Once
  the reader
  understands the basic principles of operation for SQLite, 
  [capi3ref | that document] should be used as a reference
  guide.  This article is intended as introduction only and is neither a
  complete or authoritative reference for the SQLite API.
</p>

<tcl>HEADING 1 {Core Objects And Interfaces}</tcl>

<p>
  The principal function of an SQL database engine is to evaluate statements
  of SQL.  In order to accomplish this in SQLite, the developer needs
  to know about two objects:
</p>

<p><ul>
  <li> The [database connection] object: sqlite3 </li>
  <li> The [prepared statement] object: sqlite3_stmt </li>
</ul></p>

<p>
  Strictly speaking, the [prepared statement] object is not required since
  the convenience wrapper interfaces, [sqlite3_exec] or
  [sqlite3_get_table], that encapsulate and hide the prepared statement
  can be used instead.  Nevertheless, and understanding of
  [prepared statements] is needed to make full use of SQLite.
</p>

<p>
  The [database connection] and [prepared statement] objects are controlled
  by a small set of C/C++ interface routine listed below.
</p>

<p><ul>
  <li> [sqlite3_open()] </li>
  <li> [sqlite3_prepare()] </li>
  <li> [sqlite3_step()] </li>
  <li> [sqlite3_column_int | sqlite3_column()] </li>
  <li> [sqlite3_finalize()] </li>
  <li> [sqlite3_close()] </li>
</ul></p>

<p>


  The six C/C++ interface routines and two objects listed above form the core




  functionality of SQLite.  The developer who understands


  what these APIs and objects
  do will have a good foundation for using SQLite.
</p>

<p>
  Note that the list of routines is conceptual rather than actual.
  Many of these routines come in multiple versions.
  For example, the list above shows a single routine
  named [sqlite3_open()] when in fact there are three separate routines
  that accomplish the same thing in slightly different ways:
  [sqlite3_open()], [sqlite3_open16()] and [sqlite3_open_v2()].
  The list mentions [sqlite3_column_int | sqlite3_column()]
  when in fact no such routine exists.
  The "sqlite3_column()" shown in the list is place holders for
  an entire families of routines to be used for extracting column




  data in various datatypes.
</p>

<p>
  Here is a summary of what the core interfaces do:
</p>

<table border="0" cellspacing="15">

<tr><td valign="top" align="right">[sqlite3_open()]</td>
<td valign="top">
  This routine 
  opens a connection to an SQLite database file and return a
  [database connection] object.  This is often the first SQLite API
  call that an application makes and is a prerequisite for most other
  SQLite APIs.
</td>

<tr><td valign="top" align="right">[sqlite3_prepare()]</td>
<td valign="top">
  This routine
  converts SQL text into a [prepared statement] object and return a pointer
  to that object.  This interface requires a [database connection] pointer
  created by a prior call to [sqlite3_open()] 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>Note that the use of [sqlite3_prepare()] is not recommended for new
  applications.  The alternative routine [sqlite3_prepare_v2()] should
  be used instead.</p>
</td>

<tr><td valign="top" align="right">[sqlite3_step()]</td>
<td valign="top">
  This routine is used to evaluate a [prepared statement] that has been
  previously created by the [sqlite3_prepare()] 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 [sqlite3_step()] again.
  Continue invoking [sqlite3_step()] 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 [sqlite3_step()].
</td>


<tr><td valign="top" align="right">[sqlite3_column_int | sqlite3_column()]</td>
<td valign="top">
  This routine returns a single column from the current row of a result
  set for a [prepared statement] that is being evaluated by [sqlite3_step()].
  Each time [sqlite3_step()] stops with a new result set row, this routine
  can be called multiple times to find the values of all columns in that row.
  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()"
191
192
193
194
195
196
197
198















199



























































































































































































    <li> [sqlite3_column_int64()] </li>
    <li> [sqlite3_column_text()] </li>
    <li> [sqlite3_column_text16()] </li>
    <li> [sqlite3_column_type()] </li>
    <li> [sqlite3_column_value()] </li>
  </ul></p>
</td>
















</table>



































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
    <li> [sqlite3_column_int64()] </li>
    <li> [sqlite3_column_text()] </li>
    <li> [sqlite3_column_text16()] </li>
    <li> [sqlite3_column_type()] </li>
    <li> [sqlite3_column_value()] </li>
  </ul></p>
</td>

<tr><td valign="top" align="right">[sqlite3_finalize()]</td>
<td valign="top">
  This routine destroys a [prepared statement] created by a prior call
  to [sqlite3_prepare()].  Every prepared statement must be destroyed using
  a call to this routine in order to avoid memory leaks.
</td>

<tr><td valign="top" align="right">[sqlite3_close()]</td>
<td valign="top">
  This routine closes a [database connection] previously opened by a call
  to [sqlite3_open()].  All [prepared statements] associated with the
  connection should be [sqlite3_finalize | finalized] prior to closing the
  connection.
</td>

</table>

<tcl>HEADING 2 {Typical Usage Of Core Routines And Objects}</tcl>

<p>
  An application that wants to use SQLite will typically use
  [sqlite3_open()] to create a single [database connection]
  during initialization.
  Note that [sqlite3_open()] can be used to either open existing database
  files or to create and open new database files.
  While many applications use only a single [database connection], there is
  no reason why an application cannot call [sqlite3_open()] multiple times
  in order to open multiple [database connections] - either to the same
  database or to different databases.  Sometimes a multi-threaded application
  will create separate [database connections] for each threads.
  Note too that is not necessary to open separate database connections in
  order to access two or more databases.  A single [database connection]
  can be made to access two or more databases at one time using the
  [ATTACH] SQL command.
</p>

<p>
  Many applications destroy their [database connections] using calls to
  [sqlite3_close()] at shutdown.  Or, for example, an application might
  open [database connections] in response to a File->Open menu action
  and then destroy the corresponding [database connection] in response
  to the File->Close menu.
</p>

<p>
  To run an SQL statement, the application follows these steps:
</p>

<p><ol>
  <li> Create a [prepared statement] using [sqlite3_prepare()]. </li>
  <li> Evaluate the [prepared statement] by calling [sqlite3_step()] one
       or more times. </li>
  <li> For queries, extract results by calling 
       [sqlite3_column_int | sqlite3_column()] in between
       two calls to [sqlite3_step()]. </li>
  <li> Destroy the [prepared statement] using [sqlite3_finalize()]. </li>
</ol></p>

<p>
  The foregoing is all one really needs to know in order to use SQLite
  effectively.  All the rest is just ornimentation and detail.
</p>

<tcl>HEADING 1 {Convenience Wrappers Around Core Routines}</tcl>

<p>
  The [sqlite3_exec()] interface is a convenience wrapper that carries out
  all four of the above steps with a single function call.  A callback
  function passed into [sqlite3_exec()] is used to process each row of
  the result set.  The [sqlite3_get_table()] is another convenience wrapper
  that does all four of the above steps.  The [sqlite3_get_table()] interface
  differs from [sqlite3_get_table()] in that it stores the results of queries
  in help memory rather than invoking a callback.
</p>

<p>
  It is important to realize that neither [sqlite3_exec()] nor
  [sqlite3_get_table()] do anything that cannot be accomplished using
  the core routines.  In fact, these wrappers are implemented purely in
  terms of the core routines.
</p>


<tcl>HEADING 1 {Binding Parameters and Reusing Prepared Statements}</tcl>

<p>
  In prior discussion, it was assumed that each SQL statement is prepared
  onces, evaluated, then destroyed.  However, the SQLite allows the same
  [prepared statement] to evaluated multiple times.  These is accomplished
  using the following routines:
</p>

<p><ul>
  <li> [sqlite3_reset()] </li>
  <li> [sqlite3_bind_int | sqlite3_bind()] </li>
</ul></p>

<p>
  After a [prepared statement] has been evaluated by one or more calls to
  [sqlite3_step()], it can be reset in order to be evaluted again by a
  single call to [sqlite3_reset()].
  Using [sqlite3_reset()] on an existing [prepared statement] rather
  creating a new [prepared statement] avoids unnecessary calls to
  [sqlite3_prepare()].
  In many SQL statements, the time needed
  to run [sqlite3_prepare()] equals or exceeds the time needed by
  [sqlite3_step()].  So avoiding calls to [sqlite3_prepare()] can result
  in a significant performance improvement.
</p>

<p>
  Usually, though, it is not useful to evaluate exactly the same SQL
  statement more than once.  More often, one wants to evalute similar
  statements.  For example, you might want to evaluate an INSERT statement
  multiple times though with different values to insert.  To accommodate
  this kind of flexibility, SQLite allows SQL statements to contain parameters
  which are "bound" to values prior to being evaluated.  These values can
  later be changed and the same [prepared statement] can be evaluated
  a second time using the new values.
</p>

<p>
  In SQLite, whereever it is valid to include a string literal, one can use
  a parameter in one of the following forms:
</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>

<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 [sqlite3_step()] for the first time or immediately
  after [sqlite3_reset()], the application can invoke one of the
  [sqlite3_bind_int | sqlite3_bind()] interfaces to attach values
  to the parameters.  Each call to [sqlite3_bind_int | sqlite3_bind()]
  overrides prior bindings on the same parameter.
</p>

<p>
  An application is allows to prepare multiple SQL statements in advance
  and evaluate them as needed.
  There is no arbitrary limit to the number of outstanding
  [prepared statements].
</p>

<tcl>HEADING 1 {Extending SQLite}</tcl>

<p>
  SQLite includes interface that can be used to extend its functionality.
  Such routines include:
</p>

<p><ul>
  <li> [sqlite3_create_collation()] </li>
  <li> [sqlite3_create_function()] </li>
  <li> [sqlite3_create_module()] </li>
</ul></p>

<p>
  The [sqlite3_create_collation()] interface is used to create new
  collating sequences for sorting text.
  The [sqlite3_create_module()] interface is used register new
  virtual table implementations.
</p>

<p>
  The [sqlite3_create_function()] interface creates new SQL functions - 
  either scalar or aggregate.  The new function implementation typically
  make use of the following additional interfaces:
</p>

<p><ul>
  <li> [sqlite3_aggregate_context()] </li>
  <li> [sqlite3_result_int | sqlite3_result()] </li>
  <li> [sqlite3_user_data()] </li>
  <li> [sqlite3_value_int | sqlite3_value()] </li>
</ul></p>

<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 <b>date.c</b> and <b>func.c</b> source files for examples.
</p>

<tcl>HEADING 1 {Other Interfaces}</tcl>

<p>
  This article only mentions the foundational SQLite interfaces.
  The SQLite library includes many other APIs implementing useful
  features that are not described here.  
  A [capi3ref_funclist | complete list of functions] that form the SQLite
  application program interface is found at the
  [capi3ref | C/C++ Interface Specification].
  Refer to that document for complete and authoritative information about
  all SQLite interfaces.
</p>