SQLITE Tutorial

hi can anyone give me a working example of sqlite plugin for ionic app. I have the tutorial from the official site but not very helpful. How do I see the results of the queries. Also is there any way to see the schema of the database of the application. I want to see the full schema of the database created so that I can verify it has been created correctly.

Hi friend,

I do believe this will help you, because it helped me as well…

Ionic / AngularJS service wrapper for Web SQL API / SQLite-Cordova-Plugin

1 Like

Yes i have read this. but i am having trouble in verifying the database I have created. I tried the query

db.transaction(function(tx) { tx.executeSql( "SELECT name FROM sqlite_master WHERE type='table'",[], function(res) { console.log("PRAGMA res: " + JSON.stringify(res)); },function(error) { console.log(error); }); });

to print table names. But it returns result {"db":{"openargs":{"name":"sample_db"},"dbname":"sample_db"},"txlock":true,"readOnly":false,"executes":[]}

Is there a way to test if the database created successfully.

Have you seen in services.js a line logging table creation? I used that to verify in my debugging (in chrome and in device)

Hi @saurabhgupta050,

Have you seen the SQLite tutorial I made not too long ago?:

Let me know if this helps you out.

Regards,

Hi @nicraboy I follow your tutorial and its awesome. But I have a problem when I try to customize it, please take a look at my code:

app.js

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

    db = $cordovaSQLite.openDB({ name: "my.db" });

    $cordovaSQLite.execute(db, "CREATE TABLE IF NOT EXIST people (id integer primary key, firstname text, lastname text)");
  });
})

.controller('AccountController', function($scope, $cordovaSQLite) {

  $scope.accounts = function() {
    var query = "SELECT firstname, lastname FROM people";
    $cordovaSQLite.execute(db, query);
  }

  $scope.addAccount = function(){
    var query = "INSERT INTO people (firstname, lastname) VALUES (?, ?)";
    $cordovaSQLite.execute(db, query, [$scope.firstnameText, $scope.lastnameText]);
    $scope.firstnameText = '';
    $scope.lastnameText = '';
  }

});

index.html

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="initial-scale=1, maximum-scale=1, user-scalable=no, width=device-width">
    <title></title>

    <link href="lib/ionic/css/ionic.css" rel="stylesheet">
    <link href="css/style.css" rel="stylesheet">

    <!-- IF using Sass (run gulp sass first), then uncomment below and remove the CSS includes above
    <link href="css/ionic.app.css" rel="stylesheet">
    -->

    <!-- ionic/angularjs js -->
    <script src="lib/ionic/js/ionic.bundle.js"></script>

    <!-- cordova script (this will be a 404 during development) -->
    <script src="js/ng-cordova.js"></script>
    <script src="cordova.js"></script>

    <!-- your app's js -->
    <script src="js/app.js"></script>
  </head>
  <body ng-app="starter">

    <ion-pane>

      <ion-header-bar class="bar-stable">
        <h1 class="title">Ionic Crud & SQLite</h1>
      </ion-header-bar>

      <ion-content ng-controller="AccountController">

          <form ng-submit="addAccount()">
            <div class="list">
              <label class="item item-input item-stacked-label">
                <span class="input-label">First Name</span>
                <input type="text" placeholder="John" ng-model="firstnameText">
              </label>
              <label class="item item-input item-stacked-label">
                <span class="input-label">Last Name</span>
                <input type="text" placeholder="Suhr" ng-model="lastnameText">
              </label>
              <div class="padding">
                <button class="button button-block button-positive">Create Account</button>
              </div>
            </div>
          </form>

          <ul class="list list-inset">
            <li class="item item-divider">
              {{accounts.length}} records
            </li>
            <li class="item" ng-repeat="account in accounts">
              <i class="icon ion-person"></i>&nbsp; - &nbsp;
              <span>{{account.firstname}} {{account.lastname}}</span>
            </li>
          </ul>

      </ion-content>

    </ion-pane>
  </body>
</html>

can you provide us implementation with form and table?

Wild guess, I’m also noob lol. I think it should be “EXISTS” instead of “EXIST”?

2 Likes

Hi All,
Can you please any one having experience in sqlite database synchronization with sql server 2012. If yes please post how to achieve the synchronization.

ie mobile sqlite db to server database & server database sqlite DB. two way synchronization need.
Please help me.

Email : gobimca2012@gmail.com

visit here to get details about how to use sqlite in ionic
http://datainflow.com/use-sqlite-ionic-framework/

I’ve compiled a comprehensive tutorial go through it. It will help you
http://waseemanswers.blogspot.com/2017/07/how-to-use-SQLite-with-ionic-v1.html

Hi I was able to do synchronization between SQLite and MySQL on a remote server with PHP as webservice. Check it out here: https://sellfy.com/p/gUWH/
This full app template was built with Ionic 3.6.0 version

I also need one example for that plugin