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

cocos2d-x C++
#16

There is no CCFileManager in Cocos2d-x or I can’t find one.

Can somebody share knowledge how he/she has got this to work on Android?

I’m trying with these steps,
first - code for Android database,
than code for opening database
and then for searching - all from examples in first post, but it seems that my database is empty. This line CCLog(“row is %d,column is %d”,r,c); gives me that there’re 0 rows and 0 columns. My database has extension sqlite and I build it in SQLite Manager add-on for Firefox. I’ve placed it in Resource folder. It has one row and 7 columns - I’m trying with small example, just to get it work. Can somebody help?

#17

I’ve got this to work. I’m working with two databases with .sqlite extenstion. I’ve probably had problem with saving dbPath to global variable, and when result=sqlite3_open(path.c_str(),&pdb); was executed it created new empty database, so that’s why there was 0 rows and 0 columns.

#18

How do I can import a big data? In native Android, I can import database from CSV file through SQLManager. How about in Cocos2dx? Thanks

#19

@lolyoshi you must learn what is the JNI for android.

#20

@yuye I run don’t have error but when i open db file. I don’t find student table. Can you help me?

#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