Unable to extract Jason data into SQL

Hi, i am runing program on Node-Red. The data format in JASON. I am trying to pull out "payload": "Open" and insert into mysql. No errors but the data in sql state as [object OBJECT] . Did attached the database field.

test2 : msg : Object
object
topic: "test2"
payload: "Open"
qos: 0
retain: false
_topic: "test2"
_msgid: "2356ede6.954ba2"

Open in new window


var temp;

temp ={"payload":msg.payload};
msg.topic = "insert into ctesting (data1) values ('"+temp+"')";


return msg;

Open in new window

db.png
Rama TitoProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Yup. Cause temp is an object. Not a string containing JSON.

Node-RED is built on node.js, thus this

var obj = {"payload":msg.payload};
var json = JSON.stringify(obj);
var escapeSingleQuotes = json.Replace("'", "''");
msg.topic = "INSERT INTO ctesting (data1) VALUES ('" + json + "')";
return msg;

Open in new window

should work.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rama TitoProgrammerAuthor Commented:
"TypeError: json.Replace is not a function"

json.png

I did tryout with this code: -
var obj = msg.payload;

var json = JSON.stringify(obj);

msg.topic = "INSERT INTO ctesting (data1) VALUES ('" + json + "')";
return msg;

Open in new window


its works but data in database updated with " " (double quote). Need to remove that.
0
Rama TitoProgrammerAuthor Commented:
I do manage to get it with following code after google on w3school

var obj = msg.payload;
var json = JSON.parse(obj);

var Device = json.Device_ID;
var Line = json.Line_ID;
var sum = json.Count;
var dt = json.Date_Now;

msg.topic = "INSERT INTO ctesting (data1,data2,data3,data4) VALUES ('" + Device + "','" + Line + "','" + sum + "', '"+ dt+"')";
return msg;

Open in new window

0
Rama TitoProgrammerAuthor Commented:
Hi Experts,

Its help me to kick start from where i am. Thank you
0
ste5anSenior DeveloperCommented:
"replace" starts with an lower-case r, sorry for that typo.

But it's important, that you use it, to avoid SQL injection.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSON

From novice to tech pro — start learning today.