JavaScript MySQL Error while inserting bulk prepared statements

Question

I have a problem. I am trying to insert a few hundred object in my database using prepared bulk statements. The table I am trying to insert into is the following:

Then I have the following code:

static save() {
    data = [[1, '', '', 0, 0, 0, 0, 0], [2, '', '', 0, 0, 0, 0, 0], [3, '', '', 0, 0, 0, 0, 0], [4, '', '', 0, 0, 0, 0, 0]];
    let sql = "INSERT INTO Candlestick (openTime, market, `interval`, open, high, low, close, volume) VALUES (?, ?, ?, ?, ?, ?, ?, ?);";
    return db.execute(sql, [data]);
}

But this results in the following output:

Error: Incorrect arguments to mysqld_stmt_execute
    at PromisePool.execute (C:\Users\Alexander\Projects\API\node_modules\mysql2\promise.js:359:22)
    at Function.save (C:\Users\Alexander\Projects\API\src\api\v1\models\Candlestick.js:18:19)
    at C:\Users\Alexander\Projects\API\src\api\v1\controllers\candlestickController.js:28:29
    at processTicksAndRejections (node:internal/process/task_queues:96:5) {
  code: 'ER_WRONG_ARGUMENTS',
  errno: 1210,
  sql: 'INSERT INTO Candlestick (openTime, market, `interval`, open, high, low, close, volume) VALUES (?, ?, ?, ?, ?, ?, ?, ?);',

When I copy the query and manually set the variables to:

INSERT INTO Candlestick (openTime, market, `interval`, open, high, low, close, volume) VALUES (1, '', '', 0, 0, 0, 0, 0);

The query does get executed in phpmyadmin:

Can someone tell me what I am missing...

I am using:
mysql2 - ^2.3.3
mariadb - 10.3.34

Your variable `data` contains an array of arrays, but when you pass it to `db.execute` you wrap it in another array. Does it work if you remove this extra array, i.e. if you replace `db.execute(sql, [data])` with `db.execute(sql, data)`? — Luke Woodward, Mar 29 '22 at 20:54
No that results in the same error. I wrapped it in another array, because that's what they told here: https://stackoverflow.com/a/14259347/10673107 — A. Vreeswijk, Mar 29 '22 at 22:03
[How do I do a bulk insert in mySQL using node.js](https://stackoverflow.com/a/14259347/724039) says that you need to replace `VALUES (?, ?, ?, ?, ?, ?, ?, ?);";`, and use: `VALUES ?;";` — Luuk, Mar 30 '22 at 09:35
@Luuk That fixed it!!! Also @Nick your comment about `db.query` was helpful as well, because with `db.query`, I got to see the final query with all the parameters filled in it! — A. Vreeswijk, Mar 30 '22 at 11:11
@A.Vreeswijk you should post an answer yourself with everything that fixed the problem (and accept it when you can) as it might be useful to other users. — Nick, Mar 30 '22 at 11:27

score 0 · Accepted Answer · answered Apr 04 '22 at 12:21

To fix this issue, I had to change the query from:

let sql = "INSERT INTO Candlestick (openTime, market, `interval`, open, high, low, close, volume) VALUES (?, ?, ?, ?, ?, ?, ?, ?);";

to:

let sql = "INSERT INTO Candlestick (openTime, market, `interval`, open, high, low, close, volume) VALUES ?;";

Because every entry in the [data] represent: (1, '', '', 0, 0, 0, 0, 0) So that fill be filled in every time. Also I changed the db.execute to db.query, so the queries will be prepared at the client side. The advantage of that is that you can see the prepared query result in the console, when the query goes wrong.

JavaScript MySQL Error while inserting bulk prepared statements

1 Answers1