Insert JSON object data to MySQL using Node.JS

Question

I want insert a JSON object into MySQL in Node.js server, this is the code

let id = 1
let date = new Date().toJSON().slice(0,10).replace(/-/g,'/');
let sql ='INSERT INTO case_record (case_details,gen_date,case_id) VALUES('+caseDetails+','+date+','+id+')'
console.log(sql)
con.query(sql,function(err, result, fields){
    if(err) throw err;
    res = result;
    console.log(res)
});

This is the caseDetails data

let caseDetails = {
    caseData,
    patData, 
    notifData,
    primecData, 
    refData}

Each of the object in the caseDetails is JSON object also. When I excute, the error return 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 '[object Object],2019/04/22,1)' at line 1

How to fix this problem?

What data type do those fields have? Normally, you should store them as string as mysql don't support an object. Use JSON.stringify() to save and JSON.parse() to load. — josephting
– josephting, Commented Apr 22, 2019 at 0:16
I changed the data type of the case_details field from JSON to long text. I also tried using JSON.stringify() but it still 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 '"caseData":"","patData":"","notifData":"","primecData":"","refData":""},2019-04-' at line 1 — Wenhao Hong
– Wenhao Hong, Commented Apr 22, 2019 at 0:24
You also need to wrap them in quotes. VALUES("'+JSON.stringify(caseDetails)+'", "'+date+'", "'+id+'") — josephting
– josephting, Commented Apr 22, 2019 at 0:27
This is the sql with quotes wrapedINSERT INTO case_record (case_details,gen_date,case_id) VALUES("{"caseData":"","patData":"","notifData":"","primecData":"","refData":""}", "2019-04-22", "1") But the error still there — Wenhao Hong
– Wenhao Hong, Commented Apr 22, 2019 at 0:38
Solved when I change the statement as@Ali D suggested. Thank you for the suggestion of the JSON.stringify — Wenhao Hong
– Wenhao Hong, Commented Apr 22, 2019 at 0:47

SuperStar518 · Accepted Answer · 2019-04-22 00:22:43Z

2

Your SQL syntax is wrong to cause parsing error.

Why don't u follow this correction?

...
let sql ='INSERT INTO case_record(case_details,gen_date,case_id) VALUES(?,?,?)';
con.query(sql, [caseDetails,date,id] ,function(err, result, fields) {
  ...
});

Hope to get helped.

answered Apr 22, 2019 at 0:22

SuperStar518

2,9252 gold badges22 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert JSON object data to MySQL using Node.JS

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related