5.c语言操作sqlite3数据库

c语言操作sqlite3数据库

1.连接数据库

c库接口

#include <sqlite3.h>
//const char *filename	：数据库 xxx.db
//sqlite3 **ppDb		：一个数据库类型的二级指针
sqlite3_open(const char *filename, sqlite3 **ppDb);//打开数据库

sqlite3_close(sqlite3 *db);							//关闭数据库

sqlite_errmsg(sqlite3 *db);							//查看数据库异常信息

openSqlite.c

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

int main(char argc, char **argv)
{
        sqlite3 *db;
        int ret;
        if(argc < 2){
                printf("Usage: %s xxx.db \n",argv[0]);
                return -1;
        }
        if((ret = sqlite3_open(argv[1],&db)) == SQLITE_OK){
                printf("open %s success\n",argv[1]);
        }else{
                printf("error: %s,%d \n",sqlite3_errmsg(db),ret);
                if(ret == 14){
                        printf("perission den\n");
                }
                return -1;
        }
        sqlite3_close(db);
        printf("done\n");
        return 0;
}

编译：

gcc openSqlite.c -lsqlite3

执行：new.db没有则创建

./a.out new.db

./a.out: error while loading shared libraries: libsqlite3.so: cannot open sh问题解决

安装缺失库：

sudo apt-get update
sudo apt-get install libsqlite3-0

2.创建表\插入\查询

c库接口

//const char *sql	：要执行的sql语句
//sqlite_callback	：执行完成的回调函数
//void *data		：传入的参数
//char **errmsg		：返回的异常
sqlite3_exec(sqlite3 *db, const char *sql, callback, void *data, char **errmsg);

//void *arg		：是sqlite3_exec的第四个参数
//column_size	：数据库字段数
//column_value	：列的值
//column_name	：字段名称
callback原型：
static int callback(void *arg, int column_size, char *column_value[], char *column_name[]);

createSqlite.c

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

static int callback(void *arg, int column_size, char *column_value[], char *column_name[])
{
        //printf("column size : %d \n",column_size);
        printf("arg:%s\n",(char *)arg);
        for(int i = 0; i < column_size; i++){
                printf("%s = %s\n",column_name[i],column_value[i]);
        }
        printf("====================\n");
        return 0;//必须返回0，有多少条，执行回调函数多少次
}

int main(char argc, char **argv)
{
        sqlite3 *db;
        char *errMsg = NULL;
        char *sql;
        int ret;
        if(argc < 2){
                printf("Usage: %s xxx.db \n",argv[0]);
                return -1;
        }
        if((ret = sqlite3_open(argv[1],&db)) == SQLITE_OK){
                printf("open %s success\n",argv[1]);
        }else{
                printf("error: %s,%d \n",sqlite3_errmsg(db),ret);
                if(ret == 14){
                        printf("perission den\n");
                }
                return -1;
        }
        //sqlite3_exec(sqlite3 *db, const char *sql, sqlite_callback, void *data, char **errmsg);
        sql = "create table class01("\
                   "cls_id integer,"\
                   "cls_name char,"\
                   "cls_grade intger"\
                   ")";
        ret = sqlite3_exec(db,sql,callback,0,&errMsg);
        if(ret != SQLITE_OK){
                printf("create table error: %s\n",errMsg);
        }
        sql = "insert into class01 values (1,'gansan',3)";
        ret = sqlite3_exec(db,sql,callback,0,&errMsg);
        if(ret != SQLITE_OK){
                printf("insert table error: %s\n",errMsg);
        }

        sql = "select * from class01";
        ret = sqlite3_exec(db,sql,callback,0,&errMsg);
        if(ret != SQLITE_OK){
                printf("select table error: %s\n",errMsg);
        }
        sqlite3_close(db);
        printf("done\n");
        return 0;
}