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

cocos2d-x C++
#62

Yes, I copied files from asset to writable folder when the app starts. At this time, it works like charm.

#63

hi! I successfully opened my database but when I

sqlite3_get_table(db,"select * from words",&re,&r,&c,NULL);
CCLog("row is %d,column is %d",r,c);

result row is 0, column is 0

#64

Nice tuts :smile:

#65

Guys i have used this code in one of my games. And hey you can get the entire code and learn from it.
I kept it on Code Canyon. Cheers!

#66 
unsigned long size;
const char* data = (char*) CCFileUtils::sharedFileUtils()->getFileData("dict.db", "rb", &size);
file = fopen(dbPath.c_str(), "wb");
fwrite(data, size, 1, file);
CC_SAFE_DELETE_ARRAY(data);

I’m getting an error for this part, trying to copy the database for android on line 3, &size

Cannot initialize a parameter of type ssize_t * (aka long *) with an rvalue  of type unsigned long *

Any ideas? Looking elsewhere, this same code is used to copy files, but I keep getting this size error.

#67

so you are trying to copy the sqlite database to another location? What version of cocos2d-x?

#68

Edit: I think I’ve solved it, fix below.

Sorry, I should’ve included all the code. This is version 3.2. I’m copying the database so that it runs on Android, I can get it to work fine on iOS (just by opening the file directly). From what I understand, I can’t open a database in Android because the resources are zipped, so I have to make a copy the content of the db into a new database in a writeable folder. This is what I’m trying to do:

sqlite3 *pdb = NULL;
int result;

std::string path = FileUtils::getInstance()->getWritablePath();
path.append("mydb.db");

FILE* file = fopen(path.c_str(), "r");
if (file == nullptr) {
    CCLOG("no such file");
    unsigned long size = 0;
    const char* data = (char*) FileUtils::getInstance()->getFileData("mydb.db", "rb", &size);
    file = fopen(path.c_str(), "wb");
    fwrite(data, size, 1, file);
    CC_SAFE_DELETE_ARRAY(data);   
}

result = sqlite3_open(path.c_str(),&pdb);
if (result != SQLITE_OK)
    CCLOG("OPEN FAILED");
else
    CCLOG("OPEN WORKED");

char **re;
int r,c;
        
sqlite3_get_table(pdb,"select * from mytable", &re, &r, &c, NULL);
CCLOG("num of rows is %d, num of columns is %d", r, c);

This should result in a positive num of rows and columns. But I’m getting that size variable error, and I don’t know enough about C++ to figure it out on my own :frowning:

Fix: Ok, so I was trying to understand the error because all examples I’ve seen use the unsigned long to store the size of the file. Since this is what was causing the error I changed unsigned long size = 0; to ssize_t size = 0; and now everything works fine. I still don’t really understand why other examples use unsigned long, and why it doesn’t work for me. Maybe it worked for older versions? If someone could explain it to me, I would very much appreciate it! I’m guessing the size is a different type? Also, is my solution any good?

#69

On Cocos2dx 3.4 i tried to build this and found multiple errors. Total of 20.
Problem with sqlite3MallocZero, its saying "Assigning to sqlite3_mutex * from incompatible type void * . And others like sqlite3DbMallocRaw "Assigning to char * from incompatible type void *
line 19240

works like a charm for cocos2dx 3.2 and 3.2rc.
If you find any solution then please reply.

#70

have you considered updating SQLite from Sqlite.org?

#71

I am working on a trivia(quiz) game and have near about 1500 rows of data.i have created the database using SQLite Manager(firefox plugin) . so i wanted to use a Pre-Populated SQLite database.
A tutorial/Link for integrating sqlite3 database to cocos 2d-x V.3.6 that will work on both iOS and android platform…? what is the steps to implement CppSQLite3 (c++ wrapper for sqlite) on cocos2dx.

#72

What do you need help with, it is pretty straightforward by looking at the SQLite example code.

Store multiple values or array in userdefault?
#73

Thank you for the reply. i checked that code. but as a beginner to cocos2d-x the code above and the explanation is a bit confusion and Sqlite is not well documented for cocos-2dx. Again i have asked about use of Pre-Populated sqlite DB if i am not wrong the tutorial is not about Pre-Populated sqlite DB for Cocos2dx and how to update the Database for the new version of the app.

#74

Well, this really isn’t anything specific to cocos2d-x.

https://www.sqlite.org/quickstart.html

https://www.sqlite.org/cintro.html

this is all I ever used.

#75

Obviously these are great sources for learning but what i belief a demo working code project with few lines of explanation is always speedup the process of learning.what i have observed from different blogs and tutorials sites. http://www.raywenderlich.com is one of them. Anyway thank you for the link.i really appreciate your help.

#76

For opening and writing from initial example I was getting error on android: database disk image is malformed

Changing from:

unsigned long size;
const char* data = (char*) CCFileUtils::sharedFileUtils()->getFileData("dict.db", "rb", &size);
file = fopen(dbPath.c_str(), "wb");
fwrite(data, size, 1, file);
CC_SAFE_DELETE_ARRAY(data);

To (lines below) fixed it.

auto data = FileUtils::getInstance()->getDataFromFile(kDatabaseName);
FileUtils::getInstance()->writeDataToFile(data, wpath);
#77

Hi. I don’t know how to integrate SQLite with cocos2d-x 3.16.

In your code what is kDatabaseName and wpath? First post seems outdated.

#78

#define kDatabaseName "gameDatabase.db"

Where “gameDatabase.db” is your database file name.

std::string wpath = FileUtils::getInstance()->getWritablePath() + kDatabaseName;

FILE* f = fopen(wpath.c_str(), "r");
if (f == nullptr) {

    auto data = FileUtils::getInstance()->getDataFromFile(kDatabaseName);
    FileUtils::getInstance()->writeDataToFile(data, wpath);
}
1 Like
#79

There really isn’t anything special to so.

Drop sqlite.c and sqlite.h into your project and then follow any SQlite tutorial you find. Like: http://sqlite.org/cintro.html

1 Like