How to insert multiple rows with a parameter?

Question

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

You add one ? even though you say there will be three columns inserted. This will not work. — Sami Kuhmonen
– Sami Kuhmonen, Commented May 12, 2015 at 3:27

Googie · Accepted Answer · 2015-05-14 08:13:16Z

16

+100

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.

answered May 14, 2015 at 8:13

Googie

6,1472 gold badges22 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Hitu Bansal Over a year ago

getting this error number of '?'s in statement string does not match argument count

Hitu Bansal Over a year ago

my loop is executing 40 times

Googie Over a year ago

console.log(data.length); and console.log(query); - see if everything looks okay

Hitu Bansal Over a year ago

length is 184, and query is INSERT OR IGNORE INTO categories (category_id, user_id, category_name,category_type) VALUES

Googie Over a year ago

You didn't have to paste it here. Just count number of '?' in the query and see if it's equal to 184. Tip: use console.log((query.match(new RegExp("\\?", "g")) || []).length);

|

Dave Alperovich · Accepted Answer · 2015-05-15 08:30:45Z

5

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

edited May 15, 2015 at 8:30

answered May 15, 2015 at 7:39

Dave Alperovich

32.6k8 gold badges82 silver badges101 bronze badges

6 Comments

Hitu Bansal Over a year ago

i guess this approach doesnot seems good. if i have 10000 loops and it will insert each row 10000 times in db. i want to insert all records in once. +1 for second approach

Dave Alperovich Over a year ago

@HituBansal. what happened in second approach?

Dave Alperovich Over a year ago

@HituBansal, when you say insert 1000 times, do you mean it does a round trip from client to server 1000 times, or that it makes 1 round trip, and then writes the row 1000 distinct times on that one round trip?

Hitu Bansal Over a year ago

it will round 1 trip and i will get 1000 rows. I am talking on Client side. According to your code . $cordovaSQLite.executeSql, tx.executeSql will execute 1000 times. which is inserting the rows into database. i am very close to Googie answer.

Dave Alperovich Over a year ago

@HituBansal, if it makes 1 roundtrip and inserts 1000 times, that's exactly how it's supposed to work. The round trips are what cost a lot. You still have to make 1000 inserts for 1000 rows. That will never change.

|

CL. · Accepted Answer · 2015-05-15 07:19:02Z

1

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.

edited May 15, 2015 at 7:19

answered May 12, 2015 at 11:37

CL.

182k18 gold badges241 silver badges282 bronze badges

8 Comments

Hitu Bansal Over a year ago

Executing multiple time is not optimize way. Can't we do in single insert?

CL. Over a year ago

AFAIK none of those JavaScript frameworks support prepared statements.

Hitu Bansal Over a year ago

@DaveAlperovich: i am also disagree with CL for the same

CL. Over a year ago

@DaveAlperovich I was not talking about transactions (and as far as transactions are concerned, I agree with you), but about whether execute() can handle arbitrary numbers of rows. JavaScript does not have Python's equivalent of executemany().

CL. Over a year ago

Well, he can almost achieve what he asked for with Googie's answer.But this appears to be an XY problem.

|

gilcierweb · Accepted Answer · 2016-07-23 15:32:46Z

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

            });
}

Trevor V · Accepted Answer · 2019-01-19 21:18:00Z

0

@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

edited Jan 19, 2019 at 21:18

Trevor V

2,1611 gold badge16 silver badges34 bronze badges

answered Jan 19, 2019 at 20:00

quinlan

1221 silver badge7 bronze badges

Comments

Kevin · Accepted Answer · 2015-05-15 15:38:45Z

-1

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

answered May 15, 2015 at 15:38

Kevin

5544 silver badges17 bronze badges

1 Comment

astef Over a year ago

Doing directly what you need to do can't be a bad practice, even if it's raw machine instructions in the code. What may become a bad practice is using high-level astraction layers like ORM, and then hacking it in order to get lower-level access.

Collectives™ on Stack Overflow

How to insert multiple rows with a parameter?

6 Answers 6

6 Comments

6 Comments

8 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

6 Comments

6 Comments

8 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related