SQLite Tutorial (14): C Language Programming Instance code (2) _sqlite

Third, efficient bulk data insertion:

A brief description of the bulk insert concept before giving the procedure is given to help you read the sample code that followed. In fact, BULK INSERT is not a new concept, in other relational database of the C interface API has provided some support, but the interface is implemented in different ways. Throughout the many popular database interfaces, such as OCI (Oracle API), MySQL API and PostgreSQL API, OCI provides the most convenient programming interface and the most efficient way to implement it. SQLite as a simple and flexible embedded database also provides this functionality, but the implementation is not as convenient as other databases obvious, it is only through an implicit technique to achieve the purpose of bulk inserts, the logic is as follows:

1. Start a thing to ensure that subsequent data manipulation statements are completed within that thing. In SQLite, if a thing is not opened manually, all of its DML statements work in autocommit mode, and the data is automatically submitted and written to disk files after each operation. In the non autocommit mode, however, the modified data is written to disk only when the object is in a manual commit, and the previously modified data resides only in memory. It is obvious that such a bulk write method will be much more efficient than a single write operation with multiple iterations.

2. Prepare the data to be inserted based on the way the variable is bound, which saves a lot of sqlite3_prepare_v2 function calls, thus saving the time it takes to compile the same SQL statement into a sqlite-recognized bytecode. In fact, SQLite's official documentation has made it clear that in many cases the SQLITE3_PREPARE_V2 function takes more time to execute than the Sqlite3_step function, so it is recommended that the user avoid repeated calls Sqlite3_prepare_ V2 function. In our implementation, if you want to avoid this kind of overhead, simply bind the data to be inserted as a variable into the SQL statement, so that the SQL statement only needs to be compiled once by invoking the SQLITE3_PREPARE_V2 function, and the subsequent action is to replace the different variable values.

3. Explicitly submit things after all data inserts have been completed. Once committed, SQLite automatically reverts the current connection to autocommit mode.

The following is the implementation step of the sample code:

1). Create a test data table.
2. Manually open a thing by executing the BEGIN TRANSACTION statement.
3. Prepare the INSERT statement and related binding variables.
4. Inserts data in an iterative style.
5. After completion, commit the thing by executing a commit statement.
6. Delete the test table.
See the following code and key comments:

Copy Code code as follows:

#include <sqlite3.h>
#include <string>
#include <stdio.h>

using namespace Std;

