Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
cordova plugin add https://github.com/brodysoft/Cordova-SQLitePlugin.git
var db = null;
angular.module('myapp', ['ionic', 'myapp.controllers', 'myapp.services', 'ngCordova'])
.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) {
// org.apache.cordova.statusbar required
StatusBar.styleDefault();
}
if(window.cordova) {
// App syntax
db = $cordovaSQLite.openDB("myapp.db");
} else {
// Ionic serve syntax
db = window.openDatabase("myapp.db", "1.0", "My app", -1);
}
$cordovaSQLite.execute(db, "CREATE TABLE IF NOT EXISTS team (id integer primary key, name text)");
});
})
.controller('TeamCtrl', function($scope, Team) {
$scope.team = [];
$scope.team = null;
$scope.updateTeam = function() {
Team.all().then(function(team){
$scope.team = team;
});
}
$scope.updateTeam();
$scope.createNewTeamMember = function(member) {
Team.add(member);
$scope.updateTeam();
};
$scope.removeMember = function(member) {
Team.remove(member);
$scope.updateTeam();
};
$scope.editMember = function(origMember, editMember) {
Team.update(origMember, editMember);
$scope.updateTeam();
};
})
<!DOCTYPE html>
<html>
<head>
<!-- standard Ionic head -->
<!-- Download https://github.com/driftyco/ng-cordova/archive/master.zip -->
<script src="js/ng-cordova.min.js"></script>
angular.module('myapp.services', [])
.factory('DBA', function($cordovaSQLite, $q, $ionicPlatform) {
var self = this;
// Handle query's and potential errors
self.query = function (query, parameters) {
parameters = parameters || [];
var q = $q.defer();
$ionicPlatform.ready(function () {
$cordovaSQLite.execute(db, query, parameters)
.then(function (result) {
q.resolve(result);
}, function (error) {
console.warn('I found an error');
console.warn(error);
q.reject(error);
});
});
return q.promise;
}
// Proces a result set
self.getAll = function(result) {
var output = [];
for (var i = 0; i < result.rows.length; i++) {
output.push(result.rows.item(i));
}
return output;
}
// Proces a single result
self.getById = function(result) {
var output = null;
output = angular.copy(result.rows.item(0));
return output;
}
return self;
})
.factory('Team', function($cordovaSQLite, DBA) {
var self = this;
self.all = function() {
return DBA.query("SELECT id, name FROM team")
.then(function(result){
return DBA.getAll(result);
});
}
self.get = function(memberId) {
var parameters = [memberId];
return DBA.query("SELECT id, name FROM team WHERE id = (?)", parameters)
.then(function(result) {
return DBA.getById(result);
});
}
self.add = function(member) {
var parameters = [member.id, member.name];
return DBA.query("INSERT INTO team (id, name) VALUES (?,?)", parameters);
}
self.remove = function(member) {
var parameters = [member.id];
return DBA.query("DELETE FROM team WHERE id = (?)", parameters);
}
self.update = function(origMember, editMember) {
var parameters = [editMember.id, editMember.name, origMember.id];
return DBA.query("UPDATE team SET id = (?), name = (?) WHERE id = (?)", parameters);
}
return self;
})
@malmosands

This comment has been minimized.

Copy link

@malmosands malmosands commented Jan 29, 2015

Hi, great work, but could you post the index.html as well? I'm not sure how to link the form to the script.

Thank you!

@alexweber

This comment has been minimized.

Copy link

@alexweber alexweber commented Jan 31, 2015

@borissondagh, thanks for sharing this is really useful! I had to wrap the query method in $ionicPlatform.ready to get it to work as expected, though:

(building & emulating on ios)

self.query = function (query, parameters) {
  parameters = parameters || [];
  var q = $q.defer();

  $ionicPlatform.ready(function () {
    $cordovaSQLite.execute(self.db, query, parameters)
      .then(function (result) {
        q.resolve(result);
      }, function (error) {
        console.warn('I found an error');
        console.warn(error);
        q.reject(error);
      });
  });

  return q.promise;
};
@botris

This comment has been minimized.

Copy link
Owner Author

@botris botris commented Feb 2, 2015

@Matthiaskra
Have a look at https://blog.nraboy.com/2014/11/use-sqlite-instead-local-storage-ionic-framework for the full index.html. But basically you take a standard Ionic app and just add the line

<script src="js/ng-cordova.min.js"></script>

Of course make sure you've added the ng-cordova script to your project.

@alexweber, was that iOS specific? At the moment I'm working just with Ionic serve for my project, which seems fine. But thanks for sharing. I'll test and update the gist.

@chrisciampoli

This comment has been minimized.

Copy link

@chrisciampoli chrisciampoli commented Mar 14, 2015

Hey Borris, thanks for this Gist! I'm having an issue where, using the syntax to open SQLite works for both emulation and browser. When I use a physical android device it errors out though, and does not work.

@marentwickler

This comment has been minimized.

Copy link

@marentwickler marentwickler commented Mar 17, 2015

hi chrisciampoli , for physical device change db = window.openDatabase("myapp.db", "1.0", "My app", -1);
with db = $cordovaSQLite.openDB({ name: "myapp.db" }); then
you have full function.

@botris

This comment has been minimized.

Copy link
Owner Author

@botris botris commented Mar 19, 2015

Hi Chris, it was hinted in the code comments, but marentwickler is right. I've updated the gist so it automagicly uses the right DB.

