Unable to open SQLite database in Android

[jgw96]

From @fjms on December 14, 2016 13:8

Ionic version: (check one with "x") [ ] 1.x [x ] 2.x

I'm submitting a ... (check one with "x") [x ] bug report [ ] feature request [ ] support request => Please do not submit support requests here, use one of these channels: https://forum.ionicframework.com/ or http://ionicworldwide.herokuapp.com/

Current behavior: Unable to open database in Android

Expected behavior: Open a SQLite database

Steps to reproduce: Create a new ionic 2 blank project Add platform android Install cordova-sqlite-storage See this https://ionicframework.com/docs/v2/native/sqlite/ ionic run android

Related code:

Other information:

Ionic info: (run ionic info from a terminal/cmd prompt and paste output below):

Cordova CLI: 6.4.0
Ionic Framework Version: 2.0.0-rc.3
Ionic CLI Version: 2.1.12
Ionic App Lib Version: 2.1.7
Ionic App Scripts Version: 0.0.45
ios-deploy version: Not installed
ios-sim version: Not installed
OS: Windows 7
Node Version: v6.9.1
Xcode version: Not installed

Copied from original issue: driftyco/ionic#9635

[jgw96]

From @jayeshanandani on December 14, 2016 16:39

I am using SQLite and I haven't faced this issue. It works perfectly fine. Can you please give some more information like android version or device on which this is being tested upon to replicate or share some piece of code which can help?

[ihadeed]

@fjms are you waiting for deviceready to fire before running that code?

[megharajdeepak]

I tried same (executing a query on a db placed under 'www\test.db.sqlite' folder of ionic project) on MAC and i get this error both on browser and on iOS simulator:

ReferenceError: sqlitePlugin is not defined

Code:

constructor(public navCtrl: NavController, public platform: Platform,
                public pps: ProdPerfService){
        platform.ready().then((readySource) => {
            pps.getSummary(); //pps is a provider named ProdPerfService
        });
    } 

//ProdPerfService:
import { Injectable } from '@angular/core';
import { SQLite } from 'ionic-native';

@Injectable()
export class ProdPerfService {
    constructor(){

    }

    getSummary(){
        let db = new SQLite();
        db.openDatabase({
            name: 'test.db.sqlite',
            location: 'default' // the location field is required
        }).then(() => {
            db.executeSql('select * from summary', {}).then(() => {
                alert('result');

        }, (err) => {
            console.error('Unable to execute sql: ', err);
            alert('err');
        })
        }, (err) => {
            console.error('Unable to open database: ', err);
            alert(err);
        });
    }

}

Cordova CLI: 6.4.0 Ionic Framework Version: 2.0.0-rc.3 Ionic CLI Version: 2.1.17 Ionic App Lib Version: 2.1.7 Ionic App Scripts Version: 0.0.45 ios-deploy version: Not installed ios-sim version: Not installed OS: OS X El Capitan Node Version: v7.2.1 Xcode version: Xcode 8.1 Build version 8B62

[ihadeed]

@megharajdeepak are you using livereload? what is the error message that you're getting?

[megharajdeepak]

Yes, it's a live reload.

This is the stack trace i get:

