Node-RED

Sqlite select query not returning data

General
#1

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.

#2

Respect for being able to read that! Could you post the query to me as a PM?

#3

have you put in a catch nde connected to a debug node (display complete msg) to see if anytheing is reported?

What version of nr and node.Js?

Can you provide your schema?

#4

Both queries (working and not-working) are included in the "template node" in the flow posted above.

#5

nr 0.19.5
node.js v10.16.3
catch node doesn't return any error/msg (I thing because query just return nothing)

What schema do you mean?

#6

The database schema - the create table's syntax to show what al the columns are

#7

There is not a problem in the database schema, AFIK. The table is really simple

CREATE TABLE IF NOT EXISTS "POWER" ( "ID" INTEGER PRIMARY KEY AUTOINCREMENT, "TIMESTAMP" DATETIME NOT NULL DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime')), "TOTAL_KWH" NUMERIC);

I can get the data with the same SQL select query using the terminal and using the DB Browser For SQLite on the production machine.
Even partial SQL select (as I mentioned in my previous post) is working.

You can find the database file, DB Browser project file and sql query file
here

#8

Sorry I haven’t been able to get back to this. (Family issues and a wind storm cleanup) I hope ll have time later today

#9

No problem, thanks for taking care anyway...

#10

Well you're going to kick yourself. Try removing the first line (the create index) and see what you get.

#11

Hmmm. I see. Query is returning data when first row is removed. But why it is not working with the first row?

When I run whole query (including the first row) from CLI and from DB browser for SQLite the query is working....

#12

I think that since you are sending the sql via the msg.topic, it ends processing at the ; at the end of the create index statement. So you can only send in one statenment at a time.

If you use the batch option, No result rows are returned.

#13

Do you think there is a way how to trigger to send the first statement separately before queriing second statement?

#14

Sure, send the first to the sql node then check the output to see it ran and if it did connect to another node to build the next sql statement and feel it to another SQLite node. Or you could put the SQLite node in a sub flow so you only have one instance of it.