SQLite Using C Library (2)

SQLite Using C Library(2)

顯示所有筆數資料

Callback方式

想要顯示出所有筆數資料就要利用 SELECT 再配合上callback的設定就能完成,程式碼如下:

    sqlStatement =  "SELECT * from stocks";
    returnCode = sqlite3_exec(pDb, sqlStatement, processRow, NULL, &errorMsg);
    if(returnCode!=SQLITE_OK) {
        fprintf(stderr, "Error in selecting from stocks table. Error: %s", errorMsg);
        sqlite3_free(errorMsg);
    }

順利的話,讀到每一筆資料時都會呼叫 processRow 這個Function,當您規畫callback時必的格式必需要遵照規定,名稱後必需要加上固定的參數:

(void *argument, int argc, char **argv, char **colName)

  • *argument 這參數是與 sqlite3_exec 倒數第2個參數呼應,如果內容為”123″,那callback得到的也會是”123″,不使用時直接輸入 “NULL”

  • argc 欄位數量,這是根據SELECT所選定的欄位的數量

  • **argv 欄位的內容,每次得到一筆資料時,會把取得的欄位資料傳入陣列中

  • **colName 欄位名稱,在建立每個欄位時都會有個名稱,此這是指這個名稱

static int processRow(void *argument, int argc, char **argv, char **colName){
    printf("Record Data:\n");
    for(int i=0; i<argc; i++){
        printf("The value for Column Name %s is equal to %s\n", colName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

結果:

-----------callback mode-----------
Record Data:
The value for Column Name symbol is equal to ALU
The value for Column Name purchasePrice is equal to 14.23
The value for Column Name unitsPurchased is equal to 100
The value for Column Name purchase_date is equal to 03-17-2012

Record Data:
The value for Column Name symbol is equal to GOOG
The value for Column Name purchasePrice is equal to 600.77002
The value for Column Name unitsPurchased is equal to 20
The value for Column Name purchase_date is equal to 01-09-2012

Record Data:
The value for Column Name symbol is equal to NT
The value for Column Name purchasePrice is equal to 20.23
The value for Column Name unitsPurchased is equal to 140
The value for Column Name purchase_date is equal to 02-05-2012

Record Data:
The value for Column Name symbol is equal to MSFT
The value for Column Name purchasePrice is equal to 30.23
The value for Column Name unitsPurchased is equal to 5
The value for Column Name purchase_date is equal to 01-03-2012

Record Data:
The value for Column Name symbol is equal to 中文測試!
The value for Column Name purchasePrice is equal to 30.23
The value for Column Name unitsPurchased is equal to 5
The value for Column Name purchase_date is equal to 01-03-2012

prepare方式

sqlite3_prepare_v2 對SQLite下資料庫指令,如果返回值是 SQLITE_OK 就可以使用 sqlite3_step 將查詢取出,再依照返回值來得知是否需要解析結果,如果 返回值 == SQLITE_ROW 代表執行的結果有資料,要取得每筆資料內容就要使用 sqlite3_column_資料型態欄位索引 ,依照資料型態會有下列這幾種Function:

SQLITE_API const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
SQLITE_API int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
SQLITE_API int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
SQLITE_API double sqlite3_column_double(sqlite3_stmt*, int iCol);
SQLITE_API int sqlite3_column_int(sqlite3_stmt*, int iCol);
SQLITE_API sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol);
SQLITE_API const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
SQLITE_API const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
SQLITE_API int sqlite3_column_type(sqlite3_stmt*, int iCol);
SQLITE_API sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);

每一筆數的每個欄位就可以看成陣例,依序就會有陣列的索引值,當然你要取得這欄位時,必需要知道它是哪型態的資料,再使用對映的Function將資料取出時也轉換型態,例如你的第1欄型態是整數值,那可能就會是:

int symbol = sqlite3_column_int(statement,0);

依照先前建立的資料,程式碼會是下面這樣:

    returnCode=sqlite3_prepare_v2(pDb, sqlStatement, strlen(sqlStatement), &statement, NULL);
    if(returnCode!=SQLITE_OK) {
        fprintf(stderr, "Error in selecting from stocks table. Error: %s", errorMsg);
        sqlite3_free(errorMsg);
    }
    returnCode = sqlite3_step(statement);
    while (returnCode == SQLITE_ROW){
        const unsigned char *symbol;
        int units;
        double price;
        symbol = sqlite3_column_text(statement,0);
        price = sqlite3_column_double(statement,1);
        units = sqlite3_column_int(statement,2);
        printf("We bought %d from %s at a price %.4f\n",units,symbol,price);
        returnCode = sqlite3_step(statement);
    }

結果:

-----------prepare mode-----------
We bought 100 from ALU at a price 14.2300
We bought 20 from GOOG at a price 600.7700
We bought 140 from NT at a price 20.2300
We bought 5 from MSFT at a price 30.2300
We bought 5 from 中文測試! at a price 30.2300

sqlStatement 中的SQL命令可以依照自已的查詢需求做改變,要注意如果SELECT中有選定欄位命令而不是萬用字元的話,會依照SELECT選項欄位的順序來決定索引值,例如:

sqlStatement =  "SELECT  stocks.symbol   stocks.purchasePrice from stocks";

這樣每筆結果時只會有2個欄位,索引值0則會是symbol,1則會是purchasePrice。

整個範例結果會一次將2種方法顯示在Log上,資料的內容是上一次介紹新增資料的內容

SQLite-Using-C-Library-2範例下載

結論

整個單元利用2個範例完成後就可以學會SQLite使用C Library方式存取。