How do I use the ngCordova sqlite service and the Cordova-SQLitePlugin

ionic-v1
1

I have been trying to incorperate sqlite into a simple Ionic app and this is the process I have been following:

 ionic start myApp sidemenu

Then I install the sqlite plugin:

ionic plugin add https://github.com/brodysoft/Cordova-SQLitePlugin

and ngCordova

bower install ngCordova

this gave me the following options:
Unable to find a suitable version for angular, please choose one:
1) angular#1.2.0 which resolved to 1.2.0 and is required by ngCordova#0.1.4-alpha
2) angular#>= 1.0.8 which resolved to 1.2.0 and is required by angular-ui-router#0.2.10
3) angular#1.2.25 which resolved to 1.2.25 and is required by angular-animate#1.2.25, angular-sanitize#1.2.25
4) angular#~1.2.17 which resolved to 1.2.25 and is required by ionic#1.0.0-beta.13Prefix the choice with ! to persist it to bower.json

I picked option 3)
and I included the scripts in the file as follows:

<script src="lib/ionic/js/ionic.bundle.js"></script>
<script src="lib/ngCordova/dist/ng-cordova.js"></script>
<script src="cordova.js"></script>
<script src="js/app.js"></script>
<script src="js/controllers.js"></script>

I then added a controller to the search view:

.controller('SearchCtrl', function ($scope, $cordovaSQLite){
  console.log('Test');
   var db = $cordovaSQLite.openDB({ name: "my.db" });

        // for opening a background db:
        var db = $cordovaSQLite.openDB({ name: "my.db", bgType: 1 });

        $scope.execute = function() {
          console.log('Test');
          var query = "INSERT INTO test_table (data, data_num) VALUES (?,?)";
          $cordovaSQLite.execute(db, query, ["test", 100]).then(function(res) {
            console.log("insertId: " + res.insertId);
          }, function (err) {
            console.error(err);
          });
     };
})

This caused the error:

