Prepopulated SQLite Databases in Ionic


#1

Hi,

I’m having some trouble integrating an SQLite database which has been prepopulated already.

My code can be found below, and my errors with various plugins at the end of this thread.

services.js

angular.module('starter.services', [])

.factory('Database', function() {
  // Opens the SQLite pre-populated DB
  var dbPath = 'db/test.db';
  var db = window.sqlitePlugin.openDatabase({name: dbPath});

  return {
    tablenames: function() {
      db.transaction(function(tx){
        tx.executeSql('SELECT name FROM sqlite_master WHERE type="table";', [],
          function(tx, res) {
            return res.rows.length;
          });
      }, function(e) {
          console.log("ERROR: " + e.message);
          alert(e.message)
      })
    }
  }
  
});

controllers.js

.controller('SettingsCtrl', function($scope, Database) {
  $scope.database = Database.tablenames();
})

app.js (extract)

.state('tab.settings', {
      url: '/settings',
      views: {
        'tab-settings': {
          templateUrl: 'templates/tab-settings.html',
          controller: 'SettingsCtrl'
        }
      }
    })

When trying to use the plugin found here: https://github.com/brodysoft/Cordova-SQLitePlugin, I get the following error in my console.log when running on an iPhone 5S:

2014-07-08 22:41:49.174 [36819:70b] SQLitePlugin openargs: {“name”:“db/TESTDB.sqlite”}
2014-07-08 22:41:49.175 [36819:70b] Detected docs path: /Users/redacted/Library/Application Support/iPhone Simulator/7.1/Applications/B3757E00-F0C9-41FB-9BFA-A01BA97AB72A/Documents
2014-07-08 22:41:49.191 [36819:70b] ERROR: error while trying to roll back: undefined

When trying to use the plugin found here: https://github.com/RikshaDriver/Cordova-PrePopulated-SQLitePlugin
Note: db is located in ionic www folder => db folder => test.db (db/test.db)

/platforms/ios/projectname/Plugins/com.phonegap.plugins.sqlite/SQLitePlugin.m:211:45: error: use of undeclared identifier ‘dbfile’
[self copyPrepopulatedDatabase:dbfile withDbname:dbname];
^
1 error generated.

** BUILD FAILED **

I don’t quite know what I’m doing wrong, but would appreciate any help whatsoever.

Thanks! :smile:


#2

Hi,

I’m looking at creating a pre-populated db at the moment as my seed sql is in the region of 75mb! Did you resolve this issue or find a way of getting around?

Thanks,

jpj


#3

This plugin makes the copy operation quite easy:

Then you are free to use your prepopulated db.


#4

Unfortunately I’m still having issues with this, I have used bower to import the project but I get a number of import issues which being a newbie I am struggling to get to grips with, are there any tutorials with Ionic specifically?

Would the above work when using ionic serve using chrome?


#5

the sqlplugin and sqlite copy thing would not work under the ionic serve, as I understand, they need the phone environment.

try ionic run ios, for example


#6

Thank you Pavel,

You are absolutely right, I have (with a clear head) got it up and running this morning, and I have to say it was straight forward - I think I was trying to over complicate it too.

So I did this following.

Installed ngCordova as instructed to on the web.
Ran bower install just to be sure.
Installed the sqlite plugin as instructed to on the ngCordova site.
Again ran bower install just to be sure
put the database initialisation code into my app.run
ran ionic run android
Used chrome to debug the app on my phone - all is working correctly.

Thanks all


What persistent data storages should i use?
#7

Sorry to revive such an old thread, but I’m wondering how you got it working. All I have is a info.db sqlite file and my ionic project. I want simply to import the file into the project, so that it can be used in the app.

I looked at many plugins, linked here and elsewhere. The one that sounded most promising was https://github.com/an-rahulpandey/cordova-plugin-dbcopy, linked by Pavel above, but it doesn’t offer good instructions, or any documentation, so I’m lost.


#8

Hi the dbcopy plugin mentioned above is very straight forward as all it does is copy the specified db file from the build directory to the phone directory where the phone’s OS expects sqlite db’s to reside. It’s really straight forward for android, anda little fiddly for IOS but a quick google should get you there in under 5 mins.

You will still need to write your database access code to open the database connection and run sql against it - this is the more complicated but. There are plenty of examples on this forum of how to do this.

If you are still struggling after giving it a go again please tell me which part of the implementation you are having trouble with - copying the database or accessing the copied database, or both.


#9

Actually, what I don’t know is simply how to install the plugin to use it in my project.

Additionally, the Readme has this snippet:

window.plugins.sqlDB.copy("demo",copysuccess,copyerror);

What is the “demo” value? The DB’s name? If I have a DB called “demo.db” in my build directory, will the plugin copy it with the same name into the phone?


#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.