Ionic 3 inserting a lot of data

Question

I have a problem with an ionic3 app I'm been developing. With my app I conect to a JSON server and download a lot of rows, when I have all rows with a for, in each row call to funcion to insert data. but I think, my for is faster than insert funcion and show finalice alert before end. this is my code:

 private downloadRows()
 {
this.platform.ready().then(()=>{
   this.translateService.get('ACTUALIZANDOBARRANCOS').subscribe(
    value => {
    let loadingContent = value; 
    let loader = this.loadingController.create({
    content: loadingContent,
    spinner: "bubbles"
    });  

    this.servicioDownloads.downloadAllRows().then(rows=> {
        this.datosrows= rows;
        loader.present();
        for (var i = 0; i < this.datosrows.length; i++)
        {
          var datos = this.datosrows[i];
          // we are going to insert rows
          this.servicioDataBase.insertRow
          ( datos.indice,
            datos.row1, 
            datos.row2,
            datos.row3, 
            datos.row4, 
            datos.row5,
            datos.row6, 
            datos.row7, 
            datos.row8, 
            datos.row9, 
            datos.row10, 
            //...
            datos.row30
          ).catch(()=>{
            console.log("da error");
          });
        }
        loader.dismiss();
        this.translateService.get('FINALIZADO').subscribe(
              Titulo =>{
                  let titulo = Titulo;

               this.translateService.get('BARRANCOSACTUALIZADOS').subscribe(
                    Descripcion =>{
                      let descripcion = Descripcion;
                      let alerta = this.alertCtrl.create({
                        title: titulo,
                        subTitle: descripcion,
                        buttons: ['OK']
                      })
                      alerta.present();
                    }
                  );
              }
            );


  }).catch(error=>{
    loader.dismiss();
        this.translateService.get('ERROR').subscribe(
              Titulo =>{
                  let titulo = Titulo;

  this.translateService.get('ERRORDESCARGABARRANCOS').subscribe(
                    Descripcion =>{
                      let descripcion = Descripcion;
                      let alerta = this.alertCtrl.create({
                        title: titulo,
                        subTitle: descripcion,
                        buttons: ['OK']
                      })
                      alerta.present();
                    }
                  );
              }
        );
  })
});
})

}

//This is insert services

public insertRow( indice: any, row1: any, row2: any,  row3: any,  row4: any, 
  row5: any,  row6: any,   row7: any,   row8: any,   row9: any,  row10: any, 
  row30: any)
 {    
  let sql = "INSERT INTO TableRows (id,Nombre,Rio,Pais,Comunidad, 
  Zona,Localidad,Interes,Caracter,Cascada_max,Cuerda_max,Desnivel, 
  Longitud, Tiempo_aprox,Tiempo_descenso,Tiempo_retorno,Observaciones, 
  Descripcion_barranco,Periodo_optimo,Caudal,Caudal_filtro,Aproximacion, 
  Retorno,Loc_entrada_barranco,Loc_salida_barranco,Loc_entrada_parking, 
  Loc_salida_parking,Autor,Actualizacion,Idioma,Visible) 
  VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
  console.log(sql); 
  return this.isReady()
  .then(()=>{
      return this.database.executeSql(sql, 
            [indice,BARRANCO, RIO, PAIS,COMUNIDAD,
              ZONA,LOCALIDAD,INTERES,CARACTER, CASCADA_MAX, 
              CUERDA_MAX,DESNIVEL,LONGITUD,TIEMPO_APROX,
              TIEMPO_DESCENSO,TIEMPO_RETORNO, OBSERVACIONES, 
              DESCRIPCION_BARRANCO,PERIODO_OPTIMO,CAUDAL,
              CAUDAL_FILTRO,APROXIMACION, RETORNO,
              LOC_ENTRADA_BARRANCO,LOC_SALIDA_BARRANCO, 
              LOC_ENTRADA_PARKING, LOC_SALIDA_PARKING, 
              AUTOR,actualización,idioma, visible]);
  }).then((data)=>{
    console.log("El insert devuelve esto " + JSON.stringify(data));
  })        

}

Somebody knows how can I do on time, I mean insert when for ends?

thanks a lot!

