I am using node-red-node-sqlite to connect to a sqlite3 db. I need to load a sqlite extension prior to submitting a select.
When I do this at the command line, it seems to work ok:
select load_extension('mod_spatialite');
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 log order by distance;
I am getting this from nodered:
Error: SQLITE_ERROR: not authorized
The error seems to be in response to the ‘select load_extension() call.
I have tried to use msg.topic, fixed statement and prepared statement.
So Im wondering if:
-There might be a way to have the select load_extension automatically execute upon connect? From what I understand sqlite3 can accept a 2nd argument (after the db name) that is a SQL statement.
I haven't used load_extension but I wonder whether it is a path issue. Try specifying the full path and filename of the extension file in the load_extension call. Try it in the command line first. If you are using Windows then I don't know what you need to do with the backslash characters in the path. You may need someone else to comment on that.
We currently don't expose load_extension so it would need to be added.
How we do that needs a bit of thought, and input from you folk. Is it common to add more than one extension ? or is it only one at a time ? Do they tend to be statically loaded ? (ie once at start time) - or need to be added during run time?
I tried the full path on the command line and it seemed to work, so I tried it in node-red-node-sqlite as a fixed statement. This only resulted in the same 'not authorized' error.
This is on a raspberry pi
Looking at other comments, I did try to use the .load 'full-path-to-mod_spatialite' and it seemed to work. So I am considering using a .sqliterc file (which I believe permits the use of any of the dot commands) and will have to test.
I am hoping that node-red-node-sqlite will cause .sqliterc to be used and that a .load call therein will be successful.
So, Sorry to report that the node-red-node-sqlite does not appear to use the .sqliterc file.
I added a .sqliterc file with one line that uses the dot command .load [extension].
When I start sqlite3 command line, the .sqliterc file is read and I am able to execute a select statement that includes functions that are only available if the extension is loaded..
When I submit the same select statement in the node, an error is returned that indicates the extension is not loaded.
So if anyone has any ideas about how I might load an extension in a flow that uses node-red-sqlite-node I am eager to try. Otherwise I guess I will need to use the workaround.
Ok - so allowing it via a msg property probably makes sense rather than adding to the config.
If you could try something for me... find the file sqlite.js in node_modules/node-red-node-sqlite and edit it... around line 50 - add the if statement below
node.on("input", function(msg) {
if (msg.hasOwnProperty("extension")) {
node.mydbConfig.db.loadExtension(msg.extension, function(err) {
if (err) { node.error(err,msg); }
});
}
Then you can send in a message with msg.extension set to the full filename of the extension.
Let me know if it helps ...
Tried to reinstall node-red-node-sqlite and got this error log:
0 info it worked if it ends with ok
1 verbose cli [ '/usr/bin/node',
1 verbose cli '/usr/bin/npm',
1 verbose cli 'install',
1 verbose cli 'node-red-node-sqlite' ]
2 info using npm@5.6.0
3 info using node@v8.11.4
4 verbose npm-session 18d7a0b8eaa6b47e
5 silly install loadCurrentTree
6 silly install readLocalPackageData
7 http fetch GET 200 https://registry.npmjs.org/node-red-node-sqlite 89ms (from cache)
8 silly pacote tag manifest for node-red-node-sqlite@latest fetched in 192ms
9 silly install loadIdealTree
10 silly install cloneCurrentTreeToIdealTree
11 silly install loadShrinkwrap
12 silly install loadAllDepsIntoIdealTree
13 silly saveTree node-red-node-sqlite@0.3.2
14 verbose stack Error: Refusing to install package with name "node-red-node-sqlite" under a package
14 verbose stack also called "node-red-node-sqlite". Did you name your project the same
14 verbose stack as the dependency you're installing?
14 verbose stack
14 verbose stack For more information, see:
14 verbose stack https://docs.npmjs.com/cli/install#limitations-of-npms-install-algorithm
14 verbose stack at checkSelf (/usr/lib/node_modules/npm/lib/install/validate-args.js:64:14)
14 verbose stack at Array. (/usr/lib/node_modules/npm/node_modules/slide/lib/bind-actor.js:15:8)
14 verbose stack at LOOP (/usr/lib/node_modules/npm/node_modules/slide/lib/chain.js:15:14)
14 verbose stack at /usr/lib/node_modules/npm/node_modules/slide/lib/chain.js:18:7
14 verbose stack at hasMinimumFields (/usr/lib/node_modules/npm/lib/install/validate-args.js:30:12)
14 verbose stack at Array. (/usr/lib/node_modules/npm/node_modules/slide/lib/bind-actor.js:15:8)
14 verbose stack at LOOP (/usr/lib/node_modules/npm/node_modules/slide/lib/chain.js:15:14)
14 verbose stack at chain (/usr/lib/node_modules/npm/node_modules/slide/lib/chain.js:20:5)
14 verbose stack at /usr/lib/node_modules/npm/lib/install/validate-args.js:16:5
14 verbose stack at /usr/lib/node_modules/npm/node_modules/slide/lib/async-map.js:52:35
14 verbose stack at Array.forEach ()
14 verbose stack at /usr/lib/node_modules/npm/node_modules/slide/lib/async-map.js:52:11
14 verbose stack at Array.forEach ()
14 verbose stack at asyncMap (/usr/lib/node_modules/npm/node_modules/slide/lib/async-map.js:51:8)
14 verbose stack at module.exports (/usr/lib/node_modules/npm/lib/install/validate-args.js:15:3)
14 verbose stack at Array. (/usr/lib/node_modules/npm/node_modules/slide/lib/bind-actor.js:15:8)
15 verbose cwd /home/pi/.signalk/red/node_modules/node-red-node-sqlite
16 verbose Linux 4.14.52-v7+
17 verbose argv "/usr/bin/node" "/usr/bin/npm" "install" "node-red-node-sqlite"
18 verbose node v8.11.4
19 verbose npm v5.6.0
20 error code ENOSELF
21 error Refusing to install package with name "node-red-node-sqlite" under a package
21 error also called "node-red-node-sqlite". Did you name your project the same
21 error as the dependency you're installing?
...
22 verbose exit [ 1, true ]
This error resolved after a reboot. Go Figure. I tried a service restart first which led to experiencing the above error. After a reboot my sqlite node is back, so on to testing.
OK, After a few errors on my part I got the flow going again.
Using a change node, I set msg.extension to the full path of the extension (the same path I use in the .sqliterc file with the .load command).
Then the flow goes to the sqlite node where I am using a fixed sql statement.
OK - I have pushed node-red-node-sqlite@0.3.3 - which has a better implementation of the fix that sorts out that timing problem. I don't have a valid extension to try - but at least it now tries to load a file (and for me then fails)... hopefully will work for you with a correct file.
I did notice it seems to want the full path, and filename - but didn't need the final extension ( On Mac .dylib) but the error message was quite clear.
