Using sqlite in Ionic Apps with anguarjs


#1

Hello there,
I was looking for some example of using sqlite in ionic applications, and I could not find anything. Some working example?


#2

Give the following a shot - I cribbed it from else where so wont take credit for it but I use something similar and it works for me:

angular.module('app.DAO', ['app.DAO.service']);

angular.module('app.DAO.service', [])
.factory('DbAccess', function ($q) {
var DbAccess = {};
var db = window.openDatabase("DatabaseName", "1.0", "DatabaseName", 200000);

DbAccess.ClearDownDB = function(tx) {
	// Drop Tables
	//console.log("Dropping Tables");

	tx.executeSql('DROP TABLE PROFILE');
	tx.executeSql('DROP TABLE TABLE1');


};

DbAccess.PopulateDB = function(tx) {
	
	// CREATE THE TABLES
	//console.log("CREATING TABLES IF NOT EXISTS ");
	
	// TABLE1
    //console.log("creating TABLE1 table");
    tx.executeSql('CREATE TABLE IF NOT EXISTS TABLE1 (id INTEGER NOT NULL PRIMARY KEY UNIQUE, vin TEXT NOT NULL, name TEXT NOT NULL, make TEXT, model TEXT, year TEXT, fuelTypeId INTEGER, consumptionTypeId INTEGER, currencyId INTEGER, purchaseDate TEXT, purchaseAmount INTEGER, purchaseOdoReading INTEGER, tyreSize TEXT, fuelUnitTypeId INTEGER)');

    console.log("COMPLETED CREATION");
};

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));
		}

		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));
		}

		deferred.resolve(output_results);

	};
};

DbAccess.DefaultErrorHandler = function(err) {
	console.log("Error processing SQL: " + err.code);
};

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

DbAccess.InitDB= function() {
	db.transaction(DbAccess.PopulateDB, DbAccess.DefaultErrorHandler, DbAccess.MultipleResultHandler);
};

DbAccess.ResetDB= function() {
	db.transaction(DbAccess.ClearDownDB, DbAccess.DefaultErrorHandler, DbAccess.MultipleResultHandler);
};

return DbAccess;

})

.factory('DBQueries', function(DbAccess) {
    var DBQueries = {};


DBQueries.GaetAllTABLE1 = function(TABLE1) {
	var query = 'Select * FROM TABLE1 ';
	return DbAccess.promisedQuery(query, DbAccess.SingleResultHandler, DbAccess.DefaultErrorHandler);
};


return DBQueries;

});

#3

Thank you, it looks good.
I’m having trouble consuming this service from the controller. Can you provide me a brief example?


#4

UPDATE: Finally got this solved, but now i dont know how to load an existent sqlite file. I need help with this please.


#5

Sorry should have included that, you need to inject DBQueries into your controller then you can call DBQueries.GaetAllTABLE1(); please excuse the typo, the above was just a quick hacked out reply.


#6

I’d assume that you would just put the right name in var db = window.openDatabase(“DatabaseName”, “1.0”, “DatabaseName”, 200000); but I havent done this


#7

Code is not working, the error is Error processing SQL: undefined


#8

.factory(‘DbAccess’, function ($q) {
var DbAccess = {};
var db = window.openDatabase(“epl.sqlite”, “1.0”, “Database”, -1);

	DbAccess.ClearDownDB = function(tx) {
		// Drop Tables
		//console.log("Dropping Tables");

		//tx.executeSql('DROP TABLE PROFILE');
		//tx.executeSql('DROP TABLE TABLE1');


	};
	DbAccess.PopulateDB = function(tx) {

		// CREATE THE TABLES
		//console.log("CREATING TABLES IF NOT EXISTS ");

		// TABLE1
		//console.log("creating TABLE1 table");
		tx.executeSql('CREATE TABLE IF NOT EXISTS TABLE1 (id INTEGER NOT NULL PRIMARY KEY UNIQUE, vin TEXT NOT NULL, name TEXT NOT NULL, make TEXT, model TEXT, year TEXT, fuelTypeId INTEGER, consumptionTypeId INTEGER, currencyId INTEGER, purchaseDate TEXT, purchaseAmount INTEGER, purchaseOdoReading INTEGER, tyreSize TEXT, fuelUnitTypeId INTEGER)');

		console.log("COMPLETED CREATION");
	};
	
	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));
			}

			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));
			}

			deferred.resolve(output_results);

		};
	};

	DbAccess.DefaultErrorHandler = function(err) {
		console.log("Error processing SQL: " + err.code);
	};

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

	DbAccess.InitDB= function() {
		db.transaction(DbAccess.PopulateDB, DbAccess.DefaultErrorHandler, DbAccess.MultipleResultHandler);
	};

	DbAccess.ResetDB= function() {
		db.transaction(DbAccess.ClearDownDB, DbAccess.DefaultErrorHandler, DbAccess.MultipleResultHandler);
	};

	return DbAccess;
})

.factory('DBQueries', function(DbAccess) {
	var DBQueries = {};


	DBQueries.GatTable = function(TABLE1) {
		var query = 'Select * FROM '+TABLE1;
		console.log(query);
		return DbAccess.promisedQuery(query, DbAccess.SingleResultHandler, DbAccess.DefaultErrorHandler);
	};


	return DBQueries;
})

#9

Kindly tell me, what the problem with this.


#11

Where it’s the service throwing the error? Which line or function? I might be able to help you, o got this code working a couple days ago.


#12

Having a . in the database name is probably your problem, you have to initialise the database first, create the tables, and only then can you query against it


#13

hai i want to create 10 table in sqlite? but i got an error named undefined openDb. i used to define it in my controller itself.not only that i cant use sqlite when i use this i got the same error please help me


#14

Hi, you can explore this 100% working template that uses SQLite as well as other Cordova plugins that illustrates the proper way to use a SQLite pre-populated database.

This template is full documented App: http://codecanyon.net/item/quizionic-a-quiz-app-template-for-ionic-framework-with-sqlite-database/14205904


#15

Test my example: https://github.com/jdnichollsc/Ionic-Starter-Template

Regards, Nicholls