Error after selecting data from SQLITE

#1

Hello,

as written in the Topic, i got a Little Problem with getting data from my SQLITE. after I trie to get the data from the databank, there is this error:

>  09.7.2018, 10:49:27node: Objekt für Stückzahlfunction : (error)
> "TypeError: Cannot read property '0' of undefined"

I know it has sth. to do with my function node, but I think the Problem might be the "join"-node.

[{"id":"b92e4eb.82d3f3","type":"ui_dropdown","z":"8661c529.80bdb","name":"Fehler","label":"","place":"auswählen","group":"96a586f5.cb3478","order":1,"width":"8","height":"1","passthru":true,"options":[{"label":"Fehler_Kontaktierung [blau]","value":"ktfehlertd","type":"str"},{"label":"Fehler_Kurzschluss [grün]","value":"ksfehlertd","type":"str"},{"label":"Fehler_Schirm [gelb]","value":"scfehlertd","type":"str"},{"label":"heutige Gesamtfehler","value":"both","type":"str"},{"label":"Gesamtfehleranzahl Kontaktierung [blau]","value":"ktfehlerall","type":"str"},{"label":"Gesamtfehleranzahl Kurzschluss [grün]","value":"ksfehlerall","type":"str"},{"label":"Gesamtfehleranzahl Schirm [gelb]","value":"scfehlerall","type":"str"},{"label":"Gesamtfehler","value":"both_all","type":"str"}],"payload":"","topic":"fault","x":610,"y":880,"wires":[["f3b38f98.468a88","6dcdb612.bbe0b"]]},{"id":"6dcdb612.bbe0b","type":"function","z":"8661c529.80bdb","name":"SQL","func":"\nvar p_30d  = 1000*60*60*24*30 ; //30 Days\nvar p_7d  = 1000*60*60*24*7 ; //7 Days\nvar p_1d   =  1000*60*60*24 ; // 1 Day\nvar d = new Date();\nvar current = d.getTime();\nvar today0h = d.setHours(0,0,0,0);\nvar day = d.getDay();\nvar monday0h = today0h - (day + (day === 0 ? -6:1)) * p_1d;\nvar fromdate = 0;\nvar enddate = 0;\nvar sql = [];\nvar sourcelist = [];\nvar aggrlist = [];\nvar title = \"\";\n\n\n\nif (msg.topic == \"fault\")\n{\n    if (msg.payload == \"ksfehlertd\" || msg.payload == \"ktfehlertd\" || msg.payload == \"scfehlertd\" || msg.payload == \"both\")\n    {\n        fromdate = current - p_1d;\n        enddate = current;\n        sql.push({ topic: \"SELECT sum(value) AS value FROM arbeitswerte WHERE device='1' AND sensor='Kontaktierungsfehler' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate });\n        sql.push({ topic: \"SELECT sum(value) AS value FROM arbeitswerte WHERE device='1' AND sensor='Kurzschlussfehler' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate });\n        sql.push({ topic: \"SELECT sum(value) AS value FROM arbeitswerte WHERE device='1' AND sensor='Schirmfehler' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate });\n        sql[sql.length-1].complete=true;\n    }\n    else if (msg.payload == \"ksfehlerall\" || msg.payload == \"ktfehlerall\" || msg.payload == \"scfehlerall\" || msg.payload == \"both_all\")\n    {\n        sql.push({ topic: \"SELECT sum(value) AS value FROM arbeitswerte WHERE device='1' AND sensor='Kontaktierungsfehler'\"});\n        sql.push({ topic: \"SELECT sum(value) AS value FROM arbeitswerte WHERE device='1' AND sensor='Kurzschlussfehler'\"});\n        sql.push({ topic: \"SELECT sum(value) AS value FROM arbeitswerte WHERE device='1' AND sensor='Schirmfehler'\"});\n        sql[sql.length-1].complete=true;\n    }\n    else \n    {\n        return 0;\n    }\n}\n\nreturn [sql];\n\n","outputs":1,"noerr":0,"x":350,"y":940,"wires":[["b204fe30.0e8098"]]},{"id":"b204fe30.0e8098","type":"sqlite","z":"8661c529.80bdb","mydb":"4d5d0557.c15f64","sqlquery":"msg.topic","sql":"","name":"DB","x":490,"y":940,"wires":[["4c7096cf.55f9d8"]]},{"id":"4c7096cf.55f9d8","type":"join","z":"8661c529.80bdb","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"3","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":610,"y":940,"wires":[["5675e3d4.bb2e34","16c9a54c.d4b78b"]]},{"id":"5675e3d4.bb2e34","type":"function","z":"8661c529.80bdb","name":"Objekt für Stückzahl","func":"msg.ksfehler = Math.floor(msg.payload[0][0].value);\nmsg.ktfehler = Math.floor(msg.payload[1][0].value);\nmsg.scfehler = Math.floor(msg.payload[2][0].value);\nvar f = global.get('fault')||0;\nvar m;\n\nif (f == \"ksfehlertd\" || f == \"ktfehlertd\" || f == \"scfehlertd\" || f == \"both\")\n    {    \n        // laden des Objekts\n        m = {\"series\":[\"heute\"],\"data\":[[msg.ktfehler,msg.ksfehler,msg.scfehler]],\"labels\":[\"Kontaktierungsfehler\",\"Kurzschluss\",\"Schirmfehler\"]};\n    }\nelse if (f == \"ksfehlerall\" || f == \"ktfehlerall\" || f == \"scfehlerall\" || f == \"both_all\")\n    {\n    // laden des Objekts\n        m = {\"series\":[\"gesamt\"],\"data\":[[msg.ktfehler,msg.ksfehler,msg.scfehler]],\"labels\":[\"Kontaktierungsfehler\",\"Kurzschluss\",\"Schirmfehler\"]};\n    }\n       \n\n\n\n\n\n\n\n\nreturn {payload:[m]};","outputs":1,"noerr":0,"x":930,"y":840,"wires":[["8e01b4e2.109f68"]]},{"id":"8e01b4e2.109f68","type":"ui_chart","z":"8661c529.80bdb","name":"Chart","group":"96a586f5.cb3478","order":3,"width":0,"height":0,"label":"","chartType":"bar","legend":"false","xformat":"dd HH:mm","interpolate":"linear","nodata":"no data","dot":false,"ymin":"","ymax":"","removeOlder":"1","removeOlderPoints":"","removeOlderUnit":"604800","cutout":"","useOneColor":false,"colors":["#1f77b4","#2ca02c","#ffff00","#ffff80","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":true,"x":1170,"y":840,"wires":[[],[]]},{"id":"96a586f5.cb3478","type":"ui_group","z":"","name":"Fehler","tab":"ce934baa.a5951","disp":true,"width":"24","collapse":true},{"id":"4d5d0557.c15f64","type":"sqlitedb","z":"","db":"/home/pi/sqlite/nodered"},{"id":"ce934baa.a5951","type":"ui_tab","z":"","name":"Variante 1","icon":"business","order":1}]
#2

Put a debug node on the output of the join (and change the output to complete msg object) and see what it gives you.

you could also add a catch node connected to a debug node for more info

#3

What is the join doing ? The query will return an array anyway - so you are creating and array of an array... Is that what you really want ?

#4

@zenofmud
SELECT sum(value) AS value FROM arbeitswerte WHERE device='1' AND sensor='Schirmfehler' : msg : Object
object
topic: "SELECT sum(value) AS value FROM arbeitswerte WHERE device='1' AND sensor='Schirmfehler'"
_msgid: "642d2e80.d30b8"
payload: array[3]
0: array[1]
0: object
value: 11
1: array[1]
0: object
value: 3
2: array[1]
0: object
value: 4

But the interesting Thing is that this error don t come every time..

@dceejay
Yes I Need the join node, otherwise i can t handle the data in my function node..

#5

SELECT sum(value) AS value FROM arbeitswerte WHERE device='1' AND sensor='Schirmfehler' : msg : Object
object
topic: "SELECT sum(value) AS value FROM arbeitswerte WHERE device='1' AND sensor='Schirmfehler'"
_msgid: "8d6dffa.416d7"
payload: array[2]
0: array[1]
0: object
value: 11
1: array[1]
0: object
value: 4

Picture

Unbenannt

this is the full debug if the error Comes, as you can see one Array is missing but i have no idea why.

#6

Well if the where clause of the sql doesn't find anything, it will return a empty array. So you need to test to see if you got data back from the request before trying to process it.