# Sqlite3 data insert problem

**URL:** <https://discourse.nodered.org/t/sqlite3-data-insert-problem/56075>\
**Category:** General\
**Created:** [4 January 2022 09:11 UTC](https://discourse.nodered.org/t/sqlite3-data-insert-problem/56075 "2022-01-04T09:11:13Z")\
**Posts on this page:** 11\
**Page:** 2

<div class="post-metadata">

**Author:** ![nerevar](https://avatars.discourse-cdn.com/v4/letter/n/f475e1/32.png) [@nerevar](https://discourse.nodered.org/u/nerevar)\
**Post date:** [4 January 2022 18:54 UTC](https://discourse.nodered.org/t/sqlite3-data-insert-problem/56075/22 "2022-01-04T18:54:59Z")

</div>

> [@Colin](#):
>
> ```auto
> 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:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/9/49ba597aae493d1ec544851f79fd05093c9fe9ee.png)

My pi has got this version installed:

```auto
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:

```auto
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](https://discourse.nodered.org/t/sqlite-doesn-t-work-after-update/1830/12):
>
> 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

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [4 January 2022 19:09 UTC](https://discourse.nodered.org/t/sqlite3-data-insert-problem/56075/23 "2022-01-04T19:09:41Z")

</div>

> [@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](https://github.com/node-red/node-red-nodes/issues/670#issuecomment-915956043)

---

<div class="post-metadata">

**Author:** ![nerevar](https://avatars.discourse-cdn.com/v4/letter/n/f475e1/32.png) [@nerevar](https://discourse.nodered.org/u/nerevar)\
**Post date:** [4 January 2022 19:17 UTC](https://discourse.nodered.org/t/sqlite3-data-insert-problem/56075/24 "2022-01-04T19:17:43Z")

</div>

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

---

<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:** [4 January 2022 19:41 UTC](https://discourse.nodered.org/t/sqlite3-data-insert-problem/56075/25 "2022-01-04T19:41:27Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [4 January 2022 21:55 UTC](https://discourse.nodered.org/t/sqlite3-data-insert-problem/56075/26 "2022-01-04T21:55:41Z")

</div>

Hi @dceejay .. im not so experienced with github but i had a go at a PR ([link](https://github.com/node-red/node-red-nodes/pull/869))

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](https://github.com/node-red/node-red-nodes/blob/master/storage/sqlite/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 ?

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

```

---

<div class="post-metadata">

**Author:** ![nerevar](https://avatars.discourse-cdn.com/v4/letter/n/f475e1/32.png) [@nerevar](https://discourse.nodered.org/u/nerevar)\
**Post date:** [5 January 2022 07:35 UTC](https://discourse.nodered.org/t/sqlite3-data-insert-problem/56075/27 "2022-01-05T07:35:58Z")

</div>

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:

```auto
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

```

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [5 January 2022 08:27 UTC](https://discourse.nodered.org/t/sqlite3-data-insert-problem/56075/28 "2022-01-05T08:27:19Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![nerevar](https://avatars.discourse-cdn.com/v4/letter/n/f475e1/32.png) [@nerevar](https://discourse.nodered.org/u/nerevar)\
**Post date:** [5 January 2022 08:41 UTC](https://discourse.nodered.org/t/sqlite3-data-insert-problem/56075/29 "2022-01-05T08:41:13Z")

</div>

> [@nerevar](#):
>
> ```auto
> 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
> 
> ```

> <https://github.com/node-red/node-red-nodes/issues/870>
>
> \<!--
> \## Before you hit that Submit button....
> 
> This issue tracker is for prob…lems with the extra nodes maintained by the Node-RED project.
> 
> If your issue is:
> - a general 'how-to' type question,
> - a feature request or suggestion for a change,
> - or problems with 3rd party (\`node-red-contrib-\`) nodes
> 
> please use the \[Node-RED Forum\](https://discourse.nodered.org) or \[slack team\](https://nodered.org/slack), or ask a question on \[Stack Overflow\](https://stackoverflow.com/questions/tagged/node-red) and tag it \`node-red\`.
> 
> That way the whole Node-RED user community can help, rather than rely on the core development team.
> 
> \## So you have a real issue to raise...
> 
> To help us understand the issue, please fill-in as much of the following information as you can:
> \--\>
> 
> \### Which node are you reporting an issue on?
> node-red-node-sqlite
> 
> \### What are the steps to reproduce?
> Insert data using: 
> \`INSERT INTO users\_connection\_data (ip, connected, date) VALUES ($1, $2, $3);\`
> or
> \`INSERT INTO users\_connection\_data (ip, connected, date) VALUES ($ip, $connected, $date);\`
> with the payload for binding:
> \`\["192.168.3.161",1,1641368629093\]\`
> 
> Table was created using:
> \`CREATE TABLE users\_connection\_data(id INTEGER PRIMARY KEY AUTOINCREMENT, ip TEXT, connected INTEGER, date INTEGER);\`
> 
> \### What happens?
> After the insert the node goes into the error state and does not work until Restart Flows.
> 
> \### What do you expect to happen?
> To insert the data and afterwords be able to use the node.
> 
> \### Please tell us about your environment:
> Node-RED version: v2.1.4
> Node.js version: v14.18.2
> Linux 4.19.95-rt38-v7 arm LE (debian 10)
> node-red-node-sqlite v1.01

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [5 January 2022 09:33 UTC](https://discourse.nodered.org/t/sqlite3-data-insert-problem/56075/30 "2022-01-05T09:33:50Z")

</div>

> [@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

---

<div class="post-metadata">

**Author:** ![nerevar](https://avatars.discourse-cdn.com/v4/letter/n/f475e1/32.png) [@nerevar](https://discourse.nodered.org/u/nerevar)\
**Post date:** [5 January 2022 14:26 UTC](https://discourse.nodered.org/t/sqlite3-data-insert-problem/56075/31 "2022-01-05T14:26:46Z")

</div>

The issue has been corrected with version 1.0.2:

> <https://github.com/node-red/node-red-nodes/issues/870#issuecomment-1005546331>
>
> \<!--
> \## Before you hit that Submit button....
> 
> This issue tracker is for prob…lems with the extra nodes maintained by the Node-RED project.
> 
> If your issue is:
> - a general 'how-to' type question,
> - a feature request or suggestion for a change,
> - or problems with 3rd party (\`node-red-contrib-\`) nodes
> 
> please use the \[Node-RED Forum\](https://discourse.nodered.org) or \[slack team\](https://nodered.org/slack), or ask a question on \[Stack Overflow\](https://stackoverflow.com/questions/tagged/node-red) and tag it \`node-red\`.
> 
> That way the whole Node-RED user community can help, rather than rely on the core development team.
> 
> \## So you have a real issue to raise...
> 
> To help us understand the issue, please fill-in as much of the following information as you can:
> \--\>
> 
> \### Which node are you reporting an issue on?
> node-red-node-sqlite
> 
> \### What are the steps to reproduce?
> Insert data using: 
> \`INSERT INTO users\_connection\_data (ip, connected, date) VALUES ($1, $2, $3);\`
> or
> \`INSERT INTO users\_connection\_data (ip, connected, date) VALUES ($ip, $connected, $date);\`
> with the payload for binding:
> \`\["192.168.3.161",1,1641368629093\]\`
> 
> Table was created using:
> \`CREATE TABLE users\_connection\_data(id INTEGER PRIMARY KEY AUTOINCREMENT, ip TEXT, connected INTEGER, date INTEGER);\`
> 
> \### What happens?
> After the insert the node goes into the error state and does not work until Restart Flows.
> 
> \### What do you expect to happen?
> To insert the data and afterwords be able to use the node.
> 
> \### Please tell us about your environment:
> Node-RED version: v2.1.4
> Node.js version: v14.18.2
> Linux 4.19.95-rt38-v7 arm LE (debian 10)
> node-red-node-sqlite v1.01

`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;`

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [6 March 2022 14:26 UTC](https://discourse.nodered.org/t/sqlite3-data-insert-problem/56075/32 "2022-03-06T14:26:52Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.

[Previous page](https://discourse.nodered.org/t/sqlite3-data-insert-problem/56075.md?page=1)