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上，资料的内容是上一次介绍新增资料的内容
结论
整个单元利用2个范例完成后就可以学会SQLite使用C Library方式存取。