Sqlite3 data insert problem

[nerevar]

@Colin

When using Normal or Prepared Statement, the query must be entered in the node config.

Pass in the parameters as an object in msg.params for Prepared Statement. Ex:
msg.params = {
    $id:1,
    $name:"John Doe"
}
Parameter object names must match parameters set up in the Prepared Statement. If you get the error SQLITE_RANGE: bind or column index out of range be sure to include $ on the parameter object key.
The SQL query for the example above could be: insert into user_table (user_id, user) VALUES ($id, $name);

This one works also, but It would be required to change the flows.

So the node has got multiple options of saving/accessing data:

My pi has got this version installed:

Node-RED version: v2.1.4
Node.js  version: v14.18.2
Linux 4.19.95-rt38-v7 arm LE
node-red-node-sqlite v1.01

And the windows test rig this one:

Node-RED version: v1.3.4
Node.js  version: v14.16.1
Windows_NT 10.0.19043 x64 LE
node-red-node-sqlite v0.60

On v0.60 it all works without problems with normal topic binding.

Atm I am reinstalling the node once again following the advice in another topic:

Sqlite doesn t work after update

I have exactly the same problem here on my RPi3 and I recently I found the solution, check if it works for you. Open a fresh installation of Node-RED Go to Manage Pallet Select the sqlite node and install it Do not touch the system for at least 10 minutes (even if the Node-RED interface tells you that the installation is done) If your hardware is older than the Pi3 maybe 15 minutes is enough. This happens because the compilation process of the sqlite binaries happens in background and it tak…

I will get back to you

[UnborN]

@nerevar

I am reinstalling the node once again following the advice in another topic

Unless you go to an older version I dont think thats going to change anything .. as you didnt have a problem with the node working or being installed like in that thread.

The issue is that after some sqlite node version, some code was introduced to check for $ instead of ?
(along with the length of $ parameters and msg.payload length)

Read this post on Github

[nerevar]

Nice find!

Thank you E1cid, Colin and UnborN.

I think they should have updated the documentation in the node-red library (especialy regarding topic bind insert).

[dceejay]

As Colin mentioned. Pull Requests to improve docs are always appreciated

[UnborN]

Hi @dceejay .. im not so experienced with github but i had a go at a PR (link)

Updated sqlite node README.md to match the more detailed node help file (the usage info was already there)
This can give better information to users that dont have the node installed but want to see how it works from the readme.md file.

Also added an example of how to use parameters Via msg.topic for both node html and README.md

ps. do you think its worth reverting the way parameters are passed with matching length
instead of $ to use .split('?') in line 57 ?

if (Array.isArray(msg.payload)) {
                                if (msg.payload.length === (msg.topic.split('$').length - 1) ) { bind = msg.payload; }
                                else { bind = []; }
                            }

[nerevar]

How to reset node errors?
When node enters the error state "Error: SQLITE_RANGE: column index out of range" the only way to reset is to delete it. Is there any other way?

There is some problem if besides topic and payload other properties are also passed. E.g. "msg.pull", "msg.sockedid", "msg.socketip" which I use in my function node to 1. SELECT and then INSERT or UPDATE the DB.

Seems like this input causes the node to go into permanent error state:

topic: "INSERT INTO users_connection_data (ip, connected, date) VALUES ($1, $2, $3);"
payload: array[3]
0: "192.168.3.161"
1: 1
2: 1641368629093

[Colin]

Please submit a bug on the node's GitHub page.
Doing a Restart Flows from the Deploy drop-down should clear the error.
Provide the database schema on the report.

[nerevar]

@nerevar

Node-RED version: v2.1.4
Node.js  version: v14.18.2
Linux 4.19.95-rt38-v7 arm LE
node-red-node-sqlite v1.01

node-red-node-sqlite new INSERT with '$' puts the node into error state

[Colin]

@nerevar

topic: "INSERT INTO users_connection_data (ip, connected, date) VALUES ($1, $2, $3);"

It should not lockup the node permanently, but that is apparently not a valid statement, hence the errror.
It should be, I think
topic: "INSERT INTO users_connection_data (ip, connected, date) VALUES ($ip, $connected, $date)"

[Edit] Added missing " at end of topic

[nerevar]

The issue has been corrected with version 1.0.2:

node-red-node-sqlite new INSERT with '$' puts the node into error state

node-red-stop
cd .node-red
npm install node-red-node-sqlite@1.0.2
node-red-start

Note that when binding a payload the UPDATE also needs to be sent with "$", example:
UPDATE users_connection_data SET connected = $connected, date = $date WHERE ip = $ip;