Prepopulated SQLite Databases in Ionic


#10

My code is as follows (roughly):

           if (window.sqlitePlugin) {
                window.plugins.sqlDB.copy("mydatabase.db", function () {
                    $state.go('home.welcome');
                }, function (e) {
                    $state.go('home.welcome');
                });
            }

Your database needs to go into the correct directory within the platforms folders as it’s a different process depending on the platform - this is wel documented on the site. Once you have the database in the right location it will copy over to the phone.


#11

Can you help me please??
I have a really simple app where I just get info from the database, but I don’t understand how use the https://github.com/an-rahulpandey/cordova-plugin-dbcopy and later access to the database.

  • Where I have to put the db file within the ionic project?
  • How I should implement the plugin to copy the database?
  • How I should implement the database access?

Can you share with me reference of guides or tutorials please? I will appreciate

Thanks


#12

The post gives you pretty straight forward instructions on how to implement the plugin, and of the locations for each of the files. You need to first create the platforms using ‘ionic platform add …’ before the locations are available to you.

Once you have given it a go and can’t find the way forward please post your code and any issues you are having so that I can better understand the problems you are having.


#13
.run(function($ionicPlatform, $cordovaSQLite) {
  $ionicPlatform.ready(function() {
    // Hide the accessory bar by default (remove this to show the accessory bar above the keyboard
    // for form inputs)
    if(window.cordova && window.cordova.plugins.Keyboard) {
      cordova.plugins.Keyboard.hideKeyboardAccessoryBar(true);
    }
    if(window.StatusBar) {
      StatusBar.styleDefault();
    }
	
	if (window.sqlitePlugin) {
		window.plugins.sqlDB.copy("mydatabase", function () {
			console.log("correct")
		}, function (e) {
			console.log("error: "+ e)
		});
	}
	
  });
})

I have this on the app.js file. But the app is destroying when I’m launching.
Also, later copy the database I just need do consult, and don’t know how how do it.
Any help please?


#14

The mydatabase needs to be the name of your actual database e.g. test.db and needs to be the exact name and file extension of your sqlite database. That should mean that your code is correct.

For Android put your sqlite database into your platform/Android/assests directory - I’m assuming you have a platforms/android directory already - the plugin gives you further instructions for ios and wp8 if you look at the requests.

When you run ionic run android the database in the assets directory is copied over to the device, the app then copies the sqlite database from the assets directory to the correct directory for the device.

Now all you need to do is to write a sql data access layer.

That should get you up and running. Please note that if the database is already on the phone you will get an error as the plugin doesn’t overwrite an existing database with the same name - this caught me out! To check for this you will need to uninstall the app from the device to ensure that the database does not exist then run ionic run android.


#15

Thanks for the data about the assets, I changed my code for the following.

var db = null;

angular.module('starter', ['ionic', 'ngCordova', 'starter.controllers', 'starter.services'])

.run(function($ionicPlatform, $cordovaSQLite) {
  $ionicPlatform.ready(function() {
    if (window.cordova && window.cordova.plugins.Keyboard) {
      cordova.plugins.Keyboard.hideKeyboardAccessoryBar(true);
    }
    if (window.StatusBar) {
      StatusBar.styleDefault();
    }
	if (window.sqlitePlugin) {
		window.plugins.sqlDB.copy("mydatabase.sqlite", function () {
			alert("database correctly added");
			
			db = $cordovaSQLite.openDB({ name: "mydatabase" });
			var query = "SELECT name FROM people WHERE name = ?";
			
			$cordovaSQLite.execute(db, query, ['charles']).then(function(res) {
				if(res.rows.length > 0) {
					console.log("SELECTED -> " + res.rows.item(0).name);
				} else {
					console.log("No results found");
				}
			}, function (err) {
				console.error(err);
			});
		}, function (e) {
			alert("database incorrectly added");
		});
	}
	
  });
})

I can copied the database file to the project, I’m having the “database correctly added” but I still can’t access to him.

When it’s running the select execution for the name “charles” the console is giving me the following error: android.database.sqlite.SQLiteException: no suc table: people (code1): , while compiling: SELECT name FROM people WHERE name = ?

