An Introduction To The SQLite C/C++ Interface

為了能夠用SQL將資料由資料庫撈出，我們必須認識兩個主要的Object：

1. The database connection object：sqlite3
執行sqlite3_open()之後，就會得到該object，感覺類似open之後的file descriptor。

2. 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為：

1. sqlite3_open()：取得database connection object。
2. sqlite3_preapre() ：取得prepared statement object。
3. sqlite3_step()：執行prepared statement object。
4. sqlite3_column_<type>()：將sqlite回傳回來的資料，轉成對應的type取出。
5. sqlite3_finalize()：釋放prepared statement object。
6. 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;
}

參考資料：
• An Introduction To The SQLite C/C++ Interface
• One-Step Query Execution Interface