SQLite tutorial (II): C/C ++ interface introduction, sqlite tutorial

SQLite tutorial (II): C/C ++ interface introduction, sqlite tutorial

I. Overview:

Five of the C/C ++ interfaces provided by SQLite are core interfaces. In this blog, we will mainly introduce their usage and the core SQLite objects they involve, such as database_connection and prepared_statement. Compared with APIs provided by other database engines, such as OCI and MySQL APIs, the APIs provided by SQLite are very easy to understand and master.

Ii. core objects and interfaces:

1. Core objects:
The two most important objects in SQLite are database_connection and prepared_statement. The database_connection object is created and returned by the sqlite3_open () interface function. before using any other SQLite interface function, you must call this function to obtain the database_connnection object, in other subsequent APIs calls, the object must be used as an input parameter to complete the corresponding work. As for prepare_statement, we can simply regard it as the compiled SQL statement. Therefore, all functions related to SQL statement execution also need this object as an input parameter to complete the specified SQL operation.

2. Core interfaces:
1). sqlite3_open
As mentioned above, this function is the entry function for operating the SQLite database. The database_connection object returned by this function is a handle parameter of many other SQLite APIs. Note: You can use this function to open existing database files or create new database files. For the database_connection object returned by this function, we can share the pointer of this object among multiple threads to complete any database-related operations. However, in the case of multithreading, we recommend that you create an independent database_connection object for each thread. This function also requires additional instructions. We do not need to create multiple database connection objects to access multiple databases, the ATTACH Command provided by SQLite allows you to easily access multiple databases in one connection.

2). sqlite3_prepare
This function converts SQL text to a prepared_statement object and returns a pointer to this object after the function is executed. In fact, this function does not evaluate the parameter-specified SQL statement. It only initializes the SQL text to the State to be executed. Finally, we can use the sqlite3_prepare_v2 interface function in place of the new application to complete the same work.

3). sqlite3_step
Evaluate the prepared_statement object returned by the sqlite3_prepare function. After the function is executed, the internal pointer of the prepared_statement object points to the first row of the returned result set. If you want to further iterate the data rows, you need to call the function until all the data rows have been traversed. However, for DML statements such as INSERT, UPDATE, and DELETE, this function can be executed once.

4). sqlite3_column
This function is used to obtain the data of the specified column in the current row. However, in strict sense, this function does not exist in the SQLite interface function, A group of related interface functions are used to complete this function. Each function returns different types of data, such:
 Copy codeThe Code is as follows:
Sqlite3_column_blob
Sqlite3_column_bytes
Sqlite3_column_bytes16
Sqlite3_column_double
Sqlite3_column_int
Sqlite3_column_int64
Sqlite3_column_text
Sqlite3_column_text16
Sqlite3_column_type
Sqlite3_column_value
Sqlite3_column_count
 
The sqlite3_column_count function is used to obtain the field data in the current result set. The following is the pseudo code for each row of data in the iteration result set using the sqlite3_step and sqlite3_column functions. Note that the sample code here simplifies the determination of the field type:
Copy codeThe Code is as follows:
Int fieldCount = sqlite3_column_count (...);
While (sqlite3_step (...) <> EOF ){
For (int I = 0; I <fieldCount; ++ I ){
Int v = sqlite3_column_int (..., I );
}
}

5). sqlite3_finalize
This function is used to destroy the prepared statement object. Otherwise, memory leakage may occur.

6). sqlite3_close
This function is used to close the previously opened database_connection object. All prepared_statements objects related to this object must be destroyed before this.

3. Parameter binding:

Like most relational databases, SQLite SQL text also supports variable binding to reduce the number of times SQL statements are dynamically parsed, thus improving the efficiency of data query and data operations. To complete this operation, we need to use the APIs, sqlite3_reset, and sqlite3_bind interfaces provided by SQLite. See the following example:

Copy codeThe Code is as follows:
Void test_parameter_binding (){
// 1. Insert multiple data entries without parameter binding.
Char strSQL [128];
For (int I = 0; I <MAX_ROWS; ++ I ){
Sprintf (strSQL, "insert into testtable values (% d)", I );
Sqlite3_prepare_v2 (..., strSQL );
Sqlite3_step (prepared_stmt );
Sqlite3_finalize (prepared_stmt );
}
// 2. Insert multiple data entries when the parameter is bound.
String strSQLWithParameter = "insert into testtable values (?) ";
Sqlite3_prepare_v2 (..., strSQL );
For (int I = 0; I <MAX_ROWS; ++ I ){
Sqlite3_bind (..., I );
Sqlite3_step (prepared_stmt );
Sqlite3_reset (prepared_stmt );
}
Sqlite3_finalize (prepared_stmt );
}

The first thing to note here is that the SQL statement "insert into testtable values (?) "Question mark (?) Represents the placeholder of parameter variables. This rule is consistent in many relational databases, so it is more convenient for database transplantation.

From the sample code above, we can see that the execution efficiency of parameter binding is higher than that of generating different SQL statements each time, that is, 2) the efficiency is significantly better than 1 ), the following is a comparison of the two methods:

1). On the program surface, the former executes more tasks in the for loop, such as string filling, SQL statement prepare, and prepared_statement object release.
2) according to the official SQLite documentation, the execution efficiency of sqlite3_prepare_v2 is usually lower than that of sqlite3_step.
3) when the inserted data volume is large, the efficiency improvement brought by the latter is considerable.