This article provides an overview and Getting Started Guide for using the SQLite C/C ++ interface.
Since the early stage of SQLite only supports five C/C ++ interfaces, it is very easy to learn and use. However, with the enhancement of the SQLite function, the new C/C ++ Interface
More than 150 different api interfaces have been added. This often makes beginners discouraged. Fortunately, most C/C ++ interfaces in SQLite are dedicated, so
Used. Despite so many calling interfaces, the core APIs are still relatively simple and easy to call. The purpose of this article is to provide basic knowledge for easier understanding of SQLite operations.
Another independent document titled The SQLite C/C ++
Interface provides detailed instructions for all C/C ++ interfaces in SQLite. Once the reader understands the basic operating principles of SQLite, this document should serve as a reference
Exam manual. This article is just an introduction to SQLite, rather than a complete and authoritative reference guide for SQLite APIs.
1.0 core objects and interfaces
The main task of the SQL database engine is to parse SQL statements. To achieve this goal, developers need to understand two objects:
* Database connection object: sqlite3 * preprocessing statement object: sqlite3_stmt
Strictly speaking, the pre-processing statement object is not necessary, because it can use sqlite_exec or sqlite3_get_table, which encapsulate and hide the pre-processing statement object. However, understanding the preprocessing object helps us to fully use SQLite.
Database Connection objects and pre-processing objects are manipulated by calling the following C/C ++ interfaces:
* Sqlite3_open () * sqlite3_prepare () * sqlite3_step () * sqlite3_column () * sqlite3_finalize () * sqlite3_close ()
These six C/C ++ interface routines and the above two objects constitute the core functions of SQLite. Developers can better use SQLite for their understanding.
Note that the list of interface routines is more conceptual than the actual interface. Many of these interfaces are available in different versions. For example
The sqlite3_open () Routine actually has three different interfaces to implement the same function in a slightly different way.
Yes: slqite3_open (), sqlite3_open16 () and sqlite3_open_v2 (). The list does not actually exist.
Sqlite3_column. "Sqlite3_column ()" displayed in the list is only a placeholder, indicating a complete set of data classes used to query a table
Column Record interface.
The main functions of core interfaces are described as follows:
* Sqlite3_open ()
This interface opens the connection to an SQLite database file and returns a database connection object. This is usually the first SQLite called by the application.
The API also calls other SQLite
The interface that needs to be called before the API. Many SQLite interfaces require a pointer to the database connection object as their first parameter. Therefore, these interfaces can also be understood as database connection objects.
Operation interface. This interface creates such a database connection object.
* Sqlite3_prepare ()
This interface converts an SQL statement text into a preprocessing statement object and returns a pointer to the object. This interface requires a database returned by calling sqlite3_open ().
The connection object pointer and a pre-processed SQL statement text string are parameters. This API does not actually parse SQL statements, but only pre-processes SQL statements for subsequent parsing.
Note that sqlite3_prepare () is not recommended for new applications, but another interface sqlite3_prepare_v2 () should be used ().
* Sqlite3_step ()
This interface is used to parse a pre-processing statement previously created through the sqlite3_prepare () interface until the first column of results is returned. Call again
Sqlite3_step () can return the results of the next column and continuously call sqlite3_step () until the entire statement is completed. For statements that do not return results (for example
For example, the insert, update, and delete statements call sqlite3_step () to complete the statement processing.
* Sqlite3_column ()
This interface returns a column of data in the current row in the result set of A preprocessing statement parsed by sqlite3_step. Each execution of sqlite3_step () returns a new result set.
. You can call sqlite3_column () multiple times to return the data of all columns in that row. As mentioned above, SQLite
The API does not contain interfaces such as sqlite3_column. Instead, a group of function interfaces are used to query various data types of each column item from the result set. In this set of function Interfaces
, Some interfaces return the size of the result set, and some return the number of columns in the result set.
* Aggregate () * sqlite3_column_count () * sqlite3_column_double () * Aggregate () * sqlite3_column_text () * Aggregate () * sqlite3_column_type () * sqlite3_column_value ()
* Sqlite3_finalize () This interface destroys the preprocessing statements created by calling sqlite3_prepare. Each Pre-processing statement must call this interface to destroy it to avoid Memory leakage.
* Sqlite3_close () This interface closes a database connection created by calling sqlite3_open. All preprocessing statements related to the connection must be destroyed before the connection is closed.
1.1 typical use of core functions and objects
Any application that uses the SQLite database typically calls the sqlite3_open () interface during initialization to create a database connection. Note that
Sqlite3_open () can be used to open an existing database file or create and open a new database file. Although many applications only use one database connection
There is no reason not to allow an application to call sqlite3_open () multiple times to create multiple data connections for the same or different databases. Sometimes a multi-threaded application creates
Independent database connection. Note that there is no need to create multiple independent database connections to access two or more databases. Attach
The SQL command can create a database connection that accesses two or more databases at the same time.
Many applications call the sqlite3_close () interface when exiting to destroy their database connections. For example, an application opens a database connection when it responds to a file-> opens a menu item operation, and destroys the corresponding database connection when it responds to the file-> closes the menu item operation.
An application can execute an SQL statement by performing the following steps:
(1) Use sqlite3_prepare () to create a preprocessing statement. (2) Call sqlite3_step () repeatedly to parse and execute the preprocessing statement. (3) For the query operation, the sqlite3_step () interface is called twice to query the returned results through the sqlite3_column () interface. (4) Finally, use sqlite3_finalize () to destroy the preprocessing statement.
The above is the knowledge required to effectively use SQLite, and the rest is just the details that need to be supplemented.
2.0 convenient encapsulation of core functions
The sqlite3_exec () interface is a convenient call encapsulation interface for the application that executes the preceding four steps and is passed to the callback function of sqlite3_exec ().
Number is used to process the result set returned by each column. The sqlite3_get_table () interface is also another encapsulation interface with the same functions, which is different from sqlite3_exec ().
This interface saves the returned results of the query in the memory heap instead of calling a callback function to process the returned results.
Note that the sqlite3_exec () and sqlite3_get_table () interfaces do not implement more functions than the core functions. In fact, these encapsulated interfaces are fully implemented by these core interfaces.
3.0 parameter binding and preprocessing statement Reuse
In the previous discussion, each time an SQL statement is executed, it is processed, parsed, and destroyed. However, SQLite allows the same pre-processing statement to be parsed and executed multiple times. This can be achieved through the following interface:
* Sqlite3_reset () * sqlite3_bind ()
After you call sqlite3_step () to parse a pre-processing statement, you can call sqlite3_reset ()
Set this preprocessing statement and continue parsing and execution. Call sqlite3_reset () to reset an existing preprocessing statement instead of creating a new one.
Sqlite3_prepare () is not required. In many SQL statements, the execution time of sqlite3_prepare () is equal to or even exceeds
Sqlite3_step () execution time. Therefore, avoiding frequent calls to sqlite3_prepare () can greatly improve execution efficiency and performance.
However, repeated execution of the same SQL statement has no practical significance. A more common case is to execute similar statements repeatedly. For example, you may want to execute
Insert statements insert different data into the table. To adapt to the flexibility of this application, SQLite allows SQL statements to include parameters and bind them with actual values before parsing execution. In this way, when
After the bound value changes, the same pre-processing statement bound to the new value is parsed and executed again.
SQLite contains a String constant anywhere. You can use any of the following parameters:
*? *? Nnn *: AAA * $ AAA * @ aaa
In the preceding example, nnn is an integer and AAA is an identifier. The initialization value of a parameter is null. In the first call
Before sqlite3_step () or after sqlite3_reset (), an application can call any interface in the sqlite3_bind () function to bind
Parameters and actual values. Each call to sqlite3_bind () overwrites the value previously bound to this parameter.
An application allows you to prepare and execute multiple pre-executed SQL statements as needed. There is no special limit on the number of pre-processed statements.
4.0 extended SQLite
SQLite contains some other interfaces that can be used to extend its functions. These interfaces include:
* Sqlite3_create_collation () * sqlite3_create_function () * sqlite3_create_module ()
Sqlite3_create_collation () interface is used to create a new control sequence for the index text. Sqlite3_create_module () interface is used to register a new table resumption interface.
Sqlite3_create_function () interface to create a new SQL function-a single or combined interface. New Function implementations usually use the following auxiliary interfaces:
* Sqlite3_aggregate_context () * sqlite3_result () * sqlite3_user_data () * sqlite3_value ()
All built-in SQL function interfaces in SQLite are implemented through these same interfaces. Check SQLite source code, especially the date. C and func. c files.
5.0 other interfaces
This article only mentions the basic SQLite function interface. The SQLite library contains many other useful features of the API implementation, but it is not described here. End
The entire SQLite Application Programming Interface documentation can be found in C/C ++ Interface
Specification found. You can refer to this document for a complete and authoritative introduction to the SQLite interface.
