Displaying MSSQL data in a HTML table

Liam Broughton

unread,

Mar 22, 2018, 5:40:22 AM3/22/18

to Node-RED

Hello all,

I am currently trying to display my MSSQL data in a table displaying in the dashboard of Node-RED.

I have attached 3 screen shots showing my current code of the HTML table, the function node and my HTML table in the ui.

I am getting an error stating there is no such column as "Status", I have tried changing the way I write the function node (by swapping around the entries, such as putting timestamp infront of status etc) and it always shows an error for the last entry in the function node.

I think I know where I am going wrong. I haven't entered my MSSQL database anywhere in the function node, but I have linked it up to the sqldbs node which has my details in?

I also know that I need to say which table I am taking the data from, the table is dbo.NodeRedData, but when I tried the code:

"msg.topic = "INSERT INTO tr (id, Topic, JobType, Information, Timestamp, Status) VALUES FROM dbo.NodeRedData (id, Topic, JobType, Information, Timestamp, Status)";

return msg;"

It returned an error message saying "Incorrect syntax near "dbo."

If anyone has any idea to how I go about doing this correctly it would be a big help. I have tried looking around for some tutorials etc but they are all mainly for mysql, which I think uses different functions to retrieve the data from the database.

Thanks,

-Liam.

FUNCTION NODE CODE.png

HTML TABLE VIEW.png

HTML TABLE CODE.png

Garry Hayne

unread,

Mar 22, 2018, 6:08:45 AM3/22/18

to Node-RED

On Thursday, March 22, 2018 at 9:40:22 AM UTC, Liam Broughton wrote:

Hello all,

I am currently trying to display my MSSQL data in a table displaying in the dashboard of Node-RED.

I have attached 3 screen shots showing my current code of the HTML table, the function node and my HTML table in the ui.

I am getting an error stating there is no such column as "Status", I have tried changing the way I write the function node (by swapping around the entries, such as putting timestamp infront of status etc) and it always shows an error for the last entry in the function node.

I think I know where I am going wrong. I haven't entered my MSSQL database anywhere in the function node, but I have linked it up to the sqldbs node which has my details in?

I also know that I need to say which table I am taking the data from, the table is dbo.NodeRedData, but when I tried the code:
"msg.topic = "INSERT INTO tr (id, Topic, JobType, Information, Timestamp, Status) VALUES FROM dbo.NodeRedData (id, Topic, JobType, Information, Timestamp, Status)";
return msg;"

Hi, you should be using the SQL "SELECT" statement to get information from your database.

Garry

Liam Broughton

unread,

Mar 22, 2018, 6:21:18 AM3/22/18

to Node-RED

Hi Garry,

After a bit of playing with your guidance I have gotten a bit further!

My function node now reads:

"msg.topic = "INSERT INTO tr (id, Topic, JobType, Information, Timestamp, Status) SELECT * FROM dbo.NodeRedData";

return msg;"

I am, however, now getting the error "Invalid object name "tr".

How do I find out what the object name for the table is?? Is there anyway within HTML that I can give the table an object name to refer to in my function node?

Thanks for the help so far Garry!

-Liam.

Mark Setrem

unread,

Mar 22, 2018, 6:31:37 AM3/22/18

to Node-RED

your msg.topic still reads INSERT

Check out the syntax you need for a SELECT statement

https://www.w3schools.com/sql/sql_select.asp

Garry Hayne

unread,

Mar 22, 2018, 6:36:48 AM3/22/18

to Node-RED

is trying to insert data into table tr, which is why you are getting an error as it doesn't exist.

Just try: SELECT * FROM dbo.NodeRedData and feed it to a debug node to see if it works.

Garry

Liam Broughton

unread,

Mar 22, 2018, 6:41:27 AM3/22/18

to Node-RED

"INSERT INTO tr (id, Topic, JobType, Information, Timestamp, Status)
is trying to insert data into table tr, which is why you are getting an error as it doesn't exist.
Just try: SELECT * FROM dbo.NodeRedData and feed it to a debug node to see if it works.

Garry

When using this the result I get is, "Executing (default): SELECT * FROM dbo.NodeRedData", but then no outcome in the table and no other messages displayed.

Is this because I need to include an INSERT function to insert the data retrieved into the table? If so, how do I create a table name in html for the table I have created in order to refer to it in my function node?

Thanks,

-Liam.

Garry Hayne

unread,

Mar 22, 2018, 6:48:44 AM3/22/18

to Node-RED

Liam, the INSERT statement will only work on the database, not what you are trying to do with the HTML table. I cannot help you with that as I have very little experience in that area. I am sure you will get help from someone here soon.

Garry

Liam Broughton

unread,

Mar 22, 2018, 6:49:37 AM3/22/18

to Node-RED

Right I see, well thanks for your help so far!

Thanks,

-Liam.

Zenofmud

unread,

Mar 22, 2018, 7:10:14 AM3/22/18

to node...@googlegroups.com

Attach a debug node (displaying the complete message object) to the output of the mysql object and see what you are receiving.

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/c5d21824-408f-4038-b3dc-75d07790cfe2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Liam Broughton

unread,

Mar 22, 2018, 7:14:35 AM3/22/18

to Node-RED

Hi Paul,

I can see exactly what I am receiving from the MSSQL node. I am retrieving all the data that is being displayed in my database. This is exactly what I need.

What I am now stuck with is displaying this data in my HTML table that I have created.

I am guessing I will have to create a object or table name for the HTML table and then refer to the data output of the mssql node?

I have tried a couple of ways already with creating a table name but no luck so far.

Any ideas?

Thanks,

-Liam.

Liam Broughton

unread,

Mar 22, 2018, 7:22:44 AM3/22/18

to Node-RED

Something else to bare in mind.

I am wanting the data being inserted into the table to overwrite every time.

Will I have to include something else in my function node in order to allow this? I do not want a table with thousands of entries in overtime because they aren't being deleted!

Thanks,

-Liam.

steve rickus

unread,

Mar 22, 2018, 9:09:03 AM3/22/18

to Node-RED

Liam,

I think you are confusing html "tables" with database tables. The Sql INSERT statement is trying to add rows to an existing database table (which in your case was never created) and so is causing that error.

The Sql SELECT statement simply retrieves data for the rows you want to see -- so we usually use a "filtered" query statement like SELECT col1, col2, col3 FROM tablename WHERE col4 > '2018-01-01' (if col4 is a date or timestamp field). The MSSQL node returns the selected rows of data in msg.payload as an array of javascript objects, with fields matching the database table column names. This is all the data you need to render an html table.

