SQLite - 如何在 C 使用

編譯時加 -lsqlite3 即可使用

lib 定義

    #define RETRY_TIMES       10
    #define RETRY_TIMES_QUERY  5
    #define SQLITE_ROW_MAX_MEMBER  100
    typedef char **sqlite3_row;

    #define sqlite_open(filename, ppDb)                     sqlite3_open(filename, ppDb)
    #define sqlite_close(ppDb)                              sqlite3_close(ppDb)
    #define sqlite_exec(ppDb, sql, callback, callback_argv, errmsg) sqlite3_exec(ppDb, sql, callback, callback_argv, errmsg)
    #define sqlite_errcode(db)                              sqlite3_errcode(db)
    #define sqlite_errmsg(db)                               sqlite3_errmsg(db)
    #define sqlite_prepare(db, zSql, nByte, ppStmt, pzTail) sqlite3_prepare(db, zSql, nByte, ppStmt, pzTail)
    #define sqlite_column_count(pStmt)                      sqlite3_column_count(pStmt)
    #define sqlite_data_count(pStmt)                        sqlite3_data_count(pStmt)
    #define sqlite_column_text(pStmt, iCol)                 sqlite3_column_text(pStmt, iCol)
    #define sqlite_finalize(pStmt)                          sqlite3_finalize(pStmt)
    #define sqlite_step(pStmt)                              sqlite3_step(pStmt)
    #define sqlite_changes(db)                              sqlite3_changes(db)
    #define sqlite_last_insert_rowid(db)                    sqlite3_last_insert_rowid(db)

    int my_sqlite_connect(sqlite3 **sql, char *database)
    {
        int times = RETRY_TIMES, result = -1;

        while(times-- > 0) {
            if ((result = sqlite_open(database, sql)) == SQLITE_OK) {
                break;
            }
            sleep(1);
        }

        return result;
    }
    void my_sqlite_close(sqlite3 *sql)
    {
        int times = RETRY_TIMES, result = -1;

        while(times-- > 0) {
            if ((result = sqlite_close(sql)) == SQLITE_OK) {
                break;
            }
            sleep(1);
        }
    }
    int my_sqlite_exec(sqlite3 *sql, int (*callback)(void*,int,char**,char**), char *format, ...) {
        va_list arg;
        int times = RETRY_TIMES_QUERY, result = -1;
        char *query = NULL;

        va_start(arg, format);
        vasprintf(&query, format, arg);
        va_end(arg);

        if (query) {
            while(times-- > 0) {
                if ((result = sqlite_exec(sql, query, callback, NULL, NULL)) == SQLITE_OK)
                    break;
                syslog(LOG_ERR, "Error Number: %d, msg: %s\n",  result, sqlite_errmsg(sql));

                if (times > 0)
                    sleep(1);
            }

            if (times < 0) {
                syslog(LOG_ERR, "Can't run this query: %s",  query);
            }

            free(query);
        }
        return result;
    }
    sqlite3_stmt *my_sqlite_query(sqlite3 *sql, char *format, ...)
    {
        va_list arg;
        int times = RETRY_TIMES_QUERY, result = -1;
        char *query = NULL;
        sqlite3_stmt *pSelect = NULL;

        va_start(arg, format);
        vasprintf(&query, format, arg);
        va_end(arg);

        if (query) {
            while(times-- > 0) {
                if ((result = sqlite_prepare(sql, query, -1, &pSelect, 0)) == SQLITE_OK)
                    break;

                syslog(LOG_ERR, "Error Number: %d, msg: %s\n",  result, sqlite_errmsg(sql));

                sleep(2);
            }

            if (times < 0) {
                pSelect = NULL;
                syslog(LOG_ERR, "Can't run this query: %s",  query);
            }

            free(query);
        }

        return pSelect;
    }
    sqlite3_row my_sqlite_fetch_row(sqlite3_stmt *pSelect) {
        int i=0, nResult = -1;
        sqlite3_row row = NULL;
        char *p = NULL;

        if (pSelect && sqlite_step(pSelect) == SQLITE_ROW) {
            nResult = sqlite_column_count(pSelect);
            row = calloc(1, (SQLITE_ROW_MAX_MEMBER+1)*sizeof(char *));
            if (!row) return NULL;
            for(i=0; i<nResult && i<SQLITE_ROW_MAX_MEMBER; i++) {
                p = (char *)sqlite_column_text(pSelect, i);
                row[i] = p ? strdup(p) : NULL;
            }
            row[i] = NULL;
        }

        return row;
    }
    void _my_sqlite_free_row(char *_note, sqlite3_row row) {
        int i;
        for(i=0; i<SQLITE_ROW_MAX_MEMBER; i++) {
            free(row[i]);
        }
        free(row);
    }
    void _my_sqlite_finalize(char *_note, sqlite3_stmt *pSelect) {
        if (pSelect != NULL)
            sqlite_finalize(pSelect);
    }

使用方法

    sqlite3 *sql;
    sqlite3_stmt *pSelect;
    sqlite3_row row;
    int i;
        
    if (my_sqlite_connect(&sql, M("%saaa.db", SQLITE_DB_PATH)) != SQLITE_OK)
        return -1;

    if (my_sqlite_exec(sql, NULL, "create table tb_name(s1, v1, v2, s2);") == SQLITE_OK) {
        my_sqlite_exec(sql, NULL, "insert into tb_name values ('aaa', 118, 1991, 'bbb');");
        my_sqlite_exec(sql, NULL, "insert into tb_name values ('zzz', 153, 1997, 'yyy');");
    }
        
    pSelect = my_sqlite_query(sql, "select * from tb_name");
    while((row=my_sqlite_fetch_row(pSelect))) {
        for(i=0; row[i]; i++) {
            printf("---%s---\n", row[i]);
        }
        my_sqlite_free_row(row);
    }
    my_sqlite_finalize(pSelect);
        
    my_sqlite_close(sql);
        
    return 0;