SQL入门

2014年3月15日

An Introduction To The SQLite C/C++ Interface

An Introduction To The SQLiteC/C++ Interface

This article provides an overview to the C/C++ interface to SQLite.

Early versions of SQLite were very easy to learn since they only supported 5C/C++ interfaces. But as SQLite has grown in capability, new C/C++ interfaceshave been added so that now there are over 200 distinct APIs. This can beoverwhelming to a new programmer. Fortunately, most of the C/C++ interfacesin SQLite are very specialized and never need to be considered. Despite havingso many entry points, the core API is still relatively simple and easy to code to.This article aims to provide all of the background information needed to easilyunderstand how SQLite works.

A separate document,The SQLite C/C++ Interface, provides detailedspecifications for all of the various C/C++ APIs for SQLite. Once the readerunderstands the basic principles of operation for SQLite,that documentshouldbe used as a reference guide. This article is intended as introduction only and isneither a complete nor authoritative reference for the SQLite API.

1.0 Core Objects And Interfaces

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 twoobjects:

The database connectionobject: sqlite3
The prepared statementobject: sqlite3_stmt

Strictly speaking, theprepared statementobject is not required since theconvenience wrapper interfaces,sqlite3_execor sqlite3_get_table, can be usedand these convenience wrappers encapsulate and hide the prepared statementobject. Nevertheless, an understanding of prepared statementsis needed tomake full use of SQLite.

The database connectionand prepared statementobjects are controlled by asmall set of C/C++ interface routine listed below.

sqlite3_open()

Small. Fast. Reliable.Choose any three.

AboutDownload

SitemapLicense

DocumentationNews Support

Search SQLite Docs...

Go

http://w w w .sqlite .or g/c intr o.html 1/6

2014年3月15日

sqlite3_prepare()sqlite3_step()sqlite3_column()sqlite3_finalize()sqlite3_close()

The six C/C++ interface routines and two objects listed above form the corefunctionality of SQLite. The developer who understands them will have a goodfoundation for using SQLite.

Note that the list of routines above is conceptual rather than actual. Many ofthese routines come in multiple versions. For example, the list above shows asingle routine namedsqlite3_open()when in fact there are three separateroutines that accomplish the same thing in slightly different ways:sqlite3_open(),sqlite3_open16()and sqlite3_open_v2(). The list mentionssqlite3_column()when in fact no such routine exists. The "sqlite3_column()"shown in the list is place holders for an entire family of routines to be used forextracting column data in various datatypes.

Here is a summary of what the core interfaces do:

An Introduction To The SQLite C/C++ Interface

sqlite3_open()

This routine opens a connection to an SQLite databasefile and returns adatabase connectionobject. This isoften the first SQLite API call that an application makesand is a prerequisite for most other SQLite APIs. ManySQLite interfaces require a pointer to thedatabaseconnectionobject as their first parameter and can bethought of as methods on thedatabase connectionobject. This routine is the constructor for thedatabaseconnectionobject.

This routine converts SQL text into aprepared statementobject and returns a pointer to that object. This interfacerequires a database connectionpointer created by a priorcall tosqlite3_open()and a text string containing the SQLstatement to be prepared. This API does not actuallyevaluate the SQL statement. It merely prepares the SQLstatement for evaluation.

Think of each SQL statement as a small computerprogram. The purpose ofsqlite3_prepare()is to compilethat program into object code. Theprepared statementis the object code. Thesqlite3_step()interface then runsthe object code to get a result.

Note that the use ofsqlite3_prepare()is notrecommended for new applications. A newer alternativeroutinesqlite3_prepare_v2()should be used instead.

This routine is used to evaluate aprepared statementthat has been previously created by thesqlite3_prepare()

sqlite3_prepare()

sqlite3_step()

http://w w w .sqlite .or g/c intr o.html

2/6

2014年3月15日

An Introduction To The SQLite C/C++ Interface

interface. The statement is evaluated up to the pointwhere the first row of results are available. To advanceto the second row of results, invokesqlite3_step()again.Continue invokingsqlite3_step()until the statement iscomplete. Statements that do not return results (ex:INSERT, UPDATE, or DELETE statements) run tocompletion on a single call tosqlite3_step().

This routine returns a single column from the current rowof a result set for aprepared statementthat is beingevaluated bysqlite3_step(). Each timesqlite3_step()stops with a new result set row, this routine can be calledmultiple times to find the values of all columns in thatrow. 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 foran entire family of functions that return a value from theresult set in various data types. There are also routinesin this family that return the size of the result (if it is astring or BLOB) and the number of columns in the resultset.

