I have a relatively big SQL SELECT query (44 lines).
On the locally deployed sqlite database query works.
On the server query works as well (server is the machine, where node-red runs + database file is deployed).
From the node-red the query returns empty [ empty ]
This is the flow I use:
[{"id":"70c58138.e674f","type":"debug","z":"6bc007d7.6ef94","name":"","active":true,"console":"false","complete":"false","x":870,"y":520,"wires":[]},{"id":"103432cd.1d8ff5","type":"inject","z":"6bc007d7.6ef94","name":"Timestamp","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":320,"y":400,"wires":[["b91d3c65.9f12b8"]]},{"id":"b91d3c65.9f12b8","type":"template","z":"6bc007d7.6ef94","name":"Format query 1","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"create index if not exists power_jd on power (julianday(timestamp), total_kwh);\n\nwith pwr (timestamp, reading, ratetoprior) as\n (\n select julianday(timestamp),\n total_kwh,\n (select (c.total_kwh - p.total_kwh) / (julianday(c.timestamp) - julianday(p.timestamp))\n from power as p\n where julianday(p.timestamp) < julianday(c.timestamp)\n order by julianday(p.timestamp) desc\n limit 1)\n from power as c\n order by julianday(timestamp)\n ),\n periods (timestamp) as\n (\n select julianday(date(min(timestamp), '-1 day') || ' 23:59:59.999')\n from pwr\n union all\n select julianday(datetime(timestamp, '+1 day'))\n from periods\n where timestamp < (select max(timestamp) from pwr)\n ),\n readings (timestamp, reading) as\n (\n select timestamp,\n (select reading - (b.timestamp - p.timestamp) * ratetoprior\n from pwr as b\n where b.timestamp >= p.timestamp\n limit 1) as reading\n from periods as p\n where timestamp between (select min(timestamp) from pwr)\n and (select max(timestamp) from pwr)\n ),\n used (timestamp, kwh) as\n (\n select timestamp,\n reading - lag(reading) over ()\n from readings\n )\n select datetime(timestamp),\n kwh\n from used\n where kwh is not null;","output":"str","x":520,"y":400,"wires":[["70c58138.e674f","3efdfe04.5461e2"]]},{"id":"3efdfe04.5461e2","type":"sqlite","z":"6bc007d7.6ef94","mydb":"4375201f.2a2928","sqlquery":"msg.topic","sql":"","name":"power.qlite","x":780,"y":420,"wires":[["70c58138.e674f"]]},{"id":"4375201f.2a2928","type":"sqlitedb","z":"","db":"/root/.node-red/sqlite/power.sqlite","mode":"RWC"}]
Part of the query is working when executed in the same flow.
[{"id":"70c58138.e674f","type":"debug","z":"6bc007d7.6ef94","name":"","active":true,"console":"false","complete":"false","x":890,"y":480,"wires":[]},{"id":"103432cd.1d8ff5","type":"inject","z":"6bc007d7.6ef94","name":"Timestamp","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":300,"y":400,"wires":[["b91d3c65.9f12b8"]]},{"id":"b91d3c65.9f12b8","type":"template","z":"6bc007d7.6ef94","name":"Format query 1","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":" select julianday(timestamp),\n total_kwh,\n (select (c.total_kwh - p.total_kwh) / (julianday(c.timestamp) - julianday(p.timestamp))\n from power as p\n where julianday(p.timestamp) < julianday(c.timestamp)\n order by julianday(p.timestamp) desc\n limit 1)\n from power as c\n order by julianday(timestamp)\n","output":"str","x":500,"y":400,"wires":[["70c58138.e674f","3efdfe04.5461e2"]]},{"id":"3efdfe04.5461e2","type":"sqlite","z":"6bc007d7.6ef94","mydb":"4375201f.2a2928","sqlquery":"msg.topic","sql":"","name":"power.qlite","x":750,"y":400,"wires":[["70c58138.e674f"]]},{"id":"4375201f.2a2928","type":"sqlitedb","z":"","db":"/root/.node-red/sqlite/power.sqlite","mode":"RWC"}]
I can not figure out where the problem is located.