How to do bulk insert In mariadb using nodejs?

Question

Whenever i am trying to run my code this is showing the column count doesn't match error.

values=[
    [{id:12227722345,name:"dgssssdavgsgfv",pass:"cvhsssssadfvugod"}],
    [{id:12,name:"ddd",pass:"cvh"}]
   ];

c.query('insert into Hash.asn(userid,username,password) values (?,?,?)',[values],function(err,rows)
{
  if (err)
    console.log(err);

  c.query('commit');
  console.log(rows);

});

Error:

{ [Error: Column count doesn't match value count at row 1] code: 1136 }

I am not sure but just suggestion try with one value maybe the [] format is causing issue. — Neha, Jun 09 '16 at 08:02
One value is working but i want to do with multiple value So can you help me how to do it? — Akash Sourav Nayak, Jun 09 '16 at 08:04
just suggestion if its help. as i also not tried, why u keeping [] around the {} object ? any reason. if u remove it and try. — Neha, Jun 09 '16 at 08:07
Then how will you send multiple values Like multiple id,multiple username at a time . — Akash Sourav Nayak, Jun 09 '16 at 08:58
I think something like this - var values = [{1,'a','b'}, {2,'v','h'}]; insert into Hash.asn(userid,username,password) values (?,?,?)',[values] — Neha, Jun 09 '16 at 09:41
if u still struggling with this -once try this way -- insert into Hash.asn(userid,username,password) values (?,?,?)',[(1,'a','b'),(2,'v','h')] — Neha, Jun 10 '16 at 01:57
Its not takeing the 2nd value .I have solve this problem for static data but i am not able to insert dynamic data . — Akash Sourav Nayak, Jun 10 '16 at 06:46
i guess then ur dynamic data format need to be similar like ur static data. something is not matching data format. — Neha, Jun 10 '16 at 06:52
may be..let me try .if i face any difficulties then i will comment here — Akash Sourav Nayak, Jun 10 '16 at 07:12

score 8 · Answer 1 · answered Feb 12 '19 at 11:27

In case anyone is still wondering this you can use connection.batch() to perform bulk queries.

connection.beginTransaction();
connection.query("INSERT INTO BASKET(customerId) values (?)", [1], (err, res) => {
   //must handle error if any
   const basketId = res.insertId;
   try {
     connection.batch("INSERT INTO basket_item(basketId, itemId) VALUES (?, ?)",[
         [basketId, 100],
         [basketId, 101],
         [basketId, 103],
         [basketId, 104],
         [basketId, 105]
     ]);
     //must handle error if any
     connection.commit();
     } catch (err) {
     connection.rollback();
     //handle error
     }
});

https://github.com/MariaDB/mariadb-connector-nodejs/blob/master/documentation/batch.md

score -1 · Answer 2 · answered Nov 08 '18 at 03:09

-1

These days, As I know it, mariaDB module was not support bulk insert on the node.js.

https://mariadb.com/kb/en/library/connectornodejs-pipelining/

answered Nov 08 '18 at 03:09

Yunho Jang

11

Welcome to StackOverflow! While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – Tân Nov 08 '18 at 03:42

How to do bulk insert In mariadb using nodejs?

2 Answers2