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