适用于物联网Linux设备上使用的sqlite3

在嵌入式Linux上使用sqlite3的一个简单示例，包含写入，查询第一条和最后一条，删除第一条和最后一条，在物联网设备上读写非常方便。

使用sqlite3_exec（）与 sqlite3_prepare（）+ sqlite3_step（）二种方式是一样的;

注意，在创建完表后最好加一个sleep，防止立马写入导致的错误。
 

#include "sqlite3.h"

#define MAX_LEN 1000
typedef struct rdate
{
	int type;	
    int length;
	char data[MAX_LEN];
}Rdate;
Rdate p_Rdate;
sqlite3 *db;

int ret;
int i,j;
int nrow=0;
int ncolumn = 0;
char *zErrMsg =0;
char **azResult=0; //二维数组存放结果

//nrow   
int get_db_count(sqlite3 *db) {
    char *sql = "SELECT COUNT(*) FROM Database;";
    sqlite3_stmt *stmt;
    int count = 0;
    char *zErrMsg = 0;
    int rc;

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        printf("error sqlite3_prepare_v2 %d\n", rc);
        return -1;
    }
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_ROW) {
        printf("error sqlite3_step: %d\n", rc);
        return -1;
    }
    count = sqlite3_column_int(stmt, 0);
    sqlite3_finalize(stmt);

    return count;
}

int get_all_db(sqlite3 *db) {
    char *sql="select *from Database";
    sqlite3_get_table(db, sql, &azResult, &nrow, &ncolumn, &zErrMsg );
    printf("nrow=%d ncolumn=%d\n", nrow, ncolumn);
    printf("the result is:\n");
    for(i = 0;i < (nrow + 1);i++){
        for(j = 0;j < ncolumn;j++){
            printf("azResult[%d]=%s\t",i * ncolumn + j,azResult[i * ncolumn + j]);
        }
        printf("\n");
    }
    sqlite3_free_table(azResult);
    return 0;
}

int get_first_db(sqlite3 *db) {
    char *sql = "SELECT * FROM Database LIMIT 1;";
    sqlite3_stmt *stmt;

    sqlite3_prepare(db, sql, strlen(sql), &stmt, 0);
    int result = sqlite3_step(stmt);
    while (result == SQLITE_ROW) {
        p_Rdate.type = sqlite3_column_int(stmt, 1);
        p_Rdate.length = sqlite3_column_int(stmt, 2);
        strcpy(p_Rdate.data, sqlite3_column_text(stmt, 3));
        printf("get_first_db: %d, %d, %s\n", p_Rdate.type, p_Rdate.length, p_Rdate.data);
        result = sqlite3_step(stmt);
    }
    sqlite3_finalize(stmt);

    return 0;
}

int get_last_db(sqlite3 *db) {
    char *sql = "SELECT * FROM Database ORDER BY ID DESC LIMIT 1;";
    sqlite3_stmt *stmt;

    sqlite3_prepare(db, sql, strlen(sql), &stmt, 0);
    int result = sqlite3_step(stmt);
    while (result == SQLITE_ROW) {
        p_Rdate.type = sqlite3_column_int(stmt, 1);
        p_Rdate.length = sqlite3_column_int(stmt, 2);
        strcpy(p_Rdate.data, sqlite3_column_text(stmt, 3));
        printf("get_last_db: %d, %d, %s\n", p_Rdate.type, p_Rdate.length, p_Rdate.data);
        result = sqlite3_step(stmt);
    }
    sqlite3_finalize(stmt);

    return 0;
}


int insert_db(sqlite3* db, Rdate *p_Rdate) {
    char sql[512] = {0};

    sqlite3_stmt *stmt;
    int feature_index;

    snprintf(sql, 512, "INSERT INTO Database (type, length, data) VALUES (%d, %d, '%s');", 
        p_Rdate->type, p_Rdate->length, p_Rdate->data);
 
    // printf("sql: %s\n", sql);

    int rc = sqlite3_prepare(db, sql, strlen(sql), &stmt, 0);
    if (rc != SQLITE_OK) {
        printf("sqlite3_prepare error: %d\n", rc);
        return -1;
    }

    sqlite3_step(stmt);
    sqlite3_finalize(stmt);

    return 0;
}

int insert_db_test(sqlite3* db){
    static int i = 0;
    p_Rdate.type = i++;
    strcpy(p_Rdate.data, "test");
    p_Rdate.length = 88;
    insert_db(db, &p_Rdate);
}

int delete_last_db(sqlite3* db){
    char *sql="delete from Database where ID = (SELECT ID FROM Database ORDER BY ID DESC LIMIT 1) ;";
    sqlite3_exec( db , sql , NULL , NULL , &zErrMsg );

#ifdef _DEBUG_
    printf("zErrMsg = %s \n", zErrMsg);
    sqlite3_free(zErrMsg);
#endif
}

int delete_first_db(sqlite3* db){
    char *sql="delete from Database where ID = (SELECT ID FROM Database LIMIT 1) ;";
    sqlite3_exec( db , sql , NULL , NULL , &zErrMsg );

#ifdef _DEBUG_
    printf("zErrMsg = %s \n", zErrMsg);
    sqlite3_free(zErrMsg);
#endif
}

int delete_all_db(sqlite3* db){
    char *sql="delete from Database;";
    sqlite3_exec( db , sql , NULL , NULL , &zErrMsg );

#ifdef _DEBUG_
    printf("zErrMsg = %s \n", zErrMsg);
    sqlite3_free(zErrMsg);
#endif
}

int open(sqlite3 *db){
    // 打开数据库 
    ret = sqlite3_open(DATABASE,&db);
    if( ret != SQLITE_OK){
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(1);
    }else
        printf("opened a sqlite3 database successfully!\n");
    sleep(1);
    
    //创建表
    char *sql = "CREATE TABLE IF NOT EXISTS Database("\
                "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"\
                "type INTEGER,"\
                "length INTEGER,"\
                "data TEXT);";
    int rc = sqlite3_exec(db,sql,NULL,NULL,&zErrMsg);
    if(rc != SQLITE_OK){
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }else{
        fprintf(stdout, "Table created successfully\n");
    }
    sleep(1);// must do before insert
}