Catch Serial output and store it to MSSQL

General
22

ed
ed.JPG1101×249 37.7 KB

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 + "')"

msg.topic = ''
msg.payload = pld

return msg;

code in function node

23

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.

24

As an alternative, you might want to look at using a template node to build this INSERT string:

image

I just find it easier to see the SQL syntax better, without all the JS string appending and quoting.

26

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

27

Show us the output of a debug node connected to the output of the function node. As I have said before, if a node is not doing what you expect then first look at what you are feeding into it.

28

get rid of the seperators in your date: 20181118

29

ed1
ed1.JPG1149×205 27.1 KB

code in function node

pld =       "INSERT INTO [SAOS1].[dbo].[attendance1] "
pld = pld + "(Atd_Date, Atd_InTime, SID) "
pld = pld + "VALUES ('" + pld.Date + "', '" + pld.InTime + "', '" + pld.SID + "')"

msg.payload = pld

return msg;
30

Do the undefined values look right in the debug output.

Ask yourself what the code above is supposed to be doing. Shouldn't it be getting the data out of msg.payload or something?

31

So how can I make the undefined values look right in the debug output??
I just refer online code, but ending up getting undefined values

I tried EricMe suggestion, cannot work also if i remove the seperators.

I know it should look like:

"INSERT INTO [SAOS1].[dbo].[attendance1] (Atd_Date, Atd_InTime, SID) VALUES (1, 2018/11/20, 12:33:11)"

32

Do you understand what that line of code means? If not then you need to try and understand the basics of javascript. We can't just keep feeding you the code unless you make an effort to understand.

33

Your SQL syntax is not correct -- your values are strings and need to be inside whichever quotes are expected by the target database. I've not used MSSQL much, but I believe you need single-quotes, so the insert statement should be:

INSERT INTO [SAOS1].[dbo].[attendance1] (SID, Atd_Date, Atd_InTime) VALUES (1, '2018/11/20', '12:33:11')

which is why I put them into the template example above in my last post.

Edit: I just noticed that your list of columns do not have the same order as your list of values -- that will cause the values to be placed into the wrong columns. I've adjusted the columns in the SQL statement above to be correct.

34

Hi, shrickus. I am able to insert attendance record to MSSQL now without any problem. Thank you for your help!
I would like to send a SMS notification to student's parent, and I am able to get parent phone number from my MSSQL database.

But I have a small problem with sending SMS, the Twilio Node properties only allows me to put one TO phone number. Meaning that all SMS will be received by only one phone number (parent), even not his/her children

1
1.JPG1148×455 54.5 KB

35

According to the node's Info, you can pass in the phone number on the msg.topic field:

Sends an SMS message or makes a call using the Twilio service.

msg.payload can either contain the text of the SMS message, or the URL of the TWiML used to create the call. The node can be configured with the number to send the message to. Alternatively, if the number is left blank, it can be set using msg.topic . If the node is configured to make a call then the TWiML URL must be publically accessible.

But make sure you leave the "To" field blank, or else it will ignore the number passed in through the topic.

37

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