20分钟掌握sqlite库的使用

/**
@name 打开数据库，获取数据库连接对象
@param filename 数据库文件完整路径，UTF8编码
@param ppDb 数据库连接对象，调用其它接口时需要传入
@return SQLITE_OK 成功，其它值是错误码
*/
int sqlite3_open(
    const char *filename,   /* Database filename (UTF-8) */
    sqlite3 **ppDb          /* OUT: SQLite db handle */
    );

/**
@name 执行SQL语句
@param pDb 数据库连接对象，sqlite3_open返回的值
@param sql 要执行的SQL语句，多个SQL语句之间使用分号（;）隔开
@param callback SQL执行过程中每个结果行都会进行回调，
                            SQL查询时必需设置回调获取查询结果
@param callback_context 作为callback的第一个参数，透传
@param errmsg 如果调用失败，返回错误内容，不需要时调sqlite3_free()释放
@return SQLITE_OK 成功，其它值是错误码
*/
int sqlite3_exec(
    sqlite3* pDb,                                  /* An open database */
    const char *sql,                           /* SQL to be evaluated */
    int(*callback)(void*, int, char**, char**),  /* Callback function */
    void * callback_context,                                    /* 1st argument to callback */
    char **errmsg                              /* Error msg written here */
    );

/**
int(*callback)(void* callback_context, int column_num, char** column_value, char** column_name);
回调有4个参数：
第一，callback_context 回调上下文，sqlite3_exec的callback_context，业务自己定义
第二，column_num 列数，column_value和column_name数组的大小
第三，column_value 各列的值
第四， column_name 各列的名字
如果返回值不是SQLITE_OK，sqlite3_exec将立即终止SQL执行，并返回失败
*/

/**
@name 关闭数据库连接对象
@param pDB 数据库连接对象， sqlite3_open返回的值
@return SQLITE_OK 成功，其它值是错误码
*/
int sqlite3_close(sqlite3* pDB);

#include <stdio.h>
#include <stdlib.h>
#include "sqlite/sqlite3.h" 

static int callback(void *NotUsed, int argc, char **azColValue, char **azColName) {
    int i;
    for (i = 0; i < argc; i++) {
        printf("%s = %s\n", azColName[i], azColValue[i] ? azColValue[i] : "NULL");
    }
    printf("\n");
    return 0;
}

int main(int argc, char* argv[]) {
    sqlite3 *db;
    char *zErrMsg = 0;
    int rc;
    char *sql;

    /* Open database */
    rc = sqlite3_open("test.db", &db);

    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return(0);
    }
    else {
        fprintf(stdout, "Opened database successfully\n");
    }

    /* Create Table */
    sql = "CREATE TABLE COMPANY("  \
        "ID INT PRIMARY KEY     NOT NULL," \
        "NAME           TEXT    NOT NULL," \
        "AGE            INT     NOT NULL," \
        "ADDRESS        CHAR(50)," \
        "SALARY         REAL );";

    rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);

    if (rc != SQLITE_OK){
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    else {
        fprintf(stdout, "Table created successfully\n");
    }

    /* Insert Table */
    sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
        "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
        "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
        "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     \
        "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
        "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
        "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
        "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";

    rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);

    if (rc != SQLITE_OK){
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    else {
        fprintf(stdout, "Records created successfully\n");
    }

    /* Select Table */
    sql = "SELECT * from COMPANY";

    rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);

    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    else {
        fprintf(stdout, "Operation done successfully\n");
    }

    /* Update Table */
    sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; " \
        "SELECT * from COMPANY";

    rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);

    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    else {
        fprintf(stdout, "Operation done successfully\n");
    }

    /* Delete Table */
    sql = "DELETE from COMPANY where ID=2; " \
        "SELECT * from COMPANY";

    rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);

    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    else {
        fprintf(stdout, "Operation done successfully\n");
    }

    sqlite3_close(db);
    return 0;
}