How to create table with SQLite

General
1

Hello there,
I'm relatively new to Node-Red, and I'm trying to create a table with values from SQLite, my problem is that the values are not in the table, my SQLite statement works fine but when it comes to the template node it just doesn't create a row. I'm pretty sure the problem is with the template node, but I don't know where, I hope someone can help me.

Edit:
For the SQLite DB im using "node-red-node-sqlite"

template node:

<body>
  <table style="width:100%" id="myTable">
    <thead>
      <tr>
        <th>_dateUTC_</th>
        <th>_dateLocal_</th>
        <th>_timePeriod_</th>
        <th>LB.RS1.CN</th>
        <th>LB.RS2.CN</th>
        <th>LB.RS3.CN</th>
        <th>LB.HM40811.CN</th>
        <th>LB.HM40810.CN</th>
      </tr>
    </thead>
    <tbody>
      {{#each msg.payload}}
      <tr>
        <td>{{this._dateLocal_}}</td>
        <td>{{this._dateUTC_}}</td>
        <td>{{this._timePeriod_}}</td>
        <td>{{this["LB.RS1.CN"]}}</td>
        <td>{{this["LB.RS2.CN"]}}</td>
        <td>{{this["LB.RS3.CN"]}}</td>
        <td>{{this["LB.HM40811.CN"]}}</td>
        <td>{{this["LB.HM40810.CN"]}}</td>
      </tr>
      {{/each msg.payload}}
    </tbody>
  </table>
</body>

Template node configuration:

image
image456Ă—858 24.9 KB

DB structure:
image

Flow:

image
image862Ă—130 10.1 KB 

[{"id":"6f98e7b14ffbc67d","type":"sqlite","z":"56fc7d74926adf91","mydb":"5ca7db8ffd6c58ac","sqlquery":"msg.topic","sql":"SELECT * FROM Allgemein;","name":"Counter DB","x":710,"y":1720,"wires":[["a10e02a83c543357"]]},{"id":"25bfb2b8105c07ff","type":"http in","z":"56fc7d74926adf91","name":"","url":"/table-data","method":"get","upload":false,"swaggerDoc":"","x":700,"y":1660,"wires":[["a10e02a83c543357"]]},{"id":"3aa0245fb8a7805f","type":"http response","z":"56fc7d74926adf91","name":"","statusCode":"","headers":{},"x":1150,"y":1660,"wires":[]},{"id":"a10e02a83c543357","type":"template","z":"56fc7d74926adf91","name":"try table","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<body>\n  <table style=\"width:100%\" id=\"myTable\">\n    <thead>\n      <tr>\n        <th>_dateUTC_</th>\n        <th>_dateLocal_</th>\n        <th>_timePeriod_</th>\n        <th>LB.RS1.CN</th>\n        <th>LB.RS2.CN</th>\n        <th>LB.RS3.CN</th>\n        <th>LB.HM40811.CN</th>\n        <th>LB.HM40810.CN</th>\n      </tr>\n    </thead>\n    <tbody>\n      {{#each msg.payload}}\n      <tr>\n        <td>{{this._dateLocal_}}</td>\n        <td>{{this._dateUTC_}}</td>\n        <td>{{this._timePeriod_}}</td>\n        <td>{{this[\"LB.RS1.CN\"]}}</td>\n        <td>{{this[\"LB.RS2.CN\"]}}</td>\n        <td>{{this[\"LB.RS3.CN\"]}}</td>\n        <td>{{this[\"LB.HM40811.CN\"]}}</td>\n        <td>{{this[\"LB.HM40810.CN\"]}}</td>\n      </tr>\n      {{/each msg.payload}}\n    </tbody>\n  </table>\n</body>","output":"str","x":940,"y":1660,"wires":[["3aa0245fb8a7805f","be1dc158f4b5f12d"]]},{"id":"be1dc158f4b5f12d","type":"debug","z":"56fc7d74926adf91","name":"debug 1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1160,"y":1720,"wires":[]},{"id":"31f1719a56a83c26","type":"inject","z":"56fc7d74926adf91","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"SELECT * FROM Allgemein","x":440,"y":1720,"wires":[["6f98e7b14ffbc67d"]]},{"id":"5ca7db8ffd6c58ac","type":"sqlitedb","db":"/table-data/sqlite","mode":"RO"}]

localhost:1880/table-data:

image
image1912Ă—806 36.4 KB

2

Given data in msg.payload:

[ {"value":"foo"},{"value":"bar"},{"value":"baz"} ]

your template would be:

<tbody>
  {{#payload}}
      <tr>
        <td>{{value}}</td>
      </tr>
  {{/payload}}
</tbody>

Try applying your field names to the template as shown ↑

More reading here: mustache(5) - Logic-less templates. (as linked in the built in help of the template node)

1 Like
3

Am I right in thinking that <body> .. </body> should not be present in the template?

4

This particular example, the op is not using dashboard (has created an endpoint) and so needs to provide the whole kit and caboodle from html to /html

5

Further to Steve's points
Mustache property names can not have periods in property names.
I would change the sql query to rename the properties
e.g.

SELECT 
  _dateLocal_, 
  _dateUTC_ ,
  _timePeriod_,
  LB.RS1.CN AS LBRS1CN,
  LB.RS2.CN AS LBRS2CN,
  LB.RS3.CN AS LBRS3CN,
  LB.HM40811.CN AS LBHM40811CN,
  LB.HM40810.CN AS LBHM40810CN
FROM 
  Allgemein

And the template would be


  <table style="width:100%" id="myTable">
    <thead>
      <tr>
        <th>_dateUTC_</th>
        <th>_dateLocal_</th>
        <th>_timePeriod_</th>
        <th>LB.RS1.CN</th>
        <th>LB.RS2.CN</th>
        <th>LB.RS3.CN</th>
        <th>LB.HM40811.CN</th>
        <th>LB.HM40810.CN</th>
      </tr>
    </thead>
    <tbody>
      {{#payload}}
      <tr>
        <td>{{_dateLocal_}}</td>
        <td>{{_dateUTC_}}</td>
        <td>{{_timePeriod_}}</td>
        <td>{{LBRS1CN}}</td>
        <td>{{LBRS2CN}}</td>
        <td>{{LBRS3CN}}</td>
        <td>{{LBHM40811CN}}</td>
        <td>{{LBHM40810CN}}</td>
      </tr>
      {{/payload}}
    </tbody>
  </table>

You can add body tags if you are not using ui-template node
Finally the sql node needs to be between the http in and response node.
@Colin I believe that is only ui-template.

1 Like
6

True, I had missed that.

7

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