How to insert multiple rows with a parameter?

问题:

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

回答1:

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.

回答2:

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

回答3:

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.

回答4:

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

回答5:

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

            });
}

回答6:

@Googie

Thanks for the solution. But I had problems with inserting the data and it came out that this line:

$cordovaSQLite.execute(db, query,[data])...

was the problem. And the solution was to write the data without brackets like this:

$cordovaSQLite.execute(db, query, data)...

because data is already an array.

Thought this maybe could be helpful for others.

cheers