sqlite3_column_blob()sqlite3_column_bytes()sqlite3_column_bytes16()sqlite3_column_count()sqlite3_column_double()sqlite3_column_int()sqlite3_column_int64()sqlite3_column_text()sqlite3_column_text16()sqlite3_column_type()sqlite3_column_value()

This routine destroys aprepared statementcreated by aprior call tosqlite3_prepare(). Every prepared statementmust be destroyed using a call to this routine in order toavoid memory leaks.

This routine closes adatabase connectionpreviouslyopened by a call tosqlite3_open(). Allpreparedstatementsassociated with the connection should befinalizedprior to closing the connection.

sqlite3_column()

sqlite3_finalize()

sqlite3_close()

1.1 Typical Usage Of Core Routines And Objects

An application that wants to use SQLite will typically usesqlite3_open()to createa singledatabase connectionduring initialization. Note thatsqlite3_open()canbe used to either open existing database files or to create and open newdatabase files. While many applications use only a singledatabase connection,

http://w w w .sqlite .or g/c intr o.html 3/6

2014年3月15日An Introduction To The SQLite C/C++ Interface

there is no reason why an application cannot callsqlite3_open()multiple times inorder to open multipledatabase connections­ either to the same database or todifferent databases. Sometimes a multi­threaded application will create separatedatabase connections for each threads. Note too that is not necessary to openseparate database connections in order to access two or more databases. Asingledatabase connectioncan be made to access two or more databases atone time using theATTACHSQL command.

Many applications destroy theirdatabase connectionsusing calls tosqlite3_close()at shutdown. Or, for example, an application might opendatabase connectionsin response to a File­>Open menu action and then destroythe correspondingdatabase connectionin response to the File­>Close menu.

To run an SQL statement, the application follows these steps:

1. Create a prepared statementusing sqlite3_prepare().

2. Evaluate the prepared statementby calling sqlite3_step()one or more

   times.

3. For queries, extract results by calling sqlite3_column()in between two calls

   to sqlite3_step().

4. Destroy the prepared statementusing sqlite3_finalize().

The foregoing is all one really needs to know in order to use SQLite effectively.All the rest is just ornamentation and detail.

2.0 Convenience Wrappers Around CoreRoutines

The sqlite3_exec()interface is a convenience wrapper that carries out all four ofthe above steps with a single function call. A callback function passed intosqlite3_exec()is used to process each row of the result set. Thesqlite3_get_table()is another convenience wrapper that does all four of theabove steps. Thesqlite3_get_table()interface differs fromsqlite3_exec()in thatit stores the results of queries in heap memory rather than invoking a callback.

It is important to realize that neithersqlite3_exec()nor sqlite3_get_table()doanything that cannot be accomplished using the core routines. In fact, thesewrappers are implemented purely in terms of the core routines.

3.0 Binding Parameters and Reusing PreparedStatements

In prior discussion, it was assumed that each SQL statement is prepared once,evaluated, then destroyed. However, the SQLite allows the samepreparedstatementto be evaluated multiple times. This is accomplished using thefollowing routines:

sqlite3_reset()sqlite3_bind()

http://w w w .sqlite .or g/c intr o.html 4/6

2014年3月15日An Introduction To The SQLite C/C++ Interface

After a prepared statementhas been evaluated by one or more calls tosqlite3_step(), it can be reset in order to be evaluated again by a call tosqlite3_reset(). Usingsqlite3_reset()on an existing prepared statementratherthan creating a newprepared statementavoids unnecessary calls tosqlite3_prepare(). In many SQL statements, the time needed to runsqlite3_prepare()equals or exceeds the time needed bysqlite3_step(). Soavoiding calls tosqlite3_prepare()can result in a significant performanceimprovement.

Usually, though, it is not useful to evaluate exactly the same SQL statementmore than once. More often, one wants to evaluate similar statements. Forexample, you might want to evaluate an INSERT statement multiple timesthough with different values to insert. To accommodate this kind of flexibility,SQLite allows SQL statements to containparameterswhich are "bound" tovalues prior to being evaluated. These values can later be changed and thesameprepared statementcan be evaluated a second time using the new values.

In SQLite, wherever it is valid to include a string literal, one can use aparameterin one of the following forms:

??NNN:AAA$AAA@AAA

