View SQLite records show empty (but actually full)

Dashboard
1

Hi,

i have problem to display SQLite content on the dashboard.
It find all the content in the DB and shows it in the debug nodes. But It won't display it on the dashboard. What am i doing wrong here? I already tried several example flows. Like this one:

[{"id":"4ecdb4d3.e637ec","type":"tab","label":"Flow 4","disabled":false,"info":""},{"id":"640448d2.9babe8","type":"inject","z":"4ecdb4d3.e637ec","name":"show RECORDS","topic":"SELECT * FROM phone ORDER BY TIMESTAMP DESC LIMIT 100","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"onceDelay":"","x":190,"y":160,"wires":[["bbce3c8a.708ea"]]},{"id":"9adc2560.90bc08","type":"ui_template","z":"4ecdb4d3.e637ec","group":"559d2777.1dd768","name":"UI Table","order":1,"width":"","height":"","format":"<style>\n.table\n</style>\n<div class=\"table\">\n<table style=\"width:100%\">\n  <tr>\n    <th>TIMESTAMP</th> \n    <th>battery</th>\n\n  </tr>\n  <tr ng-repeat=\"x in msg.payload | limitTo:10\">\n    <td>{{msg.payload[$index].TIMESTAMP}}</td>\n    <td>{{msg.payload[$index].level}}</td>\n\n  </tr>\n</table>\n</div>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":700,"y":160,"wires":[["a46d01b4.1a8b3"]]},{"id":"bbce3c8a.708ea","type":"sqlite","z":"4ecdb4d3.e637ec","mydb":"e41aec91.18f5b","sqlquery":"msg.topic","sql":"","name":"","x":430,"y":160,"wires":[["9adc2560.90bc08","c67c7fcf.16708"]]},{"id":"c67c7fcf.16708","type":"debug","z":"4ecdb4d3.e637ec","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":590,"y":100,"wires":[]},{"id":"a46d01b4.1a8b3","type":"debug","z":"4ecdb4d3.e637ec","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":910,"y":160,"wires":[]},{"id":"559d2777.1dd768","type":"ui_group","z":"","name":"Sensors","tab":"d9609e7e.bfb89","order":1,"disp":true,"width":"12","collapse":false},{"id":"e41aec91.18f5b","type":"sqlitedb","z":"","db":"my.db","mode":"RWC"},{"id":"d9609e7e.bfb89","type":"ui_tab","z":"","name":"Home","icon":"dashboard"}]

image
image757Ă—212 2.23 KB

P.S.: I am using the RedMobile version of NodeRed

2

Hello ESteve
What are Your output in a debug node?
Beste regards
Brian

3

Try this

<style>
.table
</style>
<div class="table">
<table style="width:100%">
  <tr>
    <th>TIMESTAMP</th> 
    <th>SENSOR</th>

  </tr>
  <tr ng-repeat="row in msg.payload | limitTo:2">
   <td ng-repeat="item in row" >{{item}}</td>
 </tr>
</table>
</div>
4

Thanks you for your answers.

@Brian5600
Looks like this: image

@zenofmud
When i use your UI Table. I get this oputput:

image
image695Ă—245 3.63 KB

5

This should do it for you

<style>
.table
</style>
<div class="table">
<table style="width:100%">
  <tr>
    <th>TIMESTAMP</th> 
    <th>SENSOR</th>

  </tr>
  <tr ng-repeat="row in msg.payload | limitTo:2">
   <td>{{row.TIMESTAMP}}</td>
   <td>{{row.SENSOR}}</td>
 </tr>
</table>
</div>
6

really ? in the debug it looks like msg.payload.rows is the array, not msg.payload

7

@dceejay I think you are right. Not having the schema, I assumed (bad idea) that the rows would be returned to msg.payload.

@Brian5600 What is the schema of your database?
I would add a change node before the ui-table node to move msg.payload.rows to msg.payload

1 Like
8

I think You miss an s in rows.TIMESTAMP

So this might be
msg.payload.rows[$index].TIMESTAMP for data
I made a solution like this some years ago. I might find it later. But I am on work right now. I cant remember how to use $index
Or @zenofmud solution with moving the rows from rows to payload. But it might not be so nice, if it mix up with or overwrite other data in payload.

9

Or maybe change the ng-repeat from row in msg.payload to row in msg.payload.rows

10

Nope, the 'row' in this case is an index into the payload. I could have use 'x' or 'foo' as long as it matched the name used in the nr-repeat in the <tr...>
If you used <tr ng-repeat="foo in msg.payload | limitTo:2"> the the <td... would be
<td>{{foo.TIMESTAMP}}</td>

As for msg.payload, if he needs to keep the entire contents, in the change node, he could set another msg property - say msg.orig_payload to it and after the ui-table node, have another change node to move it back to msg.payload

11

Yes, it is close now, Pete Scargill made some things I used back in 2017 I think it was.

12

Oh, this way, thank You,
But then @dceejay is fully right with his answer :blush:

And yes about moving around payload, but still dont move more than nescessary. It only make confusion bigger, and search for errors more difficult.

Become <tr ng-repeat="foo in msg.payload.rows | limitTo:2">

1 Like
13

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