Key/Value storage using #Sqlite in #Cordova / #Phonegap
When offline data storage needs in a PhoneGap / Cordova application outgrow the 5Mb limits of LocalStorage, then you need to turn to Sqlite. However, this doesn’t mean that you have to redesign everything. Perhaps you are still happy with key/value storage. So, here’s two simple function calls to save and load data from a simple 2 column database table using Cordova / Phonegap and the plugin from litehelpers
function SqlStorageSetItem(key,value)
{
// Location 2 – not backed to iCloud
var db = window.sqlitePlugin.openDatabase({name: “sql.db”, location: 2}, function(){
console.log(“Opened database ok”);
}, function(err){
console.log(‘Open database ERROR: ‘ + JSON.stringify(err));
});db.executeSql(“Create table if not exists keyValuePair (key,value);”,[], function(res){
console.log(“Created table if not already present”);
});db.executeSql(“select key from keyValuePair where key='” + key + “‘”,[],function(res){
if (res.rows.length == 0)
{
console.log(“Key not present, inserting row”);
db.executeSql(“insert into keyValuePair (key,value) values (‘” + key + “‘,'” + value + “‘)”,[], function(res){
console.log(“Added ” + key + ” to keyValuePair database”);
});
}
else
{
db.executeSql(“update keyValuePair set value='” + value + “‘ where key = ‘” + key + “‘”,[], function(res){
console.log(“Updated ” + key + ” to keyValuePair database”);
});
}
});
}function SqlStorageGetItem(key,callback)
{
// Location 2 – not backed to iCloud
var db = window.sqlitePlugin.openDatabase({name: “sql.db”, location: 2}, function(){
console.log(“Opened database ok”);
}, function(err){
console.log(‘Open database ERROR: ‘ + JSON.stringify(err));
});db.executeSql(“select value from keyValuePair where key='” + key + “‘”,[],function(res){
if (res.rows.length == 0)
{
console.log(“No matching key found”);
callback(null);
}
else
{
console.log(“Key found”);
callback(res.rows.item(0).value);
}
});
}
The one key difference, is that the SqlStorageGetItem function is asynchronous, so you have to pass a callback to it, in order to get the value.