My task is to fetch serial port output (from Arduino), then push the output to the Microsoft SQL Database. My Arduino project is recording student attendance using fingerprint. Every time a student has scanned his fingerprint, the Arduino will generate output like this: {'SID':1,'Date':2018/11/08,'…

Ah, JSON should be double quotes not single. Are you able to change that where it is generated of have you got to cope with it in node-red?

You will need quotes round the date and time too Serial.println("{\"SID\":"+ String(finger.fingerID) +",\"Date\":\""+ String(dateString) +"\",\"Time\":\""+ String(timeString) +"\"}"); Something like that anyway. In the debug node you want to see {"SID":1,"Date":"2018/11/08","Time":"21:25:50"} th…

[image] Conard: the Arduino will generate output like this: {'SID':1,'Date':2018/11/08,'Time':21:25:50} {'SID':2,'Date':2018/11/18,'Time':21:29:01} Those strings are not valid JSON, since the date and time values are not double quoted (and neither are the field names, I see). Instead the out…

Colin, it works now. I can see object with elements SID & etc. I try to do the rest now. patient with me. thank you!

Steve is right on the timestamp, you should save it as one date and time value in one of the standard time formats. In fact if the data is timestamped with the current time then even better to declare a timestamp column in the database and tell the db to put the current time in when it inserts it, …

[ed] this is my situation now. Table name is : attendance column: Atd_Date (date), Atd_InTime (time(7)), SID (int) pld = "INSERT INTO [SAOS1].[dbo].[attendance] " pld = pld + "(Atd_Date, Atd_InTime, SID) " pld = pld + "VALUES ('" + pld.Date + "', '" + pld.InTime + "', '" + pld.SID + "')" …

Whenever you are analysing a problem work through the flow with debug nodes checking all looks right. You are getting an error from the sql node so put the debug on the output of the function node and check that looks right.

[image] Conard: INSERT INTO [SAOS1].[dbo].[attendance] " pld = pld + "(Atd_Date, Atd_InTime, SID) " pld = pld + "VALUES ('" + pld.Date + "', '" + pld.InTime + "', '" + pld.SID + "') As an alternative, you might want to look at using a template node to build this INSERT string: [image] I jus…

shrickus, I am able to push data to my MSSQL database now. But the data stored inside MSSQL is not the same with the output. For example: output: {'SID':2,'Date':2018/11/18,'Time':21:29:01} data stored inside MSSQL : [ed1] that's odd. I don't know why

Node-RED

Node-RED Forum

Catch Serial output and store it to MSSQL

General

ukmoose 27 November 2018 08:31 37

This thread appears to be morphing into a duplicate of How to send SMS to different number using Twilio?

Topic		Replies	Views
Insert array contents into MSSQL table General	24	4036	27 July 2020
Sensor to node red to SQLite General	18	1337	15 November 2020
Read XML and write to a SQL Database General database	9	684	11 April 2022
Saving Data in DB at SQLite Function - Help! General	60	8411	7 December 2020
Store data arduino to SQL SERVER General	4	1095	12 July 2019

Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.

Catch Serial output and store it to MSSQL

Related topics