Read Existing SQLite Database File and Load Data

Ionic Native
1

Hi,

I am new to Ionic and I have been working on SQLite but I cannot make this work for a week now, and its quite frustrating :confused: .
I have tried some plugins such as litehelpers/Cordova-sqlite-storage, litehelpers/cordova-sqlite-ext, and some others but it doesnt seem to work for me. Some plugins return “Cannot find sqlitePlugin” error.
I also tried openDatabase() { this.sqlite.create({name: ‘data.db’, location: ‘default’}).then((db: SQLiteObject) but it does not read my existing database. BTW, I am using a real Android device to test.

What I want to achieve is, just load the data from the database that I previously created using DB Browser for SQLite. I have tried putting the sqlite file into my www but its still not working. I got errors like cannot open database. The database that I am talking to about already has tables and data stored so I will just be reading them and showing data to the user. Is it possible to achieve this?

I have tried some solutions on some posts like Load pre populated SQLite database - impossible in Ionic 2 and How to make SQLite in ionic-native use cordova-sqlite-ext?

Its difficult to imagine creating hundreds or thousands of INSERT statements that is why I just want to load the database with existing tables and data.

Thanks! Any help will be gladly appreciated. :slight_smile: .

Anyway, below is my ionic info:

global packages:

@ionic/cli-utils : 1.4.0
Cordova CLI      : 7.0.1
Ionic CLI        : 3.4.0

local packages:

@ionic/app-scripts              : 1.3.7
@ionic/cli-plugin-cordova       : 1.4.0
@ionic/cli-plugin-ionic-angular : 1.3.1
Cordova Platforms               : android 6.2.3 browser 4.1.0 ios 4.4.0
Ionic Framework                 : ionic-angular 3.3.0

System:

Node       : v7.6.0
OS         : Windows 10
Xcode      : not installed
ios-deploy : not installed
ios-sim    : not installed
npm        : 4.1.2
1 Like
2

https://www.thepolyglotdeveloper.com/2015/01/deploy-ionic-framework-app-pre-filled-sqlite-db/ doesnt seem to help me because I think that Ionic framework used here is an old version.

3

Hi. I hope someone notice my inquiry. By the way, the app that I am creating is an offline language tutorial.

4

Hello, do you still need help?

5

I don’t need help on this anymore. I just became helpless on this. What I did is put all the data in a json file and encode them into something. Thanks!

1 Like
6

Do you think it’s better than having a database?

7

Of course it is not. Its just that I really do not know how to solve my problem and I became helpless. I even searched about it on search engines for one week without success. But anyway, thanks for your willingness to help. :slight_smile:

8

Well i had the same problem, i actually spent weeks trying to get it to work and finally succeeded. if you or anyone else are still interested on how I was able to do it let me know!

2 Likes
9

Please help me on this. I am struggling on it for a week !

10

First of all, you need to import 3 plugins:

Then import the SQLite in your ts file:

import { Component } from '@angular/core';
import { SQLite } from "ionic-native";
import { Platform, NavController, PopoverController } from 'ionic-angular';


export class HomePage {
    public storage: SQLite;
    public itemList: Array<Object>;

 constructor(public popoverCtrl: PopoverController, public navCtrl: NavController, public platform: Platform) {

   this.itemList = [];

        this.platform.ready().then(() => {
            this.storage = new SQLite();
            this.storage.openDatabase({ name: "data.db", location: 'default', createFromLocation: 1 }).then((success) => {
                this.storage.executeSql("SELECT * FROM art", {}).then((data) => {
                    let rows = data.rows;
                    for (let i = 0; i < rows.length; i++) {
                        this.itemList.push({
                            id: rows.item(i).id
                        });
                    }
                }, (error) => {
                    console.info("Unable to execute sql " + JSON.stringify(error));
                })
         }, (err) => {
                console.info("Error opening database: " + err);
            });
        });
    }

Don’t forget to add the file data.db in www/ directory.

I hope I didn’t miss anything. Let me know if it works.

1 Like
11

Do I have to call the Copy() method to copy the Database ? Or just including the cordova-plugin-dbcopy Plugin is enough ?

12

Just include the Plugin. That’s how it worked with me!

13

Thank You. Will try it.

14

It does not work. Throws error: No Such table. Whereas I have the correct table in the Database I created using Sqlite browser.

15

In what folder did you add the database?

16

Do you have any idea why should I include those 3 if you used just one?

17

I found one of the way to play with existing DB on Deploy Ionic 3 App with pre-populated SQLite DB from MySQL DB

18

Though I previously said that I put my data into json, I was able to solve it in another project. I just followed https://ionicframework.com/docs/native/sqlite/ and put my db in www folder. That’s it.

19

Hi…I tried to do the same i put my db file in www folder and assets folder as well but it is still showing “no such table exists” error.Please let me know if i missed something.

20

Are you running it using a real device?