Access SQLite using C++

This is a description of accessing SQLite using C++

Step 1: Download the CppSQLite

For me, I have been using the version which can be downloaded from:

http://www.4shared.com/zip/aNL2EdT_/sqlite.html

Step 2: Build the CppSQLite library

In this example, I am using VS2008 C++ IDE,

1) Extract the downloaded content to the folder sqlite
2) Open sqlite\SQLite_Static_Library\SQLite_Static_Library.sln
3) You may need to change the runtime libraries of the project to match those of the executable that will include the libraries, in order to avoid linker errors.
4) Build the library.

Step 3: Add the CppSQLite to your project

1) copy the sqlite folder to your solution folder
2) Include the library directory in your application (Project->Properties->Configuration Properties->Linker->General->Additional Library Directories->["$(ProjectDir)sqlite\SQLite_Static_Library\release"])
3) Include the static library built in step 2 (Project->Properties->Configuration Properties->Linker->Input->Additional Dependencies->[SQLite_Static_Library.lib])
4) Add the C++ wrapper to your project.  It's in the sqlite root dir:

      CppSQLite3.h
      CppSQLite3.cpp

Step 4: Implement code to access SQLite

The following a simple singleton class written in C++:

#ifndef _H_DB_MANAGER_H
#define _H_DB_MANAGER_H

#include <sstream>
#include <ctime>
#include "CppSQLite3.h"

class DBManager
{
public:
 virtual ~DBManager()
 {
  
 }

private:
 DBManager()
 {
  
 }

 DBManager(const DBManager& rhs) { }
 DBManager& operator= (const DBManager& rhs) { return *this; }

public:
 void record_data(int attr1_value, const std::string& attr2_value, int attr3_value)
 {
  std::ostringstream oss;
  oss << "INSERT INTO demo_table (attr1, attr2, attr3) VALUES (" << attr1_value << ", '" << attr2_value << "', " << attr3_value << ");" ;
  try{
   mDB.execDML(oss.str().c_str());
  }catch(CppSQLite3Exception& e)
  {
   std::cerr << e.errorCode() << ": " << e.errorMessage() << "\n";
  }
 }

public:
 static DBManager* getSingletonPtr()
 {
  static DBManager theInstance;
  return &theInstance;
 }

public:
 void open(const char* dbname)
 {
  try{
   remove(dbname);
   mDB.open(dbname);

   mDB.execDML("CREATE TABLE demo_table (id INTEGER PRIMARY_KEY, attr1 INTEGER, attr2 TEXT, attr3 INTEGER);");
  
  }catch (CppSQLite3Exception& e)
  {
   std::cerr << e.errorCode() << ":" << e.errorMessage() << "\n";
  }
 }
 void close()
 {
  mDB.close();
 }

private:
 CppSQLite3DB mDB;
};
#endif

Below is a simple explanation of the database manager class

• The open() method recreate the database, and then create a datatable "demo_table" in the database file with three fields: attr1, attr2, attr3, attr1 and attr3 are INTEGER while attr2 is a TEXT. 
• The record_data() method record a single row into the database "demo_table" 
• The close() method should be called at the end of database operation to create database connection

To use the singleton class above, it is very easy, below is a simple example:

#include "DBManager.h"
DBManager::getSingletonPtr()->open("demo.db");
DBManager::getSingletonPtr()->record_data(1, "Hello World", 2);
DBManager::getSingletonPtr()->close();