# Catch Serial output and store it to MSSQL

**URL:** <https://discourse.nodered.org/t/catch-serial-output-and-store-it-to-mssql/5042>\
**Category:** General\
**Created:** [19 November 2018 14:12 UTC](https://discourse.nodered.org/t/catch-serial-output-and-store-it-to-mssql/5042 "2018-11-19T14:12:28Z")\
**Posts on this page:** 14\
**Page:** 2

<div class="post-metadata">

**Author:** ![Conard](https://avatars.discourse-cdn.com/v4/letter/c/41988e/32.png) [@Conard](https://discourse.nodered.org/u/Conard)\
**Post date:** [19 November 2018 17:19 UTC](https://discourse.nodered.org/t/catch-serial-output-and-store-it-to-mssql/5042/22 "2018-11-19T17:19:17Z")

</div>

![ed](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/9/94fa20df18ac84ff057c1d45ba0cca3107887f69.jpeg)  
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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [19 November 2018 17:47 UTC](https://discourse.nodered.org/t/catch-serial-output-and-store-it-to-mssql/5042/23 "2018-11-19T17:47:17Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![shrickus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/shrickus/32/517_2.png) [@shrickus](https://discourse.nodered.org/u/shrickus)\
**Post date:** [19 November 2018 18:04 UTC](https://discourse.nodered.org/t/catch-serial-output-and-store-it-to-mssql/5042/24 "2018-11-19T18:04:19Z")

</div>

> [@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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/3/3b1c8e3cd9a1fa4e22eee0aa95e9d458dfdca852.png)

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

---

<div class="post-metadata">

**Author:** ![Conard](https://avatars.discourse-cdn.com/v4/letter/c/41988e/32.png) [@Conard](https://discourse.nodered.org/u/Conard)\
**Post date:** [20 November 2018 14:32 UTC](https://discourse.nodered.org/t/catch-serial-output-and-store-it-to-mssql/5042/26 "2018-11-20T14:32:18Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/3/3accdbc3361bae88ab044c0972568e9c6964b0ca.jpeg)

that's odd. I don't know why

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [20 November 2018 14:41 UTC](https://discourse.nodered.org/t/catch-serial-output-and-store-it-to-mssql/5042/27 "2018-11-20T14:41:54Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![EricMe](https://avatars.discourse-cdn.com/v4/letter/e/91b2a8/32.png) [@EricMe](https://discourse.nodered.org/u/EricMe)\
**Post date:** [20 November 2018 14:50 UTC](https://discourse.nodered.org/t/catch-serial-output-and-store-it-to-mssql/5042/28 "2018-11-20T14:50:07Z")

</div>

get rid of the seperators in your date: 20181118

---

<div class="post-metadata">

**Author:** ![Conard](https://avatars.discourse-cdn.com/v4/letter/c/41988e/32.png) [@Conard](https://discourse.nodered.org/u/Conard)\
**Post date:** [20 November 2018 15:30 UTC](https://discourse.nodered.org/t/catch-serial-output-and-store-it-to-mssql/5042/29 "2018-11-20T15:30:29Z")

</div>

![ed1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/c/cd952a1b614e5cc7490d5758cca26b44a304107c.jpeg)

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;
```

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [20 November 2018 15:35 UTC](https://discourse.nodered.org/t/catch-serial-output-and-store-it-to-mssql/5042/30 "2018-11-20T15:35:47Z")

</div>

> [@Conard](#):
>
> pld = pld + "VALUES ('" + pld.Date + "', '" + pld.InTime + "', '" + pld.SID + "')"

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?

---

<div class="post-metadata">

**Author:** ![Conard](https://avatars.discourse-cdn.com/v4/letter/c/41988e/32.png) [@Conard](https://discourse.nodered.org/u/Conard)\
**Post date:** [20 November 2018 15:41 UTC](https://discourse.nodered.org/t/catch-serial-output-and-store-it-to-mssql/5042/31 "2018-11-20T15:41:59Z")

</div>

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)"`

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [20 November 2018 15:53 UTC](https://discourse.nodered.org/t/catch-serial-output-and-store-it-to-mssql/5042/32 "2018-11-20T15:53:25Z")

</div>

> [@Colin](#):
>
> pld = pld + "VALUES ('" + pld.Date + "', '" + pld.InTime + "', '" + pld.SID + "')"

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.

---

<div class="post-metadata">

**Author:** ![shrickus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/shrickus/32/517_2.png) [@shrickus](https://discourse.nodered.org/u/shrickus)\
**Post date:** [20 November 2018 17:16 UTC](https://discourse.nodered.org/t/catch-serial-output-and-store-it-to-mssql/5042/33 "2018-11-20T17:16:02Z")

</div>

> [@Conard](#):
>
> "INSERT INTO [SAOS1].[dbo].[attendance1] (Atd\_Date, Atd\_InTime, SID) VALUES (1, 2018/11/20, 12:33:11)"

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._

---

<div class="post-metadata">

**Author:** ![Conard](https://avatars.discourse-cdn.com/v4/letter/c/41988e/32.png) [@Conard](https://discourse.nodered.org/u/Conard)\
**Post date:** [22 November 2018 14:55 UTC](https://discourse.nodered.org/t/catch-serial-output-and-store-it-to-mssql/5042/34 "2018-11-22T14:55:12Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/0/076f1a35fa58fb4903834c6ca42a1b36342acdf3.jpeg)

---

<div class="post-metadata">

**Author:** ![shrickus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/shrickus/32/517_2.png) [@shrickus](https://discourse.nodered.org/u/shrickus)\
**Post date:** [26 November 2018 15:59 UTC](https://discourse.nodered.org/t/catch-serial-output-and-store-it-to-mssql/5042/35 "2018-11-26T15:59:27Z")

</div>

> [@Conard](#):
>
> But I have a small problem with sending SMS, the Twilio Node properties only allows me to put one TO phone number.

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](https://www.twilio.com/docs/api/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.

---

<div class="post-metadata">

**Author:** ![ukmoose](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ukmoose/32/13_2.png) [@ukmoose](https://discourse.nodered.org/u/ukmoose)\
**Post date:** [27 November 2018 08:31 UTC](https://discourse.nodered.org/t/catch-serial-output-and-store-it-to-mssql/5042/37 "2018-11-27T08:31:20Z")

</div>

This thread appears to be morphing into a duplicate of [How to send SMS to different number using Twilio?](https://discourse.nodered.org/t/how-to-send-sms-to-different-number-using-twilio/5111?u=ukmoose)

[Previous page](https://discourse.nodered.org/t/catch-serial-output-and-store-it-to-mssql/5042.md?page=1)