from:http://www.sqlite.org/cintro.html

An Introduction To The sqlite C/C++ Interface

This article provides an overview and roadmap to the C/C++ interface to sqlite.

Early versions of sqlite were very easy to learn since they only supported 5 C/C++ interfaces. But as sqlite has grown in capability,new C/C++ interfaces have been added so that now there are over 185 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 used. Despite having so 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 easily understand how sqlite works.

A separate document,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,100)" rel="nofollow" target="_blank">that documentshould 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.

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 two objects:

• Thedatabase connectionobject: sqlite3
• Theprepared statementobject: sqlite3_stmt

Strictly speaking,theprepared statementobject is not required since the convenience wrapper interfaces,sqlite3_execorsqlite3_get_table,can be used and these convenience wrappers encapsulate and hide theprepared statementobject. Nevertheless,an understanding ofprepared statementsis needed to make full use of sqlite.

Thedatabase connectionandprepared statementobjects are controlled by a small set of C/C++ interface routine listed below.

• sqlite3_open()
• sqlite3_prepare()
• sqlite3_step()
• sqlite3_column()
• sqlite3_finalize()
• sqlite3_close()

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.

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 namedsqlite3_open()when in fact there are three separate routines that accomplish the same thing in slightly different ways:sqlite3_open(),sqlite3_open16()andsqlite3_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 for extracting column data in varIoUs datatypes.

Here is a summary of what the core interfaces do:

1.1 Typical Usage Of Core Routines And Objects

An application that wants to use sqlite will typically usesqlite3_open()to create a singledatabase connectionduring initialization. Note thatsqlite3_open()can be used to either open existing database files or to create and open new database files. While many applications use only a singledatabase connection,there is no reason why an application cannot callsqlite3_open()multiple times in order to open multipledatabase connections- either to the same database or to different databases. Sometimes a multi-threaded application will create separatedatabase connectionsfor each threads. Note too that is not necessary to open separate database connections in order to access two or more databases. A singledatabase connectioncan be made to access two or more databases at one 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 destroy the correspondingdatabase connectionin response to the File->Close menu.

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

1. Create aprepared statementusingsqlite3_prepare().
2. Evaluate theprepared statementby callingsqlite3_step()one or more times.
3. For queries,extract results by callingsqlite3_column()in between two calls tosqlite3_step().
4. Destroy theprepared statementusingsqlite3_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 Core Routines

Thesqlite3_exec()interface is a convenience wrapper that carries out all four of the 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 the above steps. Thesqlite3_get_table()interface differs fromsqlite3_exec()in that it stores the results of queries in heap memory rather than invoking a callback.

It is important to realize that neithersqlite3_exec()norsqlite3_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.

3.0 Binding Parameters and Reusing Prepared Statements

In prior discussion,it was assumed that each sql statement is prepared once,evaluated,then destroyed. However,the sqlite allows the sameprepared statementto be evaluated multiple times. This is accomplished using the following routines:

• sqlite3_reset()
• sqlite3_bind()

After aprepared 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 existingprepared statementrather than 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(). So avoiding calls tosqlite3_prepare()can result in a significant performance improvement.

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 containparameterswhich are "bound" to values prior to being evaluated. These values can later be changed and the sameprepared 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,NNNis an integer value andAAAis an identifier. A parameter initially has a value of NULL. Prior to callingsqlite3_step()for the first time 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 and evaluate them as needed. There is no arbitrary limit to the number of outstandingprepared statements.

4.0 Extending sqlite

sqlite includes interfaces that can be used to extend its functionality. Such routines include:

• sqlite3_create_collation()
• sqlite3_create_function()
• sqlite3_create_module()

Thesqlite3_create_collation()interface is used to create new collating sequences for sorting text. Thesqlite3_create_module()interface is used to register new virtual table implementations.

Thesqlite3_create_function()interface creates new sql functions - either scalar or aggregate. The new function implementation typically makes use of the following 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 same interfaces. Refer to the sqlite source code,and in particular thedate.candfunc.csource files for examples.

5.0 Other Interfaces

This article only mentions the foundational sqlite interfaces. The sqlite library includes many other APIs implementing useful features that are not described here. Acomplete list of functionsthat form the sqlite application programming interface is found at theC/C++ Interface Specification. Refer to that document for complete and authoritative information about all sqlite interfaces.