嵌入式数据库开发编程（六）——C API-阿里云开发者社区

嵌入式数据库开发编程（六）——C API

2023-01-31 82

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 嵌入式数据库开发编程（六）——C API

一、打开、关闭和错误处理

错误处理

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
int main(int argc, char const *argv[])
{
    if (argc != 2)
    {
        printf("Please input db name\n");
        exit(1);
    }
    sqlite3 *db;
    int ret = sqlite3_open(argv[1], &db);
    if(ret != SQLITE_OK)
    {
        printf("sqlite3 open:%s",sqlite3_errmsg(db));
        exit(1);
    }
    printf("sqlite open db successful!\n");
    sqlite3_close(db);
    return 0;
}

二、执行sql

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
void print_error(int ret, char *err, sqlite3 *db)
{
    if (ret != SQLITE_OK)
    {
        printf("%s:%s\n",err, sqlite3_errmsg(db));
        exit(1);
    }
}
int main(int argc, char const *argv[])
{
    if (argc != 2)
    {
        printf("Please input db name\n");
        exit(1);
    }
    sqlite3 *db;
    char *errmsg;
    char sql[1024] = {0};
    int ret = sqlite3_open(argv[1], &db);
    print_error(ret, "sqlite_open",db);
    printf("sqlite open db successful!\n");
    memset(sql, 0, sizeof(sql));
    strcpy(sql, "create table IF NOT EXISTS student (integer primary key, name text, age integer)");
    ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    print_error(ret, "sqlite exec create table", db);
    sqlite3_close(db);
    return 0;
}

输入sqlitebrower test.db

这样就创建成功了，如果没有sqlitebrower

输入：sudo apt-get install sqlitebrower

三、封装sql

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
void print_error(int ret, char *err, sqlite3 *db)
{
    if (ret != SQLITE_OK)
    {
        printf("%s:%s\n",err, sqlite3_errmsg(db));
        exit(1);
    }
}
int main(int argc, char const *argv[])
{
    if (argc != 2)
    {
        printf("Please input db name\n");
        exit(1);
    }
    sqlite3 *db;
    char *errmsg;
    char sql[1024] = {0};
    int id;
    char name[32];
    int age;
    int ret = sqlite3_open(argv[1], &db);
    print_error(ret, "sqlite_open",db);
    printf("sqlite open db successful!\n");
    memset(sql, 0, sizeof(sql));
    strcpy(sql, "create table IF NOT EXISTS student (id integer primary key, name text, age integer)");
    ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    print_error(ret, "sqlite exec create table", db);
    //插入2行数据：id,name,age 键盘输入
    for (size_t i = 0; i < 2; i++)
    {
        printf("Please input id:\n");
        scanf("%d", &id);
        printf("Please input name:\n");
        scanf("%s", name);
        printf("Please input age:\n");
        scanf("%d", &age);
        //sql:insert into student(id, name, age) values()
        //sprintf(); fprintf
        memset(sql, 0, sizeof(sql));
        sprintf(sql, "insert into student(id, name, age) values(%d, '%s', %d)",id, name, age);
        printf("%s\n",sql);
        ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
        print_error(ret, "insert into", db);
    }
    //删除
    memset(sql, 0, sizeof(sql));
    printf("Please input delete name:\n");
    scanf("%s", name);
    sprintf(sql, "delete from student where name = '%s'", name);
    ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    print_error(ret, "delete", db);
    sqlite3_close(db);
    return 0;
}

四、回调函数

