# SQLite question

**URL:** <https://discourse.nodered.org/t/sqlite-question/2434>\
**Category:** General\
**Created:** [21 August 2018 00:08 UTC](https://discourse.nodered.org/t/sqlite-question/2434 "2018-08-21T00:08:31Z")\
**Posts on this page:** 20\
**Page:** 2

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [22 August 2018 13:22 UTC](https://discourse.nodered.org/t/sqlite-question/2434/21 "2018-08-22T13:22:08Z")

</div>

thanks for this. I updated to 0.3.3 and saw your changes to sqlite.js.

my tests indicate that the extension is not loading as I am unable to get results from the select statement that relies on code in the extension.

Same error in node-red as I see in the console log:  
22 Aug 09:20:27 - [error] [sqlite:sqlite storage] Error: SQLITE\_ERROR: no such function: acos

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [22 August 2018 13:49 UTC](https://discourse.nodered.org/t/sqlite-question/2434/22 "2018-08-22T13:49:15Z")

</div>

More testing on 0.3.3.  
I introduced an error in the path of the extension to which I set msg.extension. When I do I dont get any errors in the flow wrt loading the extension, but I do get the error on the subsequent select statement.

If I introduce that same error in the path in my .sqliterc file, I get 2 errors, the first reflecting the failure to load the extension, the 2nd is the expected select error:

pi@rpiNCTest:~ $ sqlite3 nauticloud.db "SELECT id, (6371000 \* acos ( cos ( radians(42.48) ) \* cos ( radians ( latitude ) ) \* cos( radians( longitude ) - radians(-70.8) ) + sin ( radians(42.48) ) \* sin( radians( latitude ) ) ) ) AS distance, ssid from wifilog order by distance;"  
-- Loading resources from /home/pi/.sqliterc  
Error: /usr/lib/arm-linux-gnueabihf/modx\_spatialite.so: cannot open shared object file: No such file or directory  
Error: no such function: acos

So I am wondering what is happening with the call to .loadExtension, since there seems to be error handling for any errors that are thrown by this call, but introducing a known error in the msg.extension path does not seem to generate any error message.

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [22 August 2018 21:49 UTC](https://discourse.nodered.org/t/sqlite-question/2434/23 "2018-08-22T21:49:39Z")

</div>

So are you able to load it at all ?  
If I try to load an incorrect file I get  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/8/85489319a1bae2efcdd05618679a7e66238ce3a6.png)

If I point at the file correctly I get  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/4/49dbf53b0aa5bef3765897e0f11162bc923b9e19.png)

So it certainly generates errors.

If you get it to load (no errors) then are there any "simple" functions you can call to check it's even loaded ?

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [22 August 2018 21:54 UTC](https://discourse.nodered.org/t/sqlite-question/2434/24 "2018-08-22T21:54:11Z")

</div>

I dont think I am able to get the extension to load at all, which is why I tried to see what the effect of using a bogus path would be (both in the node and at command line (bash). I see an error when in bash, but in node-red nothing.

The sql statement that I am running after connecting uses trigonometric functions (like acos) which are present when the extension is loaded and absent in native sqlite3. The error I see in node-red is 'Error: SQLITE\_ERROR: no such function: acos'.

I have tried a couple of different permutations of the extension name (path, no path, file name, file name with partial extension, file name with full extension) but I dont see any log errors or any success in using these trig functions.

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [22 August 2018 21:58 UTC](https://discourse.nodered.org/t/sqlite-question/2434/25 "2018-08-22T21:58:28Z")

</div>

This is on raspbian stretch. You are on a Mac, correct?

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [22 August 2018 22:00 UTC](https://discourse.nodered.org/t/sqlite-question/2434/26 "2018-08-22T22:00:42Z")

</div>

so it's not loading in bash either ? and yes on a Mac. Can do Pi tomorrow.

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [22 August 2018 22:03 UTC](https://discourse.nodered.org/t/sqlite-question/2434/27 "2018-08-22T22:03:24Z")

</div>

It _does_ load in bash shell, thus my dismay and search for some better error logging as well as the test with a bogus path to try to force an error to appear.

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [22 August 2018 22:11 UTC](https://discourse.nodered.org/t/sqlite-question/2434/28 "2018-08-22T22:11:47Z")

</div>

hmm - the docs for sqlite3 library we use say...

> Note: Make sure that the extensions you load are compiled or linked against the same version as `node-sqlite3` was compiled.\*\* Version mismatches can lead to unpredictable behavior

and I think they are currently on SQLite V3.24.0.

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [22 August 2018 22:50 UTC](https://discourse.nodered.org/t/sqlite-question/2434/29 "2018-08-22T22:50:57Z")

</div>

Hi - OK - have pushed 0.3.4 version . I have managed to compile the "half" example extension on Mac and it now loads and executes as it should

extension code from here - [https://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions](https://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions)

here is my test flow

```auto
[{"id":"451f4d29.b74944","type":"inject","z":"ae93e99e.884c98","name":"","topic":"","payload":"/tmp/half.dylib","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":860,"wires":[["3c296e39.a4f9a2"]]},{"id":"3c296e39.a4f9a2","type":"change","z":"ae93e99e.884c98","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"extension","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":370,"y":860,"wires":[["72fa2466.af867c","fa8c6180.6f206"]]},{"id":"72fa2466.af867c","type":"sqlite","z":"ae93e99e.884c98","mydb":"d6ed74df.89f1e8","sqlquery":"msg.topic","sql":"","name":"","x":510,"y":900,"wires":[["61e7ac4d.2c4fa4"]]},{"id":"fa8c6180.6f206","type":"debug","z":"ae93e99e.884c98","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":530,"y":840,"wires":[]},{"id":"5b5f861c.ff1f28","type":"inject","z":"ae93e99e.884c98","name":"","topic":"select half(10);","payload":"/tmp/half","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":170,"y":900,"wires":[["3c296e39.a4f9a2"]]},{"id":"f66aa9a1.ff2f98","type":"inject","z":"ae93e99e.884c98","name":"","topic":"select half(7);","payload":"","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":940,"wires":[["72fa2466.af867c"]]},{"id":"61e7ac4d.2c4fa4","type":"debug","z":"ae93e99e.884c98","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":710,"y":900,"wires":[]},{"id":"d6ed74df.89f1e8","type":"sqlitedb","z":"","db":"/tmp/testdb","mode":"RWC"}]

```

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [23 August 2018 11:28 UTC](https://discourse.nodered.org/t/sqlite-question/2434/30 "2018-08-23T11:28:37Z")

</div>

Thanks for this.  
I updated to 0.3.4 and adapted your flow to be able to test the extension I am trying to use by substituting the path to mod\_spatialite in the extension property.

This is what I see in debug after manually injecting each inject node in order from top to bottom:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/b/b8d83bb939d6a1374a7ea2f72e261c6ecad1d5ca.png)

This is the flow:  
[{"id":"ab61e92.3c14c18","type":"inject","z":"fbf9f6cb.4d2968","name":"","topic":"","payload":"/usr/lib/arm-linux-gnueabihf/mod\_spatialite","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":90,"y":180,"wires":[["3c0eacc1.331fc4"]]},{"id":"3c0eacc1.331fc4","type":"change","z":"fbf9f6cb.4d2968","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"extension","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":310,"y":180,"wires":[["5017d7c4.8bf7d8","8f635cf8.daa2f"]]},{"id":"5017d7c4.8bf7d8","type":"sqlite","z":"fbf9f6cb.4d2968","mydb":"e800f188.d763b","sqlquery":"msg.topic","sql":"","name":"","x":470,"y":220,"wires":[["ddb66aae.85fc38"]]},{"id":"8f635cf8.daa2f","type":"debug","z":"fbf9f6cb.4d2968","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":490,"y":160,"wires":},{"id":"f33b584d.fdf588","type":"inject","z":"fbf9f6cb.4d2968","name":"","topic":"select LongLatToDMS(-70,43);","payload":"/usr/lib/arm-linux-gnueabihf/mod\_spatialite","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":80,"y":220,"wires":[["3c0eacc1.331fc4"]]},{"id":"3a4c4624.59875a","type":"inject","z":"fbf9f6cb.4d2968","name":"","topic":"select LongLatToDMS(-75,28);","payload":"","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":170,"y":260,"wires":[["5017d7c4.8bf7d8"]]},{"id":"ddb66aae.85fc38","type":"debug","z":"fbf9f6cb.4d2968","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":670,"y":220,"wires":},{"id":"e800f188.d763b","type":"sqlitedb","z":"","db":"/tmp/testdb","mode":"RWC"}]

If I replicate the process at the command line this is what it looks like:

pi@rpiNCTest:~ mv .sqliterc sqlite\_ignore pi@rpiNCTest:~ sqlite3 /tmp/testdb  
SQLite version 3.16.2 2017-01-06 16:32:41  
Enter ".help" for usage hints.  
sqlite\> select LongLatToDMS(-75,28);  
Error: no such function: LongLatToDMS  
sqlite\> .quit  
pi@rpiNCTest:~ mv sqlite\_ignore .sqliterc pi@rpiNCTest:~ sqlite3 /tmp/testdb  
-- Loading resources from /home/pi/.sqliterc  
SQLite version 3.16.2 2017-01-06 16:32:41  
Enter ".help" for usage hints.  
sqlite\> select LongLatToDMS(-75,28);  
28°00′00″N 075°00′00″W  
sqlite\> .quit  
pi@rpiNCTest:~ $ more .sqliterc  
.load /usr/lib/arm-linux-gnueabihf/mod\_spatialite

pi@rpiNCTest:~ $

So it seems that the extension is compatible with sqlite (compiled from same version).

Ill try to compile the same extension that you did to see if that provides any more clues.

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [23 August 2018 12:37 UTC](https://discourse.nodered.org/t/sqlite-question/2434/31 "2018-08-23T12:37:49Z")

</div>

OK Managed to compiled the half.sqlext and confirmed that it works at command line/console.

I think I have properly updated your flow to my system:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/6/68087ebdddd20a0b15f6460dfbbd2178d9c1b64e.png)

Unfortunately the results are the same.

One thing I noted was in your test flow the middle inject node has /tmp/half as msg.payload, which is then subsequently moved to msg.extension. What is '  
/tmp/half'?

--Update-- OK I see that /tmp/half is just referring to the extension without the file extension

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [23 August 2018 14:33 UTC](https://discourse.nodered.org/t/sqlite-question/2434/32 "2018-08-23T14:33:31Z")

</div>

Im wondering if this is a problem with the version of SQLite3 that I am using (3.16.2) vs what you are using (3.24.x)?  
--Update--  
I updated the /etc/apt/sources.list to point to the 'testing' rather than 'stretch' archive. then ran apt-get update and apt-get install sqlite3. This updated my sqlite3 to 3.24.0 rather than 3.16.2. I also ran a 'apt-get install libsqlite3-mod-spatialite' to update the extension library.  
Still getting the same error in node-red flow (Error: SQLITE\_MISUSE: library routine called out of sequence)

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [23 August 2018 19:47 UTC](https://discourse.nodered.org/t/sqlite-question/2434/33 "2018-08-23T19:47:38Z")

</div>

The out of sequence thing may be timing - try making sure they are two separate injects - one to add the extension and then one to do the select... (though the fix I added last night was what made being able to do both work for me....)

Not sure what else I can suggest at this point.

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [23 August 2018 19:55 UTC](https://discourse.nodered.org/t/sqlite-question/2434/34 "2018-08-23T19:55:56Z")

</div>

Does that imply that the db connection remains open between the 2 injects? I was under the impression that it was closed

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [23 August 2018 20:58 UTC](https://discourse.nodered.org/t/sqlite-question/2434/35 "2018-08-23T20:58:16Z")

</div>

Yes the connection should stay open. (until stopped/redeployed)

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [23 August 2018 21:46 UTC](https://discourse.nodered.org/t/sqlite-question/2434/36 "2018-08-23T21:46:44Z")

</div>

managed to just try it on a pi - recompiled that half.c file to half.sqlext - and the flow works.

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [23 August 2018 22:15 UTC](https://discourse.nodered.org/t/sqlite-question/2434/37 "2018-08-23T22:15:34Z")

</div>

I just rebooted and now the flow works. This is after adjusting the apt sources.list file to allow the latest 'testing' version to be used (rather than 'stable').  
I also updated the extension and did a refresh of 'npm install sqlite3 --unsafe-perm' and npm install 'node-red-node-sqlite'. Then the reboot.

Now it works.

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [24 August 2018 15:08 UTC](https://discourse.nodered.org/t/sqlite-question/2434/38 "2018-08-24T15:08:01Z")

</div>

How is it possible to know the version of sqlite3 against which ones version of node-sqlite3 was compiled? I am able to know this from npm:  
npm list sqlite3  
/home/pi  
└── sqlite3@4.0.1

But how can I know the version of sqlite3 that version 4.0.1 was compiled with?

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [24 August 2018 17:53 UTC](https://discourse.nodered.org/t/sqlite-question/2434/39 "2018-08-24T17:53:45Z")

</div>

when I installed the node-red-node-sqlite node it drags in sqlite3 and compiled it for me - this generated the necessary .h files in my `.node-red/node_modules/sqlite3/build/Release/obj/gen/sqlite-autoconf-3240000` directory

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [24 August 2018 20:37 UTC](https://discourse.nodered.org/t/sqlite-question/2434/40 "2018-08-24T20:37:42Z")

</div>

I was trying to reproduce my configuration on one pi where you helped me to get it working onto a 2nd pi so I could try to slim down the number of things that need to be installed to get it working.

Now it appears I have done something which has caused both pi's to break. Each time I run a flow that attempts to load an extension I get a 'segmentation fault' in the process in which nodered is embedded.

Did anything change between .0.3.4 and 0.3.5 that might be contributing to these crashes?

[Previous page](https://discourse.nodered.org/t/sqlite-question/2434.md?page=1)

[Next page](https://discourse.nodered.org/t/sqlite-question/2434.md?page=3)