SQLite Tutorial (ii): Introduction to the C + + interface _sqlite

First, overview:

of the C + + interfaces provided by SQLite, 5 of these APIs belong to the core interface. In this blog we will mainly introduce their usage and the core SQLite objects they involve, such as database_connection and Prepared_statement. The interfaces provided by SQLite are very easy to understand and master compared to APIs provided by other database engines, such as OCI, MySQL APIs, and so on.

second, core objects and interfaces:

1. Core objects:
The main two objects in the SQLite are the database_connection and prepared_statement. The Database_connection object is created and returned by the Sqlite3_open () interface function, and must be called before the application uses any other SQLite interface function to obtain the Database_connnection object. In subsequent APIs calls, the object is required as an input parameter to complete the appropriate work. As for prepare_statement, we can simply treat it as a compiled SQL statement, so all functions related to the execution of the SQL statement need the object as an input parameter to complete the specified SQL operation.

2. Core interface:
1). Sqlite3_open
This function has been mentioned above, which is the entry function for manipulating the SQLite database. The Database_connection object returned by this function is a handle argument for many other SQLite APIs. Note that we can use this function to open a database file that already exists, or to create a new database file. For the Database_connection object returned by the function, we can share the object's pointer between multiple threads to complete any operations related to the database. However, in multi-threaded situations, the more recommended way to use this is to create separate Database_connection objects for each thread. There is also a point to this function that we do not need to create multiple database connection objects in order to access multiple databases, because the attach command with SQLite can easily access multiple databases in one connection.

2). Sqlite3_prepare
The function converts the SQL text to a Prepared_statement object and returns a pointer to the object after the function executes. In fact, the function does not evaluate the parameter to specify an SQL statement, it simply initializes the SQL text to a state to be executed. Finally, we can use the SQLITE3_PREPARE_V2 interface function to replace the function to do the same job for the new application.

3). Sqlite3_step
This function evaluates the Prepared_statement object returned by the Sqlite3_prepare function, and after the function is executed, the inner pointer of the Prepared_statement object will point to the first row of the result set it returns. If you intend to iterate further through the rows of data, you will need to call the function continuously until all the rows of data have been traversed. However, for DML statements such as INSERT, update, and delete, the function can be performed one time.

4). Sqlite3_column
This function is used to get data for the specified column of the current row, but in strict sense, this function does not exist in the SQLite interface function, but rather by a set of related interface functions, each of which returns different types of data, such as:

Copy Code code 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

Where the Sqlite3_column_count function is used to get the field data in the current result set. Here are the pseudocode for using the Sqlite3_step and Sqlite3_column functions to iterate through each row of data in the result set, and note that this simplifies the judgment of field types as sample code:

Copy Code code 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, or it will cause a memory leak.

6). Sqlite3_close
This function is used to close a previously opened Database_connection object in which all Prepared_statements objects associated with the object must be destroyed before that.

Third, parameter binding:

As with most relational databases, SQLite SQL text also supports variable bindings to reduce the number of dynamic parsing of SQL statements, thereby improving the efficiency of data query and data manipulation. To complete this operation, we need to use the other two interfaces provided by SQLite Apis,sqlite3_reset and Sqlite3_bind. See the following example:

Copy Code code as follows:

void Test_parameter_binding () {
1. Inserts more than one data with no parameter bindings.
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. Inserts more than one data 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 (?)" The question mark (?) in Represents a placeholder for a parameter variable that is consistent across many relational databases, so it is more convenient for database porting operations.

It is obvious from the above example code that the execution efficiency of the parameter binding is higher than that of each generation of different SQL statements, that is, 2 is better than 1 in efficiency, the following is a specific comparison of the two ways:

    1). From the surface of the program alone, the former performs more tasks in the For loop, such as the filling of strings, the prepare of SQL statements, and the release of Prepared_statement objects.
    2). In SQLite's official document, it is clear that Sqlite3_prepare_v2 's performance is often less efficient than Sqlite3_step's.
    3). When the amount of data inserted is large, the efficiency gains from the latter are considerable.