sqlite3 linux c语言 基本操作

1.创建数据库，及建立表格

 #define CAN_SQLDB_FILE     "/home/root/can.db3"
/************************************************************
 *函数说明
 *      初始化数据sqlite3
 *参数
 *      
 *
 *返回值
 *      ret： 0成功
 *备注
 *
 **************************************************************/
static int init_sqlite(void)
{
    int ret = 0;
    char *sql_cmd = NULL;
    char *zErrMsg = NULL;
    char exit_symbol = 0;

    /*判断文件是否存在*/
    exit_symbol = access(CAN_SQLDB_FILE, F_OK);
    if (0 == exit_symbol) {
        printf("######there have sqlite3 ! \n");
    }

    /*打开或创建数据库*/
    ret = sqlite3_open(CAN_SQLDB_FILE, &(g_caninfo.pCanDb));
    if (0 != ret) {
        printf("######sqlite3 open or creat error! \n");
        return ret;     
    }

    /*第一次创建sqlite，需要创建表格， 否则，不创建表格*/
    if (0 != exit_symbol) {
        /*CREATE TABLE 语句: 表格项定义*/
        sql_cmd = "CREATE TABLE CANDATA ("  \
             "ID integer," \
             "TIME integer," \
             "DATA blob);";

        /*创建表格*/
        ret = sqlite3_exec(g_caninfo.pCanDb, sql_cmd, NULL, 0, &zErrMsg);
        if ( ret != SQLITE_OK ) {
            printf("SQL error: %s \n", zErrMsg);
            sqlite3_free(zErrMsg);
        } else {
            printf("Table created successfully \n");
        }
    }

    return ret;
}

2.反初始化操作

/************************************************************
 *函数说明
 *      反初始化数据sqlite3
 *参数
 *      
 *
 *返回值
 *      ret： 0成功
 *备注
 *
 **************************************************************/
static int uninit_sqlite()
{
    return sqlite3_close(g_caninfo.pCanDb);
}

3.插入数据操作

/************************************************************
 *函数说明
 *      插入数据
 *参数
 *      
 *
 *返回值
 *      
 *备注
 *
 **************************************************************/
static int insert_sqlite_data(int id, struct timeval cur_time, char* buf, int len)
{
    sqlite3_stmt *stat;
    int rc = 0, time, retry = 5;

    /*预编译流程*/

    /*创建一个sqlite3_stmt对象*/
    rc = sqlite3_prepare( g_caninfo.pCanDb, "insert into CANDATA( ID, TIME, 
                         DATA) values( ?, ?, ? )", -1, &stat, 0);
    if (rc != SQLITE_OK) {
        printf("ERROR: insert prepare failed!\n");
        return rc;
    }

    /*获取保存时的系统时间 自定义函数*/
    get_date_time(cur_time, &time);

    /*绑定预编译字段的值*/
    rc = sqlite3_bind_int( stat, 1, id); 
    if (SQLITE_OK != rc) 
        return rc;

    rc = sqlite3_bind_int( stat, 2, time); 
    if (SQLITE_OK != rc) 
        return rc;

    rc = sqlite3_bind_blob( stat, 3, buf, len,  NULL ); 
    if (SQLITE_OK != rc) 
        return rc;

    /*执行SQL语句*/
    rc = sqlite3_step(stat);
    while ((rc == SQLITE_BUSY) && retry--) {
        usleep(200);
        rc = sqlite3_step(stat);
    }

    if ((rc != SQLITE_ROW) && (rc != SQLITE_OK) && (rc != SQLITE_DONE))
        return rc;

    /*销毁资源*/
    rc = sqlite3_finalize(stat);

    return rc;
} 

4 . 删除操作

/************************************************************
 *函数说明
 *      删除数据
 *参数
 *      
 *
 *返回值
 *      
 *备注
 *
 **************************************************************/
static int delete_sqlite_data(int id)
{
    char seq_cmd[128];
    int rc=0;
    char *zErrMsg;

    /*删除指定id行*/
    sprintf(seq_cmd, "DELETE from CANDATA where ID=%d; ", id);
    rc = sqlite3_exec(g_caninfo.pCanDb, seq_cmd, NULL, 0, &zErrMsg);

    if( rc != SQLITE_OK ){
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        diagnosis_err(rc);
    }

    return rc;
} 

5.显示数据库数据

void show_sqlite_data()
{
    static int index = 0;
    static char col = 0;

    sqlite3_stmt * stat;
    int rc, id, time, len, i;
    char buf[128];
    char *pdata = buf;

    /*插入10条才显示一次*/
    if (index++ < 10)
        return;

    index = 0;

    rc = sqlite3_prepare(g_caninfo.pCanDb, "select * from CANDATA", -1, &stat, 0);
    if (rc != SQLITE_OK)
        printf("ERROR:  show sqlite data prepare !\n");

    rc = sqlite3_step(stat);
    while (rc == SQLITE_ROW) {
        id = sqlite3_column_int(stat, 0);
        time = sqlite3_column_int(stat, 1);
        pdata = sqlite3_column_blob(stat, 2);

        /*得到所在列的字节数*/
        len = sqlite3_column_bytes(stat, 2); 

        memcpy(buf, pdata, len);

        my_debug("\n show sqlite begin, len = %d, \n", len);
        my_debug("id = %d, time=0x%x \n", id, time);
        /*打印blob值*/
        for (i = 0; i < len; i++) {
            if (col == 10) {
                col = 0;
                printf("\n");
            }   
            col++;
            my_debug("index[%d] = 0x%x. ", i, buf[i]);
        }   
        col = 0;
        my_debug("\n");

        memset(buf, 128, 0);    
        rc = sqlite3_step(stat);
    }

    sqlite3_finalize(stat);
}