How to bulk insert in mySql and node.js using mysljs

Question

Im not able to use bulk insert in my DB using node.js lib mysljs.

I followed answers from:

How do I do a bulk insert in mySQL using node.js

with no success.

var sql = "INSERT INTO resources (resource_container_id, name, title, extension, mime_type, size) VALUES ?";

var values = [
  [1, 'pic1', 'title1', '.png', 'image/png', 500], 
  [1, 'pic2', 'title2', '.png', 'image/png', 700]];

return connection.query(sql, [values], (result) => {
    if (err) throw err;
    connection.end();
});

I keep getting error:

 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'?\' at line 1'

I also tried to promisify the query mehod using bluebird but with no success, I get same error again.

typo in the callback there should be (err, result)=>{...} but that is likely not the error you describe here, since the err is actualy printed at some point so i assume that it's just a copy error. — Tomáš Růžička
– Tomáš Růžička, Commented Jun 3, 2021 at 22:34

Unicco · Accepted Answer · 2018-03-10 15:50:56Z

1

You need to mark your keys with the grave accent (backtick) character, like this: `key`

Making your query like this:

var sql = "INSERT INTO resources (`resource_container_id`, `name`, `title`, `extension`, `mime_type`, `size`) VALUES ?";

answered Mar 10, 2018 at 15:50

Unicco

2,5861 gold badge30 silver badges32 bronze badges

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

1 Comment

dmks Over a year ago

Doesn't work for me, still get the error.

Namit Piriya · Accepted Answer · 2020-03-01 17:10:38Z

1

try using () around the question mark

var sql = "INSERT INTO resources (resource_container_id, name, title, extension, mime_type, size) VALUES (?) ";

answered Mar 1, 2020 at 17:10

Namit Piriya

1417 bronze badges

1 Comment

Tomáš Růžička Over a year ago

I have the same issue with mysql2 library and also doesn't work.

Tomáš Růžička · Accepted Answer · 2021-06-03 22:32:42Z

1

I have the same issue only when i use connection.execute() and then it's actually because it is not implemented for prepared statements.

I resolved the issue by using connection.query() instead.

Don't know if that answer helps anyone tho.

It would help me when I was searching for the answer.

answered Jun 3, 2021 at 22:32

Tomáš Růžička

5397 silver badges17 bronze badges

Comments

Silvio · Accepted Answer · 2017-08-31 16:19:01Z

-2

Try removing the square brackets around values

answered Aug 31, 2017 at 16:19

Silvio

13 bronze badges

1 Comment

Hunter Over a year ago

square brackets around values imply array

Collectives™ on Stack Overflow

How to bulk insert in mySql and node.js using mysljs

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related