Mysql: Batch insert failing

[bfanyuk]

This is my table:

DROP TABLE IF EXISTS x;
CREATE TABLE x (id SERIAL, name TEXT);

This is my code:

var settings = require('settings.json');
var mysql = require('mysql');
var pool = mysql.createPool(settings);
var params = [["name1"], ["name2"]];
pool.query('insert into x (name) values ?', params, function (error, results) {
    if (error)
        console.error(error);
    if (results)
        console.log(results);
});

I am doing this according to https://github.com/mysqljs/mysql/issues/814#issuecomment-43083100
The result is:

{ Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''name1'' at line 1
    at Query.Sequence._packetToError (C:\myproject\node_modules\mysql\lib\protocol\sequences\Sequence.js:52:14)
    at Query.ErrorPacket (C:\myproject\node_modules\mysql\lib\protocol\sequences\Query.js:77:18)
    at Protocol._parsePacket (C:\myproject\node_modules\mysql\lib\protocol\Protocol.js:280:23)
    at Parser.write (C:\myproject\node_modules\mysql\lib\protocol\Parser.js:75:12)
    at Protocol.write (C:\myproject\node_modules\mysql\lib\protocol\Protocol.js:39:16)
    at Socket.<anonymous> (C:\myproject\node_modules\mysql\lib\Connection.js:103:28)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:176:18)
    at Socket.Readable.push (_stream_readable.js:134:10)
    --------------------
    at Pool.query (C:\myproject\node_modules\mysql\lib\Pool.js:199:23)
    at Object.<anonymous> (C:\myproject\test\x.js:5:6)
    at Module._compile (module.js:570:32)
    at Object.Module._extensions..js (module.js:579:10)
    at Module.load (module.js:487:32)
    at tryModuleLoad (module.js:446:12)
    at Function.Module._load (module.js:438:3)
    at Module.runMain (module.js:604:10)
    at run (bootstrap_node.js:390:7)
    at startup (bootstrap_node.js:150:9)
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  index: 0 }

The version is "2.13.0"

[dougwilson]

Hi @bfanyuk this is because you have a slight error in your method call. Your variable params is correct for what goes in the ?, but then your mistake is when you call query. The second argument is an array of values, where each element corresponds to a question mark.

The following line:

pool.query('insert into x (name) values ?', params, function (error, results) {

Should be:

pool.query('insert into x (name) values ?', [params], function (error, results) {

To make it clear why that is, here is an example with everything expanded to variables:

var settings = require('settings.json');
var mysql = require('mysql');
var pool = mysql.createPool(settings);
var sql = 'insert into x (name) values ?';
var row1 = ["name1"];
var row2 = ["name2"];
var rows = [row1, row2];
var values = [rows];
pool.query(sql, values, function (error, results) {
    if (error)
        console.error(error);
    if (results)
        console.log(results);
});

I hope this helps!