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

cocos2d-x C++
#22

Hi YuYe,

In your example, one section is “asserting data” Maybe change that to “Inserting data”?

asserting = “state a fact or belief confidently and forcefully.”

This is a great tutorial. Just trying to help with some wording :slight_smile:

Also, I could give you an example on updating values as well if you wanted to include that.

Having difficulty writing into a file
#23

Hi
I made my sqlite database and I want to read from it in game.
where should I put my database ?
I tried:
\Resources
\proj.android\src\com\example\mytest\

But Everytime my app crash ! :frowning:

Where should I put ?

PS: I even give 777 permission to mydatabse file.

Thanks in advanced
:slight_smile:

#24

Oh. I found the solution here:

http://www.cocos2d-x.org/forums/6/topics/16534
#25

@emadpres I’m sorry to reply u late. congraturation you found it .

#26

Please, i need some help :s

I cannot create and read/write sqlite when running my application on Android. In fact, it seems that it crashes on function CCFileUtils::sharedFileUtils()->fullPathForFilename()

Here is the log:

04-18 19:28:58.523: D/dalvikvm(29430): WAIT_FOR_CONCURRENT_GC blocked 9ms
04-18 19:28:58.585: D/cocos2d-x debug info(29415): [WebClient] Starting WebClient…
04-18 19:28:58.594: D/cocos2d-x debug info(29415): [SQLClient] Starting SQLClient…
04-18 19:28:58.594: D/cocos2d-x debug info(29415): [SQLClient] Here 1
04-18 19:28:58.599: A/libc(29415): Fatal signal 4 (SIGILL) at 0x40105d62 (code=1), thread 29415 (activeschool)

And my code to read sql, copy and open is:

SQLClient::SQLClient() {
CCLOG("[SQLClient] Starting SQLClient…");
pDB = NULL;

CCLOG("[SQLClient] Here 1");
std::string dbPath = CCFileUtils::sharedFileUtils()->fullPathForFilename("activeschool.db3");
CCLOG("[SQLClient] Here 2");

#if CC_TARGET_PLATFORM == CC_PLATFORM_ANDROID
CCLOG("[SQLClient] Running on Android device");
CCLOG("[SQLClient] Here 3");
dbPath = CCFileUtils::sharedFileUtils()->getWritablePath();
dbPath += “/activeschool.db3”;
FILE* file = fopen(dbPath.c_str(), “r”);
if (file == nullptr)
{
unsigned long size;
const char* data = (char*)CCFileUtils::sharedFileUtils()->getFileData(“activeschool.db”, “rb”, &size);
file = fopen(dbPath.c_str(), “wb”);
fwrite(data, size, 1, file);
CC_SAFE_DELETE_ARRAY(data);
}
fclose(file);
#endif
(…)

What am I doing wrong? I’ve readed already many posts here, many tutorials, and I cannot find a reason to this happens. I’ve already added to manifest, but with no success.

I don’t know what to do mor :confused:

Thank you in advance!

#27

I think the way the database is created and handled in this tutorial is too complicated. Here’s a simple and cross-platform (no #if CC_TARGET_PLATFORM, tested on iOS and Android using cocos2d-x 2.2.1 only so far) way of doing this:

// Database.h
class Database
{
private:
    Database();
    static Database *singleton;
	
    sqlite3 *database;
    
    static Database* getInstance();

public:
    static sqlite3* getDatabase();
    
    static bool open();
    static bool create(std::string aTableName);
    static void close();
    
    static bool execute(cocos2d::CCString *aSql);
};

// Database.cpp
#include "Database.h"

USING_NS_CC;

Database* Database::singleton = NULL;

Database::Database()
{
    database = NULL;
}

Database* Database::getInstance()
{
	if (singleton == NULL)
        singleton = new Database();
	return singleton;
}

sqlite3* Database::getDatabase()
{
    return Database::getInstance()->database;
}

bool Database::open()
{
    std::string filePath = CCFileUtils::sharedFileUtils()->getWritablePath() + "db.sqlite3";
    if (sqlite3_open(filePath.c_str(), &Database::getInstance()->database) != SQLITE_OK)
	{
		sqlite3_close(Database::getInstance()->database);
		CCLOG("Failed to open database: %s", filePath.c_str());
		return false;
	}
	
	return true;
}

void Database::close()
{
	sqlite3_close(Database::getInstance()->database);
}

bool Database::execute(CCString *aSql)
{
	char *errorMsg;
	
	if (sqlite3_exec(Database::getInstance()->database, aSql->getCString(), NULL, NULL, &errorMsg) != SQLITE_OK)
	{
		CCString *status = CCString::createWithFormat("Error executing SQL statement: %s", errorMsg);
		CCLOG("%s", status->getCString());
		return false;
	}
	
	return true;
}

And using the above class is simple enough. In your AppDelegate::applicationDidFinishLaunching() add the this line at the end Database::open(); and then use the class to execute SQL statements, something like Database::execute("YOUR SQL").

Hope this helps.

2 Likes
#28

@yuye - would you like some code for updating records as well as reading records back to Objects?

#29

@mserougi
Thanks. May I know where you place your db file in proj.android folder?
If I understand correctly, the db file is in read-only and need to copy to writable path first. That’s the reason to check (CC_TARGET_PLATFORM == CC_PLATFORM_ANDROID)

#30

@yuye getFileData() is DEPRECATED in cocos2dx 3.0 and i don’t know how to use SQLite in android. Can you show me how to change it, please?

#31

@Hoangtaiki - user @mserougi posted a solution above that works.

#32

@slackmoehrle Like as situation @winipcfg say. I have difficulty in this place.

#33

For Android you would use the value returned by Context.getFilesDir() and place your SQLite database here. This usually return something like: /data/data//

#34

@slackmoehrle

I use this code to log path where can i place my SQLite database:

File f = new File(this.getApplicationContext().getFilesDir() + "/");
f.mkdirs();
        
System.out.printf("The value of e is %s", f.getAbsolutePath());

in file AppActivity.java but i don’t see anything in Logcat. Help me please.

#35

@Hoangtaiki can you post the output to the console?

#36

@slackmoehrle This is my console. No error but i don’t see i printf in AppActivity.java

rRL63WB.png916×778

#37

@Hoangtalki - Where is the line: “The value of e is” I don’t see that in the screenshot.

#38

What I would do to be safe is not copy your database, meaning, nothing prebuilt.

I’d let your app create the database. That way it will put it where it goes and everything will be fine. Usually data/data/your.app.package/databases/ or something. Android has built in SQLite support.

So I’d execute your create tables, inserts, etc when you start the app if the database doesn’t exist. After that is done the first time, no need to do it anymore, your will check to see if it exists and it will, so you can proceed with normal operations.

#39

@slackmoehrle I have tried to do what you said to me but I really can not find the path to place the database file. Creating a new database really no problem at all. You can add my skype and help me? I tried to conversion my game from cocos2d-x 2.* to 3.0 but this makes me really disappointed.

#40

@HoangTaiki - What help do you need though? Why don’t you e-mail me with what your issues are with the conversion from 2 to 3 and let me see if I can help you. jason@us.chukong-inc.com

#41

Hi yuye, I have a question that how to read Chinese from sqlite. If you know I think you can answer me.Thanks.