SQL ite3 database C language simple operation basic data search (3)
Overview: the creation, addition, deletion, and modification of sqlite3 database have all been mentioned. Today, it is the search of the database and the application of the core of database comparison. If the previous API is used for execution, because there is a return value in the search database, the SQLite ﹣ exec() function needs to be written as a callback function. I think this is inefficient and cumbersome to write. I will use several new APIs to do this. These new APIs are actually the exploded version of SQLite ﹣ exec() function.
1. Introduction to the API of the decomposed version
int sqlite3_prepare_v2(
sqlite3 *db, /* Database handle ---Database operation handle*/
const char *zSql, /* SQL statement, UTF-8 encoded --- UTF8 Encoding sql statements*/
int nByte, /* Maximum length of zSql in bytes. ---sql Statement length*/
sqlite3_stmt **ppStmt, /* OUT: Statement handle ---Prepared binary execution statement handle*/
const char **pzTail /* OUT: Pointer to unused portion of zSql ---Points to unused statements in sql statements*/
);
/*
The main function of this function is to convert an sql statement into SQLite3 executable binary sql statement and store it in sqlite3'stmt type data, that is, the preparation process of sql statement.
*/
int sqlite3_step(sqlite3_stmt*);
/*
This function is mainly to execute the binary sql statements we prepared, and judge the status of successful execution by the return value.
SQLITE_BUSY It means that the database engine cannot obtain the database lock needed to perform its work;
SQLITE_DONE Indicates that the statement has been executed successfully;
SQLITE_ROW If the executing SQL statement returns any data, the value is returned whenever the caller is ready to process a new row of data;
SQLITE_ERROR Means a runtime error has occurred;
SQLITE_MISUSE Means that the routine is called improperly;
*/
int sqlite3_column_int(sqlite3_stmt*, int iCol);
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
/*
These functions are mainly to obtain the value of a column in a row of data after executing SQLite3 step. This value may be of type int,text, etc. Use the corresponding type function to get the value. Detailed official website address: http://www.sqlite.org/c3ref/column_blob.html;
*/
int sqlite3_finalize(sqlite3_stmt * pStmt)
/*
Release the binary sql execution statement to avoid memory leakage.
*/2. Find data
It mainly depends on sql syntax. The key word used for inserting data is * * SELECT * *. Its syntax knowledge can be simply summarized into two types.
SELECT column name FROM table name
SELECT * FROM table name
WHERE condition can be added after it, such as select * from student WHERE name ='sky 'Here is a code to find the number of data:
/*Number of search data*/
int sqlite_find_count(sqlite3 *db)
{
/*Find the number of Sky*/
char *sql = "select count(*) from student where name = 'Sky';";
sqlite3_stmt *stmt = NULL;
/*Convert sql statement to sqlite3 recognizable statement and return pointer to stmt*/
int res = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL);
if (SQLITE_OK != res || NULL == stmt) {
goto err1;
}
/*Execute the prepared sqlite3 statement*/
res = sqlite3_step(stmt);
if (res != SQLITE_ROW) {
goto err2;
}
int count = sqlite3_column_int(stmt, 0);
if (count < 0) {
goto err2;
}
printf("count = %d\n", count);
sqlite3_finalize(stmt);
return count;
err2:
sqlite3_finalize(stmt);
err1:
return -1;
}Here is a code to find data and retrieve it:
/*Find and retrieve data*/
int sqlite_find_parse(sqlite3 *db)
{
/*Find data with name Sky*/
char *sql = "select * from student where name = 'Sky';";
sqlite3_stmt *stmt = NULL;
/*Convert sql statement to sqlite3 recognizable statement and return pointer to stmt*/
int res = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL);
if (SQLITE_OK != res || NULL == stmt) {
goto err1;
}
/*Execute the prepared sqlite3 statement*/
while (SQLITE_ROW == sqlite3_step(stmt)) {
printf("name: %s, uuid: %u\n",\
sqlite3_column_text(stmt, 1),\
sqlite3_column_int(stmt, 2));
}
sqlite3_finalize(stmt);
return 0;
err2:
sqlite3_finalize(stmt);
err1:
return -1;
}3. Source code and running results of the entire demo
demo
#include <stdio.h>
#include <stdint.h>
#include <string.h>
#include <time.h>
#include <pthread.h>
#include "sqlite/sqlite3.h"
/*Insert data into database*/
int insert_data(sqlite3 *db)
{
char *sql = "INSERT INTO student (name,uuid) VALUES('Alice', 17531000);"
"INSERT INTO student (name,uuid) VALUES('Bob', 17531001);"
"INSERT INTO student (name,uuid) VALUES('Sky', 17531002);"
"INSERT INTO student (name,uuid) VALUES('Born', 17531003);"
"INSERT INTO student (name,uuid) VALUES('Jason', 17531004);"
"INSERT INTO student (name,uuid) VALUES('Mike', 17531005);"
"INSERT INTO student (name,uuid) VALUES('Tisa', 17531006);"
"INSERT INTO student (name,uuid) VALUES('Sky', 17531007);";
char *err_msg = NULL;
int rc = sqlite3_exec(db, sql, 0, 0, &err_msg);
if (rc != SQLITE_OK ) {
fprintf(stderr, "SQL error: %s\n", err_msg);
sqlite3_free(err_msg);
return -1;
}
return 0;
}
/*Number of search data*/
int sqlite_find_count(sqlite3 *db)
{
/*Find the number of Sky*/
char *sql = "select count(*) from student where name = 'Sky';";
sqlite3_stmt *stmt = NULL;
/*Convert sql statement to sqlite3 recognizable statement and return pointer to stmt*/
int res = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL);
if (SQLITE_OK != res || NULL == stmt) {
goto err1;
}
/*Execute the prepared sqlite3 statement*/
res = sqlite3_step(stmt);
if (res != SQLITE_ROW) {
goto err2;
}
int count = sqlite3_column_int(stmt, 0);
if (count < 0) {
goto err2;
}
printf("count = %d\n", count);
sqlite3_finalize(stmt);
return count;
err2:
sqlite3_finalize(stmt);
err1:
return -1;
}
/*Find and retrieve data*/
int sqlite_find_parse(sqlite3 *db)
{
/*Find data with name Sky*/
char *sql = "select * from student where name = 'Sky';";
sqlite3_stmt *stmt = NULL;
/*Convert sql statement to sqlite3 recognizable statement and return pointer to stmt*/
int res = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL);
if (SQLITE_OK != res || NULL == stmt) {
goto err1;
}
/*Execute the prepared sqlite3 statement*/
while (SQLITE_ROW == sqlite3_step(stmt)) {
printf("name: %s, uuid: %u\n",\
sqlite3_column_text(stmt, 1),\
sqlite3_column_int(stmt, 2));
}
sqlite3_finalize(stmt);
return 0;
err2:
sqlite3_finalize(stmt);
err1:
return -1;
}
int main(void) {
sqlite3 *db;
char *err_msg = NULL;
/*Open or create the database test.db file*/
int rc = sqlite3_open("test.db", &db);
if (rc != SQLITE_OK) {
printf("open database test.db failed\n");
sqlite3_close(db);
return 1;
}
/*
This sql statement means to create a student table if the student table does not exist,
The format of the table is primary key id int type, name string type, uuid int type and uuid uniqueness, which are not repeatable
*/
char *sql = "CREATE TABLE IF NOT EXISTS student (\
[id] INTEGER PRIMARY KEY AUTOINCREMENT,\
[name] TXT,\
[uuid] INTEGER,\
UNIQUE([uuid])\
);";
/*Execute the above sql statement*/
rc = sqlite3_exec(db, sql, 0, 0, &err_msg);
if (rc != SQLITE_OK ) {
printf("SQL error: %s\n", err_msg);
sqlite3_free(err_msg);
sqlite3_close(db);
return 1;
}
insert_data(db);
sqlite_find_count(db);
sqlite_find_parse(db);
/*close database*/
sqlite3_close(db);
return 0;
}The operation results are as follows:
sky@ubuntu:~/Study/sqlite3/build$ ./sqlite_test
count = 2
name: Sky, uuid: 17531002
name: Sky, uuid: 17531007