corry corry - 6 months ago 23
JSON Question

Inserting multiple rows from JSON structure to MySQL

I'm stuck with inserting JSON data to MySQL db using NodeJS.
I got this error:


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 ''[{\"id\":\"d3f3d\",\"code\":\"' at line 1


I'm getting JSON data from
url.json
using the
request
Node module and I'm trying to store the data in a MySQL db.

//mysql connection setup
var connection = mysql.createConnection({
host : "localhost",
port: "3306",
user : "root",
password : "root",
database : "db",
multipleStatements: true
});

request('url.json', function (error, response, body) {
if (!error && response.statusCode == 200) {
//console.log(body)
}

var sql = "INSERT INTO table (id, code, country_name, city) VALUES ?";

var data = JSON.parse(body);
var responseJson = JSON.stringify(data.response.docs);

var query = connection.query(sql, [responseJson], function(err, result) {
if(err) throw err;
console.log('data inserted');
});
console.log(query.sql);

});


The data is logged as ''
'[{\"id\":\"d3f3d\",\"code\":\"'... }]'
. I think this may be the source of the error.

JSON structure looks like this:

{
"header":
{
"file1":0,
"file2":1,
"subfiles":{
"subfile1":"true",
"subfile2":"true",
}
},

"response":
{
"number":678,
"start":0,
"docs":[
{
"id":"d3f3d",
"code":"l876s",
"country_name":"United States",
"city":"LA"
},
{
"id":"d2f2d",
"code":"2343g",
"country_name":"UK",
"city":"London"
}
]
}
}


How do I resolve this?

Thanks.

Answer

The error stems from the query object's attempt to parse the values supplied. The function expects the values array to contain multiple arrays but received a string instead. The responseJson need not be converted into a JSON string.

You'll need to convert the array of objects into an array of arrays with only the values. For example, the query expects an array of this form:

"docs":[
  [
    "d3f3d",
    "l876s",
    "United States",
    "LA"
  ],
  [
    "d2f2d",
    "2343g",
    "UK",
    "London"
  ]
]

This is a method to convert an array of objects into an array of values:

function ObjToArray(obj) {
  var arr = obj instanceof Array;

  return (arr ? obj : Object.keys(obj)).map(function(i) {
    var val = arr ? i : obj[i];
    if(typeof val === 'object')
      return ObjToArray(val);
    else
      return val;
  });
}

This is the code refactored:

var data = JSON.parse(body);
// Convert the array of objects into an array of arrays.
var responseJson = ObjToArray(data.response.docs);

// The query object expects an array of objects so you pass in 'responseJson' as is
var query = connection.query(sql, responseJson, function(err, result) {
    if(err) throw err;
    console.log('data inserted');
});

This issue explains the problem with bulk inserts for node-mysql.