void Dotest ()
{
sqlite3* conn = NULL;
1. Open Database
int result = Sqlite3_open ("d:/mytest.db", &conn);
If (result!= Sqlite_ok) {
Sqlite3_close (conn);
Return
}
Const char* Createtablesql =
"CREATE TABLE testtable (int_col int, float_col real, String_col TEXT)";
sqlite3_stmt* stmt = NULL;
int len = strlen (createtablesql);
2. Prepare to create the data table, and if the creation fails, you need to release the Sqlite3_stmt object with sqlite3_finalize to prevent memory leaks.
if (SQLITE3_PREPARE_V2 (conn,createtablesql,len,&stmt,null)!= Sqlite_ok) {
if (stmt)
Sqlite3_finalize (stmt);
Sqlite3_close (conn);
Return
}
3. Executes the statement that creates the table through the Sqlite3_step command. For DDL and DML statements, Sqlite3_step performs the correct return value
Only Sqlite_done, in the case of a select query, returns if there is data returned Sqlite_row, and when the end of the result set is reached
Sqlite_done.
if (Sqlite3_step (stmt)!= Sqlite_done) {
Sqlite3_finalize (stmt);
Sqlite3_close (conn);
Return
}
4. Frees the resources that create a table statement object.
Sqlite3_finalize (stmt);
printf ("Succeed to create Test table now.\n");

5. To open a thing explicitly.
sqlite3_stmt* stmt2 = NULL;
Const char* Beginsql = "BEGIN TRANSACTION";
if (Sqlite3_prepare_v2 (Conn,beginsql,strlen (beginsql), &stmt2,null)!= Sqlite_ok) {
if (STMT2)
Sqlite3_finalize (STMT2);
Sqlite3_close (conn);
Return
}
if (Sqlite3_step (STMT2)!= Sqlite_done) {
Sqlite3_finalize (STMT2);
Sqlite3_close (conn);
Return
}
Sqlite3_finalize (STMT2);

6. Constructs the insertion data based on the binding variable.
Const char* Insertsql = "INSERT into TestTable VALUES (?,?,?)";
sqlite3_stmt* stmt3 = NULL;
if (Sqlite3_prepare_v2 (Conn,insertsql,strlen (insertsql), &stmt3,null)!= Sqlite_ok) {
if (STMT3)
Sqlite3_finalize (STMT3);
Sqlite3_close (conn);
Return
}
int insertcount = 10;
Const char* Strdata = "This is a test.";
7. Based on an existing SQL statement, the iterative binding of different variable data
for (int i = 0; i < Insertcount; ++i) {
At the time of binding, the leftmost variable index value is 1.
Sqlite3_bind_int (Stmt3,1,i);
Sqlite3_bind_double (Stmt3,2,i * 1.0);
Sqlite3_bind_text (Stmt3,3,strdata,strlen (strdata), sqlite_transient);
if (Sqlite3_step (STMT3)!= Sqlite_done) {
Sqlite3_finalize (STMT3);
Sqlite3_close (conn);
Return
}
Reinitialize the variable that the Sqlite3_stmt object binds to.
Sqlite3_reset (STMT3);
printf ("Insert succeed.\n");
}
Sqlite3_finalize (STMT3);

   //8. Before submitting the things.
    const char* commitsql = "COMMIT";
    sqlite3_stmt* stmt4 = NULL;
    if (Sqlite3_prepare_v2 (Conn,commitsql,strlen (commitsql), &stmt4,null)!= sqlite_ok) {
        if (STMT4)
             sqlite3_finalize (STMT4);
        Sqlite3_close (conn);
        return;
   }
    if (Sqlite3_step (STMT4)!= sqlite_done) {
        Sqlite3_finalize (STMT4);
        Sqlite3_close (conn);
        return;
   }
    sqlite3_finalize (STMT4);

   //9. To facilitate the next test run, we need to remove the data table created by the function, otherwise we will not be able to
   //create the table at the next run, because it already exists.
    const char* dropsql = "DROP TABLE testtable";
    sqlite3_stmt* stmt5 = NULL;
    if (Sqlite3_prepare_v2 (Conn,dropsql,strlen (dropsql), &stmt5,null)!= sqlite_ok) {
        if (STMT5)
             sqlite3_finalize (STMT5);
        Sqlite3_close (conn);
        return;
   }
    if (sqlite3_step (stmt5) = = Sqlite_done) {
        printf ("The test table has been dropped.\n");
   }
    sqlite3_finalize (STMT5);
    Sqlite3_close (conn);
}

int main ()
{
Dotest ();
return 0;
}
The output results are as follows:
Succeed to create test table now.
Insert succeed.
Insert succeed.
Insert succeed.
Insert succeed.
Insert succeed.
Insert succeed.
Insert succeed.
Insert succeed.
Insert succeed.
Insert succeed.
The test table has been dropped.

This result is exactly the same as the previous example (normal data insertion), but is obviously superior to the former in the execution efficiency.

Four, data inquiry:

A data query is the most basic function provided by each relational database, and the following code example shows how to get data through the SQLite API.
1). Create a test data table.
2. Insert a test data into the datasheet to facilitate subsequent queries.
3). Executes the SELECT statement to retrieve data.
4. Delete the test table.
See the following sample code and critical comments:

Copy Code code as follows:

#include <sqlite3.h>
#include <string>
#include <stdio.h>

using namespace Std;

