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: |
4dfdc948eff2479441d661fdb57ddd50 |
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
Changes to pages/cintro.in.
︙ | ︙ | |||
101 102 103 104 105 106 107 | a string of one or more SQL statements.</td> </tr> </table> <tcl>HEADING 1 {Introduction}</tcl> <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 | <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> | < < < < < < | 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 | 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. | > | | | > | | 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 | </td> </table> <tcl>HEADING 2 {Typical Usage Of Core Routines And Objects}</tcl> <p> | | < | < | > | 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 | [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 | | | 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 | </p> <tcl>HEADING 1 {Binding Parameters and Reusing Prepared Statements}</tcl> <p> In prior discussion, it was assumed that each SQL statement is prepared | | > > | | | | > > | | < > | > | | > > > > > > | 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 |
︙ | ︙ |