為了能夠用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; // SQL Statement Object int ret = 0; int cols; // This routine opens a connection to an SQLite database file // and returns a database connection object. 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"; // This routine opens a connection to an SQLite database file // and returns a database connection object. 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; }