How I should access to the database with the SQLite plugin later had the original database copied in the files?

Thanks for you help in advice!!!


#16

At first glance it looks like you have the database name wrong. Try changing this line

db = $cordovaSQLite.openDB({ name: "mydatabase" });

to

db = $cordovaSQLite.openDB({ name: "mydatabase.sqlite" });

#17

Kararade did you ever get this working? I am having pretty much the same issue as you.
If so can you post your code?
thank


#19

Yes, I did for android, I’m not sure is working too for IOS, but here you are what I did.

  • In your project you have to define the platform with cordova
ionic platform add android
  • Copy and paste your database in the android folder that you find it in platforms/android/assets

  • Add the plugins “cordova-plugin-dbcopy” and “Cordova-SQLitePlugin” to your project using

cordova plugin add https://github.com/an-rahulpandey/cordova-plugin-dbcopy
cordova plugin add https://github.com/brodysoft/Cordova-SQLitePlugin.git
  • Go to http://ngcordova.com/build/, check the SQLITE plugin and download the ng-cordova.min.js, copy and paste in your project and add like a script file into your index.html file in the head above the cordova.js file

  • Do the following changes in your app.js file

// global variable where will define the database
var db = null;

angular.module('starter', ['ionic', 'ngCordova'])

.run(function($ionicPlatform, $cordovaSQLite) {
  $ionicPlatform.ready(function() {
    if (window.cordova && window.cordova.plugins.Keyboard) {
      cordova.plugins.Keyboard.hideKeyboardAccessoryBar(true);
    }
    if (window.StatusBar) {
      StatusBar.styleDefault();
    }

        /* DATABASE INTEGRATION */	 

	if (window.sqlitePlugin) {

		// copy the database
		window.plugins.sqlDB.copy("name_of_the_database.db",

		function () {
			// copy success, if not yet been copied
			// set "db" as the database
			db = window.sqlitePlugin.openDatabase({name: "name_of_the_database.db"});
		}, function() {
			// copy error, if it has already been copied
			// set "db" as the database
			db = window.sqlitePlugin.openDatabase({name: "name_of_the_database.db"});
		});
	}
  });
})
  • Now you can use the database using the following for example
.controller('ExampleCtrl', function($scope) {
	
	$scope.order = function(){
                // db is your database defined before
		db.transaction(function(tx) {
                        // running a sql querie 
			tx.executeSql("SELECT name, lastname FROM your_table_name ORDER BY name ASC", [], function(tx, res) {
				var len = res.rows.length;
				for (var i = 0; i < len; i++) { // loop as many times as there are row results
					alert( res.rows.item(i).name +' '+ res.rows.item(i).lastname ); // showing the results
				}
			}, function(e) {
				alert("ERROR: " + e.message);
			});
		});
	};
	
})

This last step is just an example of how you can do to run your queries. Later you can edit or do whatever you want with the database.

Well, that is what I did, I hope this help.

Regards.


How to retrieve SQLite database has BLOB type to text in cordovaSQLite
#20

I’ve pulled the below code together from a number of examples on various forums, and it works well for my needs

.factory('DbAccess', function ($q, $cordovaSQLite, $ionicLoading, $log) {

    var DbAccess = {};

    var db = {};

    if (window.sqlitePlugin) {
        db = $cordovaSQLite.openDB("my.db");
    }
    else {
        db = window.openDatabase("my.db", "1.0", "my.db", 200000);
    }

    DbAccess.SingleResultHandler = function (deferred) {
        return function (tx, results) {
            var len = results.rows.length;
            var output_results = [];

            for (var i = 0; i < len; i++) {
                output_results.push(results.rows.item(i));
            }
            // Hide spinner dialog
            $ionicLoading.hide();
            deferred.resolve(output_results[0]);

        };
    };

    DbAccess.MultipleResultHandler = function (deferred) {
        return function (tx, results) {
            var len = results.rows.length;
            var output_results = [];

            for (var i = 0; i < len; i++) {
                output_results.push(results.rows.item(i));
            }
            // Hide spinner dialog
            $ionicLoading.hide();
            deferred.resolve(output_results);

        };
    };

    DbAccess.DefaultErrorHandler = function (err) {
        // Hide spinner dialog
        $ionicLoading.hide();
        $log.log("Error processing SQL: ", err.code);
    };

    DbAccess.promisedQuery = function (query, successCB, errorCB) {
        $log.log(query);
        // Show spinner dialog with message
        // Title and message only works on Android
        $ionicLoading.show({
            template: 'Loading hill information, please be patient...'
        });

        var deferred = $q.defer();
        db.transaction(function (tx) {
            tx.executeSql(query, [], successCB(deferred), errorCB);
        }, errorCB);
        return deferred.promise;
    };

    return DbAccess;

})

