SQLite tutorial (14): C language programming example code (2), sqlite programming example

SQLite tutorial (14): C language programming example code (2), sqlite programming example

3. Efficient batch data insertion:

Before giving the operation steps, let's briefly describe the concept of batch insert to help you read the subsequent sample code. In fact, batch insert is not a new concept. It is supported in the C interface APIs of other relational databases, but the interface implementation methods are different. OCI provides the most convenient and efficient programming interfaces for a wide range of popular database interfaces, such as OCI (Oracle API), MySQL API, and PostgreSQL API. SQLite, as a simple and flexible embedded database, also provides this function, but the implementation method is not as convenient as other databases, it only uses an implicit technique to achieve batch insert. Its logic is as follows:

1) Start a transaction to ensure that all subsequent data operation statements are completed within the transaction. In SQLite, if a transaction is not manually enabled, all of its DML statements work in the automatic commit mode. After each operation, the data is automatically submitted and written to the disk file. However, in the non-automatic submission mode, the modified data is written to the disk only after the transaction is manually committed. The modified data is only stored in the memory. Obviously, this batch write method will certainly be much more efficient than multiple iterative single write operations.

2 ). prepare the data to be inserted based on variable binding, which can save a lot of sqlite3_prepare_v2 function calls, this saves time for compiling the same SQL statement into the bytecode recognized by SQLite. In fact, the official SQLite documentation clearly states that in many cases, sqlite3_prepare_v2 function execution time is more than sqlite3_step function execution time. Therefore, we recommend that you avoid repeated calls to sqlite3_prepare_v2 function. In our implementation, to avoid such overhead, you only need to bind the data to be inserted to the SQL statement as a variable, in this way, the SQL statement only needs to call the sqlite3_prepare_v2 function once for compilation. The subsequent operation only replaces different variable values.

3). Submit things explicitly after all data inserts are completed. After submission, SQLite automatically restores the current connection to the automatic submission mode.

The following describes how to implement the sample code:

1). Create a test data table.
2) execute the begin transaction statement to manually start a TRANSACTION.
3) Prepare the insert statement and related binding variables.
4) insert data iteratively.
5) after the execution, submit the transaction by executing the COMMIT statement.
6). Delete the test table.
See the following code and key notes:

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 a data table. If creation fails, use sqlite3_finalize to release the sqlite3_stmt object to prevent memory leakage.
If (sqlite3_prepare_v2 (conn, createTableSQL, len, & stmt, NULL )! = SQLITE_ OK ){
If (stmt)
Sqlite3_finalize (stmt );
Sqlite3_close (conn );
Return;
}
// 3. Run the sqlite3_step command to create a table. For DDL and DML statements, sqlite3_step executes the correct return value.
// Only SQLITE_DONE is supported. For SELECT queries, if data is returned, SQLITE_ROW is returned. If data arrives at the end of the result set, SQLITE_ROW is returned.
// SQLITE_DONE.
If (sqlite3_step (stmt )! = SQLITE_DONE ){
Sqlite3_finalize (stmt );
Sqlite3_close (conn );
Return;
}
// 4. Release the resource used to create the TABLE statement object.
Sqlite3_finalize (stmt );
Printf ("Succeed to create test table now. \ n ");

// 5. Enable a transaction 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 an insert data based on the bound 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. Bind different variable data iteratively based on existing SQL statements
For (int I = 0; I <insertCount; ++ I ){
// When 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 bound to the sqlite3_stmt object.
Sqlite3_reset (stmt3 );
Printf ("Insert Succeed. \ n ");
}
Sqlite3_finalize (stmt3 );

// 8. Submit the previous transaction.
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 delete the data table created by the function.
// 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 result is 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 results are exactly the same as those in the previous example (normal data insertion), but they are much better than the former in terms of execution efficiency.

4. Data Query:

Data Query is the most basic function provided by each relational database. The following code example shows how to get data through the SQLite API.
1). Create a test data table.
2) Insert a test data to the data table for later query.
3) execute the SELECT statement to retrieve data.
4). Delete the test table.
See the following sample code and key notes:
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 a data table. If creation fails, use sqlite3_finalize to release the sqlite3_stmt object to prevent memory leakage.
If (sqlite3_prepare_v2 (conn, createTableSQL, len, & stmt, NULL )! = SQLITE_ OK ){
If (stmt)
Sqlite3_finalize (stmt );
Sqlite3_close (conn );
Return;
}
// 3. Run the sqlite3_step command to create a table. For DDL and DML statements, sqlite3_step executes the correct return value.
// Only SQLITE_DONE is supported. For SELECT queries, if data is returned, SQLITE_ROW is returned. If data arrives at the end of the result set, SQLITE_ROW is returned.
// SQLITE_DONE.
If (sqlite3_step (stmt )! = SQLITE_DONE ){
Sqlite3_finalize (stmt );
Sqlite3_close (conn );
Return;
}
// 4. Release the resource used to create the TABLE statement object.
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 the 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 ){
// Determine the type of the current field in the record, and then use different API functions based on the returned type.
// Obtain 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 delete the data table created by the function.
// 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 result is 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.