sqlite integration in cocos2d-x

SQLite is basically used for creating and maintaining databases in the games which have some complex data to save.So, in this tutorial we will learn to integrate sqlite in our cocos2d-x game in xcode using c++

Step 1
First create a default HelloWorld project and in your HelloWorldScene.cpp , delete the lines in init function after these lines upto return true statement.
if(!CCLayer::init())
{
return false;
}

Don’t delete return true.

Now your init function should look like

bool HelloWorld::init()
{
if ( !CCLayer::init() )
{
return false;
}

return true;
}

Step 2
Now we have to add a library in our project. In order to this this just click on your project and go to Build Phases. Click on build phases and then expand Link Binary With Libraries
There click on + button and search for a library with name libsqlite3.0.dylib

Add this to your project such that your Link Binary With Libraries must look like

It should contain the libsqlite3.0.dylib file
There is also one more library with name libsqlite3.dylib. You can add any of these two

Step 3
Now go to your HelloWorldScene.cpp and include the sqlite header file as

#include<sqlite3.h>

Step 4
Now we have to first create a database which will be our .sqlite file
So in order to do this just after the lines
bool HelloWorld::init()
{
if ( !CCLayer::init() )
{
return false;
}
Write the following code
sqlite3 *pDB = NULL; //for database path
char* errMsg = NULL; //for error message
string sqlstr; //for sql query string
int result;
string dbPath = CCFileUtils::sharedFileUtils()->getWritablePath();

dbPath.append("rajan.sqlite");
result = sqlite3_open(dbPath.c_str(),&pDB);
if (result != SQLITE_OK)
CCLOG("OPENING WRONG, %d, MSG:%s",result,errMsg);
CCLOG("result %d",result);
sqlite3_close(pDB);//for closing connection

If the value of result is 0 then you have successfully created the database otherwise it will throw and exception with OPENING WRONG MSG. Here I am naming my database as rajan.sqlite
When you run the code the value of result will print as 0 if everything is correct other wise it will throw OPENING WRONG EXCEPTION

Now to see your database where it is actually made, just follow this path
/Users/yourusername/Library/Application Support/iPhone Simulator/(your version)7.0.3/Applications/(something like this.You just find your application)E2932C46-F347-4E1E-9E3E-BF986C1320C4/Documents/yourdatabasename.sqlite

Just make sure your library folder is unhidden by running the command on the terminal
chflags nohidden ~/Library

So this means you have successfully created the database. Now its time to create table in it

Step 5
In order to create the table, add these lines just above the line sqlite3_close(pDB);
//creation of table
result = sqlite3_exec(pDB, "create table game(stage_name varchar(32), stage_no
INT)",NULL,NULL,&errMsg);
if(result != SQLITE_OK)
CCLOG("CREATE TABLE FAIL %d, Msg: %s",result,errMsg);

I am giving my table name as game and creating two columns. First is the name of the stage and second is the stage number
Now run the code. If you get the error create table fail then something might be wrong. Otherwise if everything is OK then your table is created

Now in order to see the table, you can view it in mozilla firefox by integrating the tool sqlite manager.
Click on tools menu after opening mozilla and look for sqlite manager. If you dont have it then you can download it by clicking get more tools. If you have successfully integrated with SQlite manager then you will see the tool menu with the sqlite manager icon

Now click on the SQLite Manager and then click on connect database folder like icon

Browse to the path mentioned above (/Users/yourusername/Library/Application Support/iPhone Simulator/(your version)7.0.3/Applications/(something like this.You just find your application)E2932C46-F347-4E1E-9E3E-BF986C1320C4/Documents/yourdatabasename.sqlite)
Open the .sqlite file. SQLite manager will show the file with a created table with name game on the left

Step 6
Now its time to add some content in this table. As we have already created the table so you can commnent all the creation part of the table otherwise if you dont comment then also it will not lead to any problem except a message that table already exists

So after the creation part add the following lines to populate data in the table
// for insertion
for(int i=1;i<=5;i++)
{
CCString *stage_name=CCString::createWithFormat("stage%d",i);
std::string stage=stage_name->getCString();
CCString *stage_num=CCString::createWithFormat("%d",i);
std::string s_num=stage_num->getCString();
sqlstr="insert into game values('"+stage+"','"+s_num+"')";
result=sqlite3_exec(pDB,sqlstr.c_str(),NULL,NULL,NULL);
if(result!=SQLITE_OK)
CCLog("insert data failed!");
}

Run the program and if everything goes fine the table game will be populated with 5 rows. In order to view the data you can go to SQLite Manager, click on table game and click on browse and search and you will find the data as shown in the snapshot

Step 7
Now if you want to extract the data in your game you can use the following code but comment the insertion part now so that the data is only retrieved and not inserted
//for display
sqlstr="select * from game";
CCString *number;
CCString *name;

result=sqlite3_prepare_v2(pDB,sqlstr.c_str() , -1, &ppStmt1, NULL);

for (;;) {

result = sqlite3_step(ppStmt1);

if (result == SQLITE_DONE)
break;
if (result != SQLITE_ROW) {
printf("error: %s!\n", sqlite3_errmsg(pDB));
break;
}
name=CCString::create((const char*)sqlite3_column_text(ppStmt1, 0));
number=CCString::create((const char*)sqlite3_column_text(ppStmt1, 1));
CCLOG("stage_name %s stage_no %s",name->getCString(),number->getCString());

}

I have just used CCLOG to display. Below is the output when I run the code

So, this tutorial covers creation of database, creation of table, insertion of contents in the table and then displaying the contents of the table. Hope you liked it. This comes to the end of the tutorial

HAPPY CODING