行缓冲

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
void print_error(int ret, char *err, sqlite3 *db)
{
    if (ret != SQLITE_OK)
    {
        printf("%s:%s\n",err, sqlite3_errmsg(db));
        exit(1);
    }
}
//放在指针数组里面了
int my_sqlite_callback(void *para, int columnCount, char **columnValue, char **columnName)
{
    int flag = *((int *)para);
    printf("flag = %d\n",flag);
    printf("columbCount = %d\n", columnCount);
    for (size_t i = 0; i < columnCount; i++)
    {
        printf("%s:%s|",columnName[i] ,columnValue[i]);
    }
    printf("\n");
    return 0;
}
int main(int argc, char const *argv[])
{
    if (argc != 2)
    {
        printf("Please input db name\n");
        exit(1);
    }
    sqlite3 *db;
    char *errmsg;
    char sql[1024] = {0};
    int id;
    char name[32];
    int age;
    int ret = sqlite3_open(argv[1], &db);
    print_error(ret, "sqlite_open",db);
    printf("sqlite open db successful!\n");
    memset(sql, 0, sizeof(sql));
    strcpy(sql, "create table IF NOT EXISTS student (id integer primary key, name text, age integer)");
    ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    print_error(ret, "sqlite exec create table", db);
    //插入2行数据：id,name,age 键盘输入
#if 0
    for (size_t i = 0; i < 1; i++)
    {
        printf("Please input id:\n");
        scanf("%d", &id);
        printf("Please input name:\n");
        scanf("%s", name);
        printf("Please input age:\n");
        scanf("%d", &age);
        //sql:insert into student(id, name, age) values()
        //sprintf(); fprintf
        memset(sql, 0, sizeof(sql));
        sprintf(sql, "insert into student(id, name, age) values(%d, '%s', %d)",id, name, age);
        printf("%s\n",sql);
        ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
        print_error(ret, "insert into", db);
    }
    //删除
    memset(sql, 0, sizeof(sql));
    printf("Please input delete name:\n");
    scanf("%s", name);
    sprintf(sql, "delete from student where name = '%s'", name);
    ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    print_error(ret, "delete", db);
#endif
    memset(sql, 0, sizeof(sql));
    strcpy(sql, "select * from student");
    int flag = 0;
    ret = sqlite3_exec(db, sql, my_sqlite_callback, (void *)&flag, &errmsg);
    //有多少行，就会执行所少次
    print_error(ret, "select", db);
    sqlite3_close(db);
    //flag无法在函数my_sqlite_callback中使用
    printf("flag = %d\n",flag);
    //不是select的时候，callback不作用
    return 0;
}

五、全缓冲查询

全局缓冲

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
void print_error(int ret, char *err, sqlite3 *db)
{
    if (ret != SQLITE_OK)
    {
        printf("%s:%s\n",err, sqlite3_errmsg(db));
        exit(1);
    }
}
//放在指针数组里面了
int my_sqlite_callback(void *para, int columnCount, char **columnValue, char **columnName)
{
    int flag = *((int *)para);
    printf("flag = %d\n",flag);
    printf("columbCount = %d\n", columnCount);
    for (size_t i = 0; i < columnCount; i++)
    {
        printf("%s:%s|",columnName[i] ,columnValue[i]);
    }
    printf("\n");
    return 0;
}
int main(int argc, char const *argv[])
{
    if (argc != 2)
    {
        printf("Please input db name\n");
        exit(1);
    }
    sqlite3 *db;
    char *errmsg;
    char sql[1024] = {0};
    int id;
    char name[32];
    int age;
    int ret = sqlite3_open(argv[1], &db);
    print_error(ret, "sqlite_open",db);
    printf("sqlite open db successful!\n");
    memset(sql, 0, sizeof(sql));
    strcpy(sql, "create table IF NOT EXISTS student (id integer primary key, name text, age integer)");
    ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    print_error(ret, "sqlite exec create table", db);
    //插入2行数据：id,name,age 键盘输入
#if 0
    for (size_t i = 0; i < 1; i++)
    {
        printf("Please input id:\n");
        scanf("%d", &id);
        printf("Please input name:\n");
        scanf("%s", name);
        printf("Please input age:\n");
        scanf("%d", &age);
        //sql:insert into student(id, name, age) values()
        //sprintf(); fprintf
        memset(sql, 0, sizeof(sql));
        sprintf(sql, "insert into student(id, name, age) values(%d, '%s', %d)",id, name, age);
        printf("%s\n",sql);
        ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
        print_error(ret, "insert into", db);
    }
    //删除
    memset(sql, 0, sizeof(sql));
    printf("Please input delete name:\n");
    scanf("%s", name);
    sprintf(sql, "delete from student where name = '%s'", name);
    ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    print_error(ret, "delete", db);
#endif
#if 0
    memset(sql, 0, sizeof(sql));
    strcpy(sql, "select * from student");
    int flag = 0;
    ret = sqlite3_exec(db, sql, my_sqlite_callback, (void *)&flag, &errmsg);
    //有多少行，就会执行所少次
    print_error(ret, "select", db);
    //flag无法在函数my_sqlite_callback中使用
    printf("flag = %d\n",flag);
    //不是select的时候，callback不作用
#endif
    char **result;
    int nrow;
    int ncolumn;
    memset(sql, 0, sizeof(sql));
    strcpy(sql, "select * from student");
    ret = sqlite3_get_table(db, sql, &result, &nrow, &ncolumn, &errmsg);
    //从1开始遍历，要是从0的话，列名也会打印出来
    for (size_t i = 1; i <= nrow; i++)
    {
        for (size_t j = 0; j < ncolumn; j++)
        {
            printf("%s|", result[i * ncolumn + j]);
        }
        printf("\n");
    }
    sqlite3_close(db);
    return 0;
}