sqlite3 linux c語言 基本操作

1.建立資料庫，及建立表格

#define CAN_SQLDB_FILE     "/home/root/can.db3"
/************************************************************
 *函數說明
 *      初始化資料sqlite3
 *參數
 *      
 *
 *傳回值
 *      ret： 0成功
 *備注
 *
 **************************************************************/
static int init_sqlite(void)
{
    int ret = ;
    char *sql_cmd = NULL;
    char *zErrMsg = NULL;
    char exit_symbol = ;

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

    /*打開或建立資料庫*/
    ret = sqlite3_open(CAN_SQLDB_FILE, &(g_caninfo.pCanDb));
    if ( != ret) {
        printf("######sqlite3 open or creat error! \n");
        return ret;     
    }

    /*第一次建立sqlite，需要建立表格， 否則，不建立表格*/
    if ( != exit_symbol) {
        /*CREATE TABLE 語句: 表格項定義*/
        sql_cmd = "CREATE TABLE CANDATA ("  \
             "ID integer," \
             "TIME integer," \
             "DATA blob);";

        /*建立表格*/
        ret = sqlite3_exec(g_caninfo.pCanDb, sql_cmd, NULL, , &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 = , time, retry = ;

    /*預編譯流程*/

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

    /*擷取儲存時的系統時間 自定義函數*/
    get_date_time(cur_time, &time);

    /*綁定預編譯字段的值*/
    rc = sqlite3_bind_int( stat, , id); 
    if (SQLITE_OK != rc) 
        return rc;

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

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

    /*執行SQL語句*/
    rc = sqlite3_step(stat);
    while ((rc == SQLITE_BUSY) && retry--) {
        usleep();
        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 = ;
    static char col = ;

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

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

    index = ;

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

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

        /*得到所在列的位元組數*/
        len = sqlite3_column_bytes(stat, ); 

        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 = ; i < len; i++) {
            if (col == ) {
                col = ;
                printf("\n");
            }   
            col++;
            my_debug("index[%d] = 0x%x. ", i, buf[i]);
        }   
        col = ;
        my_debug("\n");

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

    sqlite3_finalize(stat);
}