Catch Serial output and store it to MSSQL

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,'Time':21:25:50}
{'SID':2,'Date':2018/11/18,'Time':21:29:01}
...
...

I would like to store these output to Microsoft SQL database (MSSQL), which means I want to insert new row to table called attendance, based on the serial output. How can I do that?? Writing a JavaScript using function node? If yes, how to do that?

I see there are multiple mssql nodes, which one are you using? It will probably node-red-contrib-something.

I have attached the image above, node-red-contrib-mssql

The image does not tell us which contrib-node you used, another one may look just the same on screen.

What problem are you having?
Have you managed to get it to connect to the server?
What query have you specified in the mssql node?
What are you passing in msg.payload?

  1. I can view the serial output if I connect serial node and msg.payload directly, however, I want to fetch the serial output SID, Date & Time to store inside MSSQL.
  2. Yes, I do. I can show data from database by using inject node with SELECT statement, but when I come with this flow design, cannot.
  3. So far, no. If I am able to fetch the serial output, i will be going to use INSERT statement to store the output to MSSQL
  4. So far, nothing. The function node nothing inside also.

If I write a javascript code inside function node, can I fetch the serial output and push to MSSQL? If yes, how to do it?

OK so the first problem is getting the data out of the serial input. I suggest first feeding the data through a Json node, that should convert it from a string to a javascript object. Try that first and feed the output into a debug node. If it works correctly you should see an object with elements SID, Date etc. If so then in the function node you can build the query string using something like
msg.payload = "Insert bla bla " + msg.payload.SID + " bla " + msg.payload.Date + " bla;";
I am assuming that you know what the query should look like.
Then feed the output of the function node into another debug node to check that the query looks right, when all working connect it the the sql node.

Ok Colin, I try it first.
Please be patient with me, cause I'm pretty new to node-red, and thank for your reply.
I will ask your help again if I have a problem. Thank you

Is this successful?

What came out the end??

That's odd, it is a string. Do the same with the debug node on the COM4 output. Don't forget to deploy before running it, I am always forgetting that, then you see the debug from where the debug node was before.

ok I do it again.
For these 3 nodes, what should I change for the properties or setting?

When comes to the json node, do I need to change property to msg.schema?

Leave the json node at its default setting. We need to understand what is coming out of the Com node. I assumed that was a json string so feeding it into a Json node should convert it to a javascript object, but apparently it didn't, which is why I want to see what comes out of the com node (in the debug window so we can see the type of data as well as the value.

ok I leave these 3 nodes as default setting. just drag & drop.
this is what I get

In addition, the output is actually coming from my Arduino Project. I connect my Arduino Uno to my laptop using USB cable. So if i scan my fingerprint, the attendance is taken. And it will display output like this. It 's a string

I asked to see the output coming from the com4 node. We need to understand exactly what you are getting.


Like this?

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"}
then feeding that into the json node should convert it ok.

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 output string should be {"SID": 1, "Date": "2018/11/08", "Time": "21:25:50"}

However, sending separate date and time strings is never a good idea -- it's better to use a UTC timestamp so future queries can find the records within a time range. Then, you can always format it for display within the UI.

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, so you don't need to generate a time yourself.