Then you can wire the MSSQL node to a core template node that outputs the html <table> ... </table> as one big string, using "mustache" substitution, similar to this (untested - you will have to make it match your data):

<table>
{{#payload}}
  <tr>
    <td>{{{col1}}}</td>
    <td>{{{col2}}}</td>
    <td>{{{col3}}}</td>
  </tr>
{{/payload}}
</table>

The double-curly-brace "payload" tags cause the interpreter to iterate over the array of payload objects, creating an html table row for each one. Inside the row, you can substitute the value for each column using the triple-curly-brace syntax (which escapes any html specific characters). Set the template node to return plain text (which happens to contain html syntax) and wire the output to a dashboard ui_template node, containing the default value of:

<div ng-bind-html="msg.payload"></div>

which just inserts all the table html as-is inside the <div> element, but interprets it as html. This should render a simple table of your query results. There are other more fancy ways to do this, using Angular material design elements, but that can be done after this is working. If you have trouble showing your results, then send us the query results as a JSON string, and one of us can help you figure out what to try next.

--

Steve

Liam Broughton

unread,

Mar 22, 2018, 10:51:46 AM3/22/18

to Node-RED

Hi Steve,

I understand the difference between the HTML table and the database table, I am just getting confused with the different functions for both :')

After trying to implement the code you have given me I am left with some code being displayed in my ui. (See attached screenshot).

How do I send the query results as a JSON string?

In the meantime I will attach screenshots of each node to show you what I have entered.

Thanks,

-Liam.

FUNCTION CODE.png

CORE TEMPLATE NODE CODE.png

UI CODE.png

NODE-RED FLOW.png

Zenofmud

unread,

Mar 22, 2018, 10:55:46 AM3/22/18

to node...@googlegroups.com

The number of rows you get from a database table will be determined by the number of rows in the table and the ‘where’ clause you use in your ‘select’ statement. A ‘select’ with no where clause will return all rows in the table.

You may want to think about how much data is returned and how fast you want to get it. You don’t want to cause problems by using up all the memory on your machine or trying to display too much data on the UI at any one time.

Maybe you need to explain what you are trying to do - i.e. something like “I need to display the last hours readings from the database in a table in ten minute increments. The data is stored in the database every 2 seconds”

This would let me know that you have stored a row every 2 seconds, this is 30 rows per minute or 300 rows in 10 minutes. Now displaying 300 pieces of data in a table is a big table, so I might ask if you could summarize it bit, maybe take the the average every 15 seconds which would give you 20 rows - a little easier to digest.

Just some thoughts.

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/65c640a0-bd5a-462d-90be-c1fd45f2c7d0%40googlegroups.com.

Liam Broughton

unread,

Mar 22, 2018, 10:56:39 AM3/22/18

to Node-RED

Just realised! Instead of changing the output to plain text, I changed the format to plain text!

I have adjusted this and now the UI page is showing nothing. No display at all.

Any ideas?

Thanks,

-Liam.

Liam Broughton

unread,

Mar 22, 2018, 11:03:49 AM3/22/18

to Node-RED

Hi Paul,

I am just trying to get the data to actually display at the moment. However I do understand where you are coming from.

Basically what I am trying to achieve through the whole of this is my own Job Ticketing app.

I have already created UI pages in order to insert a job, update a job status and delete a single job or a range of jobs.

I am now looking to display these jobs from my database into a table on my ui page on Node-RED.

I understand that if I am pulling a vast amount of jobs across then this may cause many issues. Maybe I need a way to be able to choose how many jobs I want the table to load? The easiest way to do this would be via the timestamp as Steve has already suggested.

Could I use the ui_form in order to control how many jobs are pulled from the database? By maybe inserting dates into the form and then inserting the payload data into the function node?

Thanks,

-Liam.

Liam Broughton

unread,

Mar 22, 2018, 11:12:20 AM3/22/18

to Node-RED

In regards to how many jobs would be entered is totally unanimous.

This could vary between 1 job per hour, to 20 jobs per hour. At the end of the day there isn't going to be many jobs being displayed in the table at one time once I add the where clause.

One thing I am going to look into afterwards is being able to filter the html table. If someone is using the table to look at what jobs they have to do then I want them to be able to filter the results.

Is this a difficult process?

Thanks,

-Liam.

Zenofmud

unread,

Mar 22, 2018, 12:21:42 PM3/22/18

to node...@googlegroups.com

Why filter the HTML table? If the jobs in the DB have an assigned user, you just need to create the ‘where’ clause using the name of the field and the user ID. Something like

SELECT * FROM tablename WHERE userid = IDofUser

How you grab the users ID is up to you, maybe they enter it in a field in the UI and you grab it from there, maybe you have a sign and grab it from there, that’s up to you.

Note: the above select assumes that ‘userid’ is a field in the ‘table name’ table. Your names will probably be different.

Liam Broughton

unread,

Mar 22, 2018, 12:29:17 PM3/22/18

to Node-RED

Hi Paul,

Didn't even think of doing it like that!! My original thought was to embed my excel file that I have an external data conenction to my mssql database on the node-red ui page (as I have enabled the dashboard to be accessed over the internet I can insert some html code into a node-red ui_template node and it displays the spreadsheet).

However, Microsoft being Microsoft this requires a SharePoint license and a PowerBI Pro license also. This is just because of the external data connection I have in the spreadsheet.

So therefore I am trying to do this cheap, and sometimes cheap requires a lot of time!

I am learning all of this as I go along so thank you all for your help with this so far!

I have noted down what you suggested Paul and I will have a look into that once the html table is up and running.

Thanks,

-Liam.

Liam Broughton

unread,

Mar 23, 2018, 6:52:35 AM3/23/18

to Node-RED

Hi all,

Has anyone got any clue to why nothing is being displayed in the dashboard? Do I have to add anything to the code at all?

Thanks,

-Liam.

Zenofmud

unread,

Mar 23, 2018, 6:59:10 AM3/23/18

to node...@googlegroups.com

Liam,

If you export your flow and paste it into a response it will make it easier for people to actually see what you are doing and help.

Paul

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/d7ee7fd6-e58f-4dfc-8d76-9206b9e93aec%40googlegroups.com.

Liam Broughton

unread,

Mar 23, 2018, 7:07:20 AM3/23/18

to Node-RED

Hi Paul,

Here is my Node-RED flow.

-Liam.

Zenofmud

unread,

Mar 23, 2018, 9:39:01 AM3/23/18

to node...@googlegroups.com

Well I just imported the flow and had to change from sqldbs to mysql and things seem fine. Where do you see an issue?

On Mar 23, 2018, at 7:07 AM, 'Liam Broughton' via Node-RED <node...@googlegroups.com> wrote:

Hi Paul,

Here is my Node-RED flow.

<…snip…>

-Liam.

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/ec5aae2e-61e8-4fcb-a959-ca12f60aa86c%40googlegroups.com.

Liam Broughton

unread,

Mar 23, 2018, 10:05:55 AM3/23/18

to Node-RED

That's very strange...

There is just nothing showing in the dashboard page (see attached screenshot).

What could be causing a blank table then??

Thanks,

-Liam.

NO HTML TABLE SHOWING.png

Zenofmud

unread,

Mar 23, 2018, 10:18:52 AM3/23/18

to node...@googlegroups.com

What happens when you press refresh?

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/81985ada-e20f-40bb-b7d5-b1135a9e695f%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

<NO HTML TABLE SHOWING.png>

Liam Broughton

unread,

Mar 23, 2018, 10:38:39 AM3/23/18

to Node-RED

I get this displaying in my debug tab (see screenshot)

Seems as if the data is being selected from my database, but isn't inputting correctly into my html table. (see screenshot of HTML code for reference).

-Liam.

DEBUG.png

HTML CODE.png

Zenofmud

unread,

Mar 23, 2018, 11:06:04 AM3/23/18

to node...@googlegroups.com

one problem is that in the template node you use ‘Job Type” and the field is “JobType”

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/e45f268a-420b-4e74-a9dd-8866a714b402%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

<DEBUG.png><HTML CODE.png>

Liam Broughton

unread,

Mar 23, 2018, 11:10:20 AM3/23/18

to Node-RED

I will try this shortly.

I am currently updating my nodejs and node-red.

Thanks,

-Liam.

Zenofmud

unread,

Mar 23, 2018, 11:23:13 AM3/23/18

to node...@googlegroups.com

Here is another thing to try. Import this flow (a single inject node) to the spreadsheet flow. Then attach it to the input of the ’template’ node (not the ‘ui_template’). Deploy, press the inject and see what the output of the template node looks like.

This is data from the testing I set up.

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/c198a8d6-4374-4b51-8b81-342974d5bb40%40googlegroups.com.

Nick O'Leary

unread,

Mar 23, 2018, 12:19:42 PM3/23/18

to Node-RED Mailing List

Liam,

in your template, the syntax isn't quite right. For each of the rows where you have:

<th>{{{payload.SOMETHING}}}</th>

you should remove the payload. bit:

<th>{{{SOMETHING}}}</th>

This is because they are inside the {{#payload}} ... {{/payload}} block, so the 'payload' bit is already assumed.

Nick

On 23 March 2018 at 15:23, Zenofmud <zeno...@zenofmud.org> wrote:

Here is another thing to try. Import this flow (a single inject node) to the spreadsheet flow. Then attach it to the input of the ’template’ node (not the ‘ui_template’). Deploy, press the inject and see what the output of the template node looks like.

This is data from the testing I set up.

On Mar 23, 2018, at 11:10 AM, 'Liam Broughton' via Node-RED <node...@googlegroups.com> wrote:

I will try this shortly.

I am currently updating my nodejs and node-red.

Thanks,

-Liam.

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.

To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/c198a8d6-4374-4b51-8b81-342974d5bb40%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.

To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/B6603251-32C5-40FC-917A-5F772D4560C9%40zenofmud.org.

Liam Broughton

unread,

Mar 23, 2018, 12:26:11 PM3/23/18

to Node-RED

Hi Nick,

I only added that into the template node to see if it would work. Just because it wasn't displaying anything beforehand anyway.

My Node-RED for some reason didn't install (even though it said it did) so after the "update-nodejs-and-nodered! command, I went to run node red (node-red-start) and it said that Node-RED wasn't installed yet.

So it has asked me if I wanted to install, I have chosen to install again. It has run the same command as before so will have to see if it works this time.

I will try out all your suggestions as soon as my Node-RED server starts working...

If it isn't within the next hour though I am going to have to leave this until Monday.

Thankyou all for the help so far and I will get back to you as soon as I have gotten my Node-RED to install again, and when I have tested out all your suggestions.

Have a nice weekend all.

-Liam.

Nick O'Leary

unread,

Mar 23, 2018, 12:29:38 PM3/23/18

to Node-RED Mailing List

Hi Liam,

You've mentioned getting errors a few times, but you haven't shared what errors they are.

if you share the errors you are getting when you run the script we will be able to help.

As soon as you start experimenting with other commands and approaches it becomes increasingly hard for us to help as we don't know what state you're in.

Nick

--

http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/30de9e45-69fd-4922-b21f-d70ab2b6628f%40googlegroups.com.

Colin Law

unread,

Mar 23, 2018, 12:31:35 PM3/23/18

to node...@googlegroups.com

As I said, if the update script apparently didn't work then post the log here. Also copy/paste the result of nod-red-start and run

node-red-log

and paste the result here. As Nick says just ploughing on trying other things without sorting the underlying problem is unlikely to end up with a fully working system.

Colin

--

http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/30de9e45-69fd-4922-b21f-d70ab2b6628f%40googlegroups.com.

Liam Broughton

unread,

Mar 23, 2018, 12:32:34 PM3/23/18

to Node-RED

I will start sharing what errors I am receiving as it will be a lot clearer to you guys about what is actually going wrong haha.

Thanks,

-Liam.

Zenofmud

unread,

Mar 23, 2018, 1:28:51 PM3/23/18

to 'Liam Broughton' via Node-RED

ummm, it wold help if I actually included the data:

[{"id":"130d1c60.26f93c","type":"inject","z":"b712be2a.b8866","name":"","topic":"","payload":"[{\"id\":1,\"Topic\":\"Job\",\"JobType\":\"PAUL\",\"Information\":\"EXERCISE\",\"Status\":\"Not Completed\",\"Timestamp\":\"2018-03-23T08:34:37.000Z\"},{\"id\":2,\"Topic\":\"Job\",\"JobType\":\"RYAN\",\"Information\":\"Baby sit Lily\",\"Status\":\"Not Completed\",\"Timestamp\":\"2018-03-23T08:34:57.000Z\"},{\"id\":3,\"Topic\":\"Job\",\"JobType\":\"Carol ane\",\"Information\":\"Upgrade her computer\",\"Status\":\"Not Completed\",\"Timestamp\":\"2018-03-23T08:42:25.000Z\"}]","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":90,"y":420,"wires":[["f1553a4e.7f3858","b24abc49.1a918"]]}]

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/B6603251-32C5-40FC-917A-5F772D4560C9%40zenofmud.org.

Mark Setrem

unread,

Mar 23, 2018, 6:31:01 PM3/23/18

to Node-RED

it would also be useful to clarify which version of the dashboard you are using. I am assuming this is using Tiago’s hack? If so you need to find out which version of the node-red dashboard it is based on

Zenofmud

unread,

Mar 23, 2018, 7:03:23 PM3/23/18

to node...@googlegroups.com

I imported Liam’s flow into a basic install that has node-red-dashboard installed and it works fine.

> On Mar 23, 2018, at 6:31 PM, Mark Setrem <mse...@gmail.com> wrote:
>
> it would also be useful to clarify which version of the dashboard you are using. I am assuming this is using Tiago’s hack? If so you need to find out which version of the node-red dashboard it is based on
>

> --
> http://nodered.org
>
> Join us on Slack to continue the conversation: http://nodered.org/slack
> ---
> You received this message because you are subscribed to the Google Groups "Node-RED" group.

> To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
> To post to this group, send an email to node...@googlegroups.com.

> Visit this group at https://groups.google.com/group/node-red.

> To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/7872506b-23c9-41cd-a06f-69591f67b329%40googlegroups.com.

Liam Broughton

unread,

Mar 26, 2018, 4:22:53 AM3/26/18

to Node-RED

Hi all,

I have been having some issues with updating nodejs and node red on my raspberry pi 1, so haven't been able to get round to trying out Paul's flow..

Mark - I was using the regular updated version of node-red-dashboard when trying out this html table (v2.8.2) However, I am now trying to use Tiago's hack, but in order to use it I am having to update my Node-RED and nodejs.

I will post my Node-RED log here also (just posted in another post also) just incase you guys see this first:

Started : Mon 26 Mar 07:36:43 UTC 2018

Running for user pi at /home/pi

Found global nodes: :

Reading package lists...

Building dependency tree...

Reading state information...

Package 'nodered' is not installed, so not removed

0 upgraded, 0 newly installed, 0 to remove and 206 not upgraded.

Reading package lists...

Building dependency tree...

Reading state information...

Package 'nodejs' is not installed, so not removed

Package 'nodejs-legacy' is not installed, so not removed

Package 'npm' is not installed, so not removed

0 upgraded, 0 newly installed, 0 to remove and 206 not upgraded.

dpkg: warning: ignoring request to remove nodejs which isn't installed

dpkg: warning: ignoring request to remove node which isn't installed

Reading package lists...

Building dependency tree...

Reading state information...

0 upgraded, 0 newly installed, 0 to remove and 206 not upgraded.

npm WARN using --force I sure hope you know what you are doing.

npm WARN deprecated i18next...@1.10.3: you can use npm install i18next from version 2.0.0

npm WARN deprecated nodem...@1.11.0: All versions below 4.0.1 of Nodemailer are deprecated. See https://nodemailer.com/status/

npm WARN deprecated mailp...@0.6.2: This project is unmaintained

npm WARN deprecated mim...@0.3.1: This project is unmaintained

npm WARN deprecated mailco...@2.1.0: This project is unmaintained

npm WARN deprecated buil...@2.0.0: This project is unmaintained

npm ERR! code EINTEGRITY

npm ERR! sha512-o3KqipXNUdS7wpQzBHSe180lBGO60SoK0yVo3CYJgb2MkobuWuBX6dhkYP5ORCLd55y+SaflMOV5fqAB53ux4w== integrity checksum failed when using sha512: wanted sha512-o3KqipXNUdS7wpQzBHSe180lBGO60SoK0yVo3CYJgb2Mkobu$

As soon as I get my Node-RED and nodejs updated I will be sure to try out all of your suggestions.

Thanks,

-Liam.

Liam Broughton

unread,

Mar 28, 2018, 8:04:18 AM3/28/18

to Node-RED

Hi all,

I have finally been able to update my Node-RED (v0.18.4), my npm (5.8.0) and my nodejs (v8.10.0).

I am now using Tiago's hack for the Node-RED-Dashboard from - https://github.com/netsmarttech/node-red-dashboard

For some reason the HTML table is still not working...

When I added Paul's inject node I get some data showing on my dashboard page (see attached screenshot). But I am still getting nothing when trying to insert my own data into the HTML table..

I am still seeing the data being pulled from my MSSQL database (this is being displaying in the debug tab).

Any suggestions?

-Liam.

DATA SHOWING IN DASHBOARD.png

Zenofmud

unread,

Mar 28, 2018, 8:55:17 AM3/28/18

to node...@googlegroups.com

Attach a debug node to your mysql node and display the complete msg object and see what it looks like compared to what I send in the inject node.

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.

Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/72d0a2e0-ced4-4da6-800e-a820eb679240%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

<DATA SHOWING IN DASHBOARD.png>

Liam Broughton

unread,

Mar 28, 2018, 9:23:34 AM3/28/18

to Node-RED

I can see what you mean by having the debug node attached to the mssql node..

When I copy the value from the debug tab it is just "payload".

However, payload is already defined in my html table right?

So do I have to reference something else that is coming off of the debug node?

-Liam.

Zenofmud

unread,

Mar 28, 2018, 9:39:27 AM3/28/18

to node...@googlegroups.com

Lets backup for a moment.

1) right now you are working of the ‘spreadsheet’ tab…correct?

2) on that tab you have connected a debug node to the OUTPUT of the mysql tab…correct?

3) in the debug node, you changed it to display the complete msg object…correct?

4) attach an ‘Inject’ node to the input of the function node

