Database Connection

I have come across a problem in my application with my sqlite database connection. I am having problems with shipping a pre-populated sqlite database with my application. After researching the problem I have discovered that android doesn’t allow sqlite database to be stored and accessed from within the APK. This is a problem….

What I have discovered

Many people have come across this problem before and there is a phonegap plugin which is meant to fix it. The plugin Litehelpers / sqlite-plugin. This plugin is meant to find your sqlite database from within your app and move it to the android data store if the plugin cannot find your database it will create a new blank database in the android data store. This plugin is maintained and currently has a forum and a gitter for support.  The problem is I cannot get this plugin to work. I installed the plugin and added a line of code opening my database

var db = window.sqlitePlugin.openDatabase({name: “slfw.db”});

While checking the console I get the message:

Database opened

I added a line of code to query the database and select and output all the data from the categories table.

No error but not data output …. odd

I read that if it couldn’t find a database in my application it would create a new one in the android data store. I tried adding a new table and inserting some data then outputting the results.

db.transaction(function(tx) {
tx.executeSql(‘DROP TABLE IF EXISTS test_table’);
tx.executeSql(‘CREATE TABLE IF NOT EXISTS test_table (id integer primary key, data text, data_num integer)’);

tx.executeSql(“INSERT INTO test_table (data, data_num) VALUES (?,?)”, [“test”, 100], function(tx, res) {
console.log(“insertId: ” + res.insertId + ” — probably 1″);
console.log(“rowsAffected: ” + res.rowsAffected + ” — should be 1″);

db.transaction(function(tx) {
tx.executeSql(“select count(id) as cnt from test_table;”, [], function(tx, res) {
console.log(“res.rows.length: ” + res.rows.length + ” — should be 1″);
console.log(“res.rows.item(0).cnt: ” + res.rows.item(0).cnt + ” — should be 1″);
});

Success! This method works. The problem with this method is that the first time the user loads up my application they would have to wait while my application builds the database and inserts the data in each column.  The other option is to store all my data into Json files and get my data from these files. This method is more time consuming for me because I need to create each Json object individually.

 

Conclusion

I have come to a cross roads with my application and am unsure about which road to take.

If I continue with trying to load my pre-populated database into my application I could waste vital time (especially if I speed lots of time on it and I don’t get it working).  The pre-populated database method  is far the best method for this application.

Storing my content as json files should work and would be faster then creating a database on the first load of the application

Creating the database and populating the database on the first load of the application creates an large overhead for the user BUT I know this method works!