Javascript – How to insert multiple rows with a parameter

angularjsionic-frameworkjavascriptnode.jssqlite

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);
    });

Best 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.