Read Existing SQLite Database File and Load Data

Ionic Native
21

Yes , i am testing it in real device , this is sample code
initializeApp() {
this.platform.ready().then(() => {
this.statusBar.styleDefault();
this.splashScreen.hide();
this.sqlite.create({
name: ‘AgroPod_db’,
location: ‘default’
}).then((db: SQLiteObject) => {
db.executeSql(‘SELECT * FROM mas_crop_generic’, {})
.then((res) => console.log(‘Executed SQL’, res))
.catch(e => console.log(e));
}).catch(e => console.log(e));
});
}

22 
openDB() {
  	this.sqlite.create({
	  name: 'dbname.db',
	  location: 'default'
	}).then((db: SQLiteObject) => {
        /*creation here something like for example:*/
		db.executeSql("CREATE TABLE IF NOT EXISTS table_name_here(id INTEGER PRIMARY KEY AUTOINCREMENT, columnname1 TEXT, columnname2 TEXT)", {}).then(() => {
	    /*select sql here*/
	    });
	}).catch(e => /*log error here*/ );
  }
23

Thanks for the suggestion However , I need to use prepopulated DB to fetch the data as soon as the app loads so i may not need to create the tables.

24

Any lead would be appreciated…I am stuck with it from a week. :expressionless:

25

Hello,
like documentation says this.sqlite.create open or create database. the executesql command is only for demonstation of usage in promise. Here you can use whatever you need.

best regards, anna-liebt.

1 Like
26

Thanks @anna_liebt , I was able to solve it using SQLite porter .

27

If you still interested please look at this solution:

28

Hi Nejthe, i’m having the same problem. I’m trying to read a prepopulated SQLite db but i can’t…

29

same problem here.
could you solve it?

I get " error: No Such table"

Please help.

Thanks.

30

can you explain please

31

Have you tried adding the file database file in www/ directory?

32 
            this.database.executeSql("Select prdt.ProductCode, prdt.FleetRate, pbe.Topten, ut.TopTen_bis from Product2 prdt inner join PriceBookEntry pbe on prdt.SFID = pbe.Product2 inner join PriceBook pbs on pbe.Pricebook2 = pbs.SFID inner join FamilyProduct fp on prdt.FamilyProductId = fp.SFID left join UserTopTenProduct ut on ut.Product2= pbe.Product2  and ut.Pricebook2=pbe.Pricebook2 LIMIT 200000", []).then((data2) => {
                CacheService.BindPriceBookDetails3 = [];
                if (data2.rows.length > 0) {
                    console.log("data.rows.length :" + data2.rows.length);
                    for (let i = 0; i < data2.rows.length; i++) {
                        CacheService.BindPriceBookDetails3.push({
                            ProductCode: data2.rows.item(i).ProductCode,
                            FleetRate: data2.rows.item(i).FleetRate,
                            TopTen: data2.rows.item(i).Topten,
                            TopTen_bis: data2.rows.item(i).TopTen_bis
                        });
                    }
                    console.log("CacheService.BindPriceBookDetails3 : " + CacheService.BindPriceBookDetails3.length);
                }
            }, (error) => {
                console.log("ERROR: " + JSON.stringify(error));
            });

The above query when you run , it will take 1 mins for retrieving records from sqlite…
Can anyone help me?

33

hey @derman10 for ios, set the second argument of your sqlite copy to “o”, it worked for me that way

34

limit = 200000 ??? what are you doing its take time. if you want records fast please use pagination.

35

When you run android, www directory is re-created. Then the db file is also deleted, isn’t it.