SQLite Basic Demo for Cocos2d-x

sqldemo.cpp

#include "sqlite3.h"
#include "cocos2d.h"

sqlite3 *_db = nullptr; 

// sql query helper function.
int sql_query(const char * query,int (*callback)(void*,int,char**,char**) = NULL, void* data = NULL) 
{ 
   char* errMsg = 0; 
   int result = sqlite3_exec(_db, query, callback, data, &errMsg); 
   if (errMsg)  { 
      CCLOG("SQLite3: %s", errMsg); 
      sqlite3_free(errMsg); 
   } 
   return result; 
}

void sqldemo() 
{
   // Open database
   std::string dbPath = CCFileUtils::sharedFileUtils()->getWritablePath();
   dbPath.append("save.sqlite"); 
   result = sqlite3_open(dbPath.c_str(),&_db);
  
   // Add a table
   sql_query("CREATE TABLE BOATS (BOAT_ID INTEGER PRIMARY KEY), (BOAT_NAME TEXT), (DISTANCE REAL)");
   // Insert into table 
   sql_query("INSERT INTO BOATS (BOAT_ID, BOAT_NAME, DISTANCE) VALUES (8988, 'Voodoo Prince', 300), (777, 'Lucky Numbers', 600)");
   // update row
   sql_query("UPDATE BOATS SET BOAT_ID = 666, BOAT_NAME = 'Number of the Beast' WHERE BOAT_ID = 777");
   // delete row
   sql_query("DELETE FROM BOATS WHERE BOAT_ID = 666");

   // fetch table data
   sqlite3_stmt *stmt;
   int rc = sqlite3_prepare_v2(_db, "SELECT * FROM BOATS", -1, &stmt, NULL);
   CCASSERT(rc == SQLITE_OK, "Database fail");
   
   rc = sqlite3_step(stmt);
   while (rc != SQLITE_DONE) {
     int boat_id =               sqlite3_column_int(stmt, 0);
     const char* boat_name =     reinterpret_cast<const char*>(sqlite_column_text(stmt,1));
     float path_dist =           sqlite3_column_double(stmt, 2);
     rc = sqlite3_step(stmt);
   }
   sqlite3_finalize(stmt);
   // close database
   sqlite3_close(_db);
}