2012年8月4日 星期六

An Introduction To The SQLite C/C++ Interface


為了能夠用SQL將資料由資料庫撈出,我們必須認識兩個主要的Object:
  1. The database connection object:sqlite3
  2. 執行sqlite3_open()之後,就會得到該object,感覺類似open之後的file descriptor。
  3. The prepared statement object:sqlite3_stmt
  4. 把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;
}






熱門文章