In the examples above,NNN is an integer value andAAA is an identifier. Aparameter initially has a value of NULL. Prior to callingsqlite3_step()for the firsttime or immediately aftersqlite3_reset(), the application can invoke one of thesqlite3_bind()interfaces to attach values to the parameters. Each call tosqlite3_bind()overrides prior bindings on the same parameter.

An application is allowed to prepare multiple SQL statements in advance andevaluate them as needed. There is no arbitrary limit to the number ofoutstandingprepared statements.

4.0 Configuring SQLite

The default configuration for SQLite works great for most applications. Butsometimes developers want to tweak the setup to try to squeeze out a littlemore performance, or take advantage of some obscure feature.

The sqlite3_config()interface is used to make global, process­wide configurationchanges for SQLite. Thesqlite3_config()interface must be called before anydatabase connectionsare created. Thesqlite3_config()interface allows theprogrammer to do things like:

Adjust how SQLite doesmemory allocation, including setting up alternativememory allocators appropriate for safety­critical real­time embeddedsystems and application­defined memory allocators.
Set up a process­wide error log.

Specify an application­defined page cache.

http://w w w .sqlite .or g/c intr o.html 5/6

2014年3月15日An Introduction To The SQLite C/C++ Interface
Adjust the use of mutexes so that they are appropriate for various

threading models, or substitute an application­defined mutex system.

After process­wide configuration is complete anddatabase connectionshavebeen created, individual database connections can be configured using calls tosqlite3_limit()and sqlite3_db_config().

5.0 Extending SQLite

SQLite includes interfaces that can be used to extend its functionality. Suchroutines include:

sqlite3_create_collation()sqlite3_create_function()sqlite3_create_module()sqlite3_vfs_register()

The sqlite3_create_collation()interface is used to create newcollating sequencesfor sorting text. Thesqlite3_create_module()interface is used to register newvirtual tableimplementations. Thesqlite3_vfs_register()interface creates newVFSes.

The sqlite3_create_function()interface creates new SQL functions ­ either scalaror aggregate. The new function implementation typically makes use of thefollowing additional interfaces:

sqlite3_aggregate_context()sqlite3_result()sqlite3_user_data()sqlite3_value()

All of the built­in SQL functions of SQLite are created using exactly these sameinterfaces. Refer to the SQLite source code, and in particular thedate.candfunc.csource files for examples.

Shared libraries or DLLs can be used asloadable extensionsto SQLite.

6.0 Other Interfaces

This article only mentions the foundational SQLite interfaces. The SQLite libraryincludes many other APIs implementing useful features that are not describedhere. Acomplete list of functionsthat form the SQLite application programminginterface is found at theC/C++ Interface Specification. Refer to that documentfor complete and authoritative information about all SQLite interfaces.

http://w w w .sqlite .or g/c intr o.html 6/6 

2014年3月15日

Query Language Understood by SQLite

AboutDownload

SitemapLicense

DocumentationNews Support

SQL As Understood By SQLite

SQLite understands most of the standard SQL language. But it does omit somefeatures while at the same time adding a few features of its own. This documentattempts to describe precisely what parts of the SQL language SQLite does and doesnot support. A list of SQL keywords is also provided. The SQL language syntax isdescribed by syntax diagrams.

The following syntax documentation topics are available:

Small. Fast. Reliable.Choose any three.

aggregate functions
ALTER TABLE
ANALYZE
ATTACH DATABASE
BEGIN TRANSACTION DROP INDEX REPLACE

date and time ON CONFLICT clause

functions
DELETE
DETACH DATABASE

PRAGMA
REINDEX
RELEASE SAVEPOINT

comment
COMMIT DROP TRIGGER
TRANSACTION DROP VIEW SAVEPOINT

DROP TABLE

ROLLBACKTRANSACTION

core functions
CREATE INDEX EXPLAIN UPDATE

END TRANSACTION SELECT

CREATE TABLE
CREATE TRIGGER
CREATE VIEW INSERTCREATE VIRTUAL keywordsTABLE

expression
INDEXED BY WITH clause

VACUUM

The routines sqlite3_prepare_v2(), sqlite3_prepare(), sqlite3_prepare16(),sqlite3_prepare16_v2(), sqlite3_exec(), and sqlite3_get_table() accept an SQLstatement list (sql­stmt­list) which is a semicolon­separated list of statements.

sql­stmt­list:

Each SQL statement in the statement list is an instance of the following:

sql­stmt:

Search SQLite Docs...

Go

http://w w w .sqlite .or g/la ng.html 1/2