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
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.
So are you able to load it at all ?
If I try to load an incorrect file I get
If I point at the file correctly I get
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 ?
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.
This is on raspbian stretch. You are on a Mac, correct?
so it's not loading in bash either ? and yes on a Mac. Can do Pi tomorrow.
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.
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-sqlite3was compiled.** Version mismatches can lead to unpredictable behavior
and I think they are currently on SQLite V3.24.0.
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
here is my test flow
[{"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"}]
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:
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.
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:
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
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)
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.
Does that imply that the db connection remains open between the 2 injects? I was under the impression that it was closed
Yes the connection should stay open. (until stopped/redeployed)
managed to just try it on a pi - recompiled that half.c file to half.sqlext - and the flow works.
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.
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?
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
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?
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy
