How to insert multiple rows with a parameter?

Problem

I am trying to insert multiple rows in SQLite using ionic framework. Single row insert in working fine.

even if i run

 INSERT INTO categories (category_id, category_name,category_type) VALUES (1,"test",1),(2,"test again", 2); 

this is also working fine. but when i try to create a dynamic string it gives me error "could not prepare statement (1 near "?": syntax error)".

.success((function (result) {
                 var query = "INSERT INTO categories (category_id, category_name,category_type) VALUES ?";
                 var data = [];
                 result.forEach(function (category) {
                         data.push([category.id, category.category_name, category.category_type]);
                     });
    $cordovaSQLite.execute(db, query,[data]).then(function (res) {
         console.log("inserted");
     }, function (err) {
   console.dir(err);
    });
Problem courtesy of: Hitu Bansal

Solution

Add multiple parameters to your insert, just like you do in your test query (the first you mentioned), then pass all arguments as a one dimensional array:

.success((function (result) {
                 var query = "INSERT INTO categories (category_id, category_name,category_type) VALUES ";
                 var data = [];
                 var rowArgs = [];
                 result.forEach(function (category) {
                         rowArgs.push("(?, ?, ?)");
                         data.push(category.id);
                         data.push(category.category_name);
                         data.push(category.category_type);
                     });
                 query += rowArgs.join(", ");
    $cordovaSQLite.execute(db, query,[data]).then(function (res) {
         console.log("inserted");
     }, function (err) {
   console.dir(err);
    });

This code will produce query like:

INSERT INTO categories (category_id, category_name,category_type) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?);

and your data array will be of size 12, where every 3 entries in array will be one row of data to be inserted.

Those numbers are just example and they depend on the size of result.

Solution courtesy of: Googie

Discussion

This seems to be an approach:

var query = "INSERT INTO categories (category_id,category_name,category_type) VALUES (?,?,?)";

...

.success((function (result) {

$cordovaSQLite.transaction(function(tx){

   result.forEach(function (category) {
      tx.executeSql(query, [category.id, category.category_name, category.category_type]);
   });
}).success(function(){

  .....
});

I have also seen this approach:

$cordovaSQLite.execute("BEGIN IMMEDIATE TRANSACTION");
result.forEach(function (category) {
    $cordovaSQLite.executeSql(query, [category.id, category.category_name, category.category_type]);
       });
$cordovaSQLite.execute("COMMIT TRANSACTION");
Discussion courtesy of: Dave Alperovich

Ionic 2, angular 2 and typescript

import {Platform, NavController, NavParams, Storage, SqlStorage} from 'ionic-angular'`;

//SqlStorage uses SQLite or WebSQL (development only!) to store data in a
// persistent SQL store on the filesystem.
//automatically

public  getDB() {

        // if (this.platform.is('cordova')) {
        // } else {
        this.db = new Storage(SqlStorage, {name: this.db_name});
        this.db = this.db._strategy._db;
        // }
        return this.db;
    }



query(sql:any, params:any = []) {
return new Promise((resolve, reject) => {

                try {

                    console.log('query try');

                    this.getDB().transaction((transaction) => {

                        transaction.executeSql(sql, params, (transaction, success) => {
                            // resolve(this.fetchAll(success));
                            resolve(success);

                        }, (transaction, error) => {

                            reject(error);

                        });

                    });


                } catch (error) {
                    console.log("Unable to open database,", +error);
                    reject(error);
                }

            });
}
Discussion courtesy of: gilcierweb

It is not possible to use arrays as parameter values.

You have to create an SQL command string with three parameters for the three columns, and execute it multiple times.

To ensure efficiency, you have to do all INSERTs in a single transaction.

Discussion courtesy of: CL.

In any case it's a bad practice to write a raw sql queries in the code. Is it possible to use some Node ORM with ionic-framework? For example node-orm2: https://github.com/dresende/node-orm2

In this case your solution will looks like this:

var data = [];
result.forEach(function (category) {
    data.push({'id' : category.id, 'name' : category.category_name, 'type' : category.category_type});
});

Category.create(data, function (err, items) {
    // err - description of the error or null
    // items - array of inserted items
});
Discussion courtesy of: Dyrk

This recipe can be found in it's original form on Stack Over Flow.