Sqlite3 database

SQLite Web site

Step by step scenario

Connecing to sqlite3 terminal

sqlite3 [file_name]

Show help

sqlite> .help

Show all tables

sqlite> .tables

Show table decription

sqlite> .schema [table_name]

Select table

Create table

Insert table

Update table

Delete table

Exit terminal

sqlite> .quit

c samples

#include <sqlite3.h>

typedef struct _DBMSG {
  char key[1024];
  int seq;
  char flag;
  char msg[4096];
} DBMSG;

// Sample
sqlite3* sqlitedb;
char *dbfname = "test";
char DBMSG dbmsg;

if (initSqlite3(&sqlitedb, dbfname) != 0) {
  printf("Failed to initialize db (%s) (%d)\n", dbfname, err);
}

if (selectSqlite3(sqlitedb, &dbmsg, *err) < 1) { // error
  printf("Failed to get date from sqlite3 (%d)\n", err);
} else {
  printf("MESSAGE:(%s)\n", dbmsg.msg);
}

destroySqlite3(sqlitedb);

// --------------------------------------------
// initialize Sqlite3
// --------------------------------------------
int initSqlite3(sqlite3** db, char* dbfname)
{
  char path[1024] = {0x00};
  int rc = 0;

  snprintf(path, sizeof(path), "%s", dbfname); // full name
  if (rc = sqlite3_open_v2(path, db, SQLITE_OPEN_READONLY|SQLITE_OPEN_FULLMUTEX|SQLITE_OPEN_SHAREDCACHE, NULL))) {
    printf("Failed to open sqlite3 (%s)(%d)\n", path, err);
    return (-1);
  }
  return 0;
}

// --------------------------------------------
// destroy Sqlite3
// --------------------------------------------
void destroySqlite3(sqlite3** db)
{
  if (db && *db) {
    sqlite3_close_v2(*db);
  }
}

// --------------------------------------------
// select sqlite3
// --------------------------------------------
int selectSqlite3(sqlite3* db, DBMSG* dbmsg, int* err)
{
  sqlite3_stmt* stmt;
  char sql[1024] = {0x00};
  int sqllen = 0;
  int rc = 0;

  sqllen = snprintf(sql, sizeof(sql),
        "select * from yourtbl where key='%.*s' and flag='%c' and seq=%d "
        "order by seq desc limit 1",
        sizeof(dbmsg->key), dbmsg->key, dbmsg->flag, dbmsg->seq);
  
  if ((rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0)) != SQLITE_OK) {
    *err = rc;
    printf("sqllite3_prepare_v2(%.*s): (%d) %s", sqllen, sql, *err, sqllite3_errmsg(db));
    return (-1);
  }

  if ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
    strncpy(dbmsg->key, (const char*)sqlite3_column_text(stmt, 0), sizeof(sqlite3_column_text(stmt, 0)));
    dbmsg->seq = sqlite3_column_int(stmt, 1);
    dbmsg->flag = *sqlite3_column_text(stmt, 2);
    strncpy(dbmsg->msg, (const char*)sqlite3_column_text(stmt, 3), sizeof(sqlite3_column_text(stmt, 3)));
  } else {
    *err = rc;
    re = -1;

    if (*err != SQLITE_DONE) // No data
      printf("No data found - sqlite_step(%.*s): (%d) %s", sqllen, sql, *err, sqllite3_errmsg(db));
  }

  sqlite3_finalize(stmt);
  return rc;
}