1

I am doing an application with Node js, Express and MySQL. I am running on localhost with XAMPP, not sure if this information is important. I want to insert multiple rows in my database, so I did:

    db.query('INSERT INTO tasks (done, user_id, task) VALUES ?', values, (err, result) => {
            if(err) {
                console.log(err);
            } 
            console.log("done");

        });

where values is an array of arrays containing each a triplet (done, user_id, task), example [[0, 10, 'foo'], [1, 10, 'bar']]. However, when this snippet runs with more than one array in values it gives me an error like

Error: ER_PARSE_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 '0, 10, 'test'' at line 1

What am I doing wrong? Is there another way to insert multiple rows?

** EDIT: ** My problem is multiple row insertion, I know that the generated query has the wrong syntax, but according to this accepted answer my Javascript syntax sould be correct

Essay97
  • 648
  • 1
  • 9
  • 24

2 Answers2

1

As it is stated in the mentioned answer, your values should be wrapped in another brackets [values]

Note: values is an array of arrays wrapped in an array

Zavael
  • 2,383
  • 1
  • 32
  • 44
  • The problem is with multiple insertion, my javascript syntax was taken by [this accepted answer](https://stackoverflow.com/questions/8899802/how-do-i-do-a-bulk-insert-in-mysql-using-node-js), maybe I have to edit and clarify the question – Essay97 Dec 09 '20 at 10:29
  • I see, in that example they have array of array too, but they wrap it in another one by `[values]` as the query parameter so it becomes "an array of arrays wrapped in an array" as stated in that answer. Did you tried it that way? – Zavael Dec 09 '20 at 11:06
  • 1
    Oh, I didn't notice it, now it works! Edit the answer with this information, I'll mark as accepted – Essay97 Dec 09 '20 at 14:04
0

I don't know variable "values" look like, but you need check syntax.

right syntax is : INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Đinh Quốc Việt
  • 39
  • 9
  • `values` is for example `[[0, 10, 'foo'], [1, 10, 'bar']]`. I can't replace values one by one because I don't know how many do I have to insert every time. According to [this accepted answer](https://stackoverflow.com/questions/8899802/how-do-i-do-a-bulk-insert-in-mysql-using-node-js) my Javascript syntax is correct for multiple row insertion, but it actually messes up SQL syntax – Essay97 Dec 09 '20 at 10:27
  • you need convert [[0, 10, 'foo'], [1, 10, 'bar']] to (0, 10, 'foo'), (1, 10, 'bar') – Đinh Quốc Việt Dec 10 '20 at 09:35