5) deploy the flow and press the inject button

When all of this is done, look at the debug panel and you should see something like this:

Press the area indicated by the circle (a copy of the data ) and paste it into a reply.

also paste a copy of your entire flow again so I can check to make sure you have things correct.

paul

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/43413b37-6530-4090-a7ca-81863582f6a3%40googlegroups.com.

Liam Broughton

unread,

Mar 28, 2018, 9:48:12 AM3/28/18

to Node-RED

1) Yes

2) Yes

3) It displays the data as [ array[5], array[5] ], but I have copied the value from it (which you will see in the test inject node that I have in my flow.

4) Yes, I tested that with yours and it inputted data in correctly. However when trying to do it with mine it didn't?

5) See "4)"

Here is the value copied from my debug from my mssql node:

[[{"id":92,"Topic":"Job ","JobType":"TEST ","Information":"TESTING OVER THE INTERNET ","Status":"In Progress ","Timestamp":"2018-03-20T14:58:13.567Z"},{"id":93,"Topic":"Job ","JobType":"Liam ","Information":"This is a test from mobile app. ","Status":"Not Completed ","Timestamp":"2018-03-21T08:51:23.973Z"},{"id":94,"Topic":"Job ","JobType":"Update test ","Information":"Test ","Status":"Not Completed ","Timestamp":"2018-03-21T09:40:15.963Z"},{"id":96,"Topic":"Job ","JobType":"TEST ","Information":"TESTINFO ","Status":"Not Completed ","Timestamp":"2018-03-22T09:26:05.510Z"},{"id":95,"Topic":"Job ","JobType":"Bob ","Information":"Go upstairs ","Status":"Not Completed ","Timestamp":"2018-03-21T16:25:09.640Z"}],[{"id":92,"Topic":"Job ","JobType":"TEST ","Information":"TESTING OVER THE INTERNET ","Status":"In Progress ","Timestamp":"2018-03-20T14:58:13.567Z"},{"id":93,"Topic":"Job ","JobType":"Liam ","Information":"This is a test from mobile app. ","Status":"Not Completed ","Timestamp":"2018-03-21T08:51:23.973Z"},{"id":94,"Topic":"Job ","JobType":"Update test ","Information":"Test ","Status":"Not Completed ","Timestamp":"2018-03-21T09:40:15.963Z"},{"id":96,"Topic":"Job ","JobType":"TEST ","Information":"TESTINFO ","Status":"Not Completed ","Timestamp":"2018-03-22T09:26:05.510Z"},{"id":95,"Topic":"Job ","JobType":"Bob ","Information":"Go upstairs ","Status":"Not Completed ","Timestamp":"2018-03-21T16:25:09.640Z"}]]

Here is my complete Node-RED project flow:

Zenofmud

unread,

Mar 28, 2018, 10:18:53 AM3/28/18

to node...@googlegroups.com

Liam

Please open the debug node attached to the output of the mssql node. In the debug node options you will “output” which now displays m.s.payload. Click the down arrow and display the ‘complete message object.

I notice in the data you provided, the output of the mssql has two open brackets - ‘[[‘ - at the start and at the end has two closing brackets ‘]]’ while my data from the mysql node only has a single bracket in both positions. That would explain why my test inject works and the data from the mssql node does not.

Seeing the complete msg object may shine some light on this.

I have to leave for a bit but will check in when I get back.

Paul

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/da433b3f-868e-4286-a6b8-ad2b3cbbf163%40googlegroups.com.

Liam Broughton

unread,

Mar 28, 2018, 10:38:45 AM3/28/18

to Node-RED

Thanks for that Paul.

Here is the complete message object:

{"payload":[[{"id":92,"Topic":"Job","JobType":"TEST ","Information":"TESTING OVER THE INTERNET","Status":"In Progress","Timestamp":"2018-03-20T14:58:13.567Z"},{"id":93,"Topic":"Job","JobType":"Liam","Information":"This is a test from mobile app.","Timestamp":"2018-03-21T08:51:23.973Z"},{"id":94,"Topic":"Job","JobType":"Update test","Information":"Test","Status":"Not Completed","Timestamp":"2018-03-21T09:40:15.963Z"},{"id":96,"Topic":"Job","JobType":"TEST ","Information":"TESTINFO","Status":"Not Completed","Timestamp":"2018-03-22T09:26:05.510Z"},{"id":95,"Topic":"Job","JobType":"Bob","Information":"Go upstairs","Status":"Not Completed","Timestamp":"2018-03-21T16:25:09.640Z"}],[{"id":92,"Topic":"Job","JobType":"TEST","Information":"TESTING OVER THE INTERNET","Status":"In Progress","Timestamp":"2018-03-20T14:58:13.567Z"},{"id":93,"Topic":"Job","JobType":"Liam","Information":"This is a test from mobile app","Status":"Not Completed","Timestamp":"2018-03-21T08:51:23.973Z"},{"id":94,"Topic":"Job","JobType":"Update test","Information":"Test","Status":"Not Completed","Timestamp":"2018-03-21T09:40:15.963Z"},{"id":96,"Topic":"Job","JobType":"TEST","Information":"TESTINFO","Status":"Not Completed","Timestamp":"2018-03-22T09:26:05.510Z"},{"id":95,"Topic":"Job","JobType":"Bob ","Information":"Go upstairs","Status":"Not Completed","Timestamp":"2018-03-21T16:25:09.640Z"}]],"topic":"SELECT id, Topic, JobType, Information, Status, Timestamp FROM dbo.NodeRedData","socketid":"qdSWJBgHNz8GMby7AABT","_msgid":"9ecb2d03.f0f57"}

