為了能夠用SQL將資料由資料庫撈出，我們必須認識兩個主要的Object：
- The database connection object：sqlite3
執行sqlite3_open()之後，就會得到該object，感覺類似open之後的file descriptor。
- The prepared statement object：sqlite3_stmt
把SQL轉成bytecode，但是還沒真正被執行。請參考Compiling An SQL Statement/To execute an SQL query, it must first be compiled into a byte-code program using one of these routines.
An instance of this object represents a single SQL statement. This object is variously known as a "prepared statement" or a "compiled SQL statement" or simply as a "statement".
一個常見的SQLite pattern為：
- sqlite3_open()：取得database connection object。
- sqlite3_preapre() ：取得prepared statement object。
- sqlite3_step()：執行prepared statement object。
- sqlite3_column_<type>()：將sqlite回傳回來的資料，轉成對應的type取出。
- sqlite3_finalize()：釋放prepared statement object。
- sqlite3_close()：釋放database connection object。
#include <iostream>
#include <sqlite3.h>
#include <cstdlib>
#include <assert.h>
using namespace std;
int main(int argc, char** argv)
{
sqlite3 *conn;
sqlite3_stmt *statement;
int ret = 0;
int cols;
ret = sqlite3_open_v2("hello.db", &conn, SQLITE_OPEN_READONLY, NULL);
if (ret) {
cout << "can not open database\n";
exit(0);
}
ret = sqlite3_prepare_v2(conn, "select * from hello", -1, &statement, NULL);
if (ret != SQLITE_OK) {
cout << "We did not get any data\n";
exit(0);
}
cols = sqlite3_column_count(statement);
for (int col = 0; col < cols; col++) {
cout << " " << sqlite3_column_name(statement, col);
};
cout << endl;
while (true) {
ret = sqlite3_step(statement);
if (ret == SQLITE_ROW) {
for (int col = 0; col < cols; col++) {
switch (sqlite3_column_type(statement, col)) {
case SQLITE_INTEGER:
cout << " " << sqlite3_column_int(statement, col) << " ";
break;
case SQLITE_FLOAT:
cout << " " << sqlite3_column_double(statement, col) << " ";
break;
case SQLITE_TEXT:
cout << " " << sqlite3_column_text(statement, col) << " ";
break;
case SQLITE_NULL:
cout << " " << "NULL" << " ";
break;
}
};
cout << endl;
} else if (ret == SQLITE_DONE) {
cout << "done" << endl;
break;
} else {
cout << "ret:" << ret << endl;
break;
}
}
sqlite3_finalize(statement);
sqlite3_close(conn);
return 0;
}
sqlite3_exec()是將sqlite3_prepare_v2(), sqlite3_step()和 sqlite3_finalize()包裝起來的API，其原型為
int sqlite3_exec(
sqlite3*, /* An open database */
const char *sql, /* SQL to be evaluated */
int (*callback)(void*,int,char**,char**), /* Callback function */
void *, /* 1st argument to callback */
char **errmsg /* Error msg written here */
);
- 第二個參數要執行的SQL statement。
- 第三個參數是callback function。每一個row都會呼叫該callback一次。
- 第四個參數是要傳給callback function的data。
- 第五個參數就是發生錯誤時，儲存錯誤資訊的指標。
因此上面的程式可以改寫為
#include <iostream>
#include <sqlite3.h>
using namespace std;
int callback(void *data, int column, char **value, char **name)
{
int i;
cout << (char *)data << endl;
for (i = 0; i < column; i++) {
cout << name[i] << ":" << (value[i] ?: "NULL") << endl << flush;
}
return 0;
}
int main(int argc, char** argv)
{
sqlite3 *conn;
int ret = 0;
char *errmsg = NULL;
char data[] = "brook";
ret = sqlite3_open_v2("hello.db", &conn, SQLITE_OPEN_READONLY, NULL);
if (ret != SQLITE_OK) {
cout << sqlite3_errmsg(conn) << ".(" << ret << ")" << endl;
exit(0);
}
ret = sqlite3_exec(conn, "select * from hello", callback, data, &errmsg);
if (ret != SQLITE_OK) {
cout << "We did not get any data. " << errmsg << endl;
sqlite3_free(errmsg);
}
sqlite3_close(conn);
return 0;
}
