今天在学习一个项目的时候遇到了sqlite的使用。我就潜心研究了下。先在c平台下进行了S实验。成果如下

1.创建一个table，操作很简单，open，exec，close即可囊括全部。

代码如下：

#include <stdio.h>
#include <stdlib.h>
#include "util.h"
#include <sqlite3.h>

//#pragma commect(lib,"sqlite3.lib")

int main(int argc,const char * argv[])
{
    
    sqlite3 *db;
    char *zErr;
    int rc;
    char *sql;
    
    rc = sqlite3_open("/Users/lichan/Desktop/test1.db",&db);
    
    if (rc) {
        fprintf(stderr,"cant open db %s \n",sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(1);
    }
    
    sql = "create table episodes(name text,id int)";
    
    rc = sqlite3_exec(db,sql,NULL,&zErr);
    
    if (rc != sqlITE_OK) {
        if (zErr != NULL) {
            fprintf(stderr,"sql error:%s\n",zErr);
            sqlite3_free(zErr);
        }
    }
    
    sql = "insert into episodes(name,id) values('lichan',1)";
    
    rc = sqlite3_exec(db,&zErr);
    
    if (rc != sqlITE_OK) {
        fprintf(stderr,zErr);
        sqlite3_free(zErr);
    };
    
    sqlite3_close(db);
    
    //创建完成！
    
    
    

    // insert code here...
    printf("Hello,World!\n");
    return 0;
}

我只是通过代码写了第一行，其余的两行是我通过客户端写进去的。方便下面的使用

2.下面看看关于table内容的打印输出。

关于查询所有行的方法也比较简单，open，prepared，step（loop） close

//
//  main.c
//  sqlite3查询
//
//  Created by lichan on 13-12-14.
//  Copyright (c) 2013年 lichan. All rights reserved.
//

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <string.h>

int main(int argc,const char * argv[])
{
    char *path = "/Users/lichan/Desktop/test1.db";
    
    int rc,i,ncols;
    sqlite3 *db;
    sqlite3_stmt *stmt;
    char *sql;
    const char *tail;
    
    rc = sqlite3_open(path,&db);
    if (rc) {
        fprintf(stderr,"cant  open db %s \n",sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(1);
    }
    
    sql = "select *from episodes;";
    
    rc = sqlite3_prepare(db,(int)strlen(sql),&stmt,&tail);
    if (rc != sqlITE_OK) {
        fprintf(stderr,"sql err %s \n",sqlite3_errmsg(db));
    }
    
    rc = sqlite3_step(stmt);
    ncols = sqlite3_column_count(stmt);//计算所有的行数。
    
    while (rc == sqlITE_ROW) {
        for (i = 0; i < ncols; i++) {
                   fprintf(stderr," %s \n",sqlite3_column_text(stmt,i));
        }
        
        fprintf(stderr,"\n");
        rc = sqlite3_step(stmt);
    }
    
    sqlite3_finalize(stmt);

/*

** The application must finalize every [prepared statement] in order to avoid
** resource leaks. It is a grievous error for the application to try to use
** a prepared statement after it has been finalized. Any use of a prepared
** statement after it has been finalized can result in undefined and
** undesirable behavior such as segfaults and heap corruption.

*/
    sqlite3_close(db);
    
    

    // insert code here...
    printf("Hello,World!\n");
    return 0;
}

lichan

1

xuna

2

zahomingwei

3

Hello,World!

Program ended with exit code: 0