To clarify, the $cordovaSQLite.openDB uses the Cordova-SQLitePlugin, which makes sqlite available with Cordova obviously. The 'window.openDatabase' uses the Web SQL database of your browser.

I've also updated the services.js to include the improvement by alexweber which is needed in most use cases.

@volantes

This comment has been minimized.

Copy link

@volantes volantes commented May 12, 2015

This is nice looking code. Thanks for the examples!

@rebelholic

This comment has been minimized.

Copy link

@rebelholic rebelholic commented May 17, 2015

Hi borissondagh ,

How I can async data from sqlite db with remote server?

@botris

This comment has been minimized.

Copy link
Owner Author

@botris botris commented May 19, 2015

@rebelholic you could have a look at PouchDB it can async data with CouchDB and is in fact a layer on top of sqlite.

@joicacanindin

This comment has been minimized.

Copy link

@joicacanindin joicacanindin commented Jun 5, 2015

thanks this is nice. 😄

@lardi

This comment has been minimized.

Copy link

@lardi lardi commented Jun 9, 2015

Hi borissondagh,
Thanks for sharing this helpful code.
but can I get a data from pre-filled sqlite file from this code, Thanks.

@botris

This comment has been minimized.

Copy link
Owner Author

@botris botris commented Jul 1, 2015

Hi Lardi, so what I do is create and read the "filled" variable and if it's not set fill it with

in app.js is:

$cordovaSQLite.execute(db, "CREATE TABLE IF NOT EXISTS team (id integer primary key, name text)");
var teamfilled = null;

if(teamfilled !== true) {
        $http.get('js/filldb.json').success(function(data) {
          // you can do some processing here
          obj  = data;
        }).then(function(){
          angular.forEach(obj.team, function(value) {
            Team.add(value);
            // console.log(value);
          });
          // Call a factory that sets the table-filled-variable to true
          Config.add("team", "filled");
        })
      }
@hridayakandel

This comment has been minimized.

Copy link

@hridayakandel hridayakandel commented Jul 8, 2015

how do i store data from remote json

@pareshgami

This comment has been minimized.

Copy link

@pareshgami pareshgami commented Jul 10, 2015

Wow.!!!!! Amazing.!!!! Really really nice.

@aramirezreyes

This comment has been minimized.

Copy link

@aramirezreyes aramirezreyes commented Jul 24, 2015

This is awesome, mate! I started developing with ionic with no angular.js background and this has been exceptionally useful to understand the structure of an app with controllers and services!
Thanks!

@whitem24

This comment has been minimized.

Copy link

@whitem24 whitem24 commented Aug 20, 2015

hi, Nice post... I'm having an issue, i did follow the steps, but when i'm triying to test the code on my physical android device, it freeze, so it doesn't work to me. However, it works on my web browser...

I have the following test template, the rest of the code is the same.

letras.html

<ion-view view-title="Letras">
  <ion-content>
    <ion-list>
      <ion-item class="item-remove-animate item-avatar item-icon-right" ng-repeat="te in team" type="item-text-wrap" href="#">
        <h2>{{te.id}}</h2>
        <p>{{te.name}}</p>

        <i class="icon ion-chevron-right icon-accessory"></i>
      </ion-item>

    </ion-list>
  </ion-content>
</ion-view>
@praveen2308

This comment has been minimized.

Copy link

@praveen2308 praveen2308 commented Aug 24, 2015

Thanks a lot for the gist .. Really really really helpful 👍

@botris

This comment has been minimized.

Copy link
Owner Author

@botris botris commented Sep 23, 2015

Glad it's useful, unfortunately github doesn't provide alerts on comments so I'm a little late on response.

@CarolinaKinetic

This comment has been minimized.

Copy link

@CarolinaKinetic CarolinaKinetic commented Oct 11, 2015

Don't forget to add myApp.services to your index.html if it isn't already in there. (If it wasn't for Firebug and StackOverflow, I'd probably STILL be trying to figure this one out... :-)

@jdnichollsc

This comment has been minimized.

Copy link

@jdnichollsc jdnichollsc commented Oct 23, 2015

See my example using ngCordova and Service Pattern :)
https://gist.github.com/jdnichollsc/9ac79aaa3407e92677ba/

To download the code: http://1drv.ms/1Ono0Ys

Regards, Nicholls

@tarekeldeeb

This comment has been minimized.

Copy link

@tarekeldeeb tarekeldeeb commented Oct 28, 2015

@borissondagh, I followed you but got the following error in chrome:
Exception in onResRdy: TypeError: Cannot read property 'htmlRes' of undefined

All my code: https://gist.github.com/tarekeldeeb/975cbe0fc85239766e9f

@oyeyemi

This comment has been minimized.

Copy link

@oyeyemi oyeyemi commented Oct 31, 2015

Thanks for the great work Boriss.
I got an error when trying to display a single item.

"Error: Failed to execute 'item' on 'SQLResultSetRowList': The index provided (0) is greater than or equal to the maximum bound (0).
at Error (native)
at Object.self.getById"
Thanks for your help.

@dirkpostma

This comment has been minimized.

Copy link

@dirkpostma dirkpostma commented May 11, 2016

If I'm correct, get getById method returns the first result. Is "getFirst" not a much better method name?

@botris

This comment has been minimized.

Copy link
Owner Author

@botris botris commented May 13, 2016

Hi Dirk, no it returns 1 result based on the ID, so not the first result.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment