# SQL data return array data types? MSSQL Node

**URL:** <https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157>\
**Category:** General\
**Created:** [13 December 2022 22:54 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157 "2022-12-13T22:54:11Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![martinav](https://avatars.discourse-cdn.com/v4/letter/m/bc79bd/32.png) [@martinav](https://discourse.nodered.org/u/martinav)\
**Post date:** [13 December 2022 22:54 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/1 "2022-12-13T22:54:11Z")

</div>

I have successfully accessed a table from an MSSQL node, but the data my query returns is not coming importing into an array of proper datatype.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/9/09b5c504a0b93aa99d77f0a7fecd927efd83bfb9.png)

The VR1148 data fails because its not an integer? Well, how do I initialize the import variables to be of proper type?

---

<div class="post-metadata">

**Author:** ![tree-frog](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tree-frog/32/89303_2.png) [@tree-frog](https://discourse.nodered.org/u/tree-frog)\
**Post date:** [14 December 2022 01:18 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/2 "2022-12-14T01:18:20Z")

</div>

The result of your query string is forcing the value to be interpreted as an integer by SQL. You must surround your string with single quotes for SQL to properly see it as a string.

include single quotes arouond the content of the msg.payload for SQL to know it's a string.  
example:  
pld = pld + "WHERE '" + msg.payload + "' in (select value from string... blah blah blah"

resulting string will be like this .. WHERE 'VR1148' in(select value from string... blah blah blah

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [14 December 2022 09:04 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/3 "2022-12-14T09:04:40Z")

</div>

Also, when using SQL Server (or any SQL), you should avoid building string queries as it opens you up to [SQLi hacks](https://en.wikipedia.org/wiki/SQL_injection)

Here is how you would (should) do this using `node-red-contrib-MSSQL-PLUS`

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/d/ad4b457446a8c1cd3f015a570311924eec962fd6.png)

---

<div class="post-metadata">

**Author:** ![martinav](https://avatars.discourse-cdn.com/v4/letter/m/bc79bd/32.png) [@martinav](https://discourse.nodered.org/u/martinav)\
**Post date:** [14 December 2022 15:48 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/4 "2022-12-14T15:48:15Z")

</div>

@Steve-Mcl ,

Great idea. Well, I seem to have broken it. I get the dreaded "TypeError: sql.ConnectionPool is not a constructor" error. You posted to someone with this problem, and this is what I did. I used the pallate to uninstall MSSQL and installed MSSQL-Plus. Unfortunately, I dont really know where node-red is installed, nor how to manipulate it via ssh. Its like its not really on my server. I suppose it could be a docker or something. What I do know, is I cannot reboot the server because the server is supporting other things. I have to ping my admin to restart it and thats a pain.

> [@Catch MSSQL node error](https://discourse.nodered.org/t/catch-mssql-node-error/23675):
>
> Hi All, I'm kind of new to this, so I'm struggling with a MSSQL Node Catch. My node red server has an unreliable connection to my SQL server that I am querying, so it often throws a connection error. If I try again, it usually works. So I want a Catch node to trigger a retry after a 5 second delay, but it seems that the SQL node doesn't throw a general error that is easily caught when the connection is down. Any tips?

So, in that post, you describe how you uninstall and install nodes. However, not what to do after the fact.

Lastly, a bit off topic... I see a LOT of things about how node red is basically only to play around in a test environment. However, I was planning to use this for as an applet between a few PLCs and SQL in a production environment. My NodeRed is on a full featured HP server, not on a Rasberry Pi. Should I be concerned about using NodeRed in this manner? I would have no clue how else to tackle such a project.

Thanks

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [14 December 2022 16:47 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/5 "2022-12-14T16:47:33Z")

</div>

> [@martinav](#):
>
> I see a LOT of things about how node red is basically only to play around in a test environment. However, I was planning to use this for as an applet between a few PLCs and SQL in a production environment. My NodeRed is on a full featured HP server, not on a Rasberry Pi. Should I be concerned about using NodeRed in this manner? I would have no clue how else to tackle such a project

Definitely not. It works and it works well. Why prototype on node-red, test it works then rewrite it in a complied application that will prevent all the nice runtime monitoring and ease of fix in the event of a bug. In my previous job I used node red to gather thousands of data points from lots of different PLCs, atlas copco tooling, hundreds of Fanuc robots & more, present the data in MQTT and store them in a database. It would have been crazy to replicate this in a custom application or a proprietary application.

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [14 December 2022 17:03 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/6 "2022-12-14T17:03:46Z")

</div>

> [@martinav](#):
>
> I see a LOT of things about how node red is basically only to play around in a test environment

🤯

Thats absurd.

We use Node RED in a corporate setting, for various tasks.

- Automating our work force decisions.
- Tracking 600+ vehicles (tracking private/personal mileage via GSM/GPS odometer units) - affecting monthly invoices for engineers
- Integrating with our client systems
- And running my entire home 🤓

I do believe its used by Samsung also for their Smart Eco System, where users can sign up to an automation platform

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [14 December 2022 17:04 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/7 "2022-12-14T17:04:50Z")

</div>

Also the Raspberry Pi is not a toy computer, it's a range of small, low power computers, some specifically designed for industrial uses.

Your application _might_ push the I/O, memory, or CPU constraints (or not), in which case a beefier server might be appropriate. A few PLCs and SQL isn't going to need a supercomputer.

Of course you might get fired for recommending free software on a single board computer. 🙃

---

<div class="post-metadata">

**Author:** ![martinav](https://avatars.discourse-cdn.com/v4/letter/m/bc79bd/32.png) [@martinav](https://discourse.nodered.org/u/martinav)\
**Post date:** [14 December 2022 17:50 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/8 "2022-12-14T17:50:48Z")

</div>

@marcus-j-davies , @jbudd , @Steve-Mcl ,

I might have been a bit unclear there. The notion was not my idea that node-red was suitable for production or not. I have seen a lot of forum posts that have said that it isnt. I havent personally seen anything specifically unreliable with it at all. I have installed it on a VM on a HP server, and its working great. I was wanting some real reason why it would NOT be proper for a production setup. I intend to use it as such, because I have no capacity to develop any other API.

---

<div class="post-metadata">

**Author:** ![martinav](https://avatars.discourse-cdn.com/v4/letter/m/bc79bd/32.png) [@martinav](https://discourse.nodered.org/u/martinav)\
**Post date:** [14 December 2022 17:53 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/9 "2022-12-14T17:53:29Z")

</div>

@Steve-Mcl ,

My fault for hijacking my own post, but what can you give me for advice on what do to now that I have this ConnectionPool error? Its too late to do what you do when installing/uninstalling. Its broke, and I need it fixed.

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [14 December 2022 17:59 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/10 "2022-12-14T17:59:38Z")

</div>

> [@martinav](#):
>
> I was wanting some real reason why it would NOT be proper for a production setup

I guess the only thing I can come up with, is if you require parallel processing.  
Node RED is based on Node JS, which is based on Javascript.

javascript is single threaded, so if your project requires multithreading, then you might need to get creative to get around these barriers.

that's all I have 😅

---

<div class="post-metadata">

**Author:** ![martinav](https://avatars.discourse-cdn.com/v4/letter/m/bc79bd/32.png) [@martinav](https://discourse.nodered.org/u/martinav)\
**Post date:** [14 December 2022 18:19 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/11 "2022-12-14T18:19:24Z")

</div>

@marcus-j-davies

Hmm, this is interesting. I was wondering how I would use this for multiple PLC's of identical setup, and identical function. I was thinking I would use multiple MQTT inputs, becuase there would be the case where more than one PLC would trigger node-red at the same time. I was thinking that each PLC would use a different port number to distinguish between them. I will cross that bridge later. But, I think I can figure that out. Ill post another thread on that one.

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [14 December 2022 18:39 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/12 "2022-12-14T18:39:35Z")

</div>

See here for the event loop model  
I'll stop now - as I am way off topic 😅

> **[Making flows asynchronous by default](https://nodered.org/blog/2019/08/16/going-async)**
>
> Changes are coming in Node-RED 1.0 to how messages are routed in a flow. Find out what it means to go asynchronous.

---

<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:** [14 December 2022 21:03 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/13 "2022-12-14T21:03:49Z")

</div>

> [@martinav](#):
>
> I was wondering how I would use this for multiple PLC's of identical setup, and identical function. I was thinking I would use multiple MQTT inputs, becuase there would be the case where more than one PLC would trigger node-red at the same time. I was thinking that each PLC would use a different port number to distinguish between them

That is not the way to do it. You would have one MQTT broker and the PLCs would publish to different top level topics with the same structure underneath. You don't need to worry about multiple PLCs publishing at the same time, the network layer and the broker will sort that out.

Also, although node-red is single threaded, provided you have sufficient processor power that is usually irrelevant and the messaging architecture gives the impression of everything happening at once. The only time it is an issue is if you have a task that needs a large amount of processor in one go, image processing for example, in which case you might need to farm that out to a separate process.

---

<div class="post-metadata">

**Author:** ![tree-frog](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tree-frog/32/89303_2.png) [@tree-frog](https://discourse.nodered.org/u/tree-frog)\
**Post date:** [17 December 2022 18:46 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/14 "2022-12-17T18:46:10Z")

</div>

> [@martinav](#):
>
> Hmm, this is interesting. I was wondering how I would use this for multiple PLC's of identical setup, and identical function. I was thinking I would use multiple MQTT inputs, becuase there would be the case where more than one PLC would trigger node-red at the same time. I was thinking that each PLC would use a different port number to distinguish between them. I will cross that bridge later. But, I think I can figure that out. Ill post another thread on that one.

MQTT would use a different TOPIC. The broker would be on the same port. Then you'd need a PLC that can speak MQTT... If you used TCP, then you'd need each PLC to be on a different port. MQTT would be the best way to ensure you catch all asynchronous values. Node-RED can also be used with TCP because each port connection would be monitored separately. I would suggest you "MODEL" or test your methods and then you can see if there would be any bandwidth failures.

The reason you see the many instances of "TESTING" is because the programming format makes it incredibly easy to test and figure things out to create efficient means of solving problems. That is the one thing that I like about Node-RED.

Question: Since the first question was about MSSQL, are you collecting data from PLC's to send to a SQL Server? Can you give a more direct description of your project? Thanks.

---

<div class="post-metadata">

**Author:** ![rafadh92](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rafadh92/32/74950_2.png) [@rafadh92](https://discourse.nodered.org/u/rafadh92)\
**Post date:** [24 January 2023 16:28 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/15 "2023-01-24T16:28:30Z")

</div>

> [@Steve-Mcl](#):
>
> present the data in MQTT and stor

Hi Steve do you have an example of flow for extract data for fanuc robot?

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [24 January 2023 16:36 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/16 "2023-01-24T16:36:12Z")

</div>

Sorry, no. As stated it was in my previous job.

Not difficult though, it used a list (array) of objects --\> split node that ran the following flow for each robot: HTTP Request node to grab specific pages from the robots web server, split out the IO, Registers and alarms, publish them to MQTT and/or store interesting items in database.

---

<div class="post-metadata">

**Author:** ![rafadh92](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rafadh92/32/74950_2.png) [@rafadh92](https://discourse.nodered.org/u/rafadh92)\
**Post date:** [25 January 2023 15:14 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/17 "2023-01-25T15:14:21Z")

</div>

Hi Steve, thanks for your answer, excuse me do you have a manual or how can know the meaning of the variables from the fanuc robot?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [26 March 2023 15:14 UTC](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/18 "2023-03-26T15:14:57Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.