I have quite complex SQL query.
From the terminal on the server everything is OK with the command
sqlite3 power.sqlite ".read sql.txt" (my query is stored in the sql.txt file).
Unfortunatelly from the NR the result of the query is: msg.payload : array[0] [ empty ]
[
{
"id": "ce0dde258edf86ae",
"type": "sqlite",
"z": "3ec042cc2b88ead8",
"mydb": "a33f828b.b04e2",
"sqlquery": "msg.topic",
"sql": "create index if not exists power_jd on power (julianday(timestamp), total_kwh); with pwr(timestamp, reading, ratetoprior) as ( select julianday(timestamp), total_kwh, (select(c.total_kwh - p.total_kwh) / (julianday(c.timestamp) - julianday(p.timestamp)) from power as p where julianday(p.timestamp) < julianday(c.timestamp) order by julianday(p.timestamp) desc limit 1) from power as c order by julianday(timestamp) ), periods(timestamp) as ( select julianday(strftime('%Y-%m-%d %H', (min(timestamp)), '-1 month') || ':00:00.000') from pwr union all select julianday(datetime(timestamp, '+1 month')) from periods where timestamp < (select max(timestamp) from pwr) ), readings(timestamp, reading) as ( select timestamp, (select reading - (b.timestamp - p.timestamp) * ratetoprior from pwr as b where b.timestamp >= p.timestamp limit 1) as reading from periods as p where timestamp between(select min(timestamp) from pwr) and(select max(timestamp) from pwr) ), used(timestamp, kwh) as ( select timestamp, reading - lag(reading) over() from readings ) select strftime('%m.%Y', timestamp) as \"mm-yyy\", cast(kwh as int) as \"kwh\" from used where kwh is not null",
"name": "",
"x": 580,
"y": 1140,
"wires": [
[
"f0ee7a20a2b3b935"
]
]
},
{
"id": "247626382557a383",
"type": "function",
"z": "3ec042cc2b88ead8",
"name": "function 4",
"func": "msg.topic = String.raw`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 ),\nperiods(timestamp) as\n (\n select julianday(strftime('%Y-%m-%d %H', (min(timestamp)), '-1 month') || ':00:00.000')\n from pwr\n union all\n select julianday(datetime(timestamp, '+1 month'))\n from periods\n where timestamp < (select max(timestamp) from pwr)\n ),\nreadings(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)\nand(select max(timestamp) from pwr)\n ),\nused(timestamp, kwh) as\n (\n select timestamp,\n reading - lag(reading) over()\n from readings\n )\n select strftime('%m.%Y', timestamp) as \"mm-yyy\",\n cast(kwh as int) as \"kwh\"\n from used\n where kwh is not null`;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 340,
"y": 1140,
"wires": [
[
"ce0dde258edf86ae"
]
]
},
{
"id": "07691c24cdc0a11a",
"type": "inject",
"z": "3ec042cc2b88ead8",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 140,
"y": 1140,
"wires": [
[
"247626382557a383"
]
]
},
{
"id": "f0ee7a20a2b3b935",
"type": "debug",
"z": "3ec042cc2b88ead8",
"name": "debug 5",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 800,
"y": 1140,
"wires": []
},
{
"id": "a33f828b.b04e2",
"type": "sqlitedb",
"db": "/root/sqlite-data/power.sqlite",
"mode": "RWC"
}
]
My setup:
Sqlite 3.34.1
NR 3.0.2
node-red-node-sqlite
link to download database for testing: power.sqlite
