A simple Tutorial How to use SQLite in cocos2d-x ……（Tutorial by YUYE）

cocos2d-x C++
#42

How to read records ?
I mean i got this part :
sqlite3_get_table(pdb,"select * from student",&re,&r,&c,NULL);

but if i want to get row 3 column 2 & column 3 (or in my table terms stageno and highscore from the third row) ?

How to access row[3]-column[2]column[3] ?

i tried with this : re[(2*c+3*c)+3] but no success…

#43

@rammehta

you need to use sqlite3_step() for this.

re[(2*c+3*c)+3] would be trying to do math…

#44

k…i will look into that function…
@slackmoehrle will u be doing a wiki for SQLite ?

#45

Sure, I can take the tutorial that Yuye did, clean it up and add more functionality.

#46

Thanks that will help more people…

Cocos2d-x 3.0 Online Party
#47

Can I access 2 db3 file in a game at the same time?
For example, I have 2 db3 files A & B. File A stores values which are synced from server. File B stores local values.
Can I work with them at the same time with Cocos2dx? If yes, how can I do that? If now, please tell me why or its risk?

#48

sure, open a connection to each.

#49

It won’t affect the performance, will it?

#50

The only thing that doesn’t effect performance is doing nothing…I’m not sure how to answer your question without sounding like a smart mouth. SQLite is pretty quick. I’d do what you need and decide if it is acceptable to you.

Also, make sure you need 2 Sqlite db’s. Perhaps your data can be structured to just use one.

#51

Great thank!
However, I got a rock again. I have an exist database file (a.db3)

  1. How can I check if it exists in the device, both in Android and iOS?
  2. I just want to open to read the database, not create a new one, how can I do that? When I follow this tutorial, it just creates the new database, not open the exist database. In addition, how to import the sqlite file in Android and iOS?

Thanks!

#52

are you using 3.2?

#53

Yes, that’s it

#54

if you pull v3.3 from GitHub, there is new functionality for FileUtils: isDirectoryExist(), createDirectory(), removeDirectory(), removeFile(), renameFile(), getFileSize()

#55

So, I can’t work with an exist datavase in Cocos2dx v3.2? I have to create a blank file and insert manually?

#56

you can totally do that

consider something like:

int result = sqlite3_open(_dbPath.c_str(), &_pdb);
    
    if(result == SQLITE_OK)
    {
        Utils::LogText("open database successfull!");
        _bConnect = true;
    }

if you try and open a database that doesn’t exist, it is created.

then, check for a table that should exist if your database is not new.

if that table exists, continue on, you have your database and it has data

if that table does not exist then you must have an newly created, empty database, run some SQL statements and create them and then continue on your way.

#57

How can I copy the database to the device? At this time, I put the db in Resources/db/db.db3. I don’t know ow to copy this db to the phone data both in Android and iOS

After that, I use this code to open the db, but it always creates a new one

bool DBProcess::OpenDB(char *path)
{
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;
	std::string dbPath = CCFileUtils::sharedFileUtils()->getWritablePath();
	
	dbPath.append(path);
	rc = sqlite3_open(dbPath.c_str(), &db);

	if( rc )
	{
	  CCLOG("Can't open database: %s\n", sqlite3_errmsg(db));
	  return false;
	}
	else
	{
	  CCLOG("Opened database successfully\n");
	}
	sqlite3_close(db);
	
	return true;
}
#58

I don’t know. I have never manually copied one. I always just start the app. If it exists and contains some data to prove it, then I use it. If it doesn’t exist, create it and I code up the SQL creates, inserts to create the structure my app needs to run.

This way my app runs on anything and the database gets put were getWriteablePath() puts it.

#59

so is my help not good enough? You asked on Stack Overflow just 10 mins ago: http://stackoverflow.com/questions/24961937/work-with-an-exist-datavase-in-cocos2dx-v3-2

what is the output of this:

std::string dbPath = CCFileUtils::sharedFileUtils()->getWritablePath();
std::cout << "dbPath: " << dbPath << std::endl;

dbPath.append(path);
std::cout << "dbPath after append: " << dbPath << std::endl;

somewhere you must be specifying a .db3 file in this dbPath

#60

My main problem is someone will give the db file, and I have to use it in code. But, I don’t know where to put the db file to read. At this time, I put db.db3 in Resources/db/ folder. When I build game, I want to put this file to the path which is returned from std::string dbPath = CCFileUtils::sharedFileUtils()->getWritablePath(); (In iOS simulator, it’s something like users/…/v7/Documents/14D… folder)
Now, I have nothing in this path, so it always create the new one.

Your answer is good when I launch game first time and everything is clean, and I can create database, tables, add values. I did it successfully. But I have the file already (created without code in game), so I want to know how to put this file to let the device can determine that file exists.

#61

I’m not sure you can on every device. iOS, I bet sandboxing gets in the way…

Perhaps include the .db3 in your Resources and when your app starts, check getWritablePath() for the db and if it isn’t there maybe you can copy it somehow. Or maybe you could use curl and download it really quick. I guess there are options. Most devs just create it in code the first time and be done with it.