sqlite3のexample

ちょっとsqlite3をWindowsAPIアプリで使ってみる場合の自分用覚書です。

オープン

// sqlite3* db;
bool dbopen(char* filename)
{
  int ret;

  ret=sqlite3_open_v2(filename, &db, SQLITE_OPEN_READWRITE| SQLITE_OPEN_CREATE| SQLITE_OPEN_FULLMUTEX, NULL);
  if(ret!=SQLITE_OK){
    dbclose();
    return false;
  }

  ret=sqlite3_exec(db,
                   "CREATE TABLE IF NOT EXISTS datatable("
                   "id INTEGER primary key,"
                   "updatetime INTEGER,"
                   "name TEXT(64),"
                   "value1 REAL,"
                   "value2 INTEGER);",
                   NULL, NULL, NULL); // callback_function, callback_argument, err_message
  if(ret!=SQLITE_OK){
    dbclose();
    return false;
  }
  return true;
}

クローズ

// sqlite3* db;
void dbclose()
{
  if(db!=NULL){
    sqlite3_close(db);
    db=NULL;
  }
}

アップサート

// sqlite3* db;
bool dbupsert(DATASTRUCT* ci)
{
  if(db==NULL) return false;
  if(ci->id==0) return false;

  int ret;
  sqlite3_stmt *stmt=NULL;
  bool store_data=false;

  ret=sqlite3_exec(db, "BEGIN;", NULL, NULL, NULL);
  {
    ret=sqlite3_prepare_v2(db,
                           "REPLACE INTO datatable("
                           "id, updatetime, name, value1, value2) "
                           "VALUES("
                           "?, ?, ?, ?, ?");",
                           -1, &stmt, NULL);

    if((ret==SQLITE_OK)&&(stmt!=NULL)){
      sqlite3_reset(stmt);

      // bind is 1base
      sqlite3_clear_bindings(stmt);
      sqlite3_bind_int64    (stmt,  1, ci->id); 
      sqlite3_bind_int      (stmt,  2, ci->updatetime);
      sqlite3_bind_text     (stmt,  3, ci->name, (int)strlen(ci->name), SQLITE_TRANSIENT);
      sqlite3_bind_double   (stmt,  4, ci->value1);
      sqlite3_bind_int      (stmt,  5, ci->value2);

      int iret=sqlite3_step(stmt);
      if(iret==SQLITE_DONE) store_data=true;

      sqlite3_finalize(stmt);
    }
  }
  sqlite3_exec(db, "COMMIT;", NULL, NULL, NULL);

  return store_data;
}

セレクト

// sqlite3* db;
bool dbselect(DATASTRUCT* ci)
{
  if(db==NULL) return false;
  if(ci->id==0) return false;

  int ret;
  sqlite3_stmt *stmt=NULL;
  bool aquire_data=false;

  char sqltxt[256];
  sprintf(sqltxt,
          "SELECT "
          "updatetime,"
          "value2 "
          "FROM datatable "
          "WHERE id=%llu;", ci->id);

  ret=sqlite3_prepare_v2(db, sqltxt, -1, &stmt, NULL);

  if((ret==SQLITE_OK)&&(stmt!=NULL)){
    sqlite3_reset(stmt);

    int iret=sqlite3_step(stmt);
    if(iret==SQLITE_ROW){

    // column is 0base
      ci->updatetime=sqlite3_column_int(stmt, 0);
      ci->value2=sqlite3_column_int(stmt, 1);
      aquire_data=true;
    }

    sqlite3_finalize(stmt);
  }

  return aquire_data;
}