Sqlite insert multiple records inside of a loop

Ionic Framework ionic-v3
1

Hello,

I have a question on how to insert multiple records to sqlite using for loop.
What I would like to do is to insert many record ~10,000 to sqlite. After all records are inserted successfully, to
Set date in localstorage and read the data from the db back.
My problem is with the async nature of the executeSq() API. what happens, is that my read API is called before the insert completes.
This is basically what I am doing:

insertProduct(products) {
    let i: number = 0;
    for (let product of products) {
      this.database.executeSql('INSERT INTO ' + this.TABLE_NAME + ' (product) VALUES(?);', JSON.stringify(product)).then(res => {
        i++;
        if (i == products.length) {
          this.setLastUpdatedDate();
        }
      }, err => {
        this.presentToast('sqdb_insertProduct fail: ' + err);
      });
    }
  }

How do I call this API so when all data is inserted reading back?
This is my read API:

  getAllProducts() {
    return this.database.executeSql('SELECT * FROM ' + this.TABLE_NAME + ';', []).then(data => {
      let products = [];
      for (var i = 0; i < data.rows.length; i++) {
        products.push(data.rows.item(i).product);
      }
      return products;
    }, err => {
      this.presentToast('sqdb_getAllProducts fail: ' + err);
      return [];
    });
  }

My second question, is how to read back all data from the db and once it returns to continue doing other stuff?
Thanks,

2

A couple questions/ideas. If you’re just dealing with json anyway, why not just use the ionic storage plugin? Easier to use and works in the browser.

If you want to keep using sql, why not do a single insert of multiple values?

3

Hi,

  1. At the present I am only using json. In future it will have more columns.
  2. What do you mean “single insert of multiple values”? Note that I may have up to ~10,000 records.

Thanks

4

I mean that you can write a SQL statement that inserts multiple values, so I believe there’s nothing stopping you from simply building a string with a bunch of inserts in it. So you could build out the whole string first then do a single insert. This should also give you better performance since you’d do it all at once.

The other possibility is to build an array of queries and use sqlBatch https://ionicframework.com/docs/native/sqlite/#sqlBatch to execute them all at once. This again should also give you better performance, in addition to simplifying the async stuff to a single call.

5

Maybe to be more specific, how do I call executeSql() in a loop?

The problem is that the number of records I insert every time is different. So using your approach will not do much. I still will need to have a loop if the number of records is larger than the number of INSERT statements. Besides that, what if the I have less number of inserts than the number of statements?

What people usually do if they need to insert more than one record and the number of records is different every time. I think that the async approach\implementation has more pain than benefit.

6

Take a look at async and await you can do something like this.

async getAllProducts(){...}; 

const products = await getAllProducts();
// Here do something when your getAllProducts has finished

And I think you shoud disable and enable auto commit. This shoud make your getAllProducts much faster.

7

The get works great!.
I have two questions: how do you implement this for the insert?
In my example I have this.database.executeSql inside a for loop.

My sqlite module is in provider called databaseProvider.
in database provider I have this insert api:

insertProduct(products: string[]) {
    let i: number = 0;
    for (let product of products) {
      return this.database.executeSql('INSERT INTO ' + this.TABLE_NAME + ' (product) VALUES(?);', [JSON.stringify(product)]).then(res => {
        i++;
        if (i == products.length) {
          this.setLastUpdatedDate();
          return res;
        }
      }, err => {
        this.presentToast('sqdb_insertProduct fail: ' + err);
        return err;
      });
    }
  }

note that the executeSql is inside for.
in my app I call insertProduct()

const products = await this.databaseProvider.insertProduct(this.downloadedProductsFromServer);

This doesn’t work as in the getAllProducts() sample.
I’d appreciate you help.
Also, what do you mean disable auto commit? Disable auto commit before the select and enable it after it?

Thanks

8

To enable and disable the auto commit is only important for the insert method. Set it to false before your loop.
Make the commit after your loop manually and set it to true.

Change this


const products = await this.databaseProvider.insertProduct(this.downloadedProductsFromServer);

to

const download= await this.downloadedProductsFromServer();
const products = await this.dataProvider.insertProduct(download);

Don’t forget to put this in a try catch block for error handling.

9

The insertProduct implementation is ok?

Thanks.

10

Hard to say.

You coud add some console.log to check if everey record is processed correctly.

11

