Documentation Source Text

Check-in [4dfdc948ef]
Login

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

Overview
Comment:Improvements to the cintro.html document.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4dfdc948eff2479441d661fdb57ddd500491bed9
User & Date: drh 2015-04-14 13:24:24.975
Context
2015-04-14
15:14
Update the API decoder to identify and cross-reference methods on objects. (check-in: e9c7eaba3b user: drh tags: trunk)
13:24
Improvements to the cintro.html document. (check-in: 4dfdc948ef user: drh tags: trunk)
2015-04-13
10:21
Fix a typo on the quickstart page. (check-in: 80577829a2 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/cintro.in.
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
a string of one or more SQL statements.</td>
</tr>
</table>

<tcl>HEADING 1 {Introduction}</tcl>

<p>
  Early versions of SQLite were very easy to learn since they only
  supported five C/C++ interfaces.  But as SQLite has grown in capability,
  new C/C++ interfaces have been added so that now there
  are over 200 distinct APIs.  This can be overwhelming to a new programmer.
  Fortunately, most of the C/C++ interfaces in SQLite are very specialized
  and never need to be considered.  Despite having so many
  entry points, the core API is still simple to learn and easy to code to.
  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 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 nor authoritative reference for the SQLite API.
</p>

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

<p>
  The principal task of an SQL database engine is to evaluate statements
  of SQL.  In order to accomplish this purpose, 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>








|
<
<
|
|
|
|
|
<
















|
|
<







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
a string of one or more SQL statements.</td>
</tr>
</table>

<tcl>HEADING 1 {Introduction}</tcl>

<p>
  SQLite currently has over 200 distinct APIs.


  This can be overwhelming to a new programmer.
  Fortunately, most of the interfaces are very specialized
  and need not be considered by beginners.
  The core API is small, simple, and easy to learn.
  This article summarizes the core API.

</p>

<p>
  A separate document, [capi3ref | The SQLite C/C++ Interface],
  provides detailed
  specifications for all 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 nor authoritative reference for the SQLite API.
</p>

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

<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><ul>
  <li> The [database connection] object: sqlite3 </li>
  <li> The [prepared statement] object: sqlite3_stmt </li>
</ul></p>

159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
  <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 them
  will have a good foundation for using SQLite.
</p>

<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 [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()].







<
<
<
<
<
<







155
156
157
158
159
160
161






162
163
164
165
166
167
168
  <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>
  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 [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()].
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
  the SQL statement.  It merely prepares the SQL statement for evaluation.

  <p>Think of each SQL statement as a small computer program.  The purpose
  of [sqlite3_prepare()] is to compile that program into object code.
  The [prepared statement] is the object code.  The [sqlite3_step()] interface
  then runs the object code to get a result.


  <p>Note that the use of [sqlite3_prepare()] is not recommended for new
  applications.  A newer 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()"
  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><ul>







>
|
|
|




















>
|







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
  the SQL statement.  It merely prepares the SQL statement for evaluation.

  <p>Think of each SQL statement as a small computer program.  The purpose
  of [sqlite3_prepare()] is to compile that program into object code.
  The [prepared statement] is the object code.  The [sqlite3_step()] interface
  then runs the object code to get a result.

  <p>New applications should always invoke [sqlite3_prepare_v2()] instead
  of [sqlit3_prepare()].  The older [sqlite3_prepare()] is retained for
  backwards compatibility.  But [sqlite3_prepare_v2()] provides a much
  better interface.</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.
  
  <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><ul>
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
</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 that
  uses SQLite as its [application file format] might
  open [database connections] in response to a File/Open menu action







|









<
|
<
|
>







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
</td>

</table>

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

<p>
  An application 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 that a single [database connection] can access two or more

  databases using the [ATTACH] SQL command, so it is not necessary to
  have a separate database connection for each database file.
</p>

<p>
  Many applications destroy their [database connections] using calls to
  [sqlite3_close()] at shutdown.  Or, for example, an application that
  uses SQLite as its [application file format] might
  open [database connections] in response to a File/Open menu action
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
       [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 ornamentation 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







|







308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
       [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 optimization 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
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
</p>


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

<p>
  In prior discussion, it was assumed that each SQL statement is prepared
  once, evaluated, then destroyed.  However, the SQLite allows the same
  [prepared statement] to be evaluated multiple times.  This 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 evaluated again by a
  call to [sqlite3_reset()].


  Using [sqlite3_reset()] on an existing [prepared statement] rather than
  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 evaluate 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
  [parameter | 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, wherever 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 allowed 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 {Configuring SQLite}</tcl>

<p>
  The default configuration for SQLite works great for most applications.
  But sometimes developers want to tweak the setup to try to squeeze out







|













>
>



|

|
|



|


>
>
|
|
<






>
|
>
|















|










>
>
>
>
>
>







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
</p>


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

<p>
  In prior discussion, it was assumed that each SQL statement is prepared
  once, evaluated, then destroyed.  However, SQLite allows the same
  [prepared statement] to be evaluated multiple times.  This 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 evaluated again by a
  call to [sqlite3_reset()].
  Think of [sqlite3_reset()] as rewinding the [prepared statement] program
  back to the beginning.
  Using [sqlite3_reset()] on an existing [prepared statement] rather than
  creating a new [prepared statement] avoids unnecessary calls to
  [sqlite3_prepare()].
  For 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 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 [parameter | 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>
  SQLite allows a [parameter] wherever
  a string literal, numeric constant, or NULL is allowed.
  (Parameters may not be used for column or table names.)
  A [parameter] takes 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 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 allowed to prepare multiple SQL statements in advance
  and evaluate them as needed.
  There is no arbitrary limit to the number of outstanding
  [prepared statements].
  Some applications call [sqlite3_prepare()] multiple times at start-up to
  create all of the [prepared statements] they will ever need.  Other
  applications keep a cache of the most recently used [prepared statements]
  and then reuse [prepared statements] out of the cache when available.
  Another approach is to only reuse [prepared statements] when they are
  inside of a loop.
</p>

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

<p>
  The default configuration for SQLite works great for most applications.
  But sometimes developers want to tweak the setup to try to squeeze out