An example factory for creating and getting a user from the database is as follows : this shows both single query results and multiple

.factory(‘UserQueries’, function (DbAccess) {
var UserQueries = {
createUser: createUser,
getUsers:getUsers
};

    function createUser(userKey, userName) {
        var query = "Update User set Userkey ='"+ userKey +"',Username ='" + userName + "'";
        return DbAccess.promisedQuery(query, DbAccess.SingleResultHandler, DbAccess.DefaultErrorHandler);
    }

    function getUsers() {
        var query = "Select * from User";
        return DbAccess.promisedQuery(query, DbAccess.MultipleResultHandler, DbAccess.DefaultErrorHandler);
    }

    return UserQueries;
})

#21

Thanks I will check it out.


#22

I think i have everything working except getting the data to display. I get rows to return, but instead of displaying data I get “undefined” for every row of data. So if I run a query that returns 25 rows of data I get 25 rows but the actual data is not showing instead I get “undefined” in every column and every row. I get the right amount of rows returned but I must have missed something to get the data to display properly.


#23

Look in the example code that I used before in my comment

db.transaction(function(tx) {
	// running a sql querie 
	tx.executeSql("SELECT name, lastname FROM your_table_name ORDER BY name ASC", [], function(tx, res) {
		var len = res.rows.length;
		for (var i = 0; i < len; i++) { // loop as many times as there are row results
			alert( res.rows.item(i).name +' '+ res.rows.item(i).lastname ); // showing the results
		}
	}, function(e) {
		alert("ERROR: " + e.message);
	});
});
  • You do a sql querie, in the example to get the "name" and "lastname" columns of the "your_table_name" table and order by "name" in ascendent
tx.executeSql("SELECT name, lastname FROM your_table_name ORDER BY name ASC", [], function(tx, res) {..............................})
  • To show the data in the "name" and "lastname" columns of each row you should use the reference of each column in a cycle
for (var i = 0; i < len; i++) {
     alert( res.rows.item(i).name +' '+ res.rows.item(i).lastname );
}

Where "i" is the iterator to reference the specific row as a result and the ".name" and ".lastname" are the data that you want to get.

I hope this can help.

Thanks


#24

Hi @shubs,

If you’re still having trouble with pre-filled databases, you can check out a tutorial I did on the subject.

Hopefully that helps you out.

Regards,


#25

Nevermind I fixed it. Wasn’t working because the column name I was querying had the name is all uppercase. Once I changed that in my query all is well. Thanks!


#26

If you are having issues submitting to the App store with regards the policies surrounding what data you are allowed where please refer to my post in the link posted above by @nicraboy.


#27

How I can sync data from my sqlite mobile with remote json?


#28

Hi,

I had the same problem, and been looking for answers and almost all answers points out to use copy and stuff like that.

finally I got it working, I debugged the $cordovasqlite plugin, and found that it doesn’t support createFromLocation params.

So, here is the solution: use window.sqlitePlugin.opendatabase instead of window.openDB! simple

Check this article: http://redwanhilali.com/ionic-sqlite/


#29

Is very easy with SQLite (Tested with pre-filled databases with 25000 records) https://github.com/jdnichollsc/Ionic-Starter-Template

Regards, Nicholls


#30

@redhopit,

You plagiarized a significant amount of your article from my article. If you’re going to copy chunks of my article you need to link back to mine and give the appropriate credit.

Regards,