https://developpaper.com/sqlite-tutorial-13-c-language-programming-example-code-1/
SQLite Tutorial (13): C Language Programming Example Code (1) - Develop Paper
1. Get Schema information for tables: 1. Create tables dynamically. 2. According to the API provided by sqlite3, get the information of table fields, such as the number of fields and the type of each field. 3. Delete the table. See the following code and k
developpaper.com
1. Get Schema information for tables:1. Create tables dynamically.
2. According to the API provided by sqlite3, get the information of table fields, such as the number of fields and the type of each field.
3. Delete the table.
See the following code and key comments:
Copy codeThe code is as follows:
#include <sqlite3.h>
#include <string>
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. Construct sqlite3_stmt object of query table data.
const char* selectSQL = “SELECT * FROM TESTTABLE WHERE 1 = 0”;
sqlite3_stmt* stmt2 = NULL;
if (sqlite3_prepare_v2(conn,selectSQL,strlen(selectSQL),&stmt2,NULL) != SQLITE_OK) {
if (stmt2)
sqlite3_finalize(stmt2);
sqlite3_close(conn);
return;
}
// 6. Get the number of fields in the result set according to the object of the select statement.
int fieldCount = sqlite3_column_count(stmt2);
printf(“The column count is %d.\n”,fieldCount);
// 7. Traverse through the meta-information of each field in the result set and get the type when it is declared.
for (int i = 0; i < fieldCount; ++i) {
// Because there is no data in Table at this time, and the data type in SQLite itself is dynamic, so in the absence of data.
// The sqlite3_column_type function cannot be retrieved, and sqlite3_column_type only returns SQLITE_NULL.
// The specific type is not returned until data is available, so the sqlite3_column_decltype function is used here to get the table sound.
// The type of declaration given in Ming Shi.
string stype = sqlite3_column_decltype(stmt2,i);
stype = strlwr((char*)stype.c_str());
// The following parsing rules are listed in the “Data Types – > 1. Rules for Determining Field Affinity” section of the series. The links are as follows:
////www.jb51.net/article/65424.htm
if (stype.find(“int”) != string::npos) {
printf(“The type of %dth column is INTEGER.\n”,i);
} else if (stype.find(“char”) != string::npos
|| stype.find(“text”) != string::npos) {
printf(“The type of %dth column is TEXT.\n”,i);
} else if (stype.find(“real”) != string::npos
|| stype.find(“floa”) != string::npos
|| stype.find(“doub”) != string::npos ) {
printf(“The type of %dth column is DOUBLE.\n”,i);
}
}
sqlite3_finalize(stmt2);
// 8. 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* stmt3 = NULL;
if (sqlite3_prepare_v2(conn,dropSQL,strlen(dropSQL),&stmt3,NULL) != SQLITE_OK) {
if (stmt3)
sqlite3_finalize(stmt3);
sqlite3_close(conn);
return;
}
if (sqlite3_step(stmt3) == SQLITE_DONE) {
printf(“The test table has been dropped.\n”);
}
sqlite3_finalize(stmt3);
sqlite3_close(conn);
}
int main()
{
doTest();
return 0;
}
// The output results are as follows:
//Succeed to create test table now.
//The column count is 3.
//The type of 0th column is INTEGER.
//The type of 1th column is DOUBLE.
//The type of 2th column is TEXT.
//The test table has been dropped.
2. Conventional data insertion:
Create test data tables.
Insert test data through INSERT statement.
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”);
int insertCount = 10;
// 5. Construct sqlite3_stmt object for inserting data.
const char* insertSQL = “INSERT INTO TESTTABLE VALUES(%d,%f,’%s’)”;
const char* testString = “this is a test.”;
char sql[1024];
sqlite3_stmt* stmt2 = NULL;
for (int i = 0; i < insertCount; ++i) {
sprintf(sql,insertSQL,i,i * 1.0,testString);
if (sqlite3_prepare_v2(conn,sql,strlen(sql),&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(“Insert Succeed.\n”);
}
sqlite3_finalize(stmt2);
// 6. 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* stmt3 = NULL;
if (sqlite3_prepare_v2(conn,dropSQL,strlen(dropSQL),&stmt3,NULL) != SQLITE_OK) {
if (stmt3)
sqlite3_finalize(stmt3);
sqlite3_close(conn);
return;
}
if (sqlite3_step(stmt3) == SQLITE_DONE) {
printf(“The test table has been dropped.\n”);
}
sqlite3_finalize(stmt3);
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.
