example-sqlite3/dataplay.c

#include <sqlite3.h>
#include <stdio.h>
#include <string.h>

void datado(void) {
  sqlite3 *db;
  char sqlbuffer[256];
  sqlite3_stmt *stmt;
  int rc;
  const char *database = "test.db";

  rc = sqlite3_open(database, &db);
  if (rc) {
    printf("Error opening database %s : %s", database, sqlite3_errmsg(db));
    return;
  }
  // unsure
  sqlite3_busy_timeout(db, 5000);
  strcpy(sqlbuffer, "SELECT * FROM USER;");
  sqlite3_prepare_v2(db, sqlbuffer, strlen(sqlbuffer) + 1, &stmt, NULL);
  while (sqlite3_step(stmt) == SQLITE_ROW) {
    // total columns = sqlite_3_column_count(stmt);
    int i = sqlite3_column_int(stmt, 0);
    printf("%s %d : %s %s / %s %s\n", sqlite3_column_name(stmt, 0), i,
           sqlite3_column_name(stmt, 1), sqlite3_column_text(stmt, 1),
           sqlite3_column_name(stmt, 2), sqlite3_column_text(stmt, 2));
  };
  sqlite3_finalize(stmt);
  // for now

  // NEXT!
  char nick[] = "captain";

  // https://stackoverflow.com/questions/973541/how-to-set-sqlite3-to-be-case-insensitive-when-string-comparing#973785
  strcpy(sqlbuffer, "SELECT id FROM USER WHERE nick=? COLLATE NOCASE");
  // captain will now match Captain

  sqlite3_prepare_v2(db, sqlbuffer, strlen(sqlbuffer) + 1, &stmt, NULL);
  sqlite3_bind_text(stmt, 1, nick, strlen(nick), SQLITE_STATIC);
  rc = sqlite3_step(stmt);
  if (rc == SQLITE_ROW) {
    int nid = sqlite3_column_int(stmt, 0);
    printf("%s ID is %d\n", nick, nid);
  } else {
    printf("Unable to locate record.\n");
  }
  sqlite3_finalize(stmt);

  sqlite3_close(db);
}

int main(int argc, char *argv[]) {
  printf("Welcome.\n");

  datado();

  return 0;
}