Where to put SQLite databases on Ionic 2 RC0?


#1

Hi,

I am using a pre-populated database and on beta11 was working flawlessly, i would put it on /www folder and it worked with cordova-sqlite-ext plugin.

Now on RC0 i don’t know where i should put the database since /www folder is overriden everytime we run (ionic run android). I already tried to put it on /src folder or /src/assets folder but with no luck.

So does anyone know where to put a sqlite database?

Thanks,
Rui


#2

I’m interested to know too… thanks for any advice!


#3

Hi,

I created an issue and got some replies: https://github.com/driftyco/ionic/issues/8583
See if it helps, i don’t have time to test it this week.
If it works let me know how you did it.


#4

I will give a try, thank you for sharing. I think tomorrow I will be able to test it.


#5

Ok :wink:

Waiting to hear from you about the results.


#6

Ok, I think I’ve got some results, I don’t know if everything is ok, but at least I can can connect to a prepopulated database and select some data from a table.

  1. I installed the cordova-sqlite-ext plugin

  2. In app.component.ts I imported “import { SQLite } from 'ionic-native';

  3. In the platform.ready() I inserted:

    let db = new SQLite();
    db.openDatabase({
    name: “data.db”,
    location: “default”,
    createFromLocation: 1
    }).then(() => {
    db.executeSql(“SELECT * from config”, []).then((data) => {
    console.log("Data received: ", data);
    }, (error) => {
    console.error(“Unable to execute sql”, error);
    })
    }, (error) => {
    console.error(“Unable to open database”, error);
    });

  4. I create a file called copy.config.js at the same path of package.json and I inserted:

    module.exports = {
    include: [
    {
    src: ‘src/assets/’,
    dest: ‘www/assets/’
    },
    {
    src: ‘src/index.html’,
    dest: ‘www/index.html’
    },
    {
    src: ‘src/data.db’,
    dest: ‘www/data.db’
    },
    {
    src: ‘src/service-worker.js’,
    dest: ‘www/service-worker.js’
    },
    {
    src: ‘node_modules/ionic-angular/polyfills/polyfills.js’,
    dest: ‘www/build/polyfills.js’
    },
    {
    src: ‘node_modules/ionicons/dist/fonts/’,
    dest: ‘www/assets/fonts/’
    },
    ]
    };

  5. In the file package.json I inserted:

    “config”: {
    “ionic_copy”: “./copy.config.js”
    },

before the last line "description": "SqlProject: An Ionic project".

That’s all for the moment. From the console I can see that it can open the database and receive the data from the config table inside the data.db database that I placed in the src folder.

Of course, all suggestions, improvements and test results are appreciated! :slight_smile:


#7

Will try when i have time.

Thanks for taking your time to create this valuable reply.


#8

Hi, now you don’t need to do all of this anymore. See: https://github.com/driftyco/ionic-app-scripts/issues/127

Just update ionic scripts and it won’t remove anything inside www folder except build.

  1. Run npm install @ionic/app-scripts@latest --save-dev within your project
    And then only step 1 to 3 are needed.

Anyone reading this, the presented solution works as of 20 october 2016.


#9

Thank you for the updated information!


#10

I believe I’ve followed the thread and all of the directions correctly - but I’m still having issues accessing a pre-populated SQLite database. Please let me know if I’ve made a mistake or missed any steps. I have a running Ionic2 application.

Using the SQLite CLI, I opened a database and issued SQL commands to create and populate a table.
I copied the resulting file (OLA.db) to the /www directory off the root of my Ionic2 application.

I added a button the main page of the application and on the click event, I call a function callDatabase(). I included the alert function to avoid ambiguity.

callDatabase()
{
  
  let success = false;

  let db = new SQLite();
  db.openDatabase({      
    name: 'OLA.db',
    location: 'default',
    createFromLocation: 1
  }).then( () => { this.showAlert( "DB Opened"); db.executeSql("select * from Sample;", {} ).then( (data) => { this.showAlert( JSON.stringify(data) ); }, (error) => { this.showAlert( error.message ); } ); 
    }, (error) => { this.showAlert( "DB Failed to Open"); } );

  }

  showAlert( message: string )
  {
  let alert = this.alertCtrl.create(
      {
        title: message,
        buttons: ['OK']
      } );

    alert.present();              
  }

The database loads successfully, but it when I execute the SQL statement, it can’t find the table. I’ve double checked my SQL and it works correctly from the SQLite3 CLI.

When I replaced the SQL select statement with a table creation statement and then an SQL select statement, it works correctly, suggesting that the code is creating a database, populating and accessing it correctly. This rules out issues with the imported classes and libraries. I realize that I by-passed the platform ready check for the purpose of simplicity (by using a button and waiting) but have ruled that out since the create table and select statements work while in the same code structure.

Can anyone shed some light over what I’m missing?


#11

Hi,
It looks like everything is ok.