You can try using cordova-sqlite-porter. https://stackoverflow.com/a/35385963/5059916 — Tomislav Stankovic, Jul 22 '17 at 08:58
Thanks @TomislavStankovic, I going to see it and I will tell you — Toni Puche, Jul 22 '17 at 09:23
@TomislavStankovic it works perfectly, thanks a lot, I used sqlite-porter and works fine and faster! only one think more. when I imported all of data, I went to screen when show all rows, but I cant see this rows after close app and opened again. I use virtualscroll. — Toni Puche, Jul 26 '17 at 23:02
I added it as answer so people can easier see the solution. As for showing data on screen, can you see it at moment after they are inserted? Did data disappear after you close and open app or they have not been displayed at all? — Tomislav Stankovic, Jul 27 '17 at 14:59

score 0 · Answer 1 · answered Jul 25 '17 at 18:07

just use async/await in your function, it will allow you call async functions in sequence manner (since Typescript 2.1 you are able to downlevel async/await to ES3/ES5). So solution in your example could look like this:

private downloadRows()
{
this.platform.ready().then(()=>{
   this.translateService.get('ACTUALIZANDOBARRANCOS').subscribe(
    async value => {
        let loadingContent = value; 
        let loader = this.loadingController.create({
            content: loadingContent,
            spinner: "bubbles"
        });  

        let rows = await this.servicioDownloads.downloadAllRows();
        loader.present();
        for (let datos of rows) {
           // we are going to insert rows
           try {
             await this.servicioDataBase.insertRow
              ( datos.indice,
                datos.row1, 
                datos.row2,
                datos.row3, 
                datos.row4, 
                datos.row5,
                datos.row6, 
                datos.row7, 
                datos.row8, 
                datos.row9, 
                datos.row10, 
                //...
                datos.row30
              );
            }
            catch(error) {
                console.log(error);
            }
        }
        loader.dismiss();
        this.translateService.get('FINALIZADO').subscribe(
              Titulo =>{
                  let titulo = Titulo;

               this.translateService.get('BARRANCOSACTUALIZADOS').subscribe(
                    Descripcion =>{
                      let descripcion = Descripcion;
                      let alerta = this.alertCtrl.create({
                        title: titulo,
                        subTitle: descripcion,
                        buttons: ['OK']
                      })
                      alerta.present();
                    }
                  );
              }
            );
  }).catch(error=>{
    loader.dismiss();
        this.translateService.get('ERROR').subscribe(
              Titulo =>{
                  let titulo = Titulo;

  this.translateService.get('ERRORDESCARGABARRANCOS').subscribe(
                    Descripcion =>{
                      let descripcion = Descripcion;
                      let alerta = this.alertCtrl.create({
                        title: titulo,
                        subTitle: descripcion,
                        buttons: ['OK']
                      })
                      alerta.present();
                    }
                  );
              }
        );
  })
});
})

await in for cycle prevents for cycle to finish before insert is actually done.

score -1 · Answer 2 · answered Jul 27 '17 at 14:46

SQLite Porter - This Cordova/Phonegap plugin can be used to import/export to/from a SQLite database using either SQL or JSON.

Installation:

$ ionic cordova plugin add uk.co.workingedge.cordova.plugin.sqliteporter
$ npm install --save @ionic-native/sqlite-porter

Usage:

import { SQLitePorter } from '@ionic-native/sqlite-porter';


constructor(private sqlitePorter: SQLitePorter) { }

...

let db = window.openDatabase('Test', '1.0', 'TestDB', 1 * 1024);
// or we can use SQLite plugin
// we will assume that we injected SQLite into this component as sqlite
this.sqlite.create({
  name: 'data.db',
  location: 'default'
})
  .then((db: any) => {
    let dbInstance = db._objectInstance;
    // we can pass db._objectInstance as the database option in all SQLitePorter methods
  });


let sql = 'CREATE TABLE Artist ([Id] PRIMARY KEY, [Title]);' +
           'INSERT INTO Artist(Id,Title) VALUES ("1","Fred");';

this.sqlitePorter.importSqlToDb(db, sql)
  .then(() => console.log('Imported'))
  .catch(e => console.error(e));

You can try using cordova-sqlite-porter. Pass it your inserts as a JSON structure using importJsonToDb() and it will optimise the insertion into the SQLite DB.

The example project illustrates insertion of 15,000+ records. On a Samsung Galaxy S4, performing this using single SQL insert statements takes around 5 minutes/300 seconds, but the optimised JSON equivalent (using UNION SELECT - see here for info) takes around 3 seconds on the same device - 100X faster. - source

Ionic 3 inserting a lot of data

2 Answers2