For some reason all of the above code pasted very spaced out so I have deleted the massive gaps between the text.

-Liam.

Liam Broughton

unread,

Mar 28, 2018, 10:43:10 AM3/28/18

to Node-RED

{"payload":[[{"id":92,"Topic":"Job                                                                                                                                                                                                                                                             ","JobType":"TEST                                                                                                                                                                                                                                                            ","Information":"TESTING OVER THE INTERNET                                                                                                                                                                                                                                       ","Status":"In Progress                                                                                                                                                                                                                                                     ","Timestamp":"2018-03-20T14:58:13.567Z"},{"id":93,"Topic":"Job                                                                                                                                                                                                                                                             ","JobType":"Liam                                                                                                                                                                                                                                                            ","Information":"This is a test from mobile app.                                                                                                                                                                                                                                 ","Status":"Not Completed                                                                                                                                                                                                                                                   ","Timestamp":"2018-03-21T08:51:23.973Z"},{"id":94,"Topic":"Job                                                                                                                                                                                                                                                             ","JobType":"Update test                                                                                                                                                                                                                                                     ","Information":"Test                                                                                                                                                                                                                                                            ","Status":"Not Completed                                                                                                                                                                                                                                                   ","Timestamp":"2018-03-21T09:40:15.963Z"},{"id":96,"Topic":"Job                                                                                                                                                                                                                                                             ","JobType":"TEST                                                                                                                                                                                                                                                            ","Information":"TESTINFO                                                                                                                                                                                                                                                        ","Status":"Not Completed                                                                                                                                                                                                                                                   ","Timestamp":"2018-03-22T09:26:05.510Z"},{"id":95,"Topic":"Job                                                                                                                                                                                                                                                             ","JobType":"Bob                                                                                                                                                                                                                                                             ","Information":"Go upstairs                                                                                                                                                                                                                                                     ","Status":"Not Completed                                                                                                                                                                                                                                                   ","Timestamp":"2018-03-21T16:25:09.640Z"}],[{"id":92,"Topic":"Job                                                                                                                                                                                                                                                             ","JobType":"TEST                                                                                                                                                                                                                                                            ","Information":"TESTING OVER THE INTERNET                                                                                                                                                                                                                                       ","Status":"In Progress                                                                                                                                                                                                                                                     ","Timestamp":"2018-03-20T14:58:13.567Z"},{"id":93,"Topic":"Job                                                                                                                                                                                                                                                             ","JobType":"Liam                                                                                                                                                                                                                                                            ","Information":"This is a test from mobile app.                                                                                                                                                                                                                                 ","Status":"Not Completed                                                                                                                                                                                                                                                   ","Timestamp":"2018-03-21T08:51:23.973Z"},{"id":94,"Topic":"Job                                                                                                                                                                                                                                                             ","JobType":"Update test                                                                                                                                                                                                                                                     ","Information":"Test                                                                                                                                                                                                                                                            ","Status":"Not Completed                                                                                                                                                                                                                                                   ","Timestamp":"2018-03-21T09:40:15.963Z"},{"id":96,"Topic":"Job                                                                                                                                                                                                                                                             ","JobType":"TEST                                                                                                                                                                                                                                                            ","Information":"TESTINFO                                                                                                                                                                                                                                                        ","Status":"Not Completed                                                                                                                                                                                                                                                   ","Timestamp":"2018-03-22T09:26:05.510Z"},{"id":95,"Topic":"Job                                                                                                                                                                                                                                                             ","JobType":"Bob                                                                                                                                                                                                                                                             ","Information":"Go upstairs                                                                                                                                                                                                                                                     ","Status":"Not Completed                                                                                                                                                                                                                                                   ","Timestamp":"2018-03-21T16:25:09.640Z"}]],"topic":"SELECT id, Topic, JobType, Information, Status, Timestamp FROM dbo.NodeRedData","socketid":"qdSWJBgHNz8GMby7AABT","_msgid":"9ecb2d03.f0f57"}