void Dotest ()
{
sqlite3* conn = NULL;
1. Open Database
int result = Sqlite3_open ("d:/mytest.db", &conn);
If (result!= Sqlite_ok) {
Sqlite3_close (conn);
Return
}
Const char* Createtablesql =
"CREATE TABLE testtable (int_col int, float_col real, String_col TEXT)";
sqlite3_stmt* stmt = NULL;
int len = strlen (createtablesql);
2. Prepare to create the data table, and if the creation fails, you need to release the Sqlite3_stmt object with sqlite3_finalize to prevent memory leaks.
if (SQLITE3_PREPARE_V2 (conn,createtablesql,len,&stmt,null)!= Sqlite_ok) {
if (stmt)
Sqlite3_finalize (stmt);
Sqlite3_close (conn);
Return
}
3. Executes the statement that creates the table through the Sqlite3_step command. For DDL and DML statements, Sqlite3_step performs the correct return value
Only Sqlite_done, in the case of a select query, returns if there is data returned Sqlite_row, and when the end of the result set is reached
Sqlite_done.
if (Sqlite3_step (stmt)!= Sqlite_done) {
Sqlite3_finalize (stmt);
Sqlite3_close (conn);
Return
}
4. Frees the resources that create a table statement object.
Sqlite3_finalize (stmt);
printf ("Succeed to create Test table now.\n");

5. Inserts test data for the subsequent query operation.
sqlite3_stmt* stmt2 = NULL;
Const char* Insertsql = "INSERT into TestTable VALUES (20,21.0, ' It is a test. ')";
if (Sqlite3_prepare_v2 (Conn,insertsql,strlen (insertsql), &stmt2,null)!= Sqlite_ok) {
if (STMT2)
Sqlite3_finalize (STMT2);
Sqlite3_close (conn);
Return
}
if (Sqlite3_step (STMT2)!= Sqlite_done) {
Sqlite3_finalize (STMT2);
Sqlite3_close (conn);
Return
}
printf ("Succeed to insert Test data.\n");
Sqlite3_finalize (STMT2);

6. Executes a SELECT statement to query the data.
Const char* SELECTSQL = "SELECT * from TestTable";
sqlite3_stmt* stmt3 = NULL;
if (Sqlite3_prepare_v2 (Conn,selectsql,strlen (selectsql), &stmt3,null)!= Sqlite_ok) {
if (STMT3)
Sqlite3_finalize (STMT3);
Sqlite3_close (conn);
Return
}
int fieldcount = Sqlite3_column_count (STMT3);
do {
int r = sqlite3_step (STMT3);
if (r = = Sqlite_row) {
for (int i = 0; i < FieldCount; ++i) {
Here you need to determine the type of current record field, and then use different API functions based on the type returned
Gets the actual data value.
int vtype = Sqlite3_column_type (stmt3,i);
if (VType = = Sqlite_integer) {
int v = sqlite3_column_int (stmt3,i);
printf ("The INTEGER value is%d.\n", V);
else if (VType = = sqlite_float) {
Double v = sqlite3_column_double (stmt3,i);
printf ("The DOUBLE value is%f.\n", V);
else if (VType = = Sqlite_text) {
Const CHAR* v = (const char*) sqlite3_column_text (stmt3,i);
printf ("The TEXT value is%s.\n", V);
else if (VType = = Sqlite_null) {
printf ("This value is null.\n");
}
}
else if (r = = Sqlite_done) {
printf ("Select finished.\n");
Break
} else {
printf ("Failed to select.\n");
Sqlite3_finalize (STMT3);
Sqlite3_close (conn);
Return
}
} while (true);
Sqlite3_finalize (STMT3);

   //7. To facilitate the next test run, we need to remove the data table created by the function, otherwise we will not be able to
   //create the table at the next run, because it already exists.
    const char* dropsql = "DROP TABLE testtable";
    sqlite3_stmt* stmt4 = NULL;
    if (Sqlite3_prepare_v2 (Conn,dropsql,strlen (dropsql), &stmt4,null)!= sqlite_ok) {
        if (STMT4)
             sqlite3_finalize (STMT4);
        Sqlite3_close (conn);
        return;
   }
    if (sqlite3_step (stmt4) = = Sqlite_done) {
        printf ("The test table has been dropped.\n");
   }
    sqlite3_finalize (STMT4);
    Sqlite3_close (conn);
}

int main ()
{
Dotest ();
return 0;
}
The output results are as follows:
Succeed to create test table now.
Succeed to insert test data.
The INTEGER value is 20.
The DOUBLE value is 21.000000.
The TEXT value is this is a test.
Select finished.
The test table has been dropped.