prodperf-service.ts:28 Unable to open database:  ReferenceError: sqlitePlugin is not defined
    at eval (/Users/Deepak/Documents/ionicAngular2_Workspace/ChartApp/node_modules/ionic-native/dist/esm/plugins/sqlite.js:76:13)
    at new t (http://localhost:8100/build/polyfills.js:3:15636)
    at SQLite.openDatabase (/Users/Deepak/Documents/ionicAngular2_Workspace/ChartApp/node_modules/ionic-native/dist/esm/plugins/sqlite.js:75:16)
    at ProdPerfService.getSummary (/Users/Deepak/Documents/ionicAngular2_Workspace/ChartApp/src/pages/prodperf/prodperf-service.ts:22:12)
    at eval (/Users/Deepak/Documents/ionicAngular2_Workspace/ChartApp/src/pages/prodperf/prodperf.ts:33:17)
    at t.invoke (http://localhost:8100/build/polyfills.js:3:13422)
    at Object.onInvoke (/Users/Deepak/Documents/ionicAngular2_Workspace/ChartApp/node_modules/@angular/core/src/zone/ng_zone.js:238:37)
    at t.invoke (http://localhost:8100/build/polyfills.js:3:13373)
    at e.run (http://localhost:8100/build/polyfills.js:3:10809)
    at http://localhost:8100/build/polyfills.js:3:8911(anonymous function) @ prodperf-service.ts:28t.invoke @ polyfills.js:3onInvoke @ ng_zone.js:238t.invoke @ polyfills.js:3e.run @ polyfills.js:3(anonymous function) @ polyfills.js:3t.invokeTask @ polyfills.js:3onInvokeTask @ ng_zone.js:229t.invokeTask @ polyfills.js:3e.runTask @ polyfills.js:3i @ polyfills.js:3invoke @ polyfills.js:3

[ihadeed]

Please follow the update instructions here https://github.com/driftyco/ionic-app-scripts/releases/tag/v0.0.47 and then try again. This is most likely an issue with livereload (it's broken in the app-scripts version you're using).

[megharajdeepak]

Ok. Ill try that and let you know. But it also fails on iOS Simulator. Anyway, let me give it a try. Hope it works. thanks

[fjms]

@ihadeed Yes, I use it into platform.ready().then(...)

[ihadeed]

@fjms are you using livereload? if so, read my previous comment.

[megharajdeepak]

@ihadeed I tried the following:

npm install -g ionic@latest

//Deleted main.dev.ts and main.prod.ts and created a main.ts file with the following content:

import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';

import { AppModule } from './app.module';

platformBrowserDynamic().bootstrapModule(AppModule);

//made sure package.json had following scripts:

"scripts": {
    "ionic:build": "ionic-app-scripts build",
    "ionic:serve": "ionic-app-scripts serve"
  }

installed latest app scripts using:

npm install @ionic/app-scripts@latest --save-dev

and finally ran ionic serve. I still get the same error message like before:

prodperf-service.ts:19 Unable to open database:  ReferenceError: sqlitePlugin is not defined
    at http://localhost:8100/build/main.js:73360:13
    at new t (http://localhost:8100/build/polyfills.js:3:15636)
    at SQLite.openDatabase (http://localhost:8100/build/main.js:73359:16)
    at ProdPerfService.getSummary (http://localhost:8100/build/main.js:97186:12)
    at http://localhost:8100/build/main.js:51462:17
    at t.invoke (http://localhost:8100/build/polyfills.js:3:13422)
    at Object.onInvoke (http://localhost:8100/build/main.js:33469:37)
    at t.invoke (http://localhost:8100/build/polyfills.js:3:13373)
    at e.run (http://localhost:8100/build/polyfills.js:3:10809)
    at http://localhost:8100/build/polyfills.js:3:8911

[megharajdeepak]

When we say location of db file as 'default', where does ionic look for the db file? it is www folder right?

[megharajdeepak]

Anybody from the team got any update on this?

[jayeshanandani]

@megharajdeepak : This is certainly device ready issue error or plugin not being installed while running in the simulator as it works perfectly fine for me.

[megharajdeepak]

@jayeshanandani plugin is installed and sqlite db is opened on device ready. Does it work on browser too for you? Can you kindly share ionic2 package json? May be libraries' versions are different in my case.

[jayeshanandani]

@megharajdeepak : This will work only on simulators and devices. On browser it will fall back to WEBSQL. I use following custom provider to work with and it works well everywhere:

https://gist.github.com/jayeshanandani/c3e82d6545ac0659a4e1d93814b525a6

[megharajdeepak]

Got something working:

const win: any = window;

constructor(private platform: Platform) {
        if (this.platform.is('cordova')) {
            this._db = new SQLite();
            this._db.openDatabase({
                name: DB_NAME,
                location: 'default' // the location field is required
            }).then(() => {
                this._db.executeSql('create table danceMoves(name VARCHAR(32))', {}).then(() => {
                alert('query ran');

        }, (err) => {
            alert('Unable to execute sql: '+ err);
            console.error('Unable to execute sql: ', err);
        });
            })
        } else {
            console.warn('Storage: SQLite plugin not installed, falling back to WebSQL. Make sure to install cordova-sqlite-storage in production!');
            this._db = win.openDatabase(DB_NAME, '1.0', 'database', 5 * 1024 * 1024);

        }
    }

When i run that in simulator, i get a alert saying 'query ran' which means, danceMoves table is created. I have given DB name as 'test'. But where do I find this DB (physical path)? location is 'default', shouldn't db be in 'www' folder? I couldn't find it there. Anybody knows where does ionic create this DB? Can't I give a specific location like src/app/DBs/mydb.sqlite ?

[ihadeed]

@megharajdeepak

Anybody knows where does ionic create this DB? Can't I give a specific location like src/app/DBs/mydb.sqlite ?

Ionic doesn't create the database for you, this plugin does: https://github.com/litehelpers/Cordova-sqlite-storage

[grsx]

I have the same Problem with Android.

EDIT: found a solution that works for me. See below.

My Code:

private database: SQLite;

  public constructor(private platform: Platform){
     this.platform.ready().then(() => {
       if (this.platform.is('cordova')) {
       console.log(this.platform.platforms());
       }
       else {
         console.log(this.platform.platforms());
       }
       this.database = new SQLite();
       this.database.openDatabase({name: "data.db", location: "default"}).then(() => {
         /*Do Something*/
       }, (error) => {
         console.log("ERROR: Unable to open databse 'data.db' (" + error + ").");
       });

      });
  }

"ionic run android" works perfectly, the database gets created or opend. When i use "ionic run android -l -c" i get

[16:35:33] console.log: Angular 2 is running in the development mode. Call enableProdMode() to enable the production
mode.
[16:35:33] console.warn: Native: tried calling StatusBar.styleDefault, but Cordova is not available. Make sure to
include cordova.js or run in a device/simulator
[16:35:33] console.log: mobile,android,mobileweb
[16:35:33] console.log: ERROR: Unable to open databse 'data.db' (ReferenceError: sqlitePlugin is not defined).

Using "ionic run android", this.platforms.platforms() returns "cordova", "mobile", "android".

Has anybody an idea, why this happens?

ionic info:

ordova CLI: 6.4.0 
Ionic Framework Version: 2.0.0-rc.3 
Ionic CLI Version: 2.1.18 
Ionic App Lib Version: 2.1.9 
Ionic App Scripts Version: 0.0.45 
ios-deploy version: Not installed 
ios-sim version: Not installed 
OS: Windows 10 
Node Version: v6.9.2 
Xcode version: Not installed

Edit: I just figuered out what my problem was: the "scripts" part of my package.json was

"scripts": {
    "ionic:build": "ionic-app-scripts build",
    "ionic:serve": "ionic-app-scripts serve"
  },

i changed it to this configuration, which i got from another of my projects:

"scripts": {
    "build": "ionic-app-scripts build",
    "watch": "ionic-app-scripts watch",
    "serve:before": "watch",
    "emulate:before": "watch",
    "deploy:before": "build",
    "build:before": "build",
    "run:before": "watch"
  },

and now everything works fine, this.platform.platforms() returns "cordova, mobile, android".

[megharajdeepak]

This is what I did and got it working (which solved our purpose):

1. Installed 'File' (to use path in mobile to store a file) and 'Transfer'(to download a remote zip(sqlite) file) cordova plugins.
2. Unzip the file by installing 'Zip' plugin.
3. Finally use 'SQLite' plugin to read the downloaded sqlite file.

Basic code:

// Cordova
declare var cordova: any;

//using 'Transfer' plugin to download:
download() {
    const fileTransfer = new Transfer();
    let url = 'https://yourserver.com/yourdatafiles/sqlitedbfile.zip';
//cordova.file.applicationStorageDirectory is used from 'File' plugin
    fileTransfer.download(url, cordova.file.applicationStorageDirectory+'/library/localdatabase/' + 'myfile.zip').then((entry) => {
      console.log('download complete: ' + entry.toURL());
      alert(JSON.stringify(entry.toURL())); //this will give you the path where file is stored

    }, (error) => {
      // handle error
      this.state = 'error';
      alert(JSON.stringify(error));
    });
  }

//code to unzip 
unzip() {
    let source: string = cordova.file.applicationStorageDirectory+'/library/localdatabase/myfile.zip';
    let dest: string = cordova.file.applicationStorageDirectory+'/library/localdatabase/';
    Zip.unzip(source, dest, (progress) => {this.perc = Math.round((progress.loaded / progress.total) * 100);})
        .then((result) => {
          if(result === 0) alert('SUCCESS'); // name of db file inside this zip file is 'appdata.db'
          if(result === -1) alert('FAILED');
        });
  }

//code to read sqlite file using query which uses 'SQLite' plugin
public openDB(): void{
        let dbObj: {};
        if(this.platform.is('ios')){
            dbObj = {name: "appdata.db", iosDatabaseLocation: 'default'};

        }else if(this.platform.is('android')){
            dbObj = {name: "appdata.db", location: 'default'};
        }

        this.db.openDatabase(dbObj).then(() => {
            this.refresh();
           // alert('db open');

        }, (error) => {
            alert("Unable to open database"+ JSON.stringify(error));
        });
    }

//run a query once DB is open
public refresh(): void{
        this.db.executeSql("select * from companies", []).then((data) => {
            let companies = [];
            //alert(JSON.stringify(data.rows));
            if(data.rows.length > 0) {
                for(var i = 0; i < data.rows.length; i++) {
                    companies.push({companyName: data.rows.item(i).companyName, companySales: data.rows.item(i).companySales});
                }
                alert(JSON.stringify(companies));

            }
        }, (err) => {
            //console.log("ERROR: " + JSON.stringify(error));
            alert('Unable to execute sql: '+ JSON.stringify(err));
        });
    }

Hope that helps.

Cheers! Deepak

[chrisworrell]

Deepak, it was under my impressions that at least on android you cannot open a database unless it is in the www folder. I have tried your solution but when I select one of my tables is empty. (i am using the sqlite-ext plugin)

Should I be able to open a database that is not within the www folder? Looks like you are saving in in library/localdatabase on android and when I try to open my file it opens but that is because I assume if you open and the database doesnt exist in www it creates a new one.

plzsendhelp :D

[megharajdeepak]

@chrisworrell I guess, I wasn't clear enough. I actually did not use sqlite-ext plugin, I have used sqlite-storage.

These are the plugins that I used which are mentioned in ionic 2 doc (https://ionicframework.com/docs/v2/native/sqlite/):

'File' = ionic plugin add cordova-plugin-file 'Transfer' = ionic plugin add cordova-plugin-file-transfer 'Zip' = ionic plugin add cordova-plugin-zip 'SQLite' = ionic plugin add cordova-sqlite-storage

I think your query isn't working as sqlite storage and sqlite-ext are different plugins. Try sqlite storage and see how it goes.

Cheers!

[cocowalla]

@megharajdeepak you are unzipping the SQLite database file to applicationStorageDirectory + '/library/localdatabase/', but your openDB() function seems to be opening a file from a different path - surely that just creates a new, empty database?

[megharajdeepak]

@cocowalla While opening the db, following code is used. If it's ios, 'iosDatabaseLocation' would be used as default, while for android, 'location' would be used as default. In both the cases, DB would point to the same location where it was downloaded and unzipped.

`

  if(this.platform.is('ios')){
        dbObj = {name: "appdata.db", iosDatabaseLocation: 'default'};

    }else if(this.platform.is('android')){
        dbObj = {name: "appdata.db", location: 'default'};
    }

`

[cocowalla]

@megharajdeepak ah, I see, so default is mapped to applicationStorageDirectory + '/library/localdatabase/' by the SQLite plugin? Is applicationStorageDirectory + '/library/localdatabase/' the correct path for both Android and iOS?

I'm working in an emulator right now - do you know if this should work in the Android emulator? When I try it, it always seems to create a new, empty database, rather than using the one that was downloaded.

[cocowalla]

@megharajdeepak I figured it out, the path does differ for Android, so you need something like:

this.platform.is('ios')
    ? "/library/localdatabase/"
    : "/databases/"

Thanks for the sample you gave, it was really helpful for downloading and using pre-populated SQLite databses 👍

[iursevla]

@cocowalla are you using cordova-sqlite-ext instead of cordova-sqlite-storage to open pre-populated databases?

[cocowalla]

@iursevla cordova-sqlite-storage

[iursevla]

@cocowalla but with cordova-sqlite-storage you can use pre-populated databases? I used cordova-sqlite-ext to open pre-populated databases but now i can't use it because when i install the types for sqlite it won't contain the method openDatabase(options) in the SQLite interface.

[cocowalla]

@iursevla

Yes, cordova-sqlite-storage is working with pre-populated databases.

The openDatabase method has been renamed to create, which despite the misleading name only actually creates a new database if the specified file doesn't already exist.

[iursevla]

@cocowalla Thank you. Even ionic 2 official docs still talk about openDatabase method.

[brodycj]

Yes, cordova-sqlite-storage is working with pre-populated databases.

Woah as the primary author and maintainer I find that statement confusing! The cordova-sqlite-storage version does NOT implicitly support pre-populated databases (using the createFromLocation option).

UPDATE: I will document how this can work as discussed in https://github.com/litehelpers/Cordova-sqlite-help/issues/26. Further response will be below.

I just raised https://github.com/litehelpers/Cordova-sqlite-help/issues/26 to document this further.

P.S. In terms of the following change:

The openDatabase method has been renamed to create, which despite the misleading name only actually creates a new database if the specified file doesn't already exist.

I would find this confusing as well.

At least the documentation at https://ionicframework.com/docs/native/sqlite/ is now consistent.

[cocowalla]

Woah as the primary author and maintainer I find that statement confusing! The cordova-sqlite-storage version does NOT implicitly support pre-populated databases (using the createFromLocation option).

@brodybits I find that statement confusing! :) I'm using this method successfully, and it sounds like @megharajdeepak is too.

Here's what I'm doing:

1. Decide where to download the database to (note this.file is a File object from cordova-plugin-file:

// Storage path differs on iOS and Android
let pathPart = this.platform.is("ios")
    ? "library/localdatabase/"
    : "databases/";

return this.file.applicationStorageDirectory + pathPart;

2. Download the database from a remote endpoint

3. Open the database (note this.db is an SQLite object from your (rather good) cordova-sqlite-storage plugin:

let dbConfig: SQLiteDatabaseConfig = this.platform.is("ios")
    ? { name: "my_database.db", iosDatabaseLocation: "default" }
    : { name: "my_database.db", location: "default" };

this.session = await this.db.create(dbConfig);

And it works great, exactly as expected.

A couple of points:

• The reason for downloading populated databases (rather than downloading the data and inserting it into a new database created by the client), is that the databases I'm working with are rather large. It's far more efficient for my use case to download a ready-made database
• Is there some reason you would expect this not to work?
• Bit of a tangent, but both openDatabase and create don't quite describe what is happening - how about openOrCreate? (although I imagine you'll be reluctant to change it again so soon)

[brodycj]

@brodybits I find that statement confusing! :) I'm using this method successfully, and it sounds like @megharajdeepak is too.

Agreed. (Further response to this and a couple other points was in https://github.com/litehelpers/Cordova-sqlite-help/issues/26#issuecomment-299983782 for anyone interested.) As discussed in https://github.com/litehelpers/Cordova-sqlite-help/issues/26 I will update the sqlite plugin documentation to reflect this capability.

• Bit of a tangent, but both openDatabase and create don't quite describe what is happening - how about openOrCreate? (although I imagine you'll be reluctant to change it again so soon)

Nice idea but I would not favor it, reasoning in https://github.com/litehelpers/Cordova-sqlite-storage/issues/212#issuecomment-300021460 for anyone interested.

[arunpapena]

@megharajdeepak I could see applicationStorageDirectory in iOS is readonly mode and am getting

you don't have access to save in this location error.

I am working on pre-populated database and for android its working perfect as the location is read-write.

[megharajdeepak]

@arunpapena As mentioned by @cocowalla above, have you tried this?

https://github.com/ionic-team/ionic-native/issues/881#issuecomment-290431854

[arunpapena]

this.platform.is('ios') ? "/library/localdatabase/" : "/databases/"

Yes, I tried this

[arunpapena]

I figured out the solution. Use cordova.file.applicationStorageDirectory for Android device and cordova.file.documentsDirectory for iOS.

And storage path should be

this.platform.is('ios') ? "/library/localdatabase/" : ""

Opening pre populated database:

if(this.platform == 'Android') {
    this.db = window.sqlitePlugin.openDatabase({name: 'myPrePolulatedDB', location: 'default'});
} else {
    this.db = window.sqlitePlugin.openDatabase({name: 'myPrePolulatedDB', iosDatabaseLocation: 'Documents'});
}

[danielsogl]

@arunpapena Feel free to open a PR for this workaround.