Are you sure you installed sqlite-ext-plugin?
Launch cmd and run ionic plugin list

It must contain this entry(0.10.1 can be something greater than this):

cordova-sqlite-ext 0.10.1 "Cordova sqlite storage plugin with extras (xx dev version branch)"

If you don’t have it, you can install it with the following command:

ionic plugin add cordova-sqlite-ext --save

Anyway if this does not work just for the sake of rulling out issues try to wrap your code on platform ready check.

Hope it helps


#12

Thanks for the suggestions.

I moved my function into a platform.ready.then method. The result was the same.

I checked the plugins. I noticed I didn’t have the “cordova-plugin-ext 0.10.x” - I had a “cordova-sqlite-storage”, so I attempted to install the missing plug-in and an error occurred - some of the files already existed. To be safe, I uninstalled both of the plug-ins and reinstalled the “cordova-plugin-ext”

Now my configuration looks like this:

ionic plugin list

cordova-plugin-console 1.0.4 "Console"
cordova-plugin-device 1.1.3 "Device"
cordova-plugin-splashscreen 4.0.0 "Splashscreen"
cordova-plugin-statusbar 2.2.0 "StatusBar"
cordova-plugin-whitelist 1.3.0 "Whitelist"
cordova-sqlite-ext 0.10.2 "Cordova sqlite storage plugin with extras"
ionic-plugin-keyboard 2.2.1 “Keyboard”

After the database opens successfully, the specific error message I’m getting is:

sqlite3_prepare_v2 failure: no such table: Sample

Any more suggestions would be appreciated.

++ I modified the SQL statement to “SELECT name FROM sqlite_master;” which resulted in:

{“rows”:{“length”:0},“rowsAffected”:0}

This confirms my theory that the plug-in is working, but opening a new database.


#13

The problem appears to be with my database file.

I copied a database from another example into my /www directory and was able to return the number of tables in that database successfully.

Can anyone point me to documentation on how to create an SQLite database for mobile use? Is it different than creating a regular SQLite database.

I’ve tried issuing the .open command in the SQLite CLI and also entering sqlite3 from the command line and copying the resulting file over. In both cases, I noticed that the CLI didn’t append the .db onto the file name.

Again - any thoughts or insight are appreciated.


#14

You had the wrong version of the plugin so it would never work with pre-populated databases.

I read your last comment, i don’t know what CLI you are using(sqlite 3 cli?)?

Go to sqlitebrowser and download the visual interface to create, open, query any sqlite3 database.


Prepopulated SQLite Databases in Ionic 2
#15

I was using the sqlite3 CLI.

Once I created the database using the sqlitebrowser, the resulting file opened correctly.

Thanks for all your help.


#16

No problem :wink:
Now if you see people with the same problem you can help them too :slight_smile:


#17

Hi i am getting this error in console build dev failed: Each glob entry must be a string please any help


#18

Hi iam getting error my table name is abcd
Unable to execute sql Objectcode: 5message: "sqlite3_prepare_v2 failure: no such table: abcd"proto: Object(anonymous function) @ main.js:93486


#19

hi i changed copy.config.js because

module.exports = {
copyAssets: {
src: [’{{SRC}}/assets/**/*’],
dest: ‘{{WWW}}/assets’
},
copyIndexContent: {
src: [’{{SRC}}/index.html’, ‘{{SRC}}/manifest.json’, ‘{{SRC}}/service-worker.js’],
dest: ‘{{WWW}}’
},
copyDb
{
src: [’{{SRC}}’],
dest: ‘www/data.db’
},
copyPolyfills: {
src: [’{{ROOT}}/node_modules/ionic-angular/polyfills/polyfills.js’],
dest: ‘{{BUILD}}’
},

copyFonts: {
src: [’{{ROOT}}/node_modules/ionicons/dist/fonts//*’, '{{ROOT}}/node_modules/ionic-angular/fonts//*’],
dest: ‘{{WWW}}/assets/fonts’
}
}

error in console build dev failed: Each glob entry must be a string

after that i can able to open db cant able to fetch data from table
getin errr unable to execute sql
message: “sqlite3_prepare_v2 failure: no such table: empdetails”


#20

Hi

Are you following this thread? You don’t need to do anything on copy.config.js on the newer version of ionic.

So first update your ionic if it is not updated already then update app-scripts and then

  1. Install the cordova-sqlite-ext plugin

  2. In app.component.ts I import “import { SQLite } from ‘ionic-native’;”

  3. In the platform.ready() I inserted:

let db = new SQLite();
db.openDatabase({
name: “data.db”,
location: “default”,
createFromLocation: 1
}).then(() => {
db.executeSql(“SELECT * from config”, []).then((data) => {
console.log("Data received: ", data);
}, (error) => {
console.error(“Unable to execute sql”, error);
})
}, (error) => {
console.error(“Unable to open database”, error);
});