0
202
ViewsJavaScript MySQL Error while inserting bulk prepared statements
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
1 answers
Answer question0
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.