Mysql: Bulk Insert Issue with 2D Arrays

Created on 23 Dec 2013 · 5Comments · Source: mysqljs/mysql

Summary

I read in the Readme that I should be able to perform bulk inserts using 2D arrays. When I execute my insert statement I receive a syntax error.

Thank you in advance for any insight you can provide.

Version Info

"name": "mysql",
"description": "A node.js driver for mysql. It is written in JavaScript, does not require compiling, and is 100% MIT licensed.",
"version": "2.0.0-alpha9",

Detailed Issue Report

Here is the node-mysql syntax found on my server (I've omitted some information to protect our project):

var postData = [memberIds.length];
for( var i = 0; i < memberIds.length; i++){
   postData[i] = [ caId, "" + memberIds[i] ];
}

console.log("postdata: " + JSON.stringify(postData));
var sql = connection.query( "INSERT INTO CA.CAMember (CAID, MemberID) VALUES ?", postData, function(err, result) {
   callback( err, err ? undefined : result.affectedRow );
});
console.log(sql.sql);

The console log generates the following:

postdata: [["28","4"],["28","3"]]
INSERT INTO CA.CAMember (CAID, MemberID)  VALUES '28', '4'
{ [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 ''28', '4'' at line 1]
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  index: 0 }

It appears that the ["28", "3"] array is ignored completely and that the parenthesis are left off.

For sake of clarity, I expected this to create the following syntax:

INSERT INTO CA.CAMember (CAID, MemberID)  VALUES ('28', '4'), ('28', '3')

Source

jeremyrambo

Most helpful comment

Your value itself needs to be array of arrays to be treated as bulk insert. Second parameter in query is values (note plural). You are passing two values, ["28","4"] and ["28","3"]. Try this code:

var postData = [memberIds.length];
for( var i = 0; i < memberIds.length; i++){
   postData[i] = [ caId, "" + memberIds[i] ];
}

console.log("postdata: " + JSON.stringify(postData));
var sql = connection.query( "INSERT INTO CA.CAMember (CAID, MemberID) VALUES ?", [postData], function(err, result) {
   callback( err, err ? undefined : result.affectedRow );
});
console.log(sql.sql);

sidorares on 29 Dec 2013

👍2 😕1

All 5 comments

Has anyone had time to look at this, am I simply doing something wrong?

jeremyrambo on 29 Dec 2013

Your value itself needs to be array of arrays to be treated as bulk insert. Second parameter in query is values (note plural). You are passing two values, ["28","4"] and ["28","3"]. Try this code:

var postData = [memberIds.length];
for( var i = 0; i < memberIds.length; i++){
   postData[i] = [ caId, "" + memberIds[i] ];
}

console.log("postdata: " + JSON.stringify(postData));
var sql = connection.query( "INSERT INTO CA.CAMember (CAID, MemberID) VALUES ?", [postData], function(err, result) {
   callback( err, err ? undefined : result.affectedRow );
});
console.log(sql.sql);

sidorares on 29 Dec 2013

👍2 😕1

You are correct and this does fix the problem...however, I thought I already had an array of arrays.

The console output of console.log("postdata: " + JSON.stringify(postData));

postdata: [["28","4"],["28","3"]]

jeremyrambo on 29 Dec 2013

Yes, but values is an array of your values. Since you want your value to be postData (which is an array or arrays) you need to pass postData as the first element of the values array. This is why you need to write [postData] and cannot just write postData bare like in your initial post.