Zenofmud

unread,

Mar 28, 2018, 11:38:26 AM3/28/18

to node...@googlegroups.com

I’m out for a bit, but I think the double brackets is the issue. You might push it thru a change node to remove one set of the brackets to see what happens.

Or someone else may have a better idea.

Paul

--

http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/21b95de7-88bb-4687-b8cb-129664c85fa5%40googlegroups.com.

Zenofmud

unread,

Mar 28, 2018, 2:22:51 PM3/28/18

to node...@googlegroups.com

Liam,

I’m going to guess that in your table definition in mssql, you did not define ID as being automatically incremented. you have stored two copies of the same data in the database causing the sql to return two arrays of the same data.

Please try this. Delete all the rows out of the database. Change the ID field to be automatically incremented (see https://chartio.com/resources/tutorials/how-to-define-an-auto-increment-primary-key-in-sql-server/) and then add some data in and see if the spreadsheet works.

Paul

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/B92F165A-360A-435F-B944-4CAE226D731B%40zenofmud.org.

Liam Broughton

unread,

Mar 29, 2018, 3:47:35 AM3/29/18

to Node-RED

Hi Paul,

I actually did set the id in the table to be automatically incremented...

I will have a play about with the change node but I haven't used one before so may need some guidance.

I will let you know how I get on with it.

-Liam.

Liam Broughton

unread,

Mar 29, 2018, 3:48:44 AM3/29/18

to Node-RED

On a side note...

If my id already is defined as automatically incremented then how come I am getting 2 sets of data come through?

-Liam.

Colin Law

unread,

Mar 29, 2018, 3:53:37 AM3/29/18

to node...@googlegroups.com

Have a look at the contents of the database using another database
client. I don't use MSSQL so don't know what that might be. I presume
you have a good reason for using MSSQL rather then mysql or sqlite.

I have not been following the thread in detail. Have you shown us
exactly the query you are sending to the database? Preferably from a
Debug node showing the message sent to the db node.

Colin

On 29 March 2018 at 08:48, 'Liam Broughton' via Node-RED

> --
> http://nodered.org
>
> Join us on Slack to continue the conversation: http://nodered.org/slack
> ---
> You received this message because you are subscribed to the Google Groups
> "Node-RED" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to node-red+u...@googlegroups.com.
> To post to this group, send email to node...@googlegroups.com.
> Visit this group at https://groups.google.com/group/node-red.
> To view this discussion on the web, visit

> https://groups.google.com/d/msgid/node-red/2e5f6a85-a078-45ac-98e3-da672ab53b6d%40googlegroups.com.

Liam Broughton

unread,

Mar 29, 2018, 4:07:04 AM3/29/18

to Node-RED

Hi Colin,

I don't actually have a main reason for using MSSQL over the other database clients...

The main reason being that it was already installed on my machine when I joined the company I work for now and we use it for other applications.

I will have to try out using another database client... I may try out mysql as I have seen a lot of online tutorial videos regarding displaying data from a mysql table into a html table..

I will have a play later on today with this and let you all know on the progress of this.

-Liam.

Colin Law

unread,

Mar 29, 2018, 4:15:20 AM3/29/18

to node...@googlegroups.com

I was not necessarily suggesting you swtiched. If it is a simple
problem with the query then you will still have the problem anyway.
Note that you have already (I thought) cracked the problem of showing
the data, the problem is just getting the right data out of the
database.

If mssql is already used on site then that is a perfectly good reason to use it.

Colin

On 29 March 2018 at 09:07, 'Liam Broughton' via Node-RED

> --
> http://nodered.org
>
> Join us on Slack to continue the conversation: http://nodered.org/slack
> ---
> You received this message because you are subscribed to the Google Groups
> "Node-RED" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to node-red+u...@googlegroups.com.
> To post to this group, send email to node...@googlegroups.com.
> Visit this group at https://groups.google.com/group/node-red.
> To view this discussion on the web, visit

> https://groups.google.com/d/msgid/node-red/9f2aa120-ce9d-4b6b-a6d0-8ab51dfce0c9%40googlegroups.com.

Liam Broughton

unread,

Mar 29, 2018, 4:24:01 AM3/29/18

to Node-RED

That's what I was thinking Colin...

I have got this far so may aswell continue and try and solve this issue..

It's just annoying that Paul uses mysql, he inserted my flow, changed the mssql node to his mysql node and it worked perfectly fine??

Could it be a problem with the mssql node that I am using?

-Liam.

Zenofmud

unread,

Mar 29, 2018, 6:39:44 AM3/29/18

to 'Liam Broughton' via Node-RED

Liam

I see you have questions in another thread about the UI. I hope you have two Pi’s you are working so that changes you make for the other issue are not impacting this issue especially since you are using the UI in both cases.

Did you ever read the information provided with this node in the flows page for the node (i.e.on the NR website)?

Paul

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/f478b746-829a-4c36-9930-949f9bd05e62%40googlegroups.com.

Liam Broughton

unread,

Mar 29, 2018, 9:54:43 AM3/29/18

to Node-RED

Hi Paul,

To be honest I am working with one pi as that is all I have to work with...

I could run node-red on my own pc with the same setup, apart from the dashboard is the original and not the hack I am using?

Regarding the information provided with this node, I cannot find any specific information, the only info I was provided with was this link - https://github.com/netsmarttech/node-red-dashboard

-Liam.

Zenofmud

unread,

Mar 29, 2018, 11:12:01 AM3/29/18

to node...@googlegroups.com

What does the reader say about using the node?

Paul

--

http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/612a9905-bd99-466f-bc29-5f9b9c6d4bc4%40googlegroups.com.

Zenofmud

unread,

Mar 29, 2018, 11:14:55 AM3/29/18

to node...@googlegroups.com

Liam, look at the read me for the mssql node, after all that is what this thread is about

Paul

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/6CCC47FA-A626-42CE-9EEA-981EEC377BF7%40zenofmud.org.

Zenofmud

unread,

Mar 29, 2018, 11:18:48 AM3/29/18

to node...@googlegroups.com

My point about the ui is work one issue at a time. Get this working with the standard ui before changing things. Once you have the data displaying on your spreadsheet only then think about using different ui’s.

Paul

On Mar 29, 2018, at 11:11 AM, Zenofmud <zeno...@zenofmud.org> wrote:

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/6CCC47FA-A626-42CE-9EEA-981EEC377BF7%40zenofmud.org.

Liam Broughton

unread,

Mar 29, 2018, 11:23:31 AM3/29/18

to Node-RED

Right!

Well the information about the node usage for the mssql node is:

The returned data will be stored in msg.payload and it will contains two array.
You should just need to access the first object of the array to retrieve data returned from the database query.

So for example, you might create a function node that flows into your sqldbs node with code like this to find if the user exists in the table (please make a special note to "as count". It will serve as the key of the return to retrieve the data. However, if you use "select * from user", the column name will serve as the key name):

msg.topic = "select count(*) as count from user where username = '" + msg.payload.username + "'";
return msg;

Then, you can use a switch function after sqldbs node to check if the user exist:

if msg.payload[0][0].count > 0 
if msg.payload[0][0].count <= 0






So it seems that the node is supposed to provide two arrays in the debug tab..

I will look into this more Tuesday next week as I am about to finish work for the day.

I hope you have a nice Easter.

Speak soon,

-Liam.

Zenofmud

unread,

Mar 29, 2018, 1:02:11 PM3/29/18

to node...@googlegroups.com

So what does that mean for you?

Paul

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/3d61dbd7-2a46-4945-b2c7-1e1daf6db6fd%40googlegroups.com.

Liam Broughton

unread,

Apr 4, 2018, 7:31:03 AM4/4/18

to Node-RED

Hi Paul,

Sorry for the delayed response. I have been busy with other jobs.. But back onto this for now!

I am guessing that this means that I only have to reference the first array from my debug tab..

Does this mean in my html code where it says "{{#payload}}", I would change it to what my array path is in the debug tab?

-Liam.

Zenofmud

unread,

Apr 4, 2018, 7:37:03 AM4/4/18

to 'Liam Broughton' via Node-RED

If you do, what is the result?

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/aad8547c-5b24-4aa5-8757-9902e0a18784%40googlegroups.com.

Liam Broughton

unread,

Apr 4, 2018, 7:46:41 AM4/4/18

to Node-RED

Just tried this and nothing displays on the dashboard page again.

-Liam.

Zenofmud

unread,

Apr 4, 2018, 7:50:14 AM4/4/18

to node...@googlegroups.com

Ok, since you have a payload with an array of two objects and you only need the payload to contain the first or the two objects, how do you thing you can ‘change' the payload to only contain the first of the two arrays?

On Apr 4, 2018, at 7:46 AM, 'Liam Broughton' via Node-RED <node...@googlegroups.com> wrote:

Just tried this and nothing displays on the dashboard page again.

-Liam.

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/0aa519c5-3fc0-4e18-a2ff-45f38cd1de5e%40googlegroups.com.

Liam Broughton

unread,

Apr 4, 2018, 7:57:20 AM4/4/18

to Node-RED

I have attached my debug tab just to show you what I am receiving from my flow when trying to refresh the table.

I am guessing that I would use a change node and filter out the payload[1]??

-Liam.

debug tab.png

Zenofmud

unread,

Apr 4, 2018, 8:23:12 AM4/4/18

to node...@googlegroups.com

If you think a change node will work, have you tried it?

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/fc894648-5754-415d-ab62-3e65213b48bb%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

<debug tab.png>

Liam Broughton

unread,

Apr 4, 2018, 9:45:38 AM4/4/18

to Node-RED

Hi Paul,

Yes I tried this and it now only displays one array of data listed as "payload", I am now referencing it in my html table as "payload" and not "payload[0]", but it still isn't showing any data. There isn't even a table being displayed at all, just blank.

When putting the change node between my sqldbs node and the function node (with the html code in it), is showing this in the debug tab for the function node:

<table>
  <tr>
    <th></th>
    <th></th>
    <th></th>
    <th></th>
    <th></th>
    <th></th>
  </tr>
</table>

-Liam.

Zenofmud

unread,

Apr 4, 2018, 10:12:40 AM4/4/18

to 'Liam Broughton' via Node-RED

and what did you put in the change node?

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/36335f3f-27c6-4258-9f34-9f5b2175b338%40googlegroups.com.

Liam Broughton

unread,

Apr 4, 2018, 10:15:13 AM4/4/18

to Node-RED

I have attached a screenshot of what I have entered in my change node.

-Liam.

change node.png

Zenofmud

unread,

Apr 4, 2018, 10:26:22 AM4/4/18

to node...@googlegroups.com

Can you copy and paste the output of the debug node? The image is too hard to read and I can’t identify which it is.

15 AM, 'Liam Broughton' via Node-RED <node...@googlegroups.com> wrote:

I have attached a screenshot of what I have entered in my change node.

-Liam.

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/25d3034f-9f06-4e12-a8c4-6dabd8fe3e14%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

<change node.png>

Liam Broughton

unread,

Apr 4, 2018, 10:34:18 AM4/4/18

to Node-RED

This is the output of the debug node from the sqldbs going through the change node:

{

"payload": [

[

{

"id": 92,

"Topic": "Job ",

"JobType": "TEST ",

"Information": "TESTING OVER THE INTERNET ",

"Status": "In Progress ",

"Timestamp": "2018-03-20T14:58:13.567Z"

},

{

"id": 93,

"Topic": "Job ",

"JobType": "Liam ",

"Information": "This is a test from mobile app. ",

"Status": "Not Completed ",

"Timestamp": "2018-03-21T08:51:23.973Z"

},

{

"id": 94,

"Topic": "Job ",

"JobType": "Update test ",

"Information": "Test ",

"Status": "Not Completed ",

"Timestamp": "2018-03-21T09:40:15.963Z"

},

{

"id": 96,

"Topic": "Job ",

"JobType": "TEST ",

"Information": "TESTINFO ",

"Status": "Not Completed ",

"Timestamp": "2018-03-22T09:26:05.510Z"

},

{

"id": 97,

"Topic": "Job ",

"JobType": "1234 ",

"Information": "123456789 ",

"Status": "Not Completed ",

"Timestamp": "2018-04-04T05:41:53.337Z"

},

{

"id": 95,

"Topic": "Job ",

"JobType": "Bob ",

"Information": "Go upstairs ",

"Status": "Not Completed ",

"Timestamp": "2018-03-21T16:25:09.640Z"

}

]

],

"topic": "SELECT id, Topic, JobType, Information, Status, Timestamp FROM dbo.NodeRedData",

"socketid": "MZt1QctvXFtJOHVJAAXC",

"_msgid": "d24fc09b.0db7c"

}

-Liam.

Zenofmud

unread,

Apr 4, 2018, 10:46:33 AM4/4/18

to node...@googlegroups.com

Ok, that is correct, now what do you have in the template node? If you made any changes, put them back to what you originally had

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/4388115d-df9f-45fc-b9fd-d890f590e8f3%40googlegroups.com.

Liam Broughton

unread,

Apr 4, 2018, 10:50:18 AM4/4/18

to Node-RED

Here is what is in my template node, no changes made:

<table>

<tr>

<th>{{{Topic}}}</th>

<th>{{{JobType}}}</th>

<th>{{{Information}}}</th>

<th>{{{Status}}}</th>

<th>{{{Timestamp}}}</th>

</tr>

</table>

-Liam.

Colin Law

unread,

Apr 4, 2018, 10:53:54 AM4/4/18

to node...@googlegroups.com

Payload is still an array containing an array of length 1, doesn't it want to be just an array?

Colin

To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/4388115d-df9f-45fc-b9fd-d890f590e8f3%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.

To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/3804F5BB-5D8D-442F-874E-E25D837EB1E2%40zenofmud.org.

Zenofmud

unread,

Apr 4, 2018, 11:02:47 AM4/4/18

to node...@googlegroups.com

Liam, try this, edit your change node and use this:

set: msg.payload

to: msg.payload[0]

and see what that does

To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/CAL%3D0gLsNW2LpOXQx84FNa87SJGQbnadydf0JLtog%2Bk8ng5MyXQ%40mail.gmail.com.

Liam Broughton

unread,

Apr 4, 2018, 11:08:35 AM4/4/18

to Node-RED

Thanks Paul!!!!

It is now displaying the data!!

What did changing the "change node" actually do then?

The only thing now is that there are no headers on my table, and the data is being displayed in random orders.

Is there a way that I can implement a "filter" so I can arrange the id part of the table to be in order from highest to lowest?

-Liam.

Zenofmud

unread,

Apr 4, 2018, 11:38:21 AM4/4/18

to node...@googlegroups.com

On Apr 4, 2018, at 11:08 AM, 'Liam Broughton' via Node-RED <node...@googlegroups.com> wrote:

Thanks Paul!!!!

It is now displaying the data!!

What did changing the "change node" actually do then?

The output of the sql was a two dimensional array (i.e. [[0],[1]]) your change node (delete msg.payload[1]) removed that array element from the array (i.e. [[0],[1]] => [[0]]) but was was needed was to set msg.payload to the value of the first element in the array. So the change I gave you (msg.payload = msg.payload[0]) did that (i.e. [[0],[1]] => [0] note the single set of brackets).

So now msg.payload is just a one dimensional array.

The only thing now is that there are no headers on my table, and the data is being displayed in random orders.

what? you want everything (smile)

Is there a way that I can implement a "filter" so I can arrange the id part of the table to be in order from highest to lowest?

It’s time you read up about SQL https://www.w3schools.com/sql/

-Liam.

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.

To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/9e41bf16-d274-4c94-b269-96e12dd82d3f%40googlegroups.com.

Mark Setrem

unread,

Apr 4, 2018, 11:39:03 AM4/4/18

to Node-RED

Perhaps now would be a good time to do read up on javascript objects and arrays. Which if you want to do data manipulation in node-red will be time well spent.
The w3schools site is often recommended on this group. https://www.w3schools.com/jsref/jsref_obj_array.asp

Liam Broughton

unread,

Apr 4, 2018, 11:41:53 AM4/4/18

to Node-RED

Haha! Everything is needed!! :)

I will definitely have a look into both the links that you guys have suggested.

I will create a new post if I get stuck along the way (as this one is getting very congested!)