sqlite3取数据库结果

数据库结构：

# sqlite3 test.db ".schema think_test"
CREATE TABLE think_test(id integer primary key, name text, result text);

查询结果：

# sqlite3 test.db "select * from think_test"
1|zooyo|100
2|mars|90
3|moomoo|99
4|kendo|98

一次性取出全部数据

#include 
#include 

//#define DEBUG 1
#ifdef DEBUG
#define DEBUGP(format,args...)    fprintf(stdout, format, ##args)
#else
#define DEBUGP(format,args...)
#endif
#define PATH_IPWALL_DB        "/test/testdb.sqlite"
#define IPWALL_TBNAME    "think_test_test"

main(){
    sqlite3 *db;
    char sql[128];
    char **result;
    char *zErr;
    int nrows, ncols;
    int rc, i;

    //打开数据库
    rc = sqlite3_open(PATH_IPWALL_DB, &db);
    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return - 1;
    }
    
    //构造数据库执行语句
    snprintf(sql, sizeof(sql) - 1, "select * from %s;", IPWALL_TBNAME);    
    DEBUGP("sql=%s\n", sql);
    
    //取得数据库执行结果到 **result
    rc = sqlite3_get_table(db, sql, &result, &nrows, &ncols, &zErr);
    DEBUGP("row = %d, columns = %d\n", nrows, ncols);

    //打印数据库结果
    for (i = 0; i < nrows; i++) {
        fprintf(stdout, "name is %s\n", result[(i+1)*ncols + 1]);
    };
    sqlite3_close(db);
    return 0;
}

执行结果：

name is zooyo
name is mars
name is moomoo
name is kendo

setp模式,一次只取一行,这样效率得到提高,如果要打印全部数据则多次执行

#include 
#include 
#include 
#include 
   
#define PATH_IPWALL_DB "/test/test.db"
#define IPFLOWD_TBNAME "think_test"
   
int main(void) {
    sqlite3_stmt *stmt;
    sqlite3 *db;
    char sql[128];
    int rc, dir, ncols;
       
    /* 打开数据库 */
    rc = sqlite3_open(PATH_IPWALL_DB, &db);
    if (rc) {
        sqlite3_close(db);
        return - 1;
    }
   
    /* 构建查询语句 */
    snprintf(sql, sizeof(sql)-1, "select * from %s",
        IPFLOWD_TBNAME);
           
    /* 解析sqlite3命令,将stmt指针指向查询结果分配的动态内存 */
	if (sqlite3_prepare(db, sql, strlen(sql), &stmt, NULL) != SQLITE_OK) {
        fprintf(stderr, "Sqlit3 Exec Failure\n");
        exit(EXIT_FAILURE);
    }
   
    /* 获得执行的列数 */
    ncols = sqlite3_column_count(stmt);
   
    while(1) { 
        /* 执行sqlite3命令,一次只取一行数据 */
        rc = sqlite3_step(stmt);
        /* 返回SQLITE_ROW表示查询到数据
           返回SQLITE_DONE表示查询完成 */
        if (rc != SQLITE_ROW)    break;
        fprintf(stdout, "Ncols: %d\n", ncols);
        /* 读取字段内容, 如果内容为整数就用sqlite3_column_int
           如果为字符串就用sqlite3_column_text */
        fprintf(stdout, "ID: %d\n", sqlite3_column_int(stmt, 0));
        fprintf(stdout, "Name: %s\n", sqlite3_column_text(stmt, 1));
        fprintf(stdout, "Result: %s\n\n", sqlite3_column_text(stmt, 2));
    }
   
    /* 释放 */
    sqlite3_finalize(stmt);
    sqlite3_close(db);
   
    return(EXIT_SUCCESS);
}

查询结果:

Ncols: 3
ID: 1
Name: zooyo
Result: 100

Ncols: 3
ID: 2
Name: mars
Result: 90

Ncols: 3
ID: 3
Name: moomoo
Result: 99

Ncols: 3
ID: 4
Name: kendo
Result: 98