linux下sqlite3在c语言中应用实例

柔情只为你懂 2022-07-15 04:59 188阅读 0赞

/*
    **************************************************************************
    * File Name: 1.sqlite3.c
    * Description : 1)数据插入，删除，清空  
    *               2) 
    * Author   : Xubing 
    * Created Time: 2016年10月30日 星期日
    **************************************************************************
    */
    
    #include <stdio.h>
    #include <sqlite3.h>
    
    static sqlite3 *db = NULL;
    static char *errmsg = NULL;
    
    int main()
    {
        int ret;
        int insert;
        
        ret = sqlite3_open("test.db", &db);
        if(ret)
        {
            printf("can not open database.\n");
        }
        else
        {
            printf("open database succsee.\n");
        }
        insert =  sqlite3_exec(db, "insert into test_table values('wujibing',111,222)", 0, 0, &errmsg);//插入
        //insert = sqlite3_exec(db, "delete from test_table where name='xu'", 0, 0, &errmsg);//删除
        //insert = sqlite3_exec(db, "delete from test_table", 0, 0, &errmsg);//清空
        printf("exec_ret: %d\n", insert);
        printf("errMsg: %d\n", errmsg);
        sqlite3_close(db);
    
        return 0;
    }

/*
    **************************************************************************
    * File Name: 2.sqlite3_display.c
    * Description : 1) 显示数据 
    *               2) 
    * Author   : Xubing 
    * Created Time: 2016年10月30日 星期日 16时30分17秒
    **************************************************************************
    */
    
    #include <stdio.h>
    #include <sqlite3.h>
    
    static sqlite3 *db = NULL;
    static char **Result = NULL;
    static char *errmsg = NULL;
    
    int main()
    {
        int ret;
        int i;
        int j;
        int nrow;
        int ncolumn;
    
        ret = sqlite3_open("test.db", &db);
    //    sqlite3_exec(db, ".head on", 0, 0, &errmsg);
        ret = sqlite3_get_table(db, "select * from test_table", &Result, &nrow, &ncolumn, &errmsg);
        if(ret)
        {
            printf("can not open database.\n");
        }
        else
        {
            printf("open database success.\n");
            for(i = 0; i <= nrow; i++)
            {
                for(j = 0; j < ncolumn; j++)
                {
                    printf("%s|", Result[i * ncolumn + j]);
                }
                printf("\n");
            }
        }
        sqlite3_free_table(Result);
        sqlite3_close(db);
    
        return 0;
    }

/*
    **************************************************************************
    * File Name: 3.sqlite3_callback.c
    * Description : 1) 回调函数完成数据库功能 
    *               2) 
    * Author   : Xubing 
    * Created Time: 2016年10月30日 星期日 17时57分53秒
    **************************************************************************
    */
    
    #include <stdio.h>
    #include <sqlite3.h>
    #include <stdlib.h>
    
    static sqlite3 *db;
    
    void create_table(sqlite3 *db)
    {
        char *sql = NULL;
        char *errmsg = NULL;
        int ret;
    
        sql = "create table if not exists mytable (id integer primary key, name text)";
        ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
        if(ret != SQLITE_OK)
        {
             printf("create table error: %s\n", errmsg);
        }
    
    }
    
    void insert_record(sqlite3 *db)
    {
        char sql[100];
        char *errmsg = NULL;
    //    char name[20];
        int name;
        
        int ret;
        int id;
        
        printf("Please input phone1 and phone2:\n");
        printf("phone1: ");
        scanf("%d", &id);
        getchar();
        printf("name: ");
        scanf("%d", &name);
        getchar();
    
        sprintf(sql, "insert into test_table values(%d, %d)", id, name);
    
        ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
        if(ret != SQLITE_OK)
        {
            printf("insert table error: %s\n", errmsg);
        }
    }
    
    int displaycb(void *para, int ncolumn, char ** columnvalue, char *columnname[])
    {
        int i;
    
        printf("total column is %d\n", ncolumn);
    
        for(i = 0; i < ncolumn; i++)
        {
            printf("col_name: %s-----> col_value: %s\n", columnname[i], columnvalue[i]);
            printf("============================\n");
    
        }
        return 0;
    }
    
    void inquire_usecb(sqlite3 *db)
    {
        char *sql = NULL;
        char *errmsg = NULL;
        int ret;
    
        sql = "select * from mytable";
        ret = sqlite3_exec(db, sql, displaycb, NULL, &errmsg);
        if(ret != SQLITE_OK)
        {
            printf("select is error: %s\n", errmsg);
        }
    }
    
    int main()
    {
        int ret;
    
        ret = sqlite3_open("test.db", &db);
    
        sqlite3_exec(db,"select * from test_table",displaycb, NULL, NULL);
        insert_record(db);
        sqlite3_close(db);
    
        return 0;
    }