TypeError: Cannot read property ‘openDatabase’ of undefined
at Object.openDB (http://localhost:8100/lib/ngCordova/dist/ng-cordova.js:2467:36)

Next I tried manually including the SQLitePlugin.js by:
copying from plugins/com.brodysoft.sqlitePlugin/www to main www/ and adding it to the index.html page

I tried including before everything:

 <script src="SQLitePlugin.js"></script>
<script src="lib/ionic/js/ionic.bundle.js"></script>
<script src="lib/ngCordova/dist/ng-cordova.js"></script>
<script src="cordova.js"></script>
<script src="js/app.js"></script>
<script src="js/controllers.js"></script>

I get Error ReferenceError: cordova is not defined
so I then tried including it after the cordova.js script but still get the same error

has anyone got any Ideas
I get Error ReferenceError: cordova is not defined
so I then tried including it after the cordova.js script but still get the same error

would really appreciate the help

In case it is relevant, these are the current versions of Cordova and ionic I am using:

ionic --version  1.2.5
cordova --version 3.5.0-0.2.7

and this is the generated bower.json

{
  "name": "myApp",
  "private": "true",
  "devDependencies": {
    "ionic": "driftyco/ionic-bower#1.0.0-beta.13"
  }
}

and my package.json:

{
  "name": "myapp",
  "version": "1.0.0",
  "description": "myApp: An Ionic project",
  "dependencies": {
    "gulp": "^3.5.6",
    "gulp-sass": "^0.7.1",
    "gulp-concat": "^2.2.0",
    "gulp-minify-css": "^0.3.0",
    "gulp-rename": "^1.2.0"
  },
  "devDependencies": {
    "bower": "^1.3.3",
    "gulp-util": "^2.2.14",
    "shelljs": "^0.3.0"
  }
}I get Error **ReferenceError: cordova is not defined**

so I then tried including it after the cordova.js script but still get the same error

would really appreciate the help
in case it is relevant, these are the current versions of Cordova and ionic I am using:

ionic --version  1.2.5
cordova --version 3.5.0-0.2.7

and this is the generated bower.json

{
  "name": "myApp",
  "private": "true",
  "devDependencies": {
    "ionic": "driftyco/ionic-bower#1.0.0-beta.13"
  }
}

and my package.json:

{
  "name": "myapp",
  "version": "1.0.0",
  "description": "myApp: An Ionic project",
  "dependencies": {
    "gulp": "^3.5.6",
    "gulp-sass": "^0.7.1",
    "gulp-concat": "^2.2.0",
    "gulp-minify-css": "^0.3.0",
    "gulp-rename": "^1.2.0"
  },
  "devDependencies": {
    "bower": "^1.3.3",
    "gulp-util": "^2.2.14",
    "shelljs": "^0.3.0"
  }
}

FYI: I have also asked the question on Stackoverflow

1 Like
2

Turns out It does work but only on device as it is platform specific… i.e. works on device not with ionic serve

1 Like
3

Does it work on device only when you include it in the /www repository or is that not needed?

4

For people having similar problems and needing some info, this worked for me using Visual Studio Tools for Apache Cordova (code from my index.html page, inside “head” section).

Note how I am referencing the SQLite plugin file, even though in my app folder hierarchy, it sits under, plugins/com.brodysoft.sqlitePlugin/www/

<script src="scripts/ionic.bundle.js"></script>

<!-- cordova script (this will be a 404 during development) -->
<script src="scripts/ng-cordova.js"></script>
<script src="cordova.js"></script>
<script src="scripts/platformOverrides.js"></script>
<script src="scripts/angular-resource.min.js"></script>
<!--<script src="plugins/com.brodysoft.sqlitePlugin/www/SQLitePlugin.js"></script> - does not work this way-->
<script src="SQLitePlugin.js"></script>

<script type="text/javascript">
    document.addEventListener("deviceready", onDeviceReady, false);
    function onDeviceReady() {
        var db;

        alert("window.cordova: " + window.cordova);
        alert("window.SQLitePlugin: " + window.SQLitePlugin);

        if (window.cordova && window.SQLitePlugin) { // because Cordova is platform specific and doesn't work when you run ionic serve               
            db = window.sqlitePlugin.openDatabase({ "name": "snet1.db" }); //device - SQLite
            alert("device db (SQLite) loaded");
        } else {

            db = window.openDatabase("APSNetMobileDb", "1.0", "snet1.db", 100 * 1024 * 1024); // browser webSql, a fall-back for debugging
            alert("browser db (WebSQL) loaded");
        }

        db.transaction(populateDB, errorCB, successCB);
    }
    function populateDB(tx) {

        tx.executeSql("DROP TABLE IF EXISTS demo");
        //alert("table dropped (if existed)");
        tx.executeSql('CREATE TABLE IF NOT EXISTS demo (id integer primary key unique, data)');
        //alert("table created");
        tx.executeSql('INSERT INTO demo (id, data) VALUES (1, "First row")');
        tx.executeSql("INSERT INTO demo (id, data) VALUES (?,?)", [2, 'Second Row']);
        alert("inserted rows");
        queryDB(tx);
    }
    function queryDB(tx) {
        tx.executeSql("SELECT id, data FROM demo;", [], querySuccess, errorCB);
    }
    function querySuccess(tx, results) {
        var len = results.rows.length;

        for (var i = 0; i < len; i++) { // loop as many times as there are row results
            document.getElementById("output").innerHTML +=
            "<table><tr><td>ID = " + results.rows.item(i).id +
            "</td><td>data = " + results.rows.item(i).data +
            "</td></tr></table>";
        }
    }

    function errorCB() {
        alert("DB access FAILED");
    };
    function successCB() {
        alert("DB access SUCCEEDED");
    };
</script>


<!-- your app's js -->
<script src="scripts/app/app.js"></script>
<script src="scripts/app/controllers.js"></script>
<script src="scripts/app/index.js"></script>
1 Like
5

Is there a way to tell if a table exists using this?
I know I can create/drop table if exists, but I simply want to know if the table exists and if so do a query.
Should I just do a create if not exists, or is there a way to just see if it exists.

8

See my example: https://github.com/jdnichollsc/Ionic-Starter-Template

Regards, Nicholls

9

Hi, you can use this full 100% working template that uses SQLite, full documented App here: http://codecanyon.net/item/quizionic-a-quiz-app-template-for-ionic-framework-with-sqlite-database/1420590435
It also shows how to use a pre-populated database as well as other useful plugins. Highly recommended.

10

Put this on .run (the default one when you create an Ionic app)

window.sqlitePlugin.echoTest(successCallback, errorCallback);

as mentioned here GitHub - storesafe/cordova-sqlite-storage: A Cordova/PhoneGap plugin to open and use sqlite databases on Android, iOS and Windows with HTML5/Web SQL API