How can I install a database in a titanium alloy project?

Question

I have been trying to install a tiny simple database into my titanium project with the code from the documentation and trying different tweaks as suggested by a variety of sources. But I cannot get it to work, I get errors like "no such table" or resultset is null. Currenty my code looks like this:

var db = Ti.Database.install('../assets/exercises.db', 'exercisesDB');
Ti.API.info('installed '+ db.getName()  );
db.close();
Ti.API.info('closed db'  );
db = Ti.Database.open('exercisesDB');
Ti.API.info('reopened db'  );
//Ti.API.info(db.getName()  );
var exercisesDBRS = db.execute('SELECT id,name FROM exercise');

I have tried putting the database file exercises.db in the assets folder and in the Resources folder but I'm getting no where. I created the db file with "DB Browser for SQLite" ver 3.9.1 on OSX Sierra - is it compatible with appcelerator alloy projects? My current code produces the error "no such table" on the execute call. I assure you the db file has an exercise table.

PS, full code in new projects index.js file is as follow:

var db = Ti.Database.install('exercises.sqlite', 'exercisesDB');
Ti.API.debug('installed '+ db.getName()  );
db.close();
Ti.API.debug('closed db'  );
db = Ti.Database.open('exercisesDB');
Ti.API.debug('reopened db'  );
var exercisesDBRS = db.execute('SELECT id, name FROM exercise');
Ti.API.debug('executed select');
Ti.API.debug(' rowcount== '+   exercisesDBRS.rowCount);

while (exercisesDBRS.isValidRow()) {
    var exId = exercisesDBRS.fieldByName('id');
    var exName = exercisesDBRS.fieldByName('name');

    Ti.API.debug("Exercise: "+exId + ' ' + exName  );
    exercisesDBRS.next();
}
exercisesDBRS.close();
db.close(); 

function doClick(e) {
    alert($.label.text);
}

$.index.open();

New project but same code and a copy of the same database file in the app/lib folder. Same error - no such table on the execute line when tested in android, works fine in iOS sim.

Prashant Saini Prashant Saini · Accepted Answer · 2017-01-29T16:24:04

As per the docs, your external database should be located at the same place where you are running the below code:

var db = Ti.Database.install('../assets/exercises.db', 'exercisesDB');

There's no need to use ../assets/. You can simply refer the database without it because everything you put in assets folder is moved to respective Resources->iphone/android folder.
You can always use Ti.Database.install method for external database because after installing a database once, it will behave like Ti.Database.open('exercisesDB') method.

Coming to your primary concern, you can always safely put your database file in app -> lib folder (create lib folder if it's not present and put db file in there then).

After putting the db file there, you can always use below code:

var db = Ti.Database.install('exercises.db', 'exercisesDB');

Though, I have never used .db extension, instead I have always used .sqlite format (since docs also says SQLite database) and it has worked 100% correct every time.

So you can try this process by changing the database format to .sqlite and put it in app->lib folder. It will definitely work and after verifying you can use same code on using a .db file and see if it's supported or not.

On Android: Follow these steps:

1- In your PC, go to the project's root directory, delete build and Resources folders.

2- On device, clear app data from Settings and then delete the app.

3- Now install the app again with the same code and flow as I mentioned here.

If it's working fine for iOS sim, then it should also work for Android (only some cleaning issues are there or your previous database is still there).

How can I install a database in a titanium alloy project?

1 Answers