I don’t think you’re following. Consider this example:

const products = [
  {
    id: 1,
    name: 'spoon'
  },
  {
    id: 2,
    name: 'fork'
  }
];

async insertProducts(products) {
  const productQueries: string[] = products.map(product => `INSERT INTO ${this.TABLE_NAME} values(${product.id}, ${product.name}`);

  try {
    await this.database.sqlBatch(productQueries);
    this.setLastUpdatedDate();
  } catch (error) {
    this.presentToast(`product insert failed: ${error}`);
  }
}

I made a fake array of products since I don’t know what your data looks like. I also changed over to async/await instead of just regular promises, but you could do either one. So first, I create an array of strings, each string is it’s own query. There is nothing async about this and it builds the query for each item.

Then I simply await the sqlBatch call, which will run all those queries. Notice at this point there is no looping, it just takes an array of strings and runs all of them. Since I changed to async/await instead of normal promises, I use a normal try/catch block. You might also need async on setLastUpdatedDate, but I don’t know what that code does.

12

Hello rlouie,
I am following your sample but I am getting the following error:
“product insert failed: Error: a statement with no error handler failed: sqlite3_prepare_v2 failure: no such column: id”.

I am creating table like this:

createTable() {
    console.log('init');
    this.sqlite.create({
      name: 'data.db',
      location: 'default'
    })
      .then((db: SQLiteObject) => {
        this.database = db;
        db.executeSql('create table IF NOT EXISTS products(id INTEGER, name TEXT)', {})
          .then(() => console.log('Executed SQL'))
          .catch(e => console.log('error creating table' + e));
      })
      .catch(e => console.log('error creating db' + e));
  }

inserting this way:

 insert() {
    const products = [
      {
        id: 1,
        name: 'spoon'
      },
      {
        id: 2,
        name: 'fork'
      }
    ];

    this.insertProducts(products);
  }

  async insertProducts(products) {
    const productQueries: string[] = products.map(product => 'INSERT INTO products values(id, name)');

    try {
      await this.database.sqlBatch(productQueries);
      console.log('this.setLastUpdatedDate()');
    } catch (error) {
      console.log(`product insert failed: ${error}`);
    }
  }

second option doesn’t also work:

const productQueries: string[] = products.map(product => `INSERT INTO products values(?, ?)`, [product.id, product.name]);

Your sample gives error for spoon column

const productQueries: string[] = products.map(product => `INSERT INTO ${this.TABLE_NAME} values(${product.id}, ${product.name}`);

But when I am using conventional way I don’t get the error:

this.database.executeSql('INSERT INTO products VALUES(?,?)', [products[0].id, products[0].name])

It seems like the map doesn’t work.
Am I missing anything here?

Thanks,

13

I will say that my code was for example purposes only, I didn’t actually run it. I was really just trying to give an example of building a string array then using sqlBatch. The idea is there, but you have to customize it to fit. That said, I actually don’t see any issues with it, it should run. However, your table is most likely built differently and you changed my code to specifically not build actual insert queries for some reason.

First thing is your error, which I’m guessing makes perfect sense: ‘sqlite3_prepare_v2 failure: no such column: id”.’. You probably don’t have a column named id. While I can’t actually see your original db creation code or database, I’m going to guess your previous table was also named products. I can see from your old insert code that your previous table only had one column, product. It did not have a name or id column. Id comes first so that’s the first error you get. So you need to get rid of your old table and make one with an id and name. Or, you know, just customize my solution to insert the single product column.

Also, this makes zero sense.
const productQueries: string[] = products.map(product => 'INSERT INTO products values(id, name)');

On the other hand this builds queries:

const productQueries: string[] = products.map(product => `INSERT INTO ${this.TABLE_NAME} values(${product.id}, ${product.name}`);
14

Hi,

For your sample, I created new table based on the snippet code I wrote on scratch new emulator so there wasn’t any old table.
I tested it using this example:

this.database.executeSql('INSERT INTO products VALUES(?,?)', [products[0].id, products[0].name])

This is working and I didn’t get any error. But when I replaced the insert with your sample it gave me the error.

15

There was a small error in my sql stuff, as I said I never ran it, just wanted to give an example, but I’m missing the quotes around the insert string. This would work instead I think:

const productQueries: string[] = products.map(product => `INSERT INTO ${this.TABLE_NAME} values(${product.id}, '${product.name}'`);