https://developpaper.com/sqlite-tutorial-14-c-language-programming-example-code-2/
SQLite Tutorial (14): C Language Programming Example Code (2) - Develop Paper
3. Efficient batch data insertion: Before giving the steps, I will briefly explain the concept of batch insertion to help you read the following sample code. In fact, batch insertion is not a new concept. It provides some support in the C interface API of
developpaper.com
3. Efficient batch data insertion:
Before giving the steps, I will briefly explain the concept of batch insertion to help you read the following sample code. In fact, batch insertion is not a new concept. It provides some support in the C interface API of other relational databases, but the interface is implemented in different ways. The most popular database interfaces, such as OCI (Oracle API), MySQL API and PostgreSQL API, are the most convenient and efficient programming interfaces provided by OCI. As a simple and flexible embedded database, SQLite also provides this function, but its implementation is not as convenient and obvious as other databases. It only achieves the purpose of batch insertion through an implicit technique. Its logic is as follows:
1) Start a thing to ensure that the following data operation statements are completed within the thing. In SQLite, if there is no manual opening of a thing, all DML statements work in automatic submission mode, since data is automatically submitted and written to disk files after each operation. However, in the non-automatic submission mode, the modified data will only be written to disk after its location is manually COMMIT, and the previously modified data will only reside in memory. Obviously, this batch writing method will be much more efficient than the multi-iteration single writing operation.
2. Preparing the data to be inserted based on variable binding can save a lot of sqlite3_prepare_v2 function calls, thus saving the time of compiling the same SQL statement into bytecodes recognized internally in SQLite. In fact, it has been clearly pointed out in the official documents of SQLite that in many cases the execution time of sqlite3_prepare_v2 function is longer than that of sqlite3_step function. Therefore, it is recommended that users avoid calling sqlite3_prepare_v2 function repeatedly as much as possible. In our implementation, if we want to avoid such overhead, we only need to bind the data to be inserted into the SQL statement in the form of variables, so that the SQL statement only needs to call the sqlite3_prepare_v2 function to compile once, and then the operation is just to replace different variable values.
3) Submit things explicitly after all data insertions have been completed. After submission, SQLite automatically restores the current connection to automatic submission mode.
Following are the implementation steps of the sample code:
Create test data tables.
2) Manually open a thing by executing the BEGIN TRANSACTION statement.
Prepare to insert statements and related binding variables.
4) Iterative insertion of data.
Submit things by executing COMMIT statements after completion.
Delete the test table.
See the following code and key comments:
Copy codeThe code is as follows:
#include <sqlite3.h>
#include <string>
#include <stdio.h>
using namespace std;
void doTest()
{
sqlite3* conn = NULL;
// 1. Open the 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 data tables. If the creation fails, you need to release sqlite3_stmt objects 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. Execute the statement creating the table through sqlite3_step command. For DDL and DML statements, sqlite3_step performs the correct return value
// Only SQLITE_DONE. For SELECT queries, if data returns to SQLITE_ROW, it returns when it reaches the end of the result set.
//SQLITE_DONE。
if (sqlite3_step(stmt) != SQLITE_DONE) {
sqlite3_finalize(stmt);
sqlite3_close(conn);
return;
}
// 4. Release resources that create table statement objects.
sqlite3_finalize(stmt);
printf(“Succeed to create test table now.\n”);
// 5. 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. Construct insert data based on bound variables.
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. Binding different variable data iteratively based on existing SQL statements
for (int i = 0; i < insertCount; ++i) {
// When binding, the index value of the leftmost variable 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 variables bound to the sqlite3_stmt object.
sqlite3_reset(stmt3);
printf(“Insert Succeed.\n”);
}
sqlite3_finalize(stmt3);
// 8. Things before submission.
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. In order to facilitate the next test run, we need to delete the table created by this function, otherwise it will not be able to run next time.
// Create the table 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.
The result is identical to that of the previous example (ordinary data insertion), but it is obviously better than the former in execution efficiency.
4. Data query:
Data query is the most basic function provided by every relational database. The following code example shows how to get data through the SQLite API.
Create test data tables.
2) Insert a test data into the data table for subsequent queries.
3) Execute SELECT statement to retrieve data.
Delete the test table.
See the following sample code and key comments:
Copy codeThe code is as follows:
#include <sqlite3.h>
#include <string>
#include <stdio.h>
using namespace std;
void doTest()
{
sqlite3* conn = NULL;
// 1. Open the 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 data tables. If the creation fails, you need to release sqlite3_stmt objects 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. Execute the statement creating the table through sqlite3_step command. For DDL and DML statements, sqlite3_step performs the correct return value
// Only SQLITE_DONE. For SELECT queries, if data returns to SQLITE_ROW, it returns when it reaches the end of the result set.
//SQLITE_DONE。
if (sqlite3_step(stmt) != SQLITE_DONE) {
sqlite3_finalize(stmt);
sqlite3_close(conn);
return;
}
// 4. Release resources that create table statement objects.
sqlite3_finalize(stmt);
printf(“Succeed to create test table now.\n”);
// 5. Insert test data for subsequent query operations.
sqlite3_stmt* stmt2 = NULL;
const char* insertSQL = “INSERT INTO TESTTABLE VALUES(20,21.0,’this 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. Execute SELECT statement to query 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 first determine the type of the current record field, and then use different API functions according to the type returned.
// Get 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. In order to facilitate the next test run, we need to delete the table created by this function, otherwise it will not be able to run next time.
// Create the table 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.
