# Sqlite select query not returning data

**URL:** <https://discourse.nodered.org/t/sqlite-select-query-not-returning-data/16742>\
**Category:** General\
**Created:** [15 October 2019 18:44 UTC](https://discourse.nodered.org/t/sqlite-select-query-not-returning-data/16742 "2019-10-15T18:44:22Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![Petr](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/petr/32/6413_2.png) [@Petr](https://discourse.nodered.org/u/Petr)\
**Post date:** [15 October 2019 18:44 UTC](https://discourse.nodered.org/t/sqlite-select-query-not-returning-data/16742/1 "2019-10-15T18:44:22Z")

</div>

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:

```auto
[{"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.

```auto
[{"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.

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [15 October 2019 18:57 UTC](https://discourse.nodered.org/t/sqlite-select-query-not-returning-data/16742/2 "2019-10-15T18:57:23Z")

</div>

> [@Petr](#):
>
> I have a relatively big SQL SELECT query (44 lines).

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [15 October 2019 19:24 UTC](https://discourse.nodered.org/t/sqlite-select-query-not-returning-data/16742/3 "2019-10-15T19:24:33Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Petr](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/petr/32/6413_2.png) [@Petr](https://discourse.nodered.org/u/Petr)\
**Post date:** [15 October 2019 19:40 UTC](https://discourse.nodered.org/t/sqlite-select-query-not-returning-data/16742/4 "2019-10-15T19:40:19Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Petr](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/petr/32/6413_2.png) [@Petr](https://discourse.nodered.org/u/Petr)\
**Post date:** [15 October 2019 19:50 UTC](https://discourse.nodered.org/t/sqlite-select-query-not-returning-data/16742/5 "2019-10-15T19:50:18Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [16 October 2019 01:24 UTC](https://discourse.nodered.org/t/sqlite-select-query-not-returning-data/16742/6 "2019-10-16T01:24:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Petr](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/petr/32/6413_2.png) [@Petr](https://discourse.nodered.org/u/Petr)\
**Post date:** [16 October 2019 11:56 UTC](https://discourse.nodered.org/t/sqlite-select-query-not-returning-data/16742/7 "2019-10-16T11:56:11Z")

</div>

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

```auto
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](https://www.maxbox.cz/nextcloud/index.php/s/fpcMqWLMY3AyRG5)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [17 October 2019 14:37 UTC](https://discourse.nodered.org/t/sqlite-select-query-not-returning-data/16742/8 "2019-10-17T14:37:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Petr](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/petr/32/6413_2.png) [@Petr](https://discourse.nodered.org/u/Petr)\
**Post date:** [17 October 2019 21:30 UTC](https://discourse.nodered.org/t/sqlite-select-query-not-returning-data/16742/9 "2019-10-17T21:30:28Z")

</div>

No problem, thanks for taking care anyway...

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [18 October 2019 10:39 UTC](https://discourse.nodered.org/t/sqlite-select-query-not-returning-data/16742/10 "2019-10-18T10:39:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Petr](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/petr/32/6413_2.png) [@Petr](https://discourse.nodered.org/u/Petr)\
**Post date:** [19 October 2019 22:21 UTC](https://discourse.nodered.org/t/sqlite-select-query-not-returning-data/16742/11 "2019-10-19T22:21:03Z")

</div>

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....

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [19 October 2019 22:43 UTC](https://discourse.nodered.org/t/sqlite-select-query-not-returning-data/16742/12 "2019-10-19T22:43:49Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Petr](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/petr/32/6413_2.png) [@Petr](https://discourse.nodered.org/u/Petr)\
**Post date:** [20 October 2019 00:30 UTC](https://discourse.nodered.org/t/sqlite-select-query-not-returning-data/16742/13 "2019-10-20T00:30:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [20 October 2019 00:59 UTC](https://discourse.nodered.org/t/sqlite-select-query-not-returning-data/16742/14 "2019-10-20T00:59:23Z")

</div>

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.