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;
})
@mattkraemer
Copy link

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
Copy link

@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
Copy link
Author

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
Copy link

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
Copy link

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
Copy link
Author

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
Copy link

This is nice looking code. Thanks for the examples!

@rebelholic
Copy link

Hi borissondagh ,

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

@botris
Copy link
Author

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
Copy link

thanks this is nice. 😄

@lardi
Copy link

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
Copy link
Author

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
Copy link

how do i store data from remote json

@pareshgami
Copy link

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

@aramirezreyes
Copy link

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
Copy link

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
Copy link

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

@botris
Copy link
Author

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
Copy link

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
Copy link

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
Copy link

@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
Copy link

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
Copy link

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